I faced this issue where in a perticular block 146150 in one of the redo log file got corrupted in one of my database. Please note that below can be implemented for the corrupt redo log block. This involves data loss as we are clearing the redo log which is having issue.I performed this for a test database.
alert log error
====================
Mon May 07 10:54:12 2012
ARC3: Log corruption near block 146150 change 83907949 time ?
CORRUPTION DETECTED: thread 1 sequence 2670 log 3 at block 146150. Arch found corrupt blocks
Errors in file /app/oracle/admin/diag/rdbms/ORCL/ORCL/trace/ORCL_arc3_20151.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 146150 change 83907949 time 04/20/2012 14:00:10
ORA-00312: online log 3 thread 1: '/ora04/oradata/ORCL/redo/g3m2.rdo'
ORA-00312: online log 3 thread 1: '/ora03/oradata/ORCL/redo/g3m1.rdo'
ARC3: All Archive destinations made inactive due to error 354
ARC3: Closing local archive destination LOG_ARCHIVE_DEST_1: '/oraarch/oradata/ORCL/ORCL_1_2670_756578622.arc' (error 354) (ORCL)
SQL> select GROUP#,SEQUENCE#, MEMBERS, STATUS from v$log;
GROUP# SEQUENCE# MEMBERS STATUS
---------- ---------- ---------- ----------------
1 2725 2 ACTIVE
2 2726 2 CURRENT
3 2670 2 INACTIVE
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> select GROUP#,SEQUENCE#, MEMBERS, STATUS from v$log;
GROUP# SEQUENCE# MEMBERS STATUS
---------- ---------- ---------- ----------------
1 2728 2 CURRENT
2 2726 2 ACTIVE
3 2727 2 ACTIVE
SQL> select GROUP#,SEQUENCE#, MEMBERS, STATUS from v$log;
GROUP# SEQUENCE# MEMBERS STATUS
---------- ---------- ---------- ----------------
1 2728 2 ACTIVE
2 2729 2 CURRENT
3 2727 2 ACTIVE
alert log
===========
Tue May 08 13:19:45 2012
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3
Tue May 08 13:19:45 2012
Beginning global checkpoint up to RBA [0xaa6.2d.10], SCN: 85719068
Completed checkpoint up to RBA [0xaa6.2d.10], SCN: 85719068
Completed checkpoint up to RBA [0xaa6.2.10], SCN: 85718985
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN
BEFORE 04/21/2012 06:00:18 (CHANGE 83917294) CANNOT BE USED FOR RECOVERY.
Clearing online log 3 of thread 1 sequence number 2670
Completed: ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3
Tue May 08 13:20:24 2012
Direct NFS: NFS3ERR 2 No such file or directory. path somnas301 mntport 4046 nfsport 2049
Archived Log entry 2702 added for thread 1 sequence 2725 ID 0x361bd8fe dest 1:
Archiver process freed from errors. No longer stopped
This is how issue was resolved.
Thanks Friend, your post is great !
ReplyDeleteThanks Percy.
ReplyDeleteALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2
ReplyDeletedon't work throws an other error
ora-16014