eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 数据库架构设计与性能优化实践 技术交流会 | Blog首页 | Oracle数据恢复:ORA-600 4097错误解决案例一则 »

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号回滚段时出现了问题.可能是该回滚段出现了损坏.




历史上的今天...
    >> 2014-07-18文章:
    >> 2013-07-18文章:
    >> 2010-07-18文章:
    >> 2006-07-18文章:
    >> 2005-07-18文章:

无觅

By eygle on 2011-07-18 08:38 | Comments (0) | FAQ | 2843 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com