DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00470 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00470: LGWR Process Terminated with Error — Causes, Fixes & Prevention

ORA-00470 is a critical Oracle database error that occurs when the Log Writer (LGWR) background process terminates abnormally. LGWR is responsible for writing redo entries from the SGA's Redo Log Buffer to the online redo log files, and its failure causes an immediate database crash. This error is always accompanied by additional diagnostic messages in the alert.log and LGWR trace files that must be analyzed to determine the root cause.

Top 3 Causes

1. Redo Log File I/O Failure or Inaccessibility

The most common cause is the inability of LGWR to write to the online redo log files due to disk failure, filesystem issues, or permission errors. Without redo log multiplexing, a single disk failure becomes a single point of failure for the entire database.

-- Check redo log file status immediately after the error
SELECT a.group#, a.status, a.members, b.member, b.status AS file_status
FROM v$log a
JOIN v$logfile b ON a.group# = b.group#
ORDER BY a.group#;

-- Check for invalid or stale log members
SELECT group#, member, status
FROM v$logfile
WHERE status IN ('INVALID', 'STALE');
Enter fullscreen mode Exit fullscreen mode

2. OS-Level Resource Exhaustion

Insufficient OS file descriptors, out-of-memory conditions, or misconfigured kernel parameters can prevent LGWR from performing normal file I/O. On Linux systems, the OOM Killer may forcibly terminate the LGWR process when system memory is critically low.

-- Check current Oracle initialization parameters related to LGWR
SELECT name, value, description
FROM v$parameter
WHERE name IN ('log_buffer', 'db_block_size', 'log_archive_dest_state_1');

-- Review archive log space usage
SELECT dest_name, status, target, archiver, schedule,
       destination, fail_count, error
FROM v$archive_dest
WHERE status != 'INACTIVE';
Enter fullscreen mode Exit fullscreen mode

3. Oracle Internal Bug or Memory Corruption

Known Oracle bugs or SGA memory corruption can trigger unexpected LGWR termination. In these cases, ORA-00470 often appears alongside ORA-00600 (internal error) in the alert.log.

-- Identify recent incidents in the ADR (11g and above)
SELECT incident_id, create_time, problem_key, incident_status
FROM v$diag_incident
ORDER BY create_time DESC
FETCH FIRST 5 ROWS ONLY;

-- Locate the LGWR trace file path
SELECT value
FROM v$diag_info
WHERE name = 'Diag Trace';
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Step 1 — Check alert.log and identify the root error:
Always look at the errors recorded before ORA-00470 in the alert.log. The actual root cause (e.g., ORA-27063, ORA-00345) will be listed there.

Step 2 — Add redo log multiplexing if missing:

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

Step 3 — Restart the database after resolving the underlying issue:

SHUTDOWN ABORT;
-- Resolve disk/OS issue at OS level first
STARTUP MOUNT;
-- Verify redo log files are accessible
SELECT group#, status, archived FROM v$log;
ALTER DATABASE OPEN;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Always multiplex redo log files across separate physical disks. A minimum of two members per group on different storage paths is mandatory in any production environment.

-- Audit redo log groups with only one member (run regularly)
SELECT group#, COUNT(*) AS member_count
FROM v$logfile
GROUP BY group#
HAVING COUNT(*) < 2;
Enter fullscreen mode Exit fullscreen mode

2. Implement proactive monitoring with Oracle Enterprise Manager or custom scripts. Monitor redo log status, archive destination space, and OS metrics (disk I/O, memory) continuously. Set alerts for INVALID or STALE log file members before they escalate into an ORA-00470 outage.

Related Errors

Error Code Description
ORA-00313 Failure to open redo log — often precedes ORA-00470
ORA-00345 Redo log write error during LGWR I/O
ORA-00321 Error updating log file header
ORA-00600 Internal error — may appear with ORA-00470 if a bug is involved
ORA-27063/27072 OS-level I/O errors recorded alongside LGWR failure

When ORA-00470 appears with ORA-00600, open an Oracle Support Service Request (SR) immediately and provide the full alert.log excerpt and the LGWR trace file.

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