签到成功

知道了

CNDBA社区CNDBA社区

BBED 使用样例2--恢复delete数据

2018-03-13 17:30 2305 0 原创 BBED
作者： Marvinn
原文链接：http://www.cndba.cn/Marvinn
本文链接：http://www.cndba.cn/Marvinn/article/2679

创建测试数据

http://www.cndba.cn/Marvinn/article/2679
http://www.cndba.cn/Marvinn/article/2679

17:15:33 SYS@orcl>alter user scott default tablespace xtts;
User altered.

17:16:12 SYS@orcl>conn scott/tiger@orclpdb;
Connected.

17:16:22 SCOTT@orclpdb>create table jwt(id number,name varchar2(10));
Table created.

17:20:04 SCOTT@orclpdb>insert into jwt values(1,'ss');

1 row created.

Elapsed: 00:00:01.03
17:20:18 SCOTT@orclpdb>insert into jwt values(2,'mm');

1 row created.

Elapsed: 00:00:00.00
17:20:29 SCOTT@orclpdb>insert into jwt values(3,'zz');

1 row created.
17:20:36 SCOTT@orclpdb>commit;

Commit complete.

17:20:39 SCOTT@orclpdb>insert into jwt values(4,'aa');

1 row created.

Elapsed: 00:00:00.00
17:22:26 SCOTT@orclpdb>insert into jwt values(5,'bb');

1 row created.

Elapsed: 00:00:00.00
17:22:36 SCOTT@orclpdb>insert into jwt values(6,'dd');

1 row created.

Elapsed: 00:00:00.00
17:22:43 SCOTT@orclpdb>commit;

Commit complete.

​ 注意：使用BBED工具对块进行操作时，需关库或者设置对应的表空间为只读再操做数据块，作防止BBED修改的块被覆盖，造成不必要的错误或者失败http://www.cndba.cn/Marvinn/article/2679

​ 由于当前处于12C测试环境，所以只需要将对应数据文件所在的PDB数据库关闭(mount状态即可)或者设置表空间只读即可，而其他版本的数据库需关库或者设置表空间只读操作http://www.cndba.cn/Marvinn/article/2679

查看表数据
17:47:08 SCOTT@orclpdb>select * from jwt;

        ID NAME
---------- ----------
         1 ss
         2 mm
         3 zz
         4 aa
         5 bb
         6 dd

6 rows selected.

Elapsed: 00:00:00.12

查看表所在数据文件以及块号、行号
17:50:13 SCOTT@orclpdb>select dbms_rowid.rowid_relative_fno(rowid) rfn,dbms_rowid.rowid_block_number(rowid) bln,dbms_rowid.rowid_row_number(rowid) rn from JWT;

       RFN        BLN         RN
---------- ---------- ----------
        28        134          0
        28        134          1
        28        134          2
        28        134          3
        28        134          4
        28        134          5

6 rows selected.

查看数据文件号为28的数据文件
17:51:17 SCOTT@orclpdb>select file#,name from v$datafile where file#=28;

     FILE#
----------
NAME
--------------------------------------------------------------------------------
        28
/data/orcl/orclpdb/XTTS.dbf
Elapsed: 00:00:00.15

Example #1 恢复Delete删除的数据http://www.cndba.cn/Marvinn/article/2679

​ 删除情形：1、指定删除某行数据

http://www.cndba.cn/Marvinn/article/2679

​ 2、删除大部分数据（如rownum<=3等)

http://www.cndba.cn/Marvinn/article/2679
http://www.cndba.cn/Marvinn/article/2679
http://www.cndba.cn/Marvinn/article/2679http://www.cndba.cn/Marvinn/article/2679

11:14:42 SCOTT@orclpdb>select * from jwt;

        ID NAME
---------- ----------
         1 ss
         2 nn
         3 zz
         4 aa
         5 bb
         6 dd

6 rows selected.


--删除前两行数据 (模拟删除大部分数据)
11:17:21 SCOTT@orclpdb>delete from jwt where rownum<=2;

2 rows deleted.

Elapsed: 00:00:00.04
11:17:37 SCOTT@orclpdb>commit;

Commit complete.

Elapsed: 00:00:00.00
--设置只读
11:17:39 SCOTT@orclpdb>alter tablespace xtts read only;

Tablespace altered.


BBED 工具恢复已删除数据...
BBED> set filename '/data/orcl/orclpdb/XTTS.dbf'
        FILENAME        /data/orcl/orclpdb/XTTS.dbf

BBED> set block 134 offset 0
        BLOCK#          134
        OFFSET          0

接下来去数据区行目录中查看标记flag是否为0x3c 是表示被删除的行 0x02正常行
BBED> map /v
 File: /data/orcl/orclpdb/XTTS.dbf (0)
 Block: 134                                   Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       
    ub1 type_kcbh                           @0       
    ub1 frmt_kcbh                           @1       
    ub2 wrp2_kcbh                           @2       
    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      
    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[6]                                @118     

 ub1 freespace[8004]                        @130     

 ub1 rowdata[54]                            @8134    

 ub4 tailchk                                @8188   

--查看数据行数(此时数据行仍然是6行，说明空间未被分配重用)
BBED> p kdbhnrow
sb2 kdbhnrow                                @102      6

--查看前两行数据， 状态0x3c，说明这两行确实是删除的数据
BBED>  p *kdbr[0]
rowdata[45]
-----------
ub1 rowdata[45]                             @8179     0x3c

BBED>  p *kdbr[1]
rowdata[36]
-----------
ub1 rowdata[36]                             @8170     0x3c

--接下来还原恢复
BBED> modify /x 2c offset 8179
BBED-00215: editing not allowed in BROWSE mode


BBED> set mode edit
        MODE            Edit

BBED> modify /x 2c offset 8179
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
 File: /data/orcl/orclpdb/XTTS.dbf (0)
 Block: 134              Offsets: 8179 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 2c010202 c1020273 730306b4 5e 

 <32 bytes per line>

BBED> modify /x 2c offset 8170
 File: /data/orcl/orclpdb/XTTS.dbf (0)
 Block: 134              Offsets: 8170 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 2c010202 c103026e 6e2c0102 02c10202 73730306 b45e 

 <32 bytes per line>

BBED> sum
Check value for File 0, Block 134:
current = 0xdc7e, required = 0xcc6e

BBED> sum apply
Check value for File 0, Block 134:
current = 0xcc6e, required = 0xcc6e

--验证SQL
发现未发生变化...
11:36:36 SCOTT@orclpdb>select * from jwt;

        ID NAME
---------- ----------
         3 zz
         4 aa
         5 bb
         6 dd

Elapsed: 00:00:00.00

--重启库(当前测试环境是12C，所以我这里直接重启PDB即可)
11:37:13 SYS@orcl>alter pluggable database orclpdb close;

Pluggable database altered.

Elapsed: 00:00:06.13
11:37:29 SYS@orcl>alter pluggable database orclpdb open;

Pluggable database altered.

11:38:04 SYS@orcl>alter session set container=orclpdb;

Session altered.

11:38:19 SYS@orcl>select * from scott.jwt;

        ID NAME
---------- ----------
         1 ss
         2 nn
         3 zz
         4 aa
         5 bb
         6 dd

6 rows selected.

Elapsed: 00:00:00.09

13:41:52 SYS@orclpdb>alter tablespace xtts read write;

Tablespace altered.

--至此，数据恢复成功...


--删除数据(指定某行数据删除)
13:40:42 SCOTT@orclpdb>select * from jwt;

        ID NAME
---------- ----------
         1 ss
         2 nn
         3 zz
         4 aa
         5 bb
         6 dd

6 rows selected.
13:41:08 SCOTT@orclpdb>delete from jwt where ID=1;

1 row deleted.

Elapsed: 00:00:00.00
13:42:45 SCOTT@orclpdb>commit;

Commit complete.

13:42:47 SCOTT@orclpdb>select * from jwt;

        ID NAME
---------- ----------
         2 nn
         3 zz
         4 aa
         5 bb
         6 dd

 13:42:26 SYS@orclpdb>alter tablespace xtts read only;

Tablespace altered.

Elapsed: 00:00:00.09

BBED恢复删除数据...
采用另一种方法寻找被删除字段，再推被删除的row header修改恢复 (可以像上一种方法找到被删除的数据在恢复...)
BBED> set filename '/data/orcl/orclpdb/XTTS.dbf'
        FILENAME        /data/orcl/orclpdb/XTTS.dbf

BBED> set block 134 offset 0
        BLOCK#          134
        OFFSET          0
BBED> find /c ss TOP
 File: /data/orcl/orclpdb/XTTS.dbf (0)
 Block: 134              Offsets: 8186 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 73730206 94a9 

 <32 bytes per line>

BBED> dump /v offset 8186
 File: /data/orcl/orclpdb/XTTS.dbf (0)
 Block: 134     Offsets: 8186 to 8191  Dba:0x00000000
-------------------------------------------------------
 73730206 94a9                       l ss....

从这里看出字符ss所在位置为offset 8186

--若想寻找row header，则需要通过dump 命令推出行头 [即寻找出行头标记为< (即3c开头的标志) 表示新一行的开始，前一行的结束]
dump命令每4个offsets为一个完整显示 (完整显示即8字节  一个offset对应 2 字节)

BBED> dump /v offset 8186 count 128
 File: /data/orcl/orclpdb/XTTS.dbf (0)
 Block: 134     Offsets: 8186 to 8191  Dba:0x00000000
-------------------------------------------------------
 73730206 94a9                       l ss....

 <16 bytes per line>

BBED> dump /v offset 8182 count 128
 File: /data/orcl/orclpdb/XTTS.dbf (0)
 Block: 134     Offsets: 8182 to 8191  Dba:0x00000000
-------------------------------------------------------
 02c10202 73730206 94a9              l ....ss....

 <16 bytes per line>

 -- 73730206 位置变换从前8字节变成中8字节，后续还会继续变化(验证了上述dump命令每4个offsets为一个完整显示结论)

 继续推
BBED> dump /v offset 8178 count 128
 File: /data/orcl/orclpdb/XTTS.dbf (0)
 Block: 134     Offsets: 8178 to 8191  Dba:0x00000000
-------------------------------------------------------
 6e3c0202 02c10202 73730206 94a9     l n<......ss....

 <16 bytes per line>

 可以了看到出现行头标记<，但现在Offset并非完整一行开始，因为还出现字符n，所以还需调整一个字节，显示行头标记< 开头即可

 BBED> dump /v offset 8179 count 128
 File: /data/orcl/orclpdb/XTTS.dbf (0)
 Block: 134     Offsets: 8179 to 8191  Dba:0x00000000
-------------------------------------------------------
 3c020202 c1020273 73020694 a9       l <......ss....

 <16 bytes per line>

 这里已经出现了我们3c开头的（deleted）标志，此时我们可以通过row directory 来进行一个确认
 BBED> p kdbr
sb2 kdbr[0]                                 @118      8079
sb2 kdbr[1]                                 @120      8070
sb2 kdbr[2]                                 @122      8061
sb2 kdbr[3]                                 @124      8052
sb2 kdbr[4]                                 @126      8043
sb2 kdbr[5]                                 @128      8034

BBED> p *kdbr[0]
rowdata[45]
-----------
ub1 rowdata[45]                             @8179     0x3c

根据SQL语句，也可知道我们删除的行确实是第一行...

现着手恢复
BBED> modify /x 2c offset 8179
BBED-00215: editing not allowed in BROWSE mode


BBED> set mode edit
        MODE            Edit

BBED> modify /x 2c offset 8179
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
 File: /data/orcl/orclpdb/XTTS.dbf (0)
 Block: 134              Offsets: 8179 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 2c020202 c1020273 73020694 a9 

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 134:
current = 0xb922, required = 0xb922

进行SQL 验证
--先重启下数据库，再SQL验证...否则可能看见结果不成功.....上一方法步骤已有，此处省略
14:38:40 SYS@orclpdb>select * from scott.jwt;

        ID NAME
---------- ----------
         1 ss
         2 nn
         3 zz
         4 aa
         5 bb
         6 dd

6 rows selected.

Elapsed: 00:00:00.02

数据已被恢复...
14:38:46 SYS@orclpdb>alter tablespace xtts read write;

Tablespace altered.

Elapsed: 00:00:00.35

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
Marvinn

Marvinn

关注

路漫漫其修远兮、吾将上下而求索

  • 99
    原创
  • 0
    翻译
  • 2
    转载
  • 36
    评论
  • 访问：458426次
  • 积分：449
  • 等级：中级会员
  • 排名：第12名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ