DataGuard - quick notes

DATAGUARD – Setting up manual process using RMAN DUPLICATE


This is a working doc - will amend as necessary 
Thanks to Russell Willcox for the instructions.
See also
There is a prebuilt Oracle VM under here - I built the Linux 7, Oracle 12.2 version to test.

(You can use the vagrant user - password "vagrant" to login to the servers built under there but for Dataguard, the root user is "rootpasswd" and the oracle password is "oracle.)

1 On Primary database check db in archive log mode

            Export ORACLE_SID="insert name here"

            Sqlplus / as sysdba

            archive log list;

2 If not in archive log mode turn it on

            shutdown immediate;

            startup mount;

            alter database archivelog;

            alter database open;

3 Enable force logging by issuing command

            alter database force logging;

            alter system switch logfile;

4 Create standby redo logs on both the Primary and Standby


alter database add standby logfile group 4 ('+NFPSADWORADATA','+FRA') size 200M;
alter database add standby logfile group 5 ('+NFPSADWORADATA','+FRA') size 200M;
alter database add standby logfile group 6 ('+NFPSADWORADATA','+FRA') size 200M;


5 Check the DB_NAME and DB_UNIQUE_NAME on the Primary database


            Show parameter db_name

            Show parameter db_unique_name

The DB_NAME of the standby database will be the same as the primary but the DB_UNIQUE_NAME will be different.

6 Ensure that the STANDBY_FILE_MANAGEMENT is set

            alter system set standby_file_management=auto;


7 TNS service names setup


Ensure that the primary and standby databases are in the tnsnames.ora on both servers.
  

Standby




Primary



8 Setup the listener.ora on both servers

Primary


Standby


9 Stop and start the listener on both servers

            lsnrctl stop

            lsnrctl start


10 Prepare for the RMAN duplicate

11 Create the standby Control file and Pfile on the Primary database

            alter database create standby controlfile as ‘/tmp/adwdata_stby.ctl’;

            create pfile=’/tmp/adwdata_stby.ora’;


12 Create the necessary directories on the Standby server

            Mkdir -p /u01/app/12.2.0/admin/ADWDATA

                        This location needs to be the same as the primary

13 Copy the created files from the Primary server


            Scp standby control file to all locations

            Scp servername:/tmp/adwdata_stby.ctl /u01/app/oracle………

            Scp servername:/tmp/adwdata_stby.ora /u01/app/oracle/product/dbhmome/dbs


14 Establish the remote login password file
           

Either copy the password file from the primary server, or create it on the standby using the same sys password as that used on the primary, the default location for the file is:

 /u01/app/oracle/product/dbhome/dbs

Syntax to create the password file is

Orapwd file=filepath/filename password=sys password entries=10


15 Create the standby using RMAN Duplicate

            Rman TARGET sys/password@ADWDATA AUXILIARY sys/password@ADWDATA_STBY

            DUPLICATE TARGET DATABASE

            FOR STANDBY

            FROM ACTIVE DATABASE

            DORECOVER

            SPFILE

            SET db_unique_name='DB11G_STBY' COMMENT 'Is standby'

SET LOG_ARCHIVE_DEST_2='SERVICE=db11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G'

            SET FAL_SERVER='DB11G' COMMENT 'Is primary'

            NOFILENAMECHECK;


            A brief explanation of the individual clauses is shown below.

FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it will not force a DBID change.

FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source datafile, without an additional backup step.

DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.

SPFILE: Allows us to reset values in the spfile when it is copied from the source server.

NOFILENAMECHECK: Destination file locations are not checked.

16 Check the output of the duplicate process

            If no errors are seen proceed to the next step otherwise try the following:

            Dgmgrl sys/password@ADWDATA  (primary)

            Show configuration

            Show database ADWDATA

            Show database ADWDATA_STBY

            Show database ADWDATA InconsistentProperties

            Show database ADWDATA_STBY InconsistentProperties

            If any properties are inconsistent then resolve them before starting the apply process


17 Enable the broker


At this point you will have a primary database and a standby database so you need to start using the Dataguard Broker to manage those databases, enter the following command on both the Primary and the Standby:

alter system set dg_broker_start=true;


On the Primary server issue the following command to register the primary server with the broker:

Dgmgrl sys/password@ADWDATA

create configuration adwdata_dg_config as primary database is ADWDATA connect identifier is ADWDATA;


On the Standby server enter:

add database ADWDATA_STBY as connect identifier is ADWDATA_STBY maintained as physical;

enable configuration;


18 Start the apply process


            alter database recover managed standby database; or

            alter database recover managed standby database disconnect from session;

            (this will return control to your session after starting apply process)

            If you need to cancel the apply process:

            alter database recover managed standby database cancel;

            If you want to delay the application of changes to your standby enter:
  

alter database recover managed standby database cancel;

alter database recover managed standby database delay 30 disconnect from session;

alter database recover managed standby database cancel;

alter database recover managed standby database nodelay disconnect from session;





Additional

Oracle Data Guard switchover

To show the status of the databases currently

Logon onto the Oracle Data Guard broker:

DGMGRL sys/password

Ø Show configuration

Ø Show database dbname

Make a note of which database is the current standby.

To switchover to the standby instance

Ø Switchover to standby_instance

Performing switchover NOW, please wait...

Operation requires a connection to database "cdb1"

Connecting ...

Connected to "cdb1"

Connected as SYSDBA.

New primary database "cdb1" is opening...

Operation requires start up of instance "cdb1" on database "cdb1_stby"

Starting instance "cdb1"...

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Database mounted.

Connected to "cdb1_stby"

Switchover succeeded, new primary is "cdb1"

ORA-01017 error on switchover

switchover to cdb1_stby;

Performing switchover NOW, please wait...

New primary database "cdb1_stby" is opening...

Operation requires start up of instance "cdb1" on database "cdb1"

Starting instance "cdb1"...

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.


Please complete the following steps to finish switchover:

start up and mount instance "cdb1" of database "cdb1"

Resolution

Copy the orapw file from the database that you are initiating the switchover from

Or

Recreate the Oracle password file using Oracle utility as below:

orapwd file=…../dbs/filename password=sys password entries=10

Or

As a quick fix, mount the standby database and put it into recovery mode

Sqlplus / as sysdba

alter database mount;

alter database recover managed standby database disconnect from session;

Verify that switchover is successful

DGMGRL sys/password

Ø Show database primary_dbname

Database - cdb1

Role: PRIMARY

Intended State: TRANSPORT-ON

Instance(s):

cdb1

Database Status:

SUCCESS

Ø Show database standby_dbname

Database - cdb1_stby

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: 0 seconds (computed 27 seconds ago)

Apply Lag: 0 seconds (computed 27 seconds ago)

Average Apply Rate: 218.00 KByte/s

Real Time Query: OFF

Instance(s):


+++++++++++++++++++++++++++++

 This is handy - from here :

 https://dba.stackexchange.com/questions/110497/how-does-one-identify-if-oracle-data-guard-is-enabled-in-oracle-database

 
set serveroutput on
declare
  feature_boolean number;
  aux_count number;
  feature_info clob;
begin
  dbms_feature_data_guard(feature_boolean, aux_count, feature_info);
  dbms_output.put_line(feature_boolean);
  dbms_output.put_line(feature_info);
end;
/
Sample output when Data Guard is not used:
0
Data Guard usage not detected
Sample output when Data Guard is used:
1
Number of standbys: 1, Redo Apply used: TRUE, SQL Apply used: FALSE, Snapshot
Standby used: FALSE, Broker used: TRUE, Protection mode: MAXIMUM PERFORMANCE,
Log transports used: LGWR ASYNC, Fast-Start Failover used: FALSE, Real-Time
Apply used: TRUE, Compression used: FALSE, Flashback used: FALSE










No comments:

Post a Comment

Subscribe to: Post Comments (Atom)