19c DG Broker export/import configuration

This is something I wanted for a long time: be able to save a broker configuration to be able to re-configure it if needed. What I usually do is maintain a script with all commands. What I dreamed was being able to export the configuration as a script. What we have now, in 19c, is the ability to export/import the configuration as a .xml file.

Actually, the configuration is already stored as XML in the broker configuration files (the .dat ones):

SQLcl: Release 18.4 Production on Tue Feb 19 13:40:27 2019
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0
SQL> show parameter broker
NAME                   TYPE    VALUE
---------------------- ------- -------------------------------------
dg_broker_config_file1 string  ?/dbs/dr1CDB1A.dat
dg_broker_config_file2 string  ?/dbs/dr2CDB1A.dat
dg_broker_start        boolean TRUE

Those are binary files, but we can look at the content with ‘strings’. And since 12cR2 the content shows some XML. In all versions, we can see a message from the authors, ‘fine folks at NEDC’. Here is where this was developed:

NEDC Facility Information

So here are the strings in my broker file:

[oracle@db192 ~]$ strings /u01/app/oracle/product/DB192/dbs/dr1CDB1A.dat
}|{z
cdb1a
cdb1a
Brought to you by the fine folks at NEDC.
<?xml version="1.0" encoding="UTF-8"?>
<DRC Version="19.2.0.0.0" CurrentPath="True" Name="cdb1a">
  <DefaultState>ONLINE</DefaultState>
  <DRC_UNIQUE_ID>1934436017</DRC_UNIQUE_ID>
  <IntendedState>ONLINE</IntendedState>
  <FastStartFailoverOBID1>1673904225</FastStartFailoverOBID1>
  <FastStartFailoverOBID2>1673904226</FastStartFailoverOBID2>
  <FastStartFailoverOBID3>1673904227</FastStartFailoverOBID3>
  <Configuration_Name>cdb1a</Configuration_Name>
  <Member MemberID="1" CurrentPath="True" Enabled="True" MultiInstanced="True" Name="cdb1a">
    <IntendedState>PRIMARY</IntendedState>
    <DefaultState>PRIMARY</DefaultState>
...
      <OldPrimary>cdb1b</OldPrimary>
      <NewPrimary>cdb1a</NewPrimary>
      <Status>0</Status>
      <Timestamp>1000647508</Timestamp>
    </RoleChangeRecord>
  </RoleChangeHistory>
  <MIV>0</MIV>
  <PRIMARY_SITE_ID>513</PRIMARY_SITE_ID>
</DRC>

If you want to know more about those, my ex-colleague William Sescu at dbi-services explained everything:

Oracle 12c - How to Recover lost DataGuard Broker Configuration Files - Blog dbi services

Now in 19c, no need to parse that as we can export this metadata in a well-formatted XML:

[oracle@db192 ~]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Feb 19 13:46:05 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "CDB1B"
Connected as SYSDG.
DGMGRL> export configuration to MYCONFIG;
Succeeded.
DGMGRL>

You cannot mention a full path as the file goes to the diag trace directory (the one called ‘udump’ by the seasoned DBAs). If you are not sure, the broker trace shows it:

The Data Guard broker metadata is exported to /u01/app/oracle/diag/rdbms/cdb1b/CDB1B/trace/myconfig

Here you see that it was transformed to lower-case, as usual in DGMGRL, but you can also quote it to make it case sensitive. What this doesn’t show is that when you do not provide an extension, ‘.log’ will be added (which is a funny default for an XML file…)

[oracle@db192 trace]$ ls -alrt $ORACLE_BASE/diag/rdbms/*/$ORACLE_SID/trace/*myconfig*
-rw-r--r--. 1 oracle oinstall 4992 Feb 19 13:46 /u01/app/oracle/diag/rdbms/cdb1b/CDB1B/trace/myconfig.log

If you compare it with the .dat you will see that the XML content is exactly the same:

[oracle@db192 trace]$ cat /u01/app/oracle/diag/rdbms/cdb1b/CDB1B/trace/myconfig.log
<?xml version="1.0" encoding="UTF-8"?>
<DRC Version="19.2.0.0.0" CurrentPath="True" Name="cdb1a">
  <DefaultState>ONLINE</DefaultState>
  <DRC_UNIQUE_ID>1934436017</DRC_UNIQUE_ID>
  <IntendedState>ONLINE</IntendedState>
  <FastStartFailoverOBID1>1673904225</FastStartFailoverOBID1>
  <FastStartFailoverOBID2>1673904226</FastStartFailoverOBID2>
  <FastStartFailoverOBID3>1673904227</FastStartFailoverOBID3>
  <Configuration_Name>cdb1a</Configuration_Name>
  <Member MemberID="1" CurrentPath="True" Enabled="True" MultiInstanced="True" Name="cdb1a">
    <IntendedState>PRIMARY</IntendedState>
    <DefaultState>PRIMARY</DefaultState>
...
      <OldPrimary>cdb1b</OldPrimary>
      <NewPrimary>cdb1a</NewPrimary>
      <Status>0</Status>
      <Timestamp>1000647508</Timestamp>
    </RoleChangeRecord>
  </RoleChangeHistory>
  <MIV>0</MIV>
  <PRIMARY_SITE_ID>513</PRIMARY_SITE_ID>
</DRC>

Once you have the export, I test it by removing the configuration and import it:

DGMGRL> import configuration from myconfig;
Succeeded. Run ENABLE CONFIGURATION to enable the imported configuration.
DGMGRL> enable configuration
Enabled.

This import must be done from the primary or you get an error.

Actually, the XML file is cool because it has more information than just the configuration commands. Here is the XML formatted in https://countwordsfree.com/xmlviewer:

Here I have the history of the last 10 switchovers which can be very useful. And then you want to know what is this timestamp unit?

In the XML file you will find many timestamps.

Most of them are EPOCH, the number of seconds since 01-JAN-1970:

<Status>
    <Severity>Success</Severity>
    <Error>0</Error>
    <Timestamp>1550611393</Timestamp>
  </Status>

This, 19-FEB-2019 21:23:13 which is exactly when I exported this file.

But look at the switchover history:

<RoleChangeRecord>
      <Event>PhysicalSwitchover</Event>
      <OldPrimary>cdb1a</OldPrimary>
      <NewPrimary>cdb1b</NewPrimary>
      <Status>0</Status>
      <Timestamp>1000674973</Timestamp>
    </RoleChangeRecord>
    <RoleChangeRecord>
      <Event>PhysicalSwitchover</Event>
      <OldPrimary>cdb1b</OldPrimary>
      <NewPrimary>cdb1a</NewPrimary>
      <Status>0</Status>
      <Timestamp>1000675048</Timestamp>
    </RoleChangeRecord>
    <RoleChangeRecord>
      <Event>PhysicalSwitchover</Event>
      <OldPrimary>cdb1a</OldPrimary>
      <NewPrimary>cdb1b</NewPrimary>
      <Status>0</Status>
      <Timestamp>1000675267</Timestamp>
    </RoleChangeRecord>
  </RoleChangeHistory>

Those ones are the number of seconds since 06-JUN-1987 and are actually the time when the switchover command started: 19.02.19 at 21:16:13, 21:17:28, and 21:21:07.