频道栏目
首页 > 数据库 > Oracle > 正文
SHUTDOWN: Active processes prevent shutdown operation
2013-07-26 09:46:57         来源:blog.csdn.net/robinson_0612  
收藏   我要投稿

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.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';

 

 

点击复制链接 与好友分享!回本站首页
相关TAG标签
上一篇:ORACLE表连接详解
下一篇:Oracle EM 11g/12c资料库迁移操作
相关文章
图文推荐
文章
推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站