Everybody knows that you can get the Data Guard configuration in dgmgrl with the command:
|
1 |
show configuration; |
but few know that this information is actually available in x$drc:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL> desc x$drc Name Null? Type --------------- -------- --------------- ADDR RAW(8) INDX NUMBER INST_ID NUMBER CON_ID NUMBER OBJECT_ID NUMBER ATTRIBUTE VARCHAR2(30) VALUE VARCHAR2(4000) PARENT_ID VARCHAR2(15) STATUS VARCHAR2(30) MESSAGE VARCHAR2(256) ERRNUM NUMBER VALUE_RAW RAW(4095) ERRTIME NUMBER |
The table design is not very friendly, because it is a mix of ATTRIBUTE/VALUE pairs (hence many rows per object) and specific columns.
So, in order to get something usable to show the databases and their status, the only solution is to make use of PIVOT().
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
SELECT piv.*, obj.status FROM ( SELECT object_id, attribute, value FROM x$drc WHERE object_id IN ( SELECT object_id FROM x$drc WHERE attribute = 'DATABASE' ) ) drc PIVOT ( MAX ( value ) FOR attribute IN ( 'DATABASE' DATABASE , 'intended_state' intended_state , 'connect_string' connect_string , 'RAC' RAC , 'enabled' enabled , 'role' role , 'receive_from' receive_from , 'ship_to' ship_to , 'dgb_connect' dgb_connect , -- 'static_connect_identifier' static_connect_identifier , 'FSFOTargetValidity' FSFOTargetValidity ) ) piv JOIN x$drc obj ON ( obj.object_id = piv.object_id AND obj.attribute = 'DATABASE' ); |
To get it in a friendly format, I recommend using SQLcl and settting
|
1 |
set sqlformat ansiconsole |
This is what I get on a simple two databases configuration (primary/standby):
|
1 2 3 |
OBJECT_ID DATABASE INTENDED_STATE CONNECT_STRING RAC ENABLED ROLE RECEIVE_FROM SHIP_TO DGB_CONNECT FSFOTARGETVALIDITY STATUS 16842752 ludocdb2_site1 READ-WRITE-XPTON newbox01:1521/ludoCDB2_SITE1_DGMGRL NO YES PRIMARY -N/A- ludocdb2_site2 newbox01:1521/ludoCDB2_SITE1_DGMGRL 2 SUCCESS 33619968 ludocdb2_site2 PHYSICAL-APPLY-ON newbox02:1521/ludoCDB2_SITE2_DGMGRL NO YES PHYSICAL -UNKNOWN- -N/A- newbox02:1521/ludoCDB2_SITE2_DGMGRL 1 SUCCESS |
and with a real-time cascade standby:
|
1 2 3 4 |
OBJECT_ID DATABASE INTENDED_STATE CONNECT_STRING ENABLED ROLE RECEIVE_FROM SHIP_TO FSFOTARGETVALIDITY STATUS 16842752 toolcdb1_site1 READ-WRITE-XPTON newbox01:1521/TOOLCDB1_SITE1_DGMGRL YES PRIMARY -N/A- toolcdb1_site2 2 SUCCESS 33619968 toolcdb1_site2 PHYSICAL-APPLY-ON newbox02:1521/TOOLCDB1_SITE2_DGMGRL YES PHYSICAL toolcdb1_site1 toolcdb1_site3:RTC_ON 5 WARNING 50397184 toolcdb1_site3 PHYSICAL-APPLY-ON newbox03:1521/TOOLCDB1_SITE3_DGMGRL YES PHYSICAL toolcdb1_site2 -N/A- 5 WARNING |
(interesting to note the leading :RTC_ON in the ship_to attribute).
Although it is much easier to get this information from DGMGRL, get it programmatically is more sure/flexible using the SQL interface, as you know what you want to get, no matter how the dgmgrl syntax changes.
Looking forward to have the REST APIs in a future version of Data Guard 🙂
—
Ludovico
Latest posts by Ludovico (see all)
- When it comes to using Oracle, trust Oracle… - July 14, 2023
- Video: Where should I put the Observer in a Fast-Start Failover configuration? - November 29, 2022
- Video: The importance of Fast-Start Failover in an Oracle Data Guard configuration - November 29, 2022
Pingback: An early July blog… – Oracle Business Intelligence
Hi Ludovico,
nice query. Next step would be to dig into the properties. This can be achieved with something like SELECT dbms_drs.get_property_obj(, ‘LogXptMode’) FROM dual; You can use read-write and read-only.properties there.
Some property examples: DelayMins (rw), TransportLag (ro), ApplyLag (ro), ApplyRate (ro)…
Mathias
Very useful. Thanks!