在使用shutdown immediate关闭数据库时hang住,查看alert 日志,遭遇了SHUTDOWN: Active processes prevent shutdown operation。也即是说有一些活动进程阻止了当前的shutdown操作。
1、查看alert日志
[oracle@ora11g oracle]$ cd diag/rdbms/ora11g/ora11g/trace/
[oracle@ora11g trace]$
[oracle@ora11g trace]$ tail -f alert_ora11g.log
Thu Jan 09 09:40:46 2014
Shutting down instance (immediate)
Stopping background process SMCO
Shutting down instance: further logons disabled
Thu Jan 09 09:40:47 2014
Stopping background process CJQ0
Stopping background process QMNC
Stopping background process MMNL
Stopping background process MMON
License high water mark = 13
All dispatchers and shared servers shutdown
Thu Jan 09 09:45:52 2014
SHUTDOWN: Active processes prevent shutdown operation
Thu Jan 09 09:50:54 2014
SHUTDOWN: Active processes prevent shutdown operation
Thu Jan 09 09:55:55 2014
SHUTDOWN: Active processes prevent shutdown operation
2、分析与解决
由于之前的session没有断开,而后又使用了host切换到OS提示符下,导致数据库无法正常关闭。
SQL> !
[oracle@ora11g trace]$ pwd
/u01/oracle/diag/rdbms/ora11g/ora11g/trace
只要断开这个连接,数据库就会继续shutdown immediate。
[oracle@ora11g trace]$ exit
exit
SQL>
SQL> exit
Disconnected
[oracle@ora11g trace]$
再看关库的窗口:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> SQL> SQL>
此后建议我们在关闭数据库时需先断开所有SYS会话连接,再关闭数据库即可。
3、Oracle官方文档(Doc ID 416658.1)
Doc ID 416658.1
Shutdown Immediate Hangs / Active Processes Prevent Shutdown (Doc ID 416658.1)
Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.2.0.2 - Release: 10.1 to 11.2
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.2.0.2 [Release: 10.1 to 11.2]
Information in this document applies to any platform.
Symptoms
The 'shutdown immediate' command hangs or is very slow
The alert log lists messages like:
SHUTDOWN: waiting for active calls to complete.ACTIVE PROCESSES PREVENT SHUTDOWN OPERATION
Cause
This is not a bug.
If the DB Control repository is running on the database target against which shutdown immediate was attempted then an incorrect
order of events seems used.
You should stop DB Control first to get rid of all connections between DB Control and the repository database and then shutdown
the database with 'shutdown immediate'.
Current database sessions may show:
SQL> select SID, USERNAME, PROGRAM from v$session;
SID USERNAME PROGRAM
----- ---------------------- ----------------------------------
243 SYSTEM SQL Developer
246 SYSMAN OMS
247 oracle@lgiora09 (q001)
248 oracle@lgiora09 (q000)
251 DBSNMP emagent@lgiora09 (TNS V1-V3)
252 SYSMAN OMS
253 SYSMAN OMS
254 DBSNMP emagent@lgiora09 (TNS V1-V3)
255 SYSTEM java.exe
256 SYSMAN OMS
Clearly OMS and OEM are connected (Oracle Enterprise Manager Grid Control or DBConsole) via users SYSMAN and DBSNMP.
These sessions should be de-activated (that is to log off any OEM, OMS, SYSMAN and DBSNMP) before the shutdown immediate is attempted.
Oracle Enterprise Manager, Grid Control, Dbconsole and agents keep doing internal processing.
This may include a few PLSQL notification procedures running on the database by database control like
BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;
As per internal documentation of the shutdown immediate, if there are active calls then it would wait for all the active calls to finish.
Solution
To implement the solution:
1. Given OEM connections are active (SYSMAN and DBSNMP), de-activate these sessions, i.e. by stopping the agent/DBConsole
2. Then shutdown immediate as normal
- OR -
There may be processes still running and holding locks at the time a shutdown is issued.
Sometimes these are failed jobs or transactions, which are effectively 'zombies', which are not able to receive a signal from Oracle.
If this occurs, the only way to shutdown the database is by doing:
sql>
shutdown abort
startup restrict
shutdown normal
The startup does any necessary recovery and cleanup, so that a valid cold backup can be taken afterward.
If this issue occurs frequently, it would be a good practice to see if there are any active user processes running in v$session or
v$process before shutting down the instance.
If the problem persists, and no apparent user processes are active, you can set this event prior to issuing the shutdown command
in order to see what is happening. This will dump a systemstate every 5 minutes while shutdown is hanging
SQL>
connect / as sysdba
alter session set events '10400 trace name context forever, level 1';