Oracle BBED利用copy命令恢复已删除的记录_MSSQL

描述：

BBED 的copy 命令可以从旧的数据文件中copy block至目标数据文件，从而恢复已经删除的记录。

构建场景：

创建测试表空间:
SQL> create tablespace test_bbed datafile '/oracle/app/oradata/prod/bbed01.dbf' size 50M;
Tablespace created.

创建测试表：
SQL> create table t1 (id number,name varchar2(20)) tablespace test_bbed;
Table created.

SQL> insert into t1 values(1,'bao');
1 row created.

SQL> insert into t1  values(2,'hang');
1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;
        ID NAME
---------- --------------------
         1 bao
         2 hang

做一个冷备份:
SQL> shutdown immediate;
[oracle@server1 prod]$ cp bbed01.dbf bbed01.dbf.bak

删除t1表中数据：
SQL> startup;
SQL> delete from t1;
2 rows deleted.
SQL> commit;
Commit complete.

构建bbed参数文件：

SQL> startup;

查询数据文件：
SQL> select file# || ' ' ||name || ' ' || bytes from v$datafile;

                  FILE#||''||NAME||''||BYTES
1 /oracle/app/oradata/prod/system01.dbf 786432000
2 /oracle/app/oradata/prod/sysaux01.dbf 545259520
3 /oracle/app/oradata/prod/undotbs01.dbf 94371840
4 /oracle/app/oradata/prod/users01.dbf 5242880
5 /oracle/app/oradata/prod/example01.dbf 347340800
6 /oracle/app/oradata/prod/bbed01.dbf 52428800



将以上查询的信息保存在文本中。

[oracle@server1 ~]$ cat datafile.txt 
1 /oracle/app/oradata/prod/system01.dbf 786432000
2 /oracle/app/oradata/prod/sysaux01.dbf 545259520
3 /oracle/app/oradata/prod/undotbs01.dbf 94371840
4 /oracle/app/oradata/prod/users01.dbf 5242880
5 /oracle/app/oradata/prod/example01.dbf 347340800
6 /oracle/app/oradata/prod/bbed01.dbf 52428800
7 /oracle/app/oradata/prod/bbed01.dbf.bak 52428800

将冷备份文件也加入进去/oracle/app/oradata/prod/bbed01.dbf.bak

创建BBED参数文件：
[oracle@server1 ~]$ cat bbed.par 
blocksize=8192
listfile=/home/oracle/datafile.txt   //指定文本文件
mode=edit    //编辑模式

查询表的块信息：

SQL> select owner, segment_name, header_file,header_block, blocks  from dba_segments where owner = 'SYS' and segment_name = 'T1';
OWNER      SEGMENT_NA HEADER_FILE HEADER_BLOCK     BLOCKS
---------- ---------- ----------- ------------ ----------
SYS        T1                   6          130          8

dba_segments 视图里的block 是从0开始的统计的，而bbed 里是从1. 所以我们在bbed中指定block时，需要加1.

使用bbed工具copy命令进行恢复

关库：
SQL> shutdown immediate;
[oracle@server1 ~]$ bbed parfile='/home/oracle/bbed.par'
BBED> set dba 6,130 offset 0
        DBA             0x01800082 (25165954 6,130)
        OFFSET          0

BBED> p ktbbh
BBED-00400: invalid blocktype (35)
dba_segments 视图里的block 是从0开始的统计的，而bbed 里是从1. 所以我们在bbed中指定block时，需要加1.

BBED> set dba 6,131 offset 0
        DBA             0x01800083 (25165955 6,131)
        OFFSET          0

BBED> p ktbbh
struct ktbbh, 72 bytes                      @20      
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24      
      ub4 ktbbhsg1                          @24       0x00015a45
      ub4 ktbbhod1                          @24       0x00015a45
   struct ktbbhcsc, 8 bytes                 @28      
      ub4 kscnbas                           @28       0x00110a6a
      ub2 kscnwrp                           @32       0x0000
   sb2 ktbbhict                             @36       2
   ub1 ktbbhflg                             @38       0x32 (NONE)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x01800080
   struct ktbbhitl[0], 24 bytes             @44      
      struct ktbitxid, 8 bytes              @44      
         ub2 kxidusn                        @44       0x0000
         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       0x0000 (NONE)
      union _ktbitun, 2 bytes               @62      
         sb2 _ktbitfsc                      @62       0
         ub2 _ktbitwrp                      @62       0x0000
      ub4 ktbitbas                          @64       0x00000000
   struct ktbbhitl[1], 24 bytes             @68      
      struct ktbitxid, 8 bytes              @68      
         ub2 kxidusn                        @68       0x0000
         ub2 kxidslt                        @70       0x0000
         ub4 kxidsqn                        @72       0x00000000
      struct ktbituba, 8 bytes              @76      
         ub4 kubadba                        @76       0x00000000
         ub2 kubaseq                        @80       0x0000
         ub1 kubarec                        @82       0x00
      ub2 ktbitflg                          @84       0x0000 (NONE)
      union _ktbitun, 2 bytes               @86      
         sb2 _ktbitfsc                      @86       0
         ub2 _ktbitwrp                      @86       0x0000
      ub4 ktbitbas                          @88       0x00000000

使用bbed copy 从旧的datafile里恢复出来
BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /oracle/app/oradata/prod/system01.dbf                            96000
     2  /oracle/app/oradata/prod/sysaux01.dbf                            66560
     3  /oracle/app/oradata/prod/undotbs01.dbf                           11520
     4  /oracle/app/oradata/prod/users01.dbf                               640
     5  /oracle/app/oradata/prod/example01.dbf                           42400
     6  /oracle/app/oradata/prod/bbed01.dbf                               6400
     7  /oracle/app/oradata/prod/bbed01.dbf.bak 

块头为131 共8个块 131 132 133 134 135 136 137 138
从7号文件中copy 8个块到 6号文件中
BBED> copy dba 7,131 to dba 6,131
BBED> copy dba 7,132 to dba 6,132
BBED> copy dba 7,133 to dba 6,133
BBED> copy dba 7,134 to dba 6,134
BBED> copy dba 7,135 to dba 6,135
BBED> copy dba 7,136 to dba 6,136
BBED> copy dba 7,137 to dba 6,137
BBED> copy dba 7,138 to dba 6,138
BBED> sum apply

打开数据库进行查看：

SQL> startup;
ORACLE instance started.

Total System Global Area  830930944 bytes
Fixed Size                  2257800 bytes
Variable Size             541068408 bytes
Database Buffers          281018368 bytes
Redo Buffers                6586368 bytes
Database mounted.
Database opened.

SQL> select * from t1;

        ID NAME
---------- --------------------
         1 bao
         2 hang

恢复完成

问题：如果表中数据所占的数据块太多该如何批量copy呢

总结：没啥实际应用价值，学习bbed时，可以顺便了解一下

其他有关bbed的案例：

Oracle BBED工具介绍与安装

Oracle BBED利用copy命令恢复已删除的记录

Oracle BBED修改数据块进而修改数据

Oracle BBED全库跳过丢失的归档继续恢复

Oracle BBED单个数据文件跳过丢失的归档继续恢复

Oracle BBED单个数据文件跳过所有归档恢复

Oracle BBED将offline的数据文件改为online案例

本文地址：https://blog.csdn.net/baoyuhang0/article/details/108980548