Oracle数据库shutdown immediate被hang住的几个原因
2014-01-05 23:08 by 潇湘隐者,3677 阅读,0 评论,收藏,编辑
实验操作环境：
操作系统：Red Hat Enterprise Linux ES release 4 (Nahant Update 6)
数据库 ： Oracle Database 10g Release 10.2.0.4.0 �C Production 32bit
今晚使用shutdown immediate（其实是执行stop_oracle.sh脚本关闭数据库，如下所示）关闭数据库的时候，
1:[oracle@gsp-orasvr02scripts]$morestop_oracle.sh
2:lsnrctlstopLISTENER
3:sleep15
4:sqlplus/nolog<<EOF
5:conn/assysdba;
6:altersystemswitchlogfile;
7:altersystemcheckpoint;
8:shutdownimmediate;
9:exit
10:EOF
在另外一个会话中使用tail -20f 命令查看告警日志的输出，结果发现数据库等待了很长时间都没有正常关闭，hang住在下面地方：
Active call for process 11121 user 'oracle' program 'oracle@get-orasvr02 (S000)'
Active call for process 7162 user 'oracle' program 'oracle@get-orasvr02 (S011)'
截图如下
这时解决办法是找出hang住的进程并杀掉(当时操作没有截图，也没有保存输出信息),因为有些session无法被pmon进程清理，导致数据库无法顺利关闭，需要手工杀掉进程。首先使用ps 和grep找到这两个进程。
[ oracle@get-orasvr02 bdump]$ ps -ef | grep oracle | grep S000
[ oracle@get-orasvr02 bdump]$ ps -ef | grep oracle | grep S011
然后使用kill -9 processesid杀掉这两个进程即可，杀掉这两个进程后，从告警日志里面看到里面跳到关闭dispatcher 。如下所示：
1:[oracle@get-orasvr02bdump]$tailalert_epps.log
2:Currentlog#3seq#242223mem#1:/u02/oradata/epps/redo03_01.log
3:SunJan505:14:502014
4:Startingcontrolautobackup
5:ControlautobackupwrittentoDISKdevice
6:handle'/u01/app/oracle/product/10.2.0/db_1/dbs/c-2179993557-20140105-0e'
7:SunJan505:14:542014
8:ALTERSYSTEMARCHIVELOG
9:SunJan505:14:552014
10:Thread1cannotallocatenewlog,sequence242224
11:Checkpointnotcomplete
12:Currentlog#3seq#242223mem#0:/u01/app/oracle/oradata/epps/redo03_1.log
13:Currentlog#3seq#242223mem#1:/u02/oradata/epps/redo03_01.log
14:SunJan505:14:582014
15:Thread1advancedtologsequence242224(LGWRswitch)
16:Currentlog#5seq#242224mem#0:/u01/app/oracle/oradata/epps/redo05_1.log
17:Currentlog#5seq#242224mem#1:/u02/oradata/epps/redo05_02.log
18:SunJan507:31:562014
19:Thread1advancedtologsequence242225(LGWRswitch)
20:Currentlog#2seq#242225mem#0:/u01/app/oracle/oradata/epps/redo02_1.log
21:Currentlog#2seq#242225mem#1:/u02/oradata/epps/redo02_02.log
22:SunJan507:32:202014
23:StartingbackgroundprocessEMN0
24:Shuttingdowninstance:furtherlogonsdisabled
25:EMN0startedwithpid=43,OSid=7062
26:SunJan507:32:212014
27:StoppingbackgroundprocessCJQ0
28:SunJan507:32:212014
29:StoppingbackgroundprocessQMNC
30:SunJan507:32:232014
31:StoppingbackgroundprocessMMNL
32:SunJan507:32:342014
33:BackgroundprocessMMNLnotdeadafter10seconds
34:SunJan507:32:342014
35:KillingbackgroundprocessMMNL
36:SunJan507:32:352014
37:StoppingbackgroundprocessMMON
38:SunJan507:33:052014
39:BackgroundprocessMMONnotdeadafter30seconds
40:SunJan507:33:052014
41:KillingbackgroundprocessMMON
42:SunJan507:33:062014
43:Shuttingdowninstance(immediate)
44:Licensehighwatermark=561
45:SunJan507:33:062014
46:StoppingJobqueueslaveprocesses,flags=7
47:SunJan507:33:062014
48:ProcessOSid:6088aliveafterkill
49:Errorsinfile/u01/app/oracle/admin/epps/udump/epps_ora_7055.trc
50:SunJan507:33:092014
51:WaitingforJobqueueslavestocomplete
52:SunJan507:33:092014
53:Jobqueueslaveprocessesstopped
54:SunJan507:38:102014
55:Activecallforprocess11121user'oracle'program'oracle@get-orasvr02(S000)'
56:Activecallforprocess7162user'oracle'program'oracle@get-orasvr02(S011)'
57:SHUTDOWN:waitingforactivecallstocomplete.
58:SunJan507:57:282014
59:Waitingfordispatcher'D000'toshutdown
60:Waitingfordispatcher'D001'toshutdown
61:Waitingfordispatcher'D002'toshutdown
62:Waitingfordispatcher'D003'toshutdown
63:Waitingfordispatcher'D004'toshutdown
64:Waitingfordispatcher'D005'toshutdown
65:Waitingfordispatcher'D006'toshutdown
66:SunJan507:59:292014
67:AlldispatchersandsharedserveRSShutdown
68:SunJan508:04:302014
69:SHUTDOWN:Activeprocessespreventshutdownoperation
70:SunJan508:09:322014
71:SHUTDOWN:Activeprocessespreventshutdownoperation
Oracle的官方文档介绍、解释如下
The database is waiting for pmon to clean up processes,but pmon is unable to
clean them. The client connections to the server are causing the shutdown
immediate or normal to hang. Killing them allows pmon to clean up and release
the associated Oracle processes and resources.
What resources are we talking about?
1) Any non committed transactions must be rolled back
2) Any temporary space (sort segments / lobs / session temporary tables) must be freed
3) The session itself and any associated memory consumed by the session.
4) Internal locks / enqueues must be cleaned up
Often Oracle (SMON or PMON depending on whether Shared Server is used) will wait for the OS to terminate the process(es) associated with the session. If the OS never returns,or fails to terminate them,then the instance shutdown will hang with this message (Shutdown Waiting for Active Calls to Complete)
Other means exist to achieve a quick shutdown,as outlined inNote 386408.1- What Is The Fastest Way To Cleanly Shutdown An Oracle Database?
结果解决上面问题后，本以为可以顺利关闭数据库，结果又hang住了，告警日志信息提示为
SHUTDOWN: Active processes prevent shutdown operation
出现这个错误原因：
因为我大概如下的操作导致：
[oracle@gsp-orasvr02 scripts]$ sqlplus / as sysdba
...........
sql> !
[oracle@get-orasvr02 ~]$
..... （执行了一些shell 命令）
然后又使用了sqlplus启动登录了数据库，然后做shutdown immediate操作，这时导致shutdown immediate被hang住。
[oracle@gsp-orasvr02 scripts]$ sqlplus / as sysdba
