初探oracle open初始化過程

Diy_os發表於2015-04-26
[oracle@localhost ~]$ lsb_release -a
LSB Version:    :core-3.1-ia32:core-3.1-noarch:graphics-3.1-ia32:graphics-3.1-noarch
Distributor ID: EnterpriseEnterpriseServer
Description:    Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
Release:        5.5
Codename:       Carthage
SQL> select * from v$version where rownum < 2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SQL> startup mount;
ORACLE 例程已經啟動。
SQL> alter session set sql_trace = true;
會話已更改。
SQL> alter database open;
資料庫已更改。
SQL> select * from v$diag_info where NAME='Default Trace File';


   INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
         1 Default Trace File
/u01/app/oracle/diag/rdbms/orcl3939/orcl3939/trace/orcl3939_ora_14443.trc

11g提供了v$diag_info,透過查詢V$diag_info可以很容易找到自身服務程式的trace檔案位置
當然由oracle生成trace檔案的特性:
SQL> select distinct sid from v$mystat; 
    SID
----------
         9
SQL> select paddr from v$session where sid=9;
PADDR
--------
34FC8DAC
SQL> select spid from v$process where addr='34FC8DAC';
SPID
------------------------
14443

也很快在茫茫的trace檔案中找到我們想要的檔案。

*** 2015-04-26 00:28:20.034
*** SESSION ID:(9.3) 2015-04-26 00:28:20.034
*** CLIENT ID:() 2015-04-26 00:28:20.034
*** SERVICE NAME:() 2015-04-26 00:28:20.034
*** MODULE NAME:(sqlplus@localhost.localdomain (TNS V1-V3)) 2015-04-26 00:28:20.034
*** ACTION NAME:() 2015-04-26 00:28:20.034
 =====================
PARSING IN CURSOR #1 len=34 dep=0 uid=0 oct=42 lid=0 tim=1429979299982435 hv=2069488880 ad='73fed0' sqlid='1hgzr5xxpmt7h'
alter session set sql_trace = true
END OF STMT
EXEC #1:c=0,e=143,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1429979299933691
*** 2015-04-26 00:28:44.514
CLOSE #1:c=0,e=12,dep=0,type=0,tim=1429979324514259
*** 2015-04-26 00:28:44.516
XCTEND rlbk=0, rd_only=1, tim=1429979324516325
=====================
PARSING IN CURSOR #1 len=19 dep=0 uid=0 oct=35 lid=0 tim=1429979324552260 hv=1907384048 ad='349dcfe4' sqlid='a01hp0psv0rrh'
alter database open
END OF STMT
PARSE #1:c=3999,e=36299,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1429979324552257
=====================
PARSING IN CURSOR #2 len=188 dep=1 uid=0 oct=1 lid=0 tim=1429979324829034 hv=4006182593 ad='349db650' sqlid='32r4f1brckzq1'
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))
END OF STMT
PARSE #2:c=1000,e=926,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1429979324829031
EXEC #2:c=0,e=378,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=1429979324829510
CLOSE #2:c=0,e=9,dep=1,type=0,tim=1429979324829642
=====================
PARSING IN CURSOR #2 len=55 dep=1 uid=0 oct=3 lid=0 tim=1429979324830543 hv=2111436465 ad='349da4f8' sqlid='6apq2rjyxmxpj'
select line#, sql_text from bootstrap$ where obj# != :1
END OF STMT
PARSE #2:c=999,e=878,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1429979324830540
EXEC #2:c=3000,e=59167,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=867914364,tim=1429979324889837
FETCH #2:c=1000,e=1299,p=4,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=867914364,tim=1429979324891270
FETCH #2:c=0,e=21,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=867914364,tim=1429979324891377
FETCH #2:c=1000,e=329,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=867914364,tim=1429979324891738
FETCH #2:c=0,e=17,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=867914364,tim=1429979324891814
FETCH #2:c=0,e=9,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=867914364,tim=1429979324891852
FETCH #2:c=0,e=9,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=867914364,tim=1429979324891886
FETCH #2:c=0,e=31,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=867914364,tim=1429979324891942
FETCH #2:c=0,e=12,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=867914364,tim=1429979324891992
..........
..........
FETCH #2:c=0,e=9,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=867914364,tim=1429979324897158
FETCH #2:c=0,e=8,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=867914364,tim=1429979324897190
FETCH #2:c=0,e=8,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=867914364,tim=1429979324897222
STAT #2 id=1 cnt=59 pid=0 pos=1 obj=59 op='TABLE ACCESS FULL BOOTSTRAP$ (cr=61 pr=4 pw=0 time=0 us)'
CLOSE #2:c=0,e=30,dep=1,type=0,tim=1429979324927299
CREATE INDEX I_UNDO2 ON UNDO$(NAME) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 35 EXTENTS (FILE 1 BLOCK 328))
END OF STMT
PARSE #2:c=999,e=961,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=4258903948,tim=1429979324980690
EXEC #2:c=0,e=315,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=4258903948,tim=1429979324981087
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='INDEX BUILD NON UNIQUE I_UNDO2 (cr=0 pr=0 pw=0 time=0 us)'
STAT #2 id=2 cnt=0 pid=1 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=0)'
STAT #2 id=3 cnt=0 pid=2 pos=1 obj=15 op='TABLE ACCESS FULL UNDO$ (cr=0 pr=0 pw=0 time=0 us)'
CLOSE #2:c=0,e=7,dep=1,type=0,tim=1429979324981283
=====================
PARSING IN CURSOR #2 len=208 dep=1 uid=0 oct=9 lid=0 tim=1429979324983049 hv=246520463 ad='349c51f8' sqlid='18806807b36ng'
CREATE UNIQUE INDEX I_OBJ1 ON OBJ$(OBJ#,OWNER#,TYPE#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 36 EXTENTS (FILE 1 BLOCK 336))
END OF STMT

=====================
..............
..............
上面顏色部分可以由sql_id對應執行的sql語句。
紅色部分建立了表bootstrap$,這個表在1號檔案的第520個塊上,看這是什麼表空間:
SQL> select * from dba_data_files where file_id=1;
FILE_NAME     FILE_ID TABLESPACE_NAME BYTES   BLOCKS STATUS    RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------
/u01/app/oracle/oradata/orcl3939/system01.dbf   1 SYSTEM     786432000    96000 AVAILABLE       1 YES 3.4360E+10    4194302   1280  785383424 95872 SYSTEM
分析它的表結構:
bootstrap$ ( line#         number not null,   obj#           number not null,   sql_text   varchar2(4000) not null) 
裡面放的有物件編號,以及sql語句,透過查詢bootstrap$:
SQL> select * 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" VARCHAR2(1000),"SPARE6" DATE) STORAGE (  OBJNO 20 TABNO 4) CLUSTER C_OBJ#(BO#)


由上述trace檔案可知,建立boostrap$之後,又建立:
CREATE ROLLBACK SEGMENT SYSTEM STORAGE (  INITIAL 112K NEXT 56K MINEXTENTS 1 MAXEXTENTS 32765 OBJNO 0 EXTENTS (FILE 1 BLOCK 128))
CREATE TABLE TS$("TS#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"OWNER#" NUMBER NOT NULL,"ONLINE$" NUMBER NOT NULL,"CONTENTS$" NUMBER NOT NULL,"UNDOFILE#" NUMBER,"UNDOBLOCK#" NUMBER,"BLOCKSIZE" NUMBER NOT NULL,"INC#" NUMBER NOT NULL,"SCNWRP" NUMBER,"SCNBAS" NUMBER,"DFLMINEXT" NUMBER NOT NULL,"DFLMAXEXT" NUMBER NOT NULL,"DFLINIT" NUMBER NOT NULL,"DFLINCR" NUMBER NOT NULL,"DFLMINLEN" NUMBER NOT NULL,"DFLEXTPCT" NUMBER NOT NULL,"DFLOGGING" NUMBER NOT NULL,"AFFSTRENGTH" NUMBER NOT NULL,"BITMAPPED" NUMBER NOT NULL,"PLUGGED" NUMBER NOT NULL,"DIRECTALLOWED" NUMBER NOT NULL,"FLAGS" NUMBER NOT NULL,"PITRSCNWRP" NUMBER,"PITRSCNBAS" NUMBER,"OWNERINSTANCE" VARCHAR2(30),"BACKUPOWNER" VARCHAR2(30),"GROUPNAME" VARCHAR2(30),"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" VARCHAR2(1000),"SPARE4" DATE) STORAGE (  OBJNO 16 TABNO 2) CLUSTER C_TS#(TS#)
CREATE TABLE FILE$("FILE#" NUMBER NOT NULL,"STATUS$" NUMBER NOT NULL,"BLOCKS" NUMBER NOT NULL,"TS#" NUMBER,"RELFILE#" NUMBER,"MAXEXTEND" NUMBER,"INC" NUMBER,"CRSCNWRP" NUMBER,"CRSCNBAS" NUMBER,"OWNERINSTANCE" VARCHAR2(30),"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" VARCHAR2(1000),"SPARE4" DATE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 17 EXTENTS (FILE 1 BLOCK 232))
CREATE TABLE OBJ$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"OWNER#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"NAMESPACE" NUMBER NOT NULL,"SUBNAME" VARCHAR2(30),"TYPE#" NUMBER NOT NULL,"CTIME" DATE NOT NULL,"MTIME" DATE NOT NULL,"STIME" DATE NOT NULL,"STATUS" NUMBER NOT NULL,"REMOTEOWNER" VARCHAR2(30),"LINKNAME" VARCHAR2(128),"FLAGS" NUMBER,"OID$" RAW(16),"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (  INITIAL 16K NEXT 104K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 18 EXTENTS (FILE 1 BLOCK 240))
.........
.........
那資料檔案1中的第520個塊之前的塊是哪些呢?
透過下面sql語句,可以看到頭塊為520之前的塊的物件:
SQL> 
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;SQL>   2  
/


 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
已選擇47行。
由trace檔案知道(也可以直接查bootstrap$),520之前塊正是與資料庫啟動相關的塊!
結合trace檔案和bootstrap$可知,先載入bootstrap$後,由sql_text然後遞迴建立oracle啟動所需的物件。
那1號檔案第520個塊了到底放些什麼呢?
學會使用trace對於研究資料庫很重要:
SQL> alter system dump datafile 1 block 520;
系統已更改。
以下摘自部分trace檔案:
*** 2015-04-26 01:05:26.707
CLOSE #23:c=0,e=110,dep=0,type=0,tim=1429981526707493
=====================
PARSE ERROR #12:len=35 dep=0 uid=0 oct=49 lid=0 tim=1429981526715635 err=25117
alter system dump datafile 1 block
=====================
declare
  m_stmt  varchar2(512);
begin
   m_stmt:='delete from sdo_geor_ddl__table$$';
   EXECUTE IMMEDIATE m_stmt;
   EXCEPTION
     WHEN OTHERS THEN
       NULL;
end;
=====================
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
....
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)

....
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
....
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ where obj#=:1 order by intcol#
....
select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 from cdef$ where robj#=:1
....
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0),spare2,spare3 from cdef$ where obj#=:1
....
select decode(u.type#, 2, u.ext_username, u.name), o.name,        t.update$, t.insert$, t.delete$, t.enabled,        decode(bitand(t.property, 8192),8192, 1, 0),        decode(bitand(t.property, 65536), 65536, 1, 0),       decode(bitand(t.property, 131072), 131072, 1, 0),       (select o.name from obj$ o          where o.obj# = u.spare2 and o.type# =57)  from sys.obj$ o, sys.user$ u, sys.trigger$ t, sys.obj$ bo where t.baseobject=bo.obj# and bo.name = :1 and bo.spare3 = :2  and bo.namespace = 1  and t.obj#=o.obj# and o.owner#=u.user#  and o.type# = 12 and bitand(property,16)=0 and bitand(property,8)=0  order by o.obj#
....
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ where obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#, grantee#
....
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee#
m sdo_geor_ddl__table$$
*** 2015-04-26 01:05:40.179
CLOSE #12:c=0,e=20,dep=0,type=0,tim=1429981540179940
=====================
PARSING IN CURSOR #36 len=38 dep=0 uid=0 oct=49 lid=0 tim=1429981540180608 hv=4117028914 ad='0' sqlid='07605w7uq9s1k'
alter system dump datafile 1 block 520
END OF STMT
PARSE #36:c=1000,e=484,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=1429981540180606
Start dump data blocks tsn: 0 file#:1 minblk 520 maxblk 520
Block dump from cache:
Dump of buffer cache at level 4 for tsn=0, rdba=4194824
BH (0x28fe7134) file#: 1 rdba: 0x00400208 (1/520) class: 4 ba: 0x28cc6000
  set: 5 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,28
  dbwrid: 0 obj: 59 objn: 59 tsn: 0 afn: 1 hint: f
  hash: [0x33e8e55c,0x33e8e55c] lru: [0x297ec8d4,0x28fe72b4]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [0x31ff2434,0x28fe72cc]
  st: XCURRENT md: NULL tch: 1
  flags:
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
  cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
*** 2015-04-26 01:05:40.454
buffer tsn: 0 rdba: 0x00400208 (1/520)
scn: 0x0000.00000251 seq: 0x01 flg: 0x04 tail: 0x02511001
frmt: 0x02 chkval: 0xe443 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00772000 to 0x00774000
......
......
  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 
End dump data blocks tsn: 0 file#: 1 minblk 520 maxblk 520
下面是部分sql語句:
select timestamp, flags from fixed_obj$ where obj#=:1
SELECT inst_id, name, value FROM x$diag_info
SELECT inst_id, name, value FROM gv$diag_info          WHERE  inst_id = USERENV('INSTANCE')
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
select * from v$diag_info where NAME='Default Trace File'
select timestamp, flags from fixed_obj$ where obj#=:1
select timestamp, flags from fixed_obj$ where obj#=:1
select timestamp, flags from fixed_obj$ where obj#=:1
select inst_id,ksusenum,ksusestn,ksusestv from x$ksumysta where bitand(ksspaflg,1)!=0 and bitand(ksuseflg,1)!=0 and ksusestn select  SID , STATISTIC# , VALUE from GV$MYSTAT where inst_id = USERENV('Instance')
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
select * from v$mystat
select distinct sid from v$mystat
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
select paddr from v$session where sid=9
select timestamp, flags from fixed_obj$ where obj#=:1
select addr, pid, spid, pname, username, serial#, terminal, program, traceid, tracefile, background, latchwait,latchspin,pga_used_mem,pga_alloc_mem,pga_freeable_mem,pga_max_mem from gv$process where inst_id = USERENV('Instance') 
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
select spid from v$process where addr='34FC8DAC'
......
......

分析上述標記的紅色字型段:
BH (0x28fe7134) file#: 1 rdba: 0x00400208 (1/520) 
BH(0x28fe7134):記錄的是該塊在buffer cache中實際的記憶體地址(buffer header)
rdba:root dba
(1/520):猜想是指bootstrapt$,下面來驗證
後面的OX00400208是十六進位制數:
先轉換成二進位制數:
 0    0   4    0    0    2    0      8
0000  0000  0100  0000  0000  0010  0000    1000
由前面的文章知,前十位表示檔案編號:
0000000001:1
後22位表示塊號:  
0000000000001000001000:16*16*2 + 8=520
上述也可以用oracle提供的包直接來計算。
rdba指向的bootstrap$!
原來資料庫的引導過程中,rdba用來定位資料庫引導的bootstrap$資訊。
上面說了這麼多,那bootstrap$又是如何建立的呢?
透過蓋國強老師的《深入解析Oralce》得知,原來在建立資料庫的指令碼里,oracle會隱含的呼叫/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/sql.bsq 用於建立資料字典,如果oracle找不到sql.bsq指令碼,資料庫建立會出錯。我們可以透過修改sql.bsq指令碼來更改資料字典物件引數,從而實現特殊要求資料庫的建立或測試的自定義庫。
下面摘自部分sql.bsq文字:
rem 
rem $Header: rdbms/admin/sql.bsq /main/606 2008/07/14 17:25:59 vliang Exp $ sql.bsq 
rem  MODIFIED
rem   huagli     06/09/08 - add ddst.bsq
rem   dvoss      01/03/07 - add dlmnr.bsq
rem   rdecker    10/20/06 - create SYSAUX before running dplsql.bsq
rem   jklein     08/01/05 - diag 11g - split-up into units 
rem   sdavidso   08/01/05 - add tranform_param type check info 
rem   mmpandey   06/07/05 - 4390808: increase the cache value in audses$
rem   mvemulap   05/02/05 - bug fix for 4318925 
rem   mhho       04/20/05 - change colklc column size in enc$ 
rem   tfyu       02/28/05 - Bug 4262763
rem   htran      03/11/05 - remove transportable from fgr$_tablespace_info
rem   mmcracke   03/14/05 - add ddm.bsq for data mining models
rem   alakshmi   02/28/05 - error recovery for maintain_ apis
rem   ddas       01/07/05 - #(4052436) add hint_string to ol$hints
rem   sourghos   01/06/05 - Fix bug 4043119
rem   ilyubash   11/05/04 - Add gen column to i_aw_prop$ index 
rem   elu        01/03/05 - streams apply spilling 
rem   htran      11/15/04 - comments for spare1 in user$ and streams$_prepare_*
rem   apadmana   10/05/04 - bug3607838: manage any queue
rem   clei       04/15/04 - add view merge permission
rem   weiwang    10/14/04 - set queue flag in base view
rem   mtakahar   09/03/04 - create mon_mods_all$
rem   clei       09/01/04 - add comments to encryption property flags
rem   xuhuali    03/31/04 - audit java 
rem   kdias      07/15/04 - revisit privs granted to OUTLN user 
rem   nmanappa   07/20/04 - bug 3690876 - clean privileges 194-199,239,240
rem   dmwong     07/21/04 - fix connect role to only contain create session 
rem   skaluska   07/09/04 - split up tsm_hist$ into tsm_src$, tsm_dest$ 
rem   araghava   07/07/04 - (3748430): make partitioning indexes unique. 
rem   clei       06/29/04 - add enc$
rem  ssvemuri    06/25/04 - change notification privilege
rem   ramkrish   06/16/04 - correct model nmspc and type 
rem   nshodhan   06/11/04 - use streams$_capture_process.spare3 

.....
.....
rem   varora     04/28/95 -  rename col#,usercol#,cols,usercols 
rem   tcheng     03/21/95 -  add col# to adtcol$ and ntab$ 
rem   varora     01/27/95 -  add table for nested table support 
rem   skotsovo   01/25/95 -  bring normalized type tables up to date 
rem   skotsovo   01/23/95 -  move exceptions from method to method_body 
rem   jwijaya    01/04/95 -  add system privileges for type
rem   jwijaya    12/29/94 -  making type$ work (temporarily allow 'version'
rem                          'checks' columns nullable and mark 'checks'
rem                          and 'default$' not-supported (N/S))
rem   skrishna   12/06/94 -  create extent table of pre-defined types 
rem   varora     12/01/94 -  change toid in adtcol$ to type number 
rem   anori      11/17/94 -  ADT support tables and columns
rem
rem !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! IMPORTANT !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
rem Whenever new column is created to store internal, user or kernel column
rem number, be sure to update the structure adtDT in atb.c so that those 
rem columns will be updated properly during drop column.
rem !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! 
rem
dcore.bsq
dsqlddl.bsq
dmanage.bsq
dplsql.bsq
dtxnspc.bsq
dfmap.bsq
denv.bsq
drac.bsq
dsec.bsq
doptim.bsq
dobj.bsq
djava.bsq
dpart.bsq
drep.bsq
daw.bsq
dsummgt.bsq
dtools.bsq
dexttab.bsq
ddm.bsq
dlmnr.bsq
ddst.bsq
SYSTEM表空間的重要性可想而知,如果system表空間損壞,則資料庫無法開啟。SYSTEM表空間的備份重於一切。
oracle啟動初始化過程十分複雜,上面的很多東西都值得我們去研究,鑑於本人水平有限,希望讀者在此基礎上對oracle啟動初始化過程有更深次的理解。


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29876893/viewspace-1603494/,如需轉載,請註明出處,否則將追究法律責任。

相關文章