10 Replies Latest reply: Aug 2, 2011 2:25 AM by Nicolas.Gasparotto RSS

    control_file_record_keep_time vs retention policy

    orawarebyte
      Its not thread nor question just read from documents and tried to imply the fact of documents and i think its written correct what i tried to do proof it practically ,neither i am trying to be smart just share with you guys is my observation correct or not.i am using FRA area for backup.

      <quote>

      Relation between RMAN retention period and control_file_record_keep_time

      RMAN backup keeps the backup metadata information in the reusable section of the controlfile.Its depends on the parameter CONTROL_FILE_RECORD_KEEP_TIME. CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days before a reusable record in the control file can be reused.

      In the event a new record needs to be added to a reusable section and there is not enough space then it will delete the oldest record, which are aged enough.

      Backup retention policy is the rule to set regarding which backups must be retained (whether on disk or other backup media) to meet the recovery and other requirements.

      If the CONTROL_FILE_RECORD_KEEP_TIME is less than the retention policy then it may overwrite reusable records prior to obsoleting them in the RMAN metadata. Therfor it is recommended that the CONTROL_FILE_RECORD_KEEP_TIME 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

      <quote>
      RMAN> show retention policy
      2> ;
      
      RMAN configuration parameters are:
      CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
      
      SQL> show parameter control_file_record_keep_time
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      control_file_record_keep_time        integer     7
      SQL> 
      
      SQL> alter system set control_file_record_keep_time=1
        2  /
      
      System altered.
      
      SQL> show parameter control_file_record_keep_time
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      control_file_record_keep_time        integer     1
      
      RMAN> configure retention policy to redundancy  3
      2> ;
      
      new RMAN configuration parameters:
      CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
      new RMAN configuration parameters are successfully stored
      
      RMAN> show retention policy
      2> ;
      
      RMAN configuration parameters are:
      CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
      
      RMAN> backup database
      2> ;
      
      Starting backup at 20-NOV-07
      allocated channel: ORA_DISK_1
      channel ORA_DISK_1: sid=126 devtype=DISK
      channel ORA_DISK_1: starting full datafile backupset
      channel ORA_DISK_1: specifying datafile(s) in backupset
      input datafile fno=00001 name=C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\SYSTEM01.DBF
      input datafile fno=00003 name=C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\SYSAUX01.DBF
      input datafile fno=00002 name=C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\UNDOTBS01.DBF
      input datafile fno=00004 name=C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\USERS01.DBF
      channel ORA_DISK_1: starting piece 1 at 20-NOV-07
      channel ORA_DISK_1: finished piece 1 at 20-NOV-07
      piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORC\ORC\BACKUPSET\2007_11_20\O1_MF_NN
      NDF_TAG20071120T185951_3N5T2RTX_.BKP comment=NONE
      channel ORA_DISK_1: backup set complete, elapsed time: 00:01:17
      Finished backup at 20-NOV-07
      
      Starting Control File and SPFILE Autobackup at 20-NOV-07
      piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORC\ORC\AUTOBACKUP\2007_11_20\O1_MF_S
      _639169270_3N5T598C_.BKP comment=NONE
      Finished Control File and SPFILE Autobackup at 20-NOV-07
      
      RMAN> report obsolete
      2> ;
      
      RMAN retention policy will be applied to the command
      RMAN retention policy is set to redundancy 3
      no obsolete backups found
      
      RMAN> backup database
      2> ;
      
      Starting backup at 20-NOV-07
      using channel ORA_DISK_1
      channel ORA_DISK_1: starting full datafile backupset
      channel ORA_DISK_1: specifying datafile(s) in backupset
      input datafile fno=00001 name=C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\SYSTEM01.DBF
      input datafile fno=00003 name=C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\SYSAUX01.DBF
      input datafile fno=00002 name=C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\UNDOTBS01.DBF
      input datafile fno=00004 name=C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\USERS01.DBF
      channel ORA_DISK_1: starting piece 1 at 20-NOV-07
      channel ORA_DISK_1: finished piece 1 at 20-NOV-07
      piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORC\ORC\BACKUPSET\2007_11_20\O1_MF_NN
      NDF_TAG20071120T190313_3N5T94SG_.BKP comment=NONE
      channel ORA_DISK_1: backup set complete, elapsed time: 00:01:22
      Finished backup at 20-NOV-07
      
      Starting Control File and SPFILE Autobackup at 20-NOV-07
      piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORC\ORC\AUTOBACKUP\2007_11_20\O1_MF_S
      _639169477_3N5TCPVW_.BKP comment=NONE
      Finished Control File and SPFILE Autobackup at 20-NOV-07
      
      RMAN> report obsolete
      2> ;
      
      RMAN retention policy will be applied to the command
      RMAN retention policy is set to redundancy 3
      no obsolete backups found
      
      RMAN> backup database
      2> ;
      
      Starting backup at 20-NOV-07
      using channel ORA_DISK_1
      channel ORA_DISK_1: starting full datafile backupset
      channel ORA_DISK_1: specifying datafile(s) in backupset
      input datafile fno=00001 name=C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\SYSTEM01.DBF
      input datafile fno=00003 name=C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\SYSAUX01.DBF
      input datafile fno=00002 name=C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\UNDOTBS01.DBF
      input datafile fno=00004 name=C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\USERS01.DBF
      channel ORA_DISK_1: starting piece 1 at 20-NOV-07
      channel ORA_DISK_1: finished piece 1 at 20-NOV-07
      piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORC\ORC\BACKUPSET\2007_11_20\O1_MF_NN
      NDF_TAG20071120T190500_3N5TDGC9_.BKP comment=NONE
      channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
      Finished backup at 20-NOV-07
      
      Starting Control File and SPFILE Autobackup at 20-NOV-07
      piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORC\ORC\AUTOBACKUP\2007_11_20\O1_MF_S
      _639169556_3N5TG65X_.BKP comment=NONE
      Finished Control File and SPFILE Autobackup at 20-NOV-07
      
      RMAN> report obsolete
      2> ;
      
      RMAN retention policy will be applied to the command
      RMAN retention policy is set to redundancy 3
      Report of obsolete backups and copies
      Type                 Key    Completion Time    Filename/Handle
      -------------------- ------ ------------------ --------------------
      Archive Log          30     19-NOV-07          C:\ORACLE\FLASH_RECOVERY_AREA\ORC
      \ORC\ARCHIVELOG\2007_11_19\O1_MF_1_1_3N35QQX7_.ARC
      Archive Log          31     19-NOV-07          C:\ORACLE\FLASH_RECOVERY_AREA\ORC
      \ORC\ARCHIVELOG\2007_11_19\O1_MF_1_2_3N39LR86_.ARC
      Archive Log          32     20-NOV-07          C:\ORACLE\FLASH_RECOVERY_AREA\ORC
      \ORC\ARCHIVELOG\2007_11_20\O1_MF_1_3_3N4RZBXV_.ARC
      Archive Log          33     20-NOV-07          C:\ORACLE\FLASH_RECOVERY_AREA\ORC
      \ORC\ARCHIVELOG\2007_11_20\O1_MF_1_4_3N4W8SLF_.ARC
      Archive Log          34     20-NOV-07          C:\ORACLE\FLASH_RECOVERY_AREA\ORC
      \ORC\ARCHIVELOG\2007_11_20\O1_MF_1_5_3N4Y1CN7_.ARC
      Archive Log          35     20-NOV-07          C:\ORACLE\FLASH_RECOVERY_AREA\ORC
      \ORC\ARCHIVELOG\2007_11_20\O1_MF_1_6_3N4Z6LGF_.ARC
      Archive Log          36     20-NOV-07          C:\ORACLE\FLASH_RECOVERY_AREA\ORC
      \ORC\ARCHIVELOG\2007_11_20\O1_MF_1_7_3N53S4XK_.ARC
      Archive Log          37     20-NOV-07          C:\ORACLE\FLASH_RECOVERY_AREA\ORC
      \ORC\ARCHIVELOG\2007_11_20\O1_MF_1_8_3N57XHYB_.ARC
      Archive Log          38     20-NOV-07          C:\ORACLE\FLASH_RECOVERY_AREA\ORC
      \ORC\ARCHIVELOG\2007_11_20\O1_MF_1_9_3N5C52D8_.ARC
      Archive Log          39     20-NOV-07          C:\ORACLE\FLASH_RECOVERY_AREA\ORC
      \ORC\ARCHIVELOG\2007_11_20\O1_MF_1_10_3N5C83YB_.ARC
      Archive Log          40     20-NOV-07          C:\ORACLE\FLASH_RECOVERY_AREA\ORC
      \ORC\ARCHIVELOG\2007_11_20\O1_MF_1_11_3N5K9J2Z_.ARC
      Archive Log          41     20-NOV-07          C:\ORACLE\FLASH_RECOVERY_AREA\ORC
      \ORC\ARCHIVELOG\2007_11_20\O1_MF_1_12_3N5MMXHO_.ARC
      Archive Log          42     20-NOV-07          C:\ORACLE\FLASH_RECOVERY_AREA\ORC
      \ORC\ARCHIVELOG\2007_11_20\O1_MF_1_13_3N5N566W_.ARC
      Archive Log          43     20-NOV-07          C:\ORACLE\FLASH_RECOVERY_AREA\ORC
      \ORC\ARCHIVELOG\2007_11_20\O1_MF_1_14_3N5RVB4G_.ARC
      
      RMAN>
      Now i just delete alls backup and increase the control_file_record_keep_time 7 which will be more then retention policy redundancy 3
      RMAN> list backup
      2> ;
      
      
      List of Backup Sets
      ===================
      
      BS Key  Type LV Size       Device Type Elapsed Time Completion Time
      ------- ---- -- ---------- ----------- ------------ ---------------
      6       Full    512M       DISK        00:01:10     20-NOV-07
              BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20071120T185951
              Piece Name: C:\ORACLE\FLASH_RECOVERY_AREA\ORC\ORC\BACKUPSET\2007_11_20\O
      1_MF_NNNDF_TAG20071120T185951_3N5T2RTX_.BKP
        List of Datafiles in backup set 6
        File LV Type Ckp SCN    Ckp Time  Name
        ---- -- ---- ---------- --------- ----
        1       Full 478504     20-NOV-07 C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\SYSTEM01
      .DBF
        2       Full 478504     20-NOV-07 C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\UNDOTBS0
      1.DBF
        3       Full 478504     20-NOV-07 C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\SYSAUX01
      .DBF
        4       Full 478504     20-NOV-07 C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\USERS01.
      DBF
      
      BS Key  Type LV Size       Device Type Elapsed Time Completion Time
      ------- ---- -- ---------- ----------- ------------ ---------------
      7       Full    2M         DISK        00:00:04     20-NOV-07
              BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20071120T190110
              Piece Name: C:\ORACLE\FLASH_RECOVERY_AREA\ORC\ORC\AUTOBACKUP\2007_11_20\
      O1_MF_S_639169270_3N5T598C_.BKP
        Controlfile Included: Ckp SCN: 478975       Ckp time: 20-NOV-07
        SPFILE Included: Modification time: 20-NOV-07
      
      BS Key  Type LV Size       Device Type Elapsed Time Completion Time
      ------- ---- -- ---------- ----------- ------------ ---------------
      8       Full    513M       DISK        00:01:15     20-NOV-07
              BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20071120T190313
              Piece Name: C:\ORACLE\FLASH_RECOVERY_AREA\ORC\ORC\BACKUPSET\2007_11_20\O
      1_MF_NNNDF_TAG20071120T190313_3N5T94SG_.BKP
        List of Datafiles in backup set 8
        File LV Type Ckp SCN    Ckp Time  Name
        ---- -- ---- ---------- --------- ----
        1       Full 480265     20-NOV-07 C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\SYSTEM01
      .DBF
        2       Full 480265     20-NOV-07 C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\UNDOTBS0
      1.DBF
        3       Full 480265     20-NOV-07 C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\SYSAUX01
      .DBF
        4       Full 480265     20-NOV-07 C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\USERS01.
      DBF
      
      BS Key  Type LV Size       Device Type Elapsed Time Completion Time
      ------- ---- -- ---------- ----------- ------------ ---------------
      9       Full    2M         DISK        00:00:02     20-NOV-07
              BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20071120T190437
              Piece Name: C:\ORACLE\FLASH_RECOVERY_AREA\ORC\ORC\AUTOBACKUP\2007_11_20\
      O1_MF_S_639169477_3N5TCPVW_.BKP
        Controlfile Included: Ckp SCN: 480346       Ckp time: 20-NOV-07
        SPFILE Included: Modification time: 20-NOV-07
      
      BS Key  Type LV Size       Device Type Elapsed Time Completion Time
      ------- ---- -- ---------- ----------- ------------ ---------------
      10      Full    513M       DISK        00:00:52     20-NOV-07
              BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20071120T190500
              Piece Name: C:\ORACLE\FLASH_RECOVERY_AREA\ORC\ORC\BACKUPSET\2007_11_20\O
      1_MF_NNNDF_TAG20071120T190500_3N5TDGC9_.BKP
        List of Datafiles in backup set 10
        File LV Type Ckp SCN    Ckp Time  Name
        ---- -- ---- ---------- --------- ----
        1       Full 480415     20-NOV-07 C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\SYSTEM01
      .DBF
        2       Full 480415     20-NOV-07 C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\UNDOTBS0
      1.DBF
        3       Full 480415     20-NOV-07 C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\SYSAUX01
      .DBF
        4       Full 480415     20-NOV-07 C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\USERS01.
      DBF
      
      BS Key  Type LV Size       Device Type Elapsed Time Completion Time
      ------- ---- -- ---------- ----------- ------------ ---------------
      11      Full    2M         DISK        00:00:02     20-NOV-07
              BP Key: 11   Status: AVAILABLE  Compressed: NO  Tag: TAG20071120T190556
              Piece Name: C:\ORACLE\FLASH_RECOVERY_AREA\ORC\ORC\AUTOBACKUP\2007_11_20\
      O1_MF_S_639169556_3N5TG65X_.BKP
        Controlfile Included: Ckp SCN: 480449       Ckp time: 20-NOV-07
        SPFILE Included: Modification time: 20-NOV-07
      
      RMAN> delete backup
      2> ;
      
      using channel ORA_DISK_1
      
      List of Backup Pieces
      BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
      ------- ------- --- --- ----------- ----------- ----------
      6       6       1   1   AVAILABLE   DISK        C:\ORACLE\FLASH_RECOVERY_AREA\OR
      C\ORC\BACKUPSET\2007_11_20\O1_MF_NNNDF_TAG20071120T185951_3N5T2RTX_.BKP
      7       7       1   1   AVAILABLE   DISK        C:\ORACLE\FLASH_RECOVERY_AREA\OR
      C\ORC\AUTOBACKUP\2007_11_20\O1_MF_S_639169270_3N5T598C_.BKP
      8       8       1   1   AVAILABLE   DISK        C:\ORACLE\FLASH_RECOVERY_AREA\OR
      C\ORC\BACKUPSET\2007_11_20\O1_MF_NNNDF_TAG20071120T190313_3N5T94SG_.BKP
      9       9       1   1   AVAILABLE   DISK        C:\ORACLE\FLASH_RECOVERY_AREA\OR
      C\ORC\AUTOBACKUP\2007_11_20\O1_MF_S_639169477_3N5TCPVW_.BKP
      10      10      1   1   AVAILABLE   DISK        C:\ORACLE\FLASH_RECOVERY_AREA\OR
      C\ORC\BACKUPSET\2007_11_20\O1_MF_NNNDF_TAG20071120T190500_3N5TDGC9_.BKP
      11      11      1   1   AVAILABLE   DISK        C:\ORACLE\FLASH_RECOVERY_AREA\OR
      C\ORC\AUTOBACKUP\2007_11_20\O1_MF_S_639169556_3N5TG65X_.BKP
      
      Do you really want to delete the above objects (enter YES or NO)? yes
      deleted backup piece
      backup piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORC\ORC\BACKUPSET\2007_11_20\O
      1_MF_NNNDF_TAG20071120T185951_3N5T2RTX_.BKP recid=6 stamp=639169192
      deleted backup piece
      backup piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORC\ORC\AUTOBACKUP\2007_11_20\
      O1_MF_S_639169270_3N5T598C_.BKP recid=7 stamp=639169273
      deleted backup piece
      backup piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORC\ORC\BACKUPSET\2007_11_20\O
      1_MF_NNNDF_TAG20071120T190313_3N5T94SG_.BKP recid=8 stamp=639169396
      deleted backup piece
      backup piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORC\ORC\AUTOBACKUP\2007_11_20\
      O1_MF_S_639169477_3N5TCPVW_.BKP recid=9 stamp=639169478
      deleted backup piece
      backup piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORC\ORC\BACKUPSET\2007_11_20\O
      1_MF_NNNDF_TAG20071120T190500_3N5TDGC9_.BKP recid=10 stamp=639169502
      deleted backup piece
      backup piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORC\ORC\AUTOBACKUP\2007_11_20\
      O1_MF_S_639169556_3N5TG65X_.BKP recid=11 stamp=639169558
      Deleted 6 objects
      
      RMAN> delete archivelog all
      2> ;
      
      released channel: ORA_DISK_1
      allocated channel: ORA_DISK_1
      channel ORA_DISK_1: sid=126 devtype=DISK
      specification does not match any archive log in the recovery catalog
      
      RMAN> list backup
      2> ;
      
      
      RMAN> show retention policy
      2> ;
      
      RMAN configuration parameters are:
      CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
      
      SQL> show parameter control_file_record_keep_time
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      control_file_record_keep_time        integer     1
      SQL> alter system set control_file_record_keep_time=7
        2  /
      
      System altered.
      
      SQL> show parameter control_file_record_keep_time
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      control_file_record_keep_time        integer     7
      SQL> 
      
      RMAN> backup database
      2> ;
      
      Starting backup at 20-NOV-07
      using channel ORA_DISK_1
      channel ORA_DISK_1: starting full datafile backupset
      channel ORA_DISK_1: specifying datafile(s) in backupset
      input datafile fno=00001 name=C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\SYSTEM01.DBF
      input datafile fno=00003 name=C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\SYSAUX01.DBF
      input datafile fno=00002 name=C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\UNDOTBS01.DBF
      input datafile fno=00004 name=C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\USERS01.DBF
      channel ORA_DISK_1: starting piece 1 at 20-NOV-07
      channel ORA_DISK_1: finished piece 1 at 20-NOV-07
      piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORC\ORC\BACKUPSET\2007_11_20\O1_MF_NN
      NDF_TAG20071120T191412_3N5TXRSG_.BKP comment=NONE
      channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
      Finished backup at 20-NOV-07
      
      Starting Control File and SPFILE Autobackup at 20-NOV-07
      piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORC\ORC\AUTOBACKUP\2007_11_20\O1_MF_S
      _639170131_3N5V03YT_.BKP comment=NONE
      Finished Control File and SPFILE Autobackup at 20-NOV-07
      
      RMAN> report obsolete
      2> ;
      
      RMAN retention policy will be applied to the command
      RMAN retention policy is set to redundancy 3
      no obsolete backups found
      
      RMAN> backup database
      2> ;
      
      Starting backup at 20-NOV-07
      using channel ORA_DISK_1
      channel ORA_DISK_1: starting full datafile backupset
      channel ORA_DISK_1: specifying datafile(s) in backupset
      input datafile fno=00001 name=C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\SYSTEM01.DBF
      input datafile fno=00003 name=C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\SYSAUX01.DBF
      input datafile fno=00002 name=C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\UNDOTBS01.DBF
      input datafile fno=00004 name=C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\USERS01.DBF
      channel ORA_DISK_1: starting piece 1 at 20-NOV-07
      channel ORA_DISK_1: finished piece 1 at 20-NOV-07
      piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORC\ORC\BACKUPSET\2007_11_20\O1_MF_NN
      NDF_TAG20071120T191655_3N5V2S20_.BKP comment=NONE
      channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
      Finished backup at 20-NOV-07
      
      Starting Control File and SPFILE Autobackup at 20-NOV-07
      piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORC\ORC\AUTOBACKUP\2007_11_20\O1_MF_S
      _639170261_3N5V462Z_.BKP comment=NONE
      Finished Control File and SPFILE Autobackup at 20-NOV-07
      
      RMAN> report obsolete
      2> ;
      
      RMAN retention policy will be applied to the command
      RMAN retention policy is set to redundancy 3
      no obsolete backups found
      
      RMAN> backup database
      2> ;
      
      Starting backup at 20-NOV-07
      using channel ORA_DISK_1
      channel ORA_DISK_1: starting full datafile backupset
      channel ORA_DISK_1: specifying datafile(s) in backupset
      input datafile fno=00001 name=C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\SYSTEM01.DBF
      input datafile fno=00003 name=C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\SYSAUX01.DBF
      input datafile fno=00002 name=C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\UNDOTBS01.DBF
      input datafile fno=00004 name=C:\ORACLE10G\ORACLE\ORADATA\ORC\ORC\USERS01.DBF
      channel ORA_DISK_1: starting piece 1 at 20-NOV-07
      channel ORA_DISK_1: finished piece 1 at 20-NOV-07
      piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORC\ORC\BACKUPSET\2007_11_20\O1_MF_NN
      NDF_TAG20071120T191836_3N5V5X20_.BKP comment=NONE
      channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
      Finished backup at 20-NOV-07
      
      Starting Control File and SPFILE Autobackup at 20-NOV-07
      piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORC\ORC\AUTOBACKUP\2007_11_20\O1_MF_S
      _639170362_3N5V7C1K_.BKP comment=NONE
      Finished Control File and SPFILE Autobackup at 20-NOV-07
      
      RMAN> report obsolete
      2> ;
      
      RMAN retention policy will be applied to the command
      RMAN retention policy is set to redundancy 3
      no obsolete backups found
      nothing obsolete it means control_file_record_keep_time will not affect backups unless it will be less then or equal
      to retention policy.

      Note : Please agree or disagree with me may be i would be wrong

      Khurram
        • 1. Re: control_file_record_keep_time vs retention policy
          558120
          Yes its correct.
          • 2. Re: control_file_record_keep_time vs retention policy
            610062
            Good one with practical example.

            Rgds,
            Soma Sekhar Karri
            • 3. Re: control_file_record_keep_time vs retention policy
              user449447
              I keep CONTROL_FILE_RECORD_KEEP_TIME =30 and recovery window is 30
              is it any harm by keeping these values or can i change it to according the formula.
              i m taking full backups daily and archive one hour interval.and i have been configured retention policy to recovery window is 30 then i have been changed control_file_record_keep_time to 30.i only want to kow is there be any harm by keeping these values same.

              pls reply
              thanks
              • 4. Re: control_file_record_keep_time vs retention policy
                Aman....
                nice one khurram.Good stuff!
                Aman....
                • 5. Re: control_file_record_keep_time vs retention policy
                  orawarebyte
                  Sorry for alls readers after a long practical observation at my own database server i came across that my observation was wrong and absurd in this first post, the above observation is not due to control_file_record_keep_time ,its due to something obvious which i am going to share you guys here.
                  RMAN> show retention policy
                  2> ;
                  
                  RMAN configuration parameters are:
                  CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
                  
                  RMAN> report obsolete
                  2> ;
                  
                  RMAN retention policy will be applied to the command
                  RMAN retention policy is set to redundancy 2
                  no obsolete backups found
                  
                  RMAN> list backup
                  2> ;
                  
                  RMAN>
                  AS documentation says

                  "Besides affecting datafile and control file backups, the retention policy affects archived logs and archived log backups.
                  First, RMAN decides which datafile and control file backups are obsolete. Then, RMAN considers as obsolete all archived
                  log backups that are older than the oldest datafile or control file backup that must be retained.
                  "

                  Now i connect to SQL and create two big table for filling the redo log file and then get it archived.Please
                  consider it here that i am getting archived before any taking backup i.e backup database in order to make
                  archive older then the oldest datafile.
                  SQL> show parameter control_file_record_keep_time
                  
                  NAME                                 TYPE        VALUE
                  ------------------------------------ ----------- ------------------------------
                  control_file_record_keep_time        integer     7
                  
                  SQL> create table a1 as select * from all_objects
                    2  /
                  
                  Table created.
                  
                  SQL> create table a2 as select * from all_objects
                    2  /
                  
                  Table created.
                  
                  C:\>dir C:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_03_17
                   Volume in drive C is khurram
                   Volume Serial Number is F49D-FF2B
                  
                   Directory of C:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_03_17
                  
                  03/17/2008  03:44 PM    <DIR>          .
                  03/17/2008  03:44 PM    <DIR>          ..
                  03/17/2008  03:44 PM         9,750,528 O1_MF_1_15_3XWLVK6T_.ARC
                                 1 File(s)      9,750,528 bytes
                                 2 Dir(s)  62,714,875,904 bytes free
                  
                  Now i take backup
                  
                  RMAN> backup database
                  2> ;
                  
                  Starting backup at 17-MAR-08
                  using channel ORA_DISK_1
                  
                  RMAN> report obsolete
                  2> ;
                  
                  RMAN retention policy will be applied to the command
                  RMAN retention policy is set to redundancy 2
                  no obsolete backups found
                  
                  RMAN> backup database
                  2> ;
                  
                  Starting backup at 17-MAR-08
                  using channel ORA_DISK_1
                  
                  RMAN> report obsolete
                  2> ;
                  
                  RMAN retention policy will be applied to the command
                  RMAN retention policy is set to redundancy 2
                  Report of obsolete backups and copies
                  Type                 Key    Completion Time    Filename/Handle
                  -------------------- ------ ------------------ --------------------
                  Archive Log          402    17-MAR-08          C:\ORACLE\PRODUCT\10.1.0\FLASH_RE
                  COVERY_AREA\ORCL\ARCHIVELOG\2008_03_17\O1_MF_1_15_3XWLVK6T_.ARC
                  You can see the obsolete archived files which is the older then the oldest file which has been obsolete.It also
                  does'nt make sense to keep the archived log files which is older then the oldest file cause it will no longer
                  be useful for recovery process.

                  now back to pavillion
                  RMAN> delete obsolete
                  2> ;
                  
                  RMAN retention policy will be applied to the command
                  RMAN retention policy is set to redundancy 2
                  using channel ORA_DISK_1
                  Deleting the following obsolete backups and copies:
                  Type                 Key    Completion Time    Filename/Handle
                  -------------------- ------ ------------------ --------------------
                  Archive Log          402    17-MAR-08          C:\ORACLE\PRODUCT\10.1.0\FLASH_RE
                  COVERY_AREA\ORCL\ARCHIVELOG\2008_03_17\O1_MF_1_15_3XWLVK6T_.ARC
                  
                  Do you really want to delete the above objects (enter YES or NO)? yes
                  deleted archive log
                  archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
                  G\2008_03_17\O1_MF_1_15_3XWLVK6T_.ARC recid=402 stamp=649611842
                  Deleted 1 objects
                  
                  
                  RMAN> delete backup
                  2> ;
                  
                  using channel ORA_DISK_1
                  
                  RMAN> list backup
                  2> ;
                  
                  
                  C:\>dir C:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_03_17
                   Volume in drive C is khurram
                   Volume Serial Number is F49D-FF2B
                  
                   Directory of C:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_
                  03_17
                  
                  03/17/2008  03:59 PM    <DIR>          .
                  03/17/2008  03:59 PM    <DIR>          ..
                                 0 File(s)              0 bytes
                                 2 Dir(s)  62,724,440,064 bytes free
                  now this time what i did i created the archived log files after first backup in order to not to make it older
                  then the oldest datafile backup.
                  RMAN> backup database
                  2> ;
                  
                  Starting backup at 17-MAR-08
                  using channel ORA_DISK_1
                  
                  RMAN> report obsolete
                  2> ;
                  
                  RMAN retention policy will be applied to the command
                  RMAN retention policy is set to redundancy 2
                  no obsolete backups found
                  now i come to at SQL and make redo log file filled by creating 2 big tables in order to get it archived
                  SQL> create table a3 as select * from all_objects
                    2  /
                  
                  Table created.
                  
                  SQL> create table a4 as select * from all_objects
                    2  /
                  
                  Table created.
                  
                  C:\>dir C:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_03_17
                   Volume in drive C is khurram
                   Volume Serial Number is F49D-FF2B
                  
                   Directory of C:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_
                  03_17
                  
                  03/17/2008  04:09 PM    <DIR>          .
                  03/17/2008  04:09 PM    <DIR>          ..
                  03/17/2008  04:09 PM         9,751,552 O1_MF_1_16_3XWNCGRS_.ARC
                                 1 File(s)      9,751,552 bytes
                                 2 Dir(s)  62,563,205,120 bytes free
                  
                  RMAN> backup database
                  2> ;
                  
                  Starting backup at 17-MAR-08
                  using channel ORA_DISK_1
                  channel ORA_DISK_1: starting compressed full datafile backupset
                  channel ORA_DISK_1: specifying datafile(s) in backupset
                  
                  RMAN> report obsolete
                  2> ;
                  
                  RMAN retention policy will be applied to the command
                  RMAN retention policy is set to redundancy 2
                  no obsolete backups found
                  You can see no archived logs get obsolete yet cause the archivelog O1_MF_1_16_3XWNCGRS_.ARC
                  is not older then oldest datafile backup.

                  But this archivelog file will get obsolete if its beyond the retention policy,lest see how ,just take one more
                  backup ,as i have already taken two time backup and the moment i go to take third backup it will cross ours
                  retetnion policy from the period of 2.
                  RMAN> backup database
                  2> ;
                  
                  RMAN> report obsolete
                  2> ;
                  
                  RMAN retention policy will be applied to the command
                  RMAN retention policy is set to redundancy 2
                  Report of obsolete backups and copies
                  Type                 Key    Completion Time    Filename/Handle
                  -------------------- ------ ------------------ --------------------
                  Backup Set           240    17-MAR-08
                    Backup Piece       231    17-MAR-08          C:\ORACLE\PRODUCT\10.1.0\FLASH_RE
                  COVERY_AREA\ORCL\BACKUPSET\2008_03_17\O1_MF_NNNDF_TAG20080317T160604_3XWN4WTB_.B
                  KP
                  Backup Set           241    17-MAR-08
                    Backup Piece       232    17-MAR-08          C:\ORACLE\PRODUCT\10.1.0\FLASH_RE
                  COVERY_AREA\ORCL\BACKUPSET\2008_03_17\O1_MF_NCSNF_TAG20080317T160604_3XWN6Z95_.B
                  KP
                  Archive Log          403    17-MAR-08          C:\ORACLE\PRODUCT\10.1.0\FLASH_RE
                  COVERY_AREA\ORCL\ARCHIVELOG\2008_03_17\O1_MF_1_16_3XWNCGRS_.ARC
                  Khurram
                  • 6. Re: control_file_record_keep_time vs retention policy
                    Aman....
                    Khurram,
                    Please apologize as its a lazy kind of request.Can you please put the observation into words so that we can understand(here we->me:)) and digest.Its already 6 and mind is flying some where else ;-).
                    Thanks and regards,
                    Aman....
                    • 7. Re: control_file_record_keep_time vs retention policy
                      orawarebyte
                      aman in my first post i wanted to mention the relation between control_file_record_keep_time and retention policy ,oracle document also says that

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

                      In my first post and in first observation i tried to show the archvied logs is obsoleting due to behaviour of control_file_record_keep_time < retention policy which is wrong.

                      Its obsoleting the archive log files due to the retention policy which affects archived logs and archived log backups. First, RMAN decides which datafile and control file backups are obsolete. Then, RMAN considers as obsolete all archived log backups that are older than the oldest datafile or control file backup that must be retained. This behavior occurs regardless of whether the retention policy is configured for a recovery window or redundancy.

                      BTW if you read my first demo and today demo you will see what obvious i missed in my first demo.

                      Have a tea not coffee ;)

                      Khurram
                      • 8. Re: control_file_record_keep_time vs retention policy
                        Aman....
                        Aah Thanks for it. Its clear now and I am going to note it down.
                        Yes need "strong tea" indeed. Will go back to my hotel now.
                        There is one recovery scenerio that has come.I shall discuss that with you over the email.keep watching your email ;-).
                        Aman....
                        • 9. Re: control_file_record_keep_time vs retention policy
                          868750
                          Hi Khurram,

                          I've seen your post and its very helpful. THank you for sharing with us. I've one doubt regarding the same when we use with Recover Window.
                          Please find the code snippet which i'll be using for my backup.

                          DB Version is 10203 running on windows

                          RMAN> show all;
                          2> crosscheck backup;
                          3> crosscheck archivelog all;
                          4> run {
                          5>      allocate channel c1 type disk;
                          6>      sql "alter system archive log current";
                          7>      BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;      
                          8>      backup current controlfile format '%d_CTL_%u';
                          9>      copy current controlfile to 'D:\oracle\product\10.2.0\database\copy_ONL_ctl.dbf';
                          10>      release channel c1;
                          11>      delete noprompt obsolete;
                          12>      delete noprompt expired backup;
                          13> }
                          14> list backup of database;
                          15>
                          RMAN configuration parameters are:
                          CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
                          CONFIGURE BACKUP OPTIMIZATION OFF; # default
                          CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
                          CONFIGURE CONTROLFILE AUTOBACKUP ON;
                          CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
                          CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
                          CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
                          CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
                          CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2 G;
                          CONFIGURE MAXSETSIZE TO UNLIMITED; # default
                          CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
                          CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
                          CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
                          CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\PRODUCT\10.2.0\DATABASE\SNCFBWFFA01.ORA'; # default


                          Here, report obsolete is not reporting backups older than 1 days or even days. Is there anything that I need to look into it.
                          Please help me.
                          • 10. Re: control_file_record_keep_time vs retention policy
                            Nicolas.Gasparotto
                            Please, do not hijack multi years old threads, create your own with all the info, i.e. Oracle version.

                            Nicolas.

                            +-- thread locked --+