Oracle 10.2.0.4 High Load Triggers ORA-00494 Error

THERE IS AN ARTICLE ON MOS DEVOTED TO THIS ISSUE, WITH REFERENCE TO: (ID 779552.1).

In Oracle 10.2.0.4, the alert log may have the following error:

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

THIS ERROR USUALLY OCCURS WITH THE ORA-600 ( 2103 ) . THE REASONS FOR THEIR EMERGENCE ARE THE SAME. THE PROCESS CANNOT GET THE CF QUEUE FOR A SPECIFIED AMOUNT OF TIME (THE DEFAULT IS 900 SECONDS).

Typically only when the system is highly loaded, high concurrent, IO waiting and competing, Oracle processes occupy the associated resources without being released caused.

there are 2 bugs that can cause this problem:

（1） Bug7692631 - 'DATABASE CRASHES WITH ORA-494 AFTER UPGRADE TO 10.2.0.4'

（2）unpublished Bug 7914003 'KILL BLOCKER AFTER ORA-494 LEADS TO FATALBG PROCESS BEING KILLED'

solution:

In the Oracle mechanism of 10.2.0.4, all block processes, both background and non-background processes, are klkilled. Note that:

(1) If a non-background process has a CF enqueue, then kill the process, then the instance will function properly.

(2) If it is a background process, such as LGWR holder, then kill LGWR will cause db crash.

to avoid kill dropping blocker processes (background and non-background processes), you can set parameters:

_kill_controlfile_enqueue_blocker=false.

this way, all blocker processes will not be dropped by kill. even if the process is hold for a long time. in general, the best case scenario is to avoid kill background processes and allow kill non-background processes.

in order to prevent background processes from being kill, you can set the parameter in the init.ora file: _kill_enqueue_blocker s1, which defaults to 3.

when this parameter is set, if enqueue is hellod by a background process, the background process does not give kill. so instances don't crash. if enqueue is hellod by a non-background process, then the mechanism of 10.2.0.4 will still attempt to go to the kill non-background process.

Oracle retains ORA-494 because users prefer instance crashes, and db hangs if they don't have a crash.

-------------------------------------------------------------------------------------------------------

QQ:492913789

Email:[email protected]

Blog: http://www.cndba.cn/dave

Weibo: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

Linkedin: http://cn.linkedin.com/in/tianlesoftware

DBA1 GROUP: 62697716 (FULL); DBA2 GROUP: 62697977 (FULL) DBA3 GROUP: 62697850 (FULL)

DBA SUPERGROUP: 63306533 (FULL); DBA4 GROUP: 83829929 DBA5 GROUP: 142216823

DBA6 GROUP: 158654907 CHAT GROUP: 40132017 CHAT GROUP 2: 69087192

--Adding groups is required to explain the relationship between Oracle table space and data files in the notes, otherwise the request will be rejected