Recovery from missing or corrupted redo log group:

Case 1: A multiplexed copy of the missing log is available.


if a redo log is missing, it should be restored from a multiplexed copy, if possible. Here's an example, where I attempt to startup from SQLPlus when a redo log is missing:

SQL> startup


ORACLE instance started.
Total System Global Area 131555128 bytes
Fixed Size 454456 bytes
Variable Size 88080384 bytes
Database Buffers 41943040 bytes
Redo Buffers 1077248 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'D:\ORACLE_DATA\LOGS\ORCL\REDO03.LOG'

SQL>

To fix this we simply copy REDO03.LOG from its multiplexed location on E: to the above location on D:.

SQL> alter database open;

Database altered.

SQL>

That's it - the database is open for use.



Case 2: Only A redo log file backup copy available

If a redo log is missing, it should be restored from a Cold backup (if redo log backup available in Sunday Cold Backup) if possible. Here's an example, where I attempt to startup from SQLPlus when a redo log is missing:

SQL> startup

ORACLE instance started.



Total System Global Area 122755896 bytes

Fixed Size 453432 bytes

Variable Size 67108864 bytes

Database Buffers 54525952 bytes

Redo Buffers 667648 bytes

Database mounted.

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: 'C:\O\ORADATA\REDO01.LOG'



SQL>

To fix this we simply copy REDO01.LOG from Cold Backup.

SQL> alter database clear unarchived logfile group 1;

SQL> alter database open;

Database altered.
SQL>

That's it - the database is open for use.



Case 3: All redo log file or any one redo log file missing and we have no backup copy of redo log file or no multiplexing redo log file.

If all or some redo log is missing. Here's an example, where I attempt to startup from SQLPlus when a redo log is missing:

SQL> startup

ORACLE instance started.



Total System Global Area 122755896 bytes

Fixed Size 453432 bytes

Variable Size 67108864 bytes

Database Buffers 54525952 bytes

Redo Buffers 667648 bytes

Database mounted.

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: 'C:\O\ORADATA\REDO01.LOG'



SQL> recover database until cancel;

Media recovery complete.



SQL> alter database open resetlogs;



Database altered.

That's it - the database is open for use.