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 'oracle@test02 (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:

https://support.oracle.com/CSP/ui/flash.html#tab=KBHome(page=KBHome&id=()),

(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 'oracle@jpsun3127 (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语句重建索引。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10248702/viewspace-621595/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10248702/viewspace-621595/