This was first published on https://blog.dbi-services.com/display-data-guard-configuration-in-sql-developer (2017-08-01)
Republishing here for new followers. The content is related to the the versions available at the publication date
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
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:
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:
The second route defined in each property is applied when orclb is the primary database:
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:
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.
I’m testing it right now, (SQL Developer’s new dataguard panel) and the new panel doesn’t shows up if you don’t have a Broker configuration.
Hi, Sure, it reads information from V$DG_BROKER_CONFIG and so on. Any reason you don’t use the broker? Regards, Franck.
Hi Franck,
We don’t use the DG broker below 12c, as per security we don’t use sys password at all, in 12c we have sysdg and setup broker with that.
what you think?
thanks
Hi John, I rarely see environments with this separation of roles, but yes, that makes sense. SYSDG is there to give the minimum rights to administrate the Data Guard configuration.