今晚辦公樓停電維護，需要提前關閉服務器，為防止異常關閉導致的各種問題，有個測試庫，使用shutdown normal停庫，結果就是很常見的hang住了。
操作順序：
?1. shutdown normal，然後關閉了當前sqlplus窗口。?2. 此時重新登錄，sqlplus / as sysdba?，執行startup或shutdown immediate命令都提示失敗，
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 22 17:03:06 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected. SQL> startup ORA-01012: not logged on SQL> shutdown immediate ORA-24324: service handle not initialized ORA-24323: value not allowed ORA-01090: shutdown in progress - connection is not permitted Mon Jun 22 16:50:24 2015 Stopping background process CJQ0 Stopping background process QMNC Stopping background process MMNL Stopping background process MMON License 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 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance.
再次執行startup-shutdown normal，
SQL> startup ORACLE instance started. Total System Global Area 3290345472 bytes Fixed Size 2217832 bytes Variable Size 2499807384 bytes Database Buffers 771751936 bytes Redo Buffers 16568320 bytes Database mounted. Database opened. SQL> shutdown normal Database closed. Database dismounted. ORACLE instance shut down.由於現在已經沒有連接的用戶了，正常啟動，正常關閉了。
從alert日志看，
Mon Jun 22 17:46:01 2015 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 /oracle/ora11gR2/product/11.2.0/dbhome_1/dbs/arch Autotune of undo retention is turned on. IMODE=BR ILAT =27 LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With 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.ora System 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 2015 PMON started with pid=2, OS id=30699 Mon Jun 22 17:46:03 2015 VKTM started with pid=3, OS id=30701 at elevated priority VKTM running at (10)millisec precision with DBRM quantum (100)ms Mon Jun 22 17:46:03 2015 GEN0 started with pid=4, OS id=30705 Mon Jun 22 17:46:03 2015 DIAG started with pid=5, OS id=30707 Mon Jun 22 17:46:03 2015 DBRM started with pid=6, OS id=30709 Mon Jun 22 17:46:03 2015 PSP0 started with pid=7, OS id=30711 Mon Jun 22 17:46:03 2015 DIA0 started with pid=8, OS id=30713 Mon Jun 22 17:46:03 2015 MMAN started with pid=9, OS id=30715 Mon Jun 22 17:46:03 2015 DBW0 started with pid=10, OS id=30717 Mon Jun 22 17:46:03 2015 LGWR started with pid=11, OS id=30721 Mon Jun 22 17:46:03 2015 CKPT started with pid=12, OS id=30723 Mon Jun 22 17:46:03 2015 SMON started with pid=13, OS id=30725 Mon Jun 22 17:46:03 2015 RECO started with pid=14, OS id=30727 Mon Jun 22 17:46:03 2015 MMON 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 2015 MMNL started with pid=16, OS id=30731 starting up 1 shared server(s) ... ORACLE_BASE from environment = /oracle/ora11gR2 Mon Jun 22 17:46:04 2015 ALTER DATABASE MOUNT Successful mount of redo thread 1, with mount id 2809595100 Database mounted in Exclusive Mode Lost write protection disabled Completed: ALTER DATABASE MOUNT Mon Jun 22 17:46:08 2015 ALTER DATABASE OPEN Thread 1 opened at log sequence 1279 Current log# 1 seq# 1279 mem# 0: /oracle/ora11gR2/oradata/dcsopen/redo01.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 ZHS16GBK No Resource Manager plan active replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC Mon Jun 22 17:46:09 2015 QMNC started with pid=20, OS id=30789 Completed: ALTER DATABASE OPEN Starting background process CJQ0 Mon Jun 22 17:46:11 2015 CJQ0 started with pid=22, OS id=30806 Mon Jun 22 17:46:18 2015 Shutting down instance (normal) Shutting down instance: further logons disabled Stopping background process QMNC Stopping background process CJQ0 Stopping background process MMNL Stopping background process MMON License high water mark = 5 All dispatchers and shared servers shutdown ALTER DATABASE CLOSE NORMAL Mon Jun 22 17:46:22 2015 SMON: disabling tx recovery SMON: disabling cache recovery Mon Jun 22 17:46:22 2015 Shutting down archive processes Archiving is disabled Archive process shutdown avoided: 0 active Thread 1 closed at log sequence 1279 Successful close of redo thread 1 Completed: ALTER DATABASE CLOSE NORMAL ALTER DATABASE DISMOUNT Completed: ALTER DATABASE DISMOUNT ARCH: Archival disabled due to shutdown: 1090 Shutting down archive processes Archiving is disabled Archive process shutdown avoided: 0 active Mon Jun 22 17:46:23 2015 Stopping background process VKTM: ARCH: Archival disabled due to shutdown: 1090 Shutting down archive processes Archiving is disabled Archive process shutdown avoided: 0 active Mon Jun 22 17:46:25 2015 Instance 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則是最暴力的關閉，關閉時間最快，但代價是啟動需要實例恢復，因為關閉時存在未回滾未提交的事務。
