Friday, January 29, 2010

ORA-16146: standby destination control file enqueue unavailable

In one of my standby db, I found this error in the alert log file: 
ORA-16146: standby destination control file enqueue unavailable.


Cause: high workload in the primary db: many update & insert & delete activities > many commits > many archived log files > slow I/O in standby db cannot work fast enough to process those archived log files.


ORA-16146 occurs in primary when the control file enqueue is hold by some process for long time and while archiving to the standby, control file enqueue is not available.

There is no error related to control file in the standby this means, the control file enqueue is not getting blocked but it is getting locked for long time.This can happen due to slow I/O to the control file.

Solution: move the control files on the standby database to the faster disk and check whether issue reproduces.

 The standby controlfile is updated by several sources namely the RFS  processes and the local process which performs recovery or the MRP process  which is responsible for sustained recovery at the standby. The standby's  controlfile is updated with the latest logfile when its shipped from the source to the destination. The standby controlfile is updated with the logfile information for the sustained recovery to proceed. Also the SRL's need to be updated when the online logs are copied. The error 16146 is returned when the controlfile enqueue in the standby cannot be obtained. There were bugs before reported where primary hung because of the standby CF enqueue problems. Actually this error can indicate latent controlfile
 activities on the remote side and hence the stack might not be necessary.
 .
 - All processes waiting on CF and causing 16146
 - This does not affect the physical standby
 - This error on itself might indicate OS specific problems
 - The holder here is waiting for "logfile sequential read"






In primary db, I got this error: 
ORA-16198: Timeout incurred on internal channel during remote archival
LGWR: Error 16198 closing archivelog file 'stbydb'


Cause:
The primary ORA-16198 is happening because of the current settings of remote archiving.
 From the primary diagnostic information:
log_archive_dest_2   service=stbydb LGWR SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="stbbdb" register net_timeout=180 valid_for=(online_logfile,primary_role)

Solution: change the LGWR SYNC to
LGWR ASYNC since the protection_mode and protection_level is set to  MAX_PERFORMANCE.

SQL> alter system set log_archive_dest_2   service='stbydb LGWR SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="stbbdb" register net_timeout=180 valid_for=(online_logfile,primary_role)';

0 Comments:

Post a Comment

Subscribe to Post Comments [Atom]

<< Home