BBED恢复非归档offline drop的数据文件

环境模拟

SQL> alter database datafile '/oradata/prod/testtbs01.dbf' offline drop;

Database altered.

SQL> alter system checkpoint;

System altered.

SQL> select file#,to_char(change#) from v$recover_file;

     FILE# TO_CHAR(CHANGE#)
---------- ----------------------------------------
         5 42951050917

SQL> select file#,to_char(checkpoint_change#) from v$datafile;

     FILE# TO_CHAR(CHECKPOINT_CHANGE#)
---------- ----------------------------------------
         1 42951051037
         2 42951051037
         3 42951051037
         4 42951051037
         5 42951050917


SQL> alter system switch logfile;

System altered.
执行多次，让日志文件覆盖

SQL> alter database datafile '/oradata/prod/testtbs01.dbf' online;
alter database datafile '/oradata/prod/testtbs01.dbf' online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/oradata/prod/testtbs01.dbf'

SQL> recover datafile 5;
ORA-00279: change 42951050917 generated at 11/23/2020 22:45:27 needed for thread 1
ORA-00289: suggestion : /arch/1_67_1052188220.dbf
ORA-00280: change 42951050917 for thread 1 is in sequence #67


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '/arch/1_67_1052188220.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

 

BBED修改文件SCN号恢复

BBED> set file 5 block 1
        FILE#           5
        BLOCK#          1

BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
   struct kcvcpscn, 8 bytes                 @484     
      ub4 kscnbas                           @484      0x001506a5
      ub2 kscnwrp                           @488      0x000a
   ub4 kcvcptim                             @492      0x3f04b207
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500     
      struct kcvcprba, 12 bytes             @500     
         ub4 kcrbaseq                       @500      0x00000043
         ub4 kcrbabno                       @504      0x000085f4
         ub2 kcrbabof                       @508      0x0010

  ub4 kscnbas                           @484  需要修改为当前数据文件的CHECKPOINT_CHANGE#
SQL>  select to_char(42951051037-to_char(to_number('a','xxxxx')*4294967296),'xxxxxxxx') from dual;

TO_CHAR(42951051037-TO_CHAR(TO_NUMBER('A','XXXXX')*4294967296),'XXXXXXXX')
----------------------------------- 
   15071d


   ub4 kcvcptim      @492      0x3f04b207需要修改为当前数据文件的creation_name_scn
SQL> select file#,
  2  to_char(CHECKPOINT_TIME, 'yyyy-mm-dd hh24:mi:ss') CHECKPOINT_TIME_file,
  3  (to_char(CHECKPOINT_TIME, 'yyyy') - 1988) * 12 * 31 * 24 * 3600 +
  4  (to_char(CHECKPOINT_TIME, 'mm') - 1) * 31 * 24 * 3600 +
  5  (to_char(CHECKPOINT_TIME, 'dd') - 1) * 24 * 3600 +
  6  to_char(CHECKPOINT_TIME, 'hh24') * 3600 +
  7  to_char(CHECKPOINT_TIME, 'mi') * 60 + to_char(CHECKPOINT_TIME, 'ss') creation_name_scn
  8  from v$datafile
  9  order by 1;

     FILE# CHECKPOINT_TIME_FILE                                                        CREATION_NAME_SCN
---------- --------------------------------------------------------------------------- -----------------
         1 2020-11-23 22:46:27                                                                1057272387
         2 2020-11-23 22:46:27                                                                1057272387
         3 2020-11-23 22:46:27                                                                1057272387
         4 2020-11-23 22:46:27                                                                1057272387
         5 2020-11-23 22:45:27                                                                1057272327

SQL> select to_char(1057272387,'xxxxxxxxxx') from dual;

TO_CHAR(1057272387,'XXXXXXXXXX')
------------------------------------------------ 
3f04b243

BBED> modify /x 1d0715 offset 484 
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
 File: /oradata/prod/testtbs01.dbf (5)
 Block: 1                Offsets:  484 to  995           Dba:0x01400001
------------------------------------------------------------------------
 1d071500 0a000000 07b2043f 01000000 43000000 f4850000 10000000 02000000 
BBED>  modify /x  43b2043f offset 492
 File: /oradata/prod/testtbs01.dbf (5)
 Block: 1                Offsets:  492 to  499           Dba:0x01400001
------------------------------------------------------------------------
 43b2043f 01000000 

BBED> P kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
   struct kcvcpscn, 8 bytes                 @484     
      ub4 kscnbas                           @484      0x0015071d
      ub2 kscnwrp                           @488      0x000a
   ub4 kcvcptim                             @492      0x3f04b243

BBED> sum apply
Check value for File 5, Block 1:
current = 0x7a42, required = 0x7a42

SQL> alter database datafile '/oradata/prod/testtbs01.dbf' online;
alter database datafile '/oradata/prod/testtbs01.dbf' online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/oradata/prod/testtbs01.dbf'


SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile '/oradata/prod/testtbs01.dbf' online;

Database altered.