断电引起文件scn异常数据库恢复---惜分飞

由于异常断电，数据库最初启动报错

Fri Mar 01 08:41:17 2024 ALTER DATABASE   MOUNT Successful mount of redo thread 1, with mount id 1865809648 Database mounted in Exclusive Mode Lost write protection disabled Completed: ALTER DATABASE   MOUNT Fri Mar 01 08:41:24 2024 ALTER DATABASE OPEN Errors in file /home/oracle/app/diag/rdbms/XFF/XFF/trace/XFF_ora_25243.trc: ORA-01113: file 13 needs media recovery ORA-01110: data file 13: '/data2/oracle/oradata/data/data00.dbf' ORA-1113 signalled during: ALTER DATABASE OPEN...

经过应用厂商一系列操作，主要是如下操作

Fri Mar 01 11:10:56 2024 ALTER DATABASE RECOVER  datafile 13  Media Recovery Start Serial Media Recovery started WARNING! Recovering data file 13 from a fuzzy backup. It might be an online backup taken without entering the begin backup command. ORA-279 signalled during: ALTER DATABASE RECOVER  datafile 13  ... Fri Mar 01 11:11:09 2024 ALTER DATABASE RECOVER    CONTINUE DEFAULT  Media Recovery Log /home/oracle/app/flash_recovery_area/XFF/archivelog/2024_03_01/o1_mf_1_153563_%u_.arc Errors with log /home/oracle/app/flash_recovery_area/XFF/archivelog/2024_03_01/o1_mf_1_153563_%u_.arc ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ... ALTER DATABASE RECOVER    CONTINUE DEFAULT  Media Recovery Log /home/oracle/app/flash_recovery_area/XFF/archivelog/2024_03_01/o1_mf_1_153563_%u_.arc Errors with log /home/oracle/app/flash_recovery_area/XFF/archivelog/2024_03_01/o1_mf_1_153563_%u_.arc ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ... ALTER DATABASE RECOVER CANCEL Media Recovery Canceled Completed: ALTER DATABASE RECOVER CANCEL Fri Mar 01 11:16:50 2024 db_recovery_file_dest_size of 10240 MB is 0.00% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. Fri Mar 01 11:51:56 2024 Starting ORACLE instance (normal) Fri Mar 01 12:11:35 2024 alter database datafile 13 offline ORA-1145 signalled during: alter database datafile 13 offline... Fri Mar 01 12:12:29 2024 alter database recover cancel ORA-1112 signalled during: alter database recover cancel... Fri Mar 01 12:13:24 2024 ALTER DATABASE RECOVER  database until cancel  Media Recovery Start  started logmerger process Fri Mar 01 12:13:24 2024 WARNING! Recovering data file 13 from a fuzzy backup. It might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 14 from a fuzzy backup. It might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 15 from a fuzzy backup. It might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 16 from a fuzzy backup. It might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 17 from a fuzzy backup. It might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 18 from a fuzzy backup. It might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 19 from a fuzzy backup. It might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 20 from a fuzzy backup. It might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 21 from a fuzzy backup. It might be an online backup taken without entering the begin backup command. WARNING! Recovering data file 22 from a fuzzy backup. It might be an online backup taken without entering the begin backup command. Parallel Media Recovery started with 48 slaves ORA-279 signalled during: ALTER DATABASE RECOVER  database until cancel  ... ALTER DATABASE RECOVER    CONTINUE DEFAULT  Media Recovery Log /home/oracle/app/flash_recovery_area/XFF/archivelog/2024_03_01/o1_mf_1_153563_%u_.arc Errors with log /home/oracle/app/flash_recovery_area/XFF/archivelog/2024_03_01/o1_mf_1_153563_%u_.arc Errors in file /home/oracle/app/diag/rdbms/XFF/XFF/trace/XFF_pr00_38097.trc: ORA-00308:cannot open archived log '/home/app/flash_recovery_area/XFF/archivelog/2024_03_01/o1_mf_1_153563_%u_.arc' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ... ALTER DATABASE RECOVER    CONTINUE DEFAULT  Media Recovery Log /home/oracle/app/flash_recovery_area/XFF/archivelog/2024_03_01/o1_mf_1_153563_%u_.arc Errors with log /home/oracle/app/flash_recovery_area/XFF/archivelog/2024_03_01/o1_mf_1_153563_%u_.arc Errors in file /home/oracle/app/diag/rdbms/XFF/XFF/trace/XFF_pr00_38097.trc: ORA-00308:cannot open archived log '/home/app/flash_recovery_area/XFF/archivelog/2024_03_01/o1_mf_1_153563_%u_.arc' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ... ALTER DATABASE RECOVER CANCEL Signalling error 1152 for datafile 1! Errors in file /home/oracle/app/diag/rdbms/XFF/XFF/trace/XFF_pr00_38097.trc: ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: '/data1/oracle/oradata/XFF/system01.dbf' Slave exiting with ORA-1547 exception Errors in file /home/oracle/app/diag/rdbms/XFF/XFF/trace/XFF_pr00_38097.trc: ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: '/data1/oracle/oradata/XFF/system01.dbf' ORA-10879 signalled during: ALTER DATABASE RECOVER CANCEL ... Fri Mar 01 13:23:05 2024 ALTER DATABASE DATAFILE '/data2/oracle/oradata/data/data00.dbf' OFFLINE DROP Completed: ALTER DATABASE DATAFILE '/data2/oracle/oradata/data/data00.dbf' OFFLINE DROP

接手现场之后,尝试单个文件recover操作

SQL> recover datafile 1; ORA-00283: recovery session canceled due to errors ORA-00264: no recovery required SQL> recover datafile 2; Media recovery complete. SQL> recover datafile 3; Media recovery complete. SQL> recover datafile 4; Media recovery complete. SQL> recover datafile 5; Media recovery complete. SQL> recover datafile 6,7,8,9,10; Media recovery complete. SQL> recover datafile 11; Media recovery complete. SQL> recover datafile 12; Media recovery complete. SQL> recover datafile 13; ORA-00279: change 1474236715 generated at 02/29/2024 17:13:00 needed for thread 1 ORA-00289: suggestion : /home/oracle/app/program/11g/dbs/arch1_153563_1136037378.dbf ORA-00280: change 1474236715 for thread 1 is in sequence #153563 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} ORA-00308: cannot open archived log '/home/oracle/app/program/11g/dbs/arch1_153563_1136037378.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SQL> recover datafile 14; ORA-00279: change 1474236715 generated at 02/29/2024 17:13:00 needed for thread 1 ORA-00289: suggestion : /home/oracle/app/program/11g/dbs/arch1_153563_1136037378.dbf ORA-00280: change 1474236715 for thread 1 is in sequence #153563 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} ORA-00308: cannot open archived log '/home/oracle/app/program/11g/dbs/arch1_153563_1136037378.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3

基于这样的情况,通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)检查发现/data2挂载点所有数据文件异常,由于以前的操作日志已经被清空无法判断原因,初步怀疑和这个挂载点本身有关系
 

 

这种情况直接使用bbed修改文件头,然后open库,再逻辑导出数据,完成本次数据恢复工作,参考类似文档
bbed 修改datafile header
使用bbed让rac中的sysaux数据文件online
当然这类故障也可以通过自研的Oracle Recovery Tools工具进行修复处理,类似文档:
Oracle Recovery Tools解决ORA-00279 ORA-00289 ORA-00280故障