« 数据库架构设计与性能优化实践 技术交流会 | Blog首页 | Oracle数据恢复:ORA-600 4097错误解决案例一则 »
Oracle队列等待: enq: IS - contention 阻塞启动
作者:eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接:https://www.eygle.com/archives/2011/07/oracle_enq_is_contention.html
链接:https://www.eygle.com/archives/2011/07/oracle_enq_is_contention.html
在网上看到有朋友启动数据库时,无法正常OPEN打开数据库,通过10046事件跟踪启动,获得如下日志输出:
PARSING IN CURSOR #1 len=20 dep=0 uid=0 oct=35 lid=0 tim=1996856932 hv=1361353028 ad='33ecfc5c'alter database openEND OF STMTPARSE #1:c=0,e=55194,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1996856924BINDS #1:WAIT #1: nam='enq: IS - contention' ela= 2999928 name|mode=1230176262 0=0 type=1 obj#=-1 tim=1999862398WAIT #1: nam='enq: IS - contention' ela= 3000194 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2002886571WAIT #1: nam='enq: IS - contention' ela= 3000147 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2005887755*** 2011-07-15 09:19:25.797WAIT #1: nam='enq: IS - contention' ela= 3000175 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2008888928WAIT #1: nam='enq: IS - contention' ela= 2999845 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2011890945WAIT #1: nam='enq: IS - contention' ela= 2999919 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2014892245WAIT #1: nam='enq: IS - contention' ela= 3000160 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2017893430*** 2011-07-15 09:19:37.803WAIT #1: nam='enq: IS - contention' ela= 3000097 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2020894541WAIT #1: nam='enq: IS - contention' ela= 2999605 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2023895772WAIT #1: nam='enq: IS - contention' ela= 3000207 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2026896973WAIT #1: nam='enq: IS - contention' ela= 3000061 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2029898088*** 2011-07-15 09:19:49.806WAIT #1: nam='enq: IS - contention' ela= 2999260 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2032898062WAIT #1: nam='enq: IS - contention' ela= 2999582 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2035899453WAIT #1: nam='enq: IS - contention' ela= 3000204 name|mode=1230176262 0=0 type=1 obj#=-1 tim=2038900628WAIT #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#=:1END OF STMTPARSE #3:c=0,e=53,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=3,tim=14891728848BINDS #3:kkscoacdBind#0oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0kxsbbbfp=054d1860 bln=22 avl=02 flg=05value=7EXEC #3:c=0,e=174,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=3,tim=14891729170WAIT #3: nam='row cache lock' ela= 2999602 cache id=3 mode=0 request=3 obj#=-1 tim=14894728832WAIT #3: nam='row cache lock' ela= 2999768 cache id=3 mode=0 request=3 obj#=-1 tim=14897728773WAIT #3: nam='row cache lock' ela= 3000277 cache id=3 mode=0 request=3 obj#=-1 tim=14900729179*** 2011-07-15 12:54:20.637WAIT #3: nam='row cache lock' ela= 3000060 cache id=3 mode=0 request=3 obj#=-1 tim=14903729401WAIT #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 |