无法删除分区表 library cache lock

最新推荐文章于 2024-01-10 15:36:21 发布

Makaveli

最新推荐文章于 2024-01-10 15:36:21 发布

阅读量1k

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle故障解决案例文章标签： cache library session 数据库服务器 c aix

本文链接：https://blog.youkuaiyun.com/Makaveli/article/details/7063431

oracle故障解决案例专栏收录该内容

5 篇文章

订阅专栏

遇到AIX环境下Oracle RAC系统中分区表删除操作挂起的问题，通过HangAnalysis发现是由于一个长时间运行的job进程阻塞了多个进程导致的。最终，通过终止该job进程，成功解决了分区表删除操作无法执行的问题。

   今天值班的同事打电话来说在做一个删除分区表的时候一直hang住，无法删除，环境是aix上的oracle9i rac，让我帮忙看看。

他说他在节点二上执行的这个操作，半天都没有响应，以前执行同样的操作没有这么长时间，所以请我帮忙看看什么原因。

登录上节点二的数据库服务器查看执行此操作的session，发现有library cache lock等待事件，liarary cache lock等待事件一般是由于

通过hanganalyze来看，sid为44的是个应用查询语句不涉及到此次要删除的分区表，难道造成liarary cache lock hang的就是自己本身，当时就有点困惑了，自己怎么会hang住自己呢？（当时忽略了rac的环境，导致走了点弯路）

==============
HANG ANALYSIS:
==============
Found 19 objects waiting for <cnode/sid/sess_srno/proc_ptr/ospid/wait_event>
    <1/50/57797/0x7129af10/983196/library cache lock>
Open chains found:
Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <1/50/57797/0x7129af10/983196/library cache lock>
 -- <1/44/39313/0x7129ff10/893060/library cache lock>
Other chains found:
Chain 2 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <1/2/1/0x71290010/291060/No Wait>
Chain 3 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <1/78/50237/0x712a3610/815226/No Wait>
Extra information that will be dumped at higher levels:
[level  4] :   1 node dumps -- [REMOTE_WT] [LEAF] [LEAF_NW] 
[level  5] :   3 node dumps -- [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP] 
[level 10] :  61 node dumps -- [IGN] 
 
State of nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):
[0]/1/1/1/0x712c3e20/684238/IGN/1/2//none
[1]/1/2/1/0x712c48b0/291060/SINGLE_NODE_NW/3/4//none
[2]/1/3/1/0x712c5340/725070/IGN/5/6//none
[3]/1/4/1/0x712c5dd0/712796/IGN/7/8//none
[4]/1/5/1/0x712c6860/708770/IGN/9/10//none
[6]/1/7/1/0x712c7d80/295152/IGN/11/12//none
[8]/1/9/1/0x712c92a0/565402/IGN/13/14//none
[9]/1/10/1/0x712c9d30/483358/IGN/15/16//none
[10]/1/11/1/0x712ca7c0/344152/IGN/17/18//none
[11]/1/12/1/0x712cb250/671938/IGN/19/20//none
[12]/1/13/1/0x712cbce0/720896/IGN/21/22//none
[13]/1/14/1/0x712cc770/692394/IGN/23/24//none
[14]/1/15/1/0x712cd200/540862/IGN/25/26//none
[15]/1/16/1/0x712cdc90/430080/IGN/27/28//none
[18]/1/19/1/0x712cfc40/495630/IGN/29/30//none
[26]/1/27/13752/0x712d50c0/544976/IGN/31/32//none
[28]/1/29/28/0x712d65e0/610362/IGN/33/34//none
[29]/1/30/35903/0x712d7070/676010/IGN/35/36//none
[30]/1/31/49240/0x712d7b00/790768/IGN/37/38//none
[32]/1/33/29215/0x712d9020/335986/IGN/39/40//none
[34]/1/35/24441/0x712da540/872628/IGN/41/42//none
[36]/1/37/60707/0x712dba60/213146/IGN/43/44//none
[38]/1/39/39733/0x712dcf80/593956/IGN/45/46//none
[40]/1/41/6551/0x712de4a0/176348/IGN/47/48//none
[43]/1/44/39313/0x712e0450/893060/IGN_DMP/49/52/[49]/none
[44]/1/45/45306/0x712e0ee0/847890/IGN/53/54/[49]/none
[45]/1/46/47889/0x712e1970/852118/IGN/55/56//none
[46]/1/47/10116/0x712e2400/774218/IGN/57/58//none
[48]/1/49/65097/0x712e3920/794626/IGN/59/60/[49]/none
[49]/1/50/57797/0x712e43b0/983196/LEAF/50/51//43
[51]/1/52/61253/0x712e58d0/835762/IGN/61/62//none
[53]/1/54/60295/0x712e6df0/241828/IGN/63/64/[49]/none
[55]/1/56/33252/0x712e8310/876796/IGN/65/66/[49]/none
[60]/1/61/58067/0x712eb7e0/1134728/IGN/67/68//none
[61]/1/62/35636/0x712ec270/888926/IGN/69/70//none
[63]/1/64/22405/0x712ed790/807136/IGN/71/72/[49]/none
[64]/1/65/7555/0x712ee220/745562/IGN/73/74/[49]/none
[65]/1/66/490/0x712eecb0/753754/IGN/75/76/[49]/none
[66]/1/67/14931/0x712ef740/856300/IGN/77/78/[49]/none
[67]/1/68/17519/0x712f01d0/659544/IGN/79/80/[49]/none
[70]/1/71/58756/0x712f2180/1073324/IGN/81/82//none
[72]/1/73/25986/0x712f36a0/966846/IGN/83/84//none
[74]/1/75/48332/0x712f4bc0/331982/IGN/85/86//none
[77]/1/78/50237/0x712f6b70/815226/SINGLE_NODE_NW/87/88//none
[79]/1/80/12637/0x712f8090/933944/IGN/89/90//none
[81]/1/82/46899/0x712f95b0/868430/IGN/91/92//none
[82]/1/83/9568/0x712fa040/819348/IGN/93/94//none
[84]/1/85/58168/0x712fb560/1122394/IGN/95/96//none
[85]/1/86/64484/0x712fbff0/757916/IGN/97/98/[49]/none
[87]/1/88/36307/0x712fd510/1081380/IGN/99/100/[49]/none
[88]/1/89/51336/0x712fdfa0/1077352/IGN/101/102/[49]/none
[90]/1/91/32263/0x712ff4c0/962662/IGN/103/104//none
[95]/1/96/37982/0x71302990/585908/IGN/105/106/[49]/none
[96]/1/97/57325/0x71303420/512182/IGN/107/108//none
[98]/1/99/17104/0x71304940/467134/IGN/109/110/[49]/none
[103]/1/104/48394/0x71307e10/823496/IGN/111/112/[49]/none
[104]/1/105/23940/0x713088a0/765982/IGN/113/114//none
[114]/1/115/15193/0x7130f240/1142930/IGN/115/116//none
[117]/1/118/63680/0x713111f0/827464/IGN/117/118//none
[120]/1/121/27959/0x713131a0/647310/IGN/119/120/[49]/none
[139]/1/140/12020/0x7131fa50/614616/IGN/121/122/[49]/none
[145]/1/146/41112/0x713239b0/413794/IGN/123/124//none
[146]/1/147/46996/0x71324440/622736/IGN/125/126//none
[148]/1/149/5135/0x71325960/520408/IGN/127/128//none
[151]/1/152/44673/0x71327910/651364/IGN/129/130/[49]/none
====================
END OF HANG ANALYSIS

后来求助于群里的朋友，朋友说你在节点1上在做个同样的操作并抓个hanganalyz报告看看，通过这次在节点一上执行后，终于发现了罪魁祸首，sid为21的进程阻塞了71和77号进程，通过查看sid为21的session信息，发现此session是一个11月份起执行的一个job进程，到现在还一直在运行没有跑完，通过和应用沟通后杀掉此session后能够正常执行drop分区表的操作了。

==============
HANG ANALYSIS:
==============
Open chains found:
Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/21/51704/0x7129a010/151578/No Wait>
 -- <0/71/21705/0x71295a10/1556728/library cache lock>
 -- <0/77/60298/0x71299110/2265288/library cache lock>
Other chains found:
Chain 2 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/2/1/0x71290010/2515112/No Wait>
Chain 3 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/29/3947/0x7129b410/1319054/global cache cr request>
Chain 4 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/69/57115/0x7129aa10/934098/No Wait>
Chain 5 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/87/20599/0x71294610/2424956/library cache lock>
Extra information that will be dumped at higher levels:
[level  4] :   4 node dumps -- [REMOTE_WT] [LEAF] [LEAF_NW] 
[level  5] :   3 node dumps -- [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP] 
[level 10] :  36 node dumps -- [IGN] 
 
State of nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):
[0]/0/1/3/0x712c3e20/2482262/IGN/1/2//none
[1]/0/2/1/0x712c48b0/2515112/SINGLE_NODE_NW/3/4//none
[2]/0/3/3/0x712c5340/2470046/IGN/5/6//none
[3]/0/4/31/0x712c5dd0/2433060/IGN/7/8//none
[4]/0/5/5/0x712c6860/1568898/IGN/9/10//none
[6]/0/7/1/0x712c7d80/2117714/IGN/11/12//none
[8]/0/9/5/0x712c92a0/2105428/IGN/13/14//none
[9]/0/10/3/0x712c9d30/1945600/IGN/15/16//none
[10]/0/11/3/0x712ca7c0/1474684/IGN/17/18//none
[11]/0/12/1/0x712cb250/1994760/IGN/19/20//none
[12]/0/13/3/0x712cbce0/2121758/IGN/21/22//none
[13]/0/14/1/0x712cc770/2236426/IGN/23/24//none
[14]/0/15/1/0x712cd200/2396392/IGN/25/26//none
[15]/0/16/3/0x712cdc90/1429530/IGN/27/28//none
[18]/0/19/29/0x712cfc40/1667300/IGN/29/30//none
[20]/0/21/51704/0x712d1160/151578/LEAF_NW/31/32//70
[27]/0/28/5834/0x712d5b50/1720324/IGN/33/34//none
[28]/0/29/3947/0x712d65e0/1319054/SINGLE_NODE/35/36//none
[30]/0/31/47698/0x712d7b00/954392/IGN/37/38//none
[31]/0/32/52154/0x712d8590/2572400/IGN/39/40//none
[34]/0/35/56598/0x712da540/2502742/IGN/41/42//none
[36]/0/37/14591/0x712dba60/2203828/IGN/43/44//none
[41]/0/42/2799/0x712def30/2498708/IGN/45/46//none
[42]/0/43/815/0x712df9c0/2584796/IGN/47/48//none
[43]/0/44/1502/0x712e0450/1794168/IGN/49/50//none
[44]/0/45/17414/0x712e0ee0/1585292/IGN/51/52//none
[46]/0/47/63034/0x712e2400/2215980/IGN/53/54//none
[47]/0/48/48238/0x712e2e90/1998986/IGN/55/56//none
[48]/0/49/52010/0x712e3920/2551838/IGN/57/58//none
[50]/0/51/48234/0x712e4e40/282670/IGN/59/60//none
[54]/0/55/55282/0x712e7880/1208356/IGN/61/62//none
[56]/0/57/41499/0x712e8da0/2039868/IGN/63/64//none
[58]/0/59/32222/0x712ea2c0/2318474/IGN/65/66//none
[61]/0/62/55234/0x712ec270/1003520/IGN/67/68//none
[64]/0/65/48890/0x712ee220/1572902/IGN/69/70//none
[65]/0/66/62960/0x712eecb0/2326724/IGN/71/72//none
[67]/0/68/43742/0x712f01d0/2388020/IGN/73/74//none
[68]/0/69/57115/0x712f0c60/934098/SINGLE_NODE_NW/75/76//none
[70]/0/71/21705/0x712f2180/1556728/REMOTE_WT/77/78/[20]/76
[74]/0/75/19596/0x712f4bc0/1487064/IGN/79/80//none
[76]/0/77/60298/0x712f60e0/2265288/REMOTE_WT/81/82/[70]/none
[77]/0/78/8672/0x712f6b70/1499326/IGN/83/84//none
[86]/0/87/20599/0x712fca80/2424956/REMOTE_WT/85/86/[70]/none
====================
END OF HANG ANALYSIS
====================