bbed修改块信息

最新推荐文章于 2025-03-18 11:45:39 发布

minebmw7

最新推荐文章于 2025-03-18 11:45:39 发布

阅读量237

点赞数 1

文章标签：程序人生

本文链接：https://blog.youkuaiyun.com/minebmw7/article/details/144084102

版权

使用 bbed 修改块内容

前期准备：

SQL> create table t_1 (a varchar2(20));

Table created.

SQL> insert into t_1 values ('oradb');

1 row created.

SQL> insert into t_1 values ('testdb');

1 row created.

SQL> commit;

Commit complete.

SQL> select header_file,header_block from dba_segments where segment_name = 'T_1';

HEADER_FILE HEADER_BLOCK
----------- ------------
   5       19

SQL> alter system flush buffer_cache;

System altered.

查看表段头块信息

SQL> select header_file,header_block from dba_segments where segment_name = 'T_1';

HEADER_FILE HEADER_BLOCK
----------- ------------
   5       19

SQL> alter system flush buffer_cache;

System altered.

使用 bbed 找到当前表的相关信息

## 问题 1：使用了错误的块

BBED> set file 5 block 20
 FILE#           5
 BLOCK#          20

BBED> map /v
 File: /u01/app/oracle/oradata/10gdb/test01.dbf (5)
 Block: 20                                    Dba:0x01400014
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0
    ub1 type_kcbh                           @0
    ub1 frmt_kcbh                           @1
    ub1 spare1_kcbh                         @2
    ub1 spare2_kcbh                         @3
    ub4 rdba_kcbh                           @4
    ub4 bas_kcbh                            @8
    ub2 wrp_kcbh                            @12
    ub1 seq_kcbh                            @14
    ub1 flg_kcbh                            @15
    ub2 chkval_kcbh                         @16
    ub2 spare3_kcbh                         @18

 struct ktbbh, 72 bytes                     @20
    ub1 ktbbhtyp                            @20
    union ktbbhsid, 4 bytes                 @24
    struct ktbbhcsc, 8 bytes                @28
    b2 ktbbhict                             @36
    ub1 ktbbhflg                            @38
    ub1 ktbbhfsl                            @39
    ub4 ktbbhfnx                            @40
    struct ktbbhitl[2], 48 bytes            @44

 struct kdbh, 14 bytes                      @100
    ub1 kdbhflag                            @100
    b1 kdbhntab                             @101
    b2 kdbhnrow                             @102
    sb2 kdbhfrre                            @104
    sb2 kdbhfsbo                            @106
    sb2 kdbhfseo                            @108
    b2 kdbhavsp                             @110
    b2 kdbhtosp                             @112

 struct kdbt[0], 0 bytes                    @114
    b2 kdbtoffs                             @114
    b2 kdbtnrow                             @116

 sb2 kdbr[0]                                @114     ----这里kdbr[0]说明找到的块有问题，如果里面有记录，对应的相关数字应该是与存放数据相匹配。

 ub1 freespace[8074]                        @114

 ub1 rowdata[0]                             @8188

 ub4 tailchk                                @8188


BBED> p *kdbr[1]
BBED-00401: out of range array index (1)


BBED> p *kdbr[0]
kdbh.kdbhflag
-------------
ub1 kdbhflag                                @100      0x00 (NONE)

解决办法：

通过 rowid 找到存放数据的块号

SQL> select distinct dbms_rowid.rowid_block_number(rowid) blk# from t_1;

      BLK#
----------
 22

重新 bbed 读取

BBED> set file 5 block 22
 FILE#           5
 BLOCK#          22

BBED> map /v
 File: /u01/app/oracle/oradata/11gdb/test01.dbf (5)
 Block: 22                                    Dba:0x01400016
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0
    ub1 type_kcbh                           @0
    ub1 frmt_kcbh                           @1
    ub1 spare1_kcbh                         @2
    ub1 spare2_kcbh                         @3
    ub4 rdba_kcbh                           @4
    ub4 bas_kcbh                            @8
    ub2 wrp_kcbh                            @12
    ub1 seq_kcbh                            @14
    ub1 flg_kcbh                            @15
    ub2 chkval_kcbh                         @16
    ub2 spare3_kcbh                         @18

 struct ktbbh, 72 bytes                     @20
    ub1 ktbbhtyp                            @20
    union ktbbhsid, 4 bytes                 @24
    struct ktbbhcsc, 8 bytes                @28
    b2 ktbbhict                             @36
    ub1 ktbbhflg                            @38
    ub1 ktbbhfsl                            @39
    ub4 ktbbhfnx                            @40
    struct ktbbhitl[2], 48 bytes            @44

 struct kdbh, 14 bytes                      @100
    ub1 kdbhflag                            @100
    b1 kdbhntab                             @101
    b2 kdbhnrow                             @102
    sb2 kdbhfrre                            @104
    sb2 kdbhfsbo                            @106
    sb2 kdbhfseo                            @108
    b2 kdbhavsp                             @110
    b2 kdbhtosp                             @112

 struct kdbt[1], 4 bytes                    @114
    b2 kdbtoffs                             @114
    b2 kdbtnrow                             @116

 sb2 kdbr[2]                                @118     --kdbr正常

 ub1 freespace[8047]                        @122

 ub1 rowdata[19]                            @8169

 ub4 tailchk                                @8188


BBED> p *kdbr[1]
rowdata[0]
----------
ub1 rowdata[0]                              @8169     0x2c

BBED> x /rcccccccc
rowdata[0]                                  @8169
----------
flag@8169: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8170: 0x01
cols@8171:    1

col    0[6] @8172: testdb



BBED> d /v offset 8172 count 20
 File: /u01/app/oracle/oradata/11gdb/test01.dbf (5)
 Block: 22      Offsets: 8172 to 8191  Dba:0x01400016
-------------------------------------------------------
 066b696c 6c64622c 01010572 6f676572 l .testdb,...oradb
 0406a93d                            l ..©=

 <16 bytes per line>

offset 8172 中包含了 col 长度信息，因此修改内容应从 8173 偏移量进行

BBED> modify /c google offset 8173
 File: /u01/app/oracle/oradata/11gdb/test01.dbf (5)
 Block: 22               Offsets: 8173 to 8191           Dba:0x01400016
------------------------------------------------------------------------
 676f6f67 6c652c01 0105726f 67657204 06a93d

 <32 bytes per line>

BBED> sum apply
Check value for File 5, Block 22:
current = 0x4da6, required = 0x4da6

BBED>

验证

SQL> alter system flush buffer_cache;

System altered.

SQL> select \* from t_1;

A\
\-----------------

oradb
google

恢复删除数据

前期准备：

SQL> create table t_dml (a number);

Table created.

SQL> insert into t_dml values (1);

1 row created.

SQL> insert into t_dml values (2);

1 row created.

SQL> c/2/3
1\* insert into t_dml values (3)
SQL> /

1 row created.

SQL> c/3/4
1\* insert into t_dml values (4)
SQL> /

1 row created.

SQL> c/4/5
1\* insert into t_dml values (5)
SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select \* from t_dml;

     A

---

     1
     2
     3
     4
     5

查看段头块

SQL> select header_file,header_block from dba_segments where segment_name = 'T_DML';

HEADER_FILE HEADER_BLOCK

---

      5       11

删除数据

SQL> delete from t_dml where a=1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> alter system flush buffer_cache;

System altered.

SQL> select distinct dbms_rowid.rowid_block_number(rowid) blk# from t_dml;

BLK#

---

14

SQL> select \* from t_dml;

A

---

2
3
4
5

清除buffer cache

SQL> alter system flush buffer_cache;

System altered.

SQL> select distinct dbms_rowid.rowid_block_number(rowid) blk# from t_dml;

      BLK#

---

    14

恢复 3c变2c

BBED> set file 5 block 14
FILE# 5
BLOCK# 14
BBED> map /v
File: /u01/app/oracle/oradata/11gdb/test01.dbf (5)
Block: 14 Dba:0x0140000e

---

KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes @0  
 ub1 type_kcbh @0  
 ub1 frmt_kcbh @1  
 ub1 spare1_kcbh @2  
 ub1 spare2_kcbh @3  
 ub4 rdba_kcbh @4  
 ub4 bas_kcbh @8  
 ub2 wrp_kcbh @12  
 ub1 seq_kcbh @14  
 ub1 flg_kcbh @15  
 ub2 chkval_kcbh @16  
 ub2 spare3_kcbh @18

struct ktbbh, 72 bytes @20  
 ub1 ktbbhtyp @20  
 union ktbbhsid, 4 bytes @24  
 struct ktbbhcsc, 8 bytes @28  
 b2 ktbbhict @36  
 ub1 ktbbhflg @38  
 ub1 ktbbhfsl @39  
 ub4 ktbbhfnx @40  
 struct ktbbhitl[2], 48 bytes @44

struct kdbh, 14 bytes @100  
 ub1 kdbhflag @100  
 b1 kdbhntab @101  
 b2 kdbhnrow @102  
 sb2 kdbhfrre @104  
 sb2 kdbhfsbo @106  
 sb2 kdbhfseo @108  
 b2 kdbhavsp @110  
 b2 kdbhtosp @112

struct kdbt[1], 4 bytes @114  
 b2 kdbtoffs @114  
 b2 kdbtnrow @116

sb2 kdbr[5] @118 ---kdbr 有数字说明正常，数字与行数对应

ub1 freespace[8030] @128

ub1 rowdata[30] @8158

ub4 tailchk @8188

BBED> p \*kdbr[0]
rowdata[24]

---

ub1 rowdata[24] @8182 0x3c ---标记为 3c 说明记录已被标记删除

BBED> x /rnnnnnnnn
rowdata[24] @8182

---

flag@8182: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH) ---标记为 3c 说明记录已被标记删除
lock@8183: 0x02
cols@8184: 0

BBED> d /v offset 8182 count 20
File: /u01/app/oracle/oradata/11gdb/test01.dbf (5)
Block: 14 Offsets: 8182 to 8191 Dba:0x0140000e

---

3c020102 c1020206 d055 l <...￮..ϕ

<16 bytes per line>

BBED> modify /x 2c offset 8182
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/app/oracle/oradata/11gdb/test01.dbf (5)
Block: 14 Offsets: 8182 to 8191 Dba:0x0140000e

---

2c020102 c1020206 d055

<32 bytes per line>

BBED> sum apply
Check value for File 5, Block 14:
current = 0x709c, required = 0x709c

BBED>

验证

SQL> alter system flush buffer_cache;

System altered.

SQL> select \* from t_dml;

     A

---

     1
     2
     3
     4
     5

SQL>

使用 copy 方式恢复坏块

现象

select count(\*) from t1;
ora08103 : object no longer exists

定位坏块问题

oradebug setmypid
alter session set db_file_multiblock_read_count=1;
alter session set events 'immediate trace name trace_buffer_on level 1048576';
alter session set events '10200 trace name context forever, level 1';
select /_+ full(a) _/ count(\*) from test.t1 a;
alter session set events '10200 trace name context off';
oradebug close_trace
oradebug tracefile_name

trace 文件中报错块信息为 block 958

create table t2 as select \* from t1 where rownum < 10;

select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from t2;
查出新建表的数据块相关信息 block 980

delete from t2;

commit;

使用 bbed 查询出坏块上面的相关信息

查看坏块临近块的信息

set file 5 block 959

rdba_kcbh
d /v offset 4 count 4
bf034001

ktbbhsid --segment id 和 object id 值
d /v offset 24 count 24
b9ca0000

将重建新表的数据块 copy 到问题块

copy file 5 block 980 to file 5 958
将 offset4 和 24 的值修改为紧邻块 859 的 offset4 和 24 值
set file 5 block 958

modify /x be034001 offset 4 ----这里的 be034001 值是通过 959 的 bf034001 换算而成
modify /x b9ca offset 24 ----这里是 segment id 或 object id 值，因此不变

sum apply

使用 verify 扫描坏块信息

verify

验证

alter system flush buffer_cache;

select count(\*) from t1;

如果是标记坏块

SQL> select count(_) from test;
select count(_) from test \*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 13)
ORA-01110: data file 5: '/data/CEBPM/datafile/test01.dbf'

通常 kcbh 中的 ub1 seq_kcbh @14 0xff 会标记为 oxff

修复：

modify /x 01 offset 14
modify /x 01 offset 8188

这里需要修改两个地方

跳过坏块读取其他数据

SQL> exec dbms_repair.skip_corrupt_blocks('TEST','TEST');
PL/SQL procedure successfully completed.

本文由 mdnice 多平台发布