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
