Recently I built a Data Guard environment on two Exadatas with three RAC databases and did a lot of tests. The Show Configuration is probably the most frequent command I used in DG Broker.
When running show configuration from dgmgrl, we usually see the same result no matter where the command is executed, primary or any standby databases. During one switchover test, I run into a weird situation. The show configuration command can return me three different results from one primary database and two standby databases, just like above the image above (cat changes into a lion from the mirror). Here are the result:
Primary Database (wzxdb)
DGMGRL> show configuration Configuration - DG_Config Protection Mode: MaxPerformance Databases: wzxdb - Primary database wzsdb - Physical standby database Error: ORA-16664: unable to receive the result from a database wzpdb - Physical standby database Fast-Start Failover: DISABLED Configuration Status: ERROR
Checked the DG Broker log at /u01/app/oracle/diag/rdbms/wzxdb/wzkdb1/trace/drcwzkdb1.log, it has something below
07/16/2014 09:30:20 Site wzsdb returned ORA-16664. Data Guard Broker Status Summary: Type Name Severity Status Configuration DG_Config Warning ORA-16607 Primary Database wzxdb Success ORA-00000 Physical Standby Database wzsdb Error ORA-16664 Physical Standby Database wzpdb Success ORA-00000
Let’s continue to check status for the standby databases.
1st Standby Database, wzpdb
DGMGRL> show configuration Configuration - DG_Config Protection Mode: MaxPerformance Databases: wzxdb - Primary database wzsdb - Physical standby database wzpdb - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS
2nd Standby Database, wzsdb
DGMGRL> show configuration Configuration - DG_Config Protection Mode: MaxPerformance Databases: wzxdb - Primary database wzsdb - Physical standby database wzpdb - Physical standby database Fast-Start Failover: DISABLED Configuration Status: ORA-16501: the Data Guard broker operation failed ORA-16625: cannot reach database "wzxdb" DGM-17017: unable to determine configuration status
The first thing I checked whether Data Guard replication was still working or not. Did a few switch logfile from primary and can see the logs were replicated to two standby databases. Verified data guard related parameters, tnsnames and listener entries in all databases. Found no issue there. At this moment, I narrowed down the issue to DG Broker and suspect it could relate to DG Broker configuration. After a few tries, I found a solution to fix this issue.
1. On primary db (wzxdb), remove the database wzsdb from DG Broker configuration, then add it back.
2. On standby db (wzsdb), bounce the database.
Here are the detail steps:
Primary Database (wzxdb) DGMGRL> remove database wzsdb Removed database "wzsdb" from the configuration DGMGRL> add database wzsdb as connect identifier is wzsdb; Database "wzsdb" added DGMGRL> enable configuration Enabled. DGMGRL> show configuration Configuration - DG_Config Protection Mode: MaxPerformance Databases: wzxdb - Primary database wzpdb - Physical standby database wzsdb - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS
After fixing the issue in primary database, let’s goto the standby database with issues. It still has the same error from show configuration command. So I went ahead bouncing the database.
srvctl stop database -d wzsdb
srvctl start database -d wzsdb
DGMGRL> show configuration Configuration - DG_Config Protection Mode: MaxPerformance Databases: wzxdb - Primary database wzpdb - Physical standby database wzsdb - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS
Here is part of the content from data guard broker log on this standby database.
07/16/2014 09:29:58 Failed to send message to site wzxdb. Error code is ORA-16501. 07/16/2014 09:31:09 REMOVE DATABASE wzsdb [PRESERVE DESTINATIONS] Failed to send message to site wzxdb. Error code is ORA-16501. 07/16/2014 09:31:37 DMON Deregistering service wzsdb_DGB with listener(s) 07/16/2014 09:32:12 DMON Registering service wzsdb_DGB with listener(s) 07/16/2014 09:32:15 Apply Instance for Database wzsdb set to wzdb1 07/16/2014 09:32:19 Failed to send message to site wzxdb. Error code is ORA-16501. Command EDIT DATABASE wzsdb SET PROPERTY ActualApplyInstance = wzdb1 completed 07/16/2014 09:32:29 Command ENABLE CONFIGURATION completed 07/16/2014 09:32:48 Failed to send message to site wzxdb. Error code is ORA-16501. 07/16/2014 09:33:04 Failed to send message to site wzxdb. Error code is ORA-16501. 07/16/2014 09:33:23 Failed to connect to remote database wzxdb. Error is ORA-12154 Failed to send message to site wzxdb. Error code is ORA-16501. 07/16/2014 09:34:15 Failed to send message to site wzxdb. Error code is ORA-16501. 07/16/2014 09:35:30 Failed to send message to site wzxdb. Error code is ORA-16501. 07/16/2014 09:36:45 Failed to send message to site wzxdb. Error code is ORA-16501. 07/16/2014 09:38:00 Failed to send message to site wzxdb. Error code is ORA-16501. 07/16/2014 09:38:27 Data Guard Broker shutting down RSM0 successfully terminated 07/16/2014 09:38:29 >> DMON Process Shutdown << 07/16/2014 09:39:18 >> Starting Data Guard Broker bootstrap << Broker Configuration File Locations: dg_broker_config_file1 = "+RECO/wzsdb/dataguardconfig/dgb_config02.ora" dg_broker_config_file2 = "+DATA/wzsdb/dataguardconfig/dgb_config01.ora" 07/16/2014 09:39:22 DMON Registering service wzsdb_DGB with listener(s) Broker Configuration: "DG_Config" Protection Mode: Maximum Performance Fast-Start Failover (FSFO): Disabled, flags=0x0, version=0 Primary Database: wzxdb (0x03010000) Standby Database: wzsdb, Enabled Physical Standby (0x01010000) Standby Database: wzpdb, Enabled Physical Standby (0x02010000) 07/16/2014 09:39:25 wzsdb version check successfully completed wzsdb has current configuration metadata, completing bootstrap Creating process RSM0 07/16/2014 09:39:28 Apply Instance for Database wzsdb set to wzdb1 07/16/2014 09:39:37
We can see it seems the standby database received commands like REMOVE DATABASE wzsdb and ENABLE CONFIGURATION from primary DG Broker, but just can not send the message back to primary database. After bouncing the standby database, it returned normal and can communicate back to primary database.
Finally, all databases have this SUCCESS status no matter where I run the show configuration command.