數據庫的數據都存放在數據文件當中，當數據庫開啟時，Oracle怎么完成從數據文件讀取數據到內存當中這個初始化過程呢?SQL>?startup?mount;

ORACLE?instance?started.

Total?System?Global?Area?6680915968?bytes

Fixed?Size??????????????????2213936?bytes

Variable?Size????????????3758098384?bytes

Database?Buffers?????????2885681152?bytes

Redo?Buffers???????????????34922496?bytes

Database?mounted.

SQL>?alter?session?set?sql_trace=true;

SQL>?alter?database?open;

通過上面的開啟SQL跟蹤參數后，啟動數據的信息被記錄在trace文件中，之前獲取trace文件都是通過gettrcname.sql獲取的，11G增加了一個新的視圖可以直接獲取到trace文件：select?value?from?v$diag_info?where?name?=?'Default?Trace?File';

VALUE

--------------------------------------------------------------------

/DBBK/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_22987.trc

如果直接去查看該文件，估計有點費勁，通過tkprof命令轉換成人類可讀的模式：tkprof?orcl_ora_22987.trc??1.trc

查看1.trc這個文件可以發現從開啟SQL_TRACE開始記錄的所有語句內容：SQL?ID:?4tk6t8tfsfqbf

Plan?Hash:?0

alter?session?set?sql_trace=true

call?????count???????cpu????elapsed???????disk??????query????current????????rows

-------?------??--------?----------?----------?----------?----------??----------

Parse????????0??????0.00???????0.00??????????0??????????0??????????0???????????0

Execute??????1??????0.00???????0.00??????????0??????????0??????????0???????????0

Fetch????????0??????0.00???????0.00??????????0??????????0??????????0???????????0

-------?------??--------?----------?----------?----------?----------??----------

total????????1??????0.00???????0.00??????????0??????????0??????????0???????????0

Misses?in?library?cache?during?parse:?0

Misses?in?library?cache?during?execute:?1

Optimizer?mode:?ALL_ROWS

Parsing?user?id:?SYS

......

SQL?ID:?a01hp0psv0rrh

Plan?Hash:?0

alter?database?open

call?????count???????cpu????elapsed???????disk??????query????current????????rows

-------?------??--------?----------?----------?----------?----------??----------

Parse????????1??????0.00???????0.00??????????0??????????0??????????0???????????0

Execute??????1??????0.32???????1.45?????????19?????????34?????????63???????????0

Fetch????????0??????0.00???????0.00??????????0??????????0??????????0???????????0

-------?------??--------?----------?----------?----------?----------??----------

total????????2??????0.32???????1.45?????????19?????????34?????????63???????????0

Misses?in?library?cache?during?parse:?1

Optimizer?mode:?ALL_ROWS

Parsing?user?id:?SYS

從發出open語句后開始查看：SQL?ID:?32r4f1brckzq1

Plan?Hash:?0

create?table?bootstrap$?(?line#?????????number?not?null,???obj#

number?not?null,???sql_text???varchar2(4000)?not?null)???storage?(initial

50K?objno?59?extents?(file?1?block?520))

call?????count???????cpu????elapsed???????disk??????query????current????????rows

-------?------??--------?----------?----------?----------?----------??----------

Parse????????1??????0.00???????0.00??????????0??????????0??????????0???????????0

Execute??????1??????0.00???????0.00??????????0??????????0??????????0???????????0

Fetch????????0??????0.00???????0.00??????????0??????????0??????????0???????????0

-------?------??--------?----------?----------?----------?----------??----------

total????????2??????0.00???????0.00??????????0??????????0??????????0???????????0

Misses?in?library?cache?during?parse:?1

Optimizer?mode:?CHOOSE

Parsing?user?id:?SYS???(recursive?depth:?1)

這一步，Oracle在內存中創建了一個bootstrap$的表結構，并從file 1 block 520(數據文件1的520號塊)內讀取數據到該表中。

到這里我們可以查看一下file 1 block 520中記錄的對象SQL>?select?segment_name,file_id,block_id?from?dba_extents?where?file_id=1?and?block_id=520;

SEGMENT_NAME????????????FILE_ID???BLOCK_ID

--------------------?----------?----------

BOOTSTRAP$????????????????????1????????520

我們再來看看這張表中的字段和內容：select?line#,obj#,sql_text?from?bootstrap$;

LINE#???????OBJ#?SQL_TEXT

----------?----------?--------------------------------------------------

-1?????????-1?8.0.0.0.0

0??????????0?CREATE?ROLLBACK?SEGMENT?SYSTEM?STORAGE?(??INITIAL

112K?NEXT?56K?MINEXTENTS?1?MAXEXTENTS?32765?OBJNO

0?EXTENTS?(FILE?1?BLOCK?128))

2??????????2?CREATE?CLUSTER?C_OBJ#("OBJ#"?NUMBER)?PCTFREE?5?PCT

USED?40?INITRANS?2?MAXTRANS?255?STORAGE?(??INITIAL

136K?NEXT?200K?MINEXTENTS?1?MAXEXTENTS?2147483645

PCTINCREASE?0?OBJNO?2?EXTENTS?(FILE?1?BLOCK?144))

SIZE?800

3??????????3?CREATE?INDEX?I_OBJ#?ON?CLUSTER?C_OBJ#?PCTFREE?10?I

NITRANS?2?MAXTRANS?255?STORAGE?(??INITIAL?64K?NEXT

1024K?MINEXTENTS?1?MAXEXTENTS?2147483645?PCTINCRE

ASE?0?OBJNO?3?EXTENTS?(FILE?1?BLOCK?168))

4??????????4?CREATE?TABLE?TAB$("OBJ#"?NUMBER?NOT?NULL,"DATAOBJ#

"?NUMBER,"TS#"?NUMBER?NOT?NULL,"FILE#"?NUMBER?NOT

NULL,"BLOCK#"?NUMBER?NOT?NULL,"BOBJ#"?NUMBER,"TAB#

"?NUMBER,"COLS"?NUMBER?NOT?NULL,"CLUCOLS"?NUMBER,"

PCTFREE$"?NUMBER?NOT?NULL,"PCTUSED$"?NUMBER?NOT?NU

LL,"INITRANS"?NUMBER?NOT?NULL,"MAXTRANS"?NUMBER?NO

T?NULL,"FLAGS"?NUMBER?NOT?NULL,"AUDIT$"?VARCHAR2(3

8)?NOT?NULL,"ROWCNT"?NUMBER,"BLKCNT"?NUMBER,"EMPCN

T"?NUMBER,"AVGSPC"?NUMBER,"CHNCNT"?NUMBER,"AVGRLN"

NUMBER,"AVGSPC_FLB"?NUMBER,"FLBCNT"?NUMBER,"ANALY

ZETIME"?DATE,"SAMPLESIZE"?NUMBER,"DEGREE"?NUMBER,"

INSTANCES"?NUMBER,"INTCOLS"?NUMBER?NOT?NULL,"KERNE

LCOLS"?NUMBER?NOT?NULL,"PROPERTY"?NUMBER?NOT?NULL,

"TRIGFLAG"?NUMBER,"SPARE1"?NUMBER,"SPARE2"?NUMBER,

"SPARE3"?NUMBER,"SPARE4"?VARCHAR2(1000),"SPARE5"?V

ARCHAR2(1000),"SPARE6"?DATE)?STORAGE?(??OBJNO?4?TA

BNO?1)?CLUSTER?C_OBJ#(OBJ#)

5??????????5?CREATE?TABLE?CLU$("OBJ#"?NUMBER?NOT?NULL,"DATAOBJ#

"?NUMBER,"TS#"?NUMBER?NOT?NULL,"FILE#"?NUMBER?NOT

NULL,"BLOCK#"?NUMBER?NOT?NULL,"COLS"?NUMBER?NOT?NU

LL,"PCTFREE$"?NUMBER?NOT?NULL,"PCTUSED$"?NUMBER?NO

T?NULL,"INITRANS"?NUMBER?NOT?NULL,"MAXTRANS"?NUMBE

R?NOT?NULL,"SIZE$"?NUMBER,"HASHFUNC"?VARCHAR2(30),

"HASHKEYS"?NUMBER,"FUNC"?NUMBER,"EXTIND"?NUMBER,"F

LAGS"?NUMBER,"DEGREE"?NUMBER,"INSTANCES"?NUMBER,"A

VGCHN"?NUMBER,"SPARE1"?NUMBER,"SPARE2"?NUMBER,"SPA

RE3"?NUMBER,"SPARE4"?NUMBER,"SPARE5"?VARCHAR2(1000

),"SPARE6"?VARCHAR2(1000),"SPARE7"?DATE)?STORAGE?(

OBJNO?5?TABNO?2)?CLUSTER?C_OBJ#(OBJ#)

......

可以看到該表中又包含了其它創建在內容中的對象,到這里，我們回過頭來看SQL_TRACE文件：SQL?ID:?6apq2rjyxmxpj

Plan?Hash:?867914364

select?line#,?sql_text

from

bootstrap$?where?obj#?!=?:1

call?????count???????cpu????elapsed???????disk??????query????current????????rows

-------?------??--------?----------?----------?----------?----------??----------

Parse????????1??????0.00???????0.00??????????0??????????0??????????0???????????0

Execute??????1??????0.00???????0.00??????????0??????????0??????????0???????????0

Fetch???????60??????0.00???????0.00??????????4?????????61??????????0??????????59

-------?------??--------?----------?----------?----------?----------??----------

total???????62??????0.00???????0.00??????????4?????????61??????????0??????????59

Misses?in?library?cache?during?parse:?1

Misses?in?library?cache?during?execute:?1

Optimizer?mode:?CHOOSE

Parsing?user?id:?SYS???(recursive?depth:?1)

Rows?????Row?Source?Operation

-------??---------------------------------------------------

59??TABLE?ACCESS?FULL?BOOTSTRAP$?(cr=61?pr=4?pw=0?time=0?us)

********************************************************************************

SQL?ID:?864bmh11c121a

Plan?Hash:?0

CREATE?ROLLBACK?SEGMENT?SYSTEM?STORAGE?(??INITIAL?112K?NEXT?56K?MINEXTENTS?1

MAXEXTENTS?32765?OBJNO?0?EXTENTS?(FILE?1?BLOCK?128))

call?????count???????cpu????elapsed???????disk??????query????current????????rows

-------?------??--------?----------?----------?----------?----------??----------

Parse????????1??????0.00???????0.00??????????0??????????0??????????0???????????0

Execute??????1??????0.00???????0.00??????????0??????????0??????????0???????????0

Fetch????????0??????0.00???????0.00??????????0??????????0??????????0???????????0

-------?------??--------?----------?----------?----------?----------??----------

total????????2??????0.00???????0.00??????????0??????????0??????????0???????????0

Misses?in?library?cache?during?parse:?1

Optimizer?mode:?CHOOSE

Parsing?user?id:?SYS???(recursive?depth:?1)

......

到這里我想，大家大概就能看懂了，跟我剛才查看bootstrap$一樣，數據庫去查找該表中的內容，逐一運行 (:1是系統給定的變量，估計該變量值為1)。

即：Oracle啟動先將Bootstrap$加載到內存中，隨后逐一將數據初始化所需的對象加載到內存中，在BootStrap$表中的對象，每一個都值得研究，這里暫時不詳細說明。

通過上述說明，可以看出bootstrap$的重要性，當然在書中還做了一個小測試，就是改動該表中的內容，看看對數據庫啟動是否造成影響(輕易不要做這樣的測試，做之前最好備份數據庫，當然這個測試也是可以恢復的)：SQL>?update?bootstrap$?set?sql_text='9.0.0.1.0'?where?obj#=-1;

1?row?updated.

SQL>?select?line#,obj#,sql_text?from?bootstrap$?where?obj#=-1?order?by?obj#;

LINE#???????OBJ#?SQL_TEXT

----------?----------?--------------------------------------------------

-1?????????-1?9.0.0.1.0

SQL>?commit;

Commit?complete.

SQL>?shutdown?immediate;

SQL>?startup;

ORACLE?instance?started.

Total?System?Global?Area?6680915968?bytes

Fixed?Size??????????????????2213936?bytes

Variable?Size????????????3758098384?bytes

Database?Buffers?????????2885681152?bytes

Redo?Buffers???????????????34922496?bytes

Database?mounted.

ORA-01092:?ORACLE?instance?terminated.?Disconnection?forced

ORA-00704:?bootstrap?process?failure

ORA-00702:?bootstrap?verison?'9.0.0.1.0'?inconsistent?with?version?'8.0.0.0.0'

Process?ID:?4883

Session?ID:?96?Serial?number:?3

可以看到數據庫mount沒問題，但open的時候讀取bootstrap$報錯，錯誤內容頁寫的很清楚，處理bootstrap$失敗，版本號不一致。所以對于這類問題的解決方式，最好是時長對數據庫進行備份，當問題發生時進行不完全恢復。

通過Oracle自帶工具BBED(BLOCK BROWSER/EDITOR)工具進行恢復(關于BBED的工具的編譯安裝，我的博客中有記載：http://onlinekof2001.blog.51cto.com/3106724/1604275，這里不詳細介紹)

增加配置文件：指定塊大小，修改模式和BBED連接數據文件列表

[oracle@021Y-SH-BKAP?oracle]$?cat?par.bbd

blocksize=8192

listfile=file.lst

mode=edit

指定文件列表中的數據文件格式：?數據文件號?路徑?大小

[oracle@021Y-SH-BKAP?oracle]$?cat?file.lst

1?/DBBK/oracle/oradata/orcl/system01.dbf?723525632

用bbed工具訪上面的參數文件(BBED默認密碼blockedit)

[oracle@021Y-SH-BKAP?oracle]$?bbed?parfile=par.bbd

Password:?*********

進行如下操作，因為我們已知了bootstrap是存放在file 1 block 520上的(我這里有點犯傻，就一直在該塊和前面的塊找改動的內容，實際記錄的內容應該向后移動一個塊)：BBED:?Release?2.0.0.0.0?-?Limited?Production?on?Thu?Jan?15?15:56:23?2015

Copyright?(c)?1982,?2009,?Oracle?and/or?its?affiliates.??All?rights?reserved.

*************?!!!?For?Oracle?Internal?Use?only?!!!?***************

BBED>?set?dba?1,521?offset?0

DBA?????????????0x00400209?(4194825?1,521)

OFFSET??????????0

定位被自己修改的內容(關鍵要找到內容所在的塊)BBED>?find?/c?9.0.0.1.0

File:?/DBBK/oracle/oradata/orcl/system01.dbf?(1)

Block:?521??????????????Offsets:?8181?to?8191???????????Dba:0x00400209

----------------------------------------------------------------------

302e302e?312e3002?0676c9

這里用的是ASCII碼，顯示的內容明顯為0.0.1.0 結果不正確，應該向前偏移2個單位BBED>?dump?/v?dba?1,521?offset?8179?count?11

File:?/DBBK/oracle/oradata/orcl/system01.dbf?(1)

Block:?521?????Offsets:?8179?to?8189??Dba:0x00400209

-------------------------------------------------------

392e302e?302e312e?300206????????????l?9.0.0.1.0..

修改該內容，恢復為8.0.0.0.0BBED>?modify?/c?'8.0.0.0.0'?dba?1,521?offset?8179

File:?/DBBK/oracle/oradata/orcl/system01.dbf?(1)

Block:?521??????????????Offsets:?8179?to?8189???????????Dba:0x00400209

------------------------------------------------------------------------

382e302e?302e302e?300206

BBED>?dump?/v?dba?1,521?offset?8179?count?11

File:?/DBBK/oracle/oradata/orcl/system01.dbf?(1)

Block:?521?????Offsets:?8179?to?8189??Dba:0x00400209

-------------------------------------------------------

382e302e?302e302e?300206????????????l?8.0.0.0.0..

<16?bytes?per?line>

BBED>?sum?dba?1,521?apply

Check?value?for?File?1,?Block?521:

current?=?0x5f51,?required?=?0x5f51

應用后可以看到current的值與required的值相同。再次啟動正常：SQL>?startup

ORACLE?instance?started.

Total?System?Global?Area?6680915968?bytes

Fixed?Size??????????????????2213936?bytes

Variable?Size????????????3758098384?bytes

Database?Buffers?????????2885681152?bytes

Redo?Buffers???????????????34922496?bytes

Database?mounted.

Database?opened.

《DBA入門，診斷與進階案例》 蓋國強著

總結

以上是生活随笔為你收集整理的oracle中的open,Oracle 深入分析Open过程的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。