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   : http://blog.csdn.net/robinson_0612

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏SpringBoot 核心技术

第三十章:SpringBoot使用MapStruct自动映射DTO

8887
来自专栏杨建荣的学习笔记

分分钟搭建MySQL一主多从环境(r12笔记第31天)

之前写过一篇分分钟搭建MySQL Group Replication的测试环境,如果我们在一台服务器上想搭建一主多从的测试环境,怎么能够分分钟搞定呢,其实...

3746
来自专栏杨建荣的学习笔记

dg broker配置的问题及分析 (r7笔记第22天)

今天在配置一个备库的时候碰到了一些问题,话说配置dg broker真没什么特别需要注意的细节了,本身已经给DBA省了很大的事儿了。 但是有时候就是会出现一些稀奇...

3884
来自专栏数据分析

[SQLServer大对象]——FileTable从文件系统迁移文件

阅读导航 从文件系统中迁移文件到FileTable 批量加载文件到FileTable 如何批量加载文件到FileTable 通过博文[SQLServer大对象]...

3956
来自专栏杨建荣的学习笔记

ORACLE 10g 升级 11g问题汇总(31天)

按照计划开始了生产库的升级,环境基于linux 64位. uname: Linux 2.6.18-308.el5 #1 SMP Fri Jan 27 17:1...

5899
来自专栏耕耘实录

CentOS7中mysql-5.7.21-el7-x86_64.tar.gz版MySQL的安装与配置

版权声明:本文为耕耘实录原创文章,各大自媒体平台同步更新。欢迎转载,转载请注明出处,谢谢

892
来自专栏杨建荣的学习笔记

由一条报警信息发现的一系列问题(r7笔记第67天)

今天看到一条报警短信,提示是某个表空间的问题。 ZABBIX-监控系统: ------------------------------------ 报警内容:...

3689
来自专栏杨建荣的学习笔记

11g dataguard使用总结(r5笔记第12天)

11g的dataguard相比于10g来说,最优越的特性应该算就是active dataguard了,这一点改进在很大意义上促使用户需要把数据库从10g升级到1...

2816
来自专栏杨建荣的学习笔记

一次数据库宕机问题的分析(r6笔记第5天)

今天来到办公室,发现有一台服务器中的数据库实例停掉了。这种情况真是意料之外,尤其是我还不是很熟悉这台机器的服务。 赶紧查看数据库日志,可以看到数据库在昨晚停掉了...

4805
来自专栏杨建荣的学习笔记

一次数据库无法登陆的"问题"及排查(r2第11天)

继昨天发生san存储切换导致io等待异常高的问题后,晚上客户对测试环境的数据库进行了远程启动,因为库比较多,监控process都起来了。客户就发邮件通知测试组继...

36212

扫码关注云+社区

领取腾讯云代金券