DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00290 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00290: Operating System Archival Error Occurred

ORA-00290 is raised when Oracle's archiver process (ARCn) encounters an operating system-level failure while trying to write or manage archive log files. This error typically occurs in databases running in ARCHIVELOG mode and can eventually stall the entire database if the underlying issue is not resolved promptly.

Top 3 Causes and Solutions

1. Archive Destination Disk Full

The most common cause. When the filesystem hosting LOG_ARCHIVE_DEST_n runs out of space, ARCn cannot write new archive files.

-- Check archive destination status
SELECT DEST_ID, STATUS, DESTINATION, FAIL_COUNT, ERROR
FROM   V$ARCHIVE_DEST
WHERE  STATUS != 'INACTIVE';

-- Check archive log generation rate (last 24 hours)
SELECT TO_CHAR(FIRST_TIME, 'YYYY-MM-DD HH24') AS HOUR,
       COUNT(*)                               AS LOG_COUNT,
       ROUND(SUM(BLOCKS * BLOCK_SIZE)/1024/1024, 2) AS SIZE_MB
FROM   V$ARCHIVED_LOG
WHERE  FIRST_TIME >= SYSDATE - 1
GROUP  BY TO_CHAR(FIRST_TIME, 'YYYY-MM-DD HH24')
ORDER  BY 1;
Enter fullscreen mode Exit fullscreen mode

Fix: Use RMAN to clean up old archive logs after confirming they are backed up.

-- Connect to RMAN and run:
-- DELETE ARCHIVELOG ALL BACKED UP 1 TIMES TO DISK;
-- DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
Enter fullscreen mode Exit fullscreen mode

2. Invalid Archive Destination Path or Permission Issue

If the directory specified in LOG_ARCHIVE_DEST_n does not exist, is inaccessible, or the Oracle OS user lacks write permission, ORA-00290 will be triggered.

-- Check current archive destination parameters
SELECT NAME, VALUE
FROM   V$PARAMETER
WHERE  NAME LIKE 'log_archive_dest%'
AND    VALUE IS NOT NULL;

-- Reassign to a valid, writable path
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=/arch/oradata/ORCL'
    SCOPE=BOTH;

-- Stop and restart archiving after path fix
ALTER SYSTEM ARCHIVE LOG STOP;
ALTER SYSTEM ARCHIVE LOG START;
Enter fullscreen mode Exit fullscreen mode

Verify OS-level permissions on the archive directory:

# Run as root or OS DBA
ls -ld /arch/oradata/ORCL
chown oracle:oinstall /arch/oradata/ORCL
chmod 755 /arch/oradata/ORCL
Enter fullscreen mode Exit fullscreen mode

3. Insufficient ARCn Processes Under Heavy Load

In high-throughput environments with frequent log switches, too few ARCn processes can cause an archiving backlog and trigger OS-level errors.

-- Check current max archiver processes
SHOW PARAMETER log_archive_max_processes;

-- Increase ARCn process count (up to 30)
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES = 10 SCOPE=BOTH;

-- Monitor active ARCn processes
SELECT PNAME, DESCRIPTION
FROM   V$BGPROCESS
WHERE  PNAME LIKE 'ARC%'
AND    PADDR != '00';
Enter fullscreen mode Exit fullscreen mode

Quick Fix Summary 

-- Step 1: Identify the failing destination
SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE ERROR IS NOT NULL;

-- Step 2: Check FRA usage if FRA is configured
SELECT SPACE_LIMIT/1024/1024/1024 AS LIMIT_GB,
       SPACE_USED/1024/1024/1024  AS USED_GB
FROM   V$RECOVERY_FILE_DEST;

-- Step 3: Force manual archive if logs are stuck
ALTER SYSTEM ARCHIVE LOG ALL;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Use Fast Recovery Area (FRA): Configure FRA so Oracle automatically manages archive space and reclaims storage from obsolete files. 
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST      = '/fra/oradata' SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 100G           SCOPE=BOTH;
Enter fullscreen mode Exit fullscreen mode
  1. Set up proactive monitoring: Configure OEM alerts or custom scripts to notify the DBA when archive destination usage exceeds 75%. Always include archive log deletion in your RMAN backup scripts to prevent uncontrolled growth.

Related Errors

  • ORA-00257 – Archiver process blocked, often follows ORA-00290 when disk is full.
  • ORA-16014 – All archive destinations failed; redo logs cannot be archived.
  • ORA-19504 – RMAN failure creating archive files, commonly linked to permission issues.

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