当前位置:Gxlcms > mysql > HowtodropaIndexwithbbed?

HowtodropaIndexwithbbed?

时间:2021-07-01 10:21:17 帮助过:24人阅读

本站文章除注明转载外,均为本站原创: 转载自love wife love life —Roger 的Oracle技术博客 本文链接地址: How to drop a Index with bbed? 这是oracle恢复课程的一点内容,有朋友在问,所以就贴出来,其实没有什么,很简单,供大家参考! 在某些情况下,o

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客

本文链接地址: How to drop a Index with bbed?

这是oracle恢复课程的一点内容,有朋友在问,所以就贴出来,其实没有什么,很简单,供大家参考!
在某些情况下,oracle的bootstrap$的一些对象出现异常之后,比如Index。我们无法进行rebuild,或许只能用一些极端的手段去将Index drop然后重建(当然还有其他的方法)。这里是利用bbed来drop Index的例子!

++++ 创建测试表
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来写进行修改,如下步骤:

+++++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

修改之后发现还有点问题,说明还有一些地方没有修改对,下面继续:

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才对。

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,修改完毕之后,下面我们来启动数据库,观察一下效果.

+++++启动数据库
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$的对象,因此我们还需要进行一些其他的操作。

++++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来手工将其中的键值删掉。

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。

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修改了,如下:

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校验已经不再提示错误,修改完成之后,我们来重建索引即可。

+++++Session 1
SQL> conn roger/roger
Connected.
SQL> create index idx_test_0824 on test_0824(object_id);
Index created.
SQL>

这种测试意义不大,供大家参考!

Related posts:

  1. 关于ora-1652的一点总结–续(详解rowid,index entry header)
  2. 手工构造逻辑坏块一例
  3. 存在datafile offline,如何进行异机恢复?
  4. 如何修复未格式化的坏块?
  5. Archivelog 模式下,datafile header损坏,如何恢复?
本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客 本文链接地址: How to drop a Index with bbed? 这是oracle恢复课程的一点内容,有朋友在问,所以就贴出来,其实没有什么,很简单,供大家参考! 在某些情况下,oracle的bootstrap$的一些对象出现异常之后,比如Index。我们无法进行rebuild,或许只能用一些极端的手段去将Index drop然后重建(当然还有其他的方法)。这里是利用bbed来drop Index的例子! ++++ 创建测试表 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 [...]

人气教程排行