Dataguard primary database reported ORA-16146 error Solution

Dataguard primary database reported ORA-16146 error Solution

Product Version 10.2.0.4
Operating System Oracle Solaris on iSCSI (64-bit) 5.10

1. the alert Log is as follows:
Master Database alert log
---------------------
Tue Mar 11 14:30:47 2014
LNS: Standby redo logfile selected for thread 2 sequence 192246 for destination LOG_ARCHIVE_DEST_2
Tue Mar 11 14:37:00 2014
Errors in file/oracle/admin/dbrac/bdump/dbrac2_arc9_6512.trc:
ORA-16146: standby destination control file enqueue unavailable
Tue Mar 11 14:37:00 2014
Master background archival failure: 16146
Tue Mar 11 14:40:07 2014
Errors in file/oracle/admin/dbrac/bdump/dbrac2_arc9_6512.trc:
ORA-16146: standby destination control file enqueue unavailable
 

-------------------------------------- Split line --------------------------------------

 

References:

Important configuration parameters of Oracle Data Guard

Configure Oracle 11g Data Guard based on the same host

Explore Oracle 11g elastic uard

Oracle Data Guard (RAC + DG) archive deletion policies and scripts

Role conversion for Oracle Data Guard

FAL gap in Oracle Data Guard logs

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby

-------------------------------------- Split line --------------------------------------

Standby Database alert log
---------------------
Tue Mar 11 14:29:44 2014
Primary database is in maximum performance mode
RFS [19]: Successfully opened standby log 8: '+ DATA/dbrac_standby/onlinelog/group_8.473.823623967'
Tue Mar 11 14:32:12 2014
Primary database is in maximum performance mode
RFS [13]: Successfully opened standby log 12: '+ DATA/dbrac_standby/onlinelog/group_12.1879.823705847'
Tue Mar 11 14:34:26 2014
Media Recovery Log + DATA/dbrac_standby/archivelog/2014_03_11/thread_2_seq_192246.509.841933921
Tue Mar 11 14:34:48 2014
Media Recovery Log + DATA/dbrac_standby/archivelog/2014_03_11/thread_eclipseq_192133.1784.841933765
Tue Mar 11 14:35:44 2014
Primary database is in maximum performance mode
RFS [19]: Successfully opened standby log 7: '+ DATA/dbrac_standby/onlinelog/group_7.492.823623957'
Tue Mar 11 14:37:37 2014
Media Recovery Waiting for thread 1 sequence 193464 (in transit)
Tue Mar 11 14:38:47 2014
Media Recovery Log + DATA/dbrac_standby/archivelog/2014_03_11/thread_eclipseq_192134.2163.841934073
Tue Mar 11 14:40:01 2014
Media Recovery Waiting for thread 2 sequence 192247 (in transit)
 

Ii. Analysis and Handling
Analysis ideas:
ORA-16146 errors indicate that a process held CF enqueue (control file lock) for more than 900 seconds has not been released, resulting in other processes unable to obtain CF enqueue,
In fact, this error message is not accurate enough. It is not only waiting for the CF enqueue of the slave database, but also reported when waiting for the CF enqueue of the master database.
 
Possible causes of ORA-16146 errors:
1. IO performance is slow, resulting in too long IO operation time.
2. A holding CF enqueue (control file lock) is not released for more than 900 seconds.
3. Excessive information in the control file causes the query control file to take too long.
4. This error can be ignored if there is only a ORA-16146 and no other problems.
 
Further check:
1. OSW or other OS Resource Monitoring Data
2. query the master database and slave database respectively:
SQL> select count (*) from v $ archived_log;
SQL> select count (*) from v $ log_history;
SQL> select count (*) from v $ archived_log;
SQL> select count (*) from v $ log_history;
SQL> show parameter CONTROL_FILE_RECORD_KEEP_TIME;
The query is as follows:
Master database and slave Database
Select count (*) from v $ archived_log; 18956 18956
Select count (*) from v $ log_history; 36272 36272
Select count (*) from v $ archived_log; 18956 18956
Select count (*) from v $ log_history; 36272 36272
Show parameter CONTROL_FILE_RECORD_KEEP_TIME; 7 10
3. Sun:/var/adm/messages (master and slave databases)
 
Analysis and Solution
By querying v $ archived_log and v $ log_history, a large number of historical log information is found. Therefore, it is likely that the number of logs recorded in the control file is very large,
Query consumes a lot of time.
Modify the parameters as follows:
Alter system set CONTROL_FILE_RECORD_KEEP_TIME = 3 scope = BOTH;
Through a few weeks of observation, no more ORA-16146, the problem solved!