Monday, February 23, 2015

Set CONTROL_FILE_RECORD_KEEP_TIME with RMAN Retention Policy


I wanted to share a guideline for setting your CONTROL_FILE_RECORD_KEEP_TIME parameter in the Oracle database especially when your using an RMAN retention policy. The default value for the CONTROL_FILE_RECORD_KEEP_TIME parameter is 7 days however if for example your taking RMAN database backups to disk and have limited space and know you can only have about 14 days of backups you would most likely be employing the RMAN retention policy as follows, this means keep all backups on disk that are required to recover the database within the last 14 days. Backups that are not needed for the 14 day recovery window will be marked as obsolete.

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;

The following command can be issued within your backup script to delete the obsolete backups.

RMAN> DELETE NOPROMPT OBSOLETE;


or you may run the following command as well if you do not have a recovery window defined.

RMAN> DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 14 DAYS;

The key thing to remember when you have an RMAN retention policy defined is to ensure your  CONTROL_FILE_RECORD_KEEP_TIME parameter is set to be Larger than defined retention policy otherwise you risk NOT deleting older backups and will use additional storage to keep stray backups!

If the CONTROL_FILE_RECORD_KEEP_TIME parameter is less than the retention policy then it may overwrite reusable records prior to obsoleting them in the RMAN metadata. Therefore it is recommended that the CONTROL_FILE_RECORD_KEEP_TIME parameter should set to a higher value than the retention policy.

Formula

CONTROL_FILE_RECORD_KEEP_TIME = retention period + level 0 backup interval + 1

For e.g.

e.q. level 0 backup once a week with retention policy of a recovery windows of 14 days then in this case the CONTROL_FILE_RECORD_KEEP_TIME should be 14+7+1=22 

This information is also documented in MOS note: Relation between RMAN retention period and control_file_record_keep_time (Doc ID 397269.1)



No comments:

Post a Comment