日期:2014-05-16  浏览次数:20473 次

Oracle enq: CF - contention导致数据挂起
接到客户电话,数据库失去响应,所有业务挂起
数据库版本为

引用
SQL> select * from v$version where rownum=1;

BANNER
----------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

主机版本为
引用
$ uname -a
HP-UX rp7410 B.11.23 U 9000/800 1943000290 unlimited-user license

查看数据库等待信息,可以看到enq: CF - contention导致数据挂起
引用
SQL> select sid,event from v$session_wait where event not like '%rdbms%' and event not like '%SQL%';
       SID EVENT
---------- ----------------------------
       164 control file sequential read
       200 Streams AQ: qmn slave idle wait
       205 Streams AQ: qmn coordinator idle wait
       206 Streams AQ: waiting for time management or cleanup tasks
       211 enq: CF - contention
       212 enq: CF - contention
       216 enq: CF - contention
       219 smon timer
       221 enq: CF - contention
       225 pmon timer


于是马上查看阻塞会话信息
引用
SQL> select SID,SERIAL#  from v$session where sid in (select sid from v$lock where block=1);

       SID    SERIAL#
---------- ----------
       164       7097
       220          1

首先在数据库层面将会话杀死
引用
SQL> alter system kill session '220,1';  
alter system kill session '220,1'
*
ERROR at line 1:
ORA-00029: session is not a user session

提示会话220不是用户会话,那它是什么呢?
引用
SQL> select SID,AUDSID,USERNAME,TYPE,PROGRAM
  2  FROM V$SESSION WHERE SID='220';

       SID     AUDSID USERNAME                       TYPE
---------- ---------- ------------------------------ ----------
PROGRAM
------------------------------------------------
       220          0                                BACKGROUND
oracle@rp4440 (CKPT)

可以看到是Oracle后台CKPT进程。
引用
SQL>  alter system kill session '164,7097';
alter system kill session '164,7097'
*
ERROR at line 1:
ORA-00031: session marked for kill


进一步查看操作系统进程
引用
SQL>  select SPID from v$process where addr=(select PADDR from v$session where sid=164);

SPID
------------
24660

SQL> !ps -ef|grep 24660
  oracle 24660     1  0 12:49:16 ?         0:00 oraclexxxi (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
  oracle 24706 24701  0 12:56:33 pts/ta    0:00 /usr/bin/sh -c ps -ef|grep 24660
&nbs