Backup Options   «Prev  Next»

Lesson 4Clearing corrupt Online Redo Log Files
ObjectiveDescribe the Application of Syntax to clear Redo Log Files

Clearing Corrupt online Redo Log Files in Oracle


The "ALTER DATABASE CLEAR LOGFILE" command is no longer used to clear corrupt online redo log files in Oracle. This command was deprecated in Oracle 12c and removed in Oracle 18c. Instead, you should use the "ALTER SYSTEM DROP REDO LOGFILE" command to drop the corrupt log file. This will automatically clear the log file and make it available for reuse. Here is an example of how to use the "ALTER SYSTEM DROP REDO LOGFILE" command: 
ALTER SYSTEM DROP REDO LOGFILE group 12;

This command will drop the redo log file in group 12. If the log file is corrupt, it will be cleared and made available for reuse. If you have a large number of corrupt log files, you can use the "ALTER SYSTEM DROP REDO LOGFILE ALL" command to drop all of the log files. Here is an example of how to use the "ALTER SYSTEM DROP REDO LOGFILE ALL" command: 
ALTER SYSTEM DROP REDO LOGFILE ALL;

This command will drop all of the redo log files. If any of the log files are corrupt, they will be cleared and made available for reuse. It is important to note that dropping a redo log file will make any changes that were not yet committed to the database unavailable. Therefore, you should only drop redo log files if you are sure that you do not need the data that they contain.

Media Failure damages the Online Redo Log (Legacy)

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.


Steps to clear damaged online redo log files

1) If a media failure happens, check if the media failure is permanent or temporary.
1) If a media failure happens, check if the media failure is permanent or temporary.

2) If the media failure is temporary, simply have the hardware problem fixed
2) If the media failure is temporary, simply have the hardware problem fixed

3) If the media failure is permanent, check if the database is open. If not, mount the database.
3) If the media failure is permanent, check if the database is open. If not, mount the database.

4) Check if the damaged online redo log is archived
4) Check if the damaged online redo log is archived . If not issue the 
ALTER DATABASE CLEAR UNARCHIVED LOGFILE <group_number>;
command

5) If the damaged online redo log is archived, use the ALTER DATABASE CLEAR LOGFILE <group_number>; command
5) If the damaged online redo log is archived, use the 
ALTER DATABASE CLEAR LOGFILE <group_number>;

command

ALTER DATABASE CLEAR LOGFILE GROUP in Oracle 12c

While the `ALTER DATABASE CLEAR LOGFILE GROUP` command still exists in Oracle 12c, it's important to use it with caution and understand its limitations before considering it for clearing corrupt Online Redo Log Files (ORLs). Here's a breakdown:
The command can be used for clearing corrupt ORLs in specific situations. However, there are significant factors to consider:
  • Alternatives: In most cases, other methods like dropping and adding a new log member within the group, or switching logs and then clearing the inactive one, are generally preferred over `CLEAR LOGFILE GROUP`. These methods have less impact on your ability to recover from crashes or media failures.
  • Data Loss: Using `CLEAR LOGFILE GROUP` permanently erases the unarchived redo information in the corrupted log. This renders any backups based on that specific log unusable for recovery.
  • Incomplete Recovery: If the corrupted log was needed for recovering a datafile, clearing it completely eliminates the possibility of bringing that datafile online again. You'd have to drop the datafile or perform an incomplete recovery, leading to potential data loss.
  • Limited Use Cases: The command is mainly helpful in rare situations where:
    • Only two log groups exist: Dropping a single log member is impossible in this case.
    • The current log in a closed thread is corrupt: This scenario prevents switching logs, leaving `CLEAR LOGFILE GROUP` as the only option.
    • I/O errors affect only one member: Dropping that member and adding a new one might be preferable.

Recommendations:
  • Consult with a qualified Oracle DBA before using `CLEAR LOGFILE GROUP` for corrupt ORLs.** They can assess your specific situation and suggest the safest and most appropriate course of action.
  • Consider alternative methods like dropping and adding new log members or switching logs whenever possible.
  • Always ensure your databases have proper backup strategies in place to minimize data loss risks.

Remember: Clearing unarchived redo information is a serious action with potential consequences. It's vital to understand the risks and potential data loss involved before proceeding.
The 
ALTER DATABASE CLEAR LOGFILE GROUP

command drops the corrupted online redo file and recreates it without shutting down the database.
  1. Advantages of ALTER DATABASE CLEAR LOGFILE command: 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.
  2. Disadvantages of ALTER DATABASE CLEAR LOGFILE 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.

Corrupted online redo logs for offline Datafiles

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.

SEMrush Software