Pavan DBA's Blog

The DBA Knowledge Store

how to define RMAN retention policy time?

Posted by Pavan DBA on July 11, 2011

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 friend 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. he 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

Hope this will help you ……………….

15 Responses to “how to define RMAN retention policy time?”

  1. Sohail said

    Hi Pawan,

    i request to give more explanation on report obsolete in RMAN ?

  2. Sohail said

    Hi Pawan, Awesome information i got it from you, but still i have some more doubts about rman. As u said that windows or any platform default retention policy 90 days, where to check this and how? Especially iam using linux, so in linux envirement, what is the default retention policy. Awaiting for your response ……………

    • Pavan DBA said

      hi sohail, default is not 90 days. usually we maintain 90 days. to check about retention policy either in windows or linux, use below command in rman

      RMAN> show all; – u need to look into retention policy value

  3. Kapil Relan said

    Hello Pavan,

    I find retention in RMAN quite confusing.can you please put some focus on setting different retention policy for full, Incremental and Archive Log backups ?

    Best Regards,

    • Pavan DBA said

      Hi Kapil,

      We will be setting retention policy to recovery window of 30days to 90 days in our environments. It is same for full, incremental or archive log backups.

  4. Sumit Garg said

    Hi Pawan,

    Don’t you think that control file will auto grow in case you mentioned rather than overwritting that.It should auto grow till OS limit permits but yes if control file can’t grow because of os size limit, that above case will happen ?


    • Pavan DBA said

      Hi Sumit,

      Controlfile will have two sections. Reusable and non-reusable areas. Non-reusable area contains details about controlfile parameters, locations of redologs and datafiles, latest scn etc.

      reusable section contains rman backup metadata info. so, control file will auto grow only to store non-reusable information, not for reusable data

  5. shahid ahmed said

    Firstly i would like to thanks for this post. The description is very useful and in simple and in clear word.
    I Don’t tested on higher version but in oracle 9i the parameter is

    Please verify if i am correct please add it because your post is useful for all of us.

  6. Ram said

    Too much informational Document …

    Thank you very much.


  7. Rajan.S said

    its very useful

  8. nagarjuna reddy said

    Hi Pavan,

    Very useful information.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: