How to recreate Bootstrap Index(I_OBJ1,I_USER1,I_FILE#_BLOCK#) to fix ORA-00701 ?
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: How to recreate Bootstrap Index(I_OBJ1,I_USER1,I_FILE#_BLOCK#) to fix ORA-00701 ?
在上一篇数据恢复文章中，我提到了bootstrap 核心数据数据字典表的对象index出现异常后，难以修复。实际上，仅仅是数据不一致（或类似的情况）导致的index异常，其实有其他的方式进行重建。实际上Oracle 11gR2版本中的如下脚本提供了相关的解决方案：$ORACLE_HOME/rdbms/admin/utlmmig.sql. 虽然该脚本的的解决方法是针对从10g升级到11gR2出现异常后的处理方式，然而该脚本中的内容，却值得我们深入研究。
几年前之前也写过一篇通过bbed来修复bootstrap 核心对象的例子：bootstrap$核心对象数据不一致导致ORA-08102
这里以上篇文章中提到的2个index 为例进行说明：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
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
|
1 2 3 4 5 6 7 8 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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. |
—处于性能考虑，更新表的统计信息
|
1 2 3 4 5 6 7 8 |
<pre class="brush:plain">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 进行交换
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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中的旧数据
|
1 2 3 4 5 6 7 8 9 10 11 |
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中
|
1 2 3 4 5 6 7 |
SQL> insert into bootstrap$mig select * from bootstrap$tmpstr; 4 rows created. SQL> commit; Commit complete. |
—-处理依赖关系和权限
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
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的效果一样。
|
1 2 3 4 |
BBED> p kcvfhrdb ub4 kcvfhrdb @96 0x00400208 BBED> |
—-重启数据库即可
步骤略.
Leave a Reply
You must be logged in to post a comment.