Data Guard logical physical cascaded standby databases Oracle Database Tips by Donald BurlesonDecember 9, 2015

Data Guard Cascaded Standby Databases

The term "Cascaded Standby Database" is used to describe a setup where a Oracle instance can feed its own set of standby databases. In this configuration, there are three layers of databases: the original primary database; standby databases originating from the primary database; and the cascaded standby databases. Using this setup, one primary database can have a maximum of 90 standby databases.

Although the creation of 90 standby databases may never be required for one primary database, this functionality can be fairly useful for offloading either reporting or backup activities to databases remotely located in a WAN without having the overhead of the primary database to transfer the archived log files to these standby databases over WAN. Figure 4.2 illustrates the cascaded databases configuration.

Figure 4.2 - Cascaded Standby Database

Cascaded standby databases can be setup from either a physical Oracle instance or a logical standby database.

Using Physical Standby Databases for Cascaded Standby Databases

The log_archive_dest_n parameters will need to be set in the initialization file of the physical Oracle instance to send the archived redo logs to cascaded destinations.

The physical Oracle instance will receive redo logs from the primary database in the form of standby redo logs and not archived redo logs. Hence, the log writer on the primary database will need to be set to write standby redo logs to this physical standby database.

Also, standby redo logs will need to be created on the standby site so that the LGWR of the primary database can write to the standby redo logs using RFS process. The following statement can be used to create standby redo logs on the physical standby site. This will create a standby redo log group and add a log file of size 4 MB to it:

Oracle Data Guard:  Using Physical Standby Databases for Cascaded Standby Databases

ALTER DATABASE ADD STANDBY LOGFILE GROUP 1
('/oracle/appsdb/redo/redo01.log') SIZE 4096K;

The same number of standby redo log groups and members as are present in the primary database must be created.

            WARNING - If the standby redo log on a physical Oracle instance is full and is not archived, the primary database may wait or shutdown, depending on the protection mode setting. This will disrupt the database services provided by the primary database.

When the Oracle instance is using standby redo logs, it will try to transfer, on log switch, the archived redo logs to all the destinations specified in the init.ora file using the log_archive_dest_n parameter. This may include other physical standby databases and the primary database along with cascaded archival destinations, if a symmetrical init.ora parameter file is being used.

The archival destination related to the cascaded Oracle instance should be set to DEFER the in init.ora file on the primary database. For example, the following init.ora file specifies four archival destinations.

Destinations identified by service C_apps is pointing to the cascaded destination. The log_archive_dest_state_4 is set to DEFER, so that the primary database will not try to send directly to this destination.

After switchover, where the primary database will swap the role with standby database, this parameter can be altered to ENABLE, so that archiving to the cascaded destination can begin.  The log_archive_dest_2 is a physical standby database, which is supporting the cascaded standby databases. The log transfer method for this destination is set to LGWR.

The following is the init.ora file on the primary database:

#Archive

STANDBY_ARCHIVE_DEST='LOCATION=/oracle/appsdb/arch'
LOG_ARCHIVE_FORMAT=appsdb_%t_%s.dbf
LOG_ARCHIVE_START=TRUE
LOG_ARCHIVE_DEST_1='LOCATION=/oracle/meddb/arch'
LOG_ARCHIVE_DEST_2='SERVICE=appsstdby1 LGWR'
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_3='SERVICE=appsstdby2'
LOG_ARCHIVE_DEST_STATE_3=ENABLE
LOG_ARCHIVE_DEST_4='SERVICE=C_apps'
LOG_ARCHIVE_DEST_STATE_4=DEFER

Using Physical Standby Databases for Cascaded Standby Databases

Initialization parameter file on the physical Oracle instance shows that the log_archive_dest_state_4 parameter is set to ENABLE.  As a result, the ARCH process on the standby site will transfer the archived redo logs to the cascaded destination.

The following is the init.ora file on a physical standby database:

#Archive

STANDBY_ARCHIVE_DEST='LOCATION=/oracle/appsdb/arch'
LOG_ARCHIVE_FORMAT=appsdb_%t_%s.dbf
LOG_ARCHIVE_START=TRUE
LOG_ARCHIVE_DEST_1='LOCATION=/oracle/appsdb/arch'
LOG_ARCHIVE_DEST_2='SERVICE=appsstdby1 LGWR'
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_3='SERVICE=appsstdby2'
LOG_ARCHIVE_DEST_STATE_3=ENABLE
LOG_ARCHIVE_DEST_4='SERVICE=C_apps'
LOG_ARCHIVE_DEST_STATE_4=ENABLE

Upon switchover or failover, this parameter should be changed to DEFER to stop the archival to the cascaded destination. As an option, the state can be set to DEFER for other standby databases connected to the primary database. To have a symmetrical init.ora file, the name of Oracle Net services should be consistent on all the sites.