Estrategia de Respaldos Incrementales RMAN para Base de Datos Oracle

June 1, 2020 7-minute read

RMAN • Oracle • Backup • Google Cloud

Introducción

Vamos a crear una estrategia para mantener protegidos nuestros datos en caso de pérdida o falla en nuestro ambiente de Bases de Datos Oracle en la Google Cloud Platform. Para lograrlo generaremos un respaldo semanal Full, respaldos diarios Incrementales así como respaldos intradía para los Archives usando RMAN.

Prerrequisitos

Contamos con una instancia Oracle 19c multitenant patodb con una base de datos pluggable taller recientemente configurada y corriendo en una Virtual Machine Instance en la Google Cloud Platform.
Para los pasos previos de configuración de RMAN revisar la nota: Respaldar Bases de Datos Oracle con RMAN hacia un Disco Local.

Respaldos con RMAN

De acuerdo a la Guía de Usuario para Respaldo y Recuperación los respaldos Incrementales están definidos de la siguiente forma:

Un respaldo incremental nivel 0, el cual es la base para subsequentes respaldos incrementales, copia todos los bloques que contienen datos.

Un respaldo incremental diferencial respalda todos los bloques cambiados después del respaldo incremental más reciente de nivel 1 o 0.

Entonces nuestra estrategía será ejecutar:

Semanalmente un respaldo Full incremental level 0
Diariamente respaldos Incrementales incremental level 1
Intradía respaldos de Archives archivelog all

Respaldo Full Incremental 0

Los respaldo incrementales son en línea o sea que se pueden correr mientras la base está abierta y siendo utilizada.
Inciemos ejecutando el primer respaldo incremental level 0 que también incluye los 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

Podemos ver de los mensajes de salida que los siguientes elementos fueron respaldados:

Todos los archive logs generados hasta el momento
Todos los archivos de datos de la CDB raíz y de la PDB semilla
Todos los archivos de datos de nuestra base de datos PDB

Respaldo Incremental 1

Ahora vamos a ejecutar un respaldo Incremental level 1:

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

Ahora podemos observar que sólo los archivos de datos con cambios y sólo aquellos archivos de archive generados después del último incremental 0 fueron respaldados, los demás fueron omitidos.

Respaldo de Archive

También queremos probar el respaldo de los archive logs:

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

Podemos ver que solamente aquellos archivos de archive generados después del último incremental 1 fueron respaldados.

Tamaño de los Respaldos

Finalmente queremos revisar el tamaño de cada respaldo, para validar la estrategia diferencial.
Nuestro respaldo incremental 0 generó cientos de 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

Nuestro respaldo incremental 1 generó cientos de KB.

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

Y nuestro respaldo de archivelog generó sólo unos pocos KB.

148K    PATODB_20200601_1sv23i8m.bkp

¡Nuestra estrategía de respaldos para la base de datos patodb hacia nuestro Disco de Google ha sido definida y probada!

Conclusión

Hemos creado una estrategía de respaldos RMAN para nuestro ambiente de Base de Datos Orace en la Google Cloud Platform configurando respaldos Full, Incremental y de Archive que nos ayudan a mantener protegidos nuestra base de datos en caso de cualquier pérdida de datos o falla de infraestructura.

Combinando los diferentes tipos de respaldos nos aseguramos de poder recuperar nuestra base de datos con el mínimo retroceso en el tiempo pero a la vez ahorrando recursos de disco pues sólo los datos diferenciales son respaldados cada vez.