SHUTDOWN:Activeprocessespreventshutdownoperation

在使用shutdown immediate关闭数据库时hang住，查看alert 日志，遭遇了SHUTDOWN: Active processes prevent shutdown operation。

在使用shutdown immediate关闭数据库时hang住，查看alert 日志，遭遇了SHUTDOWN: Active processes prevent shutdown operation。也即是说有一些活动进程阻止了当前的shutdown操作。咦，数据库是测试数据库啥也没有干，也没有配置db console，还有活动进程阻止呢？

1、情景再现

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
--Blog :

-->下面是后台日志的信息
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)