11g Data Guard Cascading Standby Database

This note describes the procedure used to setup and configure a Data Guard Cascaded Standby database environment.

The cascading standby database receives redo from the Primary database and then this redo is cascaded to one or more cascaded standby databases.

So the cascaded  standby database does not receive its redo directly from the Primary database and this type of data guard configuration offloads the overhead associated with performing redo transport from a primary database to a cascading standby database.

Quoting the official documentation:

“Primary database redo is written to the standby redo log as it is received at a cascading standby database. The redo is not immediately cascaded however. It is cascaded after the standby redo log file that it was written to has been archived locally. A cascaded destination will therefore always have a greater redo transport lag, with respect to the primary database, than the cascading standby database.”

We can use the cascaded standby database feature to say offload reporting from the primary database to the cascaded standby database (with or without Active Data Guard) or use the cascaded standby database along with the snapshot standby feature as a test environment in some cases where we need to test an urgent patch or fix and would like to do that using a database with real time production database and not some other test database.

This is the environment:

A) Primary Database (TESTDB1)
B) Physical Standby Database (TESTDB2)
C) Cascaded Standby Database (TESTDB3)

So this is how the redo log transport will happen:

TESTDB1 >>>> TESTDB2
TESTDB2 >>>> TESTDB3

The assumptions are:

• The Data Guard will be configured in Maximum Availability mode
• The backup location on the Primary server and Standby server is different
• The directory structure on the Standby server is not the same as the Primary server
• The Standby server will host both the Standby database as well as the Cascaded standby database
• The environment used in this example is Oracle database version 11.2.0.3 on OEL Linux 5.7
• The db_unique_name of the Primary database is testdb1 , the Standby database is testdb2 and the Cascaded Standby database is testdb3
• It is assumed that a recent RMAN backup of the Primary database and archivelogs is available on the Primary server. In this example it exists in the FRA.

Steps

Network Configuration

The tnsnames.ora file on both the Primary database server and Standby database serve have the entries :

TESTDB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb1)
)
)

TESTDB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb2)
)
)

TESTDB3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb3)
)
)

The listener.ora on the Standby database server has a static entry for testdb2 and testdb3

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=testdb2)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=testdb2)
)
(SID_DESC=
(GLOBAL_DBNAME=testdb3)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=testdb3)
)

)

On Primary take a backup of the current control file which will be used by the Standby database

RMAN> backup current controlfile for standby;

…….
…….
channel ORA_DISK_1: finished piece 1 at 06-MAY-13
piece handle=/u01/app/oracle/fast_recovery_area/TESTDB1/backupset/2013_05_06/o1_mf_ncnnf_TAG20130506T165754_8rgo3n7o_.bkp tag=TAG20130506T165754 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

………

Make a note of the backup piece name as we will be using this backup for the Standby database creation.

Take a backup of the database and archivelogs

RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile

RMAN> backup database plus archivelog ;

Starting backup at 06-MAY-13
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=814288291
input archived log thread=1 sequence=4 RECID=2 STAMP=814288349
input archived log thread=1 sequence=5 RECID=3 STAMP=814289179
input archived log thread=1 sequence=6 RECID=4 STAMP=814289262
input archived log thread=1 sequence=7 RECID=5 STAMP=814295555
input archived log thread=1 sequence=8 RECID=8 STAMP=814296764

……..

……….

input datafile file number=00002 name=/u01/app/oracle/oradata/testdb1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/testdb1/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/testdb1/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/testdb1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-MAY-13
channel ORA_DISK_1: finished piece 1 at 06-MAY-13
piece handle=/u01/app/oracle/fast_recovery_area/TESTDB1/backupset/2013_05_06/o1_mf_nnndf_TAG20130506T170129_8rgobbk1_.bkp tag=TAG20130506T170129 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
Finished backup at 06-MAY-13

…..

Copy the most recent database backup, archivelog backup and controlfile backup to the staging location on the Standby server

cd /u01/app/oracle/fast_recovery_area/TESTDB1/backupset/2013_05_06

[oracle@pdemvrhl061 2013_05_06]$ ls -l
total 1898088
-rw-r----- 1 oracle dba 684387840 May 6 17:01 o1_mf_annnn_TAG20130506T170053_8rgo964x_.bkp
-rw-r----- 1 oracle dba 64000 May 6 17:02 o1_mf_annnn_TAG20130506T170235_8rgodcsg_.bkp
-rw-r----- 1 oracle dba 9994240 May 6 17:09 o1_mf_ncnnf_TAG20130506T170934_8rgoshw2_.bkp
-rw-r----- 1 oracle dba 1247256576 May 6 17:02 o1_mf_nnndf_TAG20130506T170129_8rgobbk1_.bkp

scp -rp * oracle@host2:/home/oracle/backup

Copy password file from $ORACLE_HOME/dbs on Primary to $ORACLE_HOME/dbs on Standby

scp -rp orapwtestdb1 oracle@host2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwtestdb2

scp -rp orapwtestdb1 oracle@host2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwtestdb3

Copy init.ora file from $ORACLE_HOME/dbs on Primary to $ORACLE_HOME/dbs on Standby

scp –rp inittestdb1.ora oracle@host2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/inittestdb2.ora

scp –rp inittestdb1.ora oracle@host2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/inittestdb3.ora

Make the required changes to the Standby database (testdb2) init.ora file

These are the changes we have made to the init.ora which we have copied from the Primary database (testdb1) – the remaining parameters like sga_target, db_name, diagnostic_dest etc will be the same regardless if the database is a Primary database or Physical standby database.

Review particularly the entries related to redo transport like the log_archive_dest_* entries

*.audit_file_dest='/u01/app/oracle/admin/testdb2/adump'
*.control_files='/u01/app/oracle/oradata/testdb2/control01.ctl','/u01/app/oracle/oradata/testdb2/control02.ctl'
*.db_file_name_convert='/u01/app/oracle/oradata/testdb1','/u01/app/oracle/oradata/testdb2'
*.db_unique_name='testdb2'
*.fal_client='testdb2'
*.fal_server='testdb1'
*.log_archive_config='DG_CONFIG=(testdb1,testdb2,testdb3)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdb2'
*.log_archive_dest_2='SERVICE=testdb1 LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb1'
*.log_archive_dest_3='SERVICE=testdb3 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=testdb3'
*.log_file_name_convert='/u01/app/oracle/oradata/testdb1','/u01/app/oracle/oradata/testdb2'
*.service_names='testdb2'

Create the required directory structure on the Standby site

$ mkdir -p /u01/app/oracle/admin/testdb2/adump
$ mkdir -p /u01/app/oracle/admin/testdb3/adump
$ mkdir -p /u01/app/oracle/oradata/testdb1
$ mkdir -p /u01/app/oracle/oradata/testdb3
$ mkdir  -p /u01/app/oracle/fast_recovery_area

Add entries in /etc/oratab

 testdb2:/u01/app/oracle/product/11.2.0/dbhome_1:N
testdb3:/u01/app/oracle/product/11.2.0/dbhome_1:N

Start the Standby database instance in NOMOUNT state

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2228944 bytes
Variable Size             272633136 bytes
Database Buffers          134217728 bytes
Redo Buffers                8466432 bytes
SQL>

Restore the Standby Controlfile from the backup copied from Primary
 

RMAN> restore standby controlfile from '/home/oracle/backup/o1_mf_ncnnf_TAG20130506T170934_8rgoshw2_.bkp';

Starting restore at 06-MAY-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/testdb2/control01.ctl
output file name=/u01/app/oracle/oradata/testdb2/control02.ctl
Finished restore at 06-MAY-13

Mount the standby database (note in 11g we can just ‘ALTER DATABASE MOUNT’ command)

RMAN> sql 'alter database mount standby database';

sql statement: alter database mount standby database
released channel: ORA_DISK_1

Now catalog all the backup pieces which we have copied from Primary

RMAN> catalog start with '/home/oracle/backup';

searching for all files that match the pattern /home/oracle/backup

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup/o1_mf_annnn_TAG20130506T170235_8rgodcsg_.bkp
File Name: /home/oracle/backup/o1_mf_nnndf_TAG20130506T170129_8rgobbk1_.bkp
File Name: /home/oracle/backup/o1_mf_ncnnf_TAG20130506T170934_8rgoshw2_.bkp
File Name: /home/oracle/backup/o1_mf_annnn_TAG20130506T170053_8rgo964x_.bkp

Do you really want to catalog the above files (enter YES or NO)? y
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/backup/o1_mf_annnn_TAG20130506T170235_8rgodcsg_.bkp
File Name: /home/oracle/backup/o1_mf_nnndf_TAG20130506T170129_8rgobbk1_.bkp
File Name: /home/oracle/backup/o1_mf_ncnnf_TAG20130506T170934_8rgoshw2_.bkp
File Name: /home/oracle/backup/o1_mf_annnn_TAG20130506T170053_8rgo964x_.bkp

Restore the database –on the Standby database testdb2

Note the files are being restored in the new location:/u01/app/oracle/oradata/testdb2

RMAN> restore database;

Starting restore at 06-MAY-13
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/testdb2/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/testdb2/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/testdb2/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/testdb2/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/testdb2/example01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/o1_mf_nnndf_TAG20130506T170129_8rgobbk1_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/o1_mf_nnndf_TAG20130506T170129_8rgobbk1_.bkp tag=TAG20130506T170129
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46
Finished restore at 06-MAY-13

Note the last archived log sequence # which has been backed up

RMAN> list backup of archivelog all;

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
……
………

  List of Archived Logs in backup set 23
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    60      1602201    06-MAY-13 1604763    06-MAY-13
  1    61      1604763    06-MAY-13 1604769    06-MAY-13
  1    62      1604769    06-MAY-13 1629416    06-MAY-13
  1    63      1629416    06-MAY-13 1674083    07-MAY-13
  1    64      1674083    07-MAY-13 1694517    07-MAY-13
  1    65      1694517    07-MAY-13 1694574    07-MAY-13
  1    66      1694574    07-MAY-13 1694766    07-MAY-13

Recover the database – the SET UNTIL SEQUENCE will be the last archive log sequence backup available plus 1

RMAN> run
2> { set until sequence 67;
3> recover database;
4> }

executing command: SET until clause

Starting recover at 07-MAY-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=64
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=65
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=66
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/o1_mf_annnn_TAG20130507T141625_8rk00td3_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/o1_mf_annnn_TAG20130507T141625_8rk00td3_.bkp tag=TAG20130507T141625
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fast_recovery_area/TESTDB2/archivelog/2013_05_07/o1_mf_1_64_8rk04zb6_.arc thread=1 sequence=64
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/TESTDB2/archivelog/2013_05_07/o1_mf_1_64_8rk04zb6_.arc RECID=8 STAMP=814803519
archived log file name=/u01/app/oracle/fast_recovery_area/TESTDB2/archivelog/2013_05_07/o1_mf_1_65_8rk04zbz_.arc thread=1 sequence=65
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/TESTDB2/archivelog/2013_05_07/o1_mf_1_65_8rk04zbz_.arc RECID=7 STAMP=814803519
archived log file name=/u01/app/oracle/fast_recovery_area/TESTDB2/archivelog/2013_05_07/o1_mf_1_66_8rk04zbp_.arc thread=1 sequence=66
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/TESTDB2/archivelog/2013_05_07/o1_mf_1_66_8rk04zbp_.arc RECID=6 STAMP=814803519
media recovery complete, elapsed time: 00:00:01
Finished recover at 07-MAY-13

Now we need to perform the same steps for the Cascaded Standby database testdb3

We make the following changes in the init.ora file which we have copied from the Primary database:

 *.audit_file_dest=’/u01/app/oracle/admin/testdb3/adump’
*.control_files=’/u01/app/oracle/oradata/testdb3/control01.ctl’,’/u01/app/oracle/oradata/testdb3/control02.ctl’
*.db_file_name_convert=’/u01/app/oracle/oradata/testdb1′,’/u01/app/oracle/oradata/testdb3′
*.log_file_name_convert=’/u01/app/oracle/oradata/testdb1′,’/u01/app/oracle/oradata/testdb3′
*.db_unique_name=’testdb3′
*.fal_client=’testdb3′
*.fal_server=’testdb2′
*.log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdb3′
*.service_names=’testdb3′
*.LOG_ARCHIVE_CONFIG=’DG_CONFIG=(testdb1,testdb2,testdb3)’

•  Set the enevironment for the database testdb3
• Startup NOMOUNT the database
• Restore the backup of the controlfile taken from the Primary database
• Mount the database
• Restore the database
• Recover the database until the same archived log sequence number we used for the earlier standby database testdb2.

Add the Standby Redo Log Files

Create the Standby redo log files on all the three databases. Note that we use the same size as the redo log files and create one additional group in case of the standby redo log files as compared to the online redo log files.

For example:

SQL> alter database add standby logfile '/u01/app/oracle/oradata/testdb1/standby_redo01.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/testdb1/standby_redo02.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/testdb1/standby_redo03.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/testdb1/standby_redo04.log' size 50m;

Database altered.

Open both the Standby Database as well as the Cascaded standby database and put them in managed recovery mode

SQL> alter database open;

Database altered.

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.

Configure redo transport for the Primary Database

We add the following entries in the init.ora of the Parimary database

 *.fal_client=’testdb1′
*.fal_server=’testdb2′
*.log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=testdb1′
*.log_archive_dest_2=’SERVICE=testdb2 LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb2′
*.log_archive_dest_3=’SERVICE=testdb3  VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=testdb3′;
*.log_archive_dest_state_3=’DEFER’
*.standby_file_management=’AUTO’

Note that we have set parameter log_archive_dest_state_3 to DEFER because in normal operation, the Primary database testdb2 will only ship redo logs to the standby database testdb2 and NOT the cascaded standby database testdb3.

When the current Primary database testdb1 assumes the role of a standby database at some time in the future when a switchover happens, then only we need to enable the log shipping from testdb1 to testdb3.

Change the protection mode to MAXIMUM AVAILABILITY

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size                  2233000 bytes
Variable Size             494931288 bytes
Database Buffers          335544320 bytes
Redo Buffers                6574080 bytes
Database mounted.

SQL> alter database set standby database to maximize availability;

Database altered.

SQL> alter database open;

Database altered.

SQL> select  PROTECTION_MODE,PROTECTION_LEVEL from v$database;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

Lets Test!

Primary Database TESTDB1
 

SQL> update customers
  2  set cust_first_name=’Smith’
  3   where rownum=1;

1 row updated.

SQL> commit;

Commit complete.

Standby Database/Cascading Standby TESTDB2
 

SQL> select cust_first_name from customers where rownum=1;

CUST_FIRST_NAME
——————–
Smith

Cascaded Standby Database TESTDB3

At this stage the cascaded standby database is lagging behind the Primary as well as the cascading Standby database because the changes from the TESTDB2 will only be cascaded to TESTDB3 when a log switch is triggered when the archive redo log file fills up

SQL> select cust_first_name from customers where rownum=1;

CUST_FIRST_NAME
--------------------
Sachin

Primary Database TESTDB1

SQL> conn / as sysdba
Connected.

SQL> alter system switch logfile;

System altered.

Cascaded Standby Database TESTDB3

SQL> select cust_first_name from customers where rownum=1;

CUST_FIRST_NAME
--------------------
Smith