Unable to shutdown Oracle_20091203

現象產生過程：
1 imp oriusr/oriusrpwd   full=y buffer=81920 feedback=10 log=impcatalog.log

file=expcatalog.dmp
試圖匯入大資料量的檔案超過30個G，但沒有把自動歸檔開啟，造成無法歸檔，從而分配新的redo log.
The following is the snippet from the database alert log.

Wed Dec  2 12:43:10 2009
ARCH: Connecting to console port...
Thread 1 cannot allocate new log, sequence 4
All online logs needed archiving
  Current log# 3 seq# 3 mem# 0: /opt/oracle/oradata/demo/redo03.log

2 手動Cut session (Ctrl + C )

3 擔心造成資料不一致，試圖drop 所有已匯入的表資料。
drop table xxx;
drop table xxx;

持續了好長時間都不見有反應，又手工Cut session

4 嘗試shutdown immediate，關不掉，
SQL> shutdown immediate
ORA-01013: user requested cancel of current operation

產生下如下alert log

Wed Dec  2 16:36:34 2009
Shutting down instance: further logons disabled
Shutting down instance (immediate)
License high water mark = 4
Wed Dec  2 16:41:33 2009
Active call for process 16291 user 'oracle' program (TNS V1-V3)'
SHUTDOWN: waiting for active calls to complete.
Wed Dec  2 17:36:36 2009
SHUTDOWN: Active sessions prevent database close operation

5 查Metalink ID 305666.1
MetaLink:

=()),

(page=KBNavigator&id=(bmDocDsrc=DOCUMENT&bmDocTitle=Shutdown%20is%20Cancelled%20With%

20ORA-1013%20After%20Waiting%20for%20an%

20Hour&viewingMode=1143&bmDocID=305666.1&from=BOOKMARK&bmDocType=PROBLEM))

Shutdown is Cancelled With ORA-1013 After Waiting for an Hour [ID 305666.1]

Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.1.0.4
This problem can occur on any platform.

Symptoms
@Checked for relevance on 20-DEC-2007

This is applicable only to databases running on 9.2.X and above.

You issued a

shutdown normal

or

shutdown transactional

and after waiting for an hour you see

SQL> shutdown
ORA-1013: user requested cancel of current operation

The alert.log shows:

Fri Mar 14 13:05:42 2003
Shutting down instance: further logons disabled
Shutting down instance (normal)
License high water mark = 2
Fri Mar 14 13:10:50 2003
Active process 23595 user 'ora9203' program (TNS V1-V3)'
SHUTDOWN: waiting for logins to complete.
Fri Mar 14 14:07:30 2003
SHUTDOWN: Active sessions prevent database close operation

Cause
This is an expected behaviour in 9.2.X and above.

From 9.2.x, a 60 minute timeout is introduced during which shutdown waits for active

logins/calls to complete.  When you reach the 60 minute timeout, the following message is

written to alert.log:

SHUTDOWN: Active sessions prevent database close operation

Shutdown modes that wait for users to disconnect or for transactions to complete have a

limit on the amount of time that they wait. If all events blocking the shutdown do not

occur within one hour, the shutdown command cancels with the following message: ORA-1013:

user requested cancel of
current operation.

6 經驗
1) 對於大資料量匯入/truncate 的動作不要在客戶端做，儘量寫成指令碼的掛在後臺跑
2) 操作過程中如果中斷，不要輕易kill session
3) truncate 大表，可以先用dbms_metadata.get_ddl獲取索引的建立語句，先drop 索引，再truncate

大表，最後根據ddl語句重建索引。