DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00289 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00289: Archive Log Destination – Causes, Fixes & Prevention

ORA-00289 is an informational/warning message from Oracle indicating a suggested or problematic archive log destination. It typically appears alongside other archiver-related errors such as ORA-00255 or ORA-00257, signaling that the Archiver process (ARCn) cannot write archive logs to the configured destination. If left unresolved, this issue can cause the entire database to hang, making it one of the most operationally critical errors in an ARCHIVELOG-mode database.

Top 3 Causes

1. Archive Log Destination Disk Full

The most common cause. When the filesystem holding archive logs runs out of space, the ARCn process cannot write new archive logs, eventually blocking all redo log switches and freezing the database.

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

-- Check FRA usage
SELECT SPACE_LIMIT / 1024 / 1024 / 1024 AS LIMIT_GB,
       SPACE_USED / 1024 / 1024 / 1024  AS USED_GB,
       SPACE_RECLAIMABLE / 1024 / 1024 / 1024 AS RECLAIMABLE_GB
FROM V$RECOVERY_FILE_DEST;

-- Expand FRA size immediately
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 200G SCOPE=BOTH;
Enter fullscreen mode Exit fullscreen mode

2. Invalid or Misconfigured Archive Destination Path

If the path specified in LOG_ARCHIVE_DEST_n does not exist or becomes invalid (e.g., after a server migration or mount point change), the Archiver fails silently and emits ORA-00289.

-- Check current archive destination parameters
SHOW PARAMETER LOG_ARCHIVE_DEST;

-- Fix the destination path
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=/oradata/archive' SCOPE=BOTH;

-- Or use FRA as destination
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST' SCOPE=BOTH;

-- Verify archiver process status
SELECT * FROM V$ARCHIVE_PROCESSES;
Enter fullscreen mode Exit fullscreen mode

3. OS-Level Permission Denied on Archive Directory

If the oracle OS user lacks write permission on the archive destination directory, the Archiver cannot create new archive log files. This often happens after security policy changes or OS patching.

-- Force archive the current log to test after permission fix
ALTER SYSTEM ARCHIVE LOG CURRENT;

-- Check archive log list
ARCHIVE LOG LIST;

-- Verify archived logs are being created successfully
SELECT SEQUENCE#, NAME, FIRST_TIME, NEXT_TIME, DELETED
FROM V$ARCHIVED_LOG
WHERE FIRST_TIME >= SYSDATE - 1/24
ORDER BY SEQUENCE# DESC;
Enter fullscreen mode Exit fullscreen mode

OS-level fix (run as root):

chown oracle:dba /oradata/archive
chmod 755 /oradata/archive

Quick Fix Solutions 

-- Emergency: redirect archive destination to a temporary location
-- (when the database is hanging and you need immediate relief)
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=/tmp/archive_emergency' SCOPE=MEMORY;

-- After resolving the root cause, restore proper destination
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=/oradata/archive' SCOPE=BOTH;

-- Clean up old archived logs via RMAN (after verifying backups exist)
-- RMAN> DELETE NOPROMPT ARCHIVELOG ALL BACKED UP 1 TIMES TO DISK;
-- RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

-- Check how much archive log is being generated per hour
SELECT TO_CHAR(TRUNC(FIRST_TIME, 'HH'), 'YYYY-MM-DD HH24') AS HOUR,
       COUNT(*) AS LOG_COUNT,
       ROUND(SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024, 2) AS TOTAL_MB
FROM V$ARCHIVED_LOG
WHERE FIRST_TIME >= SYSDATE - 1
  AND DELETED = 'NO'
GROUP BY TRUNC(FIRST_TIME, 'HH')
ORDER BY 1 DESC;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Monitor disk usage proactively and automate cleanup

Set up OS-level monitoring (cron jobs or OEM alerts) to trigger warnings when archive log disk usage exceeds 75%. Integrate RMAN retention policies to automatically purge obsolete archive logs after successful backups.

2. Use FRA with redundant destinations

Configure Flash Recovery Area and set up at least two archive destinations to eliminate single points of failure. Ensure FRA size is large enough to hold at least 7 days of archive logs plus full backup sets.

-- Recommended dual-destination setup
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/fra/oradata' SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 500G SCOPE=BOTH;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 =
  'LOCATION=USE_DB_RECOVERY_FILE_DEST MANDATORY' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 =
  'LOCATION=/backup/archive OPTIONAL REOPEN=300' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_MIN_SUCCEED_DEST = 1 SCOPE=BOTH;
Enter fullscreen mode Exit fullscreen mode

Related Oracle Errors

Error Code Description
ORA-00255 Archiver failed to archive a log — almost always paired with ORA-00289
ORA-00257 Archiver error; only internal connections allowed until resolved
ORA-19809 FRA space limit exceeded; increase DB_RECOVERY_FILE_DEST_SIZE
ORA-16014 Log cannot be archived; no available destination

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