场景:
redo日志全部丢失的场景。
alert日志报错如下:
ORA-00313: 无法打开日志组 1 (用于线程 1) 的成员
ORA-00312: 联机日志 1 线程 1: '/u01/app/oradata/oradb/redo01.log'
ORA-27037: 无法获得文件状态
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Thu Oct 15 16:22:08 2020
Errors in file /u01/app/diag/rdbms/oradb/oradb/trace/oradb_m000_2835.trc:
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: '/u01/app/oradata/oradb/redo01.log'
Errors in file /u01/app/diag/rdbms/oradb/oradb/trace/oradb_m000_2835.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oradata/oradb/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Completed: ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1
Errors in file /u01/app/diag/rdbms/oradb/oradb/trace/oradb_m000_2835.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oradata/oradb/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Checker run found 6 new persistent data failures
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2
Clearing online log 2 of thread 1 sequence number 4430
Errors in file /u01/app/diag/rdbms/oradb/oradb/trace/oradb_ora_2826.trc:
ORA-00313: 无法打开日志组 2 (用于线程 1) 的成员
ORA-00312: 联机日志 2 线程 1: '/u01/app/oradata/oradb/redo02.log'
ORA-27037: 无法获得文件状态
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/diag/rdbms/oradb/oradb/trace/oradb_ora_2826.trc:
ORA-00313: 无法打开日志组 2 (用于线程 1) 的成员
ORA-00312: 联机日志 2 线程 1: '/u01/app/oradata/oradb/redo02.log'
也可以看到redo在线日志丢失。
这里可以看看redo日志的位置和状态:
SYS@oradb> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------
NEXT_CHANGE# NEXT_TIME
------------ --------------
1 1 4429 52428800 512 1 YES INACTIVE 1.0235E+13 15-10月-20
1.0235E+13 15-10月-20
3 1 4431 52428800 512 1 NO CURRENT 1.0235E+13 15-10月-20
2.8147E+14
2 1 4430 52428800 512 1 YES INACTIVE 1.0235E+13 15-10月-20
1.0235E+13 15-10月-20
SYS@oradb> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
------------------------------------------------------------------------------------------------------------------------
IS_
---
3 ONLINE
/u01/app/oradata/oradb/redo03.log
NO
2 ONLINE
/u01/app/oradata/oradb/redo02.log
NO
1 ONLINE
/u01/app/oradata/oradb/redo01.log
NO
可以看到group1和group2是inactive。可以通过如下命令恢复重建
SYS@oradb> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
数据库已更改。
SYS@oradb> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
数据库已更改。
但是，对于current的redo日志来说，就是很大的问题了。
这里是通过一个实例恢复的方式，命令如下:
正常关闭数据库
SYS@oradb> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
启动数据库至mount状态，恢复数据库实例
SYS@oradb> startup mount;
ORACLE 例程已经启动。
Total System Global Area 1.0260E+10 bytes
Fixed Size 2347880 bytes
Variable Size 2583694488 bytes
Database Buffers 7667187712 bytes
Redo Buffers 6402048 bytes
数据库装载完毕。
SYS@oradb> recover database until cancel;
完成介质恢复。
SYS@oradb> alter database open resetlogs;
NOTE: If the current online log, needed for instance recovery, is lost, the database must be restored and recovered through the last available archivelog file(PITR).
至此，模拟环境的oracle恢复成功，文献参考:
How to Recover from Loss Of Online Redo Log And ORA-312 And ORA-313 (Doc ID 117481.1) To BottomTo Bottom