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