Oracle ORA-00289 Error: Causes and Solutions Complete Guide

-- 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;

-- 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;

-- 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;

-- 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;

-- 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;