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

网上一个朋友遇到数据库启动不了，具体信息如下

数据库版本： ORA 10G R2

主机 HPUNIX

现象： 当startup之后，数据库一直停留在 database mounted状态。一直Open不鸟~。

alter log 文件(节选)如下：

Fri Nov 11 09:31:01 2011

Completed: ALTER DATABASE   MOUNT

Fri Nov 11 09:31:08 2011

ALTER DATABASE OPEN

Fri Nov 11 09:40:32 2011

Errors in file /opt/oracle/oracle1/admin/gminer/bdump/gminer_dbw0_3122.trc:

ORA-00240: control file enqueue held for more than 120 seconds

Fri Nov 11 09:47:18 2011

Errors in file /opt/oracle/oracle1/admin/gminer/bdump/gminer_dbw0_3122.trc:

Fri Nov 11 10:02:53 2011

Errors in file /opt/oracle/oracle1/admin/gminer/udump/gminer_ora_3555.trc:

ORA-00494: enqueue [CF] held for too long (more than 900 seconds) by 'inst 1, osid 3122'

Fri Nov 11 10:02:55 2011

System State dumped to trace file /opt/oracle/oracle1/admin/gminer/udump/gminer_ora_3555.trc

Killing enqueue blocker (pid=3122) on resource CF-00000000-00000000

by killing session 54.1

可以看到有个进程PID=3122，持有CF锁的时间太长，ORACLE将该进程杀掉。

MOS上有一篇文章，通过设置_kill_enqueue_blocker=1 可以阻止ORACLE杀掉核心进程，避免了数据库实例CRASH。

这个问题还需要进一步的分析，为啥PID=3122的进程持有CF锁的时间那么久。

ORA-00494 During High Load After 10.2.0.4 Upgrade [ID 779552.1]

修改时间07-OCT-2011类型PROBLEM状态MODERATED

In this Document

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.4 and later   [Release: 10.2 and later ]

Information in this document applies to any platform.

***Checked for relevance on 07-Oct-2011***

Symptoms

In 10.2.0.4 database alert log the following error appears:

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

This error can also be accompanied by ORA-600 [2103] which is basically the same problem - a process was unable to obtain the CF enqueue within the specified timeout (default 900 seconds).

This behavior can be correlated with server high load and high concurrency on resources, IO waits and contention, which keep the Oracle background processes from receiving the necessary resources.

Cause

The problem has been first investigated in

- 'DATABASE CRASHES WITH ORA-494 AFTER UPGRADE TO 10.2.0.4'

and then in unpublished

Bug 7914003 'KILL BLOCKER AFTER ORA-494 LEADS TO FATAL BG PROCESS BEING KILLED'

Solution

This kill blocker interface / ORA-494 was introduced in 10.2.0.4. This new mechanism will kill *any* kind of blocking process, non-background or background.

The difference will be that if the enqueue holder is a non-background process, even if it is killed, the instance can function without it.

In case the holder is a background process, for example the LGWR, the kill of the holder leads to instance crash.

If you want to avoid the kill of the blocker (background or non-background process), you can set

_kill_controlfile_enqueue_blocker=false.

This means that no type of blocker will be killed anymore although the resolution to this problem should focus on why the process is holding the enqueue for so long. Also, you may prefer to only avoid killing background processes, since they are vital to the instance, and you may want to allow the killing of non-background blokers.

This has been addressed in a secondary bug - unpublished Bug 7914003 'KILL BLOCKER AFTER ORA-494 LEADS TO FATAL BG PROCESS BEING KILLED' which was closed as Not a bug.

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=1With this parameter, if the enqueue holder is a background process, then it will not be killed, therefore the instance will not crash. If the enqueue holder is not a background process, the new 10.2.0.4 mechanism will still try to kill it.

The reason why the blocker interface with ORA-494 is kept is because, in most cases, customers would prefer crashing the instance than having a cluster-wide hang.