欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-05-01 19:55:37
描述: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)) tablespa...

描述:

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