Oracle數據庫啟動和關閉

要了解Oracle數據庫的啟動和停止需要先了解“實例”(instance)和“數據庫”(database)這兩個名詞的定義：

• 數據庫(database)：物理操作系統文件或磁盤(disk)的集合。

• 實例(instance)：一組Oracle后臺進程/線程以及一個共享內存區，這些內存由同一個計算機上運行的線程/進程所共享。

這兩個詞有時可以互換使用，不過二者的概念完全不同。實例和數據庫之間的關系是：數據庫可以由多個實例mount和open，而實例可以在任何時間點mount和open一個數據庫。

Oracle System Identifier (SID)

SID是Oracle實例在服務器上的唯一名字，在UNIX和Linux機器上，Oracle用SID和Oracle home值來創建共享內存的鍵值，即SID和Oracle home指定一個實例，SID也是用來定位參數文件。

有了對以上概念的認識，下面來看Oracle數據庫的啟動和關閉過程。

1、Oracle實例和數據庫的啟動

啟動Oracle數據庫的方式有很多種，最簡單的啟動Oracle數據庫的方式是就是使用sqlplus執行startup命令。

先來看官方給的圖：

從上圖可以看出庫Oracle從shutdown狀態到open狀態經歷以下階段：

1) 啟動實例，不mount數據庫

實例被啟動，但還沒關聯數據庫，對應的命令是startup nomount

1. Searches for a server parameter file in a platform-specific default location and, if not found, for a text initialization parameter file (specifying STARTUP with the SPFILE or PFILE parameters overrides the default behavior)

2. Reads the parameter file to determine the values of initialization parameters

3. Allocates the SGA based on the initialization parameter settings

4. Starts the Oracle background processes

5. Opens the alert log and trace files and writes all explicit parameter settings to the alert log in valid parameter syntax

2) 數據庫被mount

實例被啟動，打開數據庫的控制文件關聯一個數據庫。數據庫對用戶還是close狀態。對就的命令是alter database mount;

To mount the database, the instance obtains the names of the database control files specified in the CONTROL_FILES initialization parameter and opens the files. Oracle Database reads the control files to find the names of the data files and the online redo log files that it will attempt to access when opening the database.

In a mounted database, the database is closed and accessible only to database administrators. Administrators can keep the database closed while completing specific maintenance operations. However, the database is not available for normal operations.

3) 數據庫被open

實例被啟動，關聯的數據庫被open。數據庫中的數據可以被用戶訪問。對應的命令是alter database open。

• Opens the online data files in tablespaces other than undo tablespaces

  If a tablespace was offline when the database was previously shut down (see "Online and Offline Tablespaces"), then the tablespace and its corresponding data files will be offline when the database reopens.

• Acquires an undo tablespace

  If multiple undo tablespaces exists, then the UNDO_TABLESPACE initialization parameter designates the undo tablespace to use. If this parameter is not set, then the first available undo tablespace is chosen.

• Opens the online redo log files

2、Oracle實例和數據庫的關閉

通常關閉Oracle數據庫使用sqlplus執行shutdown命令

再看官方給的圖：

從上圖中也可以看出從數據庫open狀態到shutdown狀態也經歷三個階段：

1) 數據庫被關閉

數據庫還是mount狀態，但在線數據文件和日志文件被關閉了。

The database close operation is implicit in a database shutdown. The nature of the operation depends on whether the database shutdown is normal or abnormal.

When a database is closed as part of a SHUTDOWN with any option other than ABORT, Oracle Database writes data in the SGA to the data files and online redo log files. Next, the database closes online data files and online redo log files. Any offline data files of offline tablespaces have been closed already. When the database reopens, any tablespace that was offline remains offline.

At this stage, the database is closed and inaccessible for normal operations. The control files remain open after a database is closed.

If a SHUTDOWN ABORT or abnormal termination occurs, then the instance of an open database closes and shuts down the database instantaneously. Oracle Database does not write data in the buffers of the SGA to the data files and redo log files. The subsequent reopening of the database requires instance recovery, which Oracle Database performs automatically.

2) 數據庫被umount 

實例是啟動的，但不再通過控制文件關聯數據庫。

After the database is closed, Oracle Database unmounts the database to disassociate it from the instance. After a database is unmounted, Oracle Database closes the control files of the database. At this point, the instance remains in memory.

3) 實例被shutdown

實例被shutdown。

The final step in database shutdown is shutting down the instance. When the database instance is shut down, the SGA is removed from memory and the background processes are terminated.

數據庫關閉的4種模式：ABORT、IMMEDIATE、TRANSACTIONAL、NORMAL。下面的表格介紹了各模式下數據庫的行為。

• SHUTDOWN ABORT

  This mode is intended for emergency situations, such as when no other form of shutdown is successful. This mode of shutdown is the fastest. However, a subsequent open of this database may take substantially longer because instance recovery must be performed to make the data files consistent.

  Note:

  Because SHUTDOWN ABORT does not checkpoint the open data files, instance recovery is necessary before the database can reopen. The other shutdown modes do not require instance recovery before the database can reopen.

• SHUTDOWN IMMEDIATE

  This mode is typically the fastest next to SHUTDOWN ABORT. Oracle Database terminates any executing SQL statements and disconnects users. Active transactions are terminated and uncommitted changes are rolled back.

• SHUTDOWN TRANSACTIONAL

  This mode prevents users from starting new transactions, but waits for all current transactions to complete before shutting down. This mode can take a significant amount of time depending on the nature of the current transactions.

• SHUTDOWN NORMAL

  This is the default mode of shutdown. The database waits for all connected users to disconnect before shutting down.

下面通過實例演示Oracle數據庫的啟動和關閉過程，例子中Oracle版本為11.2.0.1

1、啟動數據庫

當前沒有任何進程和共享內存，設置好ORACLE_SID和ORACLE_HOME環境變量

執行sqlplus / as sysdba連接到一個空實例，當前仍然沒有共享內存，只增加了一個進程oracletest的進程

使用startup nomount啟動數據庫實例，該命令默認查找spfile參數文件啟動實例，也可以使用startup nomount pfile='/dir/init.ora'指定參數文件啟動，在內存中分配共享內存并創建后臺進程。

查看當前的實例狀態，當前狀態只能查少量的視圖如v$instance，但大部分視圖無法查詢如v$database、v$datafile，會報錯：ORA-01507: database not mounted

使用alter database mount命令mount數據庫，這種狀態只能查詢部分視圖，dba開頭的大部分視圖都不能查詢會報錯：ORA-01219: database not open: queries allowed on fixed tables/views only

使用alter database open命令open數據庫：

當前數據庫被打開，可以對外提供服務。

2、關閉數據庫

整個啟動和關閉的過程都會記錄在alert日志文件中。11g的alert日志目錄是$ORACLE_BASE/diag/rdbms/dbname/sid/trace。文件名為alert_sid.log。

參考：http://docs.oracle.com/cd/E11882_01/server.112/e40540/startup.htm#CNCPT89043

《9I10G11G編程藝術  深入數據庫體系結構 》