Oracle BBED修改文件头SCN、timestamp恢复

原创邓秋爽云和恩墨技术服务团队 2020-06-06
1869
datafile的file header存储在第一个block里
Oracle considers four attributes of this data structure when determining if a datafile is sync with the other data files of the database:（不同oracle版本offset可能不同）
	▪	kscnbas (at offset 484) – SCN of last change to the datafile.
	▪	kcvcptim (at offset 492) -Time of the last change to the datafile.
	▪	kcvfhcpc (at offset 140) – Checkpoint count.
	▪	kcvfhccc (at offset 148) – Unknown, but is always 1 less than thecheckpoint point count.
Oracle有4个属性来判断datafile 是否和其他的datafile 一致，如果都一致可以正常操作，如果不一致那么会报ORA-01113错误

模拟bbed修改数据文件头的检查点SCN、检查点时间点、检查点计数
1、asmcmd cp拷贝ASM文件到文件系统：
ASMCMD> cp BBED.268.1038092825 /tmp 

2、日志切换：
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

3、关闭数据库
SQL> shutdown immediate;

4、asmcmd rm删除bbed数据文件

5、启动数据库到mount，删除所有归档

6、注册datafilecopy并且恢复bbed数据文件
RMAN> catalog datafilecopy  '/tmp/BBED.268.1038092825';
RMAN> restore from datafilecopy datafile 6;

7、启动数据库报错
SQL>  alter database open;
 alter database open
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '+DAT/orcl11g/datafile/bbed.268.1038095477'

8、查看控制文件SCN、数据文件头SCN
SQL> set linesize 260 pagesize 10000
SQL> col name for a80
SQL> alter session set NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
SQL> select name,FILE#,CHECKPOINT_CHANGE#,UNRECOVERABLE_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME from v$datafile;

NAME                                                                                  FILE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# CHECKPOINT_TIME     UNRECOVERABLE_TIME  LAST_CHANGE# LAST_TIME
-------------------------------------------------------------------------------- ---------- ------------------ --------------------- ------------------- ------------------- ------------ -------------------
+DAT/orcl11g/datafile/system.259.1037918023                                               1            1118269                     0 2020-04-18 23:37:47                          1118269 2020-04-18 23:37:47
+DAT/orcl11g/datafile/sysaux.260.1037918027                                               2            1118269                     0 2020-04-18 23:37:47                          1118269 2020-04-18 23:37:47
+DAT/orcl11g/datafile/undotbs1.261.1037918031                                             3            1118269                     0 2020-04-18 23:37:47                          1118269 2020-04-18 23:37:47
+DAT/orcl11g/datafile/undotbs2.263.1037918045                                             4            1118269                     0 2020-04-18 23:37:47                          1118269 2020-04-18 23:37:47
+DAT/orcl11g/datafile/users.264.1037918049                                                5            1118269                     0 2020-04-18 23:37:47                          1118269 2020-04-18 23:37:47
+DAT/orcl11g/datafile/bbed.268.1038095477                                                 6            1118269                     0 2020-04-18 23:37:47                          1118269 2020-04-18 23:37:47

SQL> select CHECKPOINT_CHANGE#,CURRENT_SCN from v$database;

CHECKPOINT_CHANGE# CURRENT_SCN
------------------ -----------
           1118269           0

SQL> select file#,name,bytes,STATUS,FORMAT,RECOVER,FUZZY,CHECKPOINT_CHANGE#,CHECKPOINT_TIME,CHECKPOINT_COUNT from v$datafile_header;

     FILE# NAME                                                                                  BYTES STATUS      FORMAT REC FUZ CREATION_CHANGE#  CHECKPOINT_CHANGE# CHECKPOINT_TIME    CHECKPOINT_COUNT
---------- -------------------------------------------------------------------------------- ---------- ------- ---------- --- --- ----------------  ------------------ ------------------ ----------------
         1 +DAT/orcl11g/datafile/system.259.1037918023                                       807403520 ONLINE          10 NO  NO                 7  1118269 2020-04-18 23:37:47                        107
         2 +DAT/orcl11g/datafile/sysaux.260.1037918027                                       629145600 ONLINE          10 NO  NO              1781  1118269 2020-04-18 23:37:47                        107
         3 +DAT/orcl11g/datafile/undotbs1.261.1037918031                                     613416960 ONLINE          10 NO  NO              2814  1118269 2020-04-18 23:37:47                        107
         4 +DAT/orcl11g/datafile/undotbs2.263.1037918045                                     209715200 ONLINE          10 NO  NO             15543  1118269 2020-04-18 23:37:47                        106
         5 +DAT/orcl11g/datafile/users.264.1037918049                                          5242880 ONLINE          10 NO  NO             15878  1118269 2020-04-18 23:37:47                        106
         6 +DAT/orcl11g/datafile/bbed.268.1038095477                                         104857600 ONLINE          10 YES NO           1109648  1116548 2020-04-18 22:12:50                         18


9、bbe修改数据文件头检查点scn、检查点时间
BBED> set file 6
        FILE#           6

BBED> dump offset 484
 File: /tmp/BBED.268.1038092825 (6)
 Block: 1                Offsets:  484 to  995           Dba:0x01800001
------------------------------------------------------------------------
 84091100 00000000 db0be03d 01000000 59000000 31390000 10000000 02000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 0d000d00 0d000100 00000000 00000000 00000000 02008001 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

说明file 6 scn 1116548转换为16进制就是110984，由于linux是小字节，转换为84091100
SQL> select to_char('1116548','xxxxxxxxx') from v$database;

TO_CHAR('1
----------
    110984

BBED> dump offset 492
 File: /tmp/BBED.268.1038092825 (6)
 Block: 1                Offsets:  492 to 1003           Dba:0x01800001
------------------------------------------------------------------------
 db0be03d 01000000 59000000 31390000 10000000 02000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 0d000d00 0d000100 
 00000000 00000000 00000000 02008001 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> dump offset 140
 File: /tmp/BBED.268.1038092825 (6)
 Block: 1                Offsets:  140 to  651           Dba:0x01800001
------------------------------------------------------------------------
 12000000 d50be03d 11000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 06000000 04004242 45440000 00000000 00000000 00000000 00000000 00000000 
 00000000 06000000 00000000 00000000 d101e03d 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 84091100 00000000 
 db0be03d 01000000 59000000 31390000 10000000 02000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 0d000d00 0d000100 

 <32 bytes per line>

BBED> dump offset 148
 File: /tmp/BBED.268.1038092825 (6)
 Block: 1                Offsets:  148 to  659           Dba:0x01800001
------------------------------------------------------------------------
 11000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 06000000 04004242 
 45440000 00000000 00000000 00000000 00000000 00000000 00000000 06000000 
 00000000 00000000 d101e03d 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 84091100 00000000 db0be03d 01000000 
 59000000 31390000 10000000 02000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 0d000d00 0d000100 00000000 00000000 

 <32 bytes per line>

BBED> set file 5
        FILE#           5

BBED> dump offset 484
 File: /tmp/USERS.264.1037918049 (5)
 Block: 1                Offsets:  484 to  995           Dba:0x01400001
------------------------------------------------------------------------
 3d101100 00000000 4b11e03d 01000000 62000000 ad000000 10000b8b 02000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 0d000d00 0d000100 00000000 00000000 00000000 02004001 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

说明file 6 scn 1118269转换为16进制就是11103d，由于linux是小字节，转换为3d101100
SQL> select to_char('1118269','xxxxxxx') from v$database;

TO_CHAR(
--------
  11103d

BBED> dump offset 492
 File: /tmp/USERS.264.1037918049 (5)
 Block: 1                Offsets:  492 to 1003           Dba:0x01400001
------------------------------------------------------------------------
 4b11e03d 01000000 62000000 ad000000 10000b8b 02000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 0d000d00 0d000100 
 00000000 00000000 00000000 02004001 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> dump offset 140
 File: /tmp/USERS.264.1037918049 (5)
 Block: 1                Offsets:  140 to  651           Dba:0x01400001
------------------------------------------------------------------------
 6a000000 00000000 69000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 05000000 05005553 45525300 00000000 00000000 00000000 00000000 00000000 
 00000000 05000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 3d101100 00000000 
 4b11e03d 01000000 62000000 ad000000 10000b8b 02000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 0d000d00 0d000100 

 <32 bytes per line>

BBED> dump offset 148 
 File: /tmp/USERS.264.1037918049 (5)
 Block: 1                Offsets:  148 to  659           Dba:0x01400001
------------------------------------------------------------------------
 69000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 05000000 05005553 
 45525300 00000000 00000000 00000000 00000000 00000000 00000000 05000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 3d101100 00000000 4b11e03d 01000000 
 62000000 ad000000 10000b8b 02000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 0d000d00 0d000100 00000000 00000000 

 <32 bytes per line>

修改offset 484和offset 492(由于控制文件记录的数据文件检查点scn、检查点时间和数据文件头不一致）
BBED> m/x 3d101100 offset 484
 File: /tmp/BBED.268.1038092825 (6)
 Block: 1                Offsets:  484 to  995           Dba:0x01800001
------------------------------------------------------------------------
 3d101100 00000000 db0be03d 01000000 59000000 31390000 10000000 02000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 0d000d00 0d000100 00000000 00000000 00000000 02008001 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> m/x 4b11e03d offset 492   
 File: /tmp/BBED.268.1038092825 (6)
 Block: 1                Offsets:  492 to 1003           Dba:0x01800001
------------------------------------------------------------------------
 4b11e03d 01000000 59000000 31390000 10000000 02000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 0d000d00 0d000100 
 00000000 00000000 00000000 02008001 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

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

将数据文件从文件系统恢复到asm，可以打开数据库：
[oracle@ora11g-node01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 19 02:11:38 2020

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '+DAT/orcl11g/datafile/bbed.268.1038095477'

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

Database altered.
oracle
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
Oracle BBED修改文件头SCN、timestamp恢复

评论
