The Problem
Following errors are reported in the alert log file:
ORA-354: corrupt redo log block header ORA-353: log corruption near block %s change %s time %s ORA-312: online log %s thread %s: '%s' ORA-312: online log %s thread %s: '%s'
ORA-312 reports all the redo log file names for the same redo log group number. Example for redo log group number 3 which has two redo log file members:
ORA-00354: corrupt redo log block header ORA-00353: log corruption near block 1892904 change 878787 time 12/05/2014 13:17:10 ORA-00312: online log 3 thread 1: '/oracle/dbs/log3_A.dbf' ORA-00312: online log 3 thread 1: '/oracle/dbs/log3_B.dbf'
Both online redo log members are reported in the error. Alert log may also contain the next message if all the redo members are indeed corrupt and the database is in archivelog mode:
ARCn: All Archive destinations made inactive due to error 354 .... CORRUPTION DETECTED: thread %d sequence %d log %d at block %d. Arch found corrupt blocks
Example:
ARC1: All Archive destinations made inactive due to error 354 .... CORRUPTION DETECTED: thread 1 sequence 28 log 3 at block 1892904. Arch found corrupt blocks
This indicates a possible corruption in Online Redo Log File Members in a Redo Log Group. If Oracle detects corruption when archiving a log member, it tries to read the same redo block from the second member of the group. If the block is found corrupt in all the members of the redo log group, archiving does not proceed. The cause is normally due to an overwrite/missed due to faulty OS or bad Hardware.
Validate what Redo Log File Members are corrupt
Execute the next statement to confirm that the redo log member is corrupt:
$ sqlplus / as sysdba alter session set tracefile_identifier='VALIDATEREDO'; alter system dump logfile '&name' VALIDATE; oradebug setmypid oradebug tracefile_name
This is to confirm that the redo log member is corrupt; if the redo log file is corrupt it should produce an error. Example:
SQL> alter system dump logfile '/oracle/dbs/log3_A.dbf' validate; alter system dump logfile '/oracle/dbs/log3_A.dbf' validate * ERROR at line 1: ORA-00354: corrupt redo log block header ORA-00353: log corruption near block 1892904 change 869569 time 12/05/2014 13:17:10 ORA-00334: archived log: '/oracle/dbs/log3_A.dbf'
Repeat the same for all the redo log files in the same redo log group number. The next query can be executed to identify the members:
select member from v$logfile where group# = &group_number;
Solution 1
If one of the redo log members is not corrupt then execute the next command several times until the respective redo log group number is in status=CURRENT in view v$log.
alter system switch logfile; select status from v$log where group#=&log_group_number;
Optionally wait until the redo log gets reused and the block will be repaired.
Solution 2
If all redo log members are corrupt, follow the Oracle documentation to “clear [unarchived] logfile group”. After clearing the redo log group, alert Log may be updated with the next message:
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN BEFORE [Timestamp] (CHANGE [scn]) CANNOT BE USED FOR RECOVERY.
Make sure to take a new backup after clearing the redo log group. Also note that if an individual member of a redo log group number cannot be cleared; error ORA-1514 is produced:
SQL> alter database clear unarchived logfile '/oracle/dbs/log3_A.dbf'; alter database clear unarchived logfile '/oracle/dbs/log3_A.dbf' * ERROR at line 1: ORA-01514: error in log specification: no such log ORA-01517: log member: '/oracle/dbs/log3_A.dbf'
This is the expected behavior.