Cascading Standby Database in 11g
This article demonstrates on the steps involved in configuring a cascaded physical standby database. A cascading standby environment is one wherein a standby receives the redo data directly from the primary database and then transmits it to the other cascaded standby databases.
This method offloads the primary in transmitting the redo to multiple standbys, instead it just transmits to the single cascading standby which in turn cascades to multiple standbys. Also, the redo received by the cascading physical standby database can cascade it to upto 30 physical or logical or snapshot standbys.
After speaking about the advantages of “Cascaded Standby Databases”, it also has certain restrictions:
1. A cascading environment cannot be used if the primary database is a RAC configuration. (This restriction has been removed from 11.2.0.2)
2. If dataguard broker is configured, then cascading redo to other standbys is not allowed.
3. A logical standby database or a snapshot standby database cannot act as a Cascading standby database. In other words, a logical or a snapshot standby cannot retransmit the redo data to a cascaded standby database.
4. Since the redo data on a cascaded standby is received from a cascading standby database and not directly from the primary database, there is always a lag at the cascaded standby database. The redo will be received only when the current redo on the cascading standby will be archived.
To minimize the lag, Standby Redo Logs are needed while implementing Cascaded Standbys to make use of the Real Time Apply feature.
Let’s move on with the steps involved in achieving this. This primary database used here is a standalone database with OMF managed datafiles through ASM and the cascading as well as cascaded standbys too are standalone databases with OMF managed datafiles through ASM. Also, this post assumes that a physical standby is setup for the primary which would be acting as a cascading standby and the standby redo logs are configured on both primary and the cascading standby database and it’s creation is not outlined here.
Environment:
Primary Site:
DB Name : srprim DB Unique name : srprim Hostname : ora1-1
Cascading Site:
DB Name : srprim DB Unique Name : srpstb Hostname : ora1-2
Cascaded Site:
DB Name : srprim DB Unique Name : srcstb Hostname : ora1-3
The configuration uses “Flash Recovery Area” on all (primary, cascading and cascaded standby databases). To graphically help you understand, the cascaded environment looks as below:
SRPRIM —– > SRPSTB —– > SRCSTB
Create a pfile for the cascaded database “srcstb” with all the required parameters. Below is how the PFILE for the “srcstb” instance looks.
[oracle@ora1-3 ~]$ cat /u02/initsrcstb.ora srcstb.__db_cache_size=381681664 srcstb.__java_pool_size=4194304 srcstb.__large_pool_size=4194304 srcstb.__pga_aggregate_target=381681664 srcstb.__sga_target=566231040 srcstb.__shared_io_pool_size=0 srcstb.__shared_pool_size=159383552 srcstb.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/srcstb/adump' *.audit_trail='DB' *.compatible='11.2.0.0.0' *.control_files='+DATA1','+FRA1' *.core_dump_dest='/u01/app/oracle/diag/rdbms/srcstb/srpstb/cdump' *.db_block_size=8192 *.db_create_file_dest='+DATA1' *.db_domain='' *.db_file_name_convert='+DATA_NEW','+DATA1' *.db_name='srprim' *.db_recovery_file_dest='+FRA1' *.db_recovery_file_dest_size=4032M *.db_unique_name='srcstb' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=srcstbXDB)' *.fal_server='srprim' *.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=srcstb' *.log_buffer=5820416# log buffer update *.log_file_name_convert='+FRA_NEW','+FRA1' *.memory_target=904M *.open_cursors=300 *.optimizer_dynamic_sampling=2 *.optimizer_mode='ALL_ROWS' *.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora *.processes=150 *.query_rewrite_enabled='TRUE' *.remote_login_passwordfile='EXCLUSIVE' *.result_cache_max_size=2336K *.skip_unusable_indexes=TRUE
Make sure that the TNS entries for SRPRIM, SRPSTB and SRCSTB exist on each of the 3 (ora1-1, ora1-2 and ora1-3) servers.
SRPRIM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora1-1.mydomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = srprim)
)
)
SRPSTB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora1-2.mydomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = srpstb)
)
)
SRCSTB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora1-3.mydomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = srcstb)
)
)
Add a static entry about the details of SRCSTB instance to the listener.ora file on the cascaded standby host “ora1-3”.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.2/db1)
(SID_NAME = srcstb)
)
)
Copy the password file from “srpstb” to the cascading standby site “ora1-3” and rename it according to the cascading standby database name “srcstb”.
[oracle@ora1-2 ~]$ scp /u01/app/oracle/product/11.2.0.2/db_1/dbs/orapwsrpstb oracle@ora1-3:/u01/app/oracle/product/11.2.0.2/db1/dbs/orapwsrcstb The authenticity of host 'ora1-3 (192.168.56.107)' can't be established. RSA key fingerprint is 26:01:48:55:e9:ae:ae:9a:f9:fd:38:db:29:b7:fa:4e. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'ora1-3' (RSA) to the list of known hosts. oracle@ora1-3's password: orapwsrpstb 100% 1536 1.5KB/s 00:00 [oracle@ora1-2 ~]$
Start the instance “srcstb” in nomount stage using the PFILE created earlier.
[oracle@ora1-3 ~]$ sqlplus sys/oracle@srcstb as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Sat Aug 8 10:04:49 2015 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to an idle instance. SYS@srcstb> startup nomount ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 943669248 bytes Fixed Size 2232128 bytes Variable Size 553648320 bytes Database Buffers 381681664 bytes Redo Buffers 6107136 bytes
Current settings of log shipping parameters on my primary database “srprim” is as :
SYS@srprim> select name,value from v$parameter where name in ('log_archive_dest_1','log_archive_dest_2');
NAME VALUE
--------------------- -----------------------------------------------------------------------
log_archive_dest_1 location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) d b_unique_name=srprim
log_archive_dest_2 service="srpstb", LGWR SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="srpstb" net_ t imeout=30, valid_for=(all_logfiles,primary_role)
It ships redo data only to the cascading standby database “srpstb”. Also, on “srpstb”, there is no redo shipment parameter configured to ship it to “srcstb”.
The details are as below for “srpstb” instance.
[oracle@ora1-2 ~]$ sqlplus sys/oracle@srpstb as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Sat Aug 8 10:06:32 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SYS@srpstb> select status,instance_name,database_role from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE
------------ ---------------- ----------------
MOUNTED srpstb PHYSICAL STANDBY
SYS@srpstb> select name,value from v$parameter where name in ('log_archive_dest_1','log_archive_dest_2');
NAME VALUE
------------------- -------------------------------------------------------------------------
log_archive_dest_1 location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_ unique_name=srpstb
log_archive_dest_2 service=srprim SYNC valid_for=(all_logfiles,primary_role) db_unique_name= srprim
SYS@srpstb> select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# --------- ------------ ---------- ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CLOSING 90 RFS IDLE 0 RFS IDLE 0 RFS IDLE 0 RFS IDLE 91 MRP0 WAIT_FOR_LOG 91 9 rows selected.
It can be noticed above that, MRP is currently running on “srpstb” waiting for log sequence 91.
Now, create the standby database “srcstb” using RMAN “active” duplication method. Note that, the duplication is being carried out with “srpstb” as a target database. In other words, I’m using a standby database as a source to perform RMAN active duplicate to create a new standby. This option is available from 11.2.0.2 version, provided there are pre-requisties patches that need to be applied on both source and target environment.
If RMAN active duplicate with source as standby is not feasible or un-supported for your version, then you can go with “RMAN active duplicate” from primary database or use the traditional way of RMAN backup restore and recovery to create the cascaded standby “srcstb”.
[oracle@ora1-3 dbs]$ rman target sys/oracle@srpstb auxiliary sys/oracle@srcstb
Recovery Manager: Release 11.2.0.2.0 - Production on Sun Aug 16 11:46:55 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: SRPRIM (DBID=298418015, not open)
connected to auxiliary database: SRPRIM (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck;
Starting Duplicate Db at 16-AUG-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=24 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0.2/db_1/dbs/orapwsrpstb' auxiliary format
'/u01/app/oracle/product/11.2.0.2/db1/dbs/orapwsrcstb' ;
}
executing Memory Script
Starting backup at 16-AUG-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 device type=DISK
Finished backup at 16-AUG-15
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '+DATA1/srcstb/controlfile/current.267.887888833';
restore clone controlfile to '+FRA1/srcstb/controlfile/current.267.887888835' from
'+DATA1/srcstb/controlfile/current.267.887888833';
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''+DATA1/srcstb/controlfile/current.267.887888833'', ''+FRA1/srcstb/controlfile/current.267.887888835'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Starting backup at 16-AUG-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0.2/db_1/dbs/snapcf_srpstb.f tag=TAG20150816T114714 RECID=18 STAMP=887888836
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 16-AUG-15
Starting restore at 16-AUG-15
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 16-AUG-15
sql statement: create spfile from memory
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 943669248 bytes
Fixed Size 2232128 bytes
Variable Size 553648320 bytes
Database Buffers 381681664 bytes
Redo Buffers 6107136 bytes
sql statement: alter system set control_files = ''+DATA1/srcstb/controlfile/current.267.887888833'', ''+FRA1/srcstb/controlfile/current.267.887888835'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 943669248 bytes
Fixed Size 2232128 bytes
Variable Size 553648320 bytes
Database Buffers 381681664 bytes
Redo Buffers 6107136 bytes
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
set newname for tempfile 1 to
"+data1";
set newname for tempfile 2 to
"+data1";
switch clone tempfile all;
set newname for datafile 1 to
"+data1";
set newname for datafile 2 to
"+data1";
set newname for datafile 3 to
"+data1";
set newname for datafile 4 to
"+data1";
backup as copy reuse
datafile 1 auxiliary format
"+data1" datafile
2 auxiliary format
"+data1" datafile
3 auxiliary format
"+data1" datafile
4 auxiliary format
"+data1" ;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to +data1 in control file
renamed tempfile 2 to +data1 in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 16-AUG-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA_NEW/srpstb/datafile/system.258.882637005
output file name=+DATA1/srcstb/datafile/system.276.887888881 tag=TAG20150816T114801
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA_NEW/srpstb/datafile/sysaux.259.882637031
output file name=+DATA1/srcstb/datafile/sysaux.275.887888917 tag=TAG20150816T114801
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA_NEW/srpstb/datafile/undotbs1.260.882637057
output file name=+DATA1/srcstb/datafile/undotbs1.274.887888941 tag=TAG20150816T114801
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA_NEW/srpstb/datafile/users.261.882637061
output file name=+DATA1/srcstb/datafile/users.273.887888945 tag=TAG20150816T114801
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 16-AUG-15
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=18 STAMP=887888946 file name=+DATA1/srcstb/datafile/system.276.887888881
datafile 2 switched to datafile copy
input datafile copy RECID=19 STAMP=887888946 file name=+DATA1/srcstb/datafile/sysaux.275.887888917
datafile 3 switched to datafile copy
input datafile copy RECID=20 STAMP=887888946 file name=+DATA1/srcstb/datafile/undotbs1.274.887888941
datafile 4 switched to datafile copy
input datafile copy RECID=21 STAMP=887888946 file name=+DATA1/srcstb/datafile/users.273.887888945
Finished Duplicate Db at 16-AUG-15
RMAN> exit
Recovery Manager complete.
[oracle@ora1-3 dbs]$
Now that the standby is created, set the log shipping parameters on the primary and the cascading standby databases accordingly.
On the primary, I set “log_archive_dest_3” parameter to ship the redo to “srcstb”. Of-course, the “log_archive_dest_state_3” would be deffered as I do not want the primary to ship the redo to the cascaded standby.
By doing so, the concept of “cascaded standby” does not come into picture.
I set “log_archive_dest_3” on primary to ship the redo to “srcstb”, so that, if in case, “srpstb” goes down or is unreachable, then there wouldn’t be any DR database. So, as an alternate option, I have set this parameter.
[oracle@ora1-1 admin]$ sqlplus sys/oracle@srprim as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Sun Aug 16 12:16:14 2015 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SYS@srprim> sho parameter dest_3 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_online_log_dest_3 string log_archive_dest_3 string log_archive_dest_30 string log_archive_dest_31 string SYS@srprim> SYS@srprim> alter system set log_archive_dest_3='service=srcstb valid_for=(standby_logfiles,standby_role) db_unique_name=srcstb'; System altered. SYS@srprim> alter system set log_archive_dest_state_3=defer; System altered.
SYS@srprim> sho parameter log_archive_dest_3
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string service=srcstb valid_for=(onli
ne_logfiles,primary_role) db_u
nique_name=srcstb
log_archive_dest_30 string
log_archive_dest_31 string
SYS@srprim>
SYS@srprim>
SYS@srprim> sho parameter dest_state_3
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_3 string DEFER
log_archive_dest_state_30 string enable
log_archive_dest_state_31 string enable
SYS@srprim>
Set the log_archive_config parameter on all the 3 databases to host the value of all 3 database unique names.
SYS@srprim> alter system set log_archive_config='DG_CONFIG=(srprim,srpstb,srcstb)';
System altered.
SYS@srprim> sho parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG=(srprim,srpstb,srcst
b)
On the cascading standby database “srpstb”, set the parameter “log_archive_dest_3” to ship the redo data to the cascaded standby database “srcstb”.
[oracle@ora1-2 admin]$ sqlplus sys/oracle@srpstb as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Sun Aug 16 12:20:20 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SYS@srpstb> sho parameter dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_2 string
log_archive_dest_2 string service=srprim val id_for=(online_logfiles,primary_role) db_u
nique_name=srprim
SYS@srpstb> show parameter dest_3
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_3 string
log_archive_dest_3 string
SYS@srpstb>
SYS@srpstb> alter system set log_archive_dest_3='service=srcstb valid_for=(standby_logfiles,standby_role) db_unique_name=srcstb';
System altered.
SYS@srpstb> alter system set log_archive_config='DG_CONFIG=(srprim,srpstb,srcstb)';
System altered.
On “srcstb”, set the log_archive_config parameter and “fal_server” parameter.
FAL_SERVER on “srcstb” should use the NET alias name of “srpstb” as it would be receiving the redo data from “srpstb” database.
[oracle@ora1-3 dbs]$ sqlplus sys/oracle@srcstb as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Sun Aug 16 12:14:58 2015 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SYS@srcstb> SYS@srcstb> select status,instance_name,database_role,open_mode from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ------------ ---------------- ---------------- -------------------- MOUNTED srcstb PHYSICAL STANDBY MOUNTED SYS@srcstb> alter system set log_archive_config='DG_CONFIG=(srprim,srpstb,srcstb)'; System altered. SYS@srcstb> alter system set fal_server='srpstb'; System altered. SYS@srcstb> alter system set fal_client='srcstb'; System altered.
In this environment, RMAN automatically creates the Standby Redo logs after the duplication as they were configured previously and existed on the primary and cascading standby databases. Verify there existance on the cascaded standby and if in case, do not exist, then create them manually.
Start the Managed Recovery Process on the cascaded standby database “srcstb”.
SYS@srcstb> alter database recover managed standby database disconnect from session using current logfile; Database altered. SYS@srcstb> select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# --------- ------------ ---------- ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CLOSING 215 MRP0 WAIT_FOR_LOG 217 RFS IDLE 0 RFS IDLE 0 RFS IDLE 0 8 rows selected.
Verify the sync status of cascading standby “srpstb” and cascaded standby “srcstb” databases.
On the primary, the latest archive sequence is generated is 226:
SYS@srprim> select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE ------------ ---------------- ---------------- OPEN srprim PRIMARY SYS@srprim> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 226
On the cascading standby SRPSTB, the last sequence applied is 226.
SYS@srpstb> select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE ------------ ---------------- ---------------- MOUNTED srpstb PHYSICAL STANDBY SYS@srpstb> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 226
Similarly, on the cascaded standby SRCSTB, the last sequence generated is 225.
SYS@srcstb> select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE ------------ ---------------- ---------------- MOUNTED srcstb PHYSICAL STANDBY SYS@srcstb> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 225
COPYRIGHT
© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.
DISCLAIMER
The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.
Shivananda Rao 
Hello Shiv ,
I have cascade standby in our environment for two node rac. scenario is like that –
two node rac primary –> two node rac standby –> standalone standby (cascade)
IS cascade standby for two node rac is a standalone is possible?
Hi Sachin, if you are using 11.2.0.2 or a higher version, then the restriction has been removed. You can have a cascading standby built for a RAC primary and is supported irrespective of whether your cascaded standby is a standalone or RAC.
Regards,
Shivananda