DEV Community

umzzil nng
umzzil nng

Posted on Originally published at oraerror.com

Oracle ORA-16014 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-16014: Log Not Archiving, No Available Destinations

ORA-16014 occurs when Oracle is running in ARCHIVELOG mode but cannot find a single valid archive destination to write archived redo logs. If left unresolved, every online redo log group will eventually fill up, causing the entire database to hang and blocking all user transactions.

Top 3 Causes and Fixes

1. Archive Destination Disk Full

The most common cause. When the filesystem hosting LOG_ARCHIVE_DEST_n or the Fast Recovery Area (FRA) hits 100% capacity, Oracle automatically defers the destination and raises ORA-16014.

Diagnose:

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

-- Check FRA usage
SELECT NAME,
       ROUND(SPACE_USED / SPACE_LIMIT * 100, 2) AS USED_PCT,
       SPACE_RECLAIMABLE / (1024*1024*1024) AS RECLAIMABLE_GB
FROM   V$RECOVERY_FILE_DEST;
Enter fullscreen mode Exit fullscreen mode

Fix:

-- Delete backed-up archive logs via RMAN
-- Run inside RMAN prompt
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1';

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

2. Invalid Destination Path or Permission Error

If the directory specified in LOG_ARCHIVE_DEST_n doesn't exist, was accidentally removed, or the oracle OS user lacks write permission, archiving will fail silently and the destination will be marked ERROR.

Diagnose:

-- Find destinations with errors
SELECT DEST_ID, DESTINATION, STATUS, ERROR, FAIL_DATE
FROM   V$ARCHIVE_DEST
WHERE  ERROR IS NOT NULL;
Enter fullscreen mode Exit fullscreen mode

Fix:

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

-- Re-enable the destination
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1 = ENABLE SCOPE=BOTH;
Enter fullscreen mode Exit fullscreen mode 
# Fix directory and permissions at OS level
mkdir -p /correct/archive/path
chown oracle:oinstall /correct/archive/path
chmod 750 /correct/archive/path
Enter fullscreen mode Exit fullscreen mode

3. All Destinations in DEFER or ERROR State

Oracle requires at least LOG_ARCHIVE_MIN_SUCCEED_DEST destinations to succeed. If a Standby DB connection is lost and the destination is set to MANDATORY, or a DBA manually deferred all destinations, archiving stops entirely.

Diagnose:

-- Review all destination states
SELECT DEST_ID, STATUS, TARGET, SCHEDULE, DESTINATION, FAIL_COUNT
FROM   V$ARCHIVE_DEST
ORDER  BY DEST_ID;
Enter fullscreen mode Exit fullscreen mode

Fix:

-- Re-enable deferred destinations
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1 = ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE;

-- Lower minimum succeed requirement temporarily
ALTER SYSTEM SET LOG_ARCHIVE_MIN_SUCCEED_DEST = 1 SCOPE=BOTH;

-- Change Standby destination from MANDATORY to OPTIONAL
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 =
  'SERVICE=standby OPTIONAL REOPEN=300' SCOPE=BOTH;
Enter fullscreen mode Exit fullscreen mode

After applying any fix, manually trigger a log switch to flush pending archives:

ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

Monitor FRA usage proactively. Schedule a monitoring script to alert when usage exceeds 80%.

-- Use this in your monitoring job
SELECT NAME,
       ROUND(SPACE_USED / SPACE_LIMIT * 100, 2) AS USED_PCT
FROM   V$RECOVERY_FILE_DEST
WHERE  SPACE_USED / SPACE_LIMIT > 0.8;
Enter fullscreen mode Exit fullscreen mode

Automate archive log cleanup with RMAN policies. Set a deletion policy so archive logs are purged automatically after backup, preventing silent disk accumulation.

-- Inside RMAN
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;

-- For Data Guard environments
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-16038 – Accompanies ORA-16014 in the alert log when archiving blocks the database.
  • ORA-19809 – FRA space limit exceeded; a direct trigger for ORA-16014 when FRA is the archive destination.
  • ORA-00257 – Archiver stuck error visible to end-user sessions, blocking all DML.
  • ORA-16055 – Raised when LOG_ARCHIVE_MIN_SUCCEED_DEST cannot be satisfied.

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