FRA Undersized for a 14-Day Recovery Window
Your 12 TB production database runs in ARCHIVELOG mode with a 6 TB fast recovery area (db_recovery_file_dest_size=6T). RMAN retention is RECOVERY WINDOW OF 14 DAYS. The schedule is a compressed level 0 backup every Sunday (about 4.5 TB of backup pieces), a level 1 differential every four hours, and block change tracking is enabled. The database generates about 1.2 TB of archived redo per day. When the FRA reached 96%, a host cron job began deleting archived log files older than 24 hours with find ... -mtime +1 -delete; shortly afterward, RMAN backups failed with ORA-19809, and some level 1 backups now fail with RMAN-06023/RMAN-06025. A test restore of a datafile from three days ago cannot find the required archived logs. The FRA contains only RMAN backups and archived logs.
1. Why did the OS deletion break recovery, and what can be salvaged now?
Read the worked answer
The cron job is the direct cause of the restore failure. RMAN's repository still lists the deleted archived logs; when a backup or restore needs them, RMAN reports RMAN-06023 (no backup or copy found) or RMAN-06025 (archived log not found). Deleting files outside RMAN bypasses the retention policy and can remove logs still inside the 14-day recovery window. The correct procedure after OS deletion is CROSSCHECK ARCHIVEDLOG and DELETE EXPIRED ARCHIVEDLOG, but that only cleans the catalog - it cannot bring the logs back. To recover, restore from a backup set that includes the missing logs or perform incomplete recovery to a point before the gap. ORA-19809 means the FRA is full; deleting files outside RMAN treats the symptom, not the sizing problem.
2. Size the FRA correctly and decide whether to keep the 14-day recovery window on disk.
Read the worked answer
Archived redo alone is 1.2 TB/day x 14 = 16.8 TB, already far above the 6 TB FRA. The level 0 backups (4.5 TB) plus multiple level 1s add several more terabytes, so a 14-day recovery window cannot fit in 6 TB. Also, a recovery window requires enough backups and logs to recover to any point in the window, not just the latest state. Either increase db_recovery_file_dest_size to at least 30-40 TB (and provision the filesystem), or move backups to a separate destination such as tape, Data Domain, or object storage while keeping only a short FRA. A common design is a 2-3 day FRA for archived logs and current backups plus a longer retention on the backup appliance. Do not shrink the recovery window to 2 days unless the business agrees; document the RPO/RTO change.
3. How should archived log deletion be controlled after the fix?
Read the worked answer
After resizing, configure RMAN to manage deletion: remove the cron job; run DELETE OBSOLETE after successful backups; ensure CONFIGURE ARCHIVELOG DELETION POLICY matches the architecture (for example, BACKED UP 1 TIMES TO DEVICE TYPE disk only if that meets the recovery window; otherwise leave the default and let obsolete backups govern). Run BACKUP ... PLUS ARCHIVELOG so logs are backed up and can be deleted safely. Monitor V$RECOVERY_AREA_USAGE and V$ARCHIVED_LOG for DELETED='NO'; alert at 80%. Use REPORT OBSOLETE and CROSSCHECK weekly. If the FRA must stay small, use BACKUP ARCHIVELOG ALL DELETE INPUT after the backup and keep a separate copy. Finally, test a restore to a three-day-old point in time to prove the window works.