Oracle archive log destination full: V$ARCHIVE_DEST_STATUS, the ERROR state, and space

The most dangerous Oracle outage masquerades as “database up.” The instance shows OPEN and ACTIVE in V$INSTANCE, the listener answers TCP probes, existing sessions stay connected, and basic availability checks pass. But the database is frozen because ARCn cannot write archived redo logs and LGWR cannot switch online redo log groups. Every session that needs to generate redo hangs on log file switch (archiving needed), and new non-SYSDBA connections receive ORA-00257.

The root cause is almost always space. The archive destination filesystem is full, the Fast Recovery Area (FRA) hit its quota, the ASM disk group is exhausted, or an NFS mount dropped. V$ARCHIVE_DEST_STATUS.STATUS flips from VALID to ERROR (or in some FRA-quota cases FULL), and the ERROR column carries the specific failure text. The window between first warning and total hang can be hours or minutes depending on redo generation rate and the count of online redo log groups (typically 3 to 5).

What this means

When Oracle runs in ARCHIVELOG mode, ARCn (or LGWR in Data Guard SYNC configurations) must copy every filled online redo log to the configured archive destination before that redo log group can be reused. If the destination refuses writes, ARCn stops making progress. LGWR cycles through the remaining redo log groups, and once every group is either current or unarchived, LGWR cannot advance.

At that point:

• Existing sessions that issue COMMIT, INSERT, UPDATE, DELETE, or any DDL hang. They wait on log file switch (archiving needed) and receive no error.
• New non-SYSDBA connections get ORA-00257: archiver error, connect internal only until freed.
• The instance itself stays OPEN and ACTIVE. Naive monitoring that only checks V$INSTANCE.STATUS reports green.

This is the classic “Archive Hang” composite pattern. Treat it as PAGE, always: it is a total outage wearing an “up” disguise.

flowchart TD
    A[Archive destination full
or unreachable] --> B[ARCn cannot write
filled redo logs]
    B --> C[V$ARCHIVE_DEST_STATUS.STATUS
flips to ERROR or FULL]
    C --> D[Online redo logs cannot
be reused]
    D --> E[LGWR runs out of
switchable groups]
    E --> F[Sessions wait on
log file switch archiving needed]
    E --> G[New non-SYSDBA logins
get ORA-00257]
    F --> H[Database frozen
instance still OPEN]
    G --> H

The MANDATORY versus OPTIONAL destination attribute decides whether the hang is total. A failing MANDATORY destination blocks redo log switches regardless of other healthy destinations. A failing OPTIONAL destination is skipped, and Oracle proceeds as long as LOG_ARCHIVE_MIN_SUCCEED_DEST (default 1) destinations succeed. MANDATORY is only supported on destinations 1 through 10; destinations 11 through 31 cannot be mandatory. Most single-instance production databases have one local MANDATORY-equivalent destination, so the failure mode is effectively binary: if that destination cannot write, the database stops.

Common causes

Quick checks

Run these in order. All are read-only and safe.

-- 1. Archive destination status with error text
SELECT DEST_ID, STATUS, DESTINATION, ERROR
FROM V$ARCHIVE_DEST_STATUS
WHERE STATUS != 'INACTIVE'
ORDER BY DEST_ID;
-- Expect STATUS = VALID. ERROR or DEFERRED on a production dest is PAGE.

-- 2. FRA usage if FRA is the archive destination
SELECT NAME, SPACE_LIMIT/1073741824 AS limit_gb,
       SPACE_USED/1073741824 AS used_gb,
       SPACE_RECLAIMABLE/1073741824 AS reclaimable_gb,
       NUMBER_OF_FILES
FROM V$RECOVERY_FILE_DEST;
-- used_gb approaching limit_gb is the failure mode.

-- 3. Archiver process health
SELECT PROCESS, STATUS, STATE, LOG_SEQUENCE
FROM V$ARCHIVE_PROCESSES;
-- STATE should be IDLE or BUSY. STOPPED across all rows is PAGE.

-- 4. Online redo log group status
SELECT GROUP#, THREAD#, SEQUENCE#, BYTES/1048576 AS mb,
       ARCHIVED, STATUS
FROM V$LOG
ORDER BY GROUP#;
-- All groups ACTIVE or CURRENT with ARCHIVED=NO means LGWR is out of switchable groups.

-- 5. Wait event for the archive stall
SELECT EVENT, TOTAL_WAITS, TIME_WAITED_MICRO
FROM V$SYSTEM_EVENT
WHERE EVENT = 'log file switch (archiving needed)';
-- Non-zero and growing between samples confirms the hang mechanism.

# 6. Filesystem space on the archive destination (filesystem case)
df -h /u01/archivelog   # use your actual archive path

# 7. ASM disk group space (ASM case)
asmcmd lsdg

# 8. Alert log tail for the canonical hang warning
adrci exec="show alert -tail 50"
# Look for: Archived Log Destination ... ERROR
# Look for: Thread N cannot allocate new log, sequence N

How to diagnose it

1. Confirm the symptom. Run the V$ARCHIVE_DEST_STATUS query above. If any production destination shows ERROR (or FULL when the FRA is the destination), the diagnosis is largely done. Move to triage.

2. Read the ERROR column. It usually names the root cause directly: “No space left on device”, “Permission denied”, “Stale NFS file handle”, or “Disk quota exceeded”.

3. Determine destination type. Run SHOW PARAMETER LOG_ARCHIVE_DEST and inspect the attributes. A destination with MANDATORY will block redo switches on failure. Destinations 11 through 31 cannot carry the MANDATORY attribute.

4. Check whether the database is already hung. Sample V$SYSTEM_EVENT for log file switch (archiving needed) twice, 10 seconds apart. If TOTAL_WAITS is increasing, sessions are already stuck.

5. Distinguish ERROR from DEFERRED. DEFERRED means an operator intentionally disabled the destination via ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n=DEFER. ARCn will not retry a DEFERRED destination until you re-enable it. ERROR means ARCn tried and failed. The REOPEN attribute (default 300 seconds) controls retry cadence; if REOPEN was omitted for that destination, ARCn will not retry at all after the first error.

6. Inspect the FRA separately if applicable. V$RECOVERY_FILE_DEST is independent of OS-level df. A filesystem with free space can still have an FRA that is over quota, because DB_RECOVERY_FILE_DEST_SIZE is an Oracle-internal limit. Conversely, the FRA can show headroom while the underlying filesystem is full.

7. Verify the alert log timeline. Thread N cannot allocate new log, sequence N means the hang is imminent or in progress. The sequence number lets you correlate with V$LOG.SEQUENCE# to see exactly where LGWR is stuck.

Metrics and signals to monitor

Fixes

Free space on the destination (fastest, filesystem case)

The fastest recovery when the archive filesystem is full is to remove archived redo logs that are no longer needed. Do this with RMAN, never with rm directly, because Oracle and RMAN must agree about which logs still exist.

# Delete archive logs already backed up to tape or object storage.
# Always CROSSCHECK first so RMAN knows what is actually on disk.
rman target / <<'EOF'
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
DELETE NOPROMPT OBSOLETE;
EOF

DELETE NOPROMPT OBSOLETE respects your configured retention policy (recovery window or redundancy). If that policy is wrong, it will delete too little or too much. Verify SHOW RETENTION POLICY in RMAN before relying on it.

If you must delete specific logs by hand under extreme time pressure, only delete logs that are (a) backed up, (b) not needed for Data Guard gap resolution, and (c) older than the standby’s applied sequence. Confirm V$ARCHIVED_LOG.APPLIED = 'YES' first. This is a last resort, not standard practice.

Raise the FRA quota

If the FRA is the destination and SPACE_USED is near SPACE_LIMIT, raise the quota. The new limit takes effect immediately and does not require a restart. Confirm the underlying filesystem or ASM disk group can actually absorb the new quota first.

-- Increase FRA quota, scoped to BOTH so it survives restart.
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 200G SCOPE=BOTH;

Then run DELETE OBSOLETE so RMAN reclaims space from old backups, flashback logs, and archived logs that exceed retention.

Add space to the ASM disk group

For ASM destinations, the only real fix is to add disks to the disk group or rebalance to free capacity. Adding online redo log groups does not help here; the destination itself cannot accept writes.

# Inspect disk group attributes first
asmcmd lsattr -G RECO
# Add a disk to the disk group holding archive logs.
# This triggers a rebalance; expect elevated I/O until it completes.
sqlplus / as sysasm <<'EOF'
ALTER DISKGROUP RECO ADD DISK '/dev/oracleasm/disks/RECO05';
EOF

Re-enable a DEFERRED destination

If STATUS = DEFERRED, someone explicitly disabled the destination. Re-enable it.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE;

If the destination had an underlying problem such as a full filesystem, ARCn will fail again with ERROR until you fix that problem. Re-enabling alone does not help if the disk is still full.

Buy time with online redo log groups (does not fix the root cause)

Adding more or larger online redo log groups extends the window before the total hang by giving LGWR more switchable groups. This is a valid holding action while space is added, but it does not fix the root cause. The database will stall again once it cycles through the new groups and hits the same failed destination.

-- Add redo log groups. Confirm directory and size against existing groups first.
ALTER DATABASE ADD LOGFILE GROUP 5 ('/u01/oradata/redo05a.log') SIZE 2G;
ALTER DATABASE ADD LOGFILE GROUP 6 ('/u01/oradata/redo06a.log') SIZE 2G;

This is the right move when a backup job is still running and will free space soon, or storage is being added to the ASM disk group, and you need to keep the database accepting commits for the next 30 to 60 minutes. It is the wrong move when there is no plan to add destination capacity.

Recover from a hung database

If the database is already frozen and new connections fail with ORA-00257, connect as SYSDBA (which is allowed during an archiver stall) and clear space using the RMAN commands above. Once ARCn can write again, it will catch up automatically.

Do not kill ARCn at the OS level. Killing an Oracle background process will crash the instance and turn a recoverable stall into a crash recovery operation.

Prevention

• Monitor archive destination free space separately from tablespace free space. Tablespace monitoring does not catch archive destination exhaustion. This is the number-one blind spot for Oracle operators.
• Alert at 85% TICKET and 95% PAGE. Give yourself hours of runway, not minutes. With a sustained redo generation rate, 5% free can disappear in under an hour.
• Treat the FRA as its own signal. V$RECOVERY_FILE_DEST is independent of OS df. If the FRA is the archive destination, monitor both.
• Use RMAN retention policies that match FRA size. A 7-day recovery window combined with an FRA sized for 3 days of archives guarantees a future hang.
• Define an ALTERNATE destination. Configure a secondary destination with LOG_ARCHIVE_DEST_STATE_n = ALTERNATE so ARCn fails over automatically when the primary fills. This turns a total hang into a TICKET.
• Validate backups regularly. Backups silently failing for days means RMAN will not delete obsolete logs, and the destination fills. Track V$RMAN_BACKUP_JOB_DETAILS.
• Track redo generation rate trends. A 2x redo rate halves your runway. See Oracle redo generation rate: capacity planning for archiving and Data Guard.
• Page on cannot allocate new log. This alert log line means the hang is imminent or in progress. Treat it as PAGE, always.

How Netdata helps

• Per-second archive destination status. Netdata surfaces V$ARCHIVE_DEST_STATUS.STATUS and the ERROR text with one-second resolution, so a flip from VALID to ERROR is visible immediately rather than on a five-minute polling cycle.
• Correlated filesystem and FRA utilization. The same dashboard shows OS-level df on the archive mount, ASM disk group usage, and V$RECOVERY_FILE_DEST quota, letting you distinguish a full filesystem from an FRA quota problem at a glance.
• Wait event correlation. log file switch (archiving needed) next to archive dest status and ARCn state confirms the hang mechanism without a manual SQL session.
• Alert log ingestion. Netdata surfaces Thread N cannot allocate new log and Archived Log Destination ... ERROR as structured signals alongside metrics.

For the full setup, see Oracle Database monitoring with Netdata.

Related guides

• How Oracle Database actually works in production: a mental model for operators
• Oracle ‘Checkpoint not complete’: redo log sizing, DBWn, and log-switch stalls
• Oracle ’log file sync’ waits: slow commits, LGWR, and the redo path
• Oracle Database monitoring checklist: the signals every production instance needs
• Oracle Database monitoring maturity model: from survival to expert
• ORA-00257: archiver error, connect internal only until freed
• Oracle redo generation rate: capacity planning for archiving and Data Guard
• Oracle redo log switch frequency: undersized logs and checkpoint pressure
• Oracle slow commit cascade: when redo storage degrades and every transaction waits