当前位置: 代码迷 >> Oracle开发 >> 初探oracle open初始化历程
  详细解决方案

初探oracle open初始化历程

热度:126   发布时间:2016-04-24 06:25:34.0
初探oracle open初始化过程

[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 ksusgstl from x$ksusgif)
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启动初始化过程有更深次的理解。

 

  相关解决方案