What Limit and Retention Settings Are Really Controlling the Flash Recovery Area
FINDB runs Oracle 19c on a single instance with a 900 GB database, ARCHIVELOG mode, and a 400 GB fast recovery area on a SAN LUN. RMAN is configured with RETENTION POLICY TO REDUNDANCY 2, and a nightly job at 01:00 takes a compressed level 0 database backup plus an archive log backup. Block change tracking is disabled and no level 1 backups exist because "incrementals were never needed". The compressed level 0 backup is 260 GB, and the database generates 55 to 70 GB of archived redo per day. For the last two nights the job has failed with ORA-19809: limit exceeded for recovery files and ORA-19804: cannot reclaim 52428800 bytes of disk space. V$FLASH_RECOVERY_AREA_USAGE shows the backup area at 61 percent and archive logs at 38 percent. This morning a query on the ORDERS table returned ORA-01578 with a block number, and the team has asked for a fix that fits inside the existing 400 GB without new storage.
1. Explain with concrete arithmetic why the FRA filled up, and why RMAN did not simply delete older archive logs to make room.
Read the worked answer
Start from the retention rule, not the free space. With REDUNDANCY 2, RMAN must keep two complete backups and every archive log required to make either of them recoverable to any point up to the newer backup. That means archives from the start of the older level 0 forward are retained. Two level 0 backups alone are 2 x 260 = 520 GB, which already exceeds 400 GB before a single archived log is counted. Add roughly two to three days of redo at 55-70 GB per day and the requirement is closer to 650 GB.
RMAN will not reclaim archives while they are still needed by a retained backup, so DELETE OBSOLETE removes nothing and the FRA sits at its limit. The failure is self-reinforcing: once a backup fails, the previous backup remains the most recent recoverable copy, so it can never become obsolete, and the next run fails for the same reason. V$FLASH_RECOVERY_AREA_USAGE confirming 61 percent backups and 38 percent archives is consistent with this; a leak or an OBSCURED file storm would show a large UNKNOWN or OBSCURE percentage instead. Confirm with REPORT OBSOLETE, LIST BACKUP SUMMARY, and SELECT * FROM V$RECOVERY_FILE_DEST before changing anything.
2. Design a replacement backup strategy that fits in 400 GB and still supports full recovery, and explain how you would address the ORA-01578 without taking the datafile offline for hours.
Read the worked answer
Reduce both the backup footprint and the archive burden. Enable block change tracking (ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ...), keep a weekly level 0 and take a daily level 1; with a 900 GB database, a typical daily differential is a fraction of a full backup. Configure BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT so archives are removed as soon as they are backed up, and set CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK. Change retention to a recovery window (for example, RECOVERY WINDOW OF 2 DAYS) so the required archive span is bounded and predictable rather than tied to two full backups.
Size the FRA as: one level 0 (260 GB compressed, smaller once never-used blocks are excluded) + daily level 1s for the window + archives generated during the window + about 20 percent headroom for a restore of the backup being replaced. Two days of archives at 70 GB is 140 GB, which plus a full backup fits in 400 GB only because the level 1s replace the second full.
For the corruption, run BACKUP VALIDATE DATAFILE ... or VALIDATE DATABASE, then query V$DATABASE_BLOCK_CORRUPTION. If the block is repairable from a backup plus archived redo, use BLOCKRECOVER DATAFILE n BLOCK m; block media recovery keeps the datafile online and touches only the damaged blocks, unlike RESTORE DATAFILE followed by RECOVER, which requires the whole file.
3. How would you verify, before trusting it, that the new configuration actually meets the recovery requirement and will not repeat the same failure?
Read the worked answer
Verification has to be empirical, not a parameter review. Run REPORT NEED BACKUP and REPORT UNRECOVERABLE to confirm the window is satisfied and nothing is unprotected; run REPORT OBSOLETE and DELETE OBSOLETE to confirm the retention policy now releases space. Query V$BACKUP_FILES and V$RMAN_BACKUP_JOB_DETAILS to see real sizes and run times for the level 0 and level 1 jobs, then project them forward against the 400 GB limit, including the archives that accumulate between runs.
The decisive test is a restore rehearsal into a scratch host or an auxiliary instance: RESTORE DATABASE plus RECOVER DATABASE until a timestamp that matches the stated recovery window, without the production archive logs available beyond the FRA. If that succeeds, the window is real. Also test a point-in-time recovery to a timestamp inside the window, since the window promise is about arbitrary points, not only the end state. After go-live, monitor V$FLASH_RECOVERY_AREA_USAGE and the alert log daily, and create a threshold alert on SPACE_USED/SPACE_LIMIT above roughly 85 percent so the next drift is caught weeks before a job fails, rather than after two consecutive failed nights.