ORA-00354 corrupt redo log block header

I ran in to an issue recently where a database was reporting ORA-00257 archiver error, connect internal only, until freed. However when looking at the system I could not find evidence to support the ORA-00257.

After validating that the file system did have space in which to write the archive logs I took a quick look at the alert log for the database. Unfortunately, it too, was saying that there were no valid locations in which to write the logs.

Errors in file /u01/app/oracle/diag/rdbms/orcl/trace/orcl_arc0_4789.trc:
ORA-16014: log 3 sequence# 6231 not archived, no available destinations
ORA-00312: online log 3 thread 1: '/u02/oradata/orcl/redo/redo03a.log'
ARC0: Evaluating archive   log 3 thread 1 sequence 6231
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive log 3 thread 1 sequence 6231
Mon Sep 26 08:28:07 2011
Errors in file /u01/app/oracle/diag/rdbms/orcl/trace/orcl_arc0_4789.trc:
ORA-16014: log 3 sequence# 6231 not archived, no available destinations
ORA-00312: online log 3 thread 1: '/u02/oradata/orcl/redo/redo03a.log'
ARC0: Evaluating archive   log 3 thread 1 sequence 6231

I then decided to perform a manual switch of the logs to force an archive.

SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-16038: log 3 sequence# 6231 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 3 thread 1: '/u02/oradata/orcl/redo/redo03a.log'

SQL>

Now the problem appears. Since this database does not have mirrored archive logs the corrupt redo log will need to be cleared. An un-archived redo file can be cleared with the ALTER DATABASE CLEAR UNARCHIVED LOGFILE command.

SQL> alter database clear unarchived logfile '/u02/oradata/orcl/redo/redo03a.log';

Database altered.

SQL>

After clearing the corrupt redo log the system will be able to write archive logs. You should take a backup of the database after clearing a log file. Keep in mind if dataguard is implemented you will have a non recoverable archive gap.

If your system has the redo logs multiplexed, see the post: Multiplexing the Redo Logs, you might not be required to clear an un-archived log file. Use the command ALTER SYSTEM DUMP LOGFILE to see which member has the corruption.

SQL> alter system dump logfile '/u02/oradata/orcl/redo/redo03a.log';
alter system dump logfile '/u02/oradata/orcl/redo/redo03a.log
*
ERROR at line 1:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 33524 change 60159303 time 09/26/2011 08:32:03
ORA-00312: online log 3 thread 1: '/u02/oradata/orcl/redo/redo03a.log'


SQL>

If the log file passed to ALTER SYSTEM DUMP LOGFILE is corrupt it will give the ORA-00354. All that needs to be done to resolve the issue then is to shutdown the database and replace the corrupted redo log with the uncorrupted multiplexed copy.

If you find that all multiplexed members are corrupt you will need to issue the ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP command.

In either case, if you are unable to clear the un-archived redo log(s) the only option left is to perform incomplete recovery. If incomplete recovery is needed the data in the corrupted redo logs will be lost.