Data migration from one database to another database, importing the data & creating the 22000 indexes (login as sys user) on database.
Suddenly session was hanged & indexes also not created. Logging using my account into database throws below error:
ORA-00257:
Archiver error. Connect internal only, until freed.
I was surprised...Because whenever I used huge data import on non production database, I put the shell script to remove the old archive files every 10 minutes. This script keeps last 10 archive log files.
I verified the shell script & it was running fine and also archive log file system have enough space to keep 50 GB archive.
Finally I checked the alertlog file. It contains enough information & helped to identify the issue.
Alert Log Error:
ORA-00353: log corruption near block 655832 change 10092289716415 time 04/27/2011 22:58:57
ORA-00312: online log 5 thread 1: '/app/oracle/dbdata/data01/test1/otest1RD05.log'
Errors in file /app/oracle/admin/test1/diag/rdbms/test/test1/trace/test1_arc1_3950.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 655832 change 10092289716415 time 04/27/2011 22:58:57
ORA-00312: online log 5 thread 1: '/app/oracle/dbdata/data01/test1/otest1RD05.log'
ARC1: All Archive destinations made inactive due to error 354
Sweep Incident[65571]: completed
Thu Apr 28 02:50:55 2011
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance test1 - Archival Error
ORA-16014: log 5 sequence# 228 not archived, no available destinations
ORA-00312: online log 5 thread 1: '/app/oracle/dbdata/data01/test1/otest1RD05.log'
Errors in file /app/oracle/admin/test1/diag/rdbms/test/test1/trace/test1_arc1_3950.trc:
ORA-16014: log 5 sequence# 228 not archived, no available destinations
ORA-00312: online log 5 thread 1: '/app/oracle/dbdata/data01/test1/otest1RD05.log'
Thu Apr 28 02:56:23 2011
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance test1 - Archival Error
ORA-16038: log 5 sequence# 228 cannot be archived
ORA-00001: unique constraint (.) violated
Errors in file /app/oracle/admin/test1/diag/rdbms/test/test1/trace/test1_arc2_3955.trc:
ORA-16038: log 5 sequence# 228 cannot be archived
ORA-00001: unique constraint (.) violated
ARC1: Log corruption near block 655832 change 10092289716415 time ?
ISSUE:
Redo log group 5 was corrupted & redo log group 5 status also INACTIVE status
.
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ----------
5 1 236 536870912 1 YES INACTIVE
1.0092E+13 2011-04-28
6 1 238 536870912 1 NO CURRENT
1.0092E+13 2011-04-28
7 1 235 536870912 1 YES INACTIVE
1.0092E+13 2011-04-28
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ----------
8 1 237 536870912 1 YES INACTIVE
1.0092E+13 2011-04-28
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> select * from v$logfile where group#=5;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
5 ONLINE
/app/oracle/dbdata/data01/test1/oTEST1RD05.log
NO
While unarchived the logfile group, below information were registered in alert log.
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5
Thu Apr 28 03:02:13 2011
Beginning global checkpoint up to RBA [0xe7.df308.10], SCN: 10092289771542
Completed checkpoint up to RBA [0xe7.df308.10], SCN: 10092289771542
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN
BEFORE 04/27/2011 23:41:29 (CHANGE 10092289717904) CANNOT BE USED FOR RECOVERY.
Clearing online log 5 of thread 1 sequence number 228
Thu Apr 28 03:02:15 2011
ORACLE Instance test1 - Can not allocate log, log 5 still clearing
Thread 1 cannot allocate new log, sequence 232
Thu Apr 28 03:02:15 2011
Archiver process freed from errors. No longer stopped
Online log 5 still clearing
Current log# 7 seq# 231 mem# 0: /app/oracle/dbdata/sys03/test1/oTEST1RD07.log
Thu Apr 28 03:02:39 2011
Beginning log switch checkpoint up to RBA [0xe8.2.10], SCN: 10092289771557
Thread 1 advanced to log sequence 232 (LGWR switch)
Current log# 5 seq# 232 mem# 0: /app/oracle/dbdata/sys01/test1/oTEST1RD05.log
Thu Apr 28 03:02:39 2011
Completed: ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5
Thu Apr 28 03:02:40 2011
Clearing Resource Manager plan via parameter
Thu Apr 28 03:02:50 2011
Archived Log entry 7292 added for thread 1 sequence 230 ID 0xffffffffd697c6bc dest 1:
Thu Apr 28 03:02:55 2011
Archived Log entry 7293 added for thread 1 sequence 229 ID 0xffffffffd697c6bc dest 1:
Thu Apr 28 03:03:19 2011
Archived Log entry 7294 added for thread 1 sequence 231 ID 0xffffffffd697c6bc dest 1:
Thu Apr 28 03:04:43 2011
Reference: http://feedproxy.google.com/~r/blogspot/fnOMR/~3/_qx3Wa1BcAg/ora-00354-corrupt-redo-log-block-header.html?utm_source=feedburner&utm_medium=email
