More on setting redo routes property when adding a terminal standby database to your data guard broker configuration

As described in an earlier post, it is possible to set up a "terminal standby database" which fetches its redo information from another standby database, rather than directly from the primary database.

It is quite facinating to see how the data guard broker simplifies this setup for the DBA.

In a recent excercise @ work, I had a data guard configuration consisting of 1 primary and 3 physical standby database.

A fourth was to be added as a terminal standby database.

First, clone the new database for standby.

You can use any of the databases in the configuration as a source, from which to create a terminal database, both primary or any of the mounted physical standby databases.

When the clone is finished, add the new database to the broker config:

dgmgrl -echo sys/password@primdb_dgmgrl.oric.no as sysdba

Welcome to DGMGRL, type "help" for information.
Connected to "primdb"
Connected as SYSDBA.

Add the new database:

DGMGRL> add database 'tstby' as connect identifier is tstby.oric.no maintained as physical;
add database 'tstby' as connect identifier is tstb.oric.no maintained as physical;
Database "tstby" added
DGMGRL> show configuration;
show configuration;

Configuration - dgconfig1

  Protection Mode: MaxPerformance
  Members:
  primdb     - Primary database
    stby1     - Physical standby database
    stby2     - Physical standby database
    stby3     - Physical standby database
    tstby    - Physical standby database (disabled)

Fast-Start Failover: DISABLED

Notice how all the standby databases are indented directly underneath the primary database, indicating that they receive their redo information directly from the primary database.

I then add the redoroutes property to the primary:

DGMGRL> edit database primdb set property redoroutes='(LOCAL:stby1,stby2,stby3 ASYNC)(stby1:tstby ASYNC)';
edit database primdb set property redoroutes='(LOCAL:stby1,stby2,stby3 ASYNC)(stby1:tstby ASYNC)';
Property "redoroutes" updated

Add the redoroutes property for when the primary and the choosen standby switches roles:

DGMGRL> edit database stby1 set property redoroutes='(LOCAL:primdb,stby2,stby3 ASYNC)(primdb:tstb ASYNC)';
edit database stby1 set property redoroutes='(LOCAL:primdb,stby2,stby3 ASYNC)(primdb:tstb ASYNC)';
Property "redoroutes" updated

Note that both of these rules must be set, otherwise your terminal standby database will not receive logs. You will see messages like the follwing:

Configuration - dgconfig1

  Protection Mode: MaxPerformance
  Members:
  primdb     - Primary database
    stby1  - Physical standby database
    stby2  - Physical standby database
    stby3  - Physical standby database

  Members Not Receiving Redo:
  tstby  - Physical standby database
    Error: ORA-16685: database does not receive redo data

View the configuration again
You will see that the broker has understood that the tstb is acting like a terminal standby database for physical standby database "stby2":

DGMGRL> show configuration;
show configuration;

Configuration - dgconfig1

  Protection Mode: MaxPerformance
  Members:
  primdb     - Primary database
    stby1  - Physical standby database
      tstby  - Physical standby database (disabled)
    stby2 - Physical standby database
    stby3 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 27 seconds ago)

Finally, enable the database:

DGMGRL> enable database 'tstb';
enable database 'tsbt';
Enabled.

The output you should see at the end is:

DGMGRL> show configuration;
show configuration;

Configuration - dgconfig1

  Protection Mode: MaxPerformance
  Members:
  primdb     - Primary database
    stby1  - Physical standby database
      tstby  - Physical standby database (receiving current redo)
    stby2 - Physical standby database
    stby3 - Physical standby database