bbed-delete数据恢复

一、准备环境

 

[oracle@renCAP ~]$  sqlplus bbed_user/oracle@renpdb

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 19 23:24:49 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Wed Jun 19 2019 23:18:23 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> 
SQL> 
SQL> select * from bbed_tab;

        ID NAME
---------- --------------------
         1 zhangsan
         2 cap
         3 wangwu

SQL> delete from bbed_user.bbed_tab where id=2;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from bbed_tab;

        ID NAME
---------- --------------------
         1 zhangsan
         3 wangwu

SQL> alter system checkpoint;

System altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@renCAP ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 19 23:26:16 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> alter system flush buffer_cache;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     7
Next log sequence to archive   9
Current log sequence           9
SQL> show parameter recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /data/area
db_recovery_file_dest_size           big integer 8016M
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0
remote_recovery_file_dest            string
SQL> 
[oracle@renCAP 2019_06_19]$ pwd
/data/area/REN/archivelog/2019_06_19
[oracle@renCAP 2019_06_19]$ ls
o1_mf_1_2_gjm3zpst_.arc  o1_mf_1_4_gjm4wmff_.arc  o1_mf_1_6_gjnhzxsb_.arc  o1_mf_1_8_gjno189v_.arc
o1_mf_1_3_gjm4wmcb_.arc  o1_mf_1_5_gjmg5jbb_.arc  o1_mf_1_7_gjno14cz_.arc
[oracle@renCAP 2019_06_19]$ ll
总用量 629252
-rw-r----- 1 oracle oinstall 118903296 6月  19 09:29 o1_mf_1_2_gjm3zpst_.arc
-rw-r----- 1 oracle oinstall    442368 6月  19 09:44 o1_mf_1_3_gjm4wmcb_.arc
-rw-r----- 1 oracle oinstall      1024 6月  19 09:44 o1_mf_1_4_gjm4wmff_.arc
-rw-r----- 1 oracle oinstall 198629888 6月  19 12:23 o1_mf_1_5_gjmg5jbb_.arc
-rw-r----- 1 oracle oinstall 176411136 6月  19 22:01 o1_mf_1_6_gjnhzxsb_.arc
-rw-r----- 1 oracle oinstall 149944832 6月  19 23:26 o1_mf_1_7_gjno14cz_.arc
-rw-r----- 1 oracle oinstall      1024 6月  19 23:26 o1_mf_1_8_gjno189v_.arc
[oracle@renCAP 2019_06_19]$ cd ..
[oracle@renCAP archivelog]$ rm -rf 2019_06_19/
[oracle@renCAP archivelog]$ ll
总用量 0
[oracle@renCAP archivelog]$ 

--归档全部清除。

二、开始bbed恢复

Oracle中delete行时，数据实际上并没有被删除。而是将行标记为已删除，并相应地调整空闲空间计数器和指针。行状态存储在每行的行标头中，该行标头占用每行的前几个字节。 行标头由行标记、锁字节(ITL条目)和列计数组成。第一个Row标志是一个单字节，它保存一个显示行状态的位掩码。

因此行标头=head of row picec + first data picec + last data picec = 44 => 0x2c

当数据被删除后，行标头+16即60=>0x3c

SQL> select segment_name,tablespace_name,header_file,header_block from dba_segments where segment_name='BBED_TAB' and owner='BBED_USER';

SEGMENT_NAME                        TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK
----------------------------------- ------------------------------ ----------- ------------
BBED_TAB                            BBED_TBS                                15          138
SQL> alter system dump datafile 15 block 138;

查看file 15 block 138 dump

  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 8     
                  last map  0x00000000  #maps: 0      offset: 2716  
      Highwater::  0x03c00090  ext#: 0      blk#: 8      ext size: 8     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 5     
  mapblk  0x00000000  offset: 0     
                   Unlocked
  --------------------------------------------------------
  Low HighWater Mark : 
      Highwater::  0x03c00090  ext#: 0      blk#: 8      ext size: 8     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 5     
  mapblk  0x00000000  offset: 0     
  Level 1 BMB for High HWM block: 0x03c00088
  Level 1 BMB for Low HWM block: 0x03c00088
  --------------------------------------------------------
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0      
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x03c00089
  Last Level 1 BMB:  0x03c00088
  Last Level II BMB:  0x03c00089
  Last Level III BMB:  0x00000000
     Map Header:: next  0x00000000  #extents: 1    obj#: 73176  flag: 0x10000000
  Inc # 0 
  Extent Map
  -----------------------------------------------------------------
   0x03c00088  length: 8     
  
  Auxillary Map
  --------------------------------------------------------
   Extent 0     :  L1 dba:  0x03c00088 Data dba:  0x03c0008b
  --------------------------------------------------------
  
   Second Level Bitmap block DBAs 
   --------------------------------------------------------
   DBA 1:   0x03c00089
  
End dump data blocks tsn: 8 file#: 15 minblk 138 maxblk 138
--data dba是0x03c0008b
 SELECT DBMS_UTILITY.data_block_address_file (
           TO_NUMBER (LTRIM ('0x03c0008b', '0x'), 'xxxxxxxx'))
           AS file_no,
        DBMS_UTILITY.data_block_address_block (
           TO_NUMBER (LTRIM ('0x03c0008b', '0x'), 'xxxxxxxx'))
           AS block_no
   FROM DUAL;

SQL>  SELECT DBMS_UTILITY.data_block_address_file (
  2             TO_NUMBER (LTRIM ('0x03c0008b', '0x'), 'xxxxxxxx'))
  3             AS file_no,
  4          DBMS_UTILITY.data_block_address_block (
  5             TO_NUMBER (LTRIM ('0x03c0008b', '0x'), 'xxxxxxxx'))
  6             AS block_no
  7     FROM DUAL;

   FILE_NO   BLOCK_NO
---------- ----------
        15        139

SQL> 

--确定要恢复的数据存储是从139号块开始存储的

[oracle@renCAP bbed]$ bbed parfile=par.bbed

Password:

BBED: Release 2.0.0.0.0 - Limited Production on Thu Jun 20 00:25:49 2019

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

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

BBED> set dba 15,139

DBA 0x03c0008b (62914699 15,139)

BBED> find /c cap

BBED-00212: search string not found

--没有继续往下扫描

*********省略**************

BBED> set dba 15,142

DBA 0x03c0008e (62914702 15,142)

BBED> find /c cap

File: /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn (15)

Block: 142 Offsets: 8169 to 8191 Dba:0x03c0008e

------------------------------------------------------------------------

63617000 2c000202 c102087a 68616e67 73616e01 06a95d

<32 bytes per line>

BBED> dump /v offset 8169

File: /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn (15)

Block: 142 Offsets: 8169 to 8191 Dba:0x03c0008e

-------------------------------------------------------

63617000 2c000202 c102087a 68616e67 l cap.,...hang

73616e01 06a95d l san..

<16 bytes per line>

BBED>

BBED> p *kdbr[1]

rowdata[13]

-----------

ub1 rowdata[13] @8162 0x3c

--检查要恢复的数据是从8162开始的，接下来将offset 8162 修改成0x2c

BBED> modify /x 2c offset 8162

File: /data/REN/8BA1F4113C38CFFCE0530A648C0A2156/datafile/o1_mf_bbed_tbs_gjn (15)

Block: 142 Offsets: 8162 to 8191 Dba:0x03c0008e

------------------------------------------------------------------------

2c020202 c1030463 6170002c 000202c1 02087a68 616e6773 616e0106 a95d

<32 bytes per line>

BBED> sum apply

Check value for File 15, Block 142:

current = 0x88c3, required = 0x88c3

BBED>

SQL> alter system flush buffer_cache;

System altered.

SQL> select * from bbed_user.bbed_tab;

ID NAME

---------- --------------------

1 zhangsan

2 cap --已恢复

3 wangwu

恢复完成

--end