RMAN Incremental Backup Strategy for Oracle Database

June 1, 2020 7-minute read

RMAN • Oracle • Backup • Google Cloud

Introduction

We are going to create an strategy to keep protected our data in case of any loss or failure in our Oracle Database environment on Google Cloud Platform. To achieve this we will create a weekly Full backup, daily Incremental backups and also intraday Archive backup using RMAN.

Prerequisites

We have a recently configured Oracle 19c multitenant instance patodb with one pluggable database taller running in a Virtual Machine Instance on Google Cloud Platform.
For the previous RMAN configuration steps check the note: RMAN Backup Oracle Database to Local Disk.

RMAN Backup

According to Backup and Recovery User’s Guide the Incremental backups are defined as following:

A level 0 incremental backup, which is the base for subsequent incremental backups, copies all blocks containing data.

A differential incremental backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0.

So our Backup strategy will be to run:

Weekly Full backup incremental level 0
Daily Incremental backups incremental level 1
Intraday Archive backups archivelog all

Incremental 0 Full Backup

Incrementals are Online backups so we can run them while the database is open and being used.
Let’s initiate running the very first backup incremental level 0 also including the archive logs:

[oracle@patoracle ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Jun 1 19:16:07 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PATODB (DBID=1820073908)

RMAN> backup incremental level 0 database plus archivelog;


Starting backup at 01-JUN-20
current log archived
using channel ORA_DISK_1
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=20 RECID=4 STAMP=1042125827
input archived log thread=1 sequence=21 RECID=5 STAMP=1042125835
input archived log thread=1 sequence=22 RECID=6 STAMP=1042125843
input archived log thread=1 sequence=23 RECID=7 STAMP=1042125854
input archived log thread=1 sequence=24 RECID=8 STAMP=1042385692
input archived log thread=1 sequence=25 RECID=9 STAMP=1042400219
input archived log thread=1 sequence=26 RECID=10 STAMP=1042400303
input archived log thread=1 sequence=27 RECID=11 STAMP=1042400534
input archived log thread=1 sequence=28 RECID=12 STAMP=1042400691
input archived log thread=1 sequence=29 RECID=13 STAMP=1042400722
input archived log thread=1 sequence=30 RECID=14 STAMP=1042401019
channel ORA_DISK_1: starting piece 1 at 01-JUN-20
channel ORA_DISK_1: finished piece 1 at 01-JUN-20
piece handle=/rman/respaldo/PATODB_20200601_1gv23hnr.bkp tag=TAG20200601T195019 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 01-JUN-20

Starting backup at 01-JUN-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/cdbs/PATODB/system01.dbf
input datafile file number=00003 name=/oradata/cdbs/PATODB/sysaux01.dbf
input datafile file number=00013 name=/oradata/cdbs/PATODB/undotbs.dbf
input datafile file number=00007 name=/oradata/cdbs/PATODB/users01.dbf
channel ORA_DISK_1: starting piece 1 at 01-JUN-20
channel ORA_DISK_1: finished piece 1 at 01-JUN-20
piece handle=/rman/respaldo/PATODB_20200601_1hv23ho2.bkp tag=TAG20200601T195026 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/oradata/cdbs/PATODB/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/oradata/cdbs/PATODB/pdbseed/system01.dbf
input datafile file number=00008 name=/oradata/cdbs/PATODB/pdbseed/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 01-JUN-20
channel ORA_DISK_1: finished piece 1 at 01-JUN-20
piece handle=/rman/respaldo/PATODB_20200601_1iv23hp5.bkp tag=TAG20200601T195026 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/oradata/pdbs/taller/sysaux01.dbf
input datafile file number=00009 name=/oradata/pdbs/taller/system01.dbf
input datafile file number=00012 name=/oradata/pdbs/taller/datos.dbf
channel ORA_DISK_1: starting piece 1 at 01-JUN-20
channel ORA_DISK_1: finished piece 1 at 01-JUN-20
piece handle=/rman/respaldo/PATODB_20200601_1jv23hpv.bkp tag=TAG20200601T195026 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 01-JUN-20

Starting backup at 01-JUN-20
current log archived
using channel ORA_DISK_1
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=31 RECID=15 STAMP=1042401102
channel ORA_DISK_1: starting piece 1 at 01-JUN-20
channel ORA_DISK_1: finished piece 1 at 01-JUN-20
piece handle=/rman/respaldo/PATODB_20200601_1kv23hqe.bkp tag=TAG20200601T195142 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 01-JUN-20

Starting Control File and SPFILE Autobackup at 01-JUN-20
piece handle=/rman/controlfile/c-1820073908-20200601-0a.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 01-JUN-20

We can see from the output that the following elements were backed up:

All the archive logs generated at the time
All the datasets of the root CDB and seed PDB
All the datasets of our PDB database

Incremental 1 Backup

Now we are going to run a Incremental 1 backup:

RMAN> backup incremental level 1 database plus archivelog;

Starting backup at 01-JUN-20
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=285 device type=DISK
skipping archived logs of thread 1 from sequence 20 to 31; already 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=32 RECID=16 STAMP=1042401235
input archived log thread=1 sequence=33 RECID=17 STAMP=1042401254
input archived log thread=1 sequence=34 RECID=18 STAMP=1042401299
channel ORA_DISK_1: starting piece 1 at 01-JUN-20
channel ORA_DISK_1: finished piece 1 at 01-JUN-20
piece handle=/rman/respaldo/PATODB_20200601_1mv23i0k.bkp tag=TAG20200601T195500 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 01-JUN-20

Starting backup at 01-JUN-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/cdbs/PATODB/system01.dbf
input datafile file number=00003 name=/oradata/cdbs/PATODB/sysaux01.dbf
input datafile file number=00013 name=/oradata/cdbs/PATODB/undotbs.dbf
input datafile file number=00007 name=/oradata/cdbs/PATODB/users01.dbf
channel ORA_DISK_1: starting piece 1 at 01-JUN-20
channel ORA_DISK_1: finished piece 1 at 01-JUN-20
piece handle=/rman/respaldo/PATODB_20200601_1nv23i0l.bkp tag=TAG20200601T195501 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting compressed incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/oradata/cdbs/PATODB/pdbseed/sysaux01.dbf
skipping datafile 00006 because it has not changed
input datafile file number=00005 name=/oradata/cdbs/PATODB/pdbseed/system01.dbf
skipping datafile 00005 because it has not changed
input datafile file number=00008 name=/oradata/cdbs/PATODB/pdbseed/undotbs01.dbf
skipping datafile 00008 because it has not changed
channel ORA_DISK_1: backup cancelled because all files were skipped
channel ORA_DISK_1: starting compressed incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/oradata/pdbs/taller/sysaux01.dbf
input datafile file number=00009 name=/oradata/pdbs/taller/system01.dbf
input datafile file number=00012 name=/oradata/pdbs/taller/datos.dbf
channel ORA_DISK_1: starting piece 1 at 01-JUN-20
channel ORA_DISK_1: finished piece 1 at 01-JUN-20
piece handle=/rman/respaldo/PATODB_20200601_1pv23i15.bkp tag=TAG20200601T195501 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 01-JUN-20

Starting backup at 01-JUN-20
current log archived
using channel ORA_DISK_1
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=35 RECID=19 STAMP=1042401324
channel ORA_DISK_1: starting piece 1 at 01-JUN-20
channel ORA_DISK_1: finished piece 1 at 01-JUN-20
piece handle=/rman/respaldo/PATODB_20200601_1qv23i1c.bkp tag=TAG20200601T195524 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 01-JUN-20

Now we can see only the datasets with changes and only the archivelog files generated after the last incremental 0 were backed up, the rest were skipped.

Archive Backup

Also we want to test the archivelog backup:

RMAN> backup archivelog all;

Starting backup at 01-JUN-20
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=255 device type=DISK
skipping archived logs of thread 1 from sequence 20 to 35; already 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=36 RECID=20 STAMP=1042401443
input archived log thread=1 sequence=37 RECID=21 STAMP=1042401463
input archived log thread=1 sequence=38 RECID=22 STAMP=1042401557
channel ORA_DISK_1: starting piece 1 at 01-JUN-20
channel ORA_DISK_1: finished piece 1 at 01-JUN-20
piece handle=/rman/respaldo/PATODB_20200601_1sv23i8m.bkp tag=TAG20200601T195918 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 01-JUN-20

Starting Control File and SPFILE Autobackup at 01-JUN-20
piece handle=/rman/controlfile/c-1820073908-20200601-0c.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 01-JUN-20

We can see only those archivelog files generated after the last incremental 1 were backed up.

Backup Size

Finally we want to check the size of each backup, to validate differential strategy.
Our incremental 0 backup generated hundreds of MB.

[oracle@patoracle ~]$ cd /rman/respaldo

[oracle@patoracle respaldo]$ du -sh *
42M     PATODB_20200601_1gv23hnr.bkp
288M    PATODB_20200601_1hv23ho2.bkp
120M    PATODB_20200601_1iv23hp5.bkp
102M    PATODB_20200601_1jv23hpv.bkp
56K     PATODB_20200601_1kv23hqe.bkp

Our incremental 1 backup generated hundreds of KB.

324K    PATODB_20200601_1mv23i0k.bkp
600K    PATODB_20200601_1nv23i0l.bkp
200K    PATODB_20200601_1pv23i15.bkp
16K     PATODB_20200601_1qv23i1c.bkp

And our archivelog backup generated few KB.

148K    PATODB_20200601_1sv23i8m.bkp

Our backup strategy for backup of database patodb to our Google Disk was setup and tested!

Conclusion

We have created the RMAN backup strategy for our Oracle Database environment on Google Cloud Platform by setting up Full, Incremental and Archive backups that help us to keep our database protected in case of any data loss or infrastructure failure.

Combining the different types of backups assure us that we can recover our database with minimum rollback and also saving disk resources as only differential data is backed up each time.