I ran DML operation on database and it failed with exception. I looked for alert log and the entry is ,
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 11037 change 118820174 time 04/22/2008 11:15:12
ORA-00312: online log 2 thread 1: '/oradata1/system/ARJU/redo02.log'
Cause of The Problem:
The redo log file has been corrupted. It may be corrupted through various reasons. Suppose there is disk full or hardware failure.
Solution of The Problem:
Solution 1 or 2 is the option while database is open and solution 3 is the option if database is closed.
Solution 1: Look at the disk space usage containing redo log. If disk is full then free it by removing unwanted files. If disk is ok then try to clear the log file. Here from error we see log 2 is corrupted. So , clear group 2 Like,
SQL>ALTER DATABASE CLEAR LOGFILE GROUP 2;
Clear logfile does the thing of dropping logfile and then create. The advantage of this procedure is it can be used if you have only two redo log group.
Solution 2: If redo log was not archived (query from V$LOG) then you must specify UNARCHIVED LOGFILE. Otherwise error will come.
Try, SQL>ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
After issuing this you have lost archived data, so take backup immediately.
In both 1 and 2 if it was the logfile member of online redo log group then you must issue,
SQL> alter system checkpoint;
after clearing it.
Solution 3: If both does not work then do a point in time recovery.
SQL>RECOVER DATABASE UNTIL CANCEL; CANCEL
SQL>ALTER DATABASE OPEN RESETLOGS;
Point in time recovery is described in DBPITR
If database can't be opened that solution 3 is the only option.
Saturday, May 17, 2008