ORA-00257: archiver error, connect internal only until freed

Mid-incident: users report the application is hung. New connections from application servers fail with ORA-00257. Existing sessions are stuck and return no error. The instance reports OPEN and ACTIVE. The listener responds to TCP probes. A read-only health check says the database is up. The database is not up.

ORA-00257 is the connect-time symptom of an Archive Hang. The archiver process (ARCn) cannot copy filled online redo logs to the archive destination. Online redo logs fill, the log writer (LGWR) cannot switch to a new log group, and every session that needs to generate redo freezes. Only non-SYSDBA sessions attempting to connect see an error. Everyone else just waits.

The danger is the mask. Instance status is OPEN. The listener responds. A SELECT 1 FROM DUAL may succeed if it does not generate redo. Teams often discover the real cause 30 to 60 minutes into the outage, after ruling out locks, network, CPU, and data file storage.

This guide covers how to confirm an Archive Hang in the first minutes, identify the failing destination, and restore redo flow without losing recoverability.

What this means

Oracle in ARCHIVELOG mode must copy every filled online redo log to the archive destination before that log group can be reused. ARCn processes perform this copy. If all archive destinations fail, fill, or defer, the online redo logs accumulate. LGWR cycles through the log groups, but once every group is either active (unarchived) or needed for recovery, LGWR cannot allocate a new log. Write activity halts.

The cascade:

flowchart TD
    A[Archive destination full or unreachable] --> B[ARCn cannot write filled redo logs]
    B --> C[Online redo logs cannot be reused]
    C --> D[LGWR cannot allocate new log]
    D --> E[All redo-generating sessions wait on log file switch archiving needed]
    E --> F[TPS drops to zero]
    E --> G[New non-SYSDBA connections get ORA-00257]
    E --> H[Instance still reports OPEN and ACTIVE]

The database is technically alive. SMON, PMON, DBWn, and the listener are all functioning. Only the redo pipeline is jammed. Any monitoring that relies on instance status, listener response, or a read-only query returns green while production is frozen.

The alert log signals the progression. The first sign is typically Archived Log Destination ... ERROR. As the situation worsens, you see Thread N cannot allocate new log, sequence N. That message means the hang is imminent or in progress.

Common causes

The FRA quota trap is a common pitfall. DB_RECOVERY_FILE_DEST_SIZE sets a logical limit inside the FRA, independent of the underlying filesystem or ASM capacity. The archiver fails when that quota is exhausted, even if the disk has terabytes free. The alert log typically shows ORA-19809 before ORA-00257. Operators who only check df -h miss the real constraint.

Quick checks

Run these read-only checks in the first few minutes. All are safe.

-- Confirm the instance reports OPEN/ACTIVE while actually frozen
SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS, ACTIVE_STATE
FROM V$INSTANCE;

-- Archive destination status and any recorded error
SELECT DEST_ID, STATUS, DESTINATION, ERROR
FROM V$ARCHIVE_DEST_STATUS
WHERE STATUS <> 'INACTIVE';

-- Archiver process state (IDLE or BUSY is normal, STOPPED is not)
SELECT * FROM V$ARCHIVE_PROCESSES;

-- FRA utilization when 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
FROM V$RECOVERY_FILE_DEST;

-- Confirm the wait event driving the hang
SELECT EVENT, TOTAL_WAITS, TIME_WAITED_MICRO
FROM V$SYSTEM_EVENT
WHERE EVENT LIKE 'log file switch%';

# Check OS space on the archive destination
df -h /u01/app/oracle/fast_recovery_area
# Or wherever LOG_ARCHIVE_DEST_n points

# Tail the alert log for the specific progression
adrci exec="show alert -tail 50"

If log file switch (archiving needed) is non-zero and growing, and V$ARCHIVE_DEST_STATUS shows ERROR or the filesystem is full, the diagnosis is confirmed. Move to Fixes.

How to diagnose it

1. Confirm the wait event. Query V$SYSTEM_EVENT for log file switch (archiving needed). Non-zero and growing confirms the hang is in progress.
2. Check TPS. Query V$SYSSTAT for user commits, sample twice, and compute the rate. Near-zero TPS with application load present confirms the hang.
3. Identify the failing destination. V$ARCHIVE_DEST_STATUS returns one row per destination. Any row with STATUS of ERROR or DEFERRED is a candidate. The ERROR column often names the problem directly.
4. Distinguish filesystem space from FRA quota. Run df -h on the destination path. If the filesystem has space but the FRA is the archive destination, check V$RECOVERY_FILE_DEST. The two are independent constraints.
5. Check archiver process health. V$ARCHIVE_PROCESSES.STATE should be IDLE or BUSY. STOPPED indicates the archiver has given up.
6. Review the alert log for the error sequence. Look for ORA-19809 (FRA limit), Archived Log Destination ... ERROR, and finally Thread N cannot allocate new log.

Metrics and signals to monitor

Fixes

General principle: free or extend the archive destination first, then investigate why archiving stopped. Do not restart the instance. A restart does not clear archive destination problems and extends the outage.

Free space on the archive destination

If the destination filesystem is full, the safe action is to remove archive logs that have already been backed up to tape or object storage. Use RMAN to keep the catalog consistent.

-- In RMAN: remove backups and archive logs no longer needed per retention policy
RMAN> DELETE OBSOLETE;

-- Or remove archived logs already backed up the configured number of times
RMAN> DELETE ARCHIVELOG ALL BACKED UP 1 TIMES TO DEVICE TYPE SBT;

Never delete archive logs with rm at the OS level. If that happens, the FRA quota does not recognize the reclaimed space. Run a crosscheck and delete the expired entries.

RMAN> CROSSCHECK ARCHIVELOG ALL;
RMAN> DELETE EXPIRED ARCHIVELOG ALL;

Extend the FRA quota

When the FRA is the archive destination and SPACE_USED is near SPACE_LIMIT but the underlying filesystem or ASM disk group has capacity, raise the FRA quota.

-- Increase the FRA logical limit (does not allocate disk, just permits use)
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = <larger_value> SCOPE=BOTH;

This is a safe, online change. Set it to a value that fits the underlying storage and your retention requirements. This is the correct response when df -h shows space but V$RECOVERY_FILE_DEST is exhausted.

Restart a stopped archiver

If V$ARCHIVE_PROCESSES.STATE = 'STOPPED' and the destination is healthy, restart the archiver processes.

-- Restart archiver processes
ALTER SYSTEM ARCHIVE LOG START;

If a LOG_ARCHIVE_DEST_n destination is in ERROR state due to a transient condition (NFS blip, permissions fix), re-enable it with the REOPEN attribute.

-- Re-enable a destination with retry
ALTER SYSTEM SET LOG_ARCHIVE_DEST_n = 'LOCATION=/u01/archivelog REOPEN';

Address MANDATORY destination failures

If a MANDATORY destination fails and you cannot restore it quickly, defer it to allow redo flow to continue. Only do this if at least one other MANDATORY destination succeeds or no MANDATORY requirement is in effect. Deferring a MANDATORY destination required for your data protection SLA puts recoverability at risk. Treat this as a last resort during an active outage.

-- Defer a failing destination temporarily
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n = DEFER;

-- Re-enable after the destination is repaired
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n = ENABLE;

Buy time with redo log groups

Adding online redo log groups or increasing member size does not fix the root cause, but it extends the buffer before the hang becomes total. Use this only as a bridge while resolving the destination problem.

-- Add a redo log group
ALTER DATABASE ADD LOGFILE GROUP <n> ('/path/to/redoNn.log') SIZE <size>;

The buffer duration depends on the redo generation rate and total unarchived redo space. If archive throughput is zero, the buffer is finite and the hang resumes.

Prevention

Prevent the Archive Hang with a small number of disciplined practices.

• Monitor archive destination space separately from tablespaces. Tablespace monitoring does not cover the FRA or the archive filesystem. Alert at 85% utilization and page at 95%.
• Monitor V$RECOVERY_FILE_DEST if using FRA. Track SPACE_USED / SPACE_LIMIT and alert when SPACE_RECLAIMABLE > 0 with SPACE_USED near the limit. That condition means RMAN should be able to reclaim space and has not.
• Size archive throughput for at least 2x peak redo rate. The destination should hold at least 24 hours of archive logs at peak generation.
• Validate the RMAN retention policy and backup schedule. If backups fail silently, archive logs accumulate and the destination fills. Monitor V$RMAN_BACKUP_JOB_DETAILS for failures.
• Run a real DML health check. A read-only SELECT 1 FROM DUAL does not exercise the redo path. A health check that does an INSERT, COMMIT, DELETE, COMMIT into a small table will hang when the archive path is jammed.
• Watch the alert log for progression markers. Archived Log Destination ... ERROR is the early signal. cannot allocate new log is the late signal.
• Document the FRA quota. Operators who only know the filesystem size will misdiagnose the FRA quota trap. Record both values in runbooks.

How Netdata helps

• Per-second visibility into archive destination filesystem utilization catches the fill before the hang, not after.
• The Oracle DB collector surfaces V$ARCHIVE_DEST_STATUS, V$RECOVERY_FILE_DEST, and V$ARCHIVE_PROCESSES so the FRA quota and archiver health are visible alongside filesystem free space.
• TPS and redo generation rate trends let you see throughput drop to zero in real time, before users report it.
• Anomaly detection on redo log switch frequency surfaces a climbing rate early, which is a leading indicator of archive pressure.
• Correlated alerting across filesystem space, archive destination status, and alert log patterns compresses the diagnosis from 30 minutes to under five.
• The database health check that exercises DML rather than a plain SELECT confirms the redo path end to end.

Netdata’s Oracle Database monitoring with Netdata brings these signals together with per-second metrics.

Related guides

• How Oracle Database actually works in production: a mental model for operators
• 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
• Oracle slow commit cascade: when redo storage degrades and every transaction waits