Data Recovering from One Inactive Online Redo Log Missing

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. Shut down the database with this SQLPLUS command:

   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 with these 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. Delete the damaged online redo log files in one of the following ways:

   • As a group:

     alter database drop logfile group ;

   • As individual files:

     alter database drop logfile member '' [,''];

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

   alter database add logfile ''[,''] to group ;

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

   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 has a gap, which means that – in the event of another media error – only an incomplete recovery is possible.