月归档：十二月 2013

在11G和12C中,我们可以通过DBMS_DDL_INTERNAL.SWAP_BOOTSTRAP过程来替换bootstarp$表(见:替换bootstarp$表)，但是对于10G或者其他版本,oracle没有提供相关程序来完成使用其他表替换bootstarp$,通过分析,使用bbed修改root rdba也可以完成DBMS_DDL_INTERNAL.SWAP_BOOTSTRAP的任务

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> DESC DBMS_DDL_INTERNAL
PROCEDURE CHECK_TRIGGER_FIRING_PROPERTY
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 TRIG_OWNER                     VARCHAR2                IN
 TRIG_NAME                      VARCHAR2                IN
 CANON_OWNER                    VARCHAR2                OUT
 CANON_ONAME                    VARCHAR2                OUT
 P_PROPERTY                     NUMBER                  IN/OUT
 UNSUPPORTED_TRIG               BOOLEAN                 OUT
FUNCTION HAS_ALTER_ANY_TRIGGER_PRIV RETURNS BOOLEAN
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 P_USER                         VARCHAR2                IN
 P_TRIG_PROPERTY                NUMBER                  IN
FUNCTION HAS_EXP_IMP_PRIV RETURNS BOOLEAN
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 P_UID                          NUMBER                  IN
 P_PRIVS_TO_CHECK               VARCHAR2                IN
FUNCTION IS_DDL_TRIGGER RETURNS BOOLEAN
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 SYS_EVTS                       NUMBER                  IN

跟踪数据库启动过程

SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area  209715200 bytes
Fixed Size                  1289724 bytes
Variable Size             100663812 bytes
Database Buffers          100663296 bytes
Redo Buffers                7098368 bytes
数据库装载完毕。
SQL> oradebug setmypid
已处理的语句
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
已处理的语句
SQL> alter session set db_file_multiblocK_read_count=1;

会话已更改。

SQL> oradebug TRACEFILE_NAME
e:\oracle\product\10.2.0\admin\ora10g\udump\ora10g_ora_8360.trc
SQL> alter database Open;

数据库已更改。

SQL> oradebug EVENT 10046 trace name context off
已处理的语句

阅读10046 trace文件

WAIT #1: nam='instance state change' ela= 28 layer=2 value=1 waited=1 obj#=-1 tim=377999209439
WAIT #1: nam='db file sequential read' ela= 94860 file#=1 block#=377 blocks=1 obj#=-1 tim=377999304467
=====================
PARSING IN CURSOR #2 len=188 dep=1 uid=0 oct=1 lid=0 tim=377999305344 hv=1365064427 ad='8baee680'
create table bootstrap$ ( line#         number not null,   obj#           number not null,   sql_text   varchar2(4000) not null)   storage (initial 50K objno 56 extents (file 1 block 377))
END OF STMT
PARSE #2:c=0,e=662,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=377999305341
BINDS #2:
EXEC #2:c=0,e=102,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=377999305545
=====================
PARSING IN CURSOR #2 len=55 dep=1 uid=0 oct=3 lid=0 tim=377999305925 hv=2111436465 ad='8baedf0c'
select line#, sql_text from bootstrap$ where obj# != :1
END OF STMT
PARSE #2:c=0,e=308,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=377999305922
BINDS #2:
kkscoacd
 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=00288360  bln=22  avl=02  flg=05
  value=56
EXEC #2:c=0,e=580,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=377999306621
WAIT #2: nam='db file sequential read' ela= 328 file#=1 block#=377 blocks=1 obj#=-1 tim=377999307005
WAIT #2: nam='db file sequential read' ela= 345 file#=1 block#=378 blocks=1 obj#=-1 tim=377999307423

这里可以发现,数据库是在启动的时候读file 1 block 377，然后create table bootstrap$(注意:这里的语句小写),对于bootstarp$的查询除掉了obj#<>56

分析bootstarp$对象

SQL> select header_file,header_block from dba_segments where segment_name='BOOTSTRAP$';

HEADER_FILE HEADER_BLOCK
----------- ------------
          1          377

SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='BOOTSTRAP$';

 OBJECT_ID
----------
        56

SQL> select line#, sql_text from bootstrap$ where obj# =56;

     LINE#
----------
SQL_TEXT
--------------------------------------------------------------------------------
        56
CREATE TABLE BOOTSTRAP$("LINE#" NUMBER NOT NULL,"OBJ#" NUMBER NOT NULL,"SQL_TEXT
" VARCHAR2(4000) NOT NULL) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE
 (  INITIAL 56K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJN
O 56 EXTENTS (FILE 1 BLOCK 377))

这里可以发现bootstrap$中obj#=56的那条记录为CREATE TABLE BOOTSTRAP$(注意:该表里面保存为大写)

bbed查看root rdba

C:\Windows\system32>e:\oracle\product\10.2.0\dbhome_1\bin\bbed password=blockedit blocksize=8192

BBED: Release 2.0.0.0.0 - Limited Production on Tue Dec 17 18:36:01 2013

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set block 1
BBED-00310: no datafile specified


BBED> set filename 'E:\APP\XIFENFEI\ORADATA\ORA10G\SYSTEM01.DBF'
        FILENAME        E:\APP\XIFENFEI\ORADATA\ORA10G\SYSTEM01.DBF

BBED> set block 2
        BLOCK#          2

BBED> map
 File: E:\APP\XIFENFEI\ORADATA\ORA10G\SYSTEM01.DBF (0)
 Block: 2                                     Dba:0x00000000
------------------------------------------------------------
 Data File Header

 struct kcvfh, 360 bytes                    @0

 ub4 tailchk                                @8188


BBED> p kcvfhrdb
ub4 kcvfhrdb                                @96       0x00400179

SQL> Select to_number('00400179','xxxxxxxxxxxxxxxxxx') from dual;

TO_NUMBER('00400179','XXXXXXXXXXXXXXXXXX')
------------------------------------------
                                   4194681

SQL> select dbms_utility.data_block_address_block(4194681) "block",
  2  dbms_utility.data_block_address_file(4194681) "file" from dual;

     block       file
---------- ----------
       377          1

通过bbed查看kcvfhrdb(root rdba)指向的地址和数据库启动扫描block一致(file 1b block 377)

创建bootstarp$替换表(xifenfei)

SQL> create table xifenfei as select * from bootstrap$;

表已创建。

SQL> select count(*) from bootstrap$;

  COUNT(*)
----------
        57

SQL> select count(*) from xifenfei;

  COUNT(*)
----------
        57

SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='XIFENFEI';

 OBJECT_ID
----------
     51736

SQL> select header_file,header_block from dba_segments where segment_name='XIFENFEI';

HEADER_FILE HEADER_BLOCK
----------- ------------
          1        60241

SQL> SELECT TO_CHAR(60241,'XXXX') FROM DUAL;

TO_CH
-----
 EB51
--对应rdba为0040EB51

这里可以确定创建的xifenfei的segment header rdba为0x0040EB51,obj#为51736

清理bootstarp$中对象

SQL> DELETE FROM BOOTSTRAP$;

已删除57行。

SQL> COMMIT;

提交完成。

SQL> SHUTDOWN IMMEDIATE;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> STARTUP MOUNT
ORACLE 例程已经启动。

Total System Global Area  209715200 bytes
Fixed Size                  1289724 bytes
Variable Size             104858116 bytes
Database Buffers           96468992 bytes
Redo Buffers                7098368 bytes
数据库装载完毕。
SQL> oradebug setmypid
已处理的语句
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
已处理的语句
SQL> alter session set db_file_multiblocK_read_count=1;

会话已更改。

SQL> oradebug TRACEFILE_NAME
e:\oracle\product\10.2.0\admin\ora10g\udump\ora10g_ora_7704.trc
SQL>
SQL> alter database Open;
alter database Open
*
第 1 行出现错误:
ORA-01092: ORACLE 实例终止。强制断开连接

--trace文件
PARSING IN CURSOR #2 len=55 dep=1 uid=0 oct=3 lid=0 tim=379061819061 hv=2111436465 ad='8baedf18'
select line#, sql_text from bootstrap$ where obj# != :1
END OF STMT
PARSE #2:c=0,e=346,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=379061819058
BINDS #2:
kkscoacd
 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=00288360  bln=22  avl=02  flg=05
  value=56
EXEC #2:c=0,e=681,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=379061819868
WAIT #2: nam='db file sequential read' ela= 343 file#=1 block#=377 blocks=1 obj#=-1 tim=379061820273
WAIT #2: nam='db file sequential read' ela= 291 file#=1 block#=378 blocks=1 obj#=-1 tim=379061820651
WAIT #2: nam='db file sequential read' ela= 309 file#=1 block#=379 blocks=1 obj#=-1 tim=379061821012
WAIT #2: nam='db file sequential read' ela= 293 file#=1 block#=380 blocks=1 obj#=-1 tim=379061821416
FETCH #2:c=0,e=1542,p=4,cr=5,cu=0,mis=0,r=0,dep=1,og=4,tim=379061821450
ORA-00704: 引导程序进程失败
ORA-00702: 引导程序版本 '' 与版本 '8.0.0.0.0' 不一致
*** 2013-12-17 18:50:07.325
EXEC #1:c=62400,e=4990345,p=10,cr=6,cu=0,mis=0,r=0,dep=0,og=1,tim=379065822300
ERROR #1:err=1092 tim=37915057

删除掉bootstarp中记录后，数据库无法正常启动，报错误为ORA-00704/ORA-00702,因为数据库读取bootstarp$中记录出错导致.

bbed修改root rdba

BBED> set mode edit
        MODE            Edit

BBED> set count 32
        COUNT           32

BBED> d
 File: E:\APP\XIFENFEI\ORADATA\ORA10G\SYSTEM01.DBF (0)
 Block: 2                Offsets:   96 to  127           Dba:0x00000000
------------------------------------------------------------------------
 79014000 0b000000 00000000 d10ff624 485dbc31 4bf60700 00000000 00000000

 <32 bytes per line>


BBED> m /x 51eb
 File: E:\APP\XIFENFEI\ORADATA\ORA10G\SYSTEM01.DBF (0)
 Block: 2                Offsets:   96 to  127           Dba:0x00000000
------------------------------------------------------------------------
 51eb4000 0b000000 00000000 d10ff624 485dbc31 4bf60700 00000000 00000000

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 2:
current = 0xa3bd, required = 0xa3bd

BBED> p kcvfhrdb
ub4 kcvfhrdb                                @96       0x0040eb51

修改root rdba地址为xifenfei segment header的地址

尝试启动数据库

SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area  209715200 bytes
Fixed Size                  1289724 bytes
Variable Size             113246724 bytes
Database Buffers           88080384 bytes
Redo Buffers                7098368 bytes
数据库装载完毕。
SQL> oradebug setmypid
已处理的语句
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
已处理的语句
SQL> alter session set db_file_multiblocK_read_count=1;

会话已更改。

SQL> oradebug TRACEFILE_NAME
e:\oracle\product\10.2.0\admin\ora10g\udump\ora10g_ora_7356.trc
SQL> alter database Open;
alter database Open
*
第 1 行出现错误:
ORA-01092: ORACLE 实例终止。强制断开连接

--trace文件
WAIT #1: nam='db file sequential read' ela= 26895 file#=1 block#=60241 blocks=1 obj#=-1 tim=380397162424
=====================
PARSING IN CURSOR #2 len=193 dep=1 uid=0 oct=1 lid=0 tim=380397162916 hv=1250491271 ad='8baee6a0'
create table bootstrap$ ( line#         number not null,   obj#           number not null,   sql_text   varchar2(4000) not null)   storage (initial 50K objno 51736 extents (file 1 block 60241))
END OF STMT
PARSE #2:c=0,e=372,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=380397162912
BINDS #2:
EXEC #2:c=0,e=80,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=380397163083
=====================
PARSING IN CURSOR #2 len=55 dep=1 uid=0 oct=3 lid=0 tim=380397163449 hv=2111436465 ad='8baedf2c'
select line#, sql_text from bootstrap$ where obj# != :1
END OF STMT
PARSE #2:c=0,e=311,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=380397163447
BINDS #2:
kkscoacd
 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=00288360  bln=22  avl=04  flg=05
  value=51736
EXEC #2:c=0,e=515,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=380397164052
WAIT #2: nam='db file sequential read' ela= 314 file#=1 block#=60241 blocks=1 obj#=-1 tim=380397164415
WAIT #2: nam='db file sequential read' ela= 396 file#=1 block#=60242 blocks=1 obj#=-1 tim=380397164902
…………
PARSING IN CURSOR #2 len=272 dep=1 uid=0 oct=1 lid=0 tim=380397203298 hv=2124945659 ad='8bacb620'
CREATE TABLE BOOTSTRAP$("LINE#" NUMBER NOT NULL,"OBJ#" NUMBER NOT NULL,"SQL_TEXT" VARCHAR2(4000) NOT NULL) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (  INITIAL 56K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 56 EXTENTS (FILE 1 BLOCK 377))
END OF STMT
PARSE #2:c=0,e=239,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=380397203295
BINDS #2:
EXEC #2:c=0,e=324,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=380397203701
ERROR #2:err=955 tim=38048197
ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-00955: 名称已由现有对象使用
*** 2013-12-17 19:12:21.783
EXEC #1:c=93601,e=4199938,p=10,cr=60,cu=0,mis=0,r=0,dep=0,og=1,tim=380400250570
ERROR #1:err=1092 tim=38048501

数据库启动到创建bootstarp$的时候报错，报错的原因是因为xifenfei对象中的obj#=56的为CREATE TABLE BOOTSTRAP$,而前面的查询bootstarp$是过滤掉了obj#=56(为过滤掉xifenfei对象本身的obj#[51736])

upgrade模式启动数据库

SQL> conn / as sysdba
已连接到空闲例程。
SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area  209715200 bytes
Fixed Size                  1289724 bytes
Variable Size             117441028 bytes
Database Buffers           83886080 bytes
Redo Buffers                7098368 bytes
数据库装载完毕。
SQL> alter database Open upgrade;

数据库已更改。

虽然启动的时候在报CREATE TABLE BOOTSTRAP$(注意大写,而不是启动第一条的create table bootstrap$),但是upgrade模式可以正常启动数据库

修改xifenfei中关于CREATE TABLE BOOTSTRAP$语句对应的obj#为xifenfei object_id

SQL> UPDATE XIFENFEI SET OBJ#=51736 WHERE OBJ#=56;

已更新 1 行。

SQL> commit;

提交完成。


SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area  209715200 bytes
Fixed Size                  1289724 bytes
Variable Size             121635332 bytes
Database Buffers           79691776 bytes
Redo Buffers                7098368 bytes
数据库装载完毕。
SQL> alter database open;

数据库已更改。

至此通过bbed结合修改CREATE TABLE BOOTSTRAP$语句对应的obj#完成数据库启动读取非bootstarp$表的过程

继续分析xifenfei和bootstarp$关系

SQL> select count(*) from bootstrap$;

  COUNT(*)
----------
        57

SQL> select count(*) from xifenfei;

  COUNT(*)
----------
        57

SQL> select obj# from bootstrap$ where line#=56;

      OBJ#
----------
     51736

SQL> select obj# from xifenfei where line#=56;

      OBJ#
----------
     51736

SQL> select header_file,header_block from dba_segments where segment_name='BOOTSTRAP$';

HEADER_FILE HEADER_BLOCK
----------- ------------
          1          377

SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='BOOTSTRAP$';

 OBJECT_ID
----------
        56

SQL> truncate table xifenfei;
truncate table xifenfei
               *
第 1 行出现错误:
ORA-00701: 无法改变热启动数据库所需的对象

数据库启动过程中，会读xifenfei(root rdba指向表),然后加载bootstarp$表,而且bootstarp$表中记录和xifenfei表中记录完全相同.