Oracle 12c Real-Time cascade standby database

Oracle Community

Oracle 12c Real-Time cascade standby database

Introduction

Cascade standby database feature is introduced from 9i , Cascade standby database works same as standby database but it receives the redo logs from the first standby database but not primary database directly. In fact cascading standby database bypasses redo logs to the cascade standby. So there is much scope to reduce load , bandwidth utilization on primary database. But prior to 12c, Cascaded standby database will not be having fresh data same as cascading standby and now from 12c Cascaded standby database popularized with Real-Time Apply and now there is no more scope of LAG between primary and cascaded standby database. We will see more insight on the configuration, validation of Real-Time apply and usage of Data Guard Broker.
 

Cascading Vs Cascade

Terminology looks very similar but there is lot of difference in terms of how they function, Let me clarify to you how exactly cascading and cascaded databases will be located from the below picture.
 
 
 
Cascading standby refers to standby database which receives redo first from primary database (or) Physical standby which have direct connection to the Primary database. Cascading standby database can work in Real-Time apply mode even prior to 12c. we can also name Cascading standby database as First or Local standby because its nearer to Primary database.
 
Cascaded standby will not be having direct connection to primary database instead of that cascading standby database serves the redo logs to the cascaded standby database, But cascaded standby have to wait for the log sequence until it is completely archived locally on cascading standby database(Prior to 12c).
 

Real-Time Cascading

As we discussed in above statement prior to 12c when primary database redo is written to the standby redo logs of cascading standby database then the redo will be sent to cascaded database only after the standby redo log been archived, So there will always be one log sequence LAG with Cascading and cascaded standby database, but from 12c it is very much possible to forward the live redo as it is from cascading to cascaded standby database. Still it is up to you whether you prefer to go for Real-Time apply or not.
 
Key Points for Real-Time Cascading
 

·         Cascaded redo transport destination aliased as terminal destination

·         Cascading can be either Real-Time apply or Non Real Time apply

·         Cascading standby can cascade up to 30 terminal destinations

·         Cascading standby should be either FARSYNC instance or Physical standby

·         Cascading standby database can be in any protection mode and it doesn't matter to terminals

·         To acquire Real-Time Apply on Cascaded the Standby redo logs of cascading should have been used at least once(ensure status is ACTIVE)

·         Data Guard Broker is supported from 12c.

·         Real-Time apply on Cascaded standby database comes with Active Data Guard license.

 

Let's start understanding configuration

If Primary and Standby(cascading) is already in place and you are adding new standby as cascaded standby then follow the same procedure how we usually create standby database and only configuration parameters will be changed which we are going to see below.
By using duplicate method the standby redo log files will be created automatically on cascaded standby or else create standby redo log files same or more size than online redo log files and at least one more group than online redo log groups of source. In my below example the complete practice is on Maximum Availability mode.
 
 
INSTANCE_NAME
DB_UNIQUE_NAME
Oracle Net Service
Primary
MCDB
CANADA
CANADA
Cascading Standby
DRMCDB
INDIA
INDIA
Cascaded Standby
CMCDB
UK
UK
 
 
Few things you must know before deploying
 

·         If Transport mode is SYNC and if you haven't mentioned either AFFIRM or NOAFFIRM, Then the default transport attribute will be AFFIRM otherwise if explicitly ASYNC transport mode configured then attribute will be NOAFFIRM.

·         SYNC transport mode supports all destinations 11 to 31 but Non Real-Time cascading supports destinations 1 to 10.

·         Do not involve Logical standby database to forward redo to the cascaded standby database.

 
More confused? here is thumb rule for you......

·         If you want to configure Real-Time Cascade, Then transport mode should be ASYNC and it can be any destinations.

·         If you are not looking for Real-Time Cascade, Transport mode should be SYNC and use the destinations from 1 to 10.

 

 
 

Setup

Frankly speaking... Configuration is very easy and you need to put efforts only on redo transport. From primary to cascading, which can be (SYNC, FASTSYNC or ASYNC with LGWR)
Redo Transport from Cascading to Cascaded should be ASYNC to enable Real-Time apply, Prior to 12c so far we keep configuring Non Real-Time so let's start journey of Real-Time cascade standby.
 
Primary:
SQL> alter system set log_archive_dest_2='SERVICE=INDIA SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=INDIA' scope=both;
 
SQL> alter system set log_archive_config='DG_CONFIG=(CANADA,INDIA,UK)' scope=both;
 
SQL> alter system set fal_server='INDIA' scope=both;
 
NAME                               DISPLAY_VALUE
------------------------------ ---------------------------------------
db_name                                mcdb
db_unique_name                         canada
fal_server                             india
log_archive_config                     DG_CONFIG=(CANADA,INDIA,UK)
log_archive_dest_2                     SERVICE=INDIA SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=INDIA
log_archive_dest_state_2                                                  ENABLE
remote_login_passwordfile                                               EXCLUSIVE
 
Cascading Standby:
SQL> alter system set log_archive_dest_2='SERVICE=UK ASYNC NOAFFIRM VALID_FOR=(STANDBY_LOGFILES, STANDBY_ROLE) DB_UNIQUE_NAME=UK' scope=both;
 
SQL> alter system set log_archive_config='DG_CONFIG=(CANADA,INDIA,UK)' scope=both;
 
SQL> alter system set fal_server='UK' scope=both;
 
NAME                                DISPLAY_VALUE
------------------------------ ------------------------------------------
db_name                                mcdb
db_unique_name                         india
fal_server                             UK
log_archive_config                     DG_CONFIG=(CANADA,INDIA,UK)
log_archive_dest_2                     SERVICE=UK ASYNC NOAFFIRM VALID_FOR=(STANDBY_LOGFILES, STANDBY_ROLE) DB_UNIQUE_NAME=UK
log_archive_dest_state_2                                                   ENABLE
remote_login_passwordfile                                                EXCLUSIVE
 
Cascaded Standby:
SQL> alter system set log_archive_dest_2='SERVICE=INDIA ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=INDIA' scope=both;
 
SQL> alter system set log_archive_config='DG_CONFIG=(CANADA,INDIA,UK)' scope=both;
 
SQL> alter system set fal_server='INDIA' scope=both;
 
NAME                                DISPLAY_VALUE
------------------------------ ------------------------------------------
db_name                                mcdb
db_unique_name                    UK
fal_server                               INDIA
log_archive_config                 DG_CONFIG=(CANADA,INDIA,UK)
log_archive_dest_2                 SERVICE=INDIA SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=INDIA
log_archive_dest_state_2         ENABLE
remote_login_passwordfile      EXCLUSIVE
 
With the above configuration Real-Time cascade standby works perfectly and further more if you want to configure the flexible architecture then configure transport service from cascaded to cascading and vice versa like n to n configuration, by this it will be useful in case of switchover. Might so many are aware of the Data Guard view which show the Parent and child relationship of overall cascade standby, If in case you have never used then take a look and see how easy to understand whole configuration. Ensure you must run below view from Primary database.
 
 
From the above image, For Canada there is no parent database because it's an Primary database, The next database INDIA parent database is referring to CANADA and likewise UK parent database is referring to INDIA.
 
Very simple and easy configuration? am sure you might be thinking..... How to ensure that Real-Time cascade standby works? Here is demo which shows how the live data we can view from Cascaded standby database.

Validation of Real-Time Cascade Standby

Before testing Real-Time Cascade standby database , perform few prerequisites which are necessary.
 
SQL> select 'YES' Active_DataGuard from v$managed_standby ms, v$database db where ms.process like '%MRP%' and db.open_mode like '%READ ONLY%';
 
ACTIVE_DATAGUARD
--------------------
YES
 
SQL>
 
Task 1(Primary/CANADA)): Gather maximum sequence, create table and perform few DML's with commit and again gather maximum sequence from Primary database(CANADA)
 
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
265
SQL> select count(*) from c##nassyam.castab;
COUNT(*)
----------
396
SQL> insert into castab select * from castab;
396 rows created.
SQL> select count(*) from c##nassyam.castab;
COUNT(*)
----------
792
SQL> commit;
Commit complete.
SQL> select max(sequence#) from v$archived_log;
 
MAX(SEQUENCE#)
--------------
265
SQL>
So before and after the creation of table there are no change in sequence number, that means no log switch occurred or enforced manually, In this case if Real-Time cascade is working then without log switch the redo should have been received and applied and created table with rows visible from cascaded standby database.
 
Task2(Cascading/INDIA): Before verifying from Cascaded standby, lets verify from cascading standby database whether redo is received or not.
 
SQL> select db_unique_name,database_role from v$database;
DB_UNIQUE_NAME                   DATABASE_ROLE
------------------------------ ----------------
india                         PHYSICAL STANDBY
SQL> select count(*) from c##nassyam.castab;
COUNT(*)
----------
792
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
265
SQL> select sequence#,process,status,blocks from v$managed_standby order by sequence#;
 
SEQUENCE#   PROCESS    STATUS       BLOCKS
---------- --------- ------------ ----------
0             RFS    IDLE             0
0             RFS    IDLE             0
0             ARCH   CONNECTED        0
263           ARCH   CLOSING          1485
264           ARCH   CLOSING          720
265           ARCH   CLOSING          187
266           MRP0   APPLYING_LOG     102400
266           RFS    IDLE             1
266           LNS    WRITING          1
 
9 rows selected.
 
From Cascading standby(INDIA) the sequence is same as primary of course but the table is visible and the number of rows same as primary, but this is not something new because its common behavior even in prior releases.
 
Task3(Cascaded/UK): Now you have arrived to the exact place to test whether Real-Time is working or not on Cascaded standby database(UK)
 
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
265
SQL> select db_unique_name,database_role from v$database;
DB_UNIQUE_NAME DATABASE_ROLE
------------------------------ ----------------
UK PHYSICAL STANDBY
SQL> select count(*) from c##nassyam.castab;
COUNT(*)
----------
792
SQL> select sequence#,process,status,blocks from v$managed_standby order by sequence#;
 
SEQUENCE#   PROCESS   STATUS       BLOCKS
---------- --------- ------------ ----------
0           RFS       IDLE          0
0           ARCH      CONNECTED     0
0           RFS       IDLE          0
263         ARCH      CLOSING     1485
264         ARCH      CLOSING     720
265         ARCH      CLOSING     187
266         RFS       IDLE        1
266         MRP0     APPLYING_LOG 102400
 
Yes, so with the same sequence(266) without log switch the table and number of rows are viewable even from Cascading standby database, we can even verify the Blocks from cascading and cascaded standby database as well. That's the pin point of overall new feature "Real-Time Cascade standby"
 
Tip: If you want to ensure whether the database(standby) is in Real-Time apply mode, from alert log you can find clear information as the recovery will be performed from Online Redo Log where in Non Rea-Time mode terminal will wait for the complete log sequence until unless log switch occurred on Primary database.
 
Media Recovery Waiting for thread 1 sequence 269 (in transit)
Wed Oct 29 07:55:27 2014
Recovery of Online Redo Log: Thread 1 Group 4 Seq 269 Reading mem 0
Mem# 0: /u02/app/oracle/fast_recovery_area/UK/onlinelog/o1_mf_4_b4yy6bqc_.log
 
 
Non Real-Time mode with SYNC transport mode: I would like to show what happens if we change transport mode from ASYNC to SYNC and whether cascaded standby database will be in Real-Time mode or not.
 
Prior to the test, Change transport mode from cascading standby database as below
 
SQL> alter system set log_archive_dest_2='SERVICE=UK SYNC VALID_FOR=(STANDBY_LOGFILES, STANDBY_ROLE) DB_UNIQUE_NAME=UK' scope=both;
 
System altered.
 
SQL>
 
Task 1(Primary/CANADA)): Gather maximum sequence, perform few DML's with commit and again gather maximum sequence from Primary database(CANADA)
 
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
267
SQL> select count(*) from c##nassyam.castab;
COUNT(*)
----------
792
SQL> insert into castab select * from castab where rownum < 100;
99 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from c##nassyam.castab;
COUNT(*)
----------
891
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
267
SQL>
 
Now there are more rows are inserted but the log sequence number is same before and after the changes in the table.
 
Task2(Cascading/INDIA): Now verify the number of rows and sequence number from Cascading standby database.
 
 
SQL> select db_unique_name,database_role from v$database;
DB_UNIQUE_NAME DATABASE_ROLE
------------------------------ ----------------
india PHYSICAL STANDBY
SQL>
SQL> select count(*) from c##nassyam.castab;
COUNT(*)
----------
891
SQL> select max(sequence#) from v$archived_log;
 
MAX(SEQUENCE#)
--------------
267
SQL>
 
Cascading standby database works in Real-Time apply mode as is, But now it's time to verify whether new DML's are inserted or not on cascaded standby database after changes with redo transport mode SYNC.
 
Task3(Cascaded/UK): If Real-Time apply is working on cascaded/terminal database then the number of rows "891" should be same as Cascading standby database, However let's verify...
 
SQL> select db_unique_name,database_role from v$database;
 
DB_UNIQUE_NAME DATABASE_ROLE
------------------------------ ----------------
UK PHYSICAL STANDBY
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
267
SQL> select count(*) from c##nassyam.castab;
COUNT(*)
----------
792
SQL>
 
So after changing transport mode from ASYNC to SYNC on cascading standby database then the redo will not be applied on Terminal standby database with Real-Time apply mode, as i said above the standby database waits for the sequence but it never reads from the Redo Logs.
 
Wed Oct 29 07:44:27 2014
Media Recovery Log /u02/app/oracle/fast_recovery_area/UK/archivelog/2014_10_29/o1_mf_1_267_b50md25p_.arc
Media Recovery Waiting for thread 1 sequence 268
 
 
Real-Time Cascade standby with Broker: From 12c few more features introduced with Data Guard Broker support with Cascade standby database and one of the new parameter is "RedoRoutes" , By RedoRoutes parameter specification we can define the From and To destinations to send and receive data and much more options whether to send redo with synchronously or asynchronously.
 
For Real-Time cascading standby database, our aim is to send redo from CANADA which is also called as LOCAL in terms of Broker to the Standby INDIA and from INDIA(cascading) to the Terminal destination(UK)
 
DGMGRL> edit database CANADA set property redoroutes='(LOCAL:INDIA) (INDIA:UK)';
Property "redoroutes" updated
DGMGRL>
 
From INDIA(Cascading) the redo should be sent to the Cascaded/Terminal(UK) with asynchronously to avail the Real-Time apply.
 
DGMGRL> edit database INDIA set property redoroutes='(CANADA:UK ASYNC)';
Property "redoroutes" updated
DGMGRL>
 
Verify the Broker Configuration: Configuration is very simple by the above commands now we can verify the entire Broker configuration.
 
DGMGRL> show configuration;
 
Configuration - real-cascade
 
Protection Mode: MaxAvailability
Databases:
canada - Primary database
india - Physical standby database
uk - Physical standby database (receiving current redo)
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS
 
DGMGRL>
 
Yes, Cascaded/Terminal standby database is receiving redo from the current redo logs which means Real-Time apply worked efficiently and not waiting anymore until the log switch occurred at source/cascading standby database.
 

Conclusion

So from 12c Cascading standby forwards redo to the terminal/cascaded databases in Real-Time as it is received and there is no place for delay, Apart from that with active data guard license cascaded standby will have live data as primary for offloading read-only queries and reports and prior versions of 12c there is no Data Guard Broker concept with cascade standby configuration and from 12c Data Guard Broker supports.
6934 1 /
Follow / 18 Jan 2016 at 10:46am

Great Artical ...