Oracle队列等待: enq: IS - contention 阻塞启动

在网上看到有朋友启动数据库时,无法正常OPEN打开数据库,通过10046事件跟踪启动,获得如下日志输出:

PARSING IN CURSOR #1 len=20 dep=0 uid=0 oct=35 lid=0 tim=1996856932 hv=1361353028 ad='33ecfc5c'

alter database open

END OF STMT

PARSE #1:c=0,e=55194,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1996856924

BINDS #1:

WAIT #1: nam='enq: IS - contention' ela= 2999928 name|mode=1230176262 0=0 type=1 obj#=-1 tim=1999862398

WAIT #1: nam='enq: IS - contention' ela= 3000194 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2002886571

WAIT #1: nam='enq: IS - contention' ela= 3000147 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2005887755

*** 2011-07-15 09:19:25.797

WAIT #1: nam='enq: IS - contention' ela= 3000175 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2008888928

WAIT #1: nam='enq: IS - contention' ela= 2999845 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2011890945

WAIT #1: nam='enq: IS - contention' ela= 2999919 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2014892245

WAIT #1: nam='enq: IS - contention' ela= 3000160 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2017893430

*** 2011-07-15 09:19:37.803

WAIT #1: nam='enq: IS - contention' ela= 3000097 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2020894541

WAIT #1: nam='enq: IS - contention' ela= 2999605 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2023895772

WAIT #1: nam='enq: IS - contention' ela= 3000207 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2026896973

WAIT #1: nam='enq: IS - contention' ela= 3000061 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2029898088

*** 2011-07-15 09:19:49.806

WAIT #1: nam='enq: IS - contention' ela= 2999260 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2032898062

WAIT #1: nam='enq: IS - contention' ela= 2999582 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2035899453

WAIT #1: nam='enq: IS - contention' ela= 3000204 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2038900628

WAIT #1: nam='enq: IS - contention' ela= 2999892 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2041901560

这里的队列等待事件是:  enq: IS - contention, 这个等待极少见.可以通过字典表查询一下,获得其具体含义:

SQL> select KSQSTTYP,KSQSTEXPL from x$ksqst where KSQSTTYP='IS';

KS KSQSTEXPL

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

IS Enqueue used to synchronize instance state changes

可以看到这里的IS是 instance state的缩写,意味着,数据库在等待实例状态转换,从Mount到Open,在这个阶段被阻塞，根据进一步的跟踪文件，可以看到阻塞点:

PARSING IN CURSOR #3 len=142 dep=3 uid=0 oct=3 lid=0 tim=14891728852 hv=361892850 ad='33edaafc'

select /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1

END OF STMT

PARSE #3:c=0,e=53,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=3,tim=14891728848

BINDS #3:

kkscoacd

Bind#0

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0

  kxsbbbfp=054d1860  bln=22  avl=02  flg=05

  value=7

EXEC #3:c=0,e=174,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=3,tim=14891729170

WAIT #3: nam='row cache lock' ela= 2999602 cache id=3 mode=0 request=3 obj#=-1 tim=14894728832

WAIT #3: nam='row cache lock' ela= 2999768 cache id=3 mode=0 request=3 obj#=-1 tim=14897728773

WAIT #3: nam='row cache lock' ela= 3000277 cache id=3 mode=0 request=3 obj#=-1 tim=14900729179

*** 2011-07-15 12:54:20.637

WAIT #3: nam='row cache lock' ela= 3000060 cache id=3 mode=0 request=3 obj#=-1 tim=14903729401

WAIT #3: nam='row cache lock' ela= 2999894 cache id=3 mode=0 request=3 obj#=-1 tim=14906729519

这是在读取UNDO$时,处理第7号回滚段时出现了问题.可能是该回滚段出现了损坏.