DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00345 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00345: Redo Log Write Error – Block Count Explained

ORA-00345 is an Oracle database error that occurs when the database engine encounters a failure while attempting to write a specific number of blocks to an Online Redo Log file. This error is a critical I/O-level issue because Redo Logs are the backbone of Oracle's transaction recovery mechanism. Immediate investigation and resolution are mandatory to prevent data corruption or database unavailability.

Top 3 Causes

1. Physical Disk or Storage I/O Failure

The most common cause is a hardware-level problem — a failing disk, a bad sector, or an issue at the SAN/NAS layer that prevents the OS from completing write requests. Always check OS-level logs (/var/log/messages on Linux) alongside Oracle alert logs.

-- Check current Online Redo Log status
SELECT 
    l.GROUP#,
    l.STATUS,
    l.ARCHIVED,
    lf.MEMBER,
    lf.STATUS AS MEMBER_STATUS
FROM V$LOG l
JOIN V$LOGFILE lf ON l.GROUP# = lf.GROUP#
ORDER BY l.GROUP#;
Enter fullscreen mode Exit fullscreen mode

2. Inaccessible or Deleted Redo Log Member

If a Redo Log member file has been accidentally deleted, moved, or has incorrect OS permissions, Oracle cannot complete the write operation. The V$LOGFILE view will show the member with an INVALID status.

-- Identify invalid members and re-add them
-- Step 1: Drop the invalid member
ALTER DATABASE DROP LOGFILE MEMBER '/old_path/redo01b.log';

-- Step 2: Add a new member to the same group
ALTER DATABASE ADD LOGFILE MEMBER '/new_path/redo01b.log' 
    TO GROUP 1;

-- Step 3: Verify the fix
SELECT GROUP#, MEMBER, STATUS FROM V$LOGFILE ORDER BY GROUP#;
Enter fullscreen mode Exit fullscreen mode

3. Filesystem Full – No Space for Block Writes

When the filesystem hosting the Redo Log or Archive Log destination is full, the OS rejects write operations. In Archive Log mode, a full archive destination can cascade into Redo Log write failures.

-- Check archive destination status
SELECT DEST_NAME, STATUS, DESTINATION 
FROM V$ARCHIVE_DEST 
WHERE STATUS = 'VALID';

-- Check FRA usage
SELECT FILE_TYPE, PERCENT_SPACE_USED, PERCENT_SPACE_RECLAIMABLE
FROM V$FLASH_RECOVERY_AREA_USAGE;
Enter fullscreen mode Exit fullscreen mode 
-- Clean up archive logs using RMAN
-- Run inside RMAN prompt:
-- BACKUP ARCHIVELOG ALL DELETE INPUT;
-- DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-3';
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

If a Redo Log group is severely damaged and cannot be recovered normally, use the following procedure:

-- Force a log switch away from the problem group
ALTER SYSTEM SWITCH LOGFILE;

-- Wait for checkpoint
ALTER SYSTEM CHECKPOINT;

-- Drop and recreate the damaged group
ALTER DATABASE DROP LOGFILE GROUP 3;

ALTER DATABASE ADD LOGFILE GROUP 3 (
    '/disk1/redo03a.log',
    '/disk2/redo03b.log'
) SIZE 500M;
Enter fullscreen mode Exit fullscreen mode

If the database cannot open due to this error, mount and recover:

STARTUP MOUNT;
RECOVER DATABASE;
ALTER DATABASE OPEN;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

Multiplex your Redo Logs — Always maintain at least 2 members per group on separate physical disks or disk groups. This single practice eliminates single points of failure for Redo Log writes.

-- Verify multiplexing is in place
SELECT GROUP#, COUNT(*) AS MEMBER_COUNT 
FROM V$LOGFILE 
GROUP BY GROUP# 
HAVING COUNT(*) < 2;
-- Any result here means under-protected groups
Enter fullscreen mode Exit fullscreen mode

Monitor disk space proactively — Schedule regular checks on your archive destination and Redo Log filesystem. Set OEM alerts or cron-based shell scripts to notify when usage exceeds 80%. Also monitor Log Switch frequency to right-size your Redo Log files (recommend 500MB–1GB in production).

-- Monitor log switch frequency (last 24 hours)
SELECT 
    TO_CHAR(FIRST_TIME, 'HH24') AS HOUR,
    COUNT(*) AS SWITCHES
FROM V$LOG_HISTORY
WHERE FIRST_TIME >= SYSDATE - 1
GROUP BY TO_CHAR(FIRST_TIME, 'HH24')
ORDER BY 1;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-00340 – General I/O error on Online Redo Log
  • ORA-00341 – Redo Log member not found
  • ORA-00346 – Redo Log member marked OFFLINE
  • ORA-00257 – Archiver process stuck; often co-occurs with ORA-00345

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