Thursday, November 26, 2015

ORA-00354: corrupt redo log block header

If you see ORA-00354 in your alert log file, as can be seen bellow
Tue Nov 24 14:36:25 2015
Thread 1 cannot allocate new log, sequence 16492
Private strand flush not complete
  Current log# 9 seq# 16491 mem# 0: D:\ORACLE\ORADATA\MYDB\REDO9_01.RDO
Thread 1 advanced to log sequence 16492
  Current log# 6 seq# 16492 mem# 0: D:\ORACLE\ORADATA\MYDB\REDO6_01.RDO
Tue Nov 24 14:36:27 2015
ARCH: Archival stopped, error occurred. Will continue retrying
Tue Nov 24 14:36:27 2015
Errors in file d:\oracle\admin\MYDB\bdump\MYDB_arc0_3148.trc:
ORA-16014: log 3 sequence# 16454 not archived, no available destinations
ORA-00312: online log 3 thread 1: 'D:\ORACLE\ORADATA\MYDB\REDO3_01.RDO'

Tue Nov 24 14:37:30 2015
Thread 1 cannot allocate new log, sequence 16493
Private strand flush not complete
  Current log# 6 seq# 16492 mem# 0: D:\ORACLE\ORADATA\MYDB\REDO6_01.RDO
Thread 1 advanced to log sequence 16493
  Current log# 4 seq# 16493 mem# 0: D:\ORACLE\ORADATA\MYDB\REDO4_01.RDO
Tue Nov 24 14:37:55 2015
ARC1: Log corruption near block 132988 change 521383134 time ?
Tue Nov 24 14:37:55 2015
Errors in file d:\oracle\admin\MYDB\bdump\MYDB_arc1_6612.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 132988 change 521383134 time 11/17/2015 04:06:08
ORA-00312: online log 3 thread 1: 'D:\ORACLE\ORADATA\MYDB\REDO3_01.RDO'

ARC1: All Archive destinations made inactive due to error 354
ARCH: Archival stopped, error occurred. Will continue retrying
Tue Nov 24 14:37:55 2015
Errors in file d:\oracle\admin\MYDB\bdump\MYDB_arc1_6612.trc:
ORA-16038: log 3 sequence# 16454 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 3 thread 1: 'D:\ORACLE\ORADATA\MYDB\REDO3_01.RDO'

Trace file as highlighted above might look similar to the following.
kcrrfail: dest:1 err:354 force:0 blast:1
*** 2015-11-24 14:33:55.237 20216 kcrr.c
ORA-16038: log 3 sequence# 16454 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 3 thread 1: 'D:\ORACLE\ORADATA\MYDB\REDO3_01.RDO'
*** 2015-11-24 14:35:55.237
*** 2015-11-24 14:35:55.237 60653 kcrr.c
ARC1: Log corruption near block 132988 change 521383134 time ?
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 132988 change 521383134 time 11/17/2015 04:06:08
ORA-00312: online log 3 thread 1: 'D:\ORACLE\ORADATA\MYDB\REDO3_01.RDO'
*** 2015-11-24 14:35:55.237 60653 kcrr.c
ARC1: All Archive destinations made inactive due to error 354
*** 2015-11-24 14:35:55.237 58915 kcrr.c
kcrrfail: dest:1 err:354 force:0 blast:1
*** 2015-11-24 14:35:55.253 20216 kcrr.c
ORA-16038: log 3 sequence# 16454 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 3 thread 1: 'D:\ORACLE\ORADATA\MYDB\REDO3_01.RDO'
*** 2015-11-24 14:36:55.128
*** 2015-11-24 14:36:55.128 20216 kcrr.c
*** 2015-11-24 14:37:55.253
*** 2015-11-24 14:37:55.253 60653 kcrr.c
ARC1: Log corruption near block 132988 change 521383134 time ?
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 132988 change 521383134 time 11/17/2015 04:06:08
ORA-00312: online log 3 thread 1: 'D:\ORACLE\ORADATA\MYDB\REDO3_01.RDO'
*** 2015-11-24 14:37:55.253 60653 kcrr.c
ARC1: All Archive destinations made inactive due to error 354
*** 2015-11-24 14:37:55.253 58915 kcrr.c
kcrrfail: dest:1 err:354 force:0 blast:1
*** 2015-11-24 14:37:55.268 20216 kcrr.c
ORA-16038: log 3 sequence# 16454 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 3 thread 1: 'D:\ORACLE\ORADATA\MYDB\REDO3_01.RDO'
*** 2015-11-24 14:39:55.268
*** 2015-11-24 14:39:55.268 60653 kcrr.c
ARC1: Log corruption near block 132988 change 521383134 time ?
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 132988 change 521383134 time 11/17/2015 04:06:08
ORA-00312: online log 3 thread 1: 'D:\ORACLE\ORADATA\MYDB\REDO3_01.RDO'
*** 2015-11-24 14:39:55.268 60653 kcrr.c
ARC1: All Archive destinations made inactive due to error 354
*** 2015-11-24 14:39:55.268 58915 kcrr.c
kcrrfail: dest:1 err:354 force:0 blast:1
*** 2015-11-24 14:39:55.284 20216 kcrr.c
ORA-16038: log 3 sequence# 16454 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 3 thread 1: 'D:\ORACLE\ORADATA\MYDB\REDO3_01.RDO'

It clearly means that there is corruption in the redo log file belonging to group 3. Although current log sequence number is 16492, but sequence number 16454 has not been archived because only member of log group 3 is corrupt and hence cannot be archived. Following query also confirms that sequence 16454 has not been archived.
SQL> SELECT sequence#, name FROM V$ARCHIVED_LOG ORDER BY 1;
SEQUENCE# NAME
---------- ------------------------------------------------------------------------------
     16452 D:\ORACLE\ADMIN\MYDB\ARCHIVE\MYDBT001S164520609542238.ARC
     16453 D:\ORACLE\ADMIN\MYDB\ARCHIVE\MYDBT001S164530609542238.ARC
--MISSING SEQUENCE 16445
     16455 D:\ORACLE\ADMIN\MYDB\ARCHIVE\MYDBT001S164550609542238.ARC
     16456 D:\ORACLE\ADMIN\MYDB\ARCHIVE\MYDBT001S164560609542238.ARC
     16482 D:\ORACLE\ADMIN\MYDB\ARCHIVE\MYDBT001S164820609542238.ARC
     16483 D:\ORACLE\ADMIN\MYDB\ARCHIVE\MYDBT001S164830609542238.ARC
     16484 D:\ORACLE\ADMIN\MYDB\ARCHIVE\MYDBT001S164840609542238.ARC
     16485 D:\ORACLE\ADMIN\MYDB\ARCHIVE\MYDBT001S164850609542238.ARC
     16486 D:\ORACLE\ADMIN\MYDB\ARCHIVE\MYDBT001S164860609542238.ARC
     16487 D:\ORACLE\ADMIN\MYDB\ARCHIVE\MYDBT001S164870609542238.ARC


Cause
This issue could be caused by storage or hardware issue causing redo logs to be corrupted. have a thorough check of the hardware and storage. OS logs may or may not show issues with the hardware or storage for this scenario.

Solution
You should always have multiple redo log member in each redo log group so that if one logfile member is corrupt, other member could be used to archive the log data. For this scenario since there is only one member, the solution is to clear this redo log file group so that it can be re-used again.
SQL> alter database clear unarchived logfile group 2;

Database altered.

Important Note: Take full database backup immediately after clearing the redo log group because archived log for sequence 16454 is not available because of redo log corruption and recovery would not be possible if a medial failure occurs at this point.

No comments:

Popular Posts - All Times