联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在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表中记录完全相同.