關閉資料庫遇到SHUTDOWN: Active processes prevent shutdown operation

近期遇到一客戶資料庫關閉時遇到無法關閉的情況。
經檢查是關閉前使用了sqlplus視窗直接使用host命名回到SHELL操作介面，然後又sqlplus / as sysdba登陸了資料庫（具體切換了三、四次）；
在之後的發出關閉命令shutdown immediate;後，命令一直HANG住；此時檢視ALERT日誌，有“SHUTDOWN: Active processes prevent shutdown operation”提示；
一開始以為是有活動會話沒有關閉，查詢ps -ef|grep LOCAL  和ps -ef|grep ora_j的程式，均無相應程式；也未配置EM--DB CONSOLE之類。

剛開始以為是遇到有活動會話導致的，資料庫版本是AIX6.1+11.2.0.1單例項；
因此根據以往經驗會提示出哪個程式導致SHUTDOWN無法完成，根據提示程式號進行KILL程式，就會正常關閉。
-------------------
==》根據觀察，在ORACLE10G及以上版本，會是如下提示：
Active call for process 12345 user 'oracle' program 'oracle@abcd'
SHUTDOWN: waiting for active calls to complete.
也就是指出哪個程式引起的等待，此時kill -9 12345 程式即可。
==》但是在ORACLE 8I版本中，是不會提示哪個程式ACTIVE引起關閉程式HANG的。

在等待的過程中，耽誤了10多分鐘。後根據alert提示查詢，結合之前工程師的操作；
問題可能是sqlplus時host到os下操作，後又sqlplus / as sysdba登陸,如此反覆多次。
-------因為在發出shutdown immediate;的命令列視窗命令仍在執行狀態，事實上此時可以使用CTRL+C來取消關閉命令。
本次就使用了CTRL+C來取消關閉命令，後一路exit退出，並重新使用TELNET登陸進行關閉操作，正常完成。
############
這裡也是一個重要提醒，在發出shutdown immediate;的命令列視窗，最好新建的會話來執行，避免此類問題。
關庫前最好將ps -ef|grep LOCAL  和ps -ef|grep ora_j的程式，是否有大事務在進行及大事務回滾在進行等問題進行確認；
確認之後切換日誌、執行檢查點，再關閉資料庫，此時關庫風險會小很多，即使遇到無法關閉去KILL程式，也相應更安全。


#################################故障時
1.無法關閉時的alert日誌

Sun Dec 13 00:25:15 2015
Shutting down instance (immediate)
Stopping background process SMCO
Shutting down instance: further logons disabled
Sun Dec 13 00:25:17 2015
Stopping background process CJQ0
Stopping background process QMNC
Stopping background process MMNL
Stopping background process MMON
License high water mark = 78
Stopping Job queue slave processes, flags = 7
Job queue slave processes stopped
All dispatchers and shared servers shutdown
Sun Dec 13 00:30:21 2015
SHUTDOWN: Active processes prevent shutdown operation
Sun Dec 13 00:35:23 2015
SHUTDOWN: Active processes prevent shutdown operation
Sun Dec 13 00:40:24 2015
SHUTDOWN: Active processes prevent shutdown operation
Sun Dec 13 00:45:25 2015
SHUTDOWN: Active processes prevent shutdown operation
Sun Dec 13 00:48:31 2015

Instance shutdown cancelled

2.此類問題的解決方法：

參考文件：

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

Alert Log: Shutdown Waiting for Active Calls to Complete (文件 ID 1039389.6)

What Is The Fastest Way To Cleanly Shutdown An Oracle Database? (文件 ID 386408.1)

以下解決方案來自文件 ID 416658.1：

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)