Oracle 关闭(shutdown immediate)时hang住

列举四种shutdown Hang的情况。

第一种情况的原因和给出的解决方案

Shutdown Immediate Hangs / Active Processes Prevent Shutdown (文档 ID 416658.1)

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‘; 

Then issue the shutdown command.

3. You can kill the sessions preventing shutdown. Refer: Alert Log: Shutdown Waiting for Active Calls to Complete (Doc ID 1039389.6)

本文出自 “DBA Fighting!” 博客，请务必保留此出处http://hbxztc.blog.51cto.com/1587495/1919778

Oracle 关闭(shutdown immediate)时hang住

标签：oracle shutdown hanging