DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00471 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00471: DBWR Process Terminated with Error — Causes, Fixes & Prevention

ORA-00471 is one of the most critical Oracle errors a DBA can encounter, occurring when the Database Writer (DBWR) background process terminates abnormally. DBWR is responsible for writing dirty buffers from the SGA buffer cache to datafiles on disk, and its failure immediately causes the entire database instance to crash. Prompt diagnosis and action are essential to minimize downtime and prevent data loss.

Top 3 Causes

1. OS-Level I/O Errors or Storage Failures

The most common cause is an I/O failure at the OS level while DBWR attempts to write blocks to datafiles. Faulty disk sectors, disconnected SAN/NAS volumes, or unmounted NFS shares will all cause DBWR to fail immediately.

Check for I/O errors in the alert log:

-- Find DBWR-related errors in alert log
SELECT ORIGINATING_TIMESTAMP, MESSAGE_TEXT
FROM V$DIAG_ALERT_EXT
WHERE MESSAGE_TEXT LIKE '%DBWR%'
   OR MESSAGE_TEXT LIKE '%ORA-00471%'
   OR MESSAGE_TEXT LIKE '%I/O%'
ORDER BY ORIGINATING_TIMESTAMP DESC
FETCH FIRST 20 ROWS ONLY;

-- Check datafile status
SELECT FILE#, NAME, STATUS, BYTES/1024/1024 AS SIZE_MB
FROM V$DATAFILE
WHERE STATUS != 'ONLINE';
Enter fullscreen mode Exit fullscreen mode

Also check OS-level logs (dmesg, /var/log/messages) for hardware-level I/O errors alongside Oracle alerts.

2. Tablespace / Filesystem Full or Permission Issues

If the filesystem hosting the datafiles is full, DBWR cannot write dirty blocks even if AUTOEXTEND is enabled. Changed file permissions on the Oracle account can also prevent writes entirely.

-- Check tablespace usage
SELECT
    TABLESPACE_NAME,
    ROUND(USED_PERCENT, 2) AS USED_PCT,
    CASE WHEN USED_PERCENT >= 90 THEN 'CRITICAL'
         WHEN USED_PERCENT >= 80 THEN 'WARNING'
         ELSE 'NORMAL' END AS STATUS
FROM DBA_TABLESPACE_USAGE_METRICS
ORDER BY USED_PERCENT DESC;

-- Add a datafile to resolve space issues
ALTER TABLESPACE USERS
ADD DATAFILE '/u01/app/oracle/oradata/ORCL/users02.dbf'
SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 10G;
Enter fullscreen mode Exit fullscreen mode

3. SGA Misconfiguration or OS Memory Exhaustion

An oversized SGA that exceeds available physical memory can cause DBWR to fail when the OS (particularly Linux OOM Killer) forcibly terminates Oracle processes to reclaim memory.

-- Check current SGA configuration
SELECT COMPONENT,
       CURRENT_SIZE/1024/1024 AS CURRENT_MB,
       MAX_SIZE/1024/1024 AS MAX_MB
FROM V$SGA_DYNAMIC_COMPONENTS;

-- View SGA and PGA targets
SHOW PARAMETER SGA_TARGET;
SHOW PARAMETER PGA_AGGREGATE_TARGET;

-- Adjust SGA (keep within 60-70% of total RAM)
ALTER SYSTEM SET SGA_TARGET = 4G SCOPE=SPFILE;
ALTER SYSTEM SET DB_WRITER_PROCESSES = 4 SCOPE=SPFILE;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions 

-- Step 1: Take damaged datafile offline and recover
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' OFFLINE;

-- Step 2: Run RMAN recovery (from RMAN prompt)
-- RMAN> RESTORE DATAFILE '/u01/oradata/ORCL/users01.dbf';
-- RMAN> RECOVER DATAFILE '/u01/oradata/ORCL/users01.dbf';

-- Step 3: Bring datafile back online
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' ONLINE;

-- Step 4: Verify RMAN backup health
SELECT INPUT_TYPE, STATUS, START_TIME,
       ROUND(OUTPUT_BYTES/1024/1024/1024, 2) AS OUTPUT_GB
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY START_TIME DESC
FETCH FIRST 5 ROWS ONLY;
Enter fullscreen mode Exit fullscreen mode

If the database cannot be opened, start in MOUNT mode, perform recovery, then open:

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

Prevention Tips

  1. Proactive monitoring: Schedule regular jobs to check tablespace usage, free disk space, and OS memory. Alert when tablespace utilization exceeds 80%. Use Oracle OEM or tools like Zabbix/Prometheus for real-time alerting.

  2. Solid backup strategy: Configure RMAN with a tested backup schedule. Always run in ARCHIVELOG mode and maintain a properly sized Flash Recovery Area (FRA). Periodically run restore/recovery tests — a backup you haven't tested is a backup you can't trust.

Related Errors

Error Code Description
ORA-00470 LGWR process terminated — often seen alongside ORA-00471
ORA-00472 PMON process terminated — cascading background process failure
ORA-01114 I/O error writing block to file — direct trigger for ORA-00471
ORA-27063 Bytes read/written mismatch — storage-level I/O inconsistency

When ORA-00471 occurs, always examine the full alert log context — the root cause error is almost always logged just before the ORA-00471 message itself.

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