BBED-恢复不一致的文件头

主要存在主机异常断电的情况会出现不一致

场景构建

SQL> select * from ttt;

        ID NAME
---------- --------------------
         1 abcde

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@localhost prod]$ cp system01.dbf system01.dbf_bak

SQL> drop table ttt;

Table dropped.
[oracle@localhost prod]$ rm system01.dbf
[oracle@localhost prod]$ cp system01.dbf_bak system01.dbf 
QL> startup
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2253824 bytes
Variable Size             293604352 bytes
Database Buffers          117440512 bytes
Redo Buffers                4247552 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oradata/prod/system01.dbf'

恢复

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

TO_CHAR(CHECKPOINT_CHANGE#)
----------------------------------------
42951034868
42951034868
42951034868
42951034868
42951034868

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

     FILE# TO_CHAR(CHANGE#)
---------- ----------------------------------------
         1 42951034613
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
   struct kcvcpscn, 8 bytes                 @484     
      ub4 kscnbas                           @484      0x0014c6f5
      ub2 kscnwrp                           @488      0x000a
   ub4 kcvcptim                             @492      0x3f043ae5
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500     
     
BBED> p kcvfhcpc
ub4 kcvfhcpc                                @140      0x00000121

BBED> p kcvfhccc
ub4 kcvfhccc                                @148      0x00000120

SCN =（SCN_WRAP * 4294967296）+ SCN_BASE
SQL> select to_char(to_number('a','xxxxx')*4294967296+to_number('14c6f5','xxxxxxxx')) from dual;

TO_CHAR(TO_NUMBER('A','XXXXX')*429496729
----------------------------------------
42951034613
由于数据文件的这个值不对，正常的值应该是，42951034868，我们需要将该值设置为正确的值。
 select to_char(42951034868-to_char(to_number('a','xxxxx')*4294967296),'xxxxxxxx') from dual;

TO_CHAR(42951034868-TO_CHAR(TO_NUMBER('A','XXXXX')*4294967296),'XXXXXXXX')
--------------------------------------------------------------------------------
   14c7f4
由于linux的字节序需要调转，原本的14c6f5的值是f5c614,我们修改的时候 14c7f4需要转为f4c714
BBED> dump offset 484 count 8
 File: /oradata/prod/system01.dbf (1)
 Block: 1                Offsets:  484 to  491           Dba:0x00400001
------------------------------------------------------------------------
 f5c61400 0a000000 

p kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
   struct kcvcpscn, 8 bytes                 @484     
      ub4 kscnbas                           @484      0x0014c7f4
      ub2 kscnwrp                           @488      0x000a
   ub4 kcvcptim                             @492      0x3f043ae5
现在checkpoint的scn号已经改为正确的值了，现在需要将checkpoint改为正确的值。通过脚本查看checkpoint_time的scn号

select file#,
to_char(CHECKPOINT_TIME, 'yyyy-mm-dd hh24:mi:ss') CHECKPOINT_TIME_file,
(to_char(CHECKPOINT_TIME, 'yyyy') - 1988) * 12 * 31 * 24 * 3600 +
(to_char(CHECKPOINT_TIME, 'mm') - 1) * 31 * 24 * 3600 +
(to_char(CHECKPOINT_TIME, 'dd') - 1) * 24 * 3600 +
to_char(CHECKPOINT_TIME, 'hh24') * 3600 +
to_char(CHECKPOINT_TIME, 'mi') * 60 + to_char(CHECKPOINT_TIME, 'ss') creation_name_scn
from v$datafile
order by 1;
     FILE# CHECKPOINT_TIME_FILE                                                        CREATION_NAME_SCN
---------- --------------------------------------------------------------------------- -----------------
         1 2020-11-23 14:17:47                                                                1057241867
         2 2020-11-23 14:17:47                                                                1057241867
         3 2020-11-23 14:17:47                                                                1057241867
         4 2020-11-23 14:17:47                                                                1057241867
         5 2020-11-23 14:17:47                                                                1057241867
SQL> select to_char(1057241867,'xxxxxxxxxx') from dual;

TO_CHAR(1057241867,'XXXXXXXXXX')
-----------------------------------------------------------------------
   3f043b0b
而数据文件的值为0x3f043ae5是错误的。
BBED> dump offset 492 count 4
 File: /oradata/prod/system01.dbf (1)
 Block: 1                Offsets:  492 to  495           Dba:0x00400001
------------------------------------------------------------------------
 e53a043f 

BBED> modify /x 0b3b043f offset 492
 File: /oradata/prod/system01.dbf (1)
 Block: 1                Offsets:  492 to  495           Dba:0x00400001
------------------------------------------------------------------------
 0b3b043f 

BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
   struct kcvcpscn, 8 bytes                 @484     
      ub4 kscnbas                           @484      0x0014c7f4
      ub2 kscnwrp                           @488      0x000a
   ub4 kcvcptim                             @492      0x3f043b0b
   ub2 kcvcpthr                             @496      0x0001
现在checkpoint的scn号的checkpoint_time scn号都正确了


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

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select * from ttt;

        ID NAME
---------- --------------------
         1 abcde
我们可以看到，drop的表会仍然存在，虽然可以恢复，但是这段时间的操作会丢失。