Cascading Standby Database works in two layers.

  1. As per normal the standby configuration, standby database receives redo from the primary database.
  2. In Layer 2 standby database will receive redo from another standby database rather than directly from the primary database.

With Cascading standby database we can minimize the load of Primary Database.

As per Oracle Docs, A cascading standby database is a standby database that receives its redo logs from another standby database, not from the original primary database.

In this post, we will first create a regular Physical Standby database. And then we will create a Cascaded Standby Database.

Database Details :

Primary DB_UNIQUE_NAME Standby DB_UNIQUE_NAME Cascading Standby DB_UNIQUE_NAME
CASDB STD_CASDB STD1_CASDB

1.Physical Standby Database creation.

Step 1: Check the details of a primary database.

[oracle@localhost ~]$ export ORACLE_SID=casdb
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 3 12:04:55 2018

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

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

NAME	  OPEN_MODE	       DATABASE_ROLE
--------- -------------------- ----------------
CASDB	  READ WRITE	       PRIMARY

Step 2: Check if force_logging is enabled or not at the primary database.

SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

FOR
---
NO

ENABLE FORCE LOGGING

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

Step 3: Check if password file exists

[oracle@localhost dbs]$ ls -ltr orapwcasdb
-rw-r----- 1 oracle oinstall 1536 Jul  3 12:00 orapwcasdb

Step 4: Check log_mode

SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE
------------
ARCHIVELOG

If archiving is not enabled, enable it.

Step 5: Configure standby logfiles

SQL> alter database add standby logfile '/u01/oracle/oradata/std_redo01.log' size 51m;

Database altered.

SQL> alter database add standby logfile '/u01/oracle/oradata/std_redo02.log'  size 51m;

Database altered.

SQL> alter database add standby logfile '/u01/oracle/oradata/std_redo03.log' size 51m;

Database altered.

SQL> alter database add standby logfile '/u01/oracle/oradata/std_redo04.log' size 51m;

Database altered.

Step 6: Setting initialization parameters on primary :

SQL> ALTER SYSTEM SET LOG_ARCHIVE_dEST_2='SERVICE=STD_CASDB LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STD_CASDB';

System altered.

For now , set log_archive_dest_state_2 to defer.

SQL> ALTER SYSTEM SET LOG_aRCHIVE_DEST_STATE_2=DEFER;

System altered.

Step 7: set FAL_CLIENT and FAL_SERVER

SQL> ALTER SYSTEM SET FAL_CLIENT=CASDB;

System altered.

SQL> ALTER SYSTEM SET FAL_SERVER=STD_CASDB;

System altered.

Step 8: Set STANDBY_FILE_MANAGEMENT to auto

SQL> SHOW PARAMETER STANDBY_FILE_MANAGEMENT

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
standby_file_management 	     string	 MANUAL
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

Step 9: Set log_archive_config

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(CASDB,STD_CASDB)';

System altered.

Step 10: Configure listener.ora and tnsnames.ora file

Primary tnsnames.ora

CASDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = casdb)(UR=A)
    )
  )

STD_casdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = casdb)(UR=A)
    )
  )

Step 11:  We need the static entry for listener at standby side

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER=
         (SID_LIST=
                (SID_DESC=
                   (ORACLE_HOME=/u01/oracle/product/11.2.0/db_1)
                (SID_NAME=casdb))
         )

Step 12: Standby tnsnames.ora

CASDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.15)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = casdb)(UR=A)
    )
  )
 
STD_casdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = casdb)(UR=A)
    )
  )

Step 13: Start listener on the standby database.

Step 14: Create pfile from spfile in the primary database.

SQL> CREATE PFILE FROM SPFILE;

File created.

Step 15: Copy password file and init file to standby server.

[oracle@localhost dbs]$ scp initcasdb.ora 192.168.1.10:$ORACLE_HOME/dbs/
oracle@192.168.1.10's password: 
initcasdb.ora                                                                                                                                                100% 1260     1.2KB/s   00:00    
[oracle@localhost dbs]$ scp orapwcasdb 192.168.1.10:$ORACLE_HOME/dbs
oracle@192.168.1.10's password: 
orapwcasdb

Step 16: Create the necessary directory on the standby

[oracle@localhost dbs]$ mkdir -p /u01/oracle/admin/casdb/adump
[oracle@localhost dbs]$ mkdir -p /u01/oracle/oradata/casdb/standbylog
[oracle@localhost dbs]$ mkdir -p /u01/arc/casdb

Step 17: Change DB_UNIQUE_NAME to std_casdb and Start the standby database in nomount mode.

[oracle@localhost dbs]$ export ORACLE_SID=casdb
[oracle@localhost dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 3 13:02:58 2018

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size		    2254824 bytes
Variable Size		  377489432 bytes
Database Buffers	  138412032 bytes
Redo Buffers		    3780608 bytes
SQL>

Step 18: create a standby database using rman duplicate database from active database command.

[oracle@localhost dbs]$ rman target sys/oracle@casdb auxiliary sys/oracle@std_casdb

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jul 3 13:04:51 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CASDB (DBID=2549523612)
connected to auxiliary database: CASDB (not mounted)

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

Starting Duplicate Db at 03-JUL-18
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/oracle/product/11.2.0/db_1/dbs/orapwcasdb' auxiliary format 
 '/u01/oracle/product/11.2.0/db_1/dbs/orapwcasdb'   ;
}
executing Memory Script

Starting backup at 03-JUL-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 device type=DISK
Finished backup at 03-JUL-18

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/oracle/oradata/casdb/control01.ctl';
   restore clone controlfile to  '/u01/oracle/oradata/casdb/control02.ctl' from 
 '/u01/oracle/oradata/casdb/control01.ctl';
}
executing Memory Script

Starting backup at 03-JUL-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/oracle/product/11.2.0/db_1/dbs/snapcf_casdb.f tag=TAG20180703T130519 RECID=1 STAMP=980514321
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 03-JUL-18

Starting restore at 03-JUL-18
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 03-JUL-18

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/u01/oracle/oradata/casdb/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/u01/oracle/oradata/casdb/system01.dbf";
   set newname for datafile  2 to 
 "/u01/oracle/oradata/casdb/sysaux01.dbf";
   set newname for datafile  3 to 
 "/u01/oracle/oradata/casdb/undotbs01.dbf";
   set newname for datafile  4 to 
 "/u01/oracle/oradata/casdb/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format 
 "/u01/oracle/oradata/casdb/system01.dbf"   datafile 
 2 auxiliary format 
 "/u01/oracle/oradata/casdb/sysaux01.dbf"   datafile 
 3 auxiliary format 
 "/u01/oracle/oradata/casdb/undotbs01.dbf"   datafile 
 4 auxiliary format 
 "/u01/oracle/oradata/casdb/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/oracle/oradata/casdb/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 03-JUL-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/oracle/oradata/casdb/system01.dbf
output file name=/u01/oracle/oradata/casdb/system01.dbf tag=TAG20180703T130530
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/oracle/oradata/casdb/sysaux01.dbf
output file name=/u01/oracle/oradata/casdb/sysaux01.dbf tag=TAG20180703T130530
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/oracle/oradata/casdb/undotbs01.dbf
output file name=/u01/oracle/oradata/casdb/undotbs01.dbf tag=TAG20180703T130530
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/oracle/oradata/casdb/users01.dbf
output file name=/u01/oracle/oradata/casdb/users01.dbf tag=TAG20180703T130530
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 03-JUL-18

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=980514428 file name=/u01/oracle/oradata/casdb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=980514428 file name=/u01/oracle/oradata/casdb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=980514428 file name=/u01/oracle/oradata/casdb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=980514428 file name=/u01/oracle/oradata/casdb/users01.dbf
Finished Duplicate Db at 03-JUL-18

RMAN>

Step 19 : Now change log_archive_dest_state_2 to ENABLE.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

System altered.

Step 20: Start recovery in the Physical standby database.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.
SQL> select status, error from v$archive_dest where dest_id=2;

STATUS	  ERROR
--------- -----------------------------------------------------------------
VALID

2. Preparing for Cascading Standby database.

Step 1: Set log_archive_config parameter to accommodate all standby and primary including your cascading standby database.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(CASDB,STD_CASDB,STD1_CASDB)';

System altered.
SQL> alter system set log_archive_dest_state_3=defer;

System altered.

Step 2: Create a tnsnames.ora entries in the cascaded standby database.

STD_casdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = casdb)(UR=A)
    )
  )
STD1_casdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = casdb)(UR=A)
    )
  )

Step 3: Tns entry at physical standby.

STD1_casdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.16)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = casdb)(UR=A)
    )
  )

Step 4: Copy password file and pfile to cascading standby.

[oracle@localhost dbs]$ scp initcasdb.ora 192.168.1.16:$ORACLE_HOME/dbs/
oracle@192.168.1.16's password: 
initcasdb.ora                                                                                                                                                100% 1260     1.2KB/s   00:00    
[oracle@localhost dbs]$ scp orapwcasdb 192.168.1.16:$ORACLE_HOME/dbs
oracle@192.168.1.16's password: 
orapwcasdb

Step 5: Stop recovery at a standby database

SQL> recover managed standby database cancel;
Media recovery complete.

Step 7: Here I am renaming log files to new accommodate in oracle/oradata location.

SQL> alter system set standby_file_management=manual;

System altered.

SQL> !cp /u01/oracle/fast_recovery_area/STD_CASDB/onlinelog/o1_mf_1_fmp9x59h_.log /u01/oracle/oradata/casdb/redo01.log;

SQL> alter database rename file '/u01/oracle/fast_recovery_area/STD_CASDB/onlinelog/o1_mf_1_fmp9x59h_.log' to '/u01/oracle/oradata/casdb/redo01.log';

Database altered.

SQL> !cp /u01/oracle/fast_recovery_area/STD_CASDB/onlinelog/o1_mf_2_fmp9x6jd_.log /u01/oracle/oradata/casdb/redo02.log;

SQL> alter database rename file '/u01/oracle/fast_recovery_area/STD_CASDB/onlinelog/o1_mf_2_fmp9x6jd_.log' to '/u01/oracle/oradata/casdb/redo02.log';

Database altered.

SQL> !cp /u01/oracle/fast_recovery_area/STD_CASDB/onlinelog/o1_mf_3_fmp9x8bo_.log /u01/oracle/oradata/casdb/redo03.log

SQL> alter database rename file '/u01/oracle/fast_recovery_area/STD_CASDB/onlinelog/o1_mf_3_fmp9x8bo_.log' to '/u01/oracle/oradata/casdb/redo03.log';

Database altered.
SQL> !cp /u01/oracle/fast_recovery_area/STD_CASDB/onlinelog/o1_mf_4_fmp9xbgy_.log /u01/oracle/oradata/casdb/std_redo01.log
SQL> alter database rename file '/u01/oracle/fast_recovery_area/STD_CASDB/onlinelog/o1_mf_4_fmp9xbgy_.log' to '/u01/oracle/oradata/casdb/std_redo01.log';
Database altered.
SQL> !cp /u01/oracle/fast_recovery_area/STD_CASDB/onlinelog/o1_mf_5_fmp9xd99_.log /u01/oracle/oradata/casdb/std_redo0.log

SQL> alter database rename file '/u01/oracle/fast_recovery_area/STD_CASDB/onlinelog/o1_mf_5_fmp9xd99_.log' to '/u01/oracle/oradata/casdb/std_redo02.log';

Database altered.
SQL> !cp /u01/oracle/fast_recovery_area/STD_CASDB/onlinelog/o1_mf_6_fmp9xfwp_.log /u01/oracle/oradata/casdb/std_redo03.log
SQL> alter database rename file '/u01/oracle/fast_recovery_area/STD_CASDB/onlinelog/o1_mf_6_fmp9xfwp_.log' to 'u01/oracle/oradata/casdb/std_redo03.log
';
Database altered.

SQL> !cp /u01/oracle/fast_recovery_area/STD_CASDB/onlinelog/o1_mf_7_fmp9xj3f_.log /u01/oracle/oradata/casdb/std_redo04.log
SQL> alter database rename file '/u01/oracle/fast_recovery_area/STD_CASDB/onlinelog/o1_mf_7_fmp9xj3f_.log' to '/u01/oracle/oradata/casdb/std_redo04.log';
Database altered.

Step 6: We need to transfer files to cascading standby database shown from the v$datafile, v$logfile, and v$controlfile.

SQL> SELECT MEMBER FROM V$LOGFILE;

MEMBER
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/oracle/oradata/casdb/redo03.log
/u01/oracle/oradata/casdb/redo02.log
/u01/oracle/oradata/casdb/redo01.log
/u01/oracle/oradata/casdb/std_redo01.log
/u01/oracle/oradata/casdb/std_redo02.log
/u01/oracle/oradata/casdb/std_redo03.log
/u01/oracle/oradata/casdb/std_redo04.log

7 rows selected.

SQL> SELECT NAME FROM V$CONTROLFILE;

NAME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/oracle/oradata/casdb/control01.ctl
/u01/oracle/oradata/casdb/control02.ctl

SQL> SELECT NAME FROM V$DATAFILE;

NAME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/oracle/oradata/casdb/system01.dbf
/u01/oracle/oradata/casdb/sysaux01.dbf
/u01/oracle/oradata/casdb/undotbs01.dbf
/u01/oracle/oradata/casdb/users01.dbf

SQL>

Step 7: Create the necessary directory on cascading standby

[oracle@test1 dbs]$ mkdir -p /u01/arc/casdb
[oracle@test1 dbs]$ mkdir -p /u01/oracle/oradata/casdb/standbylog
[oracle@test1 dbs]$ mkdir -p /u01/oracle/admin/casdb/adump

Step 8: use RSYNC to copy all datafile, controlfile, and logfiles.

[oracle@localhost ~]$ rsync -azvrh --progress /u01/oracle/oradata/casdb/ 192.168.1.16:/u01/oracle/oradata/casdb/
oracle@192.168.1.16's password: 
sending incremental file list
./
control01.ctl
       9.75M 100%   41.36MB/s    0:00:00 (xfer#1, to-check=15/17)
control02.ctl
       9.75M 100%   10.80MB/s    0:00:00 (xfer#2, to-check=14/17)
redo01.log
      53.48M 100%   22.44MB/s    0:00:02 (xfer#3, to-check=13/17)
redo02.log
      53.48M 100%   30.54MB/s    0:00:01 (xfer#4, to-check=12/17)
redo03.log
      53.48M 100%   24.34MB/s    0:00:02 (xfer#5, to-check=11/17)
redo04.log
      53.48M 100%   28.98MB/s    0:00:01 (xfer#6, to-check=10/17)
std_redo01.log
      53.48M 100%   22.35MB/s    0:00:02 (xfer#7, to-check=9/17)
std_redo02.log
      53.48M 100%   28.30MB/s    0:00:01 (xfer#8, to-check=8/17)
std_redo03.log
      53.48M 100%   21.90MB/s    0:00:02 (xfer#9, to-check=7/17)
std_redo04.log
      53.48M 100%   28.93MB/s    0:00:01 (xfer#10, to-check=6/17)
sysaux01.dbf
     545.27M 100%   12.04MB/s    0:00:43 (xfer#11, to-check=5/17)
system01.dbf
     775.95M 100%    8.24MB/s    0:01:29 (xfer#12, to-check=4/17)
temp01.dbf
      20.98M 100%   39.46MB/s    0:00:00 (xfer#13, to-check=3/17)
undotbs01.dbf
      52.44M 100%    8.84MB/s    0:00:05 (xfer#14, to-check=2/17)
users01.dbf
       5.25M 100%    6.35MB/s    0:00:00 (xfer#15, to-check=1/17)
standbylog/

sent 271.51M bytes  received 304 bytes  1.70M bytes/sec
total size is 1.85G  speedup is 6.80
[oracle@localhost ~]$

Step 9 : Change log_archive_dest_state_3 to ENABLE.

SQL> alter system set log_archive_dest_state_3=enable;

System altered.

Step 10: Change db_unique_name parameter std1_casdb in pfile and start the database in mount stage

[oracle@test1 casdb]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 3 14:28:15 2018

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

Connected to an idle instance.

SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size		    2254824 bytes
Variable Size		  381683736 bytes
Database Buffers	  134217728 bytes
Redo Buffers		    3780608 bytes
Database mounted.
SQL> select name,open_mode,database_role,db_unique_name from v$database;

NAME	  OPEN_MODE	       DATABASE_ROLE	DB_UNIQUE_NAME
--------- -------------------- ---------------- ------------------------------
CASDB	  MOUNTED	       PHYSICAL STANDBY std1_CASDB

SQL>

Step 11: Start recovery on physical standby

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

Step 12: Start recovery on cascaded standby

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

Check both standby layer 1 database and cascading standby database are getting redo transport.

Stay tuned for More articles on Oracle DataGuard

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.