As I wrote previously, sometimes we need to have long-term/archival backups due to some compliance. And usually, these backups are stores outside (like a vault/bunker) but for sure not at the same datacenter as the database. But how we can do this at ZDLRA?

In my post about COPY_BACKUP, I wrote how to have an external copy of one backup set at ZDLRA. But this is not the best option when we need to archive some backup because it continues to follow the same recovery window as the original backup set. This means that if you need to have some kind of archive for 5 years, you need to define your recovery window (at the policy level) to this window. And for sure this will put high pressure on space usage because all backups will be stored until became obsolete.

So, the best way is to use the KEEP backups from rman. And as I wrote in my previous post, they not interact/broke with the incremental forever strategy. Is possible to generate the keep backup, and using the DBMS_RA.MOVE_BACKUP moves these backups to a filesystem destination (and further you can copy/store) and archive it outside of ZDLRA.

So, first, let’s create some KEEP backups (I cropped the output t to reduce the size of the post):

RMAN> run{
2> BACKUP INCREMENTAL LEVEL 1 DEVICE TYPE SBT FILESPERSET 1 DATABASE TAG 'LONGTERM' KEEP FOREVER;
3> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
4> BACKUP DEVICE TYPE SBT FILESPERSET 100 FORMAT '%U' ARCHIVELOG ALL NOT BACKED UP TAG 'LONGTERM' KEEP FOREVER;
5> }

Starting backup at 04/10/2020 23:27:27

allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=69 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: RA Library (ZDLRAS1) SID=B0E0085540FB6D61E053010310AC6E1C
backup will never be obsolete
archived logs required to recover from this backup will be backed up
...
...
piece handle=ORCL19C_a7vc5kgd_1_1 tag=LONGTERM comment=API Version 2.0,MMS Version 12.2.0.2
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:01
current log archived

using channel ORA_SBT_TAPE_1
...
...
Finished backup at 04/10/2020 23:32:50

sql statement: ALTER SYSTEM ARCHIVE LOG CURRENT

Starting backup at 04/10/2020 23:32:52
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
skipping archived logs of thread 1 from sequence 43 to 70; already backed up
skipping archived log of thread 1 with sequence 84; already 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=71 RECID=130 STAMP=1052941336
input archived log thread=1 sequence=72 RECID=131 STAMP=1052941338
input archived log thread=1 sequence=73 RECID=132 STAMP=1052942766
input archived log thread=1 sequence=74 RECID=133 STAMP=1052942786
input archived log thread=1 sequence=75 RECID=134 STAMP=1052942787
input archived log thread=1 sequence=76 RECID=135 STAMP=1052942845
input archived log thread=1 sequence=77 RECID=136 STAMP=1052942847
input archived log thread=1 sequence=78 RECID=137 STAMP=1052943564
input archived log thread=1 sequence=79 RECID=138 STAMP=1052943565
input archived log thread=1 sequence=80 RECID=139 STAMP=1052943830
input archived log thread=1 sequence=81 RECID=140 STAMP=1052943831
input archived log thread=1 sequence=82 RECID=141 STAMP=1052946686
input archived log thread=1 sequence=83 RECID=142 STAMP=1052946686
input archived log thread=1 sequence=85 RECID=144 STAMP=1052955171
input archived log thread=1 sequence=86 RECID=145 STAMP=1052955175
channel ORA_SBT_TAPE_1: starting piece 1 at 04/10/2020 23:32:57
channel ORA_SBT_TAPE_1: finished piece 1 at 04/10/2020 23:33:04
piece handle=abvc5kh9_1_1 tag=LONGTERM comment=API Version 2.0,MMS Version 12.2.0.2
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:07
Finished backup at 04/10/2020 23:33:04

RMAN> list backupset 10464;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
10464   Incr 0  783.25M    SBT_TAPE    00:01:41     04/10/2020 23:29:09
        BP Key: 10465   Status: AVAILABLE  Compressed: NO  Tag: LONGTERM
        Handle: ORCL19C_9tvc5k70_1_1   Media: Recovery Appliance (ZDLRAS1)
        Keep: BACKUP_LOGS        Until: FOREVER
  List of Datafiles in backup set 10464
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  1    0  Incr 2637014    04/10/2020 23:27:28              NO    /u01/app/oracle/oradata/ORCL19C/system01.dbf

RMAN>

As you can see above, the backup set 10464 is stored inside of ZDLRA (handle name is ORCL19C_9tvc5k70_1_1) and just one copy exists.

Now we can use DBMS_RA.MOVE_BACKUP to move where we want. The definition follows the same as COPY_BACKUP, and if we define the “template_name” as null, we can send it to the filesystem. Below I moving all the backups with tag equal “LONGTERM”:

SQL> BEGIN
  2      DBMS_RA.MOVE_BACKUP(
  3           tag => 'LONGTERM'
  4          , format => '/radump/longterm_%d_%U'
  5          , template_name => NULL
  6      );
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL>

And after some time, we have at filesystem:

[root@zdlras1n1 ~]# ls -lh /radump/longterm*
-rw-r----- 1 oracle dba 784M Oct  4 23:42 /radump/longterm_ZDLRAS1_9tvc5k70_1_2
-rw-r----- 1 oracle dba 384M Oct  4 23:43 /radump/longterm_ZDLRAS1_9uvc5ka9_1_2
-rw-r----- 1 oracle dba 1.6M Oct  4 23:43 /radump/longterm_ZDLRAS1_9vvc5kc0_1_2
-rw-r----- 1 oracle dba 217M Oct  4 23:42 /radump/longterm_ZDLRAS1_a0vc5kc1_1_2
-rw-r----- 1 oracle dba 224M Oct  4 23:42 /radump/longterm_ZDLRAS1_a1vc5kd4_1_2
-rw-r----- 1 oracle dba 202M Oct  4 23:43 /radump/longterm_ZDLRAS1_a2vc5ke7_1_2
-rw-r----- 1 oracle dba 190M Oct  4 23:42 /radump/longterm_ZDLRAS1_a3vc5kf0_1_2
-rw-r----- 1 oracle dba  69M Oct  4 23:43 /radump/longterm_ZDLRAS1_a4vc5kfq_1_2
-rw-r----- 1 oracle dba 1.7M Oct  4 23:42 /radump/longterm_ZDLRAS1_a5vc5kg9_1_2
-rw-r----- 1 oracle dba 2.4M Oct  4 23:43 /radump/longterm_ZDLRAS1_a6vc5kga_1_2
-rw-r----- 1 oracle dba 1.2M Oct  4 23:43 /radump/longterm_ZDLRAS1_a7vc5kgd_1_2
-rw-r----- 1 oracle dba  13M Oct  4 23:42 /radump/longterm_ZDLRAS1_a8vc5kgg_1_2
-rw-r----- 1 oracle dba 112K Oct  4 23:43 /radump/longterm_ZDLRAS1_a9vc5kgo_1_2
-rw-r----- 1 oracle dba  19M Oct  4 23:43 /radump/longterm_ZDLRAS1_aavc5kgq_1_2
-rw-r----- 1 oracle dba  52M Oct  4 23:42 /radump/longterm_ZDLRAS1_abvc5kh9_1_2
-rw-r----- 1 oracle dba 765M Sep 30 23:51 /radump/longterm_ZDLRAS1_s0vbof97_1_2
[root@zdlras1n1 ~]#

And if we check the backupset 10464 we can see that now we continue to have just one copy of that and it is stored at a different place (look the handle definition):

RMAN> list backupset 10464;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
10464   Incr 0  783.24M    SBT_TAPE    00:15:28     04/10/2020 23:42:56
        BP Key: 10954   Status: AVAILABLE  Compressed: NO  Tag: LONGTERM
        Handle: /radump/longterm_ZDLRAS1_9tvc5k70_1_2   Media:
        Keep: BACKUP_LOGS        Until: FOREVER
  List of Datafiles in backup set 10464
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  1    0  Incr 2637014    04/10/2020 23:27:28              NO    /u01/app/oracle/oradata/ORCL19C/system01.dbf

RMAN>

Another interesting about the move is that, if the backups are stored outside of ZDLRA, the “keep_space” from the ra_database table deducts the size. In this case, I have no more keep backups inside of ZDLRA database:

SQL> select keep_space from ra_database;

KEEP_SPACE
----------
         0

SQL>

So, rman KEEP backups and DBMS_RA.MOVE_BACKUP can be used to move backups to an external place and further archival outside of ZDLRA. Some regulations/compliances require that backups need to be stored physically outside of the same datacenter than the database (and needs to be archived for a long-term period). Maybe move the backups is needed because your sbt_library is not compatible with rman (not allowing the use with ZDLRA), or even that the ZDLRA can’t connect at your library.

Just remember that is impossible to move normal virtual full backups and even transform a virtual backup into one KEEP backup:

SQL> BEGIN
  2      DBMS_RA.MOVE_BACKUP(
  3          tag => 'LONGTERM'
  4          , format => '/radump/longterm_%d_%U'
  5          , template_name => NULL
  6      );
  7  END;
  8  /
BEGIN
*
ERROR at line 1:
ORA-45138: Backup not found.
ORA-06512: at "RASYS.DBMS_RA", line 9852
ORA-06512: at "RASYS.DBMS_RA", line 9671
ORA-06512: at "RASYS.DBMS_RA", line 9602
ORA-06512: at "RASYS.DBMS_RA", line 9831
ORA-06512: at line 2


SQL> 
SQL> BEGIN
  2      DBMS_RA.MOVE_BACKUP_PIECE(
  3          bp_key => 10367
  4          , format => '/radump/longterm_%d_%U'
  5          , template_name => NULL
  6      );
  7  END;
  8  /
BEGIN
*
ERROR at line 1:
ORA-64733: unable to move individual backup piece with BP_KEY 10367; not a KEEP backup
ORA-06512: at "RASYS.DBMS_RA", line 9761
ORA-06512: at "RASYS.DBMS_RA", line 9637
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "RASYS.DBMS_RA", line 9616
ORA-06512: at "RASYS.DBMS_RA", line 9741
ORA-06512: at line 2


SQL>

RMAN> change backupset 10861 keep until time "sysdate + 365";

using channel ORA_SBT_TAPE_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=62 device type=DISK
Not Supported on Recovery Appliance
Cannot do KEEP on virtual backups

RMAN>

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.”