17 | 10 | 2017
Latest Articles
Popular Articles

RMAN

Keep RMAN backupsets

User Rating:  / 4
PoorBest 

Keep RMAN backupsets ( with the keep option )

 

If you are using 11G See also RMAN Archival backups with restore points 

RMAN backupsets used to have a retention period or a redundancy since we can' t keep backups forever. The retention period can be configured with the "configure retention policy" syntax. ( sounds logic ). With the "delete obsolete" command we purge old backupsets. The delete obsolete command is shown below but is often one of the latest commands in a backup scripts. The sequence is generally

 

1. backup database

2. backup archivelogs

3. backup current controlfile and spfile

4. delete obsolete backupsets

 

 

C:\Windows\system32>rman target /


Recovery Manager: Release 11.1.0.7.0 - Production on Mon Jun 29 21:13:00 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: PLATINUM (DBID=2418164457)


RMAN> connect catalog rman/rman@palladium

connected to recovery catalog database

RMAN> show all;

starting full resync of recovery catalog
full resync complete
RMAN configuration parameters for database with db_unique_name PLATINUM are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
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 'BZIP2'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE\PRODUCT\11.1.0\DB_1\DATABASE\S
NCFPLATINUM.ORA'; # default

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> delete noprompt obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 4 days
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=69 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=63 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=61 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=78 device type=DISK
no obsolete backups found
 
 
Note there are also command like "report obsolete"
 
It can happen a DBA wants to take a backup and wants to keep that backup. We flag a backup as to be kept with the keep option.

If one wants to flag a backup as to be kept forever then it is a requirement the backupsets are registered in the catalog database. ( KEEP FOREVER )

if one wants to flag a backup as to be kept until a given time then it is NOT a requirement the backupsets are registered in the catalog database. ( KEEP UNTIL TIME )

 

RMAN> run {
2> backup as compressed backupset database format 'C:\temp\DB_%U'
3> keep until time 'SYSDATE+30';
4> }

Starting backup at 29-JUN-09
current log archived

using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
backup will be obsolete on date 29-JUL-09
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_SYSTEM_0LK30K1Q_.DBF
input datafile file number=00011 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_CATALOG_53ZL1X82_.DBF
channel ORA_DISK_1: starting piece 1 at 29-JUN-09
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00002 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_SYSAUX_0MK30K1T_.DBF
input datafile file number=00010 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_PM3_4ZM8M1BQ_.DBF
input datafile file number=00004 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_USERS_0QK30KA1_.DBF
channel ORA_DISK_2: starting piece 1 at 29-JUN-09
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00005 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_USERS_ARCHIVE_01_0OK30K89_.DBF
input datafile file number=00006 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_USERS_ARCHIVE_02_0PK30K9S_.DBF
input datafile file number=00009 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_PM2_4ZM8LTBW_.DBF
channel ORA_DISK_3: starting piece 1 at 29-JUN-09
channel ORA_DISK_4: starting compressed full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00003 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_UNDOTBS1_0NK30K84_.DBF
input datafile file number=00007 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_PM_4OPWTT18_.DBF
input datafile file number=00008 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_PM1_4ZM8LM6N_.DBF
channel ORA_DISK_4: starting piece 1 at 29-JUN-09
channel ORA_DISK_3: finished piece 1 at 29-JUN-09
piece handle=C:\TEMP\DB_5BKIQTL5_1_1 tag=TAG20090629T212905 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:38
channel ORA_DISK_2: finished piece 1 at 29-JUN-09
piece handle=C:\TEMP\DB_5AKIQTL4_1_1 tag=TAG20090629T212905 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:01:12
channel ORA_DISK_4: finished piece 1 at 29-JUN-09
piece handle=C:\TEMP\DB_5CKIQTLA_1_1 tag=TAG20090629T212905 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: finished piece 1 at 29-JUN-09
piece handle=C:\TEMP\DB_59KIQTL4_1_1 tag=TAG20090629T212905 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:07:00

using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
backup will be obsolete on date 29-JUL-09
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 29-JUN-09
channel ORA_DISK_1: finished piece 1 at 29-JUN-09
piece handle=C:\TEMP\DB_5DKIQU28_1_1 tag=TAG20090629T212905 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
backup will be obsolete on date 29-JUL-09
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=114 RECID=124 STAMP=690845770
channel ORA_DISK_1: starting piece 1 at 29-JUN-09
channel ORA_DISK_1: finished piece 1 at 29-JUN-09
piece handle=C:\TEMP\DB_5EKIQU2B_1_1 tag=TAG20090629T212905 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
backup will be obsolete on date 29-JUL-09
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 29-JUN-09
channel ORA_DISK_1: finished piece 1 at 29-JUN-09
piece handle=C:\TEMP\DB_5FKIQU2D_1_1 tag=TAG20090629T212905 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-JUN-09

 

 

Last week I took a cold backup of database and only afterwards I decided to flag this backup as required to be kept. So the backupsets already existed at timestamp t0 and only at timestamp t1 I decided to flag them as to be kept. Note i use the keep forever nologs option because it is a cold backup and as such we don' t need to keep archived redo logs to recover this backup. Note that backupset 12142498 is a controlfile backup. Als always we "keep" in mind that we first backup the database and AFTERWARDS the current controlfile.

 

RMAN> change backupset 12142455,12142454 keep forever nologs;
 
using channel ORA_DISK_1
keep attributes for the backup are changed
backup will never be obsolete
archived logs required to recover from this backup will not be kept
backup set key=12142454 recid=343 stamp=690341503
keep attributes for the backup are changed
backup will never be obsolete
archived logs required to recover from this backup will not be kept
backup set key=12142455 recid=344 stamp=690341521

RMAN> change backupset 12142453,12142452,12142451,12142450 keep forever nologs;

using channel ORA_DISK_1
keep attributes for the backup are changed
backup will never be obsolete
archived logs required to recover from this backup will not be kept
backup set key=12142450 recid=339 stamp=690341459
keep attributes for the backup are changed
backup will never be obsolete
archived logs required to recover from this backup will not be kept
backup set key=12142451 recid=340 stamp=690341462
keep attributes for the backup are changed
backup will never be obsolete
archived logs required to recover from this backup will not be kept
backup set key=12142452 recid=341 stamp=690341464
keep attributes for the backup are changed
backup will never be obsolete
archived logs required to recover from this backup will not be kept
backup set key=12142453 recid=342 stamp=690341493



RMAN> list backup;


BS Key  Type LV Size       Device Type Elapsed Time Completion Time

12142450 Full    43.02M     DISK        00:00:12     24-JUN-09
        BP Key: 12142457   Status: AVAILABLE  Compressed: YES  Tag: TAG20090624T013046
        Piece Name: /u01/rman/PLATINUM/DB_SILVER_arkibhi7_1_1
Keep: NOLOGS             Until: FOREVER
  List of Datafiles in backup set 12142450
  File LV Type Ckp SCN    Ckp Time  Name
  ---- — ---- ---------- --------- ----
  5       Full 8817957902281 24-JUN-09 /u01/oradata/PLATINUM/audit_data01.dbf
  7       Full 8817957902281 24-JUN-09 /u01/oradata/PLATINUM/imp_htrn_i01.dbf
  14      Full 8817957902281 24-JUN-09 /u01/oradata/PLATINUM/irt_htrn_d01.dbf
  18      Full 8817957902281 24-JUN-09 /u01/oradata/PLATINUM/gold_stage_d01.dbf
  21      Full 8817957902281 24-JUN-09 /u01/oradata/PLATINUM/silverpifii01.dbf
  22      Full 8817957902281 24-JUN-09 /u01/oradata/PLATINUM/silver_d01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

12142451 Full    1.08M      DISK        00:00:00     24-JUN-09
        BP Key: 12142458   Status: AVAILABLE  Compressed: YES  Tag: TAG20090624T013046
        Piece Name: /u01/rman/PLATINUM/DB_SILVER_avkibhim_1_1
Keep: NOLOGS             Until: FOREVER
  Control File Included: Ckp SCN: 8817957902281   Ckp time: 24-JUN-09

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

12142452 Full    80.00K     DISK        00:00:01     24-JUN-09
        BP Key: 12142459   Status: AVAILABLE  Compressed: YES  Tag: TAG20090624T013046
        Piece Name: /u01/rman/PLATINUM/DB_SILVER_b0kibhin_1_1
Keep: NOLOGS             Until: FOREVER
  SPFILE Included: Modification time: 24-JUN-09

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

12142453 Full    196.38M    DISK        00:00:46     24-JUN-09
        BP Key: 12142460   Status: AVAILABLE  Compressed: YES  Tag: TAG20090624T013046
        Piece Name: /u01/rman/PLATINUM/DB_SILVER_aukibhi7_1_1
Keep: NOLOGS             Until: FOREVER
  List of Datafiles in backup set 12142453
  File LV Type Ckp SCN    Ckp Time  Name
  ---- — ---- ---------- --------- ----
  1       Full 8817957902281 24-JUN-09 /u01/oradata/PLATINUM/system01.dbf
  3       Full 8817957902281 24-JUN-09 /u01/oradata/PLATINUM/sysaux01.dbf
  4       Full 8817957902281 24-JUN-09 /u01/oradata/PLATINUM/users01.dbf
  9       Full 8817957902281 24-JUN-09 /u01/oradata/PLATINUM/imp_long_i01.dbf
  12      Full 8817957902281 24-JUN-09 /u01/oradata/PLATINUM/imp_ltrn_d01.dbf
  16      Full 8817957902281 24-JUN-09 /u01/oradata/PLATINUM/irt_ltrn_d01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

12142454 Full    274.23M    DISK        00:00:56     24-JUN-09
        BP Key: 12142461   Status: AVAILABLE  Compressed: YES  Tag: TAG20090624T013046
        Piece Name: /u01/rman/PLATINUM/DB_SILVER_atkibhi7_1_1
Keep: NOLOGS             Until: FOREVER
  List of Datafiles in backup set 12142454
  File LV Type Ckp SCN    Ckp Time  Name
  ---- — ---- ---------- --------- ----
  2       Full 8817957902281 24-JUN-09 /u01/oradata/PLATINUM/undotbs01.dbf
  6       Full 8817957902281 24-JUN-09 /u01/oradata/PLATINUM/imp_htrn_d01.dbf
  11      Full 8817957902281 24-JUN-09 /u01/oradata/PLATINUM/imp_lref_i01.dbf
  15      Full 8817957902281 24-JUN-09 /u01/oradata/PLATINUM/irt_htrn_i01.dbf
  19      Full 8817957902281 24-JUN-09 /u01/oradata/PLATINUM/gold_stage_i01.dbf
  23      Full 8817957902281 24-JUN-09 /u01/oradata/PLATINUM/silver_i01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

12142455 Full    283.30M    DISK        00:01:14     24-JUN-09
        BP Key: 12142462   Status: AVAILABLE  Compressed: YES  Tag: TAG20090624T013046
        Piece Name: /u01/rman/PLATINUM/DB_SILVER_askibhi7_1_1
Keep: NOLOGS             Until: FOREVER
  List of Datafiles in backup set 12142455
  File LV Type Ckp SCN    Ckp Time  Name
  ---- — ---- ---------- --------- ----
  8       Full 8817957902281 24-JUN-09 /u01/oradata/PLATINUM/imp_long_d01.dbf
  10      Full 8817957902281 24-JUN-09 /u01/oradata/PLATINUM/imp_lref_d01.dbf
  13      Full 8817957902281 24-JUN-09 /u01/oradata/PLATINUM/imp_ltrn_i01.dbf
  17      Full 8817957902281 24-JUN-09 /u01/oradata/PLATINUM/irt_ltrn_i01.dbf
  20      Full 8817957902281 24-JUN-09 /u01/oradata/PLATINUM/silverpifid01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

12142498 Full    6.77M      DISK        00:00:00     24-JUN-09
        BP Key: 12142504   Status: AVAILABLE  Compressed: NO  Tag: TAG20090624T013216
        Piece Name: /u01/rman/PLATINUM/CT_20090624
Keep: NOLOGS             Until: FOREVER
  Control File Included: Ckp SCN: 8817957902281   Ckp time: 24-JUN-09

 

 

(Added on May 24 2013)

Another option is to manage the backup retention using the TAG reference.

Let' s say that today I got the request to keep the backup of last night for 1 year.

This is how I proceed

 


RMAN> list backup summary;

 
List of Backups
===============
Key      TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
-------  -- --- ----------- --------------- ------- ------- ---------- ------------------
137098063 B F A DISK        18-MAY-13        1       1      YES        TAG20130518T230031
137098064 B F A DISK        18-MAY-13        1       1      YES        TAG20130518T230031
137098482 B A A DISK        18-MAY-13        1       1      YES        TAG20130518T230323
137504774 B F A DISK        20-MAY-13        1       1       NO        TAG20130520T150237
137504778 B F A DISK        21-MAY-13        1       1       NO        TAG20130521T120500
137505481 B F A DISK        22-MAY-13        1       1      YES        TAG20130522T230031
137505482 B F A DISK        22-MAY-13        1       1      YES        TAG20130522T230031
137505617 B F A DISK        22-MAY-13        1       1       NO        TAG20130522T230414
137506104 B A A DISK        22-MAY-13        1       1      YES        TAG20130522T230506
137506105 B A A DISK        22-MAY-13        1       1      YES        TAG20130522T230506
137506295 B F A DISK        22-MAY-13        1       1       NO        TAG20130522T230829
137506317 B F A DISK        22-MAY-13        1       1       NO        TAG20130522T230906
137506329 B F A DISK        22-MAY-13        1       1       NO        TAG20130522T230912
137623478 B F A DISK        23-MAY-13        1       1      YES        TAG20130523T230032
137623479 B F A DISK        23-MAY-13        1       1      YES        TAG20130523T230032
137623521 B F A DISK        23-MAY-13        1       1       NO        TAG20130523T230404
137623635 B A A DISK        23-MAY-13        1       1      YES        TAG20130523T230443
137623636 B A A DISK        23-MAY-13        1       1      YES        TAG20130523T230443
137623703 B F A DISK        23-MAY-13        1       1       NO        TAG20130523T230606
137623739 B F A DISK        23-MAY-13        1       1       NO        TAG20130523T230642
137623787 B F A DISK        23-MAY-13        1       1       NO        TAG20130523T230651

 
RMAN> change backup TAG='TAG20130523T230032' keep until time 'sysdate+365' nologs;
 
using channel ORA_DISK_1
keep attributes for the backup are changed
backup will be obsolete on date 24-MAY-14
archived logs will not be kept or backed up
backup set key=137623478 RECID=28 STAMP=816217242
keep attributes for the backup are changed
backup will be obsolete on date 24-MAY-14
archived logs will not be kept or backed up
backup set key=137623479 RECID=29 STAMP=816217438

 
RMAN> change backupset TAG='TAG20130523T230443' keep until time 'sysdate+365' nologs;
 
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=92 device type=DISK
keep attributes for the backup are changed
backup will be obsolete on date 24-MAY-14
archived logs will not be kept or backed up
backup set key=137623635 RECID=31 STAMP=816217530
keep attributes for the backup are changed
backup will be obsolete on date 24-MAY-14
archived logs will not be kept or backed up
backup set key=137623636 RECID=32 STAMP=816217547
 
RMAN> change backupset TAG='TAG20130523T230606' keep until time 'sysdate+365' nologs;
 
using channel ORA_DISK_1
keep attributes for the backup are changed
backup will be obsolete on date 24-MAY-14
archived logs will not be kept or backed up
backup set key=137623703 RECID=33 STAMP=816217593
 
RMAN> change backupset TAG='TAG20130523T230642' keep until time 'sysdate+365' nologs;
 
using channel ORA_DISK_1
keep attributes for the backup are changed
backup will be obsolete on date 24-MAY-14
archived logs will not be kept or backed up
backup set key=137623739 RECID=34 STAMP=816217606
 
Note that files in the flash recovery area cannot be flagged as to be kept.
In this situation it were backups of control files caused by the configure controlfile autobackup option.

 
RMAN> change backupset TAG='TAG20130523T230404' keep until time 'sysdate+365' nologs;

using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of KEEP command on ORA_DISK_1 channel at 05/24/2013 13:54:47
ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes
 
RMAN> change backupset TAG='TAG20130523T230651' keep until time 'sysdate+365' nologs;

using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of KEEP command on ORA_DISK_1 channel at 05/24/2013 13:32:03
ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes

 

 

See also How to backup with the keep option and restore without a recovery catalog