RMAN Retention and FRA Pressure on a 2 TB OLTP Database
Your 19c single-instance OLTP database is 2 TB and generates about 200 GB of redo per day. The backup window is 22:00–02:00. RMAN currently runs a full database backup every Sunday, an archived log backup every six hours, and uses CONFIGURE RETENTION POLICY TO REDUNDANCY 1. The fast recovery area is 500 GB on the same SAN as the datafiles. For three nights, the 01:00 archived log backup has failed with an FRA space error; the Sunday full backup also failed last week. Business requires the ability to recover to any point in the last 14 days. Block change tracking is disabled. The storage team can add a 4 TB mount for the FRA, but not change the four-hour backup window. Management asks whether the current strategy can meet the 14-day requirement and how to stop the FRA from filling.
1. Why does the FRA keep filling under the current configuration, and which RMAN retention and archived log deletion settings would you change before adding space?
Read the worked answer
REDUNDANCY 1 keeps one backup of each datafile and keeps archived logs only until they are no longer needed by that single backup; it cannot satisfy a 14-day point-in-time requirement. The 500 GB FRA is also too small for a 2 TB database with 200 GB redo per day. The first changes are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;CONFIGURE BACKUP OPTIMIZATION ON;CONFIGURE CONTROLFILE AUTOBACKUP ON;
A recovery window makes RMAN keep all backups and logs needed to recover to any SCN in the window. The deletion policy lets logs be removed after they are backed up, but not before they are obsolete under the window. Then resize the FRA to the 4 TB mount with ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=4T; and, if required, point DB_RECOVERY_FILE_DEST at the new mount. Run REPORT OBSOLETE and DELETE OBSOLETE only after verifying that the window is satisfied. Monitor V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA_USAGE; adding space alone only delays the same failure.
2. Design the incremental backup schedule to fit the window, using block change tracking. State the commands and the expected benefit.
Read the worked answer
Enable block change tracking once: ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u01/bct/cdb1.bct';. Take a weekly level 0 on Saturday or Sunday in the longest available window, then a daily level 1 differential at 22:00:
BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;BACKUP INCREMENTAL LEVEL 1 DATABASE PLUS ARCHIVELOG;
With a 10% daily change rate, the level 1 reads only changed blocks, so it should be far smaller and faster than a full backup. Use CONFIGURE DEVICE TYPE DISK PARALLELISM 4; and CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 32G; to use the four-hour window; consider SECTION SIZE 1G for large datafiles. Keep the six-hour archived log backups but ensure they do not collide with the level 1 job. Compression can reduce FRA use, but validate restore time before relying on it. The weekly level 0 still must fit, so schedule it in the longest window and monitor V$RMAN_BACKUP_JOB_DETAILS.
3. How would you prove the new configuration satisfies the 14-day recovery window without restoring the whole database?
Read the worked answer
Use RMAN preview and validation rather than a full restore. RESTORE DATABASE PREVIEW SUMMARY; lists the backups and archived logs RMAN would need for current recovery; REPORT NEED BACKUP RECOVERY WINDOW OF 14 DAYS; shows files that lack sufficient backups for the window. VALIDATE DATABASE; checks backups and archived logs for corruption without restoring, and RESTORE DATABASE VALIDATE; validates the restore path. Also run LIST BACKUP SUMMARY;, LIST ARCHIVELOG ALL;, CROSSCHECK BACKUP;, and query V$RMAN_BACKUP_JOB_DETAILS and V$RECOVERY_FILE_DEST. If the preview shows missing logs or backups older than 14 days, fix retention or space before declaring success. A preview is not a performance test; schedule a test restore of one tablespace to measure actual recovery time.