Clearing Corrupt online Redo Log Files in Oracle

If a media failure damages the online redo log files of a database, you have to consider the following two situations in order to determine the appropriate recovery procedure:

1. Is the media failure temporary or permanent?
2. Which online redo log is damaged?
3. Is it the current or the inactive one?
4. Has it been archived yet?

In most situations, the corrupt online redo log files can be recovered by issuing the ALTER DATABASE CLEAR LOGFILE GROUP <group_number>; command with the options UNARCHIVED and UNRECOVERABLE DATAFILE. View the SlideShow below to examine the steps to clear damaged online redo log files.

The ALTER DATABASE CLEAR LOGFILE command can be used in several situations. It is applicable even if there are only two online redo log groups (the minimum needed to keep your database operating). If the corrupt redo log file belongs to the current group, you can still use the command.

However, you can't use the ALTER DATABASE CLEAR LOGFILE command across the board. If the online redo log file is not archived before it is cleared with this command, a complete recovery of the database becomes impossible. You can only recover the database up to the time before this damaged online redo log is needed. So, use the command with the UNARCHIVED option very carefully.
Performing a whole database backup after the command is issued, including the control file, is highly recommended.
You also should not use this command to clear an online redo log file that is required for media ecovery.

If you have an offline datafile and it requires the damaged, unarchived online redo log for recovery, you can still use the ALTER DATABASE CLEAR LOGFILE GROUP <grour_number> UNRECOVERABLE DATAFILE; command to clear the corrupted online redo log file. Be aware that the offline datafile needing this redo log for recovery becomes unusable after this command is issued.
For this procedure to be successful, you have to drop the datafile and its tablespace from the database. To avoid losing the datafile and its tablespace:

1. Bring the offline datafile online
2. Restore all the datafiles, and
3. Perform an incomplete recovery prior to the corrupted redo log file

Either way, it is strongly recommended that you make a backup of the database in case future recovery is necessary.
The next lesson explains how to recover a database with inactive redo logs.