zt_Resolving Shutdown Immediate Hang Situations_shutdown immediate關庫慢

  作為dba在維護資料庫時,停機維護多為家常便飯.這不,前幾日測試庫由於併發使用者過多,導致關庫異常緩慢.今天閱讀blog發現一
則相當不錯的文章,摘錄於此;供後備;
  連結:
  http://askdba.org/weblog/2008/05/shutdown-immediate-hang-2/
  (注:此文是2008年發行,而我測試版本是oracle11g)
 
  本著知行合一,自己也測試一番;
 
  ---開啟一session,建表並插入多條記錄,未提交
SQL> create table t_big(a int);

Table created.

SQL> insert into t_big select level from dual connect by level<=5e5;

500000 rows created.

SQL> insert into t_big select level from dual connect by level<=5e5;

500000 rows created.

   ---開啟sysdba session,執行shutdown immediate

---檢視alert
Wed Jan 09 13:38:31 2013
Shutting down instance (immediate) --關閉例項
Stopping background process SMCO
Shutting down instance: further logons disabled
Wed Jan 09 13:38:36 2013
Stopping background process CJQ0 ---關閉後臺程式
Stopping background process QMNC
Stopping background process MMNL
Stopping background process MMON
License high water mark = 47
All dispatchers and shared servers shutdown
ALTER DATABASE CLOSE NORMAL
Wed Jan 09 13:38:41 2013
SMON: disabling tx recovery --禁用smon事務恢復 也可用event事件來禁用smon tx recovery event=”10513 trace name context forever, level 2″,拉起庫後移除此引數
SMON: disabling cache recovery
Wed Jan 09 13:38:45 2013
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 91
Successful close of redo thread 1
Wed Jan 09 13:39:12 2013
Completed: ALTER DATABASE CLOSE NORMAL
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Wed Jan 09 13:39:14 2013
Stopping background process VKTM:
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Wed Jan 09 13:39:21 2013
Instance shutdown complete  

-----繼續在上述sysdba session,執行startup,並檢視alert
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as D:\oracle11g_64bit\product\11.2.0\dbhome_1\RDBMS
Autotune of undo retention is turned on.
IMODE=BR

PMON started with pid=2, OS id=7196 --pmon開啟程式
Wed Jan 09 13:50:18 2013
VKTM started with pid=3, OS id=7396 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Wed Jan 09 13:50:18 2013
GEN0 started with pid=4, OS id=13600 --開啟各種程式
Wed Jan 09 13:50:18 2013
DIAG started with pid=5, OS id=5204
Wed Jan 09 13:50:18 2013
DBRM started with pid=6, OS id=9044
Wed Jan 09 13:50:18 2013
PSP0 started with pid=7, OS id=11972
Wed Jan 09 13:50:18 2013
DIA0 started with pid=8, OS id=5188
Wed Jan 09 13:50:18 2013
MMAN started with pid=9, OS id=4340
Wed Jan 09 13:50:18 2013
DBW0 started with pid=10, OS id=4808
Wed Jan 09 13:50:18 2013
LGWR started with pid=11, OS id=13148
Wed Jan 09 13:50:18 2013
CKPT started with pid=12, OS id=10972
Wed Jan 09 13:50:18 2013
SMON started with pid=13, OS id=4608
Wed Jan 09 13:50:18 2013
RECO started with pid=14, OS id=8800
Wed Jan 09 13:50:18 2013
MMON started with pid=15, OS id=8400
Wed Jan 09 13:50:18 2013
MMNL started with pid=16, OS id=3964
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = D:\oracle11g_64bit
Wed Jan 09 13:50:19 2013
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 1332056859
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Wed Jan 09 13:50:24 2013
ALTER DATABASE OPEN
Thread 1 opened at log sequence 91
  Current log# 7 seq# 91 mem# 0: D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO07.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery  ----開啟快取恢復
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery ---開啟事務恢復
Database Characterset is AL32UTF8
No Resource Manager plan active
Starting background process QMNC
Wed Jan 09 13:50:29 2013
QMNC started with pid=20, OS id=9512
Completed: ALTER DATABASE OPEN
Wed Jan 09 13:50:35 2013
Starting background process CJQ0
Wed Jan 09 13:50:35 2013
CJQ0 started with pid=22, OS id=7140

 
另:event=”10061 trace name context forever, level 10″ --暫停smon clear up temporary segment

 再次說明,oracle不同版本之間的特性一直在變化,一定要注意;