SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE hangs

 

看看alert.log

        Shutting down instance (immediate)
        License high water mark = 12
        Thu Dec  8 18:43:16 1994
        alter database  close normal
        Thu Dec  8 18:43:17 1994
        SMON: disabling tx recovery
        SMON: disabling cache recovery or
    waiting for smon to disable tx recovery --这表明smon在清理一些事务信息

通常在shutdown immediate和shutdown normal时要清理extents信息，比如你进行了一个大排序，只有在重起数据库时才释放使用的空间（但文件还是那么大，不会自动变小），所以就要花 很多时间来进行清理，另外如果执行了一个大事务时发布shutdown immedaite(这时数据库就会想所有影子进程发送一个消息，要他们完成回滚或者清理工作），所以就需要很长时间的回滚工作。

对于大的排序，你在shutdown immedaite在等待smon处理时还没有正在关掉可以查看
select count(block#) from fet$;
select count(block#) from uet$;
数字是否在变小，如果变小，说明是在清理工作

Oracle9i好像不大会做extents的清理吧.

个人感觉有两种可能.

1. 之前执行了很大的dml操作, 产生非常大的undo, shutdown immediate会对这些undo进行回滚, 使用时间很长比较容易理解.

2. 系统的cpu被其他进程占用, 没有多少cpu时间用于运行Oracle的进程. 如果是Windows机器, 发生这种情况的可能比较大, 大部分是杀毒软件/系统bug造成.

Description
===========
SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE hangs. In the alert.log, you see only
the following:

Shutting down instance (immediate)
License high water mark = 12
Thu Dec 8 18:43:16 1994
alter database close normal
Thu Dec 8 18:43:17 1994
SMON: disabling tx recovery
SMON: disabling cache recovery
or
waiting for smon to disable tx recovery

There are no ORA errors or trace files.

 

Scope & Application
===================
Informational

During a SHUTDOWN IMMEDIATE and SHUTDOWN NORMAL, SMON is cleaning up extents
which are no longer needed and marking them as freed.

Either wait for SMON to clean up the free extents in the database as it
shuts down or perform a SHUTDOWN ABORT to shutdown the instance. A SHUTDOWN
ABORT will not perform a clean shutdown.

Verify that temporary segments are decreasing
---------------------------------------------
To verify that the temporary segments are decreasing have an active session
available in Server Manager during the SHUTDOWN IMMEDIATE. Issue the following
query to ensure the database is not hanging, but is actually perform extent
cleanup:

SVRMGR> select count(block#) from fet$;
COUNT(BLOC
----------
7

SVRMGR> select count(block#) from uet$;
COUNT(BLOC
----------
402

After some time has elapsed, reissue the query and see that the values for fet$
have increased while the values or uet$ have decreased:

SVRMGR> select count(block#) from fet$;
COUNT(BLOC
----------
10

SVRMGR> select count(block#) from uet$;
COUNT(BLOC
----------
399

During shutdown the SMON process is cleaning up extents and updating the data
dictionary tables with the marked free extents. As the extents are marked as
freed, they are removed from the table for used extents, UET$ and placed on the
table for free extents, FET$.

How to Avoid creating many Temporary Extents
--------------------------------------------
Once the database has shutdown cleanly, to avoid creating many temporary
extents change the initial and next extent sizes on temporary tablespaces
to a more appropriate size:

ALTER TABLESPACE <temp> DEFAULT STORAGE (INITIAL <size>M/K NEXT <size>M/K);

Note: If the temporary tablespace is of type TEMPORARY, then this change
will only affect temporary segments created after issuing the above
command. Any existing temporary segments already in the TEMPORARY tablespace
will not be affected till the instance is restarted. On shutdown, existing
temporary segments are dropped. If the TEMPORARY TABLESPACE is of type
PERMANENT, then cleanup is performed by SMON after completion of the process
using it.

Increasing the initial and next extent size will decrease the number of extents
that are allocated to temporary segments. Since there are fewer extents to
deallocate, the database should shutdown more speedily.

Take the following scenario:

A database was subject to large sorts with the following sort parameter in
the "init.ora" file:

- sort_area_size=1000000

The temporary tablespaces for this database were all created with initial and
next extents sized at 50k and the total database size was about 300mb.

Database sorts will utilize memory as much as possible based on the "init.ora"
parameter "sort_area_size". Once this memory-based sort area is filled, the
database will utilize the temporary table space associated with the database
user to complete the sort operation. During a shutdown normal, the database
will attempt to clean up the temporary tablespaces.

If a small extent size is used, then a large number of extents will be created
for a large sort. The cleanup of the temporary tablespace takes much longer
with a large number of extents.

Note:
=====
You have to do a shutdown abort and then bring the database
back up to run the suggested queries.

References:SMON - Temporary Segment Cleanup and Free Space Coalescing