DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00340 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00340: IO Error Processing Online Log of Thread – Causes, Fixes & Prevention

ORA-00340 is a serious Oracle database error indicating that an I/O failure occurred while Oracle's Log Writer (LGWR) process attempted to read or write an online redo log file. Because redo logs are critical to transaction durability and database recovery, this error can cause instance crashes or data loss if not addressed immediately. It is almost always accompanied by additional errors such as ORA-00312 or ORA-00313 in the alert log, which provide more detail about which file and thread are affected.

Top 3 Causes

1. Physical Corruption or Accidental Deletion of Redo Log Files

The most common and severe cause is when an online redo log file is deleted at the OS level or becomes physically corrupted due to a bad disk sector. LGWR constantly writes to these files, and any inaccessibility triggers ORA-00340 immediately.

-- Check the status of all online redo log files
SELECT a.GROUP#, a.THREAD#, a.SEQUENCE#, a.STATUS,
       b.MEMBER, b.STATUS AS FILE_STATUS
FROM V$LOG a
JOIN V$LOGFILE b ON a.GROUP# = b.GROUP#
ORDER BY a.GROUP#;

-- Identify invalid or missing log members
SELECT GROUP#, MEMBER, STATUS
FROM V$LOGFILE
WHERE STATUS IN ('INVALID', 'DELETED') OR STATUS IS NULL;
Enter fullscreen mode Exit fullscreen mode

2. Storage / File System I/O Errors

Failures in the underlying storage layer — such as an NFS unmount, ASM disk group degradation, or SAN connectivity loss — can prevent Oracle from writing to redo log files. This is especially critical in RAC environments where shared storage serves multiple nodes.

-- Check ASM disk group health (if using ASM)
SELECT NAME, STATE, TOTAL_MB, FREE_MB, USABLE_FILE_MB
FROM V$ASM_DISKGROUP;

-- Check for disk space issues on the redo log filesystem
-- (Run at OS level)
-- df -h /u01/oradata/

-- Verify redo log file sizes and locations
SELECT GROUP#, MEMBERS, BYTES/1024/1024 AS SIZE_MB, STATUS, ARCHIVED
FROM V$LOG
ORDER BY GROUP#;
Enter fullscreen mode Exit fullscreen mode

3. File Permission or OS Resource Issues

Incorrect file ownership or permissions on redo log files, or exhaustion of OS resources like file descriptors, can block LGWR from accessing the files. This is more common after OS-level maintenance or a change in the Oracle software owner account.

-- Confirm redo log file paths for OS-level permission check
SELECT GROUP#, MEMBER FROM V$LOGFILE ORDER BY GROUP#;

-- After fixing permissions at OS level (run as root or oracle OS user):
-- chown oracle:oinstall /u01/oradata/orcl/redo*.log
-- chmod 640 /u01/oradata/orcl/redo*.log
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

If the affected group is INACTIVE (archived): Drop and recreate it.

-- Confirm group is INACTIVE and ARCHIVED
SELECT GROUP#, STATUS, ARCHIVED FROM V$LOG;

-- Drop the damaged group
ALTER DATABASE DROP LOGFILE GROUP 2;

-- Re-add with multiplexing (best practice)
ALTER DATABASE ADD LOGFILE GROUP 2
  ('/u01/oradata/orcl/redo02a.log',
   '/u02/oradata/orcl/redo02b.log') SIZE 200M;
Enter fullscreen mode Exit fullscreen mode

If the group is CURRENT or ACTIVE (worst case): You may need to perform an incomplete recovery using RMAN or force open with RESETLOGS as a last resort.

-- Force database open (last resort — potential data loss)
SHUTDOWN ABORT;
STARTUP MOUNT;
ALTER DATABASE OPEN RESETLOGS;

-- Immediately take a full backup after RESETLOGS
-- RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Always multiplex redo log groups across separate disks.

-- Add a second member to all existing log groups
ALTER DATABASE ADD LOGFILE MEMBER
  '/u02/oradata/orcl/redo01b.log' TO GROUP 1,
  '/u02/oradata/orcl/redo02b.log' TO GROUP 2,
  '/u02/oradata/orcl/redo03b.log' TO GROUP 3;

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

2. Monitor redo log file health proactively using Oracle's alert log, V$LOGFILE, and OS-level disk health tools (e.g., SMART monitoring). Set up automated alerts when V$LOGFILE.STATUS shows INVALID or disk free space drops below a safe threshold.

Related Errors

  • ORA-00312 – Online log file description missing; often appears alongside ORA-00340.
  • ORA-00313 – Failure to open the online log file; typically precedes or accompanies ORA-00340.
  • ORA-00345 – Redo log write error; same root causes as ORA-00340 but during write operations.
  • ORA-00600 – Internal error that may cascade from severe redo log corruption requiring Oracle Support involvement.

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