SHUTDOWN: Active processes prevent shutdown operation
在使用shutdown immediate关闭
数据库时hang住，查看alert 日志，遭遇了SHUTDOWN: Active processes prevent shutdown operation。也即是说有一些活动进程阻止了当前的shutdown操作。咦，数据库是测试数据库啥也没有干，也没有配置db console，还有活动进程阻止呢？
1、情景再现
[sql] SQL> select * from v$version where rownum<2; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production SQL> @comm_sess_users; ---->查看当前连接的session nstance SID Serial ID Status Oracle User O/S User O/S PID Session Program Terminal Machine --------- ------ --------- --------- -------------- ------------ --------------- -------------------------- ---------- ------------------- sybo3 125 5 ACTIVE SYS oracle 4069 sqlplus@linux3.orasrv.com pts/1 linux3.orasrv.com SQL> host; ---->使用host切换到os提示符下 [oracle@linux3 ~]$ sqlplus / as sysdba ----->再次登陆到sqlplus SQL> @comm_sess_users; ----->下面的查询可以看到Terminal显示为同一个终端, 都为sys用户, sid 139 为ACTIVE Instance SID Serial ID Status Oracle User O/S User O/S PID Session Program Terminal Machine --------- ------ --------- --------- -------------- ------------ --------------- -------------------------- ---------- ------------------- sybo3 125 5 INACTIVE SYS oracle 4069 sqlplus@linux3.orasrv.com pts/1 linux3.orasrv.com 139 10 ACTIVE SYS oracle 4152 sqlplus@linux3.orasrv.com pts/1 linux3.orasrv.com SQL> @my_env; ---->查看自身的sid SPID SID SERIAL# USERNAME PROGRAM ------------------------ ------ ---------- --------------- ------------------------------------------------ 4152 139 10 oracle oracle@linux3.orasrv.com (TNS V1-V3) SQL>shutdown immediate; ---->关闭数据库时，hang住，不得不使用ctrl -c 终止 ORA-01013: user requested cancel of current operation SQL> shutdown abort; ---->强制关闭数据库 ORACLE instance shut down. --Author : Robinson -->下面是后台日志的信息 Thu Jul 25 10:56:43 2013 Shutting down instance (immediate) Shutting down instance: further logons disabled --->不允许新session登陆 Stopping background process QMNC Thu Jul 25 10:56:43 2013 Stopping background process CJQ0 Stopping background process MMNL Stopping background process MMON License high water mark = 3 Thu Jul 25 11:01:53 2013 SHUTDOWN: Active processes prevent shutdown operation --->出现无法shutdown的提示 Thu Jul 25 11:07:01 2013 SHUTDOWN: Active processes prevent shutdown operation Thu Jul 25 11:07:44 2013 Starting background process SMCO Thu Jul 25 11:07:44 2013 Instance shutdown cancelled --->shutdown 被cancel Thu Jul 25 11:07:44 2013 SMCO started with pid=15, OS id=4178 Thu Jul 25 11:08:06 2013 Shutting down instance (abort) --->使用abort方式shutdown database License high water mark = 3 USER (ospid: 4152): terminating the instance Termination issued to instance processes. Waiting for the processes to exit Instance termination failed to kill one or more processes Instance terminated by USER, pid = 4152 Thu Jul 25 11:08:16 2013 Instance shutdown complete
2、分析与解决
从上面的情形来看，是由于之前的session没有断开，而后又使用了host切换到OS提示符下，导致数据库无法正常关闭。
通过进一步的测试，如果是多个非sys用户登录则不存在此现象。
Oracle对此给出的说明是这不是一个Oracle bug，而是使用了一个非正常的数据库关闭顺序。
建议先断开所有连接再关闭数据库，如先关闭db control，具体见下面的Oracle 文档及示例。
3、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