Display Data Guard configuration in SQL Developer

By Franck Pachot

.
The latest version of SQL Developer, the 17.2 one released after Q2 of 2017, has a new item in the DBA view showing the Data Guard configuration. This is the occasion to show how you can cascade the log shipping in Oracle 12c

A quick note about this new versioning: this is the release for 2017 Q2 and the version number has more digits to mention the exact build time. Here this version is labeled 17.2.0.188.1159 and we can see when it has been built:

SQL>  select to_date('17.x.0.188.1159','rr."x.0".ddd.hh24mi') build_time from dual;
 
BUILD_TIME          
--------------------
07-JUL-2017 11:59:00

Non-Cascading Standby

Here is my configuration with two standby databases:

DGMGRL> show configuration
Configuration - orcl
 
  Protection Mode: MaxPerformance
  Members:
  orcla - Primary database
    orclb - Physical standby database
    orclc - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS   (status updated 9 seconds ago)

I have only the LogXptMode defined here, without any RedoRoutes

DGMGRL> show database orcla LogXptMode 
  LogXptMode = 'SYNC'

with this configuration, the broker has set the following log destination on orcla, orclb and orclc:

INSTANCE_NAME    NAME                 VALUE
---------------- -------------------- -------------------------------------------------------------------------------------------------------------
ORCLA            log_archive_dest_1   location=USE_DB_RECOVERY_FILE_DEST, valid_for=(ALL_LOGFILES, ALL_ROLES)
ORCLA            log_archive_dest_2   service="ORCLB", SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300
                                      db_unique_name="orclb" net_timeout=30, valid_for=(online_logfile,all_roles)
ORCLA            log_archive_dest_3   service="ORCLC", SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300
                                      db_unique_name="orclc" net_timeout=30, valid_for=(online_logfile,all_roles)
 
INSTANCE_NAME    NAME                 VALUE
---------------- -------------------- -------------------------------------------------------------------------------------------------------------
ORCLB            log_archive_dest_1   location=/u01/fast_recovery_area
 
INSTANCE_NAME    NAME                 VALUE
---------------- -------------------- -------------------------------------------------------------------------------------------------------------
ORCLC            log_archive_dest_1   location=/u01/fast_recovery_area

In the latest SQL Developer you have the graphical representation of it from the DBA view / Dataguard / console:

Cascading Standby

In 12c we can define cascading standby: instead of the primary shipping the redo to all standby databases, you can have the primary shipping to one standby only, and this one can forward the redo to another one. You define that with the RedoRoute property:

DGMGRL> edit database orcla set property redoroutes = '(local:orclb)         (orclb:orclc async)';
Property "redoroutes" updated
DGMGRL> edit database orclb set property redoroutes = '(orcla:orclc async)   (local:orcla)';
Property "redoroutes" updated

The first route defined in each property is applied when orcla is the primary database:

• on orcla (local:orclb) means that orcla sends redo to orclb when primary
• on orclb (orcla:orclc async) means that orclb sends redo to orclc when orcla is primary. LogXptMode is SYNC but overriden here with ASYNC

The second route defined in each property is applied when orclb is the primary database:

• on orcla (orclb:orclc async) means that orcla sends redo to orclc when orclb is primary. LogXptMode is SYNC but overriden here with ASYNC
• on orclb (local:orcla) means that orclb sends redo to orcla when primary

With this configuration, and orcla still being the primary, the broker has set the following log destination on orcla, orclb and orclc:

INSTANCE_NAME    NAME                 VALUE
---------------- -------------------- -------------------------------------------------------------------------------------------------------------
ORCLA            log_archive_dest_1   location=USE_DB_RECOVERY_FILE_DEST, valid_for=(ALL_LOGFILES, ALL_ROLES)
ORCLA            log_archive_dest_2   service="ORCLB", SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300
                                      db_unique_name="orclb" net_timeout=30, valid_for=(online_logfile,all_roles)
 
INSTANCE_NAME    NAME                 VALUE
---------------- -------------------- -------------------------------------------------------------------------------------------------------------
ORCLB            log_archive_dest_1   location=/u01/fast_recovery_area
ORCLB            log_archive_dest_2   service="ORCLC", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=3
                                      00 db_unique_name="orclc" net_timeout=30, valid_for=(standby_logfile,all_roles)
 
INSTANCE_NAME    NAME                 VALUE
---------------- -------------------- -------------------------------------------------------------------------------------------------------------
ORCLC            log_archive_dest_1   location=/u01/fast_recovery_area

The show configuration from DGMGRL displays them indented to see the cascading redo shipping:

DGMGRL> show configuration
Configuration - orcl
 
  Protection Mode: MaxPerformance
  Members:
  orcla - Primary database
    orclb - Physical standby database
      orclc - Physical standby database (receiving current redo)
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS   (status updated 27 seconds ago)

And SQL Developer Data Guard console shows:

Switchover

Now the goal of defining several routes is to have all log destination automatically changed when the database role change.
I’m doing a switchover:

Connected to "orclb"
Connected as SYSDG.
DGMGRL> switchover to orclb;
Performing switchover NOW, please wait...
New primary database "orclb" is opening...
Operation requires start up of instance "ORCLA" on database "orcla"
Starting instance "ORCLA"...
ORACLE instance started.
Database mounted.
Database opened.
Connected to "orcla"
Switchover succeeded, new primary is "orclb"

Now it is orcla which cascades the orclb redo to orclc:

DGMGRL> show configuration;
Configuration - orcl
 
  Protection Mode: MaxPerformance
  Members:
  orclb - Primary database
    orcla - Physical standby database
      orclc - Physical standby database (receiving current redo)
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS   (status updated 74 seconds ago)

Here is how it is displayed from SQL Developer:

We have seen how the configuration is displayed from DGMGRL and graphically from SQL Developer. Of course, you can also query the Data Guard configuration:

SQL> select * from V$DATAGUARD_CONFIG;
 
DB_UNIQUE_NAME  PARENT_DBUN  DEST_ROLE         CURRENT_SCN  CON_ID
--------------  -----------  ---------         -----------  ------
orcla           orclb        PHYSICAL STANDBY  3407900      0
orclc           orcla        PHYSICAL STANDBY  3408303      0
orclb           NONE         PRIMARY DATABASE  0            0

and the broker configuration:

SQL>  select * from V$DG_BROKER_CONFIG;
 
DATABASE  CONNECT_IDENTIFIER  DATAGUARD_ROLE    REDO_SOURCE  ENABLED  STATUS  VERSION  CON_ID
--------  ------------------  --------------    -----------  -------  ------  -------  ------
orcla     ORCLA               PHYSICAL STANDBY  -UNKNOWN-    TRUE     0       11.0     0
orclb     ORCLB               PRIMARY           -N/A-        TRUE     0       11.0     0
orclc     ORCLC               PHYSICAL STANDBY  orcla        TRUE     0       11.0     0

This another reason to use the broker. Once the configuration is setup and tested, you have nothing else to think about when you do a switchover. The log archive destination is automatically updated depending on the database roles.