首页 > 代码库 > 【Oracle】使用bbed恢复update的数据

【Oracle】使用bbed恢复update的数据

使用bbed不仅仅可以找回已经delete的数据还可以恢复update的数据,当然过程要比恢复delete的数据复杂一些。

实验过程如下:

SYS@ORCL>create table bbed_test(x int,y varchar2(20));

 

Table created.

 

SYS@ORCL>insert into bbed_test values(1,‘BADLY9‘);

 

1 row created.

 

SYS@ORCL>insert into bbed_test values(2,‘JP‘);

 

1 row created.

 

SYS@ORCL>commit;

 

Commit complete.

 

SYS@ORCL>select rowid, dbms_rowid.rowid_relative_fno(rowid)rel_fno,

  2  dbms_rowid.rowid_block_number(rowid)blockno,

  3  dbms_rowid.rowid_row_number(rowid) rowno

  4  from bbed_test;

 

ROWID                 REL_FNO    BLOCKNO      ROWNO

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

AAAM0WAABAAAOuCAAA          1      60290          0

AAAM0WAABAAAOuCAAB          1      60290          1

 

SYS@ORCL>update bbed_test set y=‘JP1‘ where x=1;

 

1 row updated.

 

SYS@ORCL>commit;

 

Commit complete.

 

SYS@ORCL>select dump(1,‘16‘) from dual;

 

DUMP(1,‘16‘)

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

Typ=2 Len=2: c1,2

 

SYS@ORCL>select dump(2,‘16‘) from dual;

 

DUMP(2,‘16‘)

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

Typ=2 Len=2: c1,3

 

SYS@ORCL>select dump(‘BADLY9‘,16) from dual;

 

DUMP(‘BADLY9‘,16)

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

Typ=96 Len=6: 42,41,44,4c,59,39

 

SYS@ORCL>select dump(‘JP1‘,‘16‘) from dual;

 

DUMP(‘JP1‘,‘16‘)

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

Typ=96 Len=3: 4a,50,31

 

SYS@ORCL>select * from bbed_test;

 

         X Y

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

         1 JP1

         2 JP

 

下面使用bbed进行查看

BBED> set dba 1,60290

        DBA             0x0040eb82 (4254594 1,60290)

 

BBED> map

 File: /u01/app/oracle/oradata/ORCL/system01.dbf (1)

 Block: 60290                                 Dba:0x0040eb82

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

 KTB Data Block (Table/Cluster)

 

 struct kcbh, 20 bytes                      @0       

 

 struct ktbbh, 72 bytes                     @20      

 

 struct kdbh, 14 bytes                      @92      

 

 struct kdbt[1], 4 bytes                    @106     

 

 sb2 kdbr[2]                                @110     

 

 ub1 freespace[8042]                        @114     

 

 ub1 rowdata[32]                            @8156    

 

 ub4 tailchk                                @8188    

 

 

BBED> p kdbr

sb2 kdbr[0]                                 @110      8064

sb2 kdbr[1]                                 @112      8074

 

BBED> p *kdbr[1]

rowdata[10]

-----------

ub1 rowdata[10]                             @8166     0x2c

8166-8074=92

BBED> x /rnc

rowdata[10]                                 @8166    

-----------

flag@8166: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8167: 0x00

cols@8168:    2

 

col    0[2] @8169: 2 

col    1[2] @8172: JP

 

 

BBED> p *kdbr[0]

rowdata[0]

----------

ub1 rowdata[0]                              @8156     0x2c

 

BBED> x /rnc

rowdata[0]                                  @8156    

----------

flag@8156: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8157: 0x02

cols@8158:    2

 

col    0[2] @8159: 1 

col    1[3] @8162: JP1

 

 

BBED> set count 64

        COUNT           64

 

BBED> d /v

 File: /u01/app/oracle/oradata/ORCL/system01.dbf (1)

 Block: 60290   Offsets: 8156 to 8191  Dba:0x0040eb82

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

 2c020202 c102034a 50312c00 0202c103 l ,...?..JP1,...?.

 024a502c 000202c1 02064241 444c5939 l .JP,...?..BADLY9

 0206d5a3                            l ..?

 

 <16 bytes per line>

 

使用bbed找回历史值

通过dump出来的值,推算出来第一条记录的起点02c1 02064241 444c5939

在这个值的基础上offset-3得到offset的值为8175

8175-92=8083 1f93

BBED> p kdbr

sb2 kdbr[0]                                 @110      8064

sb2 kdbr[1]                                 @112      8074

 

--修改row directory指针位置

BBED> m /x 931f      

 File: /u01/app/oracle/oradata/ORCL/system01.dbf (1)

 Block: 60290            Offsets:  110 to  173           Dba:0x0040eb82

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

 931f8a1f 02000000 02000200 791f321f 831e481e 00000000 00000000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 

 <32 bytes per line>

 

BBED> p kdbr

sb2 kdbr[0]                                 @110      8083

sb2 kdbr[1]                                 @112      8074

 

BBED> sum apply

Check value for File 1, Block 60290:

current = 0x3e20, required = 0x3e20

 

BBED> verify

DBVERIFY - Verification starting

FILE = /u01/app/oracle/oradata/ORCL/system01.dbf

BLOCK = 60290

 

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

data header at 0x79a25c

kdbchk: xaction header lock count mismatch

        trans=2 ilk=1 nlo=0      --提示事务错误

Block 60290 failed with check code 6108

 

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> p *kdbr[0]

rowdata[19]

-----------

ub1 rowdata[19]                             @8175     0x2c

 

BBED> d

 File: /u01/app/oracle/oradata/ORCL/system01.dbf (1)

 Block: 60290            Offsets: 8175 to 8191           Dba:0x0040eb82

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

 2c000202 c1020642 41444c59 390206d5 a3 

 

 <32 bytes per line>

 

BBED> x /rnc

rowdata[19]                                 @8175    

-----------

flag@8175: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8176: 0x00    --被更新前的记录事务锁标识为0,而更新后的事务锁标识为2

cols@8177:    2

 

col    0[2] @8178: 1 

col    1[6] @8181: BADLY9

 

--修改事务锁标识为2

BBED> m /x 02 offset +1

 File: /u01/app/oracle/oradata/ORCL/system01.dbf (1)

 Block: 60290            Offsets: 8176 to 8191           Dba:0x0040eb82

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

 020202c1 02064241 444c5939 0206d5a3 

 

 <32 bytes per line>

 

--把更新后值得事务锁标识改为0

BBED> set offset 8156             

        OFFSET          8156

 

BBED> m /x 00 offset +1

 File: /u01/app/oracle/oradata/ORCL/system01.dbf (1)

 Block: 60290            Offsets: 8157 to 8191           Dba:0x0040eb82

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

 000202c1 02034a50 312c0002 02c10302 4a502c02 0202c102 06424144 4c593902 

 06d5a3 

 

 <32 bytes per line>

 

BBED> sum apply

Check value for File 1, Block 60290:

current = 0x3c22, required = 0x3c22

 

BBED> verify

DBVERIFY - Verification starting

FILE = /u01/app/oracle/oradata/ORCL/system01.dbf

BLOCK = 60290

 

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

data header at 0x28225c

kdbchk: the amount of space used is not equal to block size

        used=44 fsc=3 avsp=8052 dtl=8096   --提示块的空间使用不正确

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

此时要将所有itlfsc全部改为0,然后将kdbh.kdbhavspkdbh.kdbhtosp的值改成相等,它们的值都等于dbv校验中显示的dtlused本次案例为8096-44 741f

 

 

BBED> p ktbbh

struct ktbbh, 72 bytes                      @20      

   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)

   union ktbbhsid, 4 bytes                  @24      

      ub4 ktbbhsg1                          @24       0x0000cd16

      ub4 ktbbhod1                          @24       0x0000cd16

   struct ktbbhcsc, 8 bytes                 @28      

      ub4 kscnbas                           @28       0x0007a3d3

      ub2 kscnwrp                           @32       0x0000

   b2 ktbbhict                              @36       7938

   ub1 ktbbhflg                             @38       0x03 (KTBFONFL)

   ub1 ktbbhfsl                             @39       0x00

   ub4 ktbbhfnx                             @40       0x00000000

   struct ktbbhitl[0], 24 bytes             @44      

      struct ktbitxid, 8 bytes              @44      

         ub2 kxidusn                        @44       0x0008

         ub2 kxidslt                        @46       0x0009

         ub4 kxidsqn                        @48       0x0000010e

      struct ktbituba, 8 bytes              @52      

         ub4 kubadba                        @52       0x00800270

         ub2 kubaseq                        @56       0x00c4

         ub1 kubarec                        @58       0x02

      ub2 ktbitflg                          @60       0x8000 (KTBFCOM)

      union _ktbitun, 2 bytes               @62      

         b2 _ktbitfsc                       @62       0

         ub2 _ktbitwrp                      @62       0x0000

      ub4 ktbitbas                          @64       0x0007a37e

   struct ktbbhitl[1], 24 bytes             @68      

      struct ktbitxid, 8 bytes              @68      

         ub2 kxidusn                        @68       0x0001

         ub2 kxidslt                        @70       0x0024

         ub4 kxidsqn                        @72       0x000000d5

      struct ktbituba, 8 bytes              @76      

         ub4 kubadba                        @76       0x008000d8

         ub2 kubaseq                        @80       0x0082

         ub1 kubarec                        @82       0x38

      ub2 ktbitflg                          @84       0x2001 (KTBFUPB)

      union _ktbitun, 2 bytes               @86      

         b2 _ktbitfsc                       @86       3

         ub2 _ktbitwrp                      @86       0x0003

      ub4 ktbitbas                          @88       0x0007a3d5

 

BBED> m /x 00 offset 86

 File: /u01/app/oracle/oradata/ORCL/system01.dbf (1)

 Block: 60290            Offsets:   86 to  149           Dba:0x0040eb82

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

 0000d5a3 07000001 0200ffff 1600801f 741f771f 00000200 931f8a1f 02000000 

 02000200 791f321f 831e481e 00000000 00000000 00000000 00000000 00000000 

 

 <32 bytes per line>

 

BBED> sum apply

Check value for File 1, Block 60290:

current = 0x3c21, required = 0x3c21

 

BBED> p kdbh

struct kdbh, 14 bytes                       @92      

   ub1 kdbhflag                             @92       0x00 (NONE)

   b1 kdbhntab                              @93       1

   b2 kdbhnrow                              @94       2

   sb2 kdbhfrre                             @96      -1

   sb2 kdbhfsbo                             @98       22

   sb2 kdbhfseo                             @100      8064

   b2 kdbhavsp                              @102      8052

   b2 kdbhtosp                              @104      8055

 

BBED> m /x 741f offset 102

 File: /u01/app/oracle/oradata/ORCL/system01.dbf (1)

 Block: 60290            Offsets:  102 to  165           Dba:0x0040eb82

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

 741f771f 00000200 931f8a1f 02000000 02000200 791f321f 831e481e 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 

 <32 bytes per line>

 

BBED> m /x 741f offset 104

 File: /u01/app/oracle/oradata/ORCL/system01.dbf (1)

 Block: 60290            Offsets:  104 to  167           Dba:0x0040eb82

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

 741f0000 0200931f 8a1f0200 00000200 0200791f 321f831e 481e0000 00000000 

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 

 <32 bytes per line>

 

BBED> sum apply

Check value for File 1, Block 60290:

current = 0x3c22, required = 0x3c22

 

BBED> verify

DBVERIFY - Verification starting

FILE = /u01/app/oracle/oradata/ORCL/system01.dbf

BLOCK = 60290

 

 

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

此时数据块验证通过

 

现在回到库里进行验证:

SYS@ORCL>alter system flush buffer_cache;

 

System altered.

 

SYS@ORCL>select * from bbed_test;

 

         X Y

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

         1 BADLY9

         2 JP

 

可以看到UPDATE之前的数据已经被恢复回来了。

 

实验过程参考飞总的文章:

http;//www.xifenfei.com/3704.html