利用bbed找回ORACLE更新前值

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:利用bbed找回ORACLE更新前值

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

模拟数据块更新

SQL> create table t_xifenfei(id number,name varchar2(10));

Table created.

SQL> insert into t_xifenfei values(1,'XFF');

1 row created.

SQL> insert into t_xifenfei values(2,'CHF');

1 row created.

SQL> commit; 

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> select id,rowid,
  2  dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3  dbms_rowid.rowid_block_number(rowid)blockno,
  4  dbms_rowid.rowid_row_number(rowid) rowno
  5  from t_xifenfei;

        ID ROWID                 REL_FNO    BLOCKNO      ROWNO
---------- ------------------ ---------- ---------- ----------
         1 AAASc+AAEAAAACvAAA          4        175          0
         2 AAASc+AAEAAAACvAAB          4        175          1

SQL> select dump(1,'16') from dual;

DUMP(1,'16')
-----------------
Typ=2 Len=2: c1,2

SQL> select dump(2,'16') from dual;

DUMP(2,'16')
-----------------
Typ=2 Len=2: c1,3

SQL> select dump('XFF','16') FROM DUAL;

DUMP('XFF','16')
----------------------
Typ=96 Len=3: 58,46,46

SQL> SELECT DUMP('CHF','16') FROM DUAL;

DUMP('CHF','16')
----------------------
Typ=96 Len=3: 43,48,46

SQL> update t_xifenfei set name='XIFENFEI' where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select dump('XIFENFEI','16') from dual;

DUMP('XIFENFEI','16')
-------------------------------------
Typ=96 Len=8: 58,49,46,45,4e,46,45,49

SQL> alter system checkpoint;

System altered.

SQL> select * from t_xifenfei;

        ID NAME
---------- ----------
         1 XIFENFEI
         2 CHF

这里我们对数据库进行了一次更新操作,并且dump出来对应值,为了方便定位到相应记录

bbed查看相关值

[oracle@xifenfei ~]$ bbed listfile=listfile mode=edit password=blockedit

BBED: Release 2.0.0.0.0 - Limited Production on Wed Aug 8 20:50:47 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set file 4 block 175
        FILE#           4
        BLOCK#          175

BBED> map
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175                                   Dba:0x010000af
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 72 bytes                     @20      

 struct kdbh, 14 bytes                      @100     

 struct kdbt[1], 4 bytes                    @114     

 sb2 kdbr[2]                                @118     

 ub1 freespace[8031]                        @122     

 ub1 rowdata[35]                            @8153    

 ub4 tailchk                                @8188 

BBED> p kdbr
sb2 kdbr[0]                                 @118      8053
sb2 kdbr[1]                                 @120      8068

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

BBED> x /rnc
rowdata[15]                                 @8168    
-----------
flag@8168: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8169: 0x00
cols@8170:    2

col    0[2] @8171: 2 
col    1[3] @8174: CHF

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

BBED> x /rnc
rowdata[0]                                  @8153    
----------
flag@8153: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8154: 0x02
cols@8155:    2

col    0[2] @8156: 1 
col    1[8] @8159: XIFENFEI

BBED> set count 64
        COUNT           64

 <32 bytes per line>

BBED> d /v
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175     Offsets: 8153 to 8191  Dba:0x010000af
-------------------------------------------------------
 2c020202 c1020858 4946454e 4645492c l ,......XIFENFEI,
 000202c1 03034348 462c0002 02c10203 l ......CHF,......
 58464602 068de8                     l XFF....

 <16 bytes per line>

使用bbed找回历史值

--准备工作,通过dump出来的值,推算出来第一条记录的起点02c10203584646,
--在这个值的基础上offset-3得到offset值为8078

BBED> p kdbr
sb2 kdbr[0]                                 @118      8053
sb2 kdbr[1]                                 @120      8068

--修改row directory指针位置
BBED> m /x 8e1f
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets:  118 to  181           Dba:0x010000af
------------------------------------------------------------------------
 8e1f841f 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> p kdbr
sb2 kdbr[0]                                 @118      8078
sb2 kdbr[1]                                 @120      8068

BBED> sum apply
Check value for File 4, Block 175:
current = 0xdff8, required = 0xdff8

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 175

Block Checking: DBA = 16777391, Block Type = KTB-managed data block
data header at 0xb53cd264
kdbchk: xaction header lock count mismatch
        trans=2 ilk=1 nlo=0     --提示事务锁错误
Block 175 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
Message 531 not found;  product=RDBMS; facility=BBED

BBED> p *kdbr[0] 
rowdata[25]
-----------
ub1 rowdata[25]                             @8178     0x2c

BBED> d
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets: 8178 to 8191           Dba:0x010000af
------------------------------------------------------------------------
 2c000202 c1020358 46460206 8de8 

 <32 bytes per line>

BBED> x /rnc
rowdata[25]                                 @8178    
-----------
flag@8178: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8179: 0x00  --被更新前的记录事务锁标识为0,而更新后的事务锁标识为2
cols@8180:    2

col    0[2] @8181: 1 
col    1[3] @8184: XFF

--修改事务锁标识为2
BBED> m /x 02 offset 8179
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets: 8179 to 8191           Dba:0x010000af
------------------------------------------------------------------------
 020202c1 02035846 4602068d e8 

 <32 bytes per line>

BBED> set offset 8153
        OFFSET          8153

BBED> d
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets: 8153 to 8191           Dba:0x010000af
------------------------------------------------------------------------
 2c020202 c1020858 4946454e 4645492c 000202c1 03034348 462c0202 02c10203 
 58464602 068de8 

 <32 bytes per line>

--把更新后值的事务锁标识改为0
BBED> m /x 00 offset +1
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets: 8154 to 8191           Dba:0x010000af
------------------------------------------------------------------------
 000202c1 02085849 46454e46 45492c00 0202c103 03434846 2c020202 c1020358 
 46460206 8de8 

 <32 bytes per line>

BBED> sum apply
Check value for File 4, Block 175:
current = 0xddfa, required = 0xddfa

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 175

Block Checking: DBA = 16777391, Block Type = KTB-managed data block
data header at 0xb53cd264
kdbchk: the amount of space used is not equal to block size
        used=42 fsc=0 avsp=8041 dtl=8088   -->提示块的空间使用不正确
Block 175 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
Message 531 not found;  product=RDBMS; facility=BBED

BBED> p ktbbh
struct ktbbh, 72 bytes                      @20      
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24      
      ub4 ktbbhsg1                          @24       0x0001273e
      ub4 ktbbhod1                          @24       0x0001273e
   struct ktbbhcsc, 8 bytes                 @28      
      ub4 kscnbas                           @28       0x0000e88a
      ub2 kscnwrp                           @32       0x0002
   sb2 ktbbhict                             @36       2
   ub1 ktbbhflg                             @38       0x32 (NONE)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x010000a8
   struct ktbbhitl[0], 24 bytes             @44      
      struct ktbitxid, 8 bytes              @44      
         ub2 kxidusn                        @44       0x0006
         ub2 kxidslt                        @46       0x001e
         ub4 kxidsqn                        @48       0x000002c6
      struct ktbituba, 8 bytes              @52      
         ub4 kubadba                        @52       0x00c000d9
         ub2 kubaseq                        @56       0x0086
         ub1 kubarec                        @58       0x2a
      ub2 ktbitflg                          @60       0x8000 (KTBFCOM)
      union _ktbitun, 2 bytes               @62      
         sb2 _ktbitfsc                      @62       2   
         ub2 _ktbitwrp                      @62       0x0002
      ub4 ktbitbas                          @64       0x0000e550
   struct ktbbhitl[1], 24 bytes             @68      
      struct ktbitxid, 8 bytes              @68      
         ub2 kxidusn                        @68       0x0006
         ub2 kxidslt                        @70       0x0008
         ub4 kxidsqn                        @72       0x000002c7
      struct ktbituba, 8 bytes              @76      
         ub4 kubadba                        @76       0x00c000da
         ub2 kubaseq                        @80       0x0086
         ub1 kubarec                        @82       0x12
      ub2 ktbitflg                          @84       0x2001 (KTBFUPB)
      union _ktbitun, 2 bytes               @86      
         sb2 _ktbitfsc                      @86       0
         ub2 _ktbitwrp                      @86       0x0000
      ub4 ktbitbas                          @88       0x0000e88d

--所有的_ktbitfsc修改为0
BBED> m /x 00 offset 62
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets:   62 to  125           Dba:0x010000af
------------------------------------------------------------------------
 000050e5 00000600 0800c702 0000da00 c0008600 12000120 00008de8 00000000 
 00000000 00000001 0200ffff 1600751f 691f691f 00000200 8e1f841f 00000000 

 <32 bytes per line>

BBED> sum apply
Check value for File 4, Block 175:
current = 0xddf8, required = 0xddf8

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 175

Block Checking: DBA = 16777391, Block Type = KTB-managed data block
data header at 0xb53cd264
kdbchk: the amount of space used is not equal to block size
        used=42 fsc=0 avsp=8041 dtl=8088
Block 175 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
Message 531 not found;  product=RDBMS; facility=BBED

BBED> p kdbh
struct kdbh, 14 bytes                       @100     
   ub1 kdbhflag                             @100      0x00 (NONE)
   sb1 kdbhntab                             @101      1
   sb2 kdbhnrow                             @102      2
   sb2 kdbhfrre                             @104     -1
   sb2 kdbhfsbo                             @106      22
   sb2 kdbhfseo                             @108      8053
   sb2 kdbhavsp                             @110      8045
   sb2 kdbhtosp                             @112      8045

--修改kdbhtosp和kdbhavsp值
BBED>  m /x 6e1f  offset 112
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets:  112 to  175           Dba:0x010000af
------------------------------------------------------------------------
 6e1f0000 02008e1f 841f0000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> m /x  6e1f  offset 110
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets:  110 to  173           Dba:0x010000af
------------------------------------------------------------------------
 6e1f6e1f 00000200 8e1f841f 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> sum apply
Check value for File 4, Block 175:
current = 0xddf8, required = 0xddf8

--数据块验证通过
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 175


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
Message 531 not found;  product=RDBMS; facility=BBED

重启数据库

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1344652 bytes
Variable Size             251661172 bytes
Database Buffers           54525952 bytes
Redo Buffers                6328320 bytes
Database mounted.
Database opened.

--找回更新前值
SQL> select * from chf.t_xifenfei;

        ID NAME
---------- ----------
         1 XFF
         2 CHF
此条目发表在 Oracle 分类目录,贴了 标签。将固定链接加入收藏夹。

利用bbed找回ORACLE更新前值》有 1 条评论

  1. 冷泉 说:

    飞总,好文章,精彩!