How to drop a Index with bbed?
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: How to drop a Index with bbed?
这是oracle恢复课程的一点内容，有朋友在问，所以就贴出来，其实没有什么，很简单，供大家参考！
在某些情况下，oracle的bootstrap$的一些对象出现异常之后，比如Index。我们无法进行rebuild，或许只能用一些极端的手段去将Index drop然后重建（当然还有其他的方法）。这里是利用bbed来drop 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 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
++++ 创建测试表 SQL> conn roger/roger Connected. SQL> create table test_0824 as select object_id,object_name from dba_objects where rownum < 500; Table created. SQL> create index idx_test_0824 on test_0824(object_id); Index created. SQL> SQL> set autot traceonly exp SQL> set lines 200 SQL> select count(object_id) from test_0824; Execution Plan ---------------------------------------------------------- Plan hash value: 3721076966 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | | 2 | INDEX FAST FULL SCAN| IDX_TEST_0824 | 499 | 6487 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement SQL> SQL> select object_id from dba_objects where object_name='IDX_TEST_0824'; OBJECT_ID ---------- 52969 SQL> select dbms_rowid.rowid_object(rowid) obj#, 2 dbms_rowid.rowid_relative_fno(rowid) rfile#, 3 dbms_rowid.rowid_block_number(rowid) block#, 4 dbms_rowid.rowid_row_number(rowid) row# 5 from sys.ind$ where obj#=52969; OBJ# RFILE# BLOCK# ROW# ---------- ---------- ---------- ---------- 2 1 21519 0 SQL> conn /as sysdba Connected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> |
可以看到，上面的SQL走了Index fast full scan，现在我们的目的就是把这个Index drop掉，让其走full table scan。
下面我们开始利用BBED来写进行修改，如下步骤：
|
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 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 |
+++++Session 2 BBED> set file 1 block 21519 FILE# 1 BLOCK# 21519 BBED> map File: /home/ora10g/oradata/aux/system01.dbf (1) Block: 21519 Dba:0x0040540f ------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes @0 struct ktbbh, 72 bytes @20 struct kdbh, 14 bytes @92 struct kdbt[6], 24 bytes @106 sb2 kdbr[6] @130 ub1 freespace[7236] @142 ub1 rowdata[810] @7378 ub4 tailchk @8188 BBED> p *kdbr[0] rowdata[384] ------------ ub1 rowdata[384] @7762 0xac BBED> x /rn rowdata[384] @7762 ------------ flag@7762: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK) lock@7763: 0x00 cols@7764: 1 kref@7765: 5 mref@7767: 5 hrid@7769:0x0040540f.0 nrid@7775:0x0040540f.0 col 0[4] @7781: 52968 BBED> p *kdbr[1] rowdata[256] ------------ ub1 rowdata[256] @7634 0x6c BBED> x /rn rowdata[256] @7634 ------------ flag@7634: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC) lock@7635: 0x00 cols@7636: 36 col 0[4] @7638: 52968 col 1[2] @7643: 6 col 2[2] @7646: 5 col 3[4] @7649: 12363 col 4[0] @7654: *NULL* col 5[0] @7655: *NULL* col 6[2] @7656: 2 col 7[0] @7659: *NULL* col 8[2] @7660: 10 col 9[2] @7663: 40 col 10[2] @7666: 1 col 11[3] @7669: 255 col 12[6] @7673: 1073741825 col 13[38] @7680: 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d col 14[0] @7719: *NULL* col 15[0] @7720: *NULL* col 16[0] @7721: *NULL* col 17[0] @7722: *NULL* col 18[0] @7723: *NULL* col 19[0] @7724: *NULL* col 20[0] @7725: *NULL* col 21[0] @7726: *NULL* col 22[0] @7727: *NULL* col 23[0] @7728: *NULL* col 24[0] @7729: *NULL* col 25[0] @7730: *NULL* col 26[2] @7731: 2 col 27[2] @7734: 2 col 28[6] @7737: 536870912 col 29[1] @7744: 0 col 30[3] @7746: 736 col 31[0] @7750: *NULL* col 32[0] @7751: *NULL* col 33[0] @7752: *NULL* col 34[0] @7753: *NULL* col 35[7] @7754: ######################################### BBED> p *kdbr[2] rowdata[0] ---------- ub1 rowdata[0] @7378 0x6c BBED> x /rn rowdata[0] @7378 ---------- flag@7378: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC) lock@7379: 0x02 cols@7380: 33 col 0[4] @7382: 52969 col 1[4] @7387: 52969 col 2[2] @7392: 6 col 3[2] @7395: 5 col 4[4] @7398: 12371 col 5[1] @7403: 0 col 6[2] @7405: 1 col 7[2] @7408: 10 col 8[2] @7411: 2 col 9[3] @7414: 255 col 10[0] @7418: *NULL* col 11[2] @7419: 1 col 12[2] @7422: 2 col 13[1] @7425: 0 col 14[2] @7427: 1 col 15[2] @7430: 2 col 16[3] @7433: 499 col 17[2] @7437: 1 col 18[2] @7440: 1 col 19[2] @7443: 2 col 20[7] @7446: ######################################### col 21[3] @7454: 499 col 22[3] @7458: 499 col 23[2] @7462: 1 col 24[0] @7465: *NULL* col 25[0] @7466: *NULL* col 26[0] @7467: *NULL* col 27[2] @7468: 1 col 28[0] @7471: *NULL* col 29[0] @7472: *NULL* col 30[0] @7473: *NULL* col 31[0] @7474: *NULL* col 32[7] @7475: ######################################### BBED> modify /x 04 offset 7767 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /home/ora10g/oradata/aux/system01.dbf (1) Block: 21519 Offsets: 7767 to 7770 Dba:0x0040540f ------------------------------------------------------------------------ 04000040 <32 bytes per line> BBED> p *kdbr[2] rowdata[0] ---------- ub1 rowdata[0] @7378 0x6c BBED> d /v offset 7378 count 4 File: /home/ora10g/oradata/aux/system01.dbf (1) Block: 21519 Offsets: 7378 to 7381 Dba:0x0040540f ------------------------------------------------------- 6c022100 l l.!. <16 bytes per line> BBED> modify /x 7c offset 7378 File: /home/ora10g/oradata/aux/system01.dbf (1) Block: 21519 Offsets: 7378 to 7381 Dba:0x0040540f ------------------------------------------------------------------------ 7c022100 <32 bytes per line> BBED> sum apply Check value for File 1, Block 21519: current = 0x6944, required = 0x6944 BBED> verify DBVERIFY - Verification starting FILE = /home/ora10g/oradata/aux/system01.dbf BLOCK = 21519 Block Checking: DBA = 4215823, Block Type = KTB-managed data block data header at 0xb7e9125c kdbchk: the amount of space used is not equal to block size used=357 fsc=0 avsp=7638 dtl=8096 Block 21519 failed with check code 6110 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 1 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 BBED> BBED> p kdbh struct kdbh, 14 bytes @92 ub1 kdbhflag @92 0x00 (NONE) b1 kdbhntab @93 6 b2 kdbhnrow @94 6 sb2 kdbhfrre @96 -1 sb2 kdbhfsbo @98 50 sb2 kdbhfseo @100 7286 b2 kdbhavsp @102 7638 b2 kdbhtosp @104 7638 ++++8096-357=7739（转为16进制为1E3B） BBED> d /v offset 102 File: /home/ora10g/oradata/aux/system01.dbf (1) Block: 21519 Offsets: 102 to 105 Dba:0x0040540f ------------------------------------------------------- d61dd61d l <16 bytes per line> BBED> modify /x 3b1e offset 102 File: /home/ora10g/oradata/aux/system01.dbf (1) Block: 21519 Offsets: 102 to 105 Dba:0x0040540f ------------------------------------------------------------------------ 3b1ed61d <32 bytes per line> BBED> modify /x 3b1e offset 104 File: /home/ora10g/oradata/aux/system01.dbf (1) Block: 21519 Offsets: 104 to 107 Dba:0x0040540f ------------------------------------------------------------------------ 3b1e0000 <32 bytes per line> BBED> sum apply Check value for File 1, Block 21519: current = 0x6944, required = 0x6944 BBED> verify DBVERIFY - Verification starting FILE = /home/ora10g/oradata/aux/system01.dbf BLOCK = 21519 Block Checking: DBA = 4215823, Block Type = KTB-managed data block data header at 0xb7e9125c kdbchk: space available on commit is incorrect tosp=7739 fsc=0 stb=4 avsp=7739 Block 21519 failed with check code 6111 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 1 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 |
修改之后发现还有点问题，说明还有一些地方没有修改对，下面继续：
|
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 |
BBED> d /v offset 62 count 10 File: /home/ora10g/oradata/aux/system01.dbf (1) Block: 21519 Offsets: 62 to 71 Dba:0x0040540f ------------------------------------------------------- 0a00a35a 00000100 0400 l ........ <16 bytes per line> BBED> modify /x 00 offset 62 File: /home/ora10g/oradata/aux/system01.dbf (1) Block: 21519 Offsets: 62 to 71 Dba:0x0040540f ------------------------------------------------------------------------ 0000a35a 00000100 0400 <32 bytes per line> BBED> sum apply Check value for File 1, Block 21519: current = 0x694e, required = 0x694e BBED> verify DBVERIFY - Verification starting FILE = /home/ora10g/oradata/aux/system01.dbf BLOCK = 21519 Block Checking: DBA = 4215823, Block Type = KTB-managed data block data header at 0xb7e9125c kdbchk: space available on commit is incorrect tosp=7739 fsc=0 stb=4 avsp=7739 Block 21519 failed with check code 6111 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 1 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 |
这里提示stb=4，那说明这个avsp 实际上应该是7739+4才对。
|
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 |
BBED> p kdbh struct kdbh, 14 bytes @92 ub1 kdbhflag @92 0x00 (NONE) b1 kdbhntab @93 6 b2 kdbhnrow @94 6 sb2 kdbhfrre @96 -1 sb2 kdbhfsbo @98 50 sb2 kdbhfseo @100 7286 b2 kdbhavsp @102 7739 b2 kdbhtosp @104 7739 BBED> BBED> d /v offset 104 File: /home/ora10g/oradata/aux/system01.dbf (1) Block: 21519 Offsets: 104 to 113 Dba:0x0040540f ------------------------------------------------------- 3b1e0000 01000100 0100 l ;......... <16 bytes per line> BBED> modify /x 3f1e offset 104 +++++offset向前推进4个offset，即7739+4（转为16进制为1E3F） File: /home/ora10g/oradata/aux/system01.dbf (1) Block: 21519 Offsets: 104 to 113 Dba:0x0040540f ------------------------------------------------------------------------ 3f1e0000 01000100 0100 <32 bytes per line> BBED> sum apply Check value for File 1, Block 21519: current = 0x694a, required = 0x694a BBED> verify DBVERIFY - Verification starting FILE = /home/ora10g/oradata/aux/system01.dbf BLOCK = 21519 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 BBED> |
ok，修改完毕之后，下面我们来启动数据库，观察一下效果.
|
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> startup ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1272600 bytes Variable Size 71304424 bytes Database Buffers 92274688 bytes Redo Buffers 2920448 bytes Database mounted. Database opened. SQL> conn roger/roger Connected. SQL> set lines 200 SQL> set autot traceonly exp SQL> select count(object_id) from test_0824; Execution Plan ---------------------------------------------------------- Plan hash value: 1820618955 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | | 2 | TABLE ACCESS FULL| TEST_0824 | 499 | 6487 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement SQL> |
ok,这个sql已经走全表扫描了，虽然是这样，但是如果你去重建该索引，可能会有问题，为什么呢 ？ 因为索引信息不一致了。
对于Index的信息会存放在数据字典表ind$中，而该字典表是一个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 33 34 35 36 37 38 39 40 41 42 43 44 |
++++Session1（维护数据字典） SQL> select file#,block# from ind$ where obj#=52969; FILE# BLOCK# ---------- ---------- 5 12371 SQL> delete from obj$ where obj#=52969; 1 row deleted. SQL> delete from seg$ where file#=5 and block#=12371; 1 row deleted. SQL> delete from icol$ where obj#=52969; 1 row deleted. SQL> commit; Commit complete. SQL> conn /as sysdba Connected. SQL> delete from ind$ where obj#=52969; delete from ind$ where obj#=52969 * ERROR at line 1: ORA-00600: internal error code, arguments: [13011], [2], [4215823], [0], [4215823], [3], [], [] ora-00600 [13011]错误，遇到过几次，很明显是index的信息不一致导致的。 SQL> select owner,index_name from dba_indexes where table_name='IND$'; OWNER INDEX_NAME ------------------------------ ------------------------------ SYS I_IND1 SQL> alter index sys.i_ind1 rebuild; alter index sys.i_ind1 rebuild * ERROR at line 1: ORA-00701: object necessary for warmstarting database cannot be altered |
我们发现bootstrap$对象中的index，是不能进行rebuild的，那么怎么办呢？ 下面我们利用bbed来手工将其中的键值删掉。
|
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 |
SQL> select object_id from dba_objects where object_name='I_IND1'; OBJECT_ID ---------- 39 SQL> SQL> oradebug setmypid Statement processed. SQL> alter session set events 'immediate trace name treedump level 39'; Session altered. SQL> oradebug close_trace Statement processed. SQL> oradebug tracefile_name /home/ora10g/admin/aux/udump/aux_ora_27390.trc SQL> select dump(52969,16) from dual; DUMP(52969,16) ----------------------- Typ=2 Len=4: c3,6,1e,46 ++++aux_ora_27390.trc ----- begin tree dump branch: 0x4000f2 4194546 (0: nrow: 5, level: 1) leaf: 0x4000f3 4194547 (-1: nrow: 574 rrow: 574) leaf: 0x4000f4 4194548 (0: nrow: 461 rrow: 460) leaf: 0x4000f5 4194549 (1: nrow: 525 rrow: 522) leaf: 0x4000f6 4194550 (2: nrow: 533 rrow: 533) leaf: 0x4000f7 4194551 (3: nrow: 166 rrow: 166) ----- end tree dump |
我们新建的索引肯定在最后一个block中。对于生产恢复而言，本身ind$记录不会太多，所以我们大不了一个一个index block去找，也没几个block。
|
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 |
BBED> set dba 0x4000f7 DBA 0x004000f7 (4194551 1,247) BBED> map File: /home/ora10g/oradata/aux/system01.dbf (1) Block: 247 Dba:0x004000f7 ------------------------------------------------------------ KTB Data Block (Index Leaf) struct kcbh, 20 bytes @0 struct ktbbh, 72 bytes @20 struct kdxle, 32 bytes @92 b2 kd_off[166] @124 ub1 freespace[5196] @456 ub1 rowdata[2468] @5652 ub4 tailchk @8188 BBED> find /x c3061e TOP File: /home/ora10g/oradata/aux/system01.dbf (1) Block: 247 Offsets: 5665 to 5674 Dba:0x004000f7 ------------------------------------------------------------------------ c3061e46 01000040 540f <32 bytes per line> BBED> set offset 5656 +++++前面搜索到的offset为5665,减去行头1个offset再减去8（因为system表空间是MSSM） OFFSET 5664 BBED> x /rn rowdata[12] @5664 ----------- flag@5664: 0x04 (NONE) lock@5665: 0xc3 keydata[6]: 0x06 0x1e 0x46 0x01 0x00 0x00 data key: col 0[64] @5673: 0x54 0x0f 0x00 0x00 0x04 0xc3 0x06 0x1e 0x3c 0x01 0x00 0x00 0x40 0x54 0x10 0x00 0x05 0x04 0xc3 0x06 0x1d 0x08 0x01 0x00 0x00 0x40 0x54 0x10 0x00 0x04 0x04 0xc3 0x06 0x1d 0x07 0x01 0x00 0x00 0x40 0x54 0x10 0x00 0x03 0x04 0xc3 0x06 0x1d 0x06 0x00 0x00 0x00 0x40 0x54 0x10 0x00 0x02 0x04 0xc3 0x06 0x1c 0x63 0x00 0x00 0x00 或者 SQL> alter session set tracefile_identifier='001'; Session altered. SQL> alter system dump datafile 1 block 247; System altered. SQL> oradebug tracefile_name /home/ora10g/admin/aux/udump/aux_ora_27390_001.trc SQL> ++aux_ora_27390_001.trc Block header dump: 0x004000f7 Object id on Block? Y seg/obj: 0x27 csc: 0x0a.5ab0 itc: 2 flg: O typ: 2 - INDEX fsl: 0 fnx: 0x4000f8 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0001.02f.000000ee 0x0080b30a.00a8.01 CB-- 0 scn 0x0000.00056955 0x02 0x0001.004.00000190 0x01c00473.01c7.11 --U- 1 fsc 0x0000.00005ab3 Leaf block dump =============== header address 218998876=0xd0da85c kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 1 kdxcosdc 1 kdxconro 166 kdxcofbo 368=0x170 kdxcofeo 5564=0x15bc kdxcoavs 5508 kdxlespl 0 kdxlende 0 kdxlenxt 0=0x0 kdxleprv 4194550=0x4000f6 kdxledsz 6 kdxlebksz 8032 row#0[8019] flag: ------, lock: 0, len=13, data:(6): 00 40 c8 d3 00 01 col 0; len 4; (4): c3 06 0e 0c ........ row#163[5642] flag: ------, lock: 0, len=13, data:(6): 00 40 54 10 00 01 col 0; len 4; (4): c3 06 1c 62 row#164[5629] flag: ------, lock: 0, len=13, data:(6): 00 40 54 10 00 02 col 0; len 4; (4): c3 06 1c 63 row#165[5564] flag: ------, lock: 2, len=13, data:(6): 00 40 54 0f 00 00 col 0; len 4; (4): c3 06 1e 46 ----- end of leaf block dump ----- |
对于index，我们如果需要从index block中将键值删掉，那么需要修改如下几个地方:
行头的lock标志位： 删掉一条记录，就加1，删除2条就加2.kdxlende: 这个之前，我们讲过，表示被删index entry的数量
由于system是MSSM方式，因此计算offset的公式应该是：bash =offset+68+(itl-1)*24
既然知道了这一点，那么我们就开始动手进行bbed修改了，如下：
|
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 |
BBED> set offset 5656 OFFSET 5656 BBED> x /rn rowdata[4] @5656 ---------- flag@5656: 0x00 (NONE) lock@5657: 0x02 keydata[6]: 0x00 0x40 0x54 0x0f 0x00 0x00 data key: col 0[4] @5665: 52969 BBED> modify /x 01 offset 5656 File: /home/ora10g/oradata/aux/system01.dbf (1) Block: 247 Offsets: 5656 to 5665 Dba:0x004000f7 ------------------------------------------------------------------------ 01020040 540f0000 04c3 <32 bytes per line> BBED> p kdxle struct kdxle, 32 bytes @92 struct kdxlexco, 16 bytes @92 ub1 kdxcolev @92 0x00 ub1 kdxcolok @93 0x00 ub1 kdxcoopc @94 0x80 ub1 kdxconco @95 0x01 ub4 kdxcosdc @96 0x00000001 sb2 kdxconro @100 166 b2 kdxcofbo @102 368 b2 kdxcofeo @104 5564 b2 kdxcoavs @106 5508 b2 kdxlespl @108 0 sb2 kdxlende @110 0 ub4 kdxlenxt @112 0x00000000 ub4 kdxleprv @116 0x004000f6 ub1 kdxledsz @120 0x06 ub1 kdxleunuse @121 0x11 BBED> modify /x 01 offset 110 File: /home/ora10g/oradata/aux/system01.dbf (1) Block: 247 Offsets: 110 to 119 Dba:0x004000f7 ------------------------------------------------------------------------ 01000000 0000f600 4000 <32 bytes per line> BBED> sum apply Check value for File 1, Block 247: current = 0xf017, required = 0xf017 BBED> verify DBVERIFY - Verification starting FILE = /home/ora10g/oradata/aux/system01.dbf BLOCK = 247 Block Checking: DBA = 4194551, Block Type = KTB-managed data block **** actual free space credit for itl 2 = 15 != # in trans. hdr = 0 ---- end index block validation Block 247 failed with check code 6401 ++++很明显，这里提示第2个ITL 有问题，需要修改一下fsc值。 实际上oracle这里是根据block内的可用空间来进行计算的，也可以尝试去把avsp改小。 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 0 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 1 Total Blocks Failing (Index): 1 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 BBED> BBED> p ktbbh struct ktbbh, 72 bytes @20 ub1 ktbbhtyp @20 0x02 (KDDBTINDEX) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x00000027 ub4 ktbbhod1 @24 0x00000027 struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x00005ab0 ub2 kscnwrp @32 0x000a b2 ktbbhict @36 7938 ub1 ktbbhflg @38 0x03 (KTBFONFL) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x004000f8 struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0001 ub2 kxidslt @46 0x002f ub4 kxidsqn @48 0x000000ee struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x0080b30a ub2 kubaseq @56 0x00a8 ub1 kubarec @58 0x01 ub2 ktbitflg @60 0xc000 (KTBFIBI, KTBFCOM) union _ktbitun, 2 bytes @62 b2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x00056955 struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x0001 ub2 kxidslt @70 0x0004 ub4 kxidsqn @72 0x00000190 struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x01c00473 ub2 kubaseq @80 0x01c7 ub1 kubarec @82 0x11 ub2 ktbitflg @84 0x2001 (KTBFUPB) union _ktbitun, 2 bytes @86 b2 _ktbitfsc @86 0 ub2 _ktbitwrp @86 0x0000 ub4 ktbitbas @88 0x00005ab3 BBED> d /v offset 86 count 2 File: /home/ora10g/oradata/aux/system01.dbf (1) Block: 247 Offsets: 86 to 87 Dba:0x004000f7 ------------------------------------------------------- 0000 l .. <16 bytes per line> BBED> modify /x 0f offset 86 File: /home/ora10g/oradata/aux/system01.dbf (1) Block: 247 Offsets: 86 to 87 Dba:0x004000f7 ------------------------------------------------------------------------ 0f00 <32 bytes per line> BBED> sum apply Check value for File 1, Block 247: current = 0xf018, required = 0xf018 BBED> verify DBVERIFY - Verification starting FILE = /home/ora10g/oradata/aux/system01.dbf BLOCK = 247 DBVERIFY - Verification complete Total Blocks Examined : 1 Total Blocks Processed (Data) : 0 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 1 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 BBED> |
ok，现在bbed校验已经不再提示错误，修改完成之后，我们来重建索引即可。
|
1 2 3 4 5 6 7 8 |
+++++Session 1 SQL> conn roger/roger Connected. SQL> create index idx_test_0824 on test_0824(object_id); Index created. SQL> |
这种测试意义不大，供大家参考！
Leave a Reply
You must be logged in to post a comment.