Oracle Cascading Standby

                                           Oracle   Cascading Standby 


The cascade standby database concept was introduced from Oracle 9i Release 2 onwards. In the latest versions and releases there are many changes in the cascade standby databases. The cascade standby database concept was introduced to reduce the load on your primary database and to transmit redo data from the primary to all standby databases, and the network bandwidth needs to be large enough to handle the load. If it is a huge OLTP then it will be more problematic to handle.


A cascaded redo transport destination (also known as a terminal destination) receives primary database redo indirectly from a standby database rather than directly from a primary database.

A physical standby database that cascades primary database redo to one or more terminal destinations at the same time it is applying changes to its local database files is known as a cascading standby database.

With cascading, the overhead associated with performing redo transport is offloaded from a primary database to a cascading standby database.

A cascading standby database can cascade primary database redo to up to 30 terminal destinations.

As of Oracle Database 12c Release 1 (12.1), a cascading standby database can either cascade redo in real-time (as it is being written to the standby redo log file) or non-real-time (as complete standby redo log files are being archived on the cascading standby).   Key point is  setting valid_for=(standby_logfiles,standby_role)    on cascading standby  to  enable   log shipping in mount stage


We can understand redo transport behavior from  Oracle Doc

https://docs.oracle.com/database/121/SBYDB/log_transport.htm#SBYDB5126



Restrictions 

1.
 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.

2. A cascading environment cannot be used if the primary database is a RAC configuration. (This restriction has been removed from 11.2.0.2)

3. Only physical standby databases can cascade redo.

4. Real-time cascading requires a license for the Oracle Active Data Guard option.

5. Non-real-time cascading is supported on destinations 1 through 10 only. (Real-time cascading is supported on all destinations.)





Implementations :

To minimize the lag, Standby Redo Logs are needed while implementing Cascaded Standbys to make use of the Real Time Apply feature.

Primary Site:
DB Unique Name : srprim
Hostname       : ora1-1


Cascading Site:
DB Unique Name : srpstb
Hostname       : ora1-2



Cascaded Site:
DB Unique Name : srcstb
Hostname       : ora1-3



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.


Data guard broker can only be created between  srprim and srpstb

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 paramete


Set the log_archive_config parameter on all the 3 databases to host the value of all 3 database unique names.

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      DG_CONFIG=(srprim,srpstb,srcstb)





On the cascading standby database “srpstb”, set the parameter “log_archive_dest_3” to ship the redo data to the cascaded standby database “srcstb”.

SYS@srpstb> sho parameter dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_2          string   
log_archive_dest_2                   string      service=srprim valid_for=(online_logfiles,primary_role) db_unique_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> alter system set log_archive_dest_3='service=srcstb valid_for=(standby_logfiles,standby_role) db_unique_name=srcstb';


SYS@srpstb> alter system set log_archive_config='DG_CONFIG=(srprim,srpstb,srcstb)';






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.

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)';

SYS@srcstb> alter system set fal_server='srpstb';

SYS@srcstb> alter system set fal_client='srcstb';

SYS@srcstb> alter system set log_archive_dest_1='LOCATION=/oradb/app/oracle/archive/orcl_stby2';