A-A+

BBED恢复已删除的行记录

2013年07月21日 Backup&Recovery 暂无评论 阅读 1,782 次

参考文档:disassembling the oracle data block,writter by graham thornton 2005
操作系统信息

[oracle@rhl6 lib]$ uname -a
Linux rhl6.0x64 2.6.32-71.el6.x86_64 #1 SMP Wed Sep 1 01:33:01 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux

数据库版本

SQL> set line 400  
SQL> select * from gv$version;

   INST_ID BANNER
---------- --------------------------------------------------------------------------------
         1 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
         1 PL/SQL Release 11.2.0.3.0 - Production
         1 CORE 11.2.0.3.0      Production
         1 TNS for Linux: Version 11.2.0.3.0 - Production
         1 NLSRTL Version 11.2.0.3.0 - Production

测试数据

SQL> sho user
USER is "YALLONKING"
SQL> select rowid,t.* from yallonking t;

ROWID                      ID NAME                 MY_DATE
------------------ ---------- -------------------- -------------------
AAARcnAAFAAAACEAAA          1 yallonking           2013/07/21 11:12:03
AAARcnAAFAAAACEAAB          2 oraking              2013/07/21 11:12:09
AAARcnAAFAAAACEAAC          3 xiaolong             2013/07/21 11:12:17

删除第三行记录,更新第二行记录

SQL> delete from yallonking where id=3;

1 row deleted.

SQL> commit;

Commit complete.

SQL> update yallonking set name='YALLONKING' where id=2;

1 row updated.

SQL> commit;

Commit complete.

SQL> select rowid,t.* from yallonking t;

ROWID                      ID NAME                 MY_DATE
------------------ ---------- -------------------- -------------------
AAARcnAAFAAAACEAAA          1 yallonking           2013/07/21 11:12:03
AAARcnAAFAAAACEAAB          2 YALLONKING           2013/07/21 11:12:09

首先比对下这个表三行记录的row flag值
由于记录很少,所以表上的记录会在一个block上的不同offset

SQL> sho user
USER is "SYS"
SQL> select get_rowid('AAARcnAAFAAAACEAAA') from dual;

GET_ROWID('AAARCNAAFAAAACEAAA')
--------------------------------------------------------------------------------
Row_id type is  :1
Object# is      :71463
Relative_fno is :5
Block number is :132
Row number is   :0

转储指定块

SQL> alter session set tracefile_identifier='wyl';

Session altered.

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 5 block 132;

System altered.

查看转储文件中关于bitmask的值(--H-FL--)
关于bitmask中8位的说明如下图
bbed_bitmask

此处发现,更新操作,在bitmask中不会有标注。

[root@rhl6 trace]# pwd
/u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace
[root@rhl6 trace]# grep 'Block header dump' -A 100 ora11gr2_ora_11206_wyl.trc 
Block header dump:  0x01400084
 Object id on Block? Y
 seg/obj: 0x11727  csc: 0x00.ba7e9  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1400080 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0002.01d.000002b8  0x00c00715.00b7.14  --U-    1  fsc 0x0000.000bab1a
0x02   0x0005.012.000002b0  0x00c03464.0097.22  --U-    1  fsc 0x0015.000ba7eb
bdba: 0x01400084
data_block_dump,data header at 0x7f539b40ca64
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x7f539b40ca64
     76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f39
avsp=0x1f37
tosp=0x1f4e
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0x1f7f
0x14:pri[1]     offs=0x1f39
0x16:pri[2]     offs=0x1f52
block_row_dump:
tab 0, row 0, @0x1f7f
tl: 25 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 02
col  1: [10]  79 61 6c 6c 6f 6e 6b 69 6e 67
col  2: [ 7]  78 71 07 15 0c 0d 04
tab 0, row 1, @0x1f39
tl: 25 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 03
col  1: [10]  59 41 4c 4c 4f 4e 4b 49 4e 47
col  2: [ 7]  78 71 07 15 0c 0d 0a
tab 0, row 2, @0x1f52
tl: 2 fb: --HDFL-- lb: 0x2 
end_of_block_dump
End dump data blocks tsn: 5 file#: 5 minblk 132 maxblk 132

注:此处可以看到该块中有3行该表的记录,其中第三行(tab 0, row 2)已经被标记为删除(--HDFL--)

使用bbed进行修改

SQL> col file_name for a50
SQL> set line 400
SQL> select FILE_NAME,b.TABLESPACE_NAME from dba_data_files a,dba_tables b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and b.table_name='YALLONKING';

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/ora11gr2/users01.dbf       USERS

BBED> show all
        FILE#           5
        BLOCK#          1
        OFFSET          0
        DBA             0x01400001 (20971521 5,1)
        FILENAME        /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf
        BIFILE          bifile.bbd
        LISTFILE        /home/oracle/bbedfile.lst
        BLOCKSIZE       8192
        MODE            Edit
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No

BBED> set dba 5,132   
        DBA             0x01400084 (20971652 5,132)

BBED> find /c xiaolong
 File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5)
 Block: 132              Offsets: 8125 to 8191           Dba:0x01400084
------------------------------------------------------------------------
 7869616f 6c6f6e67 07787107 150c0d12 2c000302 c103076f 72616b69 6e670778 
 7107150c 0d0a2c00 0302c102 0a79616c 6c6f6e6b 696e6707 78710715 0c0d0402 
 061aab 

 <32 bytes per line>
 
BBED> find /c xiaolong TOP
 File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5)
 Block: 132              Offsets: 8125 to 8191           Dba:0x01400084
------------------------------------------------------------------------
 7869616f 6c6f6e67 07787107 150c0d12 2c000302 c103076f 72616b69 6e670778 
 7107150c 0d0a2c00 0302c102 0a79616c 6c6f6e6b 696e6707 78710715 0c0d0402 
 061aab 

 <32 bytes per line> 

注:top指定从offset 0 开始,curr指定从当前位置开始
此处发现字符xiaolong的offset是8125,我们需要从该位置向前找到行首位置并查看其bitmask的值,也说明我们要找的offset肯定在8125之前

BBED> map /v dba 5,132
 File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5)
 Block: 132                                   Dba:0x01400084
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0			<<块头结构       
    ub1 type_kcbh                           @0			<<块类型       
    ub1 frmt_kcbh                           @1			<<块格式(1为oracle 7,2为oracle 8+)       
    ub1 spare1_kcbh                         @2       	
    ub1 spare2_kcbh                         @3       	
    ub4 rdba_kcbh                           @4       	<<相关的数据块地址
    ub4 bas_kcbh                            @8       	<<SCN base
    ub2 wrp_kcbh                            @12      	<<SCN Wrap
    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      
    sb2 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     
    sb1 kdbhntab                            @101     
    sb2 kdbhnrow                            @102     
    sb2 kdbhfrre                            @104     
    sb2 kdbhfsbo                            @106     
    sb2 kdbhfseo                            @108     
    sb2 kdbhavsp                            @110     
    sb2 kdbhtosp                            @112     

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

 sb2 kdbr[3]                                @118			<<行目录     

 ub1 freespace[7969]                        @124			<<空闲空间     

 ub1 rowdata[95]                            @8093    		<<行数据

 ub4 tailchk                                @8188 

查看表中3行记录的bitmask值并记录他们的offset值

BBED> p *kdbr[0]			<<第一行记录
rowdata[70]
-----------
ub1 rowdata[70]                             @8163     0x2c

BBED> p *kdbr[1]			<<第二行记录
rowdata[0]
----------
ub1 rowdata[0]                              @8093     0x2c

BBED> p *kdbr[2]			<<第三行记录,此处的2可以从之前的trace中标记为'--HDFL--'处获得
rowdata[25]
-----------
ub1 rowdata[25]                             @8118     0x3c

注意:前2行记录都是0x2c(十进制的2*16+12=44= --H-FL-- =0+0+32+0+8+4+0+0=44)
第三行记录是0x3c(十进制的3*16+12=60= --HDFL-- =0+0+32+16+8+4+0+0=60)
所以,我们在这里只需要将第三行的bitmask从0x3c改为0x2c,也就是将之前的trace中的‘--HDFL--’ 改为 ‘--H-FL--’即可,也就是把60变为44即可。

上边我们已找到了需要修改的offset位置了,下边进行修改即可。

BBED> dump /v dba 5,132 offset 8118
 File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5)
 Block: 132     Offsets: 8118 to 8191  Dba:0x01400084
-------------------------------------------------------
 3c020302 c1040878 69616f6c 6f6e6707 l <...á..xiaolong.
 78710715 0c0d122c 000302c1 03076f72 l xq.....,...á..or
 616b696e 67077871 07150c0d 0a2c0003 l aking.xq.....,..
 02c1020a 79616c6c 6f6e6b69 6e670778 l .á..yallonking.x
 7107150c 0d040206 1aab              l q........?

 <16 bytes per line>

BBED> modify /x 2c dba 5,132 offset 8118    <</x是指修改类型为16进制
 File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5)
 Block: 132              Offsets: 8118 to 8191           Dba:0x01400084
------------------------------------------------------------------------
 2c020302 c1040878 69616f6c 6f6e6707 78710715 0c0d122c 000302c1 03076f72 
 616b696e 67077871 07150c0d 0a2c0003 02c1020a 79616c6c 6f6e6b69 6e670778 
 7107150c 0d040206 1aab 

 <32 bytes per line>

BBED> dump /v dba 5,132 offset 8118
 File: /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf (5)
 Block: 132     Offsets: 8118 to 8191  Dba:0x01400084
-------------------------------------------------------
 2c020302 c1040878 69616f6c 6f6e6707 l ,...á..xiaolong.
 78710715 0c0d122c 000302c1 03076f72 l xq.....,...á..or
 616b696e 67077871 07150c0d 0a2c0003 l aking.xq.....,..
 02c1020a 79616c6c 6f6e6b69 6e670778 l .á..yallonking.x
 7107150c 0d040206 1aab              l q........?

 <16 bytes per line>

注意:上边的修改中,我们可以看出已经将3c改为了2c

下边返回到数据库查看恢复的记录。

SQL> alter system flush BUFFER_CACHE;

System altered.

SQL> select rowid,t.* from yallonking t;

ROWID                      ID NAME                 MY_DATE
------------------ ---------- -------------------- -------------------
AAARcnAAFAAAACEAAA          1 yallonking           2013/07/21 11:12:03
AAARcnAAFAAAACEAAB          2 YALLONKING           2013/07/21 11:12:09
AAARcnAAFAAAACEAAC          3 xiaolong             2013/07/21 11:12:17

至此ok!

标签:

给我留言

Copyright © YallonKing 保留所有权利.   Theme  Ality

用户登录

分享到: