1)查询非当前的联机重做日志文件
sys@ORCL> select group#,sequence#,status,members from v$log;
GROUP# SEQUENCE# STATUS MEMBERS
---------- ---------- ---------------- ----------
1 7 INACTIVE 2
2 8 INACTIVE 2
3 9 CURRENT 2
sys@ORCL> col member for a70 trunc
sys@ORCL> select group#,member from v$logfile where group#=2;
GROUP# MEMBER
---------- ----------------------------------------------------------------------
2 /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_8050hm84_.log
2 /u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_2_8050ho5o_.l
2)模拟文件丢失
sys@ORCL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORCL> host rm /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_8050hm84_.log
sys@ORCL> host rm -rf /u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_2_8050ho5o_.log
sys@ORCL> startup
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219760 bytes
Variable Size 159384400 bytes
Database Buffers 255852544 bytes
Redo Buffers 2973696 bytes
Database mounted.
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/onlinelog/o1_mf_2_8050hm84_.log'
ORA-00312: online log 2 thread 1:
'/u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_2_8050ho5o_.log'
3)修复丢失的联机重做日志文件
sys@ORCL> alter database clear logfile group 2;
Database altered.
sys@ORCL> alter database open;
Database altered.
sys@ORCL> col member for a72 wrapped
sys@ORCL> select group#,member from v$logfile where group#=2;
GROUP# MEMBER
---------- ------------------------------------------------------------------------
2 /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_8244vp4j_.log
2 /u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_2_8244vttx_.log