how to define RMAN retention policy time?

Many a times, I heard DBA’s complaining about RMAN retention policy through which they face some problems during restore or  recovery.

For example a DBA posted that his retention policy is set to recovery window of 90 days, but when tried to restore the database to 30 days back date, RMAN saying recovery till that time is not possible. DBA wondered why this cannot be done if he has retention of 90 days.

Here is the answer for the same.

we all know that RMAN will store backup metadata in control file’s reusable location if recovery catalog is not configured. Generally this information will be stored for 7 days and can be changed by modifying CONTROL_FILE_RECORD_KEEP_TIME parameter.

whenever we define retention policy of RMAN either to recovery window or redundancy to some X value, we need to remember that  RMAN still follow the value defined for CONTROL_FILE_RECOD_KEEP_TIME. That means, if recovery window is set to 30 days, but the above parameter is set to 7 days (default), you cannot recover beyond 7 days… which is very unfortunate…

Right ! so lets see what can be done for this?

we need to define CONTROL_FILE_RECORD_KEEP_TIME always a higher value than what we set for retention policy. As per the formula, it should be

CONTROL_FILE_RECORD_KEEP_TIME=retention policy value+ level 0 backup interval+ 1

let me throw some example for easy understanding…

If suppose your retention time is set to recovery window of 90 days and a weekly full level 0 backup is scheduled, then CONTROL_FILE_RECORD_KEEP_TIME = 90 (retention policy) + 7 (level 0 backup interval) + 1 = 98

Note: In worst case, atleast you should set that same (equal to) as retention policy value

What happens if I don’t set to higher value?

In such case, RMAN will overwrite the content of backup metadata prior to obsolete them by RMAN and even though physically backup pieces are existing, you cannot use them.