When starting one of the databases we received the ORA-00353 error. It indicates that archiver process couldn’t archive one of the redo logs due to corruption. Oracle tried to read both the logmembers from the same group but they both were corrupted and instance crashes when trying to open it.
ARC0: STARTING ARCH PROCESSES COMPLETE
ARCH: Log corruption near block 88727 change 12629319 time ?
CORRUPTION DETECTED: thread 1 sequence 221 log 2 at block 88727. Arch found corrupt blocks
Errors in file i:\db\oracle\testdb\diagnostic_dest\diag\rdbms\testdb_a\testdb\trace\testdb_ora_3796.trc (incident=25175):
ORA-00353: log corruption near block 88727 change 12629319 time 05/07/2012 21:13:47
ORA-00312: online log 2 thread 1: ‘I:\DB\ORACLE\TESTDB\ORADATA\REDO02B.LOG’
ORA-00312: online log 2 thread 1: ‘J:\DB\ORACLE\TESTDB\ORADATA\REDO02A.LOG’
…
USER (ospid: 3796): terminating the instance due to error 16038
Instance terminated by USER, pid = 3796
So to confirm the first step was after database was open in mount state to verify both online redo logfiles in the group had corruption.
SQL> ALTER SYSTEM DUMP LOGFILE ‘I:\DB\ORACLE\TESTDB\ORADATA\REDO02B.LOG’;
ALTER SYSTEM DUMP LOGFILE ‘I:\DB\ORACLE\TESTDB\ORADATA\REDO02B.LOG’
*
ERROR at line 1:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 88727 change 12629410 time 05/08/2012
09:01:28
ORA-00334: archived log: ‘I:\DB\ORACLE\TESTDB\ORADATA\REDO02B.LOG’
SQL> ALTER SYSTEM DUMP LOGFILE ‘J:\DB\ORACLE\TESTDB\ORADATA\REDO02A.LOG’;
ALTER SYSTEM DUMP LOGFILE ‘J:\DB\ORACLE\TESTDB\ORADATA\REDO02A.LOG’
*
ERROR at line 1:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 88727 change 12629410 time 05/08/2012
09:01:28
ORA-00334: archived log: ‘I:\DB\ORACLE\TESTDB\ORADATA\REDO02A.LOG’
In this case both files had corruption but note if only of them had corruption you one can copy the file that is no corruption to the corrupted one.
After it was confirmed both files had corruption, using the following steps one can startup the database it will require changing the database to noarchivelog mode so the archiver process doesn’t try to archive the online redo logs. Note: You should take a full backup before you start this step and after this step is done.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 388354048 bytes
Fixed Size 2175968 bytes
Variable Size 331353120 bytes
Database Buffers 50331648 bytes
Redo Buffers 4493312 bytes
Database mounted.
SQL> ALTER DATABASE NOARCHIVELOG;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 388354048 bytes
Fixed Size 2175968 bytes
Variable Size 331353120 bytes
Database Buffers 50331648 bytes
Redo Buffers 4493312 bytes
SQL> alter database mount;
Database altered.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
alexlima.com 