Category: Backup and Recovery

During routine monitoring, a critical issue was identified where the Fast Recovery Area (FRA) utilization reached 95%, putting the database at risk of ORA-19809, archiver process failure, and potential database hang. Despite having an RMAN retention policy configured, the FRA continued to grow uncontrollably.

---

Investigation

-- Check FRA usage
SELECT name, space_limit/1024/1024/1024 limit_gb,
       space_used/1024/1024/1024 used_gb,
       ROUND(space_used/space_limit*100,2) pct_used
FROM v$recovery_file_dest;

-- Check what is consuming space
SELECT file_type, percent_space_used, number_of_files
FROM v$flash_recovery_area_usage
ORDER BY percent_space_used DESC;

-- Review RMAN configuration
SHOW ALL;

Findings: backupsets were consuming the majority of FRA space. RMAN had a 7-day retention policy configured but backup optimization was disabled and archivelog deletion policy was not set.

---

Root Cause

The retention policy was defined but obsolete backups were not being automatically deleted. There was no scheduled execution of DELETE OBSOLETE, backup optimization was disabled causing redundant data, and archivelog lifecycle was not controlled. This led to continuous accumulation of backupsets and archivelogs.

---

Immediate Fix

-- Run RMAN cleanup
RMAN> CROSSCHECK BACKUP;
RMAN> DELETE EXPIRED BACKUP;
RMAN> DELETE OBSOLETE;

-- Verify FRA usage after cleanup
SELECT ROUND(space_used/space_limit*100,2) pct_used
FROM v$recovery_file_dest;

FRA utilization reduced from 95% to 83% after cleanup.

---

Configuration Fixes

-- Enable backup optimization
CONFIGURE BACKUP OPTIMIZATION ON;

-- Set archivelog deletion policy
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;

-- Enable compressed backups
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

---

Automation

Scheduled daily execution of DELETE OBSOLETE via cron:

-- Add to RMAN backup script
DELETE NOPROMPT OBSOLETE;

# Crontab entry for daily cleanup
0 2 * * * /path/to/rman_cleanup.sh >> /tmp/rman_cleanup.log 2>&1

---

Key Takeaways

• RMAN retention policy does not enforce deletion automatically — you must schedule DELETE OBSOLETE
• Backup lifecycle management must include automation
• Monitor FRA usage proactively to prevent saturation
• Enable backup optimization to avoid redundant data
• Set archivelog deletion policy to control archivelog lifecycle

---

Written by Syed Anwar Ahmed — Oracle Apps DBA with 11 years of production experience.
Connect: sdanwarahmed@gmail.com  |  LinkedIn