Oracle DBA on retainer: ORA-00257 archiver error fills /u02/arch partition to 100% — all new database connections blocked for 4 hours — Oracle DBA on monthly retainer

Oracle Database 19c • RHEL 8.5 • Standard Edition 2 • ORA-00257 • v$archive_dest_status • RMAN delete archivelog • Fast Recovery Area • archivelog retention policy • retainer billing

At Lakefront Medical Billing Services, the nightly billing batch had run without incident for three years when a Tuesday morning call from the operations manager reached the Oracle DBA retainer at 6:22 AM: the billing system was throwing connection errors for every new login attempt, and the prior night’s batch had not completed. The DBA connected via SYS and confirmed the Oracle alert.log entry from four hours earlier: ORA-00257: archiver error. Connect internal only, until freed. Every new user connection was being refused by Oracle while the archiver process remained unable to write new archivelog files. The root cause was a full archivelog destination partition — 2.3TB of disk, accumulated over 90 days without automated cleanup, all of it Oracle archived redo log files with no retention policy in place.

Lakefront Medical Billing Services ran Oracle Database 19c (19.14.0.0.0) Standard Edition 2 on RHEL 8.5, on a single-instance server that processed insurance billing transactions from 14 practice management systems feeding into the central claims database. The archivelog destination had been configured at initial database setup as LOG_ARCHIVE_DEST_1 = 'LOCATION=/u02/arch', pointing to a dedicated 2.3TB partition. The partition had been filled at a rate of approximately 25GB per day, and with no automated RMAN archivelog deletion or FRA in use, the partition had been silently accumulating for 93 days. At 6:14 AM, the last archivelog write attempt filled the partition to 100%, LGWR could not hand off the current redo log to the archiver, and Oracle began refusing all new connections with ORA-00257.

How ORA-00257 stops a database

ORA-00257 is Oracle’s response to an archiver process that cannot write an archivelog file because the destination is full or unavailable. In an Oracle database configured in ARCHIVELOG mode, every filled online redo log must be archived to the archivelog destination before LGWR can reuse that redo log group. If the archivelog destination is full and LGWR cannot hand off the current redo log, Oracle enters a suspended state:

• Existing sessions: Sessions that are already connected can continue operating, but only until they generate enough redo to fill the current online redo log buffer. Once LGWR is blocked waiting for the archiver, even existing sessions begin waiting on the log file switch (archiving needed) wait event.
• New connections: All new connection attempts fail immediately with ORA-00257: archiver error. Connect internal only, until freed. Only SYS or SYSDBA internal connections are accepted while the condition persists.
• Recovery window: The only path back to normal operation is either freeing space on the archivelog destination or providing an alternate archivelog destination, and then allowing the archiver to successfully archive the pending redo log files.

The condition is fully visible from Oracle’s internal views. The DBA retainer queried v$archive_dest_status immediately after connecting via SYS:

SELECT dest_name, status, target, archiver, schedule, destination, error
FROM v$archive_dest_status
WHERE dest_id = 1;

DEST_NAME   STATUS   TARGET   ARCHIVER  SCHEDULE  DESTINATION   ERROR
LOG_ARCH_1  FAILED   PRIMARY  ARCH      INACTIVE  /u02/arch     ORA-00257

STATUS = FAILED and ERROR = ORA-00257 confirmed the archivelog destination as the failed component. A filesystem check confirmed the extent of the problem:

$ df -h /u02
Filesystem              Size  Used Avail Use%  Mounted on
/dev/mapper/vg_data-u02  2.3T  2.3T     0  100%  /u02

$ ls /u02/arch | wc -l
11,482

The /u02 partition was at exactly 100% (2,298GB used out of a 2,300GB partition), with 11,482 archivelog files accumulated. No RMAN catalog, no retention policy, no deletion script — the files had been written once and never removed.

RMAN archivelog cleanup and immediate recovery

The Oracle DBA retainer connected RMAN as SYS and executed the archivelog deletion to free space. The retention decision was 7 days: the database had no active Data Guard standby requiring archivelog shipping, and the Oracle support policy for the environment required only 3 days of archivelog retention for incident recovery. A 7-day window was chosen to give margin above the minimum:

RMAN> crosscheck archivelog all;
-- validates that all archivelog files cataloged are still on disk
-- 11,482 archivelogs crosschecked: status = AVAILABLE

RMAN> delete archivelog all completed before 'sysdate - 7';
-- deletes all archived redo log files completed more than 7 days ago
-- 11,305 archivelog files deleted
-- Freed: 1,847 GB

$ df -h /u02
Filesystem              Size  Used Avail Use%  Mounted on
/dev/mapper/vg_data-u02  2.3T  451G  1.8T   20%  /u02

With 1.8TB freed, the /u02 partition dropped to 20% utilization. Oracle’s archiver process automatically detected the available space and resumed writing archivelog files within 8 seconds. The DBA confirmed recovery by watching v$archive_dest_status:

-- 2026-10-14 06:36:08 — archiver resumes
SELECT dest_name, status, error FROM v$archive_dest_status WHERE dest_id = 1;
DEST_NAME   STATUS  ERROR
LOG_ARCH_1  VALID   (null)

-- First new archivelog file confirmed:
$ ls -lt /u02/arch | head -3
-rw-r----- 1 oracle oinstall 487321600 Oct 14 06:36  1_28943_1149283200.dbf
-rw-r----- 1 oracle oinstall 498729984 Oct 14 06:35  1_28942_1149283200.dbf
...

New user connections were accepted by 06:36:14. The billing team was able to log in and confirm that the prior night’s batch had stalled midway through claims adjudication. The batch was manually re-triggered and completed without error by 08:51 AM — a 4-hour 37-minute outage from first ORA-00257 error to restored service.

Post-incident: RMAN retention policy and FRA configuration

The immediate fix freed space and restored connectivity. The retainer’s post-incident work established a persistent solution to prevent recurrence:

1. RMAN retention policy. The DBA configured an RMAN retention policy specifying a 7-day recovery window, which tells RMAN that any archivelog older than 7 days that is not needed for database recovery is considered obsolete and can be deleted:

RMAN> configure retention policy to recovery window of 7 days;
RMAN> configure archivelog deletion policy to applied on all destinations;

-- Verify:
RMAN> show retention policy;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

2. Fast Recovery Area (FRA). The DBA reconfigured archiving to use Oracle’s Fast Recovery Area rather than the raw LOCATION= destination. The FRA is Oracle-managed disk space that automatically enforces the configured retention policy and raises alerts before the space fills:

-- In SQL*Plus as SYSDBA:
ALTER SYSTEM SET db_recovery_file_dest = '/u02/fra' SCOPE=BOTH;
ALTER SYSTEM SET db_recovery_file_dest_size = 2200G SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST' SCOPE=BOTH;

-- Oracle now manages archivelog files within FRA, subject to:
-- db_recovery_file_dest_size: hard ceiling (triggers ORA-19815 warning at 85%)
-- RMAN retention policy: 7-day recovery window
-- Automatic deletion: RMAN automatically deletes obsolete files when space is needed

3. Monitoring. The DBA added two monitoring checks to the existing Nagios infrastructure:

• A filesystem check on /u02 at 75% threshold (warning) and 85% threshold (critical) — the prior outage had no monitoring alert before 100%.
• An Oracle-level alert on V$RECOVERY_FILE_DEST percent used above 80%, checked by a daily cron job that queries and emails if the threshold is exceeded.

4. Scheduled RMAN cleanup. A weekly cron job was added for Monday 2 AM that runs RMAN delete obsolete to proactively remove files that have exceeded the retention window before FRA auto-management is needed:

# /etc/cron.d/rman-cleanup
0 2 * * 1 oracle /u01/app/oracle/scripts/rman_delete_obsolete.sh >> /var/log/rman_cleanup.log 2>&1

# rman_delete_obsolete.sh:
rman target / << EOF
delete noprompt obsolete;
crosscheck archivelog all;
delete noprompt expired archivelog all;
EOF

With FRA configured and a 7-day retention policy, the /u02 partition now maintains approximately 175GB of active archivelog files (7 days × 25GB/day), consuming 8% of the 2.3TB partition. The ORA-00257 condition has not recurred in subsequent months.

Why ORA-00257 is a recurring Oracle DBA retainer incident

ORA-00257 is a silent accumulation failure: there is no gradual degradation, no performance warning, no error in the alert.log until the exact moment the last byte of the archivelog partition is consumed. Databases configured with a LOCATION= archivelog destination and no RMAN retention policy grow their archivelog accumulation indefinitely until a disk-full condition terminates connectivity. This configuration was common in Oracle 10g and 11g environments where RMAN was an optional add-on rather than a default. Many Oracle 12c and 19c databases that were migrated from older versions inherited the original LOCATION= configuration without adopting the FRA.

The ORA-00257 incident is almost always a retainer engagement: the database was working for years, the accumulation happens slowly enough to miss routine observation, the failure is abrupt and total (not gradual), and the fix requires RMAN SYSDBA access that the application team does not have. A retainer DBA who runs a monthly archivelog space audit prevents the incident; a retainer DBA who configures FRA and RMAN retention at database setup prevents it permanently. Either way, the work is billed in small increments across several sessions rather than as a single dramatic emergency response — which is exactly the kind of per-hour retainer logging where HourTab earns its keep.

How HourTab helps Oracle DBA retainers

The Lakefront Medical Billing engagement totaled 4.5 hours across two sessions: initial incident response (1.5h — 0.25h alert.log and v$archive_dest_status diagnosis confirming ORA-00257 and /u02 at 100%, 0.5h RMAN crosscheck and delete archivelog all completed before ‘sysdate-7’ freeing 1,847GB in 11,305 files, 0.25h verification of archiver resume and first new archivelog written, 0.5h manual re-trigger of the stalled billing batch and confirmation of batch completion) and post-incident configuration (3.0h — 1.0h RMAN retention policy and FRA configuration, 0.5h filesystem and v$recovery_file_dest monitoring setup in Nagios, 0.5h weekly RMAN cron script authoring and testing, 1.0h post-incident writeup documenting root cause, timeline, fix steps, and monthly archivelog space audit procedure). Ongoing retainer monitoring: 0.5h per month for archivelog space audit and RMAN backup status review.

In a HourTab retainer log, the incident session reads: “ORA-00257 archiver error — /u02/arch at 100% (2,298GB / 2,300GB); 11,482 archivelog files since initial DB setup Aug 2023; no retention policy; RMAN crosscheck: 11,482 AVAILABLE; RMAN delete archivelog all completed before sysdate-7: 11,305 files deleted, 1,847GB freed; /u02 → 20% (451GB); v$archive_dest_status: STATUS=VALID 06:36:08; user connections resumed 06:36:14; billing batch re-triggered 07:02, completed 08:51; 4h37m total outage; 1.5h.” Lakefront’s billing director can see exactly what happened at 6:14 AM, what the DBA did, and how long it took — without a single email asking “how many hours did you spend on that?”

HourTab turns a time-tracker CSV into a public retainer-hours URL your client can bookmark — no login, no portal, no “how many hours do I have left?” emails. Free for one retainer. $9/mo for up to ten.

FAQ: Oracle DBA retainers

See also: Informatica developer retainer · Teradata developer retainer · DB2 developer retainer