Redo log corruption while the database is open
Redo log corruption errors, clearing or recreating the log group, and incomplete recovery.
Redo log corruption can be reported by these errors:
ORA-16038 log %s sequence# %s cannot be archived
ORA-354 corrupt redo log block header
ORA-353 log corruption near block <num> change <str> time <str>
ORA-367 checksum error in log file header
ORA-368 checksum error in redo log block
When the corrupt group is the current one, a clean startup and a log switch may both fail. Only an INACTIVE group can be cleared and dropped; a CURRENT or ACTIVE group cannot:
alter database clear logfile group 1;
alter database clear unarchived logfile group 1;
Procedure
-
Check the log sequence numbers:
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u001/archive/ Oldest online log sequence 250 Next log sequence to archive 251 Current log sequence 252 -
Take a full database backup with RMAN to another storage location.
-
Run an incomplete recovery to a sequence number before the corruption appeared in the redo log:
run { set until sequence=245; restore database; recover database; }Transactions after the chosen sequence are lost. The database must then be opened with
alter database open resetlogs;. -
Drop and recreate the redo log group, either in place or at another location.
Option A, clear the group:
select * from v$log; alter system switch log file; alter database clear unarchived logfile group <group number>;If the clear fails, disable the thread and try again:
alter database disable thread <thread number>;Only a group with the INACTIVE status can be cleared or dropped.
Option B, drop and add the group again:
alter database drop logfile group <group number>; alter database add logfile group 1 ('/u001/app/oracle/oradata/redo/redo02.log') size 512M;