DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00474 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00474: SMON Process Terminated with Error

ORA-00474 is a critical Oracle database error that occurs when the SMON (System Monitor) background process terminates unexpectedly. SMON is responsible for essential tasks such as instance recovery, temporary segment cleanup, and extent coalescing in dictionary-managed tablespaces. When SMON goes down, Oracle typically brings down the entire instance, requiring immediate DBA intervention.

Top 3 Causes

1. Data Dictionary or System Tablespace Corruption

Block corruption in the SYSTEM or SYSAUX tablespace is one of the leading causes of SMON failure. Since SMON continuously accesses the data dictionary, any I/O error or corrupted block in these tablespaces can cause an immediate crash.

-- Check for block corruption
SELECT FILE#, BLOCK#, BLOCKS, CORRUPTION_TYPE
FROM V$DATABASE_BLOCK_CORRUPTION;

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

2. Rollback / UNDO Segment Issues During Instance Recovery

After an abnormal shutdown, SMON performs instance recovery by rolling back uncommitted transactions. If an UNDO segment is corrupted or a specific transaction cannot be rolled back, SMON will fail and terminate with an error.

-- Check rollback segment status
SELECT SEGMENT_NAME, STATUS, TABLESPACE_NAME
FROM DBA_ROLLBACK_SEGS;

-- Temporarily offline a problematic UNDO segment via SPFILE
ALTER SYSTEM SET "_offline_rollback_segments" = '_SYSSMU3_123456789$'
  SCOPE=SPFILE;

-- Verify UNDO tablespace usage
SELECT TABLESPACE_NAME,
       ROUND(SUM(BYTES)/1024/1024, 2) AS TOTAL_MB,
       SUM(DECODE(STATUS,'ACTIVE',1,0)) AS ACTIVE_EXTENTS
FROM DBA_UNDO_EXTENTS
GROUP BY TABLESPACE_NAME;
Enter fullscreen mode Exit fullscreen mode

3. OS-Level Resource Exhaustion (OOM / ulimit)

If the server runs out of physical memory or if OS-level limits (ulimit) are set below Oracle's recommended values, the OS OOM Killer may terminate Oracle background processes including SMON. Always check OS logs alongside Oracle alert logs.

-- Check current SGA usage
SELECT POOL, NAME, ROUND(BYTES/1024/1024, 2) AS MB
FROM V$SGASTAT
WHERE POOL IS NOT NULL
ORDER BY BYTES DESC
FETCH FIRST 10 ROWS ONLY;

-- Monitor background process status
SELECT PNAME, PID, SPID, BACKGROUND
FROM V$PROCESS
WHERE BACKGROUND = 1
ORDER BY PNAME;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Step 1 – Check Alert Log and Trace Files First

-- Find trace file location
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

-- Find ADR home
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'ADR Home';
Enter fullscreen mode Exit fullscreen mode

Step 2 – Run RMAN Validation

-- Connect to RMAN and validate the database
-- RMAN> VALIDATE DATABASE;
-- RMAN> VALIDATE DATAFILE 1;

-- After validation, review corruption details
SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
Enter fullscreen mode Exit fullscreen mode

Step 3 – Clean Up Temporary Segments Manually

-- Identify temporary segments
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = 'TEMPORARY';

-- Shrink TEMP tablespace (Oracle 11g+)
ALTER TABLESPACE TEMP SHRINK SPACE KEEP 100M;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Enable Block Checksum and Schedule Regular RMAN Validation

Enabling DB_BLOCK_CHECKSUM allows Oracle to detect corruption at the I/O level before it causes critical failures.

-- Enable block checksum
ALTER SYSTEM SET DB_BLOCK_CHECKSUM = TYPICAL SCOPE=BOTH;

-- Confirm the setting
SHOW PARAMETER db_block_checksum;
Enter fullscreen mode Exit fullscreen mode

2. Monitor Background Processes and Tune UNDO Settings

Set up proactive monitoring for all Oracle background processes and ensure UNDO retention and tablespace sizing match your workload patterns.

-- Monitor UNDO health
SHOW PARAMETER undo_retention;

-- Adjust if needed
ALTER SYSTEM SET UNDO_RETENTION = 900 SCOPE=BOTH;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-00470 – LGWR process terminated with error
  • ORA-00472 – PMON process terminated with error
  • ORA-00600 – Internal error, often accompanies ORA-00474 in trace files
  • ORA-01578 – Oracle data block corrupted, frequently seen alongside SMON failures

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