Effective Tips for Fixing Online Redo Log Corruption!!

Author: Alex Lima

In this article we will discuss a scenario where your database encounters some issue as a result of which your datafiles and redo log files get corrupted.

You can fix corrupted datafiles by first recreating all indexes from corrupted datafile in a new tablespace and then dropping your corrupted tablespace.

The corruption of online redo log hangs the archived log process as it will not be able to archive the log that got corrupted. In such situation you will have to clear the redo log and manually switch the logs to test the fix.

V$LOG View:

V$LOG is a data dictionary view that contains information on each log group. Our archive log process got hanged because the online redo log could not be archived due to corruption. We will view the v$log view. Our log 232904 is corrupted and can not be archived.

SELECT GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARC,STATUS FROM V$LOG;

   GROUP#   THREAD#  SEQUENCE#     BYTES          MEMBERS      ARC       STATUS
   ————     ————     —————-     —————-     ————-      ——       —————-
         1                1                  232953        215691831          2                   NO        CURRENT
         2                1                  232951        215691831          2                   NO        INACTIVE
         3                1                  232909        215691831          2                   NO        INACTIVE
         4                1                  232904        215691831          2                   NO        INACTIVE
         5                1                  232952        215691831          2                   NO        INACTIVE
         6                1                  232950        215691831          2                   NO        INACTIVE
         7                 1                232951        215691831          2                   NO        INACTIVE
7 rows selected.

Clear Online Redo Log:

Now we will run below query to clear the online redo log. Here we can see that we can not clear the online redo log because our datafile is corrupted.

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4
*
ERROR AT LINE 1:
ORA-00393: LOG 4 OF THREAD 1 IS NEEDED FOR RECOVERY OF OFFLINE DATAFILES
ORA-00312: ONLINE LOG 4 THREAD 1: ‘/ORACLE/EPB/WISPRD/REDO/REDO2/REDO4B.LOG'
ORA-00312: ONLINE LOG 4 THREAD 1: ‘/ORACLE/EPB/WISPRD/REDO/REDO1/REDO4A.LOG'
ORA-01110: DATA FILE 281: ‘/ORACLE/EPB/WISPRD/DATA/CUST_INDEX_JUL2007.DBF'

Force Online Redo Log Clear:

Now we will run an additional clause so as to force the CLEAR on our Online Redo Log. This will clear our online redo log but the corrupted log will not be recovered.

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4 UNRECOVERABLE DATAFILE;
Database altered.

Full Snap Backup:

Now we will take a full snap backup so as to recover the corrupted archive log.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARC,STATUS FROM V$LOG;

   GROUP#   THREAD#  SEQUENCE#     BYTES          MEMBERS      ARC       STATUS
   ————     ————     —————-     —————-     ————-      ——       —————-
         1                1                  232953        215691831          2                   NO       INACTIVE
         2                1                  232951        215691831          2                   YES     INACTIVE
         3                1                  232955        215691831          2                   NO       CURRENT
         4                1                        0              215691831          2                   YES     UNUSED
         5                1                  232952        215691831          2                   YES      INACTIVE
         6                1                  232920        215691831          2                   NO        INACTIVE
         7                 1                232951        215691831          2                   NO        INACTIVE
7 rows selected.

SQL> ALTER SYSTEM SWITCH LOGFILE;

SYSTEM ALTERED.
SQL> /
SYSTEM ALTERED.
SQL> /
SYSTEM ALTERED.
SQL> /
SYSTEM ALTERED.
SQL> /
SYSTEM ALTERED.
SQL> /
SYSTEM ALTERED.
SQL> /
SYSTEM ALTERED.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARC,STATUS FROM V$LOG;

   GROUP#   THREAD#  SEQUENCE#     BYTES          MEMBERS      ARC       STATUS
   ————     ————     —————-     —————-     ————-      ——       —————-
         1                1                  232959        215691831          2                   YES       INACTIVE
         2                1                  232957        215691831          2                   YES       INACTIVE
         3                1                  232955        215691831          2                   YES       INACTIVE
         4                1                  232920        215691831          2                   YES       ACTIVE
         5                1                  232958        215691831          2                   YES       INACTIVE
         6                1                  232921        215691831          2                   NO        CURRENT
         7                 1                 232951        215691831          2                   NO        INACTIVE
7 rows selected.

Drop Database:

Now we will drop the corrupted tablespace.

SQL> DROP TABLESPACE CUST_INDEX_JUL2007 INCLUDING CONTENTS;
Tablespace dropped.

V$DATAFILE Status:

Oracle v$datafile view contains datafile information from the control file. Run below command to check the datafile status. You should only see online and system.

SELECT DISTINCT STATUS FROM V$DATAFILE;

STATUS
————-
ONLINE
SYSTEM

Full Backup:

Finally we will take a full backup and you will be done with it.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Oracle – Availing The Assistance Of Wget For Patch Download
   Oracle Recovery Manager - RMAN
   Beware of Worse Oracle performance after Migration!!
   Database Guide: Beware of Fetch across Commit!!