联系：手机/微信(+86 17813235971) QQ(107644445)
标题：删除redo导致ORA-00313 ORA-00312故障处理
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有客户由于误操作直接rm 删除了redo文件,导致数据库启动报ORA-00313 ORA-00312错
2025-03-07T14:49:16.325723+08:00 ALTER DATABASE OPEN 2025-03-07T14:50:00.124620+08:00 Ping without log force is disabled: instance mounted in exclusive mode. 2025-03-07T14:50:00.198907+08:00 Crash Recovery excluding pdb 2 which was cleanly closed. 2025-03-07T14:50:00.238450+08:00 Beginning crash recovery of 1 threads parallel recovery started with 15 processes Thread 1: Recovery starting at checkpoint rba (logseq 2966 block 74686), scn 0 2025-03-07T14:50:00.325246+08:00 Started redo scan 2025-03-07T14:50:00.341193+08:00 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2681.trc: ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7 2025-03-07T14:50:00.372632+08:00 Slave encountered ORA-10388 exception during crash recovery ………… 2025-03-07T14:50:00.385698+08:00 Slave encountered ORA-10388 exception during crash recovery 2025-03-07T14:50:00.388594+08:00 Aborting crash recovery due to error 313 2025-03-07T14:50:00.388739+08:00 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2681.trc: ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7 2025-03-07T14:50:00.389243+08:00 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2681.trc: ORA-00313: open failed for members of log group 2 of thread 1 ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7 ORA-313 signalled during: ALTER DATABASE OPEN...
然后客户把历史的redo文件拷贝过来,尝试恢复数据库,报ORA-00314 ORA-00312错误
2025-03-07T15:07:30.784759+08:00 ALTER DATABASE OPEN Ping without log force is disabled: instance mounted in exclusive mode. 2025-03-07T15:07:30.808497+08:00 Crash Recovery excluding pdb 2 which was cleanly closed. 2025-03-07T15:07:30.838664+08:00 Beginning crash recovery of 1 threads parallel recovery started with 15 processes Thread 1: Recovery starting at checkpoint rba (logseq 2966 block 74686), scn 0 2025-03-07T15:07:30.897547+08:00 Started redo scan 2025-03-07T15:07:30.898222+08:00 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4106.trc: ORA-00314: log 2 of thread 1, expected sequence# 2966 doesn't match 1646 ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log' 2025-03-07T15:07:30.930089+08:00 Slave encountered ORA-10388 exception during crash recovery ………… 2025-03-07T15:07:30.940051+08:00 Slave encountered ORA-10388 exception during crash recovery 2025-03-07T15:07:30.942274+08:00 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_mz00_4138.trc: ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log' 2025-03-07T15:07:30.945509+08:00 Slave encountered ORA-10388 exception during crash recovery 2025-03-07T15:07:30.945512+08:00 Slave encountered ORA-10388 exception during crash recovery 2025-03-07T15:07:30.948369+08:00 Aborting crash recovery due to error 314 2025-03-07T15:07:30.948488+08:00 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4106.trc: ORA-00314: log 2 of thread 1, expected sequence# 2966 doesn't match 1646 ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log' 2025-03-07T15:07:30.949390+08:00 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4106.trc: ORA-00314: log 2 of thread 1, expected sequence# 2966 doesn't match 1646 ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log' ORA-314 signalled during: ALTER DATABASE OPEN...
使用Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)脚本收集信息之后数据文件头状态和所需要redo信息
数据库需要sequence#为2966的redo日志,但是当前已经被删除,基于当前情况,只能进行强制非一致性恢复,尝试强制打开库
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00314: log 2 of thread 1, expected sequence# 2966 doesn't match 1646
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'
QL> select group#,status,sequence# from v$log;
GROUP# STATUS SEQUENCE#
---------------- ---------------- ----------------
1 UNUSED 0
3 CURRENT 2967
2 ACTIVE 2966
SQL>
SQL>
SQL> recover database until cancel;
ORA-00279: change 163033183 generated at 03/07/2025 14:04:20 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/recovery_area/orcl/archivelog/2025_03_08/o1_mf_1_2966_%u_.arc
ORA-00280: change 163033183 for thread 1 is in sequence #2966
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
Database altered.
运气不错,直接打开数据库成功,然后逻辑导出数据,完成此处恢复.这个让我想起来了一些类似案例:
Oracle 23ai rm redo*.log恢复
清空redo,导致ORA-27048: skgfifi: file header information is invalid
由于默认情况下oracle的redo文件扩展名是.log,然后被当做是不重要文件从而被清理导致数据库故障,在oracle服务器上清理数据之前建议查询v$datafile,v$logfile,v$tempfile,v$controlfile来确认是否是数据库文件
