Recovery Window Versus Fast Recovery Area Capacity
SALESPROD is a 2.4 TB Oracle 19c single-instance database in ARCHIVELOG mode. RMAN runs a compressed level 0 backup every Sunday and a level 1 differential backup nightly; block change tracking is enabled. The documented recovery strategy is a 30-day recovery window with a 4-hour RTO. Backups are written to a 500 GB Fast Recovery Area and then copied to tape by a third-party scheduler. The tape copy job has failed for nine days, and the FRA is now 96% full with ORA-19815 and ORA-19809 in the alert log. Archived redo generation averages 55 GB per day, and the compressed level 0 backup is 1.1 TB. Last night's level 1 job failed after 70 minutes. An on-call DBA has been deleting old archived logs with the OS rm command to keep the database running. There is no Data Guard or logical standby. The business requires point-in-time recovery to any day in the last 30 days and no more than 4 hours of downtime.
1. What is the immediate safe sequence to relieve the FRA and re-establish recoverability? Explain why the OS deletions are dangerous.
Read the worked answer
The OS deletions are dangerous because they bypass the RMAN repository. RMAN still records those archived logs as available, so RESTORE/RECOVER can fail with missing-file errors, and retention calculations become wrong. The recovery window may already be compromised if the logs were not copied to tape.
Safe sequence:
- Stop deleting files with
rm. - Increase
DB_RECOVERY_FILE_DEST_SIZEif storage can be added, or move backups off the FRA immediately. - Run
CROSSCHECK ARCHIVELOG ALLandDELETE EXPIRED ARCHIVELOG ALLso RMAN learns which files are gone. - Run
CROSSCHECK BACKUPandDELETE EXPIRED BACKUP; then take a fresh archived log backup to tape or object storage. - Take a new level 0 backup as soon as possible, because the last nine days of archived logs may not have a durable copy.
- Only after verifying the tape copy, set an archived log deletion policy that matches the recovery window.
Do not use DELETE ARCHIVELOG ALL FORCE until a verified backup exists; that would destroy the only recovery material.
2. Which RMAN retention policy and archived log deletion policy would satisfy the 30-day recovery window while keeping the FRA bounded?
Read the worked answer
Use a recovery-window retention policy, not redundancy: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS. A redundancy policy says how many copies exist, not how far back you can recover, so it does not prove the 30-day requirement.
The FRA cannot be the system of record. At 55 GB/day, 30 days of archived logs is about 1.65 TB, plus a 1.1 TB level 0, which exceeds 500 GB. Treat the FRA as staging: back up archived logs to tape or object storage daily with BACKUP ARCHIVELOG ALL DELETE INPUT only after the copy succeeds, or use BACKUP ARCHIVELOG ALL followed by a deletion policy. Set ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DEVICE TYPE sbt if tape is the durable destination. If object storage is used, adjust the device type accordingly.
Verify with REPORT NEED BACKUP RECOVERY WINDOW OF 30 DAYS and monitor V$RECOVERY_FILE_DEST and V$BACKUP_FILES. The policy must keep enough archived logs on tape to restore to any day in the window, including before the failed tape job started.
3. How would you prove the 4-hour RTO can be met without restoring over production?
Read the worked answer
Use RMAN preview and an actual test restore on a separate host. First run RESTORE DATABASE PREVIEW SUMMARY and RECOVER DATABASE PREVIEW to see which backups and archived logs are required. Then run RESTORE DATABASE VALIDATE to check media integrity without restoring datafiles.
For a real RTO proof, duplicate the database to a non-production host from the same backup stream and time each phase: restore of the 1.1 TB level 0, application of level 1 incrementals, archived log recovery, and open/resetlogs. Include the time to retrieve tapes from the vault, because that is part of the 4-hour clock. Use comparable storage and network; a restore to fast local disk will understate production time.
Also verify that the retention policy has not marked required pieces obsolete: run CROSSCHECK and REPORT OBSOLETE, then confirm the test restore does not request an already-deleted archived log. Document the measured restore time and the tape-retrieval dependency in the runbook.