LLI Wiki — My Digital Knowledge Base

Oracle: redo log corruption (ORA-00353, ORA-00368)

Diagnose and handle an Oracle redo log corruption: drop and re-add a group, clear logfile, resetlogs.

This guide explains how to diagnose and handle a corrupted Oracle redo log file, reported by errors such as:

ORA-00368: checksum error in redo log block
ORA-00353: log corruption near block 19629 change 724380461 time 03/01/2021 16:42:13
ORA-00312: online log 1 thread 1: '/u01/oradata/ORCL/redo01.log'

These are sometimes accompanied by ORA-00354 (corrupt redo log block header) or ORA-01624 (log needed for crash recovery). The procedures below go from least to most risky.

Connect

sqlplus /nolog
CONNECT / AS SYSDBA

Gather information

-- Redo log files
select * from v$logfile;

-- Details: group, member, status, size in MB
select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) fsize
from
v$log l, v$logfile f
where f.group# = l.group#
order by 1,2;

-- Log mode
select log_mode from v$database;

With RMAN:

rman target /
list failure

Confirm the corruption

ALTER SYSTEM DUMP LOGFILE ... VALIDATE validates a redo log member:

alter system dump logfile '+DATA/orcl/onlinelog/group_43.258.875922633' validate;

Resolution

  1. Shut the database down, then start it in mount mode:
shutdown immediate
startup mount;
  1. If the database is in ARCHIVELOG mode, try disabling archiving (reference):
ALTER DATABASE NOARCHIVELOG;
  1. Force a log switch and identify the corrupted group (check the alert log for the group named in the error):
alter system switch logfile;

set lines 500 pages 500
select * from v$logfile;
  1. Drop the corrupted group and add it again:
alter database drop logfile group 4;
alter database add logfile group 4 '/u02/oradata/orcl/redo4a.log' size 250M reuse;
  1. Open the database:
alter database open;
  1. If that fails, try resetlogs:
alter database open resetlogs;
  1. Otherwise, clear the redo group (see the alert log for the affected group, and the query above):
ALTER DATABASE CLEAR LOGFILE GROUP <group_number>;
alter database clear unarchived logfile group 1;
alter database clear unarchived logfile '/u01/oradata/ORCL/redo01.log';

See also this article on ORA-00354.

Last resort: _allow_resetlogs_corruption

This option is unsupported and dangerous. It can leave the database inconsistent.

STARTUP PFILE = /u01/oracle/dbs/init.ora
alter system set "_allow_resetlogs_corruption"=true scope=spfile;

Then:

startup mount force;
alter database open resetlogs;

recover until cancel can also be attempted, with great caution.

If needed, restart cleanly:

startup nomount force;
startup mount force;
select * from v$logfile;
alter database open resetlogs;

Notes

Mastodon LLIMastodon SocialFosstodon