normal資料庫關閉hang的問題，normal資料庫hang

normal資料庫關閉hang的問題，normal資料庫hang

今晚辦公樓停電維護，需要提前關閉伺服器，為防止異常關閉導致的各種問題，有個測試庫，使用shutdown normal停庫，結果就是很常見的hang住了。

操作順序：

1. shutdown normal，然後關閉了當前sqlplus視窗。
從alert日誌中看：
Mon Jun 22 16:50:22 2015
Shutting down instance (normal)
Stopping background process SMCO
Shutting down instance: further logons disabled
這裡涉及到shutdown normal的原理，稍後引述。

2. 此時重新登入，sqlplus / as sysdba，執行startup或shutdown immediate命令都提示失敗，

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 22 17:03:06 2015Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected.SQL> startupORA-01012: not logged onSQL> shutdown immediateORA-24324: service handle not initializedORA-24323: value not allowedORA-01090: shutdown in progress - connection is not permittedMon Jun 22 16:50:24 2015Stopping background process CJQ0Stopping background process QMNCStopping background process MMNLStopping background process MMONLicense high water mark = 125

ORA-01090提示說正在執行關閉操作，不允許其他串連的操作。

3. 其實這涉及到normal關閉的原理，他需要等待所有已連線的使用者中斷串連，換句話說，如果仍有串連到庫的使用者，shutdown的操作就一直等待。這是最完全的關閉方式，但同時是變數最大的，因為可能你不知其他使用者什麼時候中斷。

首先嘗試尋找出所有串連使用者，用kill -9直接殺進程。

可以使用ps -ef尋找所有(LOCAL=NO)的進程，LOCAL=NO表示串連不是本地，而是遠程。

ps -ef|grep ora|grep -v grep|grep -v ora_|grep LOCAL=NO|awk '{print $2}'，然後kill -9 進程號或者ps -ef|grep ora|grep -v grep|grep -v ora_|grep LOCAL=NO|awk '{print $2}'|xargs kill

從alert日誌看：

Mon Jun 22 16:55:26 2015
Active process 27446 user 'oracle11g' program 'oracle@dcsopenNode1'
Active process 27402 user 'oracle11g' program 'oracle@dcsopenNode1'
Active process 27555 user 'oracle11g' program 'oracle@dcsopenNode1'
Active process 11697 user 'oracle11g' program 'oracle@dcsopenNode1'
Active process 14942 user 'oracle11g' program 'oracle@dcsopenNode1'
Active process 27559 user 'oracle11g' program 'oracle@dcsopenNode1'
Active process 27513 user 'oracle11g' program 'oracle@dcsopenNode1'
Active process 26911 user 'oracle11g' program 'oracle@dcsopenNode1'
Active process 31993 user 'oracle11g' program 'oracle@dcsopenNode1'
Active process 30810 user 'oracle11g' program 'oracle@dcsopenNode1'
Active process 27557 user 'oracle11g' program 'oracle@dcsopenNode1'
Active process 11684 user 'oracle11g' program 'oracle@dcsopenNode1'
Active process 11666 user 'oracle11g' program 'oracle@dcsopenNode1'
Active process 27510 user 'oracle11g' program 'oracle@dcsopenNode1'
Active process 11688 user 'oracle11g' program 'oracle@dcsopenNode1'
SHUTDOWN: waiting for logins to complete.
Mon Jun 22 17:01:29 2015
All dispatchers and shared servers shutdown

是提示了所有dispatcher和共用服務關閉，但sqlplus登入後仍是上面的提示。

4. 嘗試關閉監聽服務，lsnrctl stop。

問題依舊。

5. 重登陸執行shutdown abort，強制關閉。

從alert日誌看：

USER (ospid: 28558): terminating the instance

Instance terminated by USER, pid = 28558

看樣子是關閉了執行個體。

重新執行sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 22 17:43:25 2015Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to an idle instance.

再次執行startup-shutdown normal，

SQL> startupORACLE instance started.Total System Global Area 3290345472 bytesFixed Size                  2217832 bytesVariable Size            2499807384 bytesDatabase Buffers          771751936 bytesRedo Buffers               16568320 bytesDatabase mounted.Database opened.SQL> shutdown normalDatabase closed.Database dismounted.ORACLE instance shut down.

由於現在已經沒有串連的使用者了，正常啟動，正常關閉了。

從alert日誌看，

Mon Jun 22 17:46:01 2015Starting ORACLE instance (normal)LICENSE_MAX_SESSION = 0LICENSE_SESSIONS_WARNING = 0Picked latch-free SCN scheme 3Using LOG_ARCHIVE_DEST_1 parameter default value as /oracle/ora11gR2/product/11.2.0/dbhome_1/dbs/archAutotune of undo retention is turned on. IMODE=BRILAT =27LICENSE_MAX_USERS = 0SYS auditing is disabledStarting up:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options.Using parameter settings in server-side spfile /oracle/ora11gR2/product/11.2.0/dbhome_1/dbs/spfiledcsopen.oraSystem parameters with non-default values:  processes                = 150  memory_target            = 3152M  control_files            = "/oracle/ora11gR2/oradata/dcsopen/control01.ctl"  control_files            = "/oracle/ora11gR2/oradata/dcsopen/control02.ctl"  db_block_size            = 8192  compatible               = "11.2.0.0.0"  undo_tablespace          = "UNDOTBS1"  remote_login_passwordfile= "EXCLUSIVE"  db_domain                = ""  dispatchers              = "(PROTOCOL=TCP) (SERVICE=dcsopenXDB)"  audit_file_dest          = "/oracle/ora11gR2/admin/dcsopen/adump"  audit_trail              = "DB"  db_name                  = "dcsopen"  open_cursors             = 300  diagnostic_dest          = "/oracle/ora11gR2"Mon Jun 22 17:46:03 2015PMON started with pid=2, OS id=30699 Mon Jun 22 17:46:03 2015VKTM started with pid=3, OS id=30701 at elevated priorityVKTM running at (10)millisec precision with DBRM quantum (100)msMon Jun 22 17:46:03 2015GEN0 started with pid=4, OS id=30705 Mon Jun 22 17:46:03 2015DIAG started with pid=5, OS id=30707 Mon Jun 22 17:46:03 2015DBRM started with pid=6, OS id=30709 Mon Jun 22 17:46:03 2015PSP0 started with pid=7, OS id=30711 Mon Jun 22 17:46:03 2015DIA0 started with pid=8, OS id=30713 Mon Jun 22 17:46:03 2015MMAN started with pid=9, OS id=30715 Mon Jun 22 17:46:03 2015DBW0 started with pid=10, OS id=30717 Mon Jun 22 17:46:03 2015LGWR started with pid=11, OS id=30721 Mon Jun 22 17:46:03 2015CKPT started with pid=12, OS id=30723 Mon Jun 22 17:46:03 2015SMON started with pid=13, OS id=30725 Mon Jun 22 17:46:03 2015RECO started with pid=14, OS id=30727 Mon Jun 22 17:46:03 2015MMON started with pid=15, OS id=30729 starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...Mon Jun 22 17:46:03 2015MMNL started with pid=16, OS id=30731 starting up 1 shared server(s) ...ORACLE_BASE from environment = /oracle/ora11gR2Mon Jun 22 17:46:04 2015ALTER DATABASE   MOUNTSuccessful mount of redo thread 1, with mount id 2809595100Database mounted in Exclusive ModeLost write protection disabledCompleted: ALTER DATABASE   MOUNTMon Jun 22 17:46:08 2015ALTER DATABASE OPENThread 1 opened at log sequence 1279  Current log# 1 seq# 1279 mem# 0: /oracle/ora11gR2/oradata/dcsopen/redo01.logSuccessful open of redo thread 1MTTR advisory is disabled because FAST_START_MTTR_TARGET is not setSMON: enabling cache recoverySuccessfully onlined Undo Tablespace 2.Verifying file header compatibility for 11g tablespace encryption..Verifying 11g file header compatibility for tablespace encryption completedSMON: enabling tx recoveryDatabase Characterset is ZHS16GBKNo Resource Manager plan activereplication_dependency_tracking turned off (no async multimaster replication found)Starting background process QMNCMon Jun 22 17:46:09 2015QMNC started with pid=20, OS id=30789 Completed: ALTER DATABASE OPENStarting background process CJQ0Mon Jun 22 17:46:11 2015CJQ0 started with pid=22, OS id=30806 Mon Jun 22 17:46:18 2015Shutting down instance (normal)Shutting down instance: further logons disabledStopping background process QMNCStopping background process CJQ0Stopping background process MMNLStopping background process MMONLicense high water mark = 5All dispatchers and shared servers shutdownALTER DATABASE CLOSE NORMALMon Jun 22 17:46:22 2015SMON: disabling tx recoverySMON: disabling cache recoveryMon Jun 22 17:46:22 2015Shutting down archive processesArchiving is disabledArchive process shutdown avoided: 0 activeThread 1 closed at log sequence 1279Successful close of redo thread 1Completed: ALTER DATABASE CLOSE NORMALALTER DATABASE DISMOUNTCompleted: ALTER DATABASE DISMOUNTARCH: Archival disabled due to shutdown: 1090Shutting down archive processesArchiving is disabledArchive process shutdown avoided: 0 activeMon Jun 22 17:46:23 2015Stopping background process VKTM: ARCH: Archival disabled due to shutdown: 1090Shutting down archive processesArchiving is disabledArchive process shutdown avoided: 0 activeMon Jun 22 17:46:25 2015Instance shutdown complete

借鑒《Concept》，一些知識點：

1. 如果使用者試圖訪問一個正在關閉的資料庫，會得到錯誤提示：ORA-01090: shutdown in progress - connection is not permitted.

2. 關閉資料庫，必須用SYSOPER或SYSDBA的角色。

3. 關閉資料庫，是有逾時時間的，如果使用者未中斷串連，或者交易未完成，超過一小時，則shutdown命令會取消，提示錯誤：ORA-01013: user requested cancel of current operation.

4. 幾種關閉庫的參數，

shutdown normal：

預設的關閉參數，需要兩個條件：

(1) 執行語句後，不允許新的串連。

(2) 資料庫關閉之前，資料庫會等待所有已連線的使用者中斷串連。

下一次啟動時不需要執行個體恢複。

shutdown immediate：

使用情境：

(1) 初始化一個自動，無人值守的備份。

(2) 馬上就要斷電。

(3) 資料庫或應用工作不正常，你不能馬上聯絡到使用者退出登入或他們無法退出登入。

條件：

(1) 不允許新的串連，不允許新的交易。

(2) 任何未提交的事務會復原(如果此時有個長交易，未提交，那麼不會像這種關閉名稱immediate那樣迅速地關閉)。

(3) 不會等待已連線的使用者退出登入。資料庫會隱式復原活動事務，中斷串連使用者。

下一次啟動時不需要執行個體恢複。

shutdown transactional：

適用於計劃停機，允許活動交易處理完成後再停止執行個體的情境。

條件：

(1) 不允許新的串連，不允許新的交易。

(2) 所有交易完成後，會中斷所有和庫的串連。

(3) 在這個時間點，關閉執行個體就像執行了shutdown immediate。

下一次啟動時不需要執行個體恢複。

transactional參數主要會防止使用者丟失交易，同時不需要所有使用者退出登入。

shutdown abort：

適用情境：

資料庫或應用不能正常工作，並且沒有其它類型的關閉操作進行中。

(1) 需要立即關閉資料庫(例如，一分鐘後電源會被關閉)。

(2) 啟動執行個體時碰到了問題。

條件：

(1) 不允許新的串連，不允許新的交易。

(2) 正在被Oracle處理的用戶端SQL語句會被立即中斷。

(3) 未提交事務不會復原。

(4) Oracle不會等待正保持串連的用戶端退出登入。資料庫會隱式地中斷所有串連。

下一次啟動時需要進行執行個體恢複。

總結：

以上四種參數會適合於不同的情境，簡單講，shutdown normal是預設的關閉方式，最完整的關閉方式，缺點是需要被動等待所有交易完成，所有使用者退出登入。shutdown immediate只要不存在較長的需要復原的事務，其關閉時間會快。shutdown transactional會最大限度地保證交易的完成。前三種都不需要執行個體恢複。shutdown abort則是最暴力的關閉，關閉時間最快，但代價是啟動需要執行個體恢複，因為關閉時存在未復原未提交的事務。