Search Blog Post

Saturday, December 21, 2013

ORA-00494: enqueue [CF] held for too long (more than 900 seconds)

Problem: 
Today morning one of our development instance crashed due to following error found in alert log file.

Contents of alert log file:-
Sat Dec 21 02:17:04 2013
Errors in file /mnt/DEVapps/oracle/trcdevdb/11.2.0/dbhome_1/log/diag/rdbms/trcdev/TRCDEV/trace/TRCDEV_ora_5707.trc  (incident=81065):
ORA-00494: enqueue [CF] held for too long (more than 900 seconds) by 'inst 1, osid 24599'
Sat Dec 21 02:17:14 2013
minact-scn: useg scan erroring out with error e:12751
Suspending MMON action 'Block Cleanout Optim, Undo Segment Scan' for 82800 seconds
Incident details in: /mnt/DEVapps/oracle/trcdevdb/11.2.0/dbhome_1/log/diag/rdbms/trcdev/TRCDEV/incident/incdir_81065/TRCDEV_ora_5707_i81065.trc
Sat Dec 21 02:18:33 2013
Killing enqueue blocker (pid=24599) on resource CF-00000000-00000000 by (pid=5707)
 by killing session 3.1
Killing enqueue blocker (pid=24599) on resource CF-00000000-00000000 by (pid=5707)
 by terminating the process
USER (ospid: 5707): terminating the instance due to error 2103
Sat Dec 21 02:18:49 2013
Termination issued to instance processes. Waiting for the processes to exit
Instance termination failed to kill one or more processes
Instance terminated by USER, pid = 5707



Cause:-
The Possible reasons for this sudden instance crash are:
  • Database running out of memory
  • Due to high load,server became unresponsive
  • Server is running out of memory and/or swap.
  • High concurrency on resources, IO waits and contention 
Solution:-
In our development database, we reviewed the alert log and trace files related to this ORA error and we found out that there was high load on the Linux server due to RMAN hot backup running, export of a  huge schema going on simultaneously.

At the time of this instance crash, there was very high load at the server  and that's why the kill blocker interface killed the background process and so the instance crashed.


  • In order to prevent a background blocker from being killed, you can set the following init.ora parameter to 1 (default is 3).
_kill_enqueue_blocker=1


  • If you want to avoid the kill of the blocker (background or non-background process) you can set _kill_controlfile_enqueue_blocker=false.


With these parameter, if the enqueue holder is a background process, then it will not be killed, therefore the instance will not crash 



Note:- Don't set undocumented hidden parameters until advise by oracle support.

References:-
Database Crashes With ORA-00494 [ID 753290.1]
Ora-00494: Enqueue [Cf] Held For Too Long Causing Database To Crash [ID 1101862.1]  

PHYSICAL: ORA-00494: enqueue [CF] held for too long after Node Crash [ID 747071.1]

ORA-00494 Or ORA-600 [2103] During High Load After 10.2.0.4 Upgrade (Doc ID 779552.1)


12 comments:

  1. Very helpful. Thank you

    ReplyDelete
  2. what will impact on database performance if i set these hidden parameter.?

    ReplyDelete