Now we will discuss some general Data Guard broker issues.
If you ever start an instance with PFILE instead of SPFILE, DMON will not be able to communicate with the databases. SPFILE is mandatory for communicating with remote destinations to fetch required information from the broker configuration file and server parameter files. This issue can eventually be identified from DGMGRL by retrieving configuration information as follows:
DGMGRL> show configuration; Configuration - PACKT Protection Mode: MaxPerformance Databases: TURKEY_UN - Primary database INDIA_UN - Physical standby database Error: ORA-16797: database is not using a server parameter file Fast-Start Failover: DISABLED Configuration Status: ERROR
Create a new SPFILE on the standby system from PFILE, and bounce the standby database as follows:
SQL> create spfile from pfile; File created. SQL> shutdown immediate SQL> startup mount
DGMGRL> show configuration; Configuration - PACKT Protection Mode: MaxPerformance Databases: TURKEY_UN - Primary database INDIA_UN - Physical standby database UK_UN - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS
For a database to open, it must have consistency over all the data files. This can occur in case the recovery has been terminated in the previous sessions or the standby control file SCN is has not been synchronized with the data files as shown in the following query:
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u02/app/oracle/oradata/orcl/system01.dbf'
DGMGRL> show database 'INDIA_UN';
Database - INDIA_UN
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Real Time Query: OFF
Instance(s):
INDIA
Database Warning(s):
ORA-16770: Redo Apply not started since physical standby database is opening
Database Status:
WARNINGNow the database status is in MOUNT. Either start Redo Apply from DGMGRL or bounce DMON so that DMON will initiate MRP to perform a recovery. Once enough number of archived logs are applied to provide consistency, you can open the database.
Usually, the ORA-16737 error occurs if there is any communication problem with the standby database. You can query the LogXptStatus property to see the error message and you can also review the Data Guard broker logfile as follows:
DGMGRL> show database TURKEY_UN 'LogXptStatus'; LOG TRANSPORT STATUS PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME STATUS TURKEY INDIA_UN ORA-12541: TNS:no listener DGMGRL> show database 'INDIA_UN'; Database - INDIA_UN Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: (unknown) Apply Lag: (unknown) Real Time Query: OFF Instance(s): INDIA Database Status: DGM-17016: failed to retrieve status for database "INDIA_UN" ORA-12541: TNS:no listener ORA-16625: cannot reach database "INDIA_UN"
Check the listener of the status and start the listener. Wait until the Oracle service is registered with the listener, or you can manually register it as follows:
SQL> alter system register;
Ensure that the service is registered with the listener.
Usually, the ORA-16715 error occurs if there is any inconsistency between the initialization parameters and configuration file. By querying the database status from DGMGRL, we can see the parameter that is not consistent.
DGMGRL> show database 'TURKEY_UN'; Database - TURKEY_UN Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): TURKEY Warning: ORA-16715: redo transport-related property DelayMins of standby database "INDIA_UN" is inconsistent Database Status: WARNING SQL> select delay_mins,destination from v$archive_dest where dest_id=2; DELAY_MINS DESTINATION ---------- ------------ 10 india DGMGRL> show database 'TURKEY_UN' 'DelayMins'; DelayMins = '0'
From the previous two queries, we can see that there is inconsistency between SPFILE and the configuration files. Either we have to edit the configuration file's property value to 10 or change the initialization parameter's value to 0.
One example of an ORA-12514 error is a post-switchover case. After performing a switchover using DGMGRL, Data Guard requires a shutdown and startup of both the primary and standby databases. This issue can occur if any necessary entry is missing in the listener.ora file. DGMGRL is unable to connect to the database after it has been stopped while performing the switchover.
The command for the current listener is as follows:
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/home/oracle/product/11.2.0/db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = india_un) (SID_NAME = INDIA) (ORACLE_HOME = /u01/home/oracle/product/11.2.0/db_1) ) )
Add the correct entry of GLOBAL_DBNAME in the SID list description of the listener. This step is applicable for both the primary and standby databases.
Format GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain as follows:
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/home/oracle/product/11.2.0/db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = india_un_DGMGRL) (SID_NAME = INDIA) (ORACLE_HOME = /u01/home/oracle/product/11.2.0/db_1) ) ) DGMGRL> show database 'TURKEY_UN' "StaticConnectIdentifier" StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))(CONNECT_DATA=(SERVICE_NAME=turkey_un_DGMGRL)(INSTANCE_NAME=TURKEY)(SERVER=DEDICATED)))'