How to Define RMAN Retention Policy Time

How to Define RMAN Retention Policy Time

Many times, I hear DBAs complaining about RMAN retention policies, which can sometimes cause problems during restore or recovery operations.

For example, a friend shared that his retention policy was set to a recovery window of 90 days.

However, when he tried to restore the database to a point 30 days ago, RMAN reported that recovery to that time was not possible. 

He was puzzled—why couldn’t he restore if the retention was set to 90 days?

Here’s the explanation:

---

We all know that RMAN stores backup metadata in the control file’s reusable section if a recovery catalog is not configured. By default, this information is retained for 7 days, but the duration can be modified using the CONTROL_FILE_RECORD_KEEP_TIME parameter.

Whenever you define an RMAN retention policy, whether it’s based on a recovery window or redundancy, it’s essential to remember that RMAN still adheres to the value set for CONTROL_FILE_RECORD_KEEP_TIME.

For instance:

• If the recovery window is set to 30 days but CONTROL_FILE_RECORD_KEEP_TIME is left at its default value of 7 days, you cannot recover beyond 7 days.
• This can be frustrating and may lead to confusion.

---

How to Address This Issue

To resolve this, you must define CONTROL_FILE_RECORD_KEEP_TIME with a value that is greater than or equal to the retention policy. Use the following formula:

Formula

$$\text{CONTROL\_FILE\_RECORD\_KEEP\_TIME} = (\text{Retention Policy Value} + \text{Level 0 Backup Interval} + 1)$$

---

Example Scenarios

1. For a 31-Day Retention Window:

   • Retention Policy: 31 days
   • Level 0 Backup Interval: Weekly (7 days)
   • Calculation:
     $$\text{ CONTROL\_FILE\_RECORD\_KEEP\_TIME}=31+7+1=39\text{ days}$$
   • Configuration:

     CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 31 DAYS;
     ALTER SYSTEM SET CONTROL_FILE_RECORD_KEEP_TIME=39;
     

2. For a 90-Day Retention Window:

• Retention Policy: 90 days
• Level 0 Backup Interval: Weekly (7 days)
• Calculation: $$\text{CONTROL\_FILE\_RECORD\_KEEP\_TIME} = 90 + 7 + 1 = 98 \text{ days}$$
• Configuration:

  CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 90 DAYS;
  ALTER SYSTEM SET CONTROL_FILE_RECORD_KEEP_TIME=98;
  

---

Why Is This Important?

If CONTROL_FILE_RECORD_KEEP_TIME is not set to a value higher than the retention policy, RMAN will overwrite backup metadata in the control file before marking the backups as obsolete.

Even if the backup files still exist on disk, they cannot be used for recovery because their metadata has been removed. This is why ensuring alignment between CONTROL_FILE_RECORD_KEEP_TIME and your retention policy is critical.

---

Best Practices

• Always configure CONTROL_FILE_RECORD_KEEP_TIME based on the formula above.
• If you are using a Recovery Catalog, metadata is stored in the catalog indefinitely (until explicitly purged), minimizing this issue.
• Regularly verify RMAN configuration with:

  SHOW ALL;

• Test your backup and recovery processes periodically to ensure they align with business requirements.

---

Conclusion

By configuring CONTROL_FILE_RECORD_KEEP_TIME appropriately, you can prevent unnecessary backup metadata loss and ensure your RMAN retention policies work as intended.