DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00368 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00368: Checksum Error in Redo Log Block — Causes, Fixes & Prevention

ORA-00368 is a critical Oracle error that occurs when the checksum value stored in a redo log block does not match the checksum Oracle recalculates upon reading that block. This mismatch signals data corruption within the redo log, which Oracle uses to guarantee transaction recovery and database consistency. Because redo logs are fundamental to Oracle's crash recovery mechanism, this error demands immediate attention from the DBA.

Top 3 Causes

1. Disk or Storage Hardware Failure

Physical media problems — such as bad sectors, RAID controller faults, or failing HBAs — are the most common root cause. When a write to the redo log is interrupted or corrupted at the hardware level, the block's stored checksum no longer matches its actual content.

-- Check current redo log group and member status
SELECT GROUP#, SEQUENCE#, STATUS, ARCHIVED, MEMBERS
FROM V$LOG
ORDER BY GROUP#;

-- Identify redo log file paths
SELECT GROUP#, MEMBER, STATUS
FROM V$LOGFILE
ORDER BY GROUP#;
Enter fullscreen mode Exit fullscreen mode

2. OS-Level File System Corruption or Abrupt Shutdown

An unexpected power failure, kernel panic, or incomplete file system journal flush can leave a redo log block in a partially written state. Environments using NFS-mounted storage are especially vulnerable due to potential network-level write interruptions.

-- Check archive log history for anomalies
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, ARCHIVED
FROM V$LOG_HISTORY
WHERE FIRST_TIME > SYSDATE - 1
ORDER BY SEQUENCE# DESC;

-- Verify checksum-related initialization parameters
SHOW PARAMETER DB_BLOCK_CHECKSUM;
SHOW PARAMETER DB_LOST_WRITE_PROTECT;
Enter fullscreen mode Exit fullscreen mode

3. Oracle Bug or Misconfigured Parameters

Certain Oracle releases (e.g., 11.2.0.3, 12.1.0.1) have documented bugs where incorrect checksum values are written due to internal memory computation errors. Improper changes to DB_BLOCK_CHECKSUM or DB_LOST_WRITE_PROTECT parameters can also trigger this condition.

-- Recommended checksum parameter settings
ALTER SYSTEM SET DB_BLOCK_CHECKSUM = TYPICAL SCOPE=BOTH;
ALTER SYSTEM SET DB_LOST_WRITE_PROTECT = TYPICAL SCOPE=BOTH;
ALTER SYSTEM SET DB_BLOCK_CHECKING = MEDIUM SCOPE=BOTH;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Fix 1 — Clear an INACTIVE Corrupted Redo Log Group

If the corrupted group is not the current active group, drop and re-create it.

-- Drop the corrupted inactive log group
ALTER DATABASE DROP LOGFILE GROUP 2;

-- Re-add the log group with multiplexed members
ALTER DATABASE ADD LOGFILE GROUP 2
  ('/oradata/redo02a.log', '/oradata/redo02b.log') SIZE 200M;

-- Force a log switch and checkpoint
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;
Enter fullscreen mode Exit fullscreen mode

Fix 2 — Clear a Corrupted CURRENT Redo Log (Emergency)

This is the most critical scenario. Only attempt after confirming your backup status.

-- Shut down and mount the database
SHUTDOWN ABORT;
STARTUP MOUNT;

-- Clear the corrupted current log (WARNING: unrecovered transactions may be lost)
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;

-- Open the database
ALTER DATABASE OPEN;
Enter fullscreen mode Exit fullscreen mode

⚠️ Warning: CLEAR UNARCHIVED LOGFILE discards uncommitted changes in that log. Always verify your RMAN backup before proceeding.

Fix 3 — RMAN Point-in-Time Recovery

If the above steps fail, perform incomplete recovery using RMAN.

RUN {
  SET UNTIL TIME "TO_DATE('2024-01-15 10:00:00','YYYY-MM-DD HH24:MI:SS')";
  RESTORE DATABASE;
  RECOVER DATABASE;
  ALTER DATABASE OPEN RESETLOGS;
}
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

Multiplex Your Redo Logs — Always configure at least two members per redo log group on separate physical disks. This ensures that a single disk failure does not corrupt the only copy of a redo log.

-- Add a second member to each redo log group
ALTER DATABASE ADD LOGFILE MEMBER '/oradata2/redo01b.log' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER '/oradata2/redo02b.log' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '/oradata2/redo03b.log' TO GROUP 3;
Enter fullscreen mode Exit fullscreen mode

Enable Checksums and Automate Validation — Keep DB_BLOCK_CHECKSUM set to TYPICAL or FULL at all times, and schedule regular RMAN validation jobs to catch corruption early before it becomes a crisis.

-- Schedule regular RMAN validation
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

-- Check for corrupt blocks after validation
SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
Enter fullscreen mode Exit fullscreen mode

Related Errors

Error Code Description
ORA-00367 Checksum error in redo log file header (vs. block level for ORA-00368)
ORA-00333 Redo log read error during recovery, often appears alongside ORA-00368
ORA-00334 Checksum error in an archived redo log during recovery
ORA-01578 Data block corruption — if seen with ORA-00368, suspect storage-wide issues

Always cross-reference the Oracle Alert Log and check MOS (My Oracle Support) for patch advisories when ORA-00368 is encountered in production.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)