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#;