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
- Shut the database down, then start it in
mountmode:
shutdown immediate
startup mount;
- If the database is in
ARCHIVELOGmode, try disabling archiving (reference):
ALTER DATABASE NOARCHIVELOG;
- 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;
- 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;
- Open the database:
alter database open;
- If that fails, try
resetlogs:
alter database open resetlogs;
- 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.
- Either in the parameter file:
STARTUP PFILE = /u01/oracle/dbs/init.ora
- Or through the spfile:
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
- If the database opens after these steps, immediately take a full export, then recreate the database and import the data.
- Always have a backup before intervening.