Step-By-Step Setup of Oracle Active Data Guard for RAC Physical Standby Database 12c

Goal: Creating a 2-node RAC physical standby CDB for a 2-node RAC primary CDB.

Existing:

-    GI and RAC 12.1.0.2 installed / configured on the 2 primary nodes and 2 standby nodes

-    RAC primary CDB with two instances, using ASM

To set up:

-    Data Guard, by adding RAC physical standby DB with two instances, using ASM

	Primary	Standby
Cluster Nodes	dc1_db1, dc1_db2	dc2_db1, dc2_db2
Clusterware	GI (12.1.0.2)	GI (12.1.0.2)
GRID_HOME	/u01/app/12.1.0/grid	/u01/app/12.1.0/grid
SCAN	dc1-scan	dc2-scan
SCAN listener Node (port)	SCAN VIPs (port 1521)	SCAN VIPs (port 1521)
DB_NAME	cdbrac	cdbrac
DB_UNIQUE_NAME	cdbrac	stdbrac
DB Instances	cdbrac1, cdbrac2	stdbrac1, stdbrac2
DB Storage	ASM	ASM
File Management	OMF	OMF
ASM DG for DB files	+DATA	+DATA
ASM DG for FRA	+FRA	+FRA
ORACLE_HOME	/u01/app/oracle/product/12.1.0/dbhome_1	/u01/app/oracle/product/12.1.0/dbhome_1
RAC version	12.1.0.2	12.1.0.2
OS	RHEL 7.4	RHEL 7.4

Steps:

·         Set initialization parameters for the primary and standby DBs

·         Configure Oracle Net on the RAC primary and RAC physical standby nodes

·         Copy the password file for the RAC primary DB to RAC physical standby DB nodes

·         Create the standby DB using RMAN Duplicate

·         Verify Data Guard configuration

·         Test switchover and switchback using Data Guard Broker

Pre-requisites for RAC primary CDB and RAC physical standby DB

·         The primary RAC DB is in archivelog mode with force logging enabled.

SQL> select log_mode,database_role,open_mode from v$database;

LOG_MODE   DATABASE_ROLE OPEN_MODE

---------- ------------- ----------

ARCHIVELOG PRIMARY       READ WRITE

SQL> alter database force logging;

Database altered.

SQL> select name,force_logging from v$database;

NAME   FORCE_LOGGING

------ -------------

CDBRAC YES

·         The primary RAC DB is located on ASM.

·         The standby RAC DB will be created on ASM.

·         The primary and standby DBs use flash recovery area.

·         The standby RAC nodes already installed with 12.1.0.2 GI and RDBMS software.

1.       Set the DB initialization parameters for Data Guard configuration

PARAMETERS	RAC PRIMARY DATABASE	RAC PHYSICAL STANDBY DATABASE
DG_BROKER_CONFIG_FILE1	'+DATA/CDBRAC/drlCDBRAC.dat'	'+DATA/STDBRAC/drlSTDBRAC.dat'
DG_BROKER_CONFIG_FILE2	'+DATA/CDBRAC/dr2CDBRAC.dat'	'+DATA/STDBRAC/dr2STDBRAC.dat'
DG_BROKER_START	TRUE	TRUE
LOG_ARCHIVE_CONFIG	'DG_CONFIG=(cdbrac,stdbrac)'	'DG_CONFIG=(stdbrac,cdbrac)'
LOG_ARCHIVE_DEST_1	'LOCATION=USE_DB_RECOVERY_FILE_DEST  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cdbrac'	'LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdbrac'
LOG_ARCHIVE_DEST_2	'SERVICE=stdbrac VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_ UNIQUE_NAME=stdbrac'	'SERVICE=cdbrac VALID_FOR=(ONLINE_LOGFILE,ALL_ROLES) DB_UNIQUE_NAME=cdbrac'
DB_NAME	cdbrac	cdbrac
DB_UNIQUE_NAME	cdbrac	stdbrac
LOG_ARCHIVE_FORMAT	'%t_%s_%r.arc'	'%t_%s_%r.arc'
REMOTE_LOGIN_PASSWORDFILE	EXCLUSIVE	EXCLUSIVE
FAL_SERVER	stdbrac1,stdbrac2'	cdbrac1,cdbrac2'
FAL_CLIENT	cdbrac1,cdbrac2'	stdbrac1,stdbrac2'
DB_FILE_NAME_CONVERT	'+DATA/STDBRAC/DATAFILE/', '+DATA/CDBRAC/DATAFILE/'	'+DATA/CDBRAC/DATAFILE/', '+DATA/STDBRAC/DATAFILE/'
LOG_FILE_NAME_CONVERT	'+DATA/STDBRAC/ONLINELOG/', '+DATA/CDBRAC/ONLINELOG/', '+FRA/STDBRAC/ONLINELOG/', '+FRA/CDBRAC/NLINELOG/'	'+DATA/CDBRAC/ONLINELOG/', '+DATA/STDBRAC/ONLINELOG/', '+FRA/CDBRAC/ONLINELOG/', '+FRA/STDBRAC/NLINELOG/'
STANDBY_FILE_MANAGEMENT	AUTO	AUTO

SQL> alter system set log_archive_config='DG_CONFIG=(cdbrac,stdbrac)' scope=both sid='*';

SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=cdbrac' scope= both sid='*';

SQL> alter system set log_archive_dest_2='SERVICE=stdbrac VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=stdbrac' scope=both sid='*';

SQL> alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=both sid='*';

SQL> alter system set LOG_ARCHIVE_MAX_PROCESSES=8 scope=both sid='*';

SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=both sid='*';

If you prefer to change the DB_UNIQUE_NAME then you must update values in spfile and then bounce the DB, since it’s a static parameter.

SQL> alter system set DB_UNIQUE_NAME=cdbrac scope=spfile sid='*';

2.       Configure the RAC primary DB initialization parameters to support standby role.

SQL> alter system set FAL_SERVER='stdbrac1,stdbrac2' scope=both sid='*';

SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=spfile sid='*';

DB_FILE_NAME_CONVERT/LOG_FILE_NAME_CONVERT: If the primary and standby file system/disk group are different then you must configure these two parameters.

SQL> alter system set DB_FILE_NAME_CONVERT='+DATA','+DATADG' scope=spfile sid='*';

SQL> alter system set LOG_FILE_NAME_CONVERT= '+FRA','+FRADG' scope=spfile sid='*';

3.       On one of the RAC primary DB nodes, create the staging directory, /home/oracle/backup and the initialization parameter file, initSTDBRAC.ora, for the RAC physical standby DB.

oracle@dc1_db1> cd $HOME

oracle@dc1_db1> mkdir backup

SQL> create pfile='/home/oracle/backup/initSTDBRAC.ora' from spfile;

Note: Comment out 2 initialization parameters in initSTDBRAC.ora file, control_files and cluster_DB.

4.       Copy initSTDBRAC.ora file to the RAC physical standby node.

oracle@dc1_db1> scp /home/oracle/backup/initSTDBRAC.ora \ oracle@dc2_db1:/u01/app/oracle/product/12.1.0/dbhome_1/dbs/

5    5.  We are performing active duplicate of the primary DB, hence no need to back up the primary DB and copy to the standby node.

6.       Create standby redo logs on the RAC primary DB to support the standby role. The recommended number of standby redo logs is:

(maximum # of logfiles +1) * maximum # of threads

The Standby Redo Log (SRL) files must be the same size as the Online Redo Log (ORL) files, and the number of SRL files needs to be the same of ORL files, plus one. In RAC primary, “plus one” should be per RAC instance. These files need to be created on both standby and primary sides in preparation for switchover.

Create a single member in each group to avoid waits with commit for each transaction in each member.

SQL> select group#,thread#,bytes from v$log;

GROUP# THREAD# BYTES

------ ------- --------

     1       1 52428800

     2       1 52428800

     3       2 52428800

     4       2 52428800

SQL> ALTER DB ADD STANDBY LOGFILE THREAD 1 ('+FRA') SIZE 50M;

SQL> ALTER DB ADD STANDBY LOGFILE THREAD 1 ('+FRA') SIZE 50M;

SQL> ALTER DB ADD STANDBY LOGFILE THREAD 1 ('+FRA') SIZE 50M;

SQL> ALTER DB ADD STANDBY LOGFILE THREAD 2 ('+FRA') SIZE 50M;

SQL> ALTER DB ADD STANDBY LOGFILE THREAD 2 ('+FRA') SIZE 50M;

SQL> ALTER DB ADD STANDBY LOGFILE THREAD 2 ('+FRA') SIZE 50M;

SQL> select group#,thread#,bytes from v$standby_log;

GROUP# THREAD# BYTES

------ ------- --------

     5       1 52428800

     6       1 52428800

     7       1 52428800

     8       2 52428800

     9       2 52428800

10       2 52428800

Note: No need to create SRL files on standby side and Oracle take cares of it during RMAN duplicate.

7.       Copy the password file in ASM from RAC primary node to RAC physical standby node using ‘pwcopy’ command from ASMCMD prompt.

oracle@dc1_db1>  asmcmd

ASMCMD> pwget --dbuniquename cdbrac

+DATA/cdbrac/orapwcdbrac

ASMCMD> pwcopy --dbuniquename cdbrac '+DATA/cdbrac/orapwcdbrac' '/home/oracle/backup'

copying +DATA/cdbrac/orapwcdbrac -> /home/oracle/backup/orapwcdbrac

ASMCMD> exit

oracle@dc1_db1> scp /home/oracle/backup/orapwcdbrac \ oracle@dc2_db1:/home/oracle/backup

8.       Log on to the RAC physical standby node and copy the password file from local file system directory to ASM.

oracle@dc2_db1> asmcmd

ASMCMD> pwcopy --dbuniquename stdbrac /home/oracle/backup/orapwcdbrac' '+DATA/stdbrac/orapwcdbrac'

copying /home/oracle/backup/orapwcdbrac -> DATA/stdbrac/orapwcdbrac

ASMCMD> exit

9.       Configure Oracle net service/TNS names for the standby system, for the RMAN duplicate operation while connecting to the auxiliary instance.

CDBRAC1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(NODE = dc1-vip)(PORT = 1521))  

    (CONNECT_DATA =

      (SERVER = DEDICATED)(SERVICE_NAME = cdbrac)(UR=A)

      (INSTANCE_NAME = cdbrac1))

       )

STDBRAC1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(NODE = dc2-vip)(PORT = 1521))  

    (CONNECT_DATA =

      (SERVER = DEDICATED)(SERVICE_NAME = stdbrac)(UR=A)

      (INSTANCE_NAME = stdbrac1))

       )

We set UR=A with the SERVICE_NAME, the reason is during the RMAN duplicate internally it bounces the standby DB and whenever instance is closed there will be no more services registered with the listener and hence by this setting, we can escape from “ORA-12514: TNS:listener does not currently know of service requested in connect descriptor” and Oracle can bounce the standby DB safely.

10.   Log on to one of the RAC physical standby DB nodes, set up the appropriate environment variables, and start the standby DB instance in ‘NOMOUNT’ state. Using RMAN we will perform Active Duplicate to create the standby DB.

oracle@dc2_db1> export ORACLE_SID=stdbrac1

oracle@dc2_db1> export ORACLE_BASE=/u01/app/oracle

oracle@dc2_db1> export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

oracle@dc2_db1> sqlplus / as sysdba

SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initSTDBRAC.ora'

oracle@dc2_db1> rman target sys/Pswd4SYS@cdbrac1 auxiliary sys/Pswd4SYS@stdbrac1

RMAN> duplicate target DB for standby from active DB nofilenamecheck;

Starting Duplicate DB at 30-AUG-17

using target DB control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=28 device type=DISK

(Output is truncated……)

RMAN> alter system register;

using target DB control file instead of recovery catalog Statement processed

RMAN> exit

11.   Shut down the RAC physical standby DB instance (stdbrac1) and incorporate the changes for the initialization parameters, control_files and cluster_DB, in initSTDBRAC.ora file.

SQL> shutdown immediate;

oracle@dc2_db1> vi /u01/app/oracle/product/12.1.0/dbhome_1/dbs/initSTDBRAC.ora

oracle@dc2_db1> export ORACLE_SID=stdbrac1

oracle@dc2_db1> export ORACLE_BASE=/u01/app/oracle

oracle@dc2_db1> export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

oracle@dc2_db1> sqlplus / as sysdba

SQL> select open_mode, database_role from v$database;

OPEN_MODE  DATABASE_ROLE

---------  ----------------

MOUNTED    PHYSICAL STANDBY

SQL> show parameter control_files

SQL> show parameter cluster_DB

SQL> exit

12.   On either node of the standby cluster, register the standby DB and the DB instances with the Oracle Cluster Registry (OCR) using Server Control.

oracle@dc2_db1> srvctl add database -db stdbrac -dbname cdbrac \

-o /u01/app/oracle/product/12.1.0/dbhome_1 \

-spfile +DATA/STDBRAC/parameterfile/spfileSTDBRAC.ora \

-role physical_standby -diskgroup DATA,FRA

oracle@dc2_db1> srvctl add instance -db stdbrac -instance stdbrac1 -node dc2_db1

oracle@dc2_db1> srvctl add instance -db stdbrac -instance stdbrac2 -node dc2_db2

oracle@dc2_db1> srvctl start database -db stdbrac -startoption mount

oracle@dc2_db1> ps -ef | grep pmon

oracle@dc2_db1> srvctl config database -db stdbrac

The following are descriptions of the options in these SRVCTL commands:

-db specifies the DB unique name (DB_UNIQUE_NAME).

-dbname specifies DB name (DB_NAME).

-o specifies the DB Oracle home.

13.   Start recovery on the RAC physical standby DB

Start the MRP (Media Recovery Process) on any one node of the RAC physical standby DB.

SQL> alter database recover managed standby database disconnect from session;

14.   Monitor REDO transport status

To ensure the standby is catching up the redo data various views can be used to estimate the lag. The following is one of the many ways and is simple and easy to check the status.

On the primary:

SQL> select thread#, max(sequence#) from v$archived_log group by thread#;

On the standby:

SQL> select thread#, max(sequence#) from v$archived_log where applied=’YES’ group by thread#;

15.   Test Data Guard 12c configuration

Test PDB, table and data can be created on the primary RAC DB and checked on the physical standby RAC DB to confirm that the Data Guard 12c configuration is working as expected.

16.   Test switchover from RAC primary DB to RAC physical standby DB

Log on to a RAC primary DB node and check the readiness of the primary and standby DBs for switchover.

oracle@dc1_db1> dgmgrl

DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/Pswd4SYS@cdbrac

Connected as SYSDBA.

DGMGRL> connect sys/Pswd4SYS@stdbrac

Connected as SYSDBA.

DGMGRL> show configuration verbose

Configuration - cdbrac_dg_config

  Protection Mode: MaxPerformance

  Members:

  cdbrac  - Primary database

    stdbrac - Physical standby database

  Properties:

    FastStartFailoverThreshold      = '30'

    OperationTimeout                = '30'

    TraceLevel                      = 'USER'

    FastStartFailoverLagLimit       = '30'

    CommunicationTimeout            = '180'

    ObserverReconnect               = '0'

    FastStartFailoverAutoReinstate  = 'TRUE'

    FastStartFailoverPmyShutdown    = 'TRUE'

    BystandersFollowRoleChange      = 'ALL'

    ObserverOverride                = 'FALSE'

    ExternalDestination1            = ''

    ExternalDestination2            = ''

    PrimaryLostWriteAction          = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL> validate database cdbrac

  Database Role:    Primary database

  Ready for Switchover:  Yes

DGMGRL> validate database verbose stdbrac

  Database Role:     Physical standby database

  Primary Database:  cdbrac

  Ready for Switchover:  Yes

  Ready for Failover:    Yes (Primary Running)

       ……

Standby Apply-Related Information:

    Apply State:      Running

    Apply Lag:        0 seconds (computed 0 seconds ago)

    Apply Delay:      0 minutes

Transport-Related Information:

    Transport On:      Yes

    Gap Status:        No Gap

    Transport Lag:     0 seconds (computed 0 seconds ago)

    Transport Status:  Success

       ……

Apply-Related Property Settings:

    Property                        proddb Value            stdbydb Value

    DelayMins                       0                        0

    ApplyParallel                   AUTO                     AUTO

Transport-Related Property Settings:

    Property                        proddb Value            stdbydb Value

    LogXptMode                      ASYNC                    ASYNC

    RedoRoutes                      <empty>                  <empty>

    Dependency                      <empty>                  <empty>

    DelayMins                       0                        0

    Binding                         optional                 optional

    MaxFailure                      0                        0

    MaxConnections                  1                        1

    ReopenSecs                      300                      300

    NetTimeout                      30                       30

    RedoCompression                 DISABLE                  DISABLE

    LogShipping                     ON                       ON

DGMGRL> switchover to stdbrac

Performing switchover NOW, please wait...

New primary database "stdbrac" is opening...

Operation requires start up of instance "cdbrac1" on database "cdbrac"

Starting instance "cdbrac1"...

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "stdbrac"

17.   Log on to the new RAC primary DB (stdbrac) and check the DG configuration status.

oracle@dc1_db1> dgmgrl

DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/Pswd4SYS@stdbrac

Connected as SYSDBA.

DGMGRL> show configuration verbose

Configuration - cdbrac_dg_config

  Protection Mode: MaxPerformance

  Members:

  stdbrac  - Primary database

    cdbrac - Physical standby database

  Properties:

    FastStartFailoverThreshold      = '30'

    OperationTimeout                = '30'

    TraceLevel                      = 'USER'

    FastStartFailoverLagLimit       = '30'

    CommunicationTimeout            = '180'

    ObserverReconnect               = '0'

    FastStartFailoverAutoReinstate  = 'TRUE'

    FastStartFailoverPmyShutdown    = 'TRUE'

    BystandersFollowRoleChange      = 'ALL'

    ObserverOverride                = 'FALSE'

    ExternalDestination1            = ''

    ExternalDestination2            = ''

    PrimaryLostWriteAction          = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

18.   Test switchback from the new RAC primary DB (stdbrac) to the new standby DB (cdbrac)

         oracle@dc2_db1> dgmgrl

DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/Pswd4SYS@stdbrac

Connected as SYSDBA.

DGMGRL> connect sys/Pswd4SYS@cdbrac

Connected as SYSDBA.

DGMGRL> show configuration

Configuration - cdbrac_dg_config

  Protection Mode: MaxPerformance

  Members:

  stdbrac  - Primary database

    cdbrac - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL> validate database stdbrac

  Database Role:    Primary database

  Ready for Switchover:  Yes

DGMGRL> validate database cdbrac

  Database Role:     Physical standby database

  Primary Database:  stdbrac

  Ready for Switchover:  Yes

  Ready for Failover:    Yes (Primary Running)

DGMGRL> switchover to cdbrac

Performing switchover NOW, please wait...

New primary database "cdbbrac" is opening...

Operation requires start up of instance "stdbrac1" on database "stdbrac"

Starting instance "stdbrac1"...

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "cdbrac"

19.   Log on to the RAC primary DB (cdbrac) and check the DG configuration status

oracle@dc1_db1> dgmgrl

DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/Pswd4SYS@cdbrac

Connected as SYSDBA.

DGMGRL> show configuration

Configuration - cdbrac_dg_config

  Protection Mode: MaxPerformance

  Members:

  cdbrac  - Primary database

    stdbrac - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS