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#;
This entry was posted in Oracle on by .
Unknown's avatar

About SandeepSingh

Hi, I am working in IT industry with having more than 15 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Leave a Reply