Recovering from One Inactive Online Redo Log Missing 

Use

If only one member of an inactive online redo log group has been lost, you can use the recovery procedure described in Recovering from Current Online Redo Log Missing. Experienced users can correct this error without shutting down the database. For more information, see the Oracle documentation.

The recovery procedure is different when the database pauses because a redo log switch to an online redo log file was unsuccessful. None of the members in this inactive online redo log group can be read or written to.

If the problem is temporary (for example, incorrect access rights), you only need to correct it, and you can then use the group again. If the files have been destroyed, however, the group can not be used again.

No data is lost, providing the missing redo log file was fully backed up and the backup can be read for media recovery.

Procedure

...

       1.      Use the following SQLPLUS command to shut down the database:

shutdown abort

ABORT is needed because the database system cannot perform a proper shutdown, due to the damaged group.

       2.      Find out which file is missing, and check the ALERT and trace files for the reason why the redo log files were lost.

       3.      Mount the database.  Enter the following SQLPLUS commands: 

connect / as sysdba

startup mount

       4.      If you were running the database in ARCHIVELOG mode and archiving of the damaged online redo log group was not complete, you have to temporarily switch to NOARCHIVELOG mode before deleting the defective group, because otherwise the system does not let you delete the files:

alter database noarchivelog;

       5.      You can also delete the damaged online redo log files as a group with

alter database drop logfile group <group number>;

or as individual files with

alter database drop logfile member '<file name>' [,'<file name>'];

       6.      To create the new online redo log files (to replace the damaged ones, which you just deleted), enter:

alter database add logfile '<file name>'[,'<file name>'] to group <group number>;

       7.      If the database was set to NOARCHIVELOG mode during these actions, change it back to ARCHIVELOG mode now:

alter database archivelog;

       8.      If you were running the database in ARCHIVELOG mode, and the archiving of the online redo log group was not complete at the moment the problem occurred, it is essential that you now perform a backup of the entire database. If you do not, the offline redo log chain will have a gap, and only an incomplete recovery would be possible if another media error occurred.