在上一篇数据恢复文章中，我提到了bootstrap 核心数据数据字典表的对象index出现异常后，难以修复。实际上，仅仅是数据不一致（或类似的情况）导致的index异常，其实有其他的方式进行重建。实际上Oracle 11gR2版本中的如下脚本提供了相关的解决方案：$ORACLE_HOME/rdbms/admin/utlmmig.sql. 虽然该脚本的的解决方法是针对从10g升级到11gR2出现异常后的处理方式，然而该脚本中的内容，却值得我们深入研究。
几年前之前也写过一篇通过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>—-重启数据库即可
步骤略.
发表回复