07 June, 2012

CONTROLFILE AUTOBACKUPs are OBSOLETE[d]

There was a recent forums discussion about manual controlfile backups being obsolete.  Here I show that even autobackups are obsoleted by Oracle.

First I show that CONTROLFILE AUTOBACKUP is ON an RETENTION is set to REDUNDANCY 1

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
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 MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

RMAN> 
Next, I list all my controlfile backups.  This shows that I do not have manual backups but only autobackups.

RMAN> list backup of controlfile;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
13      Full    9.33M      DISK        00:00:00     01-JAN-12      
        BP Key: 13   Status: AVAILABLE  Compressed: NO  Tag: TAG20120101T115229
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_01_01/o1_mf_n_771421948_7hzp3xf6_.bkp
  Control File Included: Ckp SCN: 4957876      Ckp time: 01-JAN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14      Full    9.33M      DISK        00:00:01     13-FEB-12      
        BP Key: 14   Status: AVAILABLE  Compressed: NO  Tag: TAG20120213T234617
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_02_13/o1_mf_n_775179977_7mld2b7l_.bkp
  Control File Included: Ckp SCN: 5120748      Ckp time: 13-FEB-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
20      Full    9.33M      DISK        00:00:01     23-MAR-12      
        BP Key: 20   Status: AVAILABLE  Compressed: NO  Tag: TAG20120323T231640
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_23/o1_mf_n_778720600_7ps4ys8j_.bkp
  Control File Included: Ckp SCN: 5192490      Ckp time: 23-MAR-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
22      Full    9.33M      DISK        00:00:00     23-MAR-12      
        BP Key: 22   Status: AVAILABLE  Compressed: NO  Tag: TAG20120323T235653
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_23/o1_mf_n_778723013_7ps7b5l1_.bkp
  Control File Included: Ckp SCN: 5194773      Ckp time: 23-MAR-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
23      Full    9.33M      DISK        00:00:00     24-MAR-12      
        BP Key: 23   Status: AVAILABLE  Compressed: NO  Tag: TAG20120324T001027
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_24/o1_mf_n_778723827_7ps83mpw_.bkp
  Control File Included: Ckp SCN: 5196407      Ckp time: 24-MAR-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25      Full    9.33M      DISK        00:00:00     24-MAR-12      
        BP Key: 25   Status: AVAILABLE  Compressed: NO  Tag: TAG20120324T001528
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_24/o1_mf_n_778724128_7ps8f0o8_.bkp
  Control File Included: Ckp SCN: 5196673      Ckp time: 24-MAR-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
33      Full    1.05M      DISK        00:00:00     01-JAN-12      
        BP Key: 33   Status: AVAILABLE  Compressed: YES  Tag: TAG20120101T110008
        Piece Name: /newmountpoint/FRA/2012_01_01/o1_mf_ncnnf_TAG20120101T110008_7hzm5dob_.bkp
  Control File Included: Ckp SCN: 4955357      Ckp time: 01-JAN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
38      Full    9.33M      DISK        00:00:01     15-MAY-12      
        BP Key: 38   Status: AVAILABLE  Compressed: NO  Tag: TAG20120515T002039
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_05_15/o1_mf_n_783303639_7v2d6qkz_.bkp
  Control File Included: Ckp SCN: 5288840      Ckp time: 15-MAY-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
40      Full    9.33M      DISK        00:00:00     20-MAY-12      
        BP Key: 40   Status: AVAILABLE  Compressed: NO  Tag: TAG20120520T230909
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_05_20/o1_mf_n_783817749_7vl28oln_.bkp
  Control File Included: Ckp SCN: 5313644      Ckp time: 20-MAY-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
41      Full    9.33M      DISK        00:00:00     20-MAY-12      
        BP Key: 41   Status: AVAILABLE  Compressed: NO  Tag: TAG20120520T232651
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_05_20/o1_mf_n_783818811_7vl39vgv_.bkp
  Control File Included: Ckp SCN: 5322969      Ckp time: 20-MAY-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
42      Full    9.33M      DISK        00:00:00     03-JUN-12      
        BP Key: 42   Status: AVAILABLE  Compressed: NO  Tag: TAG20120603T224133
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785025693_7wpxwxhm_.bkp
  Control File Included: Ckp SCN: 5353308      Ckp time: 03-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
43      Full    9.33M      DISK        00:00:01     03-JUN-12      
        BP Key: 43   Status: AVAILABLE  Compressed: NO  Tag: TAG20120603T224515
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785025915_7wpy3vt1_.bkp
  Control File Included: Ckp SCN: 5353844      Ckp time: 03-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
44      Full    9.33M      DISK        00:00:00     03-JUN-12      
        BP Key: 44   Status: AVAILABLE  Compressed: NO  Tag: TAG20120603T224540
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785025940_7wpy4n49_.bkp
  Control File Included: Ckp SCN: 5353875      Ckp time: 03-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
45      Full    9.33M      DISK        00:00:01     03-JUN-12      
        BP Key: 45   Status: AVAILABLE  Compressed: NO  Tag: TAG20120603T230026
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785026826_7wpz0bxo_.bkp
  Control File Included: Ckp SCN: 5356135      Ckp time: 03-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
46      Full    9.33M      DISK        00:00:00     03-JUN-12      
        BP Key: 46   Status: AVAILABLE  Compressed: NO  Tag: TAG20120603T230639
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785027199_7wpzczo7_.bkp
  Control File Included: Ckp SCN: 5356475      Ckp time: 03-JUN-12

RMAN> 
So, I have controlfile autobackups going as far back as 01-Jan.  Quite obvious : I haven't been deleting "obsolete" files.  (This is a "play" environment with adequate disk space for multiple backups of a small database).

I now list the OBSOLETE Backups.

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           34     09-MAY-12         
  Backup Piece       34     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1p3b_.bkp
Backup Set           26     09-MAY-12         
  Backup Piece       26     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1q6l_.bkp
Backup Set           36     09-MAY-12         
  Backup Piece       36     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T110008_7hzm1rj9_.bkp
Backup Set           33     09-MAY-12         
  Backup Piece       33     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_ncnnf_TAG20120101T110008_7hzm5dob_.bkp
Backup Set           27     09-MAY-12         
  Backup Piece       27     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T110205_7hzm5g23_.bkp
Backup Set           30     09-MAY-12         
  Backup Piece       30     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjtnr_.bkp
Backup Set           32     09-MAY-12         
  Backup Piece       32     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjvr1_.bkp
Backup Set           29     09-MAY-12         
  Backup Piece       29     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
Backup Set           28     09-MAY-12         
  Backup Piece       28     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznm84k_.bkp
Backup Set           35     09-MAY-12         
  Backup Piece       35     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmq8q_.bkp
Backup Set           31     09-MAY-12         
  Backup Piece       31     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmydw_.bkp
Backup Set           37     09-MAY-12         
  Backup Piece       37     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T112655_7hznmzlq_.bkp
Archive Log          23     15-JAN-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_01_15/o1_mf_1_3_7k5vc0nf_.arc
Backup Set           13     01-JAN-12         
  Backup Piece       13     01-JAN-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_01_01/o1_mf_n_771421948_7hzp3xf6_.bkp
Archive Log          24     15-JAN-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_01_15/o1_mf_1_4_7k5wo9bl_.arc
Archive Log          25     22-JAN-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_01_22/o1_mf_1_5_7koxgt4y_.arc
Archive Log          26     10-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_10/o1_mf_1_6_7mbfpjnf_.arc
Archive Log          27     12-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_12/o1_mf_1_7_7mhmgbrx_.arc
Archive Log          28     13-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_13/o1_mf_1_8_7mlcok0g_.arc
Archive Log          29     13-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_13/o1_mf_1_9_7mlctdf6_.arc
Archive Log          30     13-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_13/o1_mf_1_10_7mld23vp_.arc
Archive Log          32     13-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_13/o1_mf_1_9_7mld241b_.arc
Archive Log          31     13-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_13/o1_mf_1_8_7mld23yr_.arc
Archive Log          33     13-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_13/o1_mf_1_1_7mldpt71_.arc
Backup Set           14     13-FEB-12         
  Backup Piece       14     13-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_02_13/o1_mf_n_775179977_7mld2b7l_.bkp
Archive Log          34     18-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_18/o1_mf_1_2_7pcwy7lm_.arc
Archive Log          35     20-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_20/o1_mf_1_3_7pk6bsr9_.arc
Archive Log          36     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_23/o1_mf_1_4_7ps4pghr_.arc
Archive Log          37     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_23/o1_mf_1_5_7ps4tvvj_.arc
Backup Set           15     23-MAR-12         
  Backup Piece       15     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_annnn_TAG20120323T231436_7ps4tww3_.bkp
Backup Set           16     23-MAR-12         
  Backup Piece       16     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_annnn_TAG20120323T231436_7ps4txz4_.bkp
Backup Set           17     23-MAR-12         
  Backup Piece       17     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_annnn_TAG20120323T231436_7ps4vf2f_.bkp
Backup Set           18     23-MAR-12         
  Backup Piece       18     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_nnndf_TAG20120323T231454_7ps4vgmf_.bkp
Archive Log          38     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_23/o1_mf_1_6_7ps4yqkg_.arc
Backup Set           19     23-MAR-12         
  Backup Piece       19     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_annnn_TAG20120323T231639_7ps4yqqm_.bkp
Backup Set           20     23-MAR-12         
  Backup Piece       20     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_23/o1_mf_n_778720600_7ps4ys8j_.bkp
Backup Set           22     23-MAR-12         
  Backup Piece       22     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_23/o1_mf_n_778723013_7ps7b5l1_.bkp
Datafile Copy        4      24-MAR-12          /home/oracle/app/oracle/oradata/orcl/add_tbs.dbf
Backup Set           23     24-MAR-12         
  Backup Piece       23     24-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_24/o1_mf_n_778723827_7ps83mpw_.bkp
Backup Set           24     24-MAR-12         
  Backup Piece       24     24-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_24/o1_mf_nnndf_TAG20120324T001527_7ps8dzm8_.bkp
Backup Set           25     24-MAR-12         
  Backup Piece       25     24-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_24/o1_mf_n_778724128_7ps8f0o8_.bkp
Backup Set           38     15-MAY-12         
  Backup Piece       38     15-MAY-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_05_15/o1_mf_n_783303639_7v2d6qkz_.bkp
Backup Set           39     20-MAY-12         
  Backup Piece       39     20-MAY-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_05_20/o1_mf_nnndf_TAG20120520T230907_7vl28n6q_.bkp
Backup Set           40     20-MAY-12         
  Backup Piece       40     20-MAY-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_05_20/o1_mf_n_783817749_7vl28oln_.bkp
Backup Set           41     20-MAY-12         
  Backup Piece       41     20-MAY-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_05_20/o1_mf_n_783818811_7vl39vgv_.bkp
Backup Set           42     03-JUN-12         
  Backup Piece       42     03-JUN-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785025693_7wpxwxhm_.bkp
Backup Set           43     03-JUN-12         
  Backup Piece       43     03-JUN-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785025915_7wpy3vt1_.bkp
Backup Set           44     03-JUN-12         
  Backup Piece       44     03-JUN-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785025940_7wpy4n49_.bkp
Backup Set           45     03-JUN-12         
  Backup Piece       45     03-JUN-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785026826_7wpz0bxo_.bkp

RMAN> 
My CONTROLFILE AUTOBACKUP Pieces are shown as OBSOLETE.

Today's question : Why are all controlfile backups but the last one (I had 5 autobackups on 03-Jun and 4 of them are obsolete) reported as OBSOLETE ?
.
.
.

7 comments:

Joel Garry said...

Because the definition of redundancy for oracle is how many copies rman should keep in order to recover. It's kind of a misnomer, because the word in common usage means extra copies. But here it means how many copies to not make obsolete.

I'm not a robot! noerch vertisha

Nitin said...

Redundancy being set to 1(Default),
there would be 1 EXTRA copy of critical files.

Is there any other reason ?

Regards!

Hemant K Chitale said...

Joel,
REDUNDANCY defines the recovery point. If you have a Full Backup one week ago and daily Incremental Backups, a REDUNDANCY of 1 sets your recovery point to the Full Backup.
Nitin,
What do you mean by "1 EXTRA copy" ?

Hemant

Joel Garry said...

Hemant, I'm not getting it. If you have a full backup one week ago and another two weeks ago, and redundancy of 2, what is this about setting recovery points? It's setting the view of whether any backups are obsolete based on how many level 0 backups there are.

I suspect Nitin is making the mistake I called a misnomer, there is nothing extra.

Proving this negative robot stuff is hard.

Hemant K Chitale said...

Joel,
It's just about how you look at it. Redundancy as the Retention Policy defines how many backups to keep. The number of backups kept defines how far back your recovery point is.
Alternatively, You could see Redundancy as stating that backups "older" than the specified count (i.e. the ones that need to be "kept") are Obsolete.
Hemant

Nitin said...

Hemant, Joel
It was colloquial on my part to call it EXTRA copy.
It is like if i set redundancy to 2 and i've 3 full backups then
the 3rd,oldest would be eligible for obsolete and backups 1 & 2 defines i can full recover up to/from this point.

Regards!

Abbei said...

REDUNDANCY define the recovery point. I think it is not good option to set it 2 or 3.
data recovery nj