最初了解Oracle數(shù)據(jù)庫(kù)的時(shí)候搭盾，僅僅認(rèn)為數(shù)據(jù)庫(kù)的狀態(tài)只有兩種祷蝌，啟動(dòng)(Open)和關(guān)閉(shutdown)。隨著對(duì)Oracle認(rèn)識(shí)的不斷加深痴昧，才逐步了解到在啟動(dòng)和關(guān)閉兩種狀態(tài)外稽穆，還有另外兩種狀態(tài)未掛載(nomount)和掛載(mount)。
啟動(dòng)和關(guān)閉很容易理解赶撰，分別代表數(shù)據(jù)庫(kù)的可用狀態(tài)和不可用狀態(tài)舌镶。在啟動(dòng)狀態(tài)下，Oracle數(shù)據(jù)庫(kù)可以正常提供服務(wù)豪娜，進(jìn)行增刪改查的事務(wù)操作餐胀，平時(shí)見到最多的也是這種狀態(tài)。而關(guān)閉狀態(tài)下瘤载，數(shù)據(jù)庫(kù)無(wú)法連接到對(duì)應(yīng)的實(shí)例否灾，最基本的查詢操作也無(wú)法進(jìn)行，完全程度上的不可用狀態(tài)鸣奔，也是日常中常見的一種數(shù)據(jù)庫(kù)狀態(tài)墨技。
shutdown狀態(tài)
數(shù)據(jù)庫(kù)實(shí)例為關(guān)閉狀態(tài)，此狀態(tài)下的數(shù)據(jù)庫(kù)無(wú)法連接到實(shí)例挎狸，通過(guò)sqlplus / as sysdba連接到數(shù)據(jù)庫(kù)時(shí)會(huì)看到Connected to idle instance的提示扣汪，表示數(shù)據(jù)庫(kù)實(shí)例當(dāng)前是不可用的。
# sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 19 13:52:19 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
可以理解為shutdown狀態(tài)下的數(shù)據(jù)庫(kù)為數(shù)據(jù)庫(kù)的初始化狀態(tài)伟叛，這個(gè)狀態(tài)下可以將數(shù)據(jù)庫(kù)啟動(dòng)到指定的狀態(tài)上(除關(guān)閉狀態(tài)之外的其他狀態(tài))私痹。例如，將數(shù)據(jù)庫(kù)啟動(dòng)到nomount狀態(tài)
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1937457152 bytes
Fixed Size 2254464 bytes
Variable Size 587204992 bytes
Database Buffers 1325400064 bytes
Redo Buffers 22597632 bytes
SQL>
nomount狀態(tài)
通過(guò)startup nomount啟動(dòng)數(shù)據(jù)庫(kù)统刮，啟動(dòng)過(guò)程中數(shù)據(jù)庫(kù)會(huì)讀取參數(shù)文件將數(shù)據(jù)庫(kù)啟動(dòng)到指定的nomount狀態(tài)上紊遵，并啟動(dòng)數(shù)據(jù)庫(kù)中的一些后臺(tái)進(jìn)程(PMON、DBRM侥蒙、LMON暗膜、DBW0、LGWR鞭衩、CKPT等等)学搜。啟動(dòng)具體詳情參見數(shù)據(jù)庫(kù)alert日志
啟動(dòng)日志如下
Sat Mar 19 13:54:25 2022
Adjusting the default value of parameter parallel_max_servers
from 1600 to 120 due to the value of parameter processes (150)
Starting ORACLE instance (normal)
...
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options.
...
Using parameter settings in server-side pfile /oracle/11.2.0/dbs/inittest1.ora
...
Sat Mar 19 13:54:31 2022
PMON started with pid=2, OS id=191701
Sat Mar 19 13:54:31 2022
PSP0 started with pid=3, OS id=191703
Sat Mar 19 13:54:32 2022
VKTM started with pid=4, OS id=191716 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Sat Mar 19 13:54:32 2022
GEN0 started with pid=5, OS id=191720
Sat Mar 19 13:54:32 2022
DIAG started with pid=6, OS id=191722
Sat Mar 19 13:54:32 2022
DBRM started with pid=7, OS id=191724
Sat Mar 19 13:54:32 2022
PING started with pid=8, OS id=191726
Sat Mar 19 13:54:32 2022
ACMS started with pid=9, OS id=191728
Sat Mar 19 13:54:32 2022
DIA0 started with pid=10, OS id=191730
Sat Mar 19 13:54:32 2022
LMON started with pid=11, OS id=191732
Sat Mar 19 13:54:32 2022
LMD0 started with pid=12, OS id=191734
* Load Monitor used for high load check
* New Low - High Load Threshold Range = [38400 - 51200]
Sat Mar 19 13:54:33 2022
LMS0 started with pid=13, OS id=191736 at elevated priority
Sat Mar 19 13:54:33 2022
LMS1 started with pid=14, OS id=191741 at elevated priority
Sat Mar 19 13:54:33 2022
LMS2 started with pid=15, OS id=191745 at elevated priority
Sat Mar 19 13:54:33 2022
RMS0 started with pid=16, OS id=191749
Sat Mar 19 13:54:33 2022
LMHB started with pid=17, OS id=191751
Sat Mar 19 13:54:33 2022
MMAN started with pid=18, OS id=191753
Sat Mar 19 13:54:33 2022
DBW0 started with pid=19, OS id=191755
Sat Mar 19 13:54:33 2022
DBW1 started with pid=20, OS id=191757
Sat Mar 19 13:54:33 2022
DBW2 started with pid=21, OS id=191759
Sat Mar 19 13:54:33 2022
DBW0 started with pid=19, OS id=191755
Sat Mar 19 13:54:33 2022
DBW1 started with pid=20, OS id=191757
Sat Mar 19 13:54:33 2022
DBW2 started with pid=21, OS id=191759
Sat Mar 19 13:54:33 2022
DBW3 started with pid=22, OS id=191761
Sat Mar 19 13:54:33 2022
DBW4 started with pid=23, OS id=191763
Sat Mar 19 13:54:33 2022
LGWR started with pid=24, OS id=191765
Sat Mar 19 13:54:33 2022
CKPT started with pid=25, OS id=191767
Sat Mar 19 13:54:33 2022
SMON started with pid=26, OS id=191769
Sat Mar 19 13:54:33 2022
RECO started with pid=27, OS id=191771
Sat Mar 19 13:54:33 2022
RBAL started with pid=28, OS id=191773
Sat Mar 19 13:54:33 2022
ASMB started with pid=29, OS id=191775
Sat Mar 19 13:54:33 2022
MMON started with pid=30, OS id=191777
Sat Mar 19 13:54:33 2022
MMNL started with pid=31, OS id=191781
nomount狀態(tài)下，數(shù)據(jù)庫(kù)處于未掛載狀態(tài)下论衍，數(shù)據(jù)庫(kù)實(shí)例可以訪問(wèn)瑞佩，但無(wú)法進(jìn)行DDL事務(wù)操作。此時(shí)查詢數(shù)據(jù)庫(kù)的狀態(tài)為Dismounted
#crsctl stat res -t | grep -A2 ora.test.db
ora.test.db
1 ONLINE INTERMEDIATE node1 Dismounted
2 ONLINE ONLINE node2 Open
查詢實(shí)例的狀態(tài)為running
# srvctl status database -d test
Instance test1 is running on node node1
Instance test2 is running on node node2
進(jìn)入數(shù)據(jù)庫(kù)中進(jìn)行嘗試創(chuàng)建表失敗坯台，報(bào)錯(cuò)ORA-01109: database not open炬丸，說(shuō)明盡管可以連接到實(shí)例，但數(shù)據(jù)庫(kù)仍未處于open狀態(tài)
SQL> create table test_nomount(status varchar(10));
create table test_nomount(status varchar(10))
*
ERROR at line 1:
ORA-01109: database not open
查詢數(shù)據(jù)庫(kù)的一些參數(shù)蜒蕾，例如數(shù)據(jù)庫(kù)監(jiān)聽稠炬、數(shù)據(jù)庫(kù)啟動(dòng)日志
SQL> show parameter listener
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
listener_networks string
local_listener string (ADDRESS=(PROTOCOL=TCP)(HOST=172.xx.xx.x)(PORT=1521))
remote_listener string oracle-scan:1521
SQL> show parameter dump
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
background_dump_dest string /oracle/diag/rdbms/test/test1/trace
core_dump_dest string /oracle/diag/rdbms/test/test1/cdump
user_dump_dest string /oracle/diag/rdbms/test/test1/trace
nomount狀態(tài)下的數(shù)據(jù)庫(kù)焕阿，可以將數(shù)據(jù)庫(kù)進(jìn)一步啟動(dòng)到mount狀態(tài)下
SQL> alter database mount;
Database altered.
mount狀態(tài)
mount狀態(tài)下，數(shù)據(jù)庫(kù)已經(jīng)掛載首启，但數(shù)據(jù)庫(kù)仍未打開暮屡。此時(shí)，查詢數(shù)據(jù)庫(kù)資源狀態(tài)為Mounted (Closed)毅桃。但當(dāng)數(shù)據(jù)庫(kù)啟動(dòng)到mount狀態(tài)時(shí)褒纲，數(shù)據(jù)庫(kù)中的redo日志組會(huì)被掛載(但并未啟用)。見啟動(dòng)到mount狀態(tài)的數(shù)據(jù)庫(kù)alert日志
Sat Mar 19 14:31:40 2022
Adjusting the default value of parameter parallel_max_servers
from 1600 to 120 due to the value of parameter processes (150)
Starting ORACLE instance (normal)
alter database mount
Sat Mar 19 14:31:53 2022
Successful mount of redo thread 1, with mount id 2397027473
Sat Mar 19 14:31:53 2022
Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE)
Lost write protection disabled
nodepleted: alter database mount
查看數(shù)據(jù)庫(kù)資源狀態(tài)
# crsctl stat res -t | grep -A2 ora.test.db
ora.test.db
1 ONLINE INTERMEDIATE node1 Mounted (Closed)
2 ONLINE ONLINE node2 Open
實(shí)例狀態(tài)相比nomount狀態(tài)下的實(shí)例沒(méi)有發(fā)生變化疾嗅。實(shí)際上外厂，數(shù)據(jù)庫(kù)在nomount狀態(tài)下已經(jīng)可以訪問(wèn)了，在mount狀態(tài)下自然也能訪問(wèn)代承。
此時(shí)汁蝶，數(shù)據(jù)庫(kù)中的數(shù)據(jù)文件、控制文件等都可以正常訪問(wèn)论悴。
SQL> select NAME,FILE#,STATUS from v$datafile;
NAME FILE# STATUS
-------------------------------------------------- ---------- --------------
+DATADG/test/datafile/system.401.1098876775 1 SYSTEM
+DATADG/test/datafile/sysaux.402.1098876777 2 ONLINE
+DATADG/test/datafile/undotbs1.403.1098876777 3 ONLINE
+DATADG/test/datafile/users.404.1098876777 4 ONLINE
+DATADG/test/datafile/undotbs2.409.1098876893 5 ONLINE
SQL> elect GROUP#,THREAD#,STATUS from v$log;
GROUP# THREAD# STATUS
---------- ---------- --------------------------------
1 1 CURRENT
2 1 INACTIVE
3 2 INACTIVE
4 2 CURRENT
嘗試訪問(wèn)數(shù)據(jù)庫(kù)中的dba_data_files視圖時(shí)報(bào)錯(cuò)掖棉，無(wú)法訪問(wèn)，此時(shí)數(shù)據(jù)文件沒(méi)有打開
SQL> select * from dba_data_files;
select * from dba_data_files
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
此狀態(tài)下的數(shù)據(jù)庫(kù)可以執(zhí)行alter database open;將數(shù)據(jù)庫(kù)啟動(dòng)到Open狀態(tài)
SQL> alter database open;
Database altered.
Open狀態(tài)
Open狀態(tài)下的數(shù)據(jù)庫(kù)為可用狀態(tài)膀估，所有的后臺(tái)服務(wù)幔亥、日志組、數(shù)據(jù)文件等都可以正常訪問(wèn)察纯，且數(shù)據(jù)庫(kù)開始提供事務(wù)服務(wù)帕棉。再次看看講數(shù)據(jù)庫(kù)啟動(dòng)到open狀態(tài)時(shí)日志中中記錄了什么，以下日志段為數(shù)據(jù)庫(kù)從mount狀態(tài)啟動(dòng)到open狀態(tài)的日志輸出
Sat Mar 19 14:51:11 2022
alter database open
Picked broadcast on nodemit scheme to generate SCNs
Sat Mar 19 14:51:12 2022
Thread 1 opened at log sequence 5
Current log# 1 seq# 5 mem# 0: +DATADG/test/onlinelog/group_1.406.1098876853
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Mar 19 14:51:12 2022
SMON: enabling cache recovery
[171895] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:1294695214 end:1294695444 diff:230 (2 seconds)
Verifying file header nodepatibility for 11g tablespace encryption..
Verifying 11g file header nodepatibility for tablespace encryption nodepleted
SMON: enabling tx recovery
Database Characterset is AL32UTF8
Opening with Resource Manager plan: default_plan
Sat Mar 19 14:51:12 2022
Starting background process VKRM
Sat Mar 19 14:51:12 2022
VKRM started with pid=40, OS id=9815
Starting background process GTX0
Sat Mar 19 14:51:12 2022
GTX0 started with pid=41, OS id=9817
Starting background process RCBG
Sat Mar 19 14:51:12 2022
RCBG started with pid=42, OS id=9819
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Sat Mar 19 14:51:13 2022
QMNC started with pid=43, OS id=9823
nodepleted: alter database open
Sat Mar 19 14:51:13 2022
minact-scn: Inst 1 is a slave inc#:12 mmon proc-id:191777 status:0x2
minact-scn status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000
Sat Mar 19 14:51:13 2022
Starting background process CJQ0
Sat Mar 19 14:51:14 2022
CJQ0 started with pid=47, OS id=9886
Setting Resource Manager plan SCHEDULER[0x32DE]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
可以看到饼记，其中最要中的是關(guān)于redo日志的應(yīng)用香伴，也即是說(shuō)，redo日志是在Oracle數(shù)據(jù)庫(kù)啟動(dòng)到Open狀態(tài)下才被應(yīng)用的具则。突然想起即纲，Oracle奉行的日志先行原則，redo日志是用來(lái)記錄事務(wù)中的博肋，但在mount狀態(tài)下顯然數(shù)據(jù)庫(kù)并不會(huì)產(chǎn)生事務(wù)(僅提供對(duì)一些參數(shù)文件的查詢)低斋，因此在mount狀態(tài)下僅僅掛載了redo日志組并沒(méi)有啟用。
在redo日志被打開后匪凡，數(shù)據(jù)庫(kù)立刻進(jìn)行了事務(wù)回滾(Undo initialization finished serial:0 start:)膊畴。接著設(shè)置數(shù)據(jù)庫(kù)字符集(Database Characterset is AL32UTF8)，最后病游，數(shù)據(jù)庫(kù)完全開啟巴比。
在看到網(wǎng)上一篇博客中寫道："OPEN，這種模式將啟動(dòng)實(shí)例，加載并打開數(shù)據(jù)庫(kù)"這里時(shí)轻绞，有點(diǎn)誤差，數(shù)據(jù)庫(kù)的實(shí)例在數(shù)據(jù)庫(kù)啟動(dòng)到nomount狀態(tài)后已經(jīng)被啟動(dòng)佣耐，并非是在open階段被啟動(dòng)的政勃。
此時(shí)數(shù)據(jù)庫(kù)資源應(yīng)為Open狀態(tài)
#crsctl stat res -t | grep -A2 ora.test.db
ora.test.db
1 ONLINE ONLINE node1 Open
2 ONLINE ONLINE node2 Open
