CLEAR REDO LOG FILE command in Oracle

CLEAR REDO LOG FILE command in Oracle

A redo log file is need to be cleared when its corrupted without restarting the database.
You can clear the redo log file without shutdowning the database.
ALTER DATABASE CLEAR LOGFILE GROUP 3;
Note: If the corrupt redo log file has not been archived, use the UNARCHIVED keyword:
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

Error:
ORA-00312: online log 6 thread 1: 'C:\ORACLE\ORADATA\REDO01.LOG'

Solution:

  1. Try the command if it worked then good otherwise continue with step 2.
    ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 6;
  2. Shutdown the database.
  3. Start the database at mount stage
  4. Clear unarchive log file
    ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 6;
  5. Start again the database in open.
  6. Check the status of redo log files:
    SELECT GROUP#,L.STATUS,V.MEMBER,L.SEQUENCE# FROM V$LOG L JOIN V$LOGFILE V USING (GROUP#) ORDER BY GROUP#;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.