Oracle bootstrap$ 详解

 

一. 官网说明

 Oracle官方文档对Bootstrap$的说明:

UnderstandingBootstrap Of Oracle Database

http://blog.csdn.net/tianlesoftware/article/details/6823453

 

In Oracle,Bootstrap refers to loading of metadata (data dictionary) before we OPEN thedatabase.

Bootstrap objects are classified as the objects (tables / indexes / clusters) with the object_id below 56 as bootstrap objects.

 

Object_id 在56以下的都作为bootstrap objects,这些对象都是Oracle 的核心对象,这里为什么是56,和Oracle 版本有关系,如果核心对象多,这里的数字就会更大。

在之前的一篇Blog中有说明,10g中X$KCVFH中字段FHRDB对应的就是bootstrap$. 而FHRDB 对应的object id 就是56. 在Oracle11g中,这个bootstrap$对应的就是59.

Oracle 10g 中 X$KCVFH 说明

http://blog.csdn.net/tianlesoftware/article/details/6919280

 

These objects are mandatory to bring up an instance, as this contains the most important metadata of the database.

 

1.1 What happens on database startup?

This shall beexplained by setting theSQL_TRACE while opening the database.

 

用sysdba 执行如下步骤可以查看在数据库OPEN时都有哪些操作:

(1)STARTUP MOUNT;
(2)ALTER SESSION SET EVENTS ’10046TRACE NAME CONTEXT FOREVER, LEVEL 12′;
(3)ALTER DATABASE OPEN;
(4)ALTER SESSION SET EVENTS ’10046TRACE NAME CONTEXT OFF’;
(5)SHOW PARAMETER USER_DUMP_DEST
(6)ORADEBUG SETMYPID
(7)ORADEBUG TRACEFILE_NAME

 

示例:

[oracle@dave ~]$ sqlplus / as sysdba;

 

SQL*Plus: Release 11.2.0.3.0 Production onMon Oct 31 10:13:01 2011

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area 1043886080 bytes

Fixed Size                  2234960 bytes

Variable Size             838862256 bytes

Database Buffers          197132288 bytes

Redo Buffers                5656576 bytes

Database mounted.

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

 

SQL> alter database open;

Database altered.

 

SQL> alter session set events '10046trace name context off';

Session altered.

 

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name

/u01/app/oracle/diag/rdbms/anqing/anqing/trace/anqing_ora_4864.trc

 

Oracle oradebug 命令 使用说明

http://blog.csdn.net/tianlesoftware/article/details/6525628

 

查看trace 文件,这个trace 的内容很多,这里截取部分内容:

*** 2011-10-31 10:15:26.314

WAIT #47868535613696: nam='db filesequential read' ela= 34204 file#=1 block#=520 blocks=1 obj#=-1tim=1320027326313943

=====================

PARSING IN CURSOR #47868535605544 len=188 dep=1uid=0 oct=1 lid=0 tim=1320027326467068 hv=4006182593 ad='9e067188'sqlid='32r4f1brck

zq1'

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#47868535605544:c=28002,e=149060,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1320027326467068

EXEC#47868535605544:c=0,e=18705,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=1320027326486816

CLOSE #47868535605544:c=0,e=0,dep=1,type=0,tim=1320027326487664

=====================

PARSING IN CURSOR #47868535605544 len=55dep=1 uid=0 oct=3 lid=0 tim=1320027326527387 hv=2111436465 ad='9e055980'sqlid='6apq2rjyxmx

pj'

select line#, sql_textfrom bootstrap$ where obj# != :1

END OF STMT

PARSE#47868535605544:c=28002,e=39628,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1320027326527353

BINDS #47868535605544:

 Bind#0

 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0

 kxsbbbfp=2b89430f84e0  bln=22  avl=02 flg=05

  value=59

EXEC#47868535605544:c=12000,e=101181,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=867914364,tim=1320027326630459

……

STAT #47868535605544 id=1cnt=59 pid=0 pos=1 obj=59 op='TABLE ACCESS FULL BOOTSTRAP$ (cr=61 pr=4 pw=0time=49592 us)'

WAIT #47868535605544: nam='latch: sharedpool' ela= 2917 address=1611695672 number=307 tries=0 obj#=59tim=1320027326745870

CLOSE#47868535605544:c=0,e=84,dep=1,type=0,tim=1320027326747472

…..

CREATE ROLLBACK SEGMENT SYSTEM STORAGE (  INITIAL 112K NEXT 56K MINEXTENTS 1 MAXEXTENTS32765 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

……

CREATE TABLE TAB$("OBJ#" NUMBERNOT NULL,"DATAOBJ#" NUMBER,"TS#" NUMBER NOTNULL,"FILE#" NUMBER NOT

……

CREATE TABLE FILE$("FILE#" NUMBERNOT NULL,"STATUS$" NUMBER NOT NULL,"BLOCKS" NUMBER NOTNULL,"TS#" NUMBER,"RELFILE#" NUMBER,"MAXEXT

……

 

通过以上的说明,可以看出可以在数据库Open 阶段会创建一些数据字典。

Oracle 数据字典 说明

http://blog.csdn.net/tianlesoftware/article/details/5862508

 

Oracle 动态性能视图

http://blog.csdn.net/tianlesoftware/article/details/5863191

 

 

这里重点看一下:

create table bootstrap$ (line# number not null, obj# number not null, sql_textvarchar2(4000) not null)storage (initial 50K objno 56 extents (file 1 block377))

 

This sys.bootstrap$ table contains the DDL’s for other bootstrap tables (object_idbelow 56).

Actually these tables were createdinternally by the time of database creation (by sql.bsq),The create DDL passedbetween MOUNT and OPEN stage will be executed throughdifferent driverroutines. In simple words these are not standard CREATE DDLs.

While starting up the database oracle will load these objects into memory(shared_pool),(ie) itwill assign the relevant object number and refer to the datafile and the blockassociated with that.

And suchoperations happen only while warm startup.

@ The internals of the above explained in‘kqlb.c’.

 

 

我们从bootstrap$中查看一下object_id 小于56的对象内容:

Now a queryexecutedagainst the sys.bootstrap$ table, which holds the create sql’s forother basetables.

SQL>select line#, sql_text from bootstrap$ ;

 

Subsequently it will create those objectsby running those queries.

Object number 0 – (System Rollback Segment)
Object number 2 to 55 (Other base tables)
Object number 1 is NOT used by any of the objects.

 

以下是Oracle 11g的查询结果:

SQL> select obj#, substr(sql_text,1,50)from bootstrap$ order by 1 desc;

 

     OBJ# SUBSTR(SQL_TEXT,1,50)

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

       59CREATE TABLE BOOTSTRAP$("LINE#" NUMBER NOT NULL,"O

--注意这里的BOOTSTRAP$ 对象变成了59

       58 CREATE UNIQUE INDEX I_CCOL2 ON CCOL$(CON#,INTCOL#)

       57 CREATE INDEX I_CCOL1 ON CCOL$(CON#,COL#) PCTFREE 1

       56 CREATE INDEX I_CDEF4 ON CDEF$(ENABLED) PCTFREE 10

       55 CREATE INDEX I_CDEF3 ON CDEF$(ROBJ#) PCTFREE 10 IN

       54 CREATE INDEX I_CDEF2 ON CDEF$(OBJ#) PCTFREE 10 INI

       53 CREATE UNIQUE INDEX I_CDEF1 ON CDEF$(CON#) PCTFREE

       52 CREATE UNIQUE INDEX I_CON2 ON CON$(CON#) PCTFREE 1

       51 CREATE UNIQUE INDEX I_CON1 ON CON$(OWNER#,NAME) PC

       50 CREATE UNIQUE INDEX I_COL3 ON COL$(OBJ#,INTCOL#) P

       49 CREATE INDEX I_COL2 ON COL$(OBJ#,COL#) PCTFREE 10

       48 CREATE UNIQUE INDEX I_COL1 ON COL$(OBJ#,NAME) PCTF

       47 CREATE UNIQUE INDEX I_USER2 ON USER$(USER#,TYPE#,S

       46 CREATE UNIQUE INDEX I_USER1 ON USER$(NAME) PCTFREE

       45 CREATE UNIQUE INDEX I_TS1 ON TS$(NAME) PCTFREE 10

       44 CREATE UNIQUE INDEX I_FILE2 ON FILE$(TS#,RELFILE#)

       43 CREATE UNIQUE INDEX I_FILE1 ON FILE$(FILE#) PCTFRE

       42 CREATE INDEX I_ICOL1 ON ICOL$(OBJ#) PCTFREE 10 INI

       41 CREATE UNIQUE INDEX I_IND1 ON IND$(OBJ#) PCTFREE 1

       40 CREATE UNIQUE INDEX I_OBJ5 ON OBJ$(SPARE3,NAME,NAM

       39 CREATE INDEX I_OBJ4 ON OBJ$(DATAOBJ#,TYPE#,OWNER#)

       38 CREATE INDEX I_OBJ3 ON OBJ$(OID$) PCTFREE 10 INITR

       37 CREATE UNIQUE INDEX I_OBJ2 ON OBJ$(OWNER#,NAME,NAM

       36 CREATE UNIQUE INDEX I_OBJ1 ON OBJ$(OBJ#,OWNER#,TYP

       35 CREATE INDEX I_UNDO2 ON UNDO$(NAME) PCTFREE 10 INI

       34 CREATE UNIQUE INDEX I_UNDO1 ON UNDO$(US#) PCTFREE

       33 CREATE INDEX I_TAB1 ON TAB$(BOBJ#) PCTFREE 10 INIT

       32 CREATE TABLE CCOL$("CON#" NUMBER NOT NULL,"OBJ#"N

       31 CREATE TABLE CDEF$("CON#" NUMBER NOT NULL,"OBJ#"N

       30 CREATE INDEX I_COBJ# ON CLUSTER C_COBJ# PCTFREE 10

       29 CREATE CLUSTERC_COBJ#("OBJ#" NUMBER) PCTFREE 0 PC

       28 CREATE TABLE CON$("OWNER#" NUMBER NOT NULL,"NAME"

       27 CREATE UNIQUE INDEX I_PROXY_ROLE_DATA$_2 ON PROXY_

       26 CREATE INDEX I_PROXY_ROLE_DATA$_1 ON

       25 CREATE TABLE PROXY_ROLE_DATA$("CLIENT#" NUMBER

       24 CREATE UNIQUE INDEX I_PROXY_DATA$ ON PROXY_DATA$(C

       23 CREATE TABLE PROXY_DATA$("CLIENT#" NUMBER NOT NULL

       22 CREATE TABLE USER$("USER#" NUMBER NOT NULL,"NAME"

       21 CREATE TABLE COL$("OBJ#" NUMBER NOT NULL,"COL#"NU

       20 CREATE TABLE ICOL$("OBJ#" NUMBER NOT NULL,"BO#"NU

       19 CREATE TABLE IND$("OBJ#" NUMBER NOT NULL,"DATAOBJ#

       18 CREATE TABLE OBJ$("OBJ#" NUMBER NOT NULL,"DATAOBJ#

       17 CREATE TABLE FILE$("FILE#" NUMBER NOT NULL,"STATUS

       16 CREATE TABLE TS$("TS#" NUMBER NOT NULL,"NAME"VARC

       15 CREATE TABLE UNDO$("US#" NUMBER NOT NULL,"NAME"VA

       14 CREATE TABLE SEG$("FILE#" NUMBER NOTNULL,"BLOCK#"

       13 CREATE TABLE UET$("SEGFILE#" NUMBER NOT NULL,"SEGB

       12 CREATE TABLE FET$("TS#" NUMBER NOT NULL,"FILE#"NU

       11 CREATE INDEX I_USER# ON CLUSTER C_USER# PCTFREE 10

       10 CREATE CLUSTER C_USER#("USER#" NUMBER) PCTFREE 10

        9 CREATE INDEX I_FILE#_BLOCK# ON CLUSTER C_FILE#_BLO

         8 CREATE CLUSTERC_FILE#_BLOCK#("TS#" NUMBER,"SEGFIL

        7 CREATE INDEX I_TS# ON CLUSTER C_TS# PCTFREE 10 INI

        6 CREATE CLUSTER C_TS#("TS#" NUMBER) PCTFREE 10 PCTU

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

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

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

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

        0 CREATE ROLLBACK SEGMENT SYSTEM STORAGE (  INITIAL

       -1 8.0.0.0.0

 

60 rows selected.

 

SQL>

 

查看一下Oracle 10g的bootstrap$:

SYS@anqing1(rac1)> select obj#,substr(sql_text,1,50) from bootstrap$ order by 1 desc;

 

     OBJ# SUBSTR(SQL_TEXT,1,50)

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

        56 CREATE TABLEBOOTSTRAP$("LINE#" NUMBER NOT NULL,"O

---- 注意这里,在Oracle 10g 下面,这里还是object_id还是56.

       55 CREATE UNIQUE INDEX I_CCOL2 ON CCOL$(CON#,INTCOL#)

       54 CREATE INDEX I_CCOL1 ON CCOL$(CON#,COL#) PCTFREE 1

       53 CREATE INDEX I_CDEF4 ON CDEF$(ENABLED) PCTFREE 10

       52 CREATE INDEX I_CDEF3 ON CDEF$(ROBJ#) PCTFREE 10 IN

       51 CREATE INDEX I_CDEF2 ON CDEF$(OBJ#) PCTFREE 10 INI

       50 CREATE UNIQUE INDEX I_CDEF1 ON CDEF$(CON#) PCTFREE

       49 CREATE UNIQUE INDEX I_CON2 ON CON$(CON#) PCTFREE 1

       48 CREATE UNIQUE INDEX I_CON1 ON CON$(OWNER#,NAME) PC

       47 CREATE UNIQUE INDEX I_COL3 ON COL$(OBJ#,INTCOL#) P

       46 CREATE INDEX I_COL2 ON COL$(OBJ#,COL#) PCTFREE 10

       45 CREATE UNIQUE INDEX I_COL1 ON COL$(OBJ#,NAME) PCTF

       44 CREATE UNIQUE INDEX I_USER1 ON USER$(NAME) PCTFREE

       43 CREATE UNIQUE INDEX I_TS1 ON TS$(NAME) PCTFREE 10

       42 CREATE UNIQUE INDEX I_FILE2 ON FILE$(TS#,RELFILE#)

       41 CREATE UNIQUE INDEX I_FILE1 ON FILE$(FILE#) PCTFRE

       40 CREATE INDEX I_ICOL1 ON ICOL$(OBJ#) PCTFREE 10 INI

       39 CREATE UNIQUE INDEX I_IND1 ON IND$(OBJ#) PCTFREE 1

       38 CREATE INDEX I_OBJ3 ON OBJ$(OID$) PCTFREE 10 INITR

       37 CREATE UNIQUE INDEX I_OBJ2 ON OBJ$(OWNER#,NAME,NAM

       36 CREATE UNIQUE INDEX I_OBJ1 ON OBJ$(OBJ#) PCTFREE 1

       35 CREATE INDEX I_UNDO2 ON UNDO$(NAME) PCTFREE 10 INI

       34 CREATE UNIQUE INDEX I_UNDO1 ON UNDO$(US#) PCTFREE

       33 CREATE INDEX I_TAB1 ON TAB$(BOBJ#) PCTFREE 10 INIT

       32 CREATE TABLECCOL$("CON#" NUMBER NOT NULL,"OBJ#" N

       31 CREATE TABLE CDEF$("CON#" NUMBER NOT NULL,"OBJ#"N

       30 CREATE INDEX I_COBJ# ON CLUSTER C_COBJ# PCTFREE 10

       29 CREATE CLUSTER C_COBJ#("OBJ#" NUMBER) PCTFREE 0 PC

       28 CREATE TABLE CON$("OWNER#" NUMBER NOT NULL,"NAME"

       27 CREATE UNIQUE INDEX I_PROXY_ROLE_DATA$_2 ON PROXY_

       26 CREATE INDEX I_PROXY_ROLE_DATA$_1 ON

       25 CREATE TABLE PROXY_ROLE_DATA$("CLIENT#" NUMBER

       24 CREATE UNIQUE INDEX I_PROXY_DATA$ ON PROXY_DATA$(C

       23 CREATE TABLE PROXY_DATA$("CLIENT#" NUMBER NOT NULL

       22 CREATE TABLE USER$("USER#" NUMBER NOT NULL,"NAME"

       21 CREATE TABLE COL$("OBJ#" NUMBER NOT NULL,"COL#"NU

       20 CREATE TABLE ICOL$("OBJ#" NUMBER NOT NULL,"BO#"NU

       19 CREATE TABLE IND$("OBJ#" NUMBER NOT NULL,"DATAOBJ#

       18 CREATE TABLE OBJ$("OBJ#" NUMBER NOT NULL,"DATAOBJ#

       17 CREATE TABLE FILE$("FILE#" NUMBER NOT NULL,"STATUS

       16 CREATE TABLE TS$("TS#" NUMBER NOT NULL,"NAME" VARC

       15 CREATE TABLE UNDO$("US#" NUMBER NOT NULL,"NAME"VA

       14 CREATE TABLE SEG$("FILE#" NUMBER NOTNULL,"BLOCK#"

       13 CREATE TABLE UET$("SEGFILE#" NUMBER NOT NULL,"SEGB

       12 CREATE TABLE FET$("TS#" NUMBER NOT NULL,"FILE#"NU

        11 CREATE INDEX I_USER# ON CLUSTER C_USER#PCTFREE 10

       10 CREATE CLUSTER C_USER#("USER#" NUMBER) PCTFREE 10

        9 CREATE INDEX I_FILE#_BLOCK# ON CLUSTER C_FILE#_BLO

        8 CREATE CLUSTER C_FILE#_BLOCK#("TS#" NUMBER,"SEGFIL

        7 CREATE INDEX I_TS# ON CLUSTER C_TS# PCTFREE 10 INI

        6 CREATE CLUSTER C_TS#("TS#" NUMBER) PCTFREE 10 PCTU

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

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

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

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

        0 CREATE ROLLBACK SEGMENT SYSTEM STORAGE (  INITIAL

       -1 8.0.0.0.0

 

57 rows selected.

 

 

二. Bootstrap$说明

 

根据我们第一小结的说明,这里看一下数据库OPEN的流程:

       在instance open 时,会读取SYSTEM 表空间的datafile header,在这个Header中保存了X$KCVFH 的信息,这部分内容之前的blog 有说明:

Oracle 10g 中 X$KCVFH 说明

http://blog.csdn.net/tianlesoftware/article/details/6919280

 

       而X$KCVFH中的FHRDB字段指向了Root dba (bootstrap$), DSI 403 上对这个root dba的说明:

Root dba: Thisfield only occurs in data file #1, and is the location of blocks required duringbootstrapping the data dictionary (bootstrap$)。

 

在DB 从mount 到open的操作中,会先创建sys.bootstrap$ 表,该表包含了object_id 小于56(11g 小于59)的对象的DDL 语句,如OBJ$,C_OBJ$,TAB$等基表,然后全表扫描bootstrap$表,获取这些DDL语句,最后在shared pool中创建这些对象。

 

 

现在我们来看一个问题,这些object_id 小于56的基表的定义到底存在什么地方,因为实际上,这些object_id 的DDL 是在DB 创建时的sql.bsq 脚本创建的,然后将这些DDL 的定义存放在一个位置,在open时,会从这个位置读取DDL 的定义然后插入到bootstrap$表。

Dbsnake 的blog 给了这个问题的答案:DDL 语句记录到了ktetb[0]. Ktetbdba中。

当我们升级instance时,需要执行catupgrd.sql, 重新将这些DDL 语句刷到 ktetb[0]. Ktetbdba中。否则会报:ORA-00704 错误。

 

关于10g中的bootstrap$

http://dbsnake.com/2009/06/10g-bootstrap.html

 

Oracle rdba和 dba 说明

http://blog.csdn.net/tianlesoftware/article/details/6529346

 

Oracle BBED 工具说明

http://blog.csdn.net/tianlesoftware/article/details/5006580

 

Oracle bbed 五个实用示例

http://blog.csdn.net/tianlesoftware/article/details/6684505

 

证明过程如下:

SQL> select * from bootstrap$ where obj#=0;

 

    LINE#       OBJ#

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

SQL_TEXT

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

        0          0

CREATE ROLLBACK SEGMENT SYSTEM STORAGE(  INITIAL 112K NEXT 56K MINEXTENTS 1 MAXEXTENTS 32765 OBJNO 0 EXTENTS (FILE 1 BLOCK 128))

 

这里我们看到了objectid 为0的对象的DDL,是rollback segment。

 

将如下内容放到ultraEdit中,然后查看其对应的十六进制:

CREATE ROLLBACK SEGMENT SYSTEM STORAGE(  INITIAL 112K NEXT 56K MINEXTENTS 1 MAXEXTENTS 32765 OBJNO 0 EXTENTS (FILE 1 BLOCK 128))

 


然后用BBED 工具dump  对应的block,来验证一下:


dave:/home/oracle> bbed parfile=/u01/bbed.par

Password:

 

BBED: Release 2.0.0.0.0 - LimitedProduction on Mon Oct 31 15:50:21 2011

 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

 

************* !!! For Oracle Internal Useonly !!! ***************

BBED> set dba 1,377

       DBA             0x00400179(4194681 1,377)

BBED> show

       FILE#           1

       BLOCK#          377

       OFFSET          0

       DBA             0x00400179(4194681 1,377)

       FILENAME       /u01/app/oracle/oradata/anqing/system01.dbf

       BIFILE          bifile.bbd

       LISTFILE        /u01/filelist.txt

        BLOCKSIZE       8192

       MODE            Edit

       EDIT            Unrecoverable

       IBASE           Dec

       OBASE           Dec

       WIDTH           80

       COUNT           512

       LOGFILE         log.bbd

       SPOOL           No

 

BBED> p ktetb

struct ktetb[0], 8 bytes                    @108    

   ub4 ktetbdba                             @108      0x0040017a

  ub4 ktetbnbk                            @112      0x00000007

 

BBED>

 

SQL> SELECT DBMS_UTILITY.data_block_address_file (            

 2           TO_NUMBER(LTRIM('0x0040017a', '0x'),'xxxxxxxx'))

 3            AS file_no,

 4       DBMS_UTILITY.data_block_address_block (

 5           TO_NUMBER(LTRIM('0x0040017a', '0x'), 'xxxxxxxx'))

 6            AS block_no

 7    FROM DUAL;

 

  FILE_NO   BLOCK_NO

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

        1        378

 

 

 BBED> set dba 1,378

       DBA             0x0040017a(4194682 1,378)

 

BBED> set offset 8030

       OFFSET          8030

 

BBED> dump

 File: /u01/app/oracle/oradata/anqing/system01.dbf(1)

 Block: 378              Offsets: 8030 to 8191           Dba:0x0040017a

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

 2c010301 80018081 4352454154452052 4f4c4c42 41434b20 5345474d 454e5420

 53595354 454d2053 544f5241 47452028 2020494e49544941 4c203131 324b204e

 45585420 31303234 4b204d49 4e455854 454e54532031204d 41584558 54454e54

 53203332 37363520 4f424a4e 4f203020 455854454e545320 2846494c 45203120

 424c4f43 4b203929 292c0103033e6466 033e6466 09382e30 2e302e30 2e300106

 6101

 

 <32 bytes per line>

 

以上的结果和我们用UltraEdit 看到的一致,由此可以确认bootstrap$里保存的DDL 对象的定语是保存在ktetb[0]. Ktetbdba中。

 

 

三.对bootstrap$ 表进行DML 测试

       Bootstrap$里保存的都是系统的核心对象,我们可以直接对这个表进行修改,但因为启动instance 会执行这里面的内容,所以如果我们的修改的信息不正确,那么导致的结果就是下次启动时报ORA-600的错误。

 

       在测试之前,我们先创建一个restore points,最后我们在回滚回来。 有关restore points的说明,参考之前的blog:

Oracle FlashbackDatabase and Restore Points 说明

http://blog.csdn.net/tianlesoftware/article/details/6917546

 

3.1 创建restore points

 

SQL> startup mount;

ORACLE instance started.


Total System Global Area  377487360 bytes

Fixed Size                  1273804 bytes

Variable Size             155189300 bytes

Database Buffers          218103808 bytes

Redo Buffers                2920448 bytes

Database mounted.

 

SQL> create restore point bootstrap_before guarantee flashback database;

Restore point created.

 

SQL> alter database open;

Database altered.

 

3.2 对bootstrap$进行DML操作

 

先看一下bootstrap$的结构:

SQL> desc bootstrap$

 Name                                     Null?    Type

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

 LINE#                                     NOT NULLNUMBER

 OBJ#                                      NOT NULLNUMBER

 SQL_TEXT                                  NOT NULLVARCHAR2(4000)

 

SQL> select * from bootstrap$ where rownum=1;

 

    LINE#       OBJ# SQL_TEXT

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

       -1         -1 8.0.0.0.0

 

Bootstrap$ 有三个字段,启动Line# 和 obj# 值相同,其中的obj# 是对象的ID,为了不破环现有的环境,我们先看一下系统中obj#的最大值:

SQL> select max(obj#) from obj$;

 MAX(OBJ#)

----------

    58903

 

SQL> Create table dave1 (id number);

Table created.

 

SQL> select max(obj#) from obj$;

 MAX(OBJ#)

----------

    58913

--注意这里的对象ID,虽然我们只创建了一个,但是在这个期间可能还有其他的对象被创建,我们在创建一个测试一次:

 

SQL> Create table dave2(id number);

Table created.

 

SQL> select max(obj#) from obj$;

 MAX(OBJ#)

----------

    58914

--这次obj# 只加一个了。

 

现在我们insert 一条记录到bootstrap$中:

 

SQL> insert into bootstrap$ values(100000,100000,'Dave test');

1 row created.

 

SQL> commit;

Commit complete.

 

SQL> select * from bootstrap$ where obj#=100000;

    LINE#       OBJ# SQL_TEXT

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

   100000     100000 Dave test

 

SQL> update bootstrap$ set sql_text='create table anqing(id number)' where obj#=100000; 

1 row updated.

 

SQL> commit;

Commit complete.

 

SQL> select * from bootstrap$ where obj#=100000;

    LINE#       OBJ# SQL_TEXT

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

   100000     100000 create tableanqing(id number)

 

SQL> select max(obj#) from obj$;

 

 MAX(OBJ#)

----------

    58914

 

这里要注意,虽然我们insert 了一条记录,但是真正的对象并没有创建。

 

SQL> insert into bootstrap$ values(100000,100000,'create table anqing2(id number)');

1 row created.

 

SQL> commit;

Commit complete.

 

SQL> select * from bootstrap$ whereobj#=100000;

 

    LINE#       OBJ# SQL_TEXT

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

   100000     100000 create tableanqing(id number)

   100000     100000 create tableanqing2(id number)

 

我们删除一条记录之后,重启我们实例,看看有什么效果:

SQL> delete from bootstrap$ where sql_text='create table anqing2(id number)';

1 row deleted.

 

SQL> commit;

Commit complete.

 

SQL> select * from bootstrap$ where obj#=100000;

 

    LINE#       OBJ# SQL_TEXT

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

100000     100000 create table anqing(id number)

 

3.3重启实例

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  377487360 bytes

Fixed Size                  1273804 bytes

Variable Size             155189300 bytes

Database Buffers          218103808 bytes

Redo Buffers                2920448 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnectionforced

 

查看alert log:

Mon Oct 31 17:28:33 CST 2011

Errors in file/u01/app/oracle/admin/anqing/udump/anqing_ora_8722.trc:

ORA-00600: internal errorcode, arguments: [16704], [100001], [], [], [], [], [], []

Mon Oct 31 17:28:33 CST 2011

Errors in file /u01/app/oracle/admin/anqing/udump/anqing_ora_8722.trc:

ORA-00704: bootstrapprocess failure

ORA-00600: internal error code, arguments:[16704], [100001], [], [], [], [], [], []

Mon Oct 31 17:28:33 CST 2011

Error 704 happened during db open, shuttingdown database

USER: terminating instance due to error 704

Instance terminated by USER, pid = 8722

ORA-1092 signalled during: ALTER DATABASEOPEN...

 

3.4 用restorepoint 将instance还原回来:

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  377487360 bytes

Fixed Size                  1273804 bytes

Variable Size             155189300 bytes

Database Buffers          218103808 bytes

Redo Buffers                2920448 bytes

Database mounted.

SQL> select open_mode,flashback_on from v$database;

OPEN_MODE FLASHBACK_ON

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

MOUNTED   RESTORE POINT ONLY

 

SQL> flashback database to restore point bootstrap_before;

Flashback complete.

 

SQL> alter database open resetlogs;

Database altered.

 

SQL> select * from bootstrap$ where obj#=100000;

no rows selected

 

这个例子虽然还原回来,但是也证明了一个问题,重启是一个危险的操作,因此重启需要慎重在谨慎,比如这里的bootstrap$中的对象,可以进行修改,而且也不影响DB 的正常运行,但是一旦重启,那么问题就会暴露出来。

 

四. 修改bootstrap$ 中的对象

       一般来说bootstrap$中的对象是不建议修改的,但是一些特殊情况下可能会用到,要注意的事,如果因为修改bootstrap$ 导致的问题,Oracle 是不提供支持的。

 

在之前的Blog里有介绍并修改过bootstrap$下的一个对象,参考:

Oracle 数据字典表 -- SYS.COL$

http://blog.csdn.net/tianlesoftware/article/details/6168314

 

在MOS 上有一篇文章:

Size ofSys.C_obj#_intcol# cluster in system tablespace is growing [ID 463226.1]

 

这个案例将的是'SYS.C_OBJ#_INTCOL#' 占据了 90%的system 表空间,在MOS的这篇文档里只是解释了问什么会占用这么多空间。

       而且给出的解决方案是重建数据库,这个也就是说,oracle 不负责bootstrap$ 对象这部分的操作。虽然Oracle 说只能重建DB,但是 oraclefans 还是提供了一种不重建DB来解决问题的方法,原文链接如下:

http://blog.oraclefans.cn/baishan3/entry/%E4%BD%BF%E7%94%A8event_38003%E6%9D%A5%E9%87%8D%E5%BB%BAbootstrap_%E5%AF%B9%E8%B1%A1

 

根据白鳝的文章,在测试一次。感谢白鳝的幸苦劳动。

 

SQL> select obj# from obj$ where name='C_OBJ#_INTCOL#';

     OBJ#

----------

      251

 

SQL> SELECT OBJ#,NAME,TYPE# FROM OBJ$ WHERE DATAOBJ#=251;   

 

     OBJ# NAME                                TYPE#

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

      251 C_OBJ#_INTCOL#                          3

      253 HISTGRM$                                2

 

当我们进行truncate 的时候报错:

SQL> truncate cluster c_obj#_intcol#;

truncate cluster c_obj#_intcol#

                 *

ERROR at line 1:

ORA-00701: object necessary forwarmstarting database cannot be altered

 

无法变更热启动数据库所需的对象,即该对象是BOOTSTRAP$对象,所以无法TRUNCATE.

 

由于这个对象的ID是251,大于56, 因此该对象不是核心BOOTSTRAP$对象。

这里可以利用EVENT 38003,EVENT 38003可以让优化器认为这些非核心BOOTSTRAP对象不是BOOTSTRAP的,这样我们就可以对这些对象进行设置该事件之前不能操作的操作了。

 

SQL> alter system set  EVENT="38003trace name context forever, level 10" scope=spfile;

System altered.

 

--该Event 需要重启才能生效

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  377487360 bytes

Fixed Size                  1273804 bytes

Variable Size             155189300 bytes

Database Buffers          218103808 bytes

Redo Buffers                2920448 bytes

Database mounted.

Database opened.

 

SQL> truncate cluster c_obj#_intcol#;

Cluster truncated.

 

这次成功执行,不过因为这里是对HISTGRM 进行的操作,所以最好重新分析一下表,否则影响执行计划。

 

关闭event:

SQL> alter system set EVENT="38003trace name context forever off" scope=spfile;

System altered.

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-02194: event specification syntax error230 (minor error 215) near 'OFF'

 

遇到小插曲了。 一看关闭event的语法写错了,多了一个forever,现在数据库根本不能操作,连nomount都不行,不过幸运的是,我们可以直接修改pfile之后,在启动。

 

SQL> create pfile from spfile;

File created.

 

修改pfile:

*.event='38003 trace name context foreveroff'

-->

*.event='38003 trace name context off'

 

SQL> create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initanqing.ora';

File created.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area  377487360 bytes

Fixed Size                  1273804 bytes

Variable Size             155189300 bytes

Database Buffers          218103808 bytes

Redo Buffers                2920448 bytes

Database mounted.

Database opened.

 

成功启动了, 有关event的说明,参考:

Oracle 跟踪事件 set event

http://blog.csdn.net/tianlesoftware/article/details/4977827

 

最后还是那句话,对bootstrap$进行操作要慎重,如果迫不得已,也需要提前做好备份。

 

 

五.ORA-00704 错误说明

5.1 What is bootstrap process failure?ORA-00704

This ORA-00704 error SERIOUS if reported at startup. This error refers to some problem duringbootstrap operation.

Any ORA-00704error on STARTUP / RECOVER is serious, this error normally rosedue to someinconsistency with the bootstrap segments (or) datacorruption on bootstrap$ (or) any of the base tables below object_id 56. Afterthis error it might not allow to open that database.

ORA-00704 是一个非常严重的错误,一般当bootstrap$ 表发生错误或者bootstrap$ 包含的object_id 小于56的对象发生错误时,会报这种错误。遇到这种情况,如果有备份,可以进行恢复,如果没有备份,可以尝试用第四小节里提到的EVENT 38003 来进行处理。

最后实在不行,还有一个工具可以尝试:BBED。这些都是非常规的手法,要慎用。

 

5.2 When ORA-00704 shall occur?

1. There is aprobable of this error when any unsupported operations are tried to force openthe database.

2. This errorcan also occur when system datafile has corrupted blocks.(ORA-01578)

3. In earlierreleases of oracle (prior to 7.3.4 and 8.0.3) this issue shallarise due to Bug434596

 

The option is to restore it from a good backupand recover it.
-> If the underlying cause is physical corruption that is due to hardware problemsthen do complete recovery.
-> If the issue is not relating to any physical corruption, then the problemcould be due some unsupported actions on Bootstrap, and a Point In Time Recoverywould be an option in such case.

 

5.3 MOS 上的一个文档

ORA-00704: Bootstrap Process Failure When10g Upgrade is Aborted [ID 427585.1]

 

Symptoms:

ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01406: fetched column value was truncated
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 20971
ORA-1092 signalled during: ALTER DATABASE OPEN...

 

原因:

The bootstraperror failure happened since the upgrade process was abnormally terminated hence,the bootstrap Objects was corrupted and encountered with above errors。

       这个案例比较简单,因为升级过程失败,导致bootstrap object称corrupted。

 

解决方法:

Restorea cold backup of the database from before the upgrade attempt was made and thenreaccomplish the upgrade。

从备份中恢复数据库,然后重新进行升级。

 

 

 

 

 

 

 

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

版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!

Blog:     http://blog.csdn.net/tianlesoftware

Weibo: http://weibo.com/tianlesoftware

Email:   tianlesoftware@gmail.com

Skype: tianlesoftware

 

-------加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请----

DBA1 群:62697716(满);   DBA2 群:62697977(满)  DBA3 群:62697850(满)  

DBA 超级群:63306533(满);  DBA4 群:83829929(满) DBA5群: 142216823(满) 

DBA6 群:158654907(满)   DBA7 群:69087192(满)  DBA8 群:172855474

DBA 超级群2:151508914  DBA9群:102954821     聊天 群:40132017(满)

posted @ 2011-10-31 19:24  hibernate我最强  阅读(226)  评论(0编辑  收藏  举报