DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00354 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00354: Corrupt Redo Log Block Header — Causes, Fixes & Prevention

ORA-00354 is a critical Oracle database error indicating that the block header of a Redo Log file has been corrupted. Since Redo Logs are the backbone of Oracle's crash recovery mechanism, this error can prevent the database from starting up or completing media recovery. It must be treated as a high-priority incident requiring immediate attention.

Top 3 Causes

1. Disk or Storage Hardware Failure

Physical bad sectors, RAID controller faults, or SAN/NAS I/O errors are the most common root causes. A failed write operation during Redo Log block creation leaves the header in an incomplete or inconsistent state.

-- Check Redo Log group status to identify the corrupted group
SELECT GROUP#, SEQUENCE#, STATUS, ARCHIVED,
       ROUND(BYTES/1024/1024, 2) AS SIZE_MB
FROM V$LOG
ORDER BY GROUP#;

-- Check physical file locations
SELECT GROUP#, MEMBER, STATUS
FROM V$LOGFILE
ORDER BY GROUP#;
Enter fullscreen mode Exit fullscreen mode

2. Abnormal Instance Shutdown (Instance Crash)

A sudden power loss, OS kernel panic, or forceful process termination (kill -9) can interrupt Oracle mid-write to a Redo Log block. The block header is left partially written, and when SMON attempts instance recovery on the next startup, it encounters the corrupted header and raises ORA-00354.

-- Check database status after abnormal shutdown
SELECT STATUS, INSTANCE_NAME, DATABASE_STATUS
FROM V$INSTANCE;

-- Review SCN information for recovery reference
SELECT CURRENT_SCN, CHECKPOINT_CHANGE#, RESETLOGS_CHANGE#
FROM V$DATABASE;
Enter fullscreen mode Exit fullscreen mode

3. OS File System or Volume Manager Issues

Bugs in file systems (ext4, XFS) or misconfigurations in LVM/ASM can distort I/O to Redo Log files. Async I/O misconfiguration or cache flush issues can create a timing gap between when Oracle believes a block was written and when it was actually committed to disk.

-- Verify ASM disk group status (if using ASM)
SELECT GROUP_NUMBER, NAME, STATE, TYPE, TOTAL_MB, FREE_MB
FROM V$ASM_DISKGROUP;

-- Check for any file header issues in controlfile
SELECT FILE#, STATUS, CHECKPOINT_CHANGE#
FROM V$DATAFILE
ORDER BY FILE#;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Scenario A — Corrupted Group is INACTIVE (Safest) 

-- Drop the corrupted log group (e.g., GROUP 2)
ALTER DATABASE DROP LOGFILE GROUP 2;

-- Re-add with multiplexed members on separate disks
ALTER DATABASE ADD LOGFILE GROUP 2
  ('/oradata1/redo/redo02a.log', '/oradata2/redo/redo02b.log')
  SIZE 500M;

-- Confirm the new group is added
SELECT GROUP#, STATUS, MEMBERS FROM V$LOG WHERE GROUP# = 2;
Enter fullscreen mode Exit fullscreen mode

Scenario B — Corrupted Group is CURRENT or ACTIVE 

-- Force a log switch to move away from the corrupted log
ALTER SYSTEM SWITCH LOGFILE;

-- Force a checkpoint
ALTER SYSTEM CHECKPOINT;

-- Re-check status
SELECT GROUP#, SEQUENCE#, STATUS FROM V$LOG;
Enter fullscreen mode Exit fullscreen mode

Scenario C — Database Won't Open (Incomplete Recovery via RMAN) 

-- In RMAN, perform point-in-time recovery before the corrupted sequence
-- Step 1: Mount the database
-- STARTUP MOUNT;

-- Step 2: Restore and recover up to the corrupted sequence
-- RESTORE DATABASE;
-- RECOVER DATABASE UNTIL SEQUENCE <corrupted_seq> THREAD 1;

-- Step 3: Open with RESETLOGS
ALTER DATABASE OPEN RESETLOGS;

-- Verify the database opened successfully
SELECT OPEN_MODE, LOG_MODE, DB_NAME FROM V$DATABASE;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Always Multiplex Redo Logs

Place at least 2 members per group on separate physical disks or ASM disk groups. This ensures that if one member is corrupted, Oracle can continue using the healthy mirror.

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

-- Verify multiplexing is in place
SELECT GROUP#, MEMBER FROM V$LOGFILE ORDER BY GROUP#, MEMBER;
Enter fullscreen mode Exit fullscreen mode

2. Implement Regular RMAN Backups with Validation

Schedule periodic BACKUP VALIDATE jobs to proactively detect block corruption before it causes an outage. Pair this with automated monitoring of V$LOG status changes.

-- Monitor Redo Log health proactively
SELECT GROUP#, STATUS, SEQUENCE#, ARCHIVED,
       ROUND(BYTES/1024/1024, 2) AS MB
FROM V$LOG
WHERE STATUS NOT IN ('INACTIVE', 'CURRENT');

-- Set up alert log monitoring path
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'ADR Home';
Enter fullscreen mode Exit fullscreen mode

Related Errors

Error Code Description
ORA-00353 Log corruption near block — often accompanies ORA-00354
ORA-00355 Change numbers out of order in Redo Log
ORA-00356 Inconsistent lengths in change description
ORA-16038 Log cannot be archived due to corruption
ORA-00600 Internal error — check trace files alongside ORA-00354

Pro Tip: Always open an Oracle Support SR (Service Request) when encountering ORA-00354 in a production environment. Provide the full alert log, relevant trace files, and the output of V$LOG and V$LOGFILE queries for faster resolution.

📖 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)