Oracle数据库启动时候,首先需要在内存中创建数据库初始化的结构,然后把数据文件中与初始化相关的内容读入这个结构,进而执行这些内容,这个初始化的结构就是bootstrap$,这些内容就是bootstrap$表中的sql语句。
实验分析(11gR2):
1. 跟踪数据库打开阶段的sql
点击(此处)折叠或打开
- SYS AS SYSDBA >startup mount;
- SYS AS SYSDBA >alter session set sql_trace = true;
- SYS AS SYSDBA >alter database open;
点击(此处)折叠或打开
- SYS AS SYSDBA >select * from v$diag_info where NAME='Default Trace File';--Oracle11g下才能用这个视图
- INST_ID|NAME |VALUE
- ----------|--------------------|--------------------------------------------------
- 1|Default Trace File |/u01/diag/rdbms/hx/hx/trace/hx_ora_10720.trc
-
- 使用tkprof格式化跟踪文件:
- SYS AS SYSDBA >show parameter timed_stat;
- NAME |TYPE |VALUE
- ------------------------------------|-----------|------------------------------
- timed_statistics |boolean |TRUE
- SYS AS SYSDBA >show parameter dump_file_size;
- NAME |TYPE |VALUE
- ------------------------------------|-----------|------------------------------
- max_dump_file_size |string |unlimited
- SYS AS SYSDBA >show parameter user_dump;
- NAME |TYPE |VALUE
- ------------------------------------|-----------|------------------------------
- user_dump_dest |string |/u01/diag/rdbms/hx/hx/trace
点击(此处)折叠或打开
- oracle[~]$cd /u01/diag/rdbms/hx/hx/trace
- oracle[~]$tkprof hx_ora_10634.sql hx_ora_10634.txt
********************************************************************************
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))
上面是第一个sql,可以看到,oracle在内存中创建这个表结构,数据是从file 1 block 520开始读取并加载的
看看file 1 block 520存放了哪个段的内容:
点击(此处)折叠或打开
- SYS AS SYSDBA >select SEGMENT_NAME,FILE_ID,BLOCK_ID,TABLESPACE_NAME from
- dba_extents where file_id=1 and block_id=520;
- SEGMENT_NAME | FILE_ID| BLOCK_ID|TABLESPACE_NAME
- -------------------------|----------|----------|--------------------
- BOOTSTRAP$ | 1| 520|SYSTEM
- SYS AS SYSDBA >select SEGMENT_NAME,FILE_ID,BLOCK_ID,blocks,TABLESPACE_NAME from dba_extents where SEGMENT_NAME='BOOTSTRAP$';
- SEGMENT_NAME | FILE_ID| BLOCK_ID| BLOCKS|TABLESPACE_NAME
- -------------------------|----------|----------|----------|--------------------
- BOOTSTRAP$ | 1| 520| 8|SYSTEM | 1| 520|SYSTEM
继续跟踪文件:
********************************************************************************
SQL ID: 6apq2rjyxmxpj Plan Hash: 867914364
select line#, sql_text
from
bootstrap$ where obj# != :1
从上面看到,oracle在装载file 1 block 520后,就开始从这个表读取内容,实际上读取的是一些sql语句,然后利用这些sql语句创建数据库启动的必要对象:
SYS AS SYSDBA >select line#,obj#,sql_text from bootstrap$ where rownum<=3;
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))
20| 20|CREATE TABLE ICOL$("OBJ#" NUMBER NOT NULL,"BO#" NUMBER NOT NULL,"COL#" NUMBER NOT NULL,"POS#" NUMBER
| | NOT NULL,"SEGCOL#" NUMBER NOT NULL,"SEGCOLLENGTH" NUMBER NOT NULL,"OFFSET" NUMBER NOT NULL,"INTCOL#
| |" NUMBER NOT NULL,"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" V
| |ARCHAR2(1000),"SPARE6" DATE) STORAGE ( OBJNO 20 TABNO 4) CLUSTER C_OBJ#(BO#)
42| 42|CREATE INDEX I_ICOL1 ON ICOL$(OBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1
| |024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 42 EXTENTS (FILE 1 BLOCK 384))
继续跟踪文件,可以看到,就是在执行这些语句:
CREATE ROLLBACK SEGMENT SYSTEM STORAGE ( INITIAL 112K NEXT 56K MINEXTENTS 1
MAXEXTENTS 32765 OBJNO 0 EXTENTS (FILE 1 BLOCK 128))
………………………………………………………………………………………………………………………………………………………………
********************************************************************************
………………………………………………………………………………………………………………………………………………………………
CREATE CLUSTER C_OBJ#("OBJ#" NUMBER) PCTFREE 5 PCTUSED 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
********************************************************************************
CREATE INDEX I_OBJ# ON CLUSTER C_OBJ# PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 OBJNO 3 EXTENTS (FILE 1 BLOCK 168))
………………………………………………………………………………………………………………………………………………………………
4. bootstrap$的生成与定位
那么,数据文件中的 bootstrap$是怎么生成的呢?那就要归功于创建数据库时候的sql.bsp文件了。隐含参数记录了这个文件:
点击(此处)折叠或打开
- SYS AS SYSDBA >col ksppinm for a20
- col ksppdesc for a40
- col ksppstvl for a10
- select ksppinm,ksppdesc,ksppstvl
- from sys.x$ksppi x,sys.x$ksppcv y
- where x.inst_id = userenv('Instance') and y.inst_id = userenv('Instance') and x.indx = y.indx and (x.ksppinm like '%&par%');
- /
old 3: where x.inst_id = userenv('Instance') and y.inst_id = userenv('Instance') and x.indx = y.indx and (x.ksppinm like '%&par%')
new 3: where x.inst_id = userenv('Instance') and y.inst_id = userenv('Instance') and x.indx = y.indx and (x.ksppinm like '%init_sql%')
KSPPINM |KSPPDESC |KSPPSTVL
--------------------|----------------------------------------|----------
_init_sql_file |File containing SQL statements to execut|?/rdbms/ad
|e upon database creation |min/sql.bs
| |q
创建数据库的create database语句隐含就要执行sql.bsp中的内容,这个文件包含了许多个bsp文件,其中的dcore.bsq文件就有表bootstrap$的创建语句:
create table bootstrap$
( line# number not null, /* statement order id */
obj# number not null, /* object number */
sql_text varchar2("M_VCSZ") not null) /* statement */
storage (initial 50K) /* to avoid space management during IOR I */
//
/* "//" required for bootstrap */
通过上面的分析,数据库在内存中建立表结构后,直接定位到file 1 block 520,从而继续后面的工作。那么,他怎么知道是这个块是bootstrap$的块呢?那就要看system表空间记录的root dba了。
5. root dba 相关
system表空间的数据文件头记录了这个东西,转存system表空间一号数据文件头看看:
10 STYLE FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=763100300=0x2d7bfc8c, Db Name='HX'
Activation ID=0=0x0
Control Seq=14715=0x397b, File size=51200=0xc800
File Number=1, Blksiz=8192, File Type=3 DATA
Tablespace #0 - SYSTEM rel_fn:1
Creation at scn: 0x0000.00000009 02/06/2013 17:15:16
Backup taken at scn: 0x0000.000cec73 03/31/2013 21:44:20 thread:1
reset logs count:0x30817c6a scn: 0x0000.003cef48
prev reset logs count:0x305eefc2 scn: 0x0000.000c7def
recovered at 05/12/2013 22:15:57
status:0x2004 root dba:0x00400208 chkpt cnt: 1095 ctl cnt:1094
begin-hot-backup file size: 41600
Checkpointed at scn: 0x0000.0054c1ba 05/13/2013 20:02:02
thread:1 rba:(0xa2.4d1e.10)
注意:只有system表空间的文件头才具备root dba,它用来定位数据库引导的bootstrap$信息,root dba一共4字节。
root dba(0x00400208)转换为2进制:0000000001 0000000000001000001000,前面10位表示文件号,就是1(/u01/oradata/hx/system01.dbf)。后面22位表示数据文件1上的第520个块。
函数转换root dba(eygle提供的脚本)
SYS AS SYSDBA >CREATE OR REPLACE FUNCTION getbfno (p_dba IN VARCHAR2)
2 RETURN VARCHAR2
3 IS
4 l_str VARCHAR2 (255) DEFAULT NULL;
5 BEGIN
6 l_str :=
7 'datafile# is:'
8 || DBMS_UTILITY.data_block_address_file
9 (TO_NUMBER (LTRIM (p_dba, '0x'),'xxxxxxxx'))
10 || chr(10)||'datablock is:'
11 || DBMS_UTILITY.data_block_address_block
12 (TO_NUMBER (LTRIM (p_dba, '0x'),'xxxxxxxx'));
13 RETURN l_str;
14 END;
15 /
col bfno for a20
Function created.
SYS AS SYSDBA >select getbfno('&a') bfno from dual;
Enter value for a: 00400208
old 1: select getbfno('&a') bfno from dual
new 1: select getbfno('00400208') bfno from dual
BFNO
--------------------
datafile# is:1
datablock is:520
不同版本数据库中root dba 块的意义:
通过下面sql语句,可以看到头块为520之前的块的对象。
点击(此处)折叠或打开
- SYS AS SYSDBA >select b.object_id,a.segment_name,a.segment_type,a.header_block from dba_segments a,dba_objects b where
- a.segment_name=b.object_name(+) and a.header_file=1 and a.header_block<=520 order by a.header_block;
- OBJECT_ID|SEGMENT_NAME |SEGMENT_TYPE |HEADER_BLOCK
|SYSTEM |ROLLBACK | 128
2|C_OBJ# |CLUSTER | 144
3|I_OBJ# |INDEX | 168
6|C_TS# |CLUSTER | 176
7|I_TS# |INDEX | 184
8|C_FILE#_BLOCK# |CLUSTER | 192
9|I_FILE#_BLOCK# |INDEX | 200
10|C_USER# |CLUSTER | 208
11|I_USER# |INDEX | 216
15|UNDO$ |TABLE | 224
17|FILE$ |TABLE | 232
18|OBJ$ |TABLE | 240
23|PROXY_DATA$ |TABLE | 248
24|I_PROXY_DATA$ |INDEX | 256
25|PROXY_ROLE_DATA$ |TABLE | 264
26|I_PROXY_ROLE_DATA$_1 |INDEX | 272
27|I_PROXY_ROLE_DATA$_2 |INDEX | 280
28|CON$ |TABLE | 288
29|C_COBJ# |CLUSTER | 296
30|I_COBJ# |INDEX | 304
33|I_TAB1 |INDEX | 312
34|I_UNDO1 |INDEX | 320
35|I_UNDO2 |INDEX | 328
36|I_OBJ1 |INDEX | 336
37|I_OBJ2 |INDEX | 344
38|I_OBJ3 |INDEX | 352
39|I_OBJ4 |INDEX | 360
40|I_OBJ5 |INDEX | 368
41|I_IND1 |INDEX | 376
42|I_ICOL1 |INDEX | 384
43|I_FILE1 |INDEX | 392
44|I_FILE2 |INDEX | 400
45|I_TS1 |INDEX | 408
46|I_USER1 |INDEX | 416
47|I_USER2 |INDEX | 424
48|I_COL1 |INDEX | 432
49|I_COL2 |INDEX | 440
50|I_COL3 |INDEX | 448
51|I_CON1 |INDEX | 456
52|I_CON2 |INDEX | 464
53|I_CDEF1 |INDEX | 472
54|I_CDEF2 |INDEX | 480
55|I_CDEF3 |INDEX | 488
56|I_CDEF4 |INDEX | 496
57|I_CCOL1 |INDEX | 504
58|I_CCOL2 |INDEX | 512
59|BOOTSTRAP$ |TABLE | 520
从上图,对比之前bootstrap$中的sql语句,我们可以看到,520之前的块都和数据库启动相关
在oracle 10g和11g中,520块正好就是bootstrap$的头块,可以转储这个块分析:
alter system dump datafile 1 block 520;
frmt: 0x02 chkval: 0xe443 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
……………………………………………………………………………………………………………………………………………………………………………………
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 7
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x0040020c ext#: 0 blk#: 3 ext size: 7
#blocks in seg. hdr's freelists: 1
#blocks below: 3
mapblk 0x00000000 offset: 0
Unlocked
Map Header:: next 0x00000000 #extents: 1 obj#: 59 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x00400209 length: 7
nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 3
SEG LST:: flg: USED lhd: 0x0040020b ltl: 0x0040020b
可以看到,这个块是obj#:=59的对象的头块,实际就是bootstrap$,以后的7个块(从0x00400209 开始,即521号块开始)都是bootstrap中存储的内容(当然,这些内容就是创建那些对象的sql语句了)。 简单的说,520号块就像一级位图块。 如果是oracle9i,第520号块存储的是一个cache段,这个cache并不直接存放bootstrap$的内容,它类似二级位图块,指向bootstrap$的头块。指向头块后,数据库需要做的工作和10g就差不多了。
6 结论:
oracle open阶段初始化的时候,要做下面几件事(10g or 11g):
1). 在内存中创建bootstraps$结构
1). 读system表空间中的1号文件的数据文件头中记录的root dba(实际就是bootstrap$的头块)
2). 通过root dba定位到bootstrap$
3). 把bootstrap$表中的数据装载到内存中的bootstraps$
4). 执行bootstrap$中的sql语句,创建一系列的数据字典。
5). 打开数据库,有冤报冤,有仇报仇,哈哈。
注意:如果是异常关闭数据库,那么需要前滚恢复完成后才能进行初始化。因为数据库一致性后,才能创建相关对象。跟踪原始日志的下面内容反应这一过程:
Started writing zeroblks thread 1 seq 163 blocks 125065-125072
*** 2013-05-14 10:36:56.943
Completed writing zeroblks thread 1 seq 163
==== Redo read statistics for thread 1 ====
Total physical reads (from disk and memory): 65536Kb
-- Redo read_disk statistics --
Read rate (ASYNC): 62531Kb in 0.50s => 122.13 Mb/sec
Longest record: 7Kb, moves: 0/45758 (0%)
Change moves: 17/18 (94%), moved: 0Mb
Longest LWN: 1542Kb, moves: 15/91 (16%), moved: 14Mb
Last redo scn: 0x0000.0055bed1 (5619409)
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets = 262144
Longest hash chain = 1
Average hash chain = 5143/5143 = 1.0
Max compares per lookup = 1
KCRA: start recovery claims for 5143 data blocks
*** 2013-05-14 10:36:57.100
KCRA: blocks processed = 5143/5143, claimed = 5143, eliminated = 0
*** 2013-05-14 10:36:57.651
Completed redo application of 37.74MB
*** 2013-05-14 10:37:04.636
Completed recovery checkpoint
----- Recovery Hash Table Statistics ---------
Hash table buckets = 262144
Longest hash chain = 1
Average hash chain = 5143/5143 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 35602/67666 = 0.5
----------------------------------------------
Recovery sets nab of thread 1 seq 163 to 125065 with 8 zeroblks
*** 2013-05-14 10:37:06.383
=====================
PARSING IN CURSOR #140574972147112 len=188 dep=1 uid=0 oct=1 lid=0 tim=1368499026383791 hv=4006182593 ad='6c8c9f50' sqlid
='32r4f1brckzq1'
create table bootstrap$ ( line# number not null, obj# number not null, sql_text varchar2(4000) no
t null) storage (initial 50K objno 59 extents (file 1 block 520))
END OF STMT
7.思考:
1.oracle为什么要这样初始化数据库?
2.bootstraps$之前的段空间管理技术是否普通表空间的管理技术一样?如果不一样,有什么异同?
3.oracle 10g和11g中,bootstraps$的头块是否可以看作是段头?
4.初始化时候,创建的哪些对象分别是干什么的,为什么每次启动数据库都需要创建那些对象,一次创建不就行了么?。
5.那个rollback对象有什么特殊性?。
6.初始化是否要等到回滚完成才能进行?