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.
PrerequisitesWe 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 BackupAccording 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 0Daily Incremental backups incremental level 1Intraday Archive backups archivelog allIncremental 0 Full BackupIncrementals 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 2020Version 19.3.0.0.0Copyright (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-20current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting compressed archived log backup setchannel ORA_DISK_1: specifying archived log(s) in backup setinput archived log thread=1 sequence=20 RECID=4 STAMP=1042125827input archived log thread=1 sequence=21 RECID=5 STAMP=1042125835input archived log thread=1 sequence=22 RECID=6 STAMP=1042125843input archived log thread=1 sequence=23 RECID=7 STAMP=1042125854input archived log thread=1 sequence=24 RECID=8 STAMP=1042385692input archived log thread=1 sequence=25 RECID=9 STAMP=1042400219input archived log thread=1 sequence=26 RECID=10 STAMP=1042400303input archived log thread=1 sequence=27 RECID=11 STAMP=1042400534input archived log thread=1 sequence=28 RECID=12 STAMP=1042400691input archived log thread=1 sequence=29 RECID=13 STAMP=1042400722input archived log thread=1 sequence=30 RECID=14 STAMP=1042401019channel ORA_DISK_1: starting piece 1 at 01-JUN-20channel ORA_DISK_1: finished piece 1 at 01-JUN-20piece handle=/rman/respaldo/PATODB_20200601_1gv23hnr.bkp tag=TAG20200601T195019 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:07Finished backup at 01-JUN-20Starting backup at 01-JUN-20using channel ORA_DISK_1channel ORA_DISK_1: starting compressed incremental level 0 datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00001 name=/oradata/cdbs/PATODB/system01.dbfinput datafile file number=00003 name=/oradata/cdbs/PATODB/sysaux01.dbfinput datafile file number=00013 name=/oradata/cdbs/PATODB/undotbs.dbfinput datafile file number=00007 name=/oradata/cdbs/PATODB/users01.dbfchannel ORA_DISK_1: starting piece 1 at 01-JUN-20channel ORA_DISK_1: finished piece 1 at 01-JUN-20piece handle=/rman/respaldo/PATODB_20200601_1hv23ho2.bkp tag=TAG20200601T195026 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:35channel ORA_DISK_1: starting compressed incremental level 0 datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00006 name=/oradata/cdbs/PATODB/pdbseed/sysaux01.dbfinput datafile file number=00005 name=/oradata/cdbs/PATODB/pdbseed/system01.dbfinput datafile file number=00008 name=/oradata/cdbs/PATODB/pdbseed/undotbs01.dbfchannel ORA_DISK_1: starting piece 1 at 01-JUN-20channel ORA_DISK_1: finished piece 1 at 01-JUN-20piece handle=/rman/respaldo/PATODB_20200601_1iv23hp5.bkp tag=TAG20200601T195026 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:25channel ORA_DISK_1: starting compressed incremental level 0 datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00010 name=/oradata/pdbs/taller/sysaux01.dbfinput datafile file number=00009 name=/oradata/pdbs/taller/system01.dbfinput datafile file number=00012 name=/oradata/pdbs/taller/datos.dbfchannel ORA_DISK_1: starting piece 1 at 01-JUN-20channel ORA_DISK_1: finished piece 1 at 01-JUN-20piece handle=/rman/respaldo/PATODB_20200601_1jv23hpv.bkp tag=TAG20200601T195026 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:15Finished backup at 01-JUN-20Starting backup at 01-JUN-20current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting compressed archived log backup setchannel ORA_DISK_1: specifying archived log(s) in backup setinput archived log thread=1 sequence=31 RECID=15 STAMP=1042401102channel ORA_DISK_1: starting piece 1 at 01-JUN-20channel ORA_DISK_1: finished piece 1 at 01-JUN-20piece handle=/rman/respaldo/PATODB_20200601_1kv23hqe.bkp tag=TAG20200601T195142 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 01-JUN-20Starting Control File and SPFILE Autobackup at 01-JUN-20piece handle=/rman/controlfile/c-1820073908-20200601-0a.bkp comment=NONEFinished Control File and SPFILE Autobackup at 01-JUN-20We can see from the output that the following elements were backed up:
All the archive logs generated at the timeAll the datasets of the root CDB and seed PDBAll the datasets of our PDB databaseIncremental 1 BackupNow we are going to run a Incremental 1 backup:
RMAN> backup incremental level 1 database plus archivelog;Starting backup at 01-JUN-20current log archivedusing target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=285 device type=DISKskipping archived logs of thread 1 from sequence 20 to 31; already backed upchannel ORA_DISK_1: starting compressed archived log backup setchannel ORA_DISK_1: specifying archived log(s) in backup setinput archived log thread=1 sequence=32 RECID=16 STAMP=1042401235input archived log thread=1 sequence=33 RECID=17 STAMP=1042401254input archived log thread=1 sequence=34 RECID=18 STAMP=1042401299channel ORA_DISK_1: starting piece 1 at 01-JUN-20channel ORA_DISK_1: finished piece 1 at 01-JUN-20piece handle=/rman/respaldo/PATODB_20200601_1mv23i0k.bkp tag=TAG20200601T195500 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 01-JUN-20Starting backup at 01-JUN-20using channel ORA_DISK_1channel ORA_DISK_1: starting compressed incremental level 1 datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00001 name=/oradata/cdbs/PATODB/system01.dbfinput datafile file number=00003 name=/oradata/cdbs/PATODB/sysaux01.dbfinput datafile file number=00013 name=/oradata/cdbs/PATODB/undotbs.dbfinput datafile file number=00007 name=/oradata/cdbs/PATODB/users01.dbfchannel ORA_DISK_1: starting piece 1 at 01-JUN-20channel ORA_DISK_1: finished piece 1 at 01-JUN-20piece handle=/rman/respaldo/PATODB_20200601_1nv23i0l.bkp tag=TAG20200601T195501 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:15channel ORA_DISK_1: starting compressed incremental level 1 datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00006 name=/oradata/cdbs/PATODB/pdbseed/sysaux01.dbfskipping datafile 00006 because it has not changedinput datafile file number=00005 name=/oradata/cdbs/PATODB/pdbseed/system01.dbfskipping datafile 00005 because it has not changedinput datafile file number=00008 name=/oradata/cdbs/PATODB/pdbseed/undotbs01.dbfskipping datafile 00008 because it has not changedchannel ORA_DISK_1: backup cancelled because all files were skippedchannel ORA_DISK_1: starting compressed incremental level 1 datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00010 name=/oradata/pdbs/taller/sysaux01.dbfinput datafile file number=00009 name=/oradata/pdbs/taller/system01.dbfinput datafile file number=00012 name=/oradata/pdbs/taller/datos.dbfchannel ORA_DISK_1: starting piece 1 at 01-JUN-20channel ORA_DISK_1: finished piece 1 at 01-JUN-20piece handle=/rman/respaldo/PATODB_20200601_1pv23i15.bkp tag=TAG20200601T195501 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:07Finished backup at 01-JUN-20Starting backup at 01-JUN-20current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting compressed archived log backup setchannel ORA_DISK_1: specifying archived log(s) in backup setinput archived log thread=1 sequence=35 RECID=19 STAMP=1042401324channel ORA_DISK_1: starting piece 1 at 01-JUN-20channel ORA_DISK_1: finished piece 1 at 01-JUN-20piece handle=/rman/respaldo/PATODB_20200601_1qv23i1c.bkp tag=TAG20200601T195524 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 01-JUN-20Now 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 BackupAlso we want to test the archivelog backup:
RMAN> backup archivelog all;Starting backup at 01-JUN-20current log archivedusing target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=255 device type=DISKskipping archived logs of thread 1 from sequence 20 to 35; already backed upchannel ORA_DISK_1: starting compressed archived log backup setchannel ORA_DISK_1: specifying archived log(s) in backup setinput archived log thread=1 sequence=36 RECID=20 STAMP=1042401443input archived log thread=1 sequence=37 RECID=21 STAMP=1042401463input archived log thread=1 sequence=38 RECID=22 STAMP=1042401557channel ORA_DISK_1: starting piece 1 at 01-JUN-20channel ORA_DISK_1: finished piece 1 at 01-JUN-20piece handle=/rman/respaldo/PATODB_20200601_1sv23i8m.bkp tag=TAG20200601T195918 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 01-JUN-20Starting Control File and SPFILE Autobackup at 01-JUN-20piece handle=/rman/controlfile/c-1820073908-20200601-0c.bkp comment=NONEFinished Control File and SPFILE Autobackup at 01-JUN-20We can see only those archivelog files generated after the last incremental 1 were backed up.
Backup SizeFinally 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.bkp288MPATODB_20200601_1hv23ho2.bkp120MPATODB_20200601_1iv23hp5.bkp102MPATODB_20200601_1jv23hpv.bkp56K PATODB_20200601_1kv23hqe.bkpOur incremental 1 backup generated hundreds of KB.
324KPATODB_20200601_1mv23i0k.bkp600KPATODB_20200601_1nv23i0l.bkp200KPATODB_20200601_1pv23i15.bkp16K PATODB_20200601_1qv23i1c.bkpAnd our archivelog backup generated few KB.
148KPATODB_20200601_1sv23i8m.bkpOur backup strategy for backup of database patodb to our Google Disk was setup and tested!
ConclusionWe have created the RMAN backup strategy for our Oracle Database environment on Google Cloud Platform bysetting 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.