How to get the Data Guard broker configuration from a SQL query?

Everybody knows that you can get the Data Guard configuration in dgmgrl with the command:

but few know that this information is actually available in x$drc:

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().

To get it in a friendly format, I recommend using SQLcl and settting

This is what I get on a simple two databases configuration (primary/standby):

and with a real-time cascade standby:

(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 🙂


The following two tabs change content below.


Oracle ACE Director and Principal Consultant at Trivadis
Ludovico is an Oracle ACE Director, frequent speaker and community contributor, working as Principal Consultant for Trivadis, the leading Oracle consulting firm in Switzerland and German-speaking Europe.

2 thoughts on “How to get the Data Guard broker configuration from a SQL query?

  1. 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 there.
    Some property examples: DelayMins (rw), TransportLag (ro), ApplyLag (ro), ApplyRate (ro)…


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.