数据库版本:10205
操作系统版本:aix
rac是否:否
问题现象:sqlplus / as sysdba 进不去,关库异常
***** 2019-01-03 05:11:35.383
*** SERVICE NAME:(SYS$BACKGROUND) 2019-01-03 05:11:35.346
*** SESSION ID:(1655.1) 2019-01-03 05:11:35.346
PMON unable to acquire latch 7000000100ea2f8 Child shared pool level=7 child#=1
Location from where latch is held: kgh: quiesce extents:
Context saved from call: 0
state=busy, wlstate=free
waiters [orapid (seconds since: put on list, posted, alive check)]:
10 (97, 1546463495, 3)
59 (97, 1546463495, 3)
36 (97, 1546463495, 3)
52 (97, 1546463495, 3)
41 (97, 1546463495, 3)
8 (97, 1546463495, 3)
18 (97, 1546463495, 3)
11 (93, 1546463495, 3)
28 (36, 1546463495, 3)
waiter count=9
gotten 1011023581 times wait, failed first 44536115 sleeps 4706865
gotten 0 times nowait, failed: 0
possible holder pid = 4 ospid=2011262
----------------------------------------**
解决办法:
杀掉客户端会话:
ps -ef|grep “LOCAL=NO”|grep -v grep|awk ‘{print "kill -9 " $2}’
尝试进入数据库,发现还是进不去。
ps -ef|grep pmon
杀掉数据库对应的pmon进程,重启数据库。
数据库正常起来,收集ash报告,根据sql_id查找对应的语句
造成此次数据库异常宕机的语句
SELECT tmp.vperiod vperiod, jbf.jobname jobname, tmp.nthopentaxmny nthopentaxmny , tmp.jopntaxmny jopntaxmny , tmp.nprelevytaxsummny nprelevytaxsummny, tmp.vdef29 vdef29 , nvl(pts.vdef3,0) vdef3, nvl(pts.vdef4,0) vdef4 , nvl(pts.vdef4,0)*(nvl(pibninoriginmny1,0)+ nvl(pioninoriginmny1,0)) AS suppe
nseprotax, tmp.recntaxmny recntaxmny, nvl(tmp.jopntaxmny,0)-nvl(tmp.nprelevytaxsummny,0)-nvl(recntaxmny,0) as backtaxmny, (nvl(tmp.jopntaxmny,0)-nvl(tmp.nprelevytaxsummny,0)-nvl(recntaxmny,0))*0.11 backaddtaxmny, nvl(tmp.jopntaxmny,0)-nvl(tmp.nprelevytaxsummny,0)-nvl(recntaxmny,0)+ (nvl(tmp.jopntax
mny,0)-nvl(tmp.nprelevytaxsummny,0)-nvl(recntaxmny,0))*0.11 sumbacktaxmny, case when nvl(pts.vdef4,0)*nvl(pibninoriginmny1,0)+ nvl(pioninoriginmny1,0) - (nvl(tmp.jopntaxmny,0)-nvl(nprelevytaxsummny,0)-nvl(recntaxmny,0)+ (nvl(tmp.jopntaxmny,0)-nvl(tmp.nprelevytaxsummny,0)-nvl(recntaxmny,0))*0.11)
<0 then 0 else nvl(pts.vdef4,0)*nvl(pibninoriginmny1,0)+ nvl(pioninoriginmny1,0) - (nvl(tmp.jopnt