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:
- Try the command if it worked then good otherwise continue with step 2.
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 6;
- Shutdown the database.
- Start the database at mount stage
- Clear unarchive log file
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 6;
- Start again the database in open.
- 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#;