KEEP backups from rman are used to provide long-term and archival retention. They are used to bypass the default policy retention and are used to sustain some regulations/compliances (like HIPAA, or others) that require archival retention. But with ZDLRA they are treated in a different way. Here I will show how the KEEP backups can impact your backup strategy for ZDLRA.
Below, the database ORCL19C was recently added and there is no backup for this database registered inside of ZDLRA.
[oracle@orcloel7 ~]$ rman target=/ catalog=vpcbronze/vpcbronze@zdlras1-scan:1521/zdlras1 Recovery Manager: Release 19.0.0.0.0 - Production on Sat Oct 24 20:55:46 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL19C (DBID=1487680695) connected to recovery catalog database RMAN> list backup of datafile 1 device type sbt_tape; specification does not match any backup in the repository RMAN>
And inside of ZDLRA, you can see that there are no backups:
SQL> select DB_KEY from ra_database where DB_UNIQUE_NAME = 'ORCL19C';
DB_KEY
----------
14577
SQL> select DBINC_KEY from rc_database where db_key = 14577;
DBINC_KEY
----------
14578
SQL> select count(*) from vbdf where DB_KEY = 14577;
COUNT(*)
----------
0
SQL> select count(*) from plans where DB_KEY = 14577;
COUNT(*)
----------
0
SQL> select DB_KEY, SPACE_USAGE, KEEP_SPACE from ra_database where DB_UNIQUE_NAME = 'ORCL19C';
DB_KEY SPACE_USAGE KEEP_SPACE
---------- ----------- ----------
14577 0 0
SQL>
And as you can see above, at table RA_DATABASE there is column KEEP_SPACE to report the space used by keep backups.
So, we can do the first keep backup (I made just for the datafile 1):
RMAN> BACKUP INCREMENTAL LEVEL 0 DEVICE TYPE SBT FILESPERSET 1 DATAFILE 1 TAG 'KEEPDF1' KEEP FOREVER;
Starting backup at 24/10/2020 22:38:54
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=71 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: RA Library (ZDLRAS1) SID=B271AF93A734380EE053010310AC1736
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_SBT_TAPE_1: starting incremental level 0 datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL19C/system01.dbf
channel ORA_SBT_TAPE_1: starting piece 1 at 24/10/2020 22:38:57
channel ORA_SBT_TAPE_1: finished piece 1 at 24/10/2020 22:40:52
piece handle=ORCL19C_c3vdq8s1_1_1 tag=KEEPDF1 comment=API Version 2.0,MMS Version 12.2.0.2
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:01:55
current log archived
using channel ORA_SBT_TAPE_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_SBT_TAPE_1: starting archived log backup set
channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=96 RECID=155 STAMP=1054680052
channel ORA_SBT_TAPE_1: starting piece 1 at 24/10/2020 22:40:54
channel ORA_SBT_TAPE_1: finished piece 1 at 24/10/2020 22:41:01
piece handle=ORCL19C_c4vdq8vm_1_1 tag=KEEPDF1 comment=API Version 2.0,MMS Version 12.2.0.2
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:07
using channel ORA_SBT_TAPE_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_SBT_TAPE_1: starting piece 1 at 24/10/2020 22:41:02
channel ORA_SBT_TAPE_1: finished piece 1 at 24/10/2020 22:41:03
piece handle=ORCL19C_c5vdq8vu_1_1 tag=KEEPDF1 comment=API Version 2.0,MMS Version 12.2.0.2
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:01
using channel ORA_SBT_TAPE_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_SBT_TAPE_1: starting piece 1 at 24/10/2020 22:41:05
channel ORA_SBT_TAPE_1: finished piece 1 at 24/10/2020 22:41:08
piece handle=ORCL19C_c6vdq900_1_1 tag=KEEPDF1 comment=API Version 2.0,MMS Version 12.2.0.2
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:03
Finished backup at 24/10/2020 22:41:08
RMAN> list backup of datafile 1 device type sbt_tape;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
15685 Incr 0 783.25M SBT_TAPE 00:01:44 24/10/2020 22:40:41
BP Key: 15686 Status: AVAILABLE Compressed: NO Tag: KEEPDF1
Handle: ORCL19C_c3vdq8s1_1_1 Media: Recovery Appliance (ZDLRAS1)
Keep: BACKUP_LOGS Until: FOREVER
List of Datafiles in backup set 15685
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 0 Incr 2676322 24/10/2020 22:38:57 NO /u01/app/oracle/oradata/ORCL19C/system01.dbf
RMAN>
Above, you can see that “Handle” does not report “VB$_” as normal virtual backups managed by ZDLRA. And if we look at internals tables of ZDLRA the backup was not used to generate ant virtual full backup:
SQL> select DB_KEY, SPACE_USAGE, KEEP_SPACE from ra_database where DB_UNIQUE_NAME = 'ORCL19C';
DB_KEY SPACE_USAGE KEEP_SPACE
---------- ----------- ----------
14577 .79296875 .79296875
SQL>
SQL> select count(*) from plans where DB_KEY = 14577;
COUNT(*)
----------
0
SQL> select count(*) from vbdf where DB_KEY = 14577;
COUNT(*)
----------
0
SQL>
SQL> select DBINC_KEY from rc_database where db_key = 14577;
DBINC_KEY
----------
14578
SQL>
SQL> select DF_KEY from df where DBINC_KEY = 14578 and file# = 1;
DF_KEY
----------
14590
SQL>
SQL> select count(*) from blocks where DF_KEY = 14590;
COUNT(*)
----------
0
SQL>
To be clear, this is was expected since the KEEP backups, even in traditional, rman scenarios (with the third-party backup appliance) are not considered as part of the backup workflow. This means that if I do one incremental backup, the keep is not considered a valid parent backup.
The same is valid at ZDLRA, as you can see below I made a subsequent level 1 backup for the datafile 1 and no parent backups were found:
RMAN> BACKUP INCREMENTAL LEVEL 1 DEVICE TYPE SBT FILESPERSET 1 DATAFILE 1 TAG 'DF1'; Starting backup at 24/10/2020 22:54:28 allocated channel: ORA_SBT_TAPE_1 channel ORA_SBT_TAPE_1: SID=34 device type=SBT_TAPE channel ORA_SBT_TAPE_1: RA Library (ZDLRAS1) SID=B271E73EF4373B90E053010310AC6873 no parent backup or copy of datafile 1 found channel ORA_SBT_TAPE_1: starting incremental level 0 datafile backup set channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL19C/system01.dbf channel ORA_SBT_TAPE_1: starting piece 1 at 24/10/2020 22:54:30 channel ORA_SBT_TAPE_1: finished piece 1 at 24/10/2020 22:56:35 piece handle=ORCL19C_c7vdq9p6_1_1 tag=DF1 comment=API Version 2.0,MMS Version 12.2.0.2 channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:02:05 Finished backup at 24/10/2020 22:56:35 Starting Control File and SPFILE Autobackup at 24/10/2020 22:56:35 piece handle=c-1487680695-20201024-06 comment=API Version 2.0,MMS Version 12.2.0.2 Finished Control File and SPFILE Autobackup at 24/10/2020 22:56:44 RMAN>
And internally at ZDLRA, you can see that this backup without a keep, was used to generate virtual backups:
SQL> select count(*) from vbdf where DB_KEY = 14577;
COUNT(*)
----------
1
SQL> select count(*) from blocks where DF_KEY = 14590;
COUNT(*)
----------
100251
SQL> select DB_KEY, SPACE_USAGE, KEEP_SPACE from ra_database where DB_UNIQUE_NAME = 'ORCL19C';
DB_KEY SPACE_USAGE KEEP_SPACE
---------- ----------- ----------
14577 1.27265625 .79296875
SQL>
So, at this moment we have keep and virtual backup:
RMAN> list backup of datafile 1 device type sbt_tape;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
15685 Incr 0 783.25M SBT_TAPE 00:01:44 24/10/2020 22:40:41
BP Key: 15686 Status: AVAILABLE Compressed: NO Tag: KEEPDF1
Handle: ORCL19C_c3vdq8s1_1_1 Media: Recovery Appliance (ZDLRAS1)
Keep: BACKUP_LOGS Until: FOREVER
List of Datafiles in backup set 15685
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 0 Incr 2676322 24/10/2020 22:38:57 NO /u01/app/oracle/oradata/ORCL19C/system01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
15776 Incr 0 325.05M SBT_TAPE 00:01:59 24/10/2020 22:56:29
BP Key: 15777 Status: AVAILABLE Compressed: YES Tag: DF1
Handle: VB$_1918343643_15775I Media:
List of Datafiles in backup set 15776
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 0 Incr 2678532 24/10/2020 22:54:30 NO /u01/app/oracle/oradata/ORCL19C/system01.dbf
RMAN>
But during the normal database life (after several backups are done) is possible/needed to do some keep backup. Fortunately, since the keep backup does not affect the incremental forever strategy, we can have it without collateral effect. Even doing a level 0 backup, the virtual full backups continue to work:
RMAN> BACKUP INCREMENTAL LEVEL 0 DEVICE TYPE SBT FILESPERSET 1 DATAFILE 1 TAG 'KEEPDF1' KEEP FOREVER;
Starting backup at 24/10/2020 23:01:29
using channel ORA_SBT_TAPE_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_SBT_TAPE_1: starting incremental level 0 datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL19C/system01.dbf
channel ORA_SBT_TAPE_1: starting piece 1 at 24/10/2020 23:01:29
channel ORA_SBT_TAPE_1: finished piece 1 at 24/10/2020 23:03:24
piece handle=ORCL19C_c9vdqa69_1_1 tag=KEEPDF1 comment=API Version 2.0,MMS Version 12.2.0.2
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:01:55
current log archived
using channel ORA_SBT_TAPE_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_SBT_TAPE_1: starting archived log backup set
channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=97 RECID=156 STAMP=1054681404
channel ORA_SBT_TAPE_1: starting piece 1 at 24/10/2020 23:03:27
channel ORA_SBT_TAPE_1: finished piece 1 at 24/10/2020 23:03:34
piece handle=ORCL19C_cavdqa9v_1_1 tag=KEEPDF1 comment=API Version 2.0,MMS Version 12.2.0.2
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:07
using channel ORA_SBT_TAPE_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_SBT_TAPE_1: starting piece 1 at 24/10/2020 23:03:35
channel ORA_SBT_TAPE_1: finished piece 1 at 24/10/2020 23:03:42
piece handle=ORCL19C_cbvdqaa7_1_1 tag=KEEPDF1 comment=API Version 2.0,MMS Version 12.2.0.2
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:07
using channel ORA_SBT_TAPE_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_SBT_TAPE_1: starting piece 1 at 24/10/2020 23:03:44
channel ORA_SBT_TAPE_1: finished piece 1 at 24/10/2020 23:03:47
piece handle=ORCL19C_ccvdqaaf_1_1 tag=KEEPDF1 comment=API Version 2.0,MMS Version 12.2.0.2
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:03
Finished backup at 24/10/2020 23:03:47
RMAN> BACKUP INCREMENTAL LEVEL 0 DEVICE TYPE SBT FILESPERSET 1 DATAFILE 1 TAG 'DF1';
Starting backup at 24/10/2020 23:05:16
using channel ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: starting incremental level 1 datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL19C/system01.dbf
channel ORA_SBT_TAPE_1: starting piece 1 at 24/10/2020 23:06:44
channel ORA_SBT_TAPE_1: finished piece 1 at 24/10/2020 23:06:45
piece handle=ORCL19C_cevdqag4_1_1 tag=DF1 comment=API Version 2.0,MMS Version 12.2.0.2
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24/10/2020 23:06:45
Starting Control File and SPFILE Autobackup at 24/10/2020 23:06:45
piece handle=c-1487680695-20201024-07 comment=API Version 2.0,MMS Version 12.2.0.2
Finished Control File and SPFILE Autobackup at 24/10/2020 23:06:49
RMAN> list backup of datafile 1 device type sbt_tape;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
15685 Incr 0 783.25M SBT_TAPE 00:01:44 24/10/2020 22:40:41
BP Key: 15686 Status: AVAILABLE Compressed: NO Tag: KEEPDF1
Handle: ORCL19C_c3vdq8s1_1_1 Media: Recovery Appliance (ZDLRAS1)
Keep: BACKUP_LOGS Until: FOREVER
List of Datafiles in backup set 15685
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 0 Incr 2676322 24/10/2020 22:38:57 NO /u01/app/oracle/oradata/ORCL19C/system01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
15776 Incr 0 325.05M SBT_TAPE 00:01:59 24/10/2020 22:56:29
BP Key: 15777 Status: AVAILABLE Compressed: YES Tag: DF1
Handle: VB$_1918343643_15775I Media:
List of Datafiles in backup set 15776
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 0 Incr 2678532 24/10/2020 22:54:30 NO /u01/app/oracle/oradata/ORCL19C/system01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
15781 Incr 0 783.25M SBT_TAPE 00:01:45 24/10/2020 23:03:14
BP Key: 15782 Status: AVAILABLE Compressed: NO Tag: KEEPDF1
Handle: ORCL19C_c9vdqa69_1_1 Media: Recovery Appliance (ZDLRAS1)
Keep: BACKUP_LOGS Until: FOREVER
List of Datafiles in backup set 15781
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 0 Incr 2679717 24/10/2020 23:01:29 NO /u01/app/oracle/oradata/ORCL19C/system01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
15895 Incr 1 48.00K SBT_TAPE 00:00:04 24/10/2020 23:06:48
BP Key: 15896 Status: AVAILABLE Compressed: YES Tag: DF1
Handle: VB$_1918343643_15894I Media:
List of Datafiles in backup set 15895
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 1 Incr 2680540 24/10/2020 23:06:44 NO /u01/app/oracle/oradata/ORCL19C/system01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
15899 Incr 0 323.93M SBT_TAPE 00:00:04 24/10/2020 23:06:48
BP Key: 15900 Status: AVAILABLE Compressed: YES Tag: DF1
Handle: VB$_1918343643_15894_1 Media:
List of Datafiles in backup set 15899
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 0 Incr 2680540 24/10/2020 23:06:44 NO /u01/app/oracle/oradata/ORCL19C/system01.dbf
RMAN>
Above you can notice that I made one keep backup level 0 for datafile 1 and after that, I made a new level 1 backup. But the virtual full backup was generated correctly meaning that the “keep level 0” not affect the normal ZDLRA behavior.
So, the KEEP backup not affect the incremental forever strategy even when taken when you already stored backups at ZDLRA. They are stored at the ZDLRA delta store, consume space, but are treated as a separate thread.
Disclaimer: “The postings on this site are my own and don’t necessarily represent my actual employer positions, strategies or opinions. The information here was edited to be useful for general purpose, specific data and identifications were removed to allow reach the generic audience and to be useful for the community. Post protected by copyright.”
Great post on keep backups!
I found a few ways to leverage the fact that keep backups do not virtualize or affect the incremental forever strategy.
1) if you need to create a one-off backup to create a clone from. For example, I want to create a disk backup and sFTP it to another server, keep is the way to go. Creating a keep backup to disk won’t affect you ZDLRA backups.
2) You can compress the keep backup if using TDE. If you compress a backup that contains TDE data it will not virtualize. But for an archival backup, it is a big plus. It will take up less room on the ZDLRA , and get written to tape or cloud compressed.
Pingback: MOVE_BACKUP and ZDLRA | Fernando Simon
Nice post on ZDLRA keep backups!
I have few queries on ZDLRA and it would be great if you can help me with below queries.
1. Suppose we have to take 10 database to taps as a part of long term retention. Monthly Tape backup status report from RA catalog?
I ran below query but it did not give correct result.
select distinct db_name,tag,backup_type,incremental_level,device_type,start_time,completion_time,status from RC_BACKUP_PIECE_DETAILS where backup_type=’D’ and incremental_level is null and tag not like ‘TAG2021%’ and to_char(start_time,’MON-YY’) =’JUN-21′ order by start_time;
2. Is there any possibility to take backup directly on tape (as a part of monthly archival for long term retention) to avoid unnecessary space gets consumed on ZDLRA?
3. How to get tape details and tag information at the time of restoration of old tape?
4. For long term retention if backup failed and it consumes space on ZDLRA then how to cleanup failed keep backup from ZDRLA ?
5. How to Restore database from tape backup (taken for long term retention) when DB deleted from ZDLRA and added backp on ZDLRA as information will be removed from RA catalog?
6. How to restore ZDLRA database from tape at DR site where no ZDLRA configured?
Hi,
Sorry about the delay to reply your comment.
So, let’s go:
1 – For the rman catalog they are maintained automatically. If the backupset appears at the rman catalog, this means that it is available for restore/recovery. If got expired, they got removed. One thing to check is the column BA_ACCESS from the RC_BACKUP_PIECE table. When the backups move/cloned from one place to another (ZDLRA to TAPES), the information of this column change too.
2 – As I know, NO.
3 – Depends on how you send backups to tape. If it is using the native way (OSB) or third-party. When using OSB you can check this at the console command. One example you can see in my previous post: https://www.fernandosimon.com/blog/zdlra-osb-and-clone-to-tape/
For EM/CC you can use the OSB paged to check this information.
4 – Sorry, not understood the question. If backups (from database to zdlra) fail, the validation (inside ZDLRA) reports an error, and backup is validated and deleted. If failures occur when copying to tape, this leads to a tape with used space that can’t be deleted (Yes, it a flaw and I already asked for a bug/enhancement request a long time ago), they are working. One way is to format the tape manually (but if you have others backups there you will lose it. Sometimes the failed backup to tape is marked as “corrupted” and space can be used again.
The point here is that (from ZDLRA) the OSB is a black box and has not so much interface (just send bytes). So, ZDLRA can’t ask OSB to rewrite just the failed block again to tape. The OSB is very limited for tape and when a failure occurs is hard to know if the error occurred at the tape of software (OSB/ZDLRA) side. So, usually, OSB marks that part of the tape as unusable because can’t trust again over it.
5 – Really don’t know. never needed to reimport catalogs of deleted databases from ZDLRA.
6 – ZDLRA copy naturally using OSB and third-party software. More or less, what is stored in the same than a rman backupset. So, you can use the access direct to tape (directly from rman) and restore it from there if you don’t have the ZDLRA at the DR site. Maybe you need to check some details (like metadata), but you will need to catalog the tape (using the catalog rman command).
Hope that helps.
Fernando Simon