在上一篇数据恢复文章中，我提到了bootstrap 核心数据数据字典表的对象index出现异常后，难以修复。实际上，仅仅是数据不一致（或类似的情况）导致的index异常，其实有其他的方式进行重建。实际上Oracle 11gR2版本中的如下脚本提供了相关的解决方案：$ORACLE_HOME/rdbms/admin/utlmmig.sql. 虽然该脚本的的解决方法是针对从10g升级到11gR2出现异常后的处理方式，然而该脚本中的内容，却值得我们深入研究。
几年前之前也写过一篇通过bbed来修复bootstrap 核心对象的例子：bootstrap$核心对象数据不一致导致ORA-08102
这里以上篇文章中提到的2个index 为例进行说明：
根据utlmmig.sql的处理思路，我们很容易进行仿制，如下。
---创建table
---创建需要修复的Index
---Prepare the bootstrap sql text for the new objects
---Copy data from old tables to the new tables.
---处于性能考虑，更新表的统计信息
---新旧表/index 进行交换
----删除bootstrap$mig中的旧数据
----将新对象插入到bootstrap$mig中
----处理依赖关系和权限
----将Swap bootstrap$mig 和 bootstrap$
上述脚本关键的一点是借助了dbms_ddl_internal.swap_bootstrap，而swap_bootstrap 这个存储过程在11g中才存在。
而我这里是10g的环境，因此无法使用该存储过程。不过这里可以换个方法，Oracle在open的时候是通过读取system数据文件头的offset 96的root dba来获取bootstrap$ 的段头地址，然后完成bootstrap 对象的创建过程。
因此我们这里通过bbed 手工修改这里的root dba地址即可，这样的效果跟使用swap_boostrap的效果一样。
----重启数据库即可
步骤略.
几年前之前也写过一篇通过bbed来修复bootstrap 核心对象的例子：bootstrap$核心对象数据不一致导致ORA-08102
这里以上篇文章中提到的2个index 为例进行说明：
SQL> alter index sys.i_obj1 rebuild;
alter index sys.i_obj1 rebuild
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
SQL> alter index sys.i_obj2 rebuild;
alter index sys.i_obj2 rebuild
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
SQL> select object_name from dba_objects where object_id=36;
OBJECT_NAME
--------------------------------------------------------------------------------
I_OBJ1
SQL> select object_name from dba_objects where object_id=37;
OBJECT_NAME
--------------------------------------------------------------------------------
I_OBJ2复制
根据utlmmig.sql的处理思路，我们很容易进行仿制，如下。
---创建table
SQL> create table obj$mig /* object table */
2 ( obj# number not null, /* object number */
3 dataobj# number, /* data layer object number */
4 owner# number not null, /* owner user number */
5 name varchar2(30) not null, /* object name */
6 namespace number not null, /* namespace of object (see KQD.H): */
7 /* 1 = TABLE/PROCEDURE/TYPE, 2 = BODY, 3 = TRIGGER, 4 = INDEX, 5 = CLUSTER, */
8 /* 8 = LOB, 9 = DIRECTORY, */
9 /* 10 = QUEUE, 11 = REPLICATION OBJECT GROUP, 12 = REPLICATION PROPAGATOR, */
10 /* 13 = JAVA SOURCE, 14 = JAVA RESOURCE */
11 /* 58 = (Data Mining) MODEL */
12 subname varchar2(30), /* subordinate to the name */
13 type# number not null, /* object type (see KQD.H): */
14 /* 1 = INDEX, 2 = TABLE, 3 = CLUSTER, 4 = VIEW, 5 = SYNONYM, 6 = SEQUENCE, */
15 /* 7 = PROCEDURE, 8 = FUNCTION, 9 = PACKAGE, 10 = NON-EXISTENT, */
16 /* 11 = PACKAGE BODY, 12 = TRIGGER, 13 = TYPE, 14 = TYPE BODY, */
17 /* 19 = TABLE PARTITION, 20 = INDEX PARTITION, 21 = LOB, 22 = LIBRARY, */
18 /* 23 = DIRECTORY , 24 = QUEUE, */
19 /* 25 = IOT, 26 = REPLICATION OBJECT GROUP, 27 = REPLICATION PROPAGATOR, */
20 /* 28 = JAVA SOURCE, 29 = JAVA CLASS, 30 = JAVA RESOURCE, 31 = JAVA JAR, */
21 /* 32 = INDEXTYPE, 33 = OPERATOR , 34 = TABLE SUBPARTITION, */
22 /* 35 = INDEX SUBPARTITION */
23 /* 82 = (Data Mining) MODEL */
24 /* 92 = OLAP PRIMARY DIMENSION, 93 = OLAP CUBE */
25 /* 94 = OLAP MEASURE FOLDER, 95 = OLAP INTERACTION */
26 ctime date not null, /* object creation time */
27 mtime date not null, /* DDL modification time */
28 stime date not null, /* specification timestamp (version) */
29 status number not null, /* status of object (see KQD.H): */
30 /* 1 = VALID/AUTHORIZED WITHOUT ERRORS, */
31 /* 2 = VALID/AUTHORIZED WITH AUTHORIZATION ERRORS, */
32 /* 3 = VALID/AUTHORIZED WITH COMPILATION ERRORS, */
33 /* 4 = VALID/UNAUTHORIZED, 5 = INVALID/UNAUTHORIZED */
34 remoteowner varchar2(30), /* remote owner name (remote object) */
35 linkname varchar2(128), /* link name (remote object) */
36 flags number, /* 0x01 = extent map checking required */
37 /* 0x02 = temporary object */
38 /* 0x04 = system generated object */
39 /* 0x08 = unbound (invoker's rights) */
40 /* 0x10 = secondary object */
41 /* 0x20 = in-memory temp table */
42 /* 0x80 = dropped table (RecycleBin) */
43 /* 0x100 = synonym VPD policies */
44 /* 0x200 = synonym VPD groups */
45 /* 0x400 = synonym VPD context */
46 oid$ raw(16), /* OID for typed table, typed view, and type */
47 spare1 number, /* sql version flag: see kpul.h */
48 spare2 number, /* object version number */
49 spare3 number, /* base user# */
50 spare4 varchar2(1000),
51 spare5 varchar2(1000),
52 spare6 date
53 )
54 /
Table created.
SQL> create table bootstrap$mig
2 ( line# number not null, /* statement order id */
3 obj# number not null, /* object number */
4 sql_text varchar2(4000) not null) /* statement */
5 /
Table created.
SQL> create table bootstrap$tmpstr
2 ( line# number not null, /* statement order id */
3 obj# number not null, /* object number */
4 sql_text varchar2(4000) not null) /* statement */
5 /
Table created.
SQL>复制
---创建需要修复的Index
SQL> create unique index i_obj_mig1 on obj$mig(obj#, owner#, type#);
Index created.
SQL> create unique index i_obj_mig2 on obj$mig(owner#, name, namespace, type#,
2 spare3, remoteowner, linkname, subname, obj#);
Index created.复制
---Prepare the bootstrap sql text for the new objects
SQL> declare
2 pl_objtxt varchar2(4000); /* bootstrap$.sql_text for the new obj */
3 pl_obj_num number; /* obj# of the new obj */
4 pl_line_num number; /* line# in bootstrap$ for the new obj */
5
6 /* Get Obj Number in OBJ$
7 Given the obj name and namespace, return the obj# in obj$.
8 */
9 function get_obj_num(pl_objname varchar2, pl_nmspc number) return number
10 is
11 pl_obn number;
12 begin
13 select obj# into pl_obn from sys.obj$
14 where owner#=0 and name=pl_objname and namespace=pl_nmspc;
15
16 return pl_obn;
17 end;
18
19 /* Get Line Number in bootstrap$
20 Given the obj name and namespace, returns the line# in boostrap$. If the
21 obj doesn't exists, then return null.
22 */
23 function get_line_num(pl_objname varchar2, pl_nmspc number) return number
24 is
25 pl_bln number;
26 begin
27 select b.line# into pl_bln
28 from sys.bootstrap$ b, sys.obj$ o
29 where o.owner# = 0
30 and o.name = pl_objname
31 and o.obj# = b.obj#
32 and o.namespace = pl_nmspc;
33
34 return pl_bln;
35 exception
36 when NO_DATA_FOUND then
37 return NULL;
38 end;
39
40 /* Storage text generation
41 The bootstrap$ sql_text requires the DDL to provide the storage
42 parameters. The following function will generate the storage
43 parameter for table creation and index creation, given the obj# as input.
44 */
45 -- generate storage parameter
46 -- it requires some info from tab$/ind$, seg$, ts$
47 function gen_storage(pl_objnum number, pl_objtype varchar2) return varchar2
48 is
49 pl_text varchar2(4000);
50 pl_pctf number;
51 pl_pctused number;
52 pl_initrans number;
53 pl_maxtrans number;
54 pl_file_num number;
55 pl_block_num number;
56 pl_ts_num number;
57 pl_tab_num number;
58 pl_initial number;
59 pl_next number;
60 pl_minext number;
61 pl_maxext number;
62 pl_pctinc number;
63 pl_block_size number;
64 begin
65 if (pl_objtype = 'TABLE') then
66 -- info from tab$
67 select pctfree$, pctused$, initrans, maxtrans, file#, block#, ts#
68 into pl_pctf, pl_pctused, pl_initrans, pl_maxtrans,
69 pl_file_num, pl_block_num, pl_ts_num
70 from sys.tab$
71 where obj# = pl_objnum;
72 elsif (pl_objtype = 'CLUSTER TABLE') then
73 select tab#
74 into pl_tab_num
75 from sys.tab$
76 where obj# = pl_objnum;
77 elsif (pl_objtype = 'INDEX') then
78 -- info from ind$
79 select pctfree$, initrans, maxtrans, file#, block#, ts#
80 into pl_pctf, pl_initrans, pl_maxtrans,
81 pl_file_num, pl_block_num, pl_ts_num
82 from ind$ where obj# = pl_objnum;
83 end if;
84
85 if (pl_objtype != 'CLUSTER TABLE') then
86 -- info from seg$
87 select iniexts, minexts, maxexts, extsize, extpct
88 into pl_initial, pl_minext, pl_maxext, pl_next, pl_pctinc
89 from sys.seg$
90 where file# = pl_file_num
91 and block# = pl_block_num
92 and ts# = pl_ts_num;
93
94 -- info from ts$
95 select blocksize into pl_block_size from sys.ts$ where ts# = pl_ts_num;
96 pl_initial := pl_initial * pl_block_size;
97 pl_next := pl_next * pl_block_size;
98 end if;
99
100 if (pl_objtype = 'TABLE') then
101 -- generate the table storage text
102 pl_text := ' PCTFREE ' || pl_pctf || ' PCTUSED ' || pl_pctused ||
103 ' INITRANS ' || pl_initrans || ' MAXTRANS '|| pl_maxtrans ||
104 ' STORAGE ( INITIAL ' || pl_initial ||
105 ' NEXT ' || pl_next ||
106 ' MINEXTENTS ' || pl_minext ||
107 ' MAXEXTENTS ' || pl_maxext ||
108 ' PCTINCREASE ' || pl_pctinc ||
109 ' OBJNO ' || pl_obj_num ||
110 ' EXTENTS (FILE ' || pl_file_num ||
111 ' BLOCK ' || pl_block_num ||'))';
112 elsif (pl_objtype = 'CLUSTER TABLE') then
113 pl_text := ' STORAGE ( OBJNO '|| pl_obj_num ||
114 ' TABNO '|| pl_tab_num ||
115 ') CLUSTER C_USER#(USER#)';
116 elsif (pl_objtype = 'INDEX') then
117 -- generate the index storage text
118 pl_text := ' PCTFREE ' || pl_pctf ||
119 ' INITRANS ' || pl_initrans ||
120 ' MAXTRANS ' || pl_maxtrans ||
121 ' STORAGE ( INITIAL ' || pl_initial ||
122 ' NEXT ' || pl_next ||
123 ' MINEXTENTS ' || pl_minext ||
124 ' MAXEXTENTS ' || pl_maxext ||
125 ' PCTINCREASE ' || pl_pctinc ||
126 ' OBJNO ' || pl_obj_num ||
127 ' EXTENTS (FILE ' || pl_file_num ||
128 ' BLOCK ' || pl_block_num ||'))';
129 end if;
130
131 return pl_text;
132 end;
133
134 begin
135 /* Create the bootstrap sql text for OBJ$ */
136 pl_obj_num := get_obj_num('OBJ$MIG', 1);
137 pl_line_num := get_line_num('OBJ$', 1);
138 pl_objtxt := '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)';
139 pl_objtxt := pl_objtxt || gen_storage(pl_obj_num, 'TABLE');
140 insert into bootstrap$tmpstr values(pl_line_num, pl_obj_num, pl_objtxt);
141 commit;
142
143
144 /* Create the bootstrap sql text for I_OBJ_MIG1 (replace i_obj1) */
145 pl_obj_num := get_obj_num('I_OBJ_MIG1', 4);
146 pl_line_num := get_line_num('I_OBJ1', 4);
147 pl_objtxt :='create unique index i_obj1 on obj$(obj#, owner#, type#)';
148 pl_objtxt := pl_objtxt || gen_storage(pl_obj_num, 'INDEX');
149 insert into bootstrap$tmpstr values(pl_line_num, pl_obj_num, pl_objtxt);
150 commit;
151
152
153 /* Create the bootstrap sql text for I_OBJ_MIG2 (replace i_obj2) */
154 pl_obj_num := get_obj_num('I_OBJ_MIG2', 4);
155 pl_line_num := get_line_num('I_OBJ2', 4);
156 pl_objtxt := 'create unique index i_obj2 on obj$(owner#, name, namespace, type#, spare3, remoteowner, linkname, subname, obj#)';
157 pl_objtxt := pl_objtxt || gen_storage(pl_obj_num, 'INDEX');
158 insert into bootstrap$tmpstr values(pl_line_num, pl_obj_num, pl_objtxt);
159 commit;
160
161 end;
162 /
PL/SQL procedure successfully completed.
SQL>
SQL>复制
---Copy data from old tables to the new tables.
SQL> declare
2 upperbound number;
3 lowerbound number;
4 maxobjnum number;
5 begin
6 lowerbound := 0;
7 upperbound := 10000;
8 select max(obj#) into maxobjnum from obj$;
9 loop
10 insert into obj$mig select * from obj$
11 where obj#>=lowerbound and obj#<upperbound;
12 commit;
13 exit when upperbound > maxobjnum;
14 lowerbound := upperbound;
15 upperbound := upperbound + 10000;
16 end loop;
17 end;
18 /
PL/SQL procedure successfully completed.
SQL> insert into bootstrap$mig select * from bootstrap$;
56 rows created.
SQL> commit;
Commit complete.复制
---处于性能考虑，更新表的统计信息
SQL> begin
2 dbms_stats.delete_table_stats('SYS', 'OBJ$MIG');
3 dbms_Stats.gather_table_stats('SYS', 'OBJ$MIG', estimate_percent => 100,
4 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
5 end;
6 /
PL/SQL procedure successfully completed.复制
---新旧表/index 进行交换
SQL> declare
2 type vc_nst_type is table of varchar2(30);
3 type nb_nst_type is table of number;
4 old_name_array vc_nst_type; /* old object name array */
5 new_name_array vc_nst_type; /* new object name array */
6 ns_array nb_nst_type; /* namespace of the object */
7 begin
8 old_name_array := vc_nst_type('OBJ$','I_OBJ1', 'I_OBJ2',
9 'BOOTSTRAP$');
10 new_name_array := vc_nst_type('OBJ$MIG', 'I_OBJ_MIG1', 'I_OBJ_MIG2',
11 'BOOTSTRAP$MIG');
12 ns_array := nb_nst_type(1,4,4,4,4,4,
13 1,4,4,
14 1);
15
16 /* Swap the name in old_name_array with new_name_array in OBJ$MIG */
17 for i in old_name_array.FIRST .. old_name_array.LAST
18 loop
19 update obj$mig set name = 'ORA$MIG_TMP'
20 where name = old_name_array(i) and owner# = 0 and namespace=ns_array(i);
21 update obj$mig set name = old_name_array(i)
22 where name = new_name_array(i) and owner# = 0 and namespace=ns_array(i);
23 update obj$mig set name = new_name_array(i)
24 where name = 'ORA$MIG_TMP' and owner# = 0 and namespace=ns_array(i);
25 end loop;
26
27 /* Commit when we're done with the swap */
28 commit;
29 end;
30 /
PL/SQL procedure successfully completed.
SQL>复制
----删除bootstrap$mig中的旧数据
SQL> delete from bootstrap$mig
2 where obj# in
3 (select obj#
4 from obj$
5 where name in ('OBJ$', 'I_OBJ1', 'I_OBJ2', 'BOOTSTRAP$'));
4 rows deleted.
SQL> commit;
Commit complete.复制
----将新对象插入到bootstrap$mig中
SQL> insert into bootstrap$mig select * from bootstrap$tmpstr;
4 rows created.
SQL> commit;
Commit complete.复制
----处理依赖关系和权限
SQL> declare
2 type vc_nst_type is table of varchar2(30);
3 old_obj_num number;
4 new_obj_num number;
5 new_ts timestamp;
6 old_name vc_nst_type;
7 new_name vc_nst_type;
8 begin
9 old_name := vc_nst_type('OBJ$', 'BOOTSTRAP$');
10 new_name := vc_nst_type('OBJ$MIG', 'BOOTSTRAP$MIG');
11
12 for i in old_name.FIRST .. old_name.LAST
13 loop
14 select obj# into old_obj_num from obj$
15 where owner#=0 and name=old_name(i) and namespace=1;
16 select obj#, stime into new_obj_num, new_ts
17 from obj$ where owner#=0 and name=new_name(i) and namespace=1;
18
19 -- Step 7
20 update dependency$
21 set p_obj# = new_obj_num,
22 p_timestamp = new_ts
23 where p_obj# = old_obj_num;
24
25 -- Step 8
26 update objauth$ set obj# = new_obj_num where obj# = old_obj_num;
27
28 end loop;
29
30 commit;
31 end;
32 /
PL/SQL procedure successfully completed.复制
----将Swap bootstrap$mig 和 bootstrap$
WHENEVER SQLERROR CONTINUE
declare
LS_Special_3 CONSTANT NUMBER := 11;
LOCbldlogid VARCHAR2(22) := NULL;
LOCLockDownScn NUMBER;
rowcnt NUMBER;
begin
SELECT COUNT(1) into rowcnt
FROM SYS.V$DATABASE V
WHERE V.LOG_MODE = 'ARCHIVELOG' and
V.SUPPLEMENTAL_LOG_DATA_MIN != 'NO';
IF 0 != rowcnt THEN
-- Logminer may be mining this redo stream, so we must do a special
-- logminer dictionary build to capture the revised obj# etc.
sys.dbms_logmnr_internal.DO_INT_BUILD(build_op=>LS_Special_3,
dictionary_filename=>NULL,
dictionary_location=>NULL,
bldlogid_initxid=>LOCbldlogid,
LockDownScn=>LOCLockDownScn,
release_locks=>FALSE);
END IF;
-- Now we can do the swap.
dbms_ddl_internal.swap_bootstrap('BOOTSTRAP$MIG');
-- We've completed the swap.
-- Remove the BOOTSTRAP_UPGRADE_ERROR entry in props$.
delete from props$ where name = 'BOOTSTRAP_UPGRADE_ERROR';
delete from props$ where name = 'LOGMNR_BOOTSTRAP_UPGRADE_ERROR';
commit;
end;
/复制
上述脚本关键的一点是借助了dbms_ddl_internal.swap_bootstrap，而swap_bootstrap 这个存储过程在11g中才存在。
而我这里是10g的环境，因此无法使用该存储过程。不过这里可以换个方法，Oracle在open的时候是通过读取system数据文件头的offset 96的root dba来获取bootstrap$ 的段头地址，然后完成bootstrap 对象的创建过程。
因此我们这里通过bbed 手工修改这里的root dba地址即可，这样的效果跟使用swap_boostrap的效果一样。
BBED> p kcvfhrdb
ub4 kcvfhrdb @96 0x00400208
BBED>复制
----重启数据库即可
步骤略.
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
【专家有话说第五期】在不同年龄段，DBA应该怎样规划自己的职业发展？
墨天轮编辑部
1270次阅读
2025-03-13 11:40:53
Oracle RAC ASM 磁盘组满了，无法扩容怎么在线处理？
Lucifer三思而后行
766次阅读
2025-03-17 11:33:53
Oracle+Deepseek+Dify 实现数据库数据实时分析
bicewow
685次阅读
2025-03-06 09:41:49
【ORACLE】ORACLE19C在19.13版本前的一个严重BUG-24761824
DarkAthena
564次阅读
2025-03-04 14:33:31
Oracle避坑指南｜同名表导出难题：如何精准排除指定用户下的表？
szrsu
513次阅读
2025-03-05 00:42:34
2月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
453次阅读
2025-03-13 14:38:19
Ogg23ai高手必看-MySQL Innodb Cluster跟oracle的亲密接触
曹海峰
447次阅读
2025-03-04 21:56:13
【ORACLE】char类型和sql优化器发生的“错误”反应
DarkAthena
404次阅读
2025-03-04 23:05:01
什么，oracle 主机用户被删了？原来是虚惊一场！
Lucifer三思而后行
401次阅读
2025-03-03 21:12:09
Oracle 如何修改 db_unique_name？强迫症福音！
Lucifer三思而后行
340次阅读
2025-03-12 21:27:56
