LLI Wiki — My Digital Knowledge Base

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

  1. 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
    
  2. Take a full database backup with RMAN to another storage location.

  3. 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;.

  4. 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;
    
Mastodon LLIMastodon SocialFosstodon