数据库的数据都存放在数据文件当中，当数据库开启时，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.0
BBED> 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.
参考：http://blog.itpub.net/26015009/viewspace-748005/
http://blog.csdn.net/tianlesoftware/article/details/5006580
《DBA入门，诊断与进阶案例》 盖国强著
