ORA-00494: enqueue [CF…

tarting background process CJQ0
Sat Oct 13 06:57:49 2012
CJQ0 started with pid=96, OS id=8195
Sat Oct 13 07:18:04 2012
Suspending MMON slave action kewrmafsa_ for 82800 seconds
Sat Oct 13 07:53:05 2012
Process 0x51000b020 appears to be hung while dumping
Sat Oct 13 07:58:28 2012
Current time = 315551548, process death time = 315488216 interval = 60000
Sat Oct 13 08:09:19 2012
Errors in file /pool02/test2/db/tech_st/11.1.0/log/diag/rdbms/test2/TEST2/trace/TEST2_arc1_26225.trc  (incident=30460):
ORA-00494: enqueue [CF] held for too long (more than 900 seconds) by 'inst 1, osid 26205'
Sat Oct 13 08:11:19 2012
WARNING: aiowait timed out 1 times
Incident details in: /pool02/test2/db/tech_st/11.1.0/log/diag/rdbms/test2/TEST2/incident/incdir_30460/TEST2_arc1_26225_i30460.trc
Sat Oct 13 08:16:53 2012
Attempting to kill process 0x51000b020 with OS pid = 5332
OSD kill succeeded for process 51000b020
Sat Oct 13 08:16:53 2012
Errors in file /pool02/test2/db/tech_st/11.1.0/log/diag/rdbms/test2/TEST2/trace/TEST2_arc2_20128.trc  (incident=30660):
ORA-00494: enqueue [CF] held for too long (more than 900 seconds) by 'inst 1, osid 26205'
Incident details in: /pool02/test2/db/tech_st/11.1.0/log/diag/rdbms/test2/TEST2/incident/incdir_30660/TEST2_arc2_20128_i30660.trc
Sat Oct 13 08:16:53 2012
Errors in file /pool02/test2/db/tech_st/11.1.0/log/diag/rdbms/test2/TEST2/trace/TEST2_arc0_26223.trc  (incident=30644):
ORA-00494: enqueue [CF] held for too long (more than 900 seconds) by 'inst 1, osid 26205'
Incident details in: /pool02/test2/db/tech_st/11.1.0/log/diag/rdbms/test2/TEST2/incident/incdir_30644/TEST2_arc0_26223_i30644.trc
Sat Oct 13 08:16:53 2012
Errors in file /pool02/test2/db/tech_st/11.1.0/log/diag/rdbms/test2/TEST2/trace/TEST2_arc3_26229.trc  (incident=30476):
ORA-00494: enqueue [CF] held for too long (more than 900 seconds) by 'inst 1, osid 26205'
Incident details in: /pool02/test2/db/tech_st/11.1.0/log/diag/rdbms/test2/TEST2/incident/incdir_30476/TEST2_arc3_26229_i30476.trc
Sat Oct 13 08:16:56 2012
Process W000 died, see its trace file
Sat Oct 13 08:17:16 2012
Sat Oct 13 08:17:16 2012
Killing enqueue blocker (pid=26205) on resource CF-00000000-00000000
Killing enqueue blocker (pid=26205) on resource CF-00000000-00000000
 by killing session 191.1
 by killing session 191.1
Killing enqueue blocker (pid=26205) on resource CF-00000000-00000000
 by terminating the process
Killing enqueue blocker (pid=26205) on resource CF-00000000-00000000
ARC1 (ospid: 26225): terminating the instance due to error 2103
 by terminating the process
Sat Oct 13 08:17:16 2012
Killing enqueue blocker (pid=26205) on resource CF-00000000-00000000
 by killing session 191.1
Killing enqueue blocker (pid=26205) on resource CF-00000000-00000000
 by terminating the process
Sat Oct 13 08:17:16 2012
Errors in file /pool02/test2/db/tech_st/11.1.0/log/diag/rdbms/test2/TEST2/trace/TEST2_smon_26207.trc:
ORA-02103: PCC: inconsistent cursor cache (out-of-range cuc ref)
Termination issued to instance processes. Waiting for the processes to exit
Sat Oct 13 08:17:26 2012
Instance termination failed to kill one or more processes
Instance terminated by ARC1, pid = 26225

 

Cause:

进程LGWR杀死了ckpt进程，进而引起数据库宕机。

l  数据库长时间等待CF enqueue，以致报：ORA-00494: enqueue [CF] held for too long (more than 900 seconds) by 'inst 1, osid 38356'

l  然后LGWR进程杀死了其阻塞进程，本例中LGWR进程kill了ckpt进程，所以导致数据库实例CRASH。

在这种情况下，我们也通常会发现联机日志文件切换相当频繁。

 

Solution:

Oracle通常建议联机日志切换时间间隔为20-30分钟，以降低控制文件的contention。

可以通过查询视图V$INSTANCE_RECOVERY.OPTIMAL_LOGFILE_SIZE，该列是以兆为单位，如果联机日志文件的实际大小小于该建议值的话，那么Oracle建议将文件大小调整为建议值。