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
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:
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
Redundancy being set to 1(Default),
there would be 1 EXTRA copy of critical files.
Is there any other reason ?
Regards!
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
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.
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
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!
REDUNDANCY define the recovery point. I think it is not good option to set it 2 or 3.
data recovery nj
Post a Comment