使用BBED手工提交事务

测试数据准备：

SQL> create user sunhailong identifiedby abcd;

 

User created.

 

SQL> alter user sunhailong defaulttablespace users;

 

User altered.

 

SQL> grant dba to sunhailong;

 

Grant succeeded.

 

SQL> conn sunhailong

Enter password:

Connected.

SQL>

SQL>

SQL> drop table t_trans purge;

 

Table dropped.

SQL> create table tt_trans as select *from dba_objects where rownum<400;

 

Table created.

 

SQL> select count(*) from tt_trans;

 

 COUNT(*)

----------

      399

 

SQL> select distinctdbms_rowid.rowid_block_number(rowid) blk# from tt_trans;

 

     BLK#

----------

       70

       72

       68

       69

       71

 

SQL> COL OBJECT_NAME FOR A50

SQL> SET LINES 200 PAGES 999           

select dbms_rowid.ROWID_BLOCK_NUMBER(rowid)BLK,

dbms_rowid.ROWID_RELATIVE_FNO(rowid) FNO,

dbms_rowid.ROWID_ROW_NUMBER(rowid) RNO,

object_id,

object_name

--dump(object_name)

from tt_trans

where object_id=22;

 

      BLK        FNO        RNO OBJECT_ID OBJECT_NAME

---------- ---------- ---------- ------------------------------------------------------------

       68          4         40         22 USER$

 

SQL> delete from tt_trans whereobject_id=22;

 

1 row deleted.

这里不提交

 

 

alter system flush buffer_cache;

 

 

 

BBED> set dba 4,68

       DBA             0x0100003f(16777279 4,63)

 

--看ITL       

BBED> p ktbbhitl

struct ktbbhitl[0], 24 bytes                @44     

  struct ktbitxid, 8 bytes                @44     

     ub2 kxidusn                          @44       0xffff

     ub2 kxidslt                          @46       0x0000

     ub4 kxidsqn                          @48       0x00000000

  struct ktbituba, 8 bytes                @52     

     ub4 kubadba                           @52       0x00000000

     ub2 kubaseq                          @56       0x0000

     ub1 kubarec                          @58       0x00

  ub2 ktbitflg                            @60       0x8000 (KTBFCOM)

  union _ktbitun, 2 bytes                  @62     

     b2 _ktbitfsc                         @62       0

     ub2 _ktbitwrp                        @62       0x0000

  ub4 ktbitbas                            @64       0x0309e7c2

struct ktbbhitl[1], 24 bytes                @68      

  struct ktbitxid, 8 bytes                @68     

     ub2 kxidusn                          @68       0x0009

     ub2 kxidslt                          @70       0x0008

     ub4 kxidsqn                          @72       0x00000139

  struct ktbituba, 8 bytes                @76     

     ub4 kubadba                          @76       0x00800062

     ub2 kubaseq                          @80       0x00d3

     ub1 kubarec                          @82       0x08

  ub2 ktbitflg                             @84       0x0001 (NONE)

  union _ktbitun, 2 bytes                 @86     

     b2 _ktbitfsc                         @86       72

     ub2 _ktbitwrp                        @86       0x0048

  ub4 ktbitbas                            @88       0x00000000

struct ktbbhitl[2], 24 bytes                @92     

  struct ktbitxid, 8 bytes                @92     

     ub2 kxidusn                          @92       0x0000

     ub2 kxidslt                          @94       0x0000

     ub4 kxidsqn                          @96       0x00000000

  struct ktbituba, 8 bytes                @100    

     ub4 kubadba                          @100      0x00000000

     ub2 kubaseq                          @104      0x0000

     ub1 kubarec                          @106      0x00

  ub2 ktbitflg                            @108      0x0000 (NONE)

  union _ktbitun, 2 bytes                 @110    

     b2 _ktbitfsc                         @110      0

     ub2 _ktbitwrp                         @110      0x0000

  ub4 ktbitbas                            @112      0x00000000

 

BBED> modify /x 0080 offset 84

 File: /data/orcl/orcl/users01.dbf (4)

 Block: 68               Offsets:   84 to 8191           Dba:0x01000044

------------------------------------------------------------------------

 00804800 00000000 00000000 00000000 0000000000000000 00000000 00000000

 

BBED> dump offset 86 count 8

 File: /data/orcl/orcl/users01.dbf (4)

 Block: 68               Offsets:   86 to  93           Dba:0x01000044

------------------------------------------------------------------------

 48000000 00000000

 

 <32 bytes per line>

 

BBED> modify /x 0000 offset 86

 File: /data/orcl/orcl/users01.dbf (4)

 Block: 68               Offsets:   86 to  93           Dba:0x01000044

------------------------------------------------------------------------

 00000000 00000000

 

 <32 bytes per line>

 

BBED> sum apply

Check value for File 4, Block 68:

current = 0xfb97, required = 0xfb97

 

BBED> verify

DBVERIFY - Verification starting

FILE = /data/orcl/orcl/users01.dbf

BLOCK = 68

 

Block Checking: DBA = 16777284, Block Type= KTB-managed data block

data header at 0x2af82f9c327c

kdbchk: row locked by non-existenttransaction             ---事务不一致，原因是行头的锁标记没有清理

       table=0   slot=40

       lockid=2   ktbbhitc=3

Block 68 failed with check code 6101

 

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

 

 

 

根据dump找到行号：

tab 0, row 40, @0x134c

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 41, @0x1301

tl: 75 fb: --H-FL-- lb: 0x0  cc: 13

col 0: [ 3]  53 59 53

col 1: [ 6]  49 5f 43 4f 4e 32

col 2: *NULL*

col 3: [ 2]  c1 32

col 4: [ 2]  c1 32

col 5: [ 5]  49 4e 44 45 58

col 6: [ 7]  78 6c 03 0c 01 28 31

col 7: [ 7]  78 6c 03 0c 01 28 31

col 8: [19]  32 30 30 38 2d 30 33 2d31 32 3a 30 30 3a 33 39 3a 34 38

col 9: [ 5]  56 41 4c 49 44

col 10: [ 1]  4e

col 11: [ 1]  4e

 

 

 

清理锁标记：

BBED> p *kdbr[40]

rowdata[3866]

-------------

ub1 rowdata[3866]                           @5064     0x3c

 

BBED> x /rccccccccccc

rowdata[3866]                               @5064   

-------------

flag@5064: 0x3c (KDRHFL, KDRHFF, KDRHFD,KDRHFH)

lock@5065: 0x02

cols@5066:    0

BBED> modify /x 3c00 offset 5064

 File: /data/orcl/orcl/users01.dbf (4)

 Block: 68               Offsets: 5064 to 5071           Dba:0x01000044

------------------------------------------------------------------------

 3c000d03 53595305

 

 <32 bytes per line>

 

BBED> sum apply

Check value for File 4, Block 68:

current = 0xf997, required = 0xf997

 

BBED> verify

DBVERIFY - Verification starting

FILE = /data/orcl/orcl/users01.dbf

BLOCK = 68

 

Block Checking: DBA = 16777284, Block Type= KTB-managed data block

data header at 0x1070fa7c

kdbchk: the amount of space used is notequal to block size

       used=7118 fsc=0 avsp=874 dtl=8064                             ---看报错变为kdbchk:the amount of space used is not equal to block size

Block 68 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

 

7118+874=7992  < dtl 8064

avsp=8064-7118=946

 

 

BBED> p kdbhavsp

b2 kdbhavsp                                 @134      874

 

BBED> p kdbhtosp

b2 kdbhtosp                                 @136      948

 

小技巧这里就不用计算了，根据kdbhtosp的16进制改就可以了。

 

BBED> dump offset 134 count 8

 File: /data/orcl/orcl/users01.dbf (4)

 Block: 68               Offsets:  134 to 141           Dba:0x01000044

------------------------------------------------------------------------

 6a03b403 00005b00

 

kdbhtosp的值为b403   字节序反转后-2，为03b2，再反转回去b203

 

BBED> modify /x b203 offset 134

 File: /data/orcl/orcl/users01.dbf (4)

 Block: 68               Offsets:  134 to 141           Dba:0x01000044

------------------------------------------------------------------------

 b203b403 00005b00

 

 <32 bytes per line>

 

BBED> p kdbhavsp

b2 kdbhavsp                                 @134      946

 

BBED> p kdbhtosp

b2 kdbhtosp                                 @136      948

 

BBED> sum apply

Check value for File 4, Block 68:

current = 0xf94f, required = 0xf94f

 

BBED> verify

DBVERIFY - Verification starting

FILE = /data/orcl/orcl/users01.dbf

BLOCK = 68

 

 

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

 

修改完毕。

 

总结：

手工提交未提交事务需要修改4个地方

1.数据块对应事务ITL槽的ktbitflg

2.清空数据块对应事务ITL槽的_ktbitwrp

3.清理行头锁标记为00

4.重新计算kdbhavsp值，并依据kdbhtosp值进行修改（其实kdbhtosp值也可以改为与kdbhavsp一致，如果不改在这里也是可以校验通过的）