Creating Database Incrementally Updated Backup

Backup & Recovery / 14/07/2022

Nos backups do tipo “AS COPY”, temos um recurso muito interessante que pode nos ajudar a diminuir o tempo de recuperação de um ambiente. Este mecanismo consiste em ter um backup level 0 do banco de dados, e os posteriores backups level 1 sendo \”aplicados\” dos datafiles gerados no backup level 0. E nos bastidores, o que ocorre não é um recover dos datafiles, mas sim uma alteração a nível de bloco de dados, o que é muito mais rápido caso façamos uma comparação. Uma breve descrição desse item está documentada AQUI. Neste artigo, vou tentar simular esta funcionalidade.

Executando o comando abaixo, como ainda não temos nenhum banco level 0, o RMAN ignorará a primeira linha, que é referente à alteração dos copy datafiles (no copy of datafile 1 found to recover). Depois disso, como um backup level 0 ainda não existe, o mesmo será disparado (no parent backup or copy of datafile 1 found):

[oracle@oel8 ~]$ rman target /
 
Recovery Manager: Release 18.0.0.0.0 - Production on Thu Mar 25 04:21:02 2021
Version 18.13.0.0.0
 
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: RMANDB (DBID=3825250984)
 
RMAN> Run
{
        RECOVER COPY OF DATABASE WITH TAG 'BSS';
        BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'BSS' DATABASE;
}2> 3> 4> 5>
 
Starting recover at 2021-03-25:04:21:12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
no copy of datafile 1 found to recover
no copy of datafile 3 found to recover
no copy of datafile 4 found to recover
no copy of datafile 5 found to recover
no copy of datafile 7 found to recover
Finished recover at 2021-03-25:04:21:12
 
Starting backup at 2021-03-25:04:21:12
using channel ORA_DISK_1
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 4 found
no parent backup or copy of datafile 5 found
no parent backup or copy of datafile 7 found
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
output file name=/oracle/fra/RMANDB/datafile/o1_mf_system_j5rgh980_.dbf tag=BSS RECID=22 STAMP=1068092488
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
output file name=/oracle/fra/RMANDB/datafile/o1_mf_sysaux_j5rgj29n_.dbf tag=BSS RECID=23 STAMP=1068092505
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
output file name=/oracle/fra/RMANDB/datafile/o1_mf_undotbs1_j5rgjkfy_.dbf tag=BSS RECID=24 STAMP=1068092516
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
output file name=/oracle/fra/RMANDB/datafile/o1_mf_ts_corte_j5rgjo05_.dbf tag=BSS RECID=25 STAMP=1068092518
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
output file name=/oracle/fra/RMANDB/datafile/o1_mf_users_j5rgjqqp_.dbf tag=BSS RECID=26 STAMP=1068092519
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2021-03-25:04:22:00
 
Starting Control File and SPFILE Autobackup at 2021-03-25:04:22:00
piece handle=/oracle/fra/RMANDB/autobackup/2021_03_25/o1_mf_s_1068092520_j5rgjsc1_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-03-25:04:22:01

Listando as informações mínimas do ambiente:

RMAN> LIST COPY OF DATABASE;
 
List of Datafile Copies
=======================
 
Key     File S Completion Time     Ckp SCN    Ckp Time            Sparse
------- ---- - ------------------- ---------- ------------------- ------
22      1    A 2021-03-25:04:21:28 2226174    2021-03-25:04:21:13 NO
        Name: /oracle/fra/RMANDB/datafile/o1_mf_system_j5rgh980_.dbf
        Tag: BSS
 
23      3    A 2021-03-25:04:21:45 2226185    2021-03-25:04:21:38 NO
        Name: /oracle/fra/RMANDB/datafile/o1_mf_sysaux_j5rgj29n_.dbf
        Tag: BSS
 
24      4    A 2021-03-25:04:21:56 2226193    2021-03-25:04:21:53 NO
        Name: /oracle/fra/RMANDB/datafile/o1_mf_undotbs1_j5rgjkfy_.dbf
        Tag: BSS
 
25      5    A 2021-03-25:04:21:58 2226197    2021-03-25:04:21:56 NO
        Name: /oracle/fra/RMANDB/datafile/o1_mf_ts_corte_j5rgjo05_.dbf
        Tag: BSS
 
26      7    A 2021-03-25:04:21:59 2226201    2021-03-25:04:21:59 NO
        Name: /oracle/fra/RMANDB/datafile/o1_mf_users_j5rgjqqp_.dbf
        Tag: BSS

Como as informações acima são limitadas, podemos extrair um pouco mais de informações utilizando o comando abaixo, principalmente no que se referel ao level de backup que foi utilizado (0):

RMAN> SELECT FILE#, STATUS, CREATION_TIME, COMPLETION_TIME, TAG, BLOCKS*BLOCK_SIZE/1024/1024 SIZE_MB, INCREMENTAL_LEVEL FROM V$DATAFILE_COPY WHERE DELETED ='NO' ORDER BY FILE#, RECID;
 
     FILE# S CREATION_TIME       COMPLETION_TIME
---------- - ------------------- -------------------
TAG                                 SIZE_MB INCREMENTAL_LEVEL
-------------------------------- ---------- -----------------
         1 A 2018-02-07:19:20:19 2021-03-25:04:21:28
BSS                                     860                 0
 
         3 A 2018-02-07:19:20:39 2021-03-25:04:21:45
BSS                                     630                 0
 
         4 A 2018-02-07:21:43:00 2021-03-25:04:21:56
BSS                                     305                 0
 
         5 A 2020-05-02:22:06:13 2021-03-25:04:21:58
BSS                                     200                 0
 
         7 A 2018-02-07:19:22:01 2021-03-25:04:21:59
BSS                                       5                 0

Gerando alguns archives:

RMAN> ALTER SYSTEM SWITCH LOGFILE;
 
Statement processed
 
RMAN> ALTER SYSTEM SWITCH LOGFILE;
 
Statement processed
 
RMAN> ALTER SYSTEM SWITCH LOGFILE;
 
Statement processed

Executando novamente o bloco RMAN. Observem que a primeira linha novamente foi ignorada, pois ainda não havia backups level 1 para serem aplicados nos backups level 0. Desse modo, foi realizado um backup level 1 conforme abaixo:

RMAN> Run
{
        RECOVER COPY OF DATABASE WITH TAG 'BSS';
        BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'BSS' DATABASE;
}2> 3> 4> 5>
 
Starting recover at 2021-03-25:04:31:56
using channel ORA_DISK_1
no copy of datafile 1 found to recover
no copy of datafile 3 found to recover
no copy of datafile 4 found to recover
no copy of datafile 5 found to recover
no copy of datafile 7 found to recover
Finished recover at 2021-03-25:04:31:56
 
Starting backup at 2021-03-25:04:31:56
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
input datafile file number=00003 name=/oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
input datafile file number=00004 name=/oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
input datafile file number=00005 name=/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
input datafile file number=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
channel ORA_DISK_1: starting piece 1 at 2021-03-25:04:31:57
channel ORA_DISK_1: finished piece 1 at 2021-03-25:04:31:58
piece handle=/oracle/fra/RMANDB/backupset/2021_03_25/o1_mf_nnnd1_BSS_j5rh3fht_.bkp tag=BSS comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-03-25:04:31:58
 
Starting Control File and SPFILE Autobackup at 2021-03-25:04:31:58
piece handle=/oracle/fra/RMANDB/autobackup/2021_03_25/o1_mf_s_1068093118_j5rh3gwl_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-03-25:04:31:59

Podemos ver o backupset gerado por conta do backup level 1 disparado:

RMAN> LIST BACKUPSET OF DATABASE;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
120     Incr 1  544.00K    DISK        00:00:00     2021-03-25:04:31:57
        BP Key: 120   Status: AVAILABLE  Compressed: NO  Tag: BSS
        Piece Name: /oracle/fra/RMANDB/backupset/2021_03_25/o1_mf_nnnd1_BSS_j5rh3fht_.bkp
  List of Datafiles in backup set 120
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  1    1  Incr 2226592    2021-03-25:04:31:57              NO    /oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
  3    1  Incr 2226592    2021-03-25:04:31:57              NO    /oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
  4    1  Incr 2226592    2021-03-25:04:31:57              NO    /oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
  5    1  Incr 2226592    2021-03-25:04:31:57              NO    /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
  7    1  Incr 2226592    2021-03-25:04:31:57              NO    /oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf

Gerando mais archives:

RMAN> ALTER SYSTEM SWITCH LOGFILE;
 
Statement processed
 
RMAN> ALTER SYSTEM SWITCH LOGFILE;
 
Statement processed
 
RMAN> ALTER SYSTEM SWITCH LOGFILE;
 
Statement processed

Executando o bloco RMAN pela terceira vez. Agora podemos ver que a primeira linha realizou a aplicação do level 1 nos datafiles copy level 0:

RMAN> Run
{
        RECOVER COPY OF DATABASE WITH TAG 'BSS';
        BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'BSS' DATABASE;
}2> 3> 4> 5>
 
Starting recover at 2021-03-25:04:36:27
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile copies to recover
recovering datafile copy file number=00001 name=/oracle/fra/RMANDB/datafile/o1_mf_system_j5rgh980_.dbf
recovering datafile copy file number=00003 name=/oracle/fra/RMANDB/datafile/o1_mf_sysaux_j5rgj29n_.dbf
recovering datafile copy file number=00004 name=/oracle/fra/RMANDB/datafile/o1_mf_undotbs1_j5rgjkfy_.dbf
recovering datafile copy file number=00005 name=/oracle/fra/RMANDB/datafile/o1_mf_ts_corte_j5rgjo05_.dbf
recovering datafile copy file number=00007 name=/oracle/fra/RMANDB/datafile/o1_mf_users_j5rgjqqp_.dbf
channel ORA_DISK_1: reading from backup piece /oracle/fra/RMANDB/backupset/2021_03_25/o1_mf_nnnd1_BSS_j5rh3fht_.bkp
channel ORA_DISK_1: piece handle=/oracle/fra/RMANDB/backupset/2021_03_25/o1_mf_nnnd1_BSS_j5rh3fht_.bkp tag=BSS
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 2021-03-25:04:36:29
 
Starting backup at 2021-03-25:04:36:29
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
input datafile file number=00003 name=/oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
input datafile file number=00004 name=/oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
input datafile file number=00005 name=/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
input datafile file number=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
channel ORA_DISK_1: starting piece 1 at 2021-03-25:04:36:29
channel ORA_DISK_1: finished piece 1 at 2021-03-25:04:36:30
piece handle=/oracle/fra/RMANDB/backupset/2021_03_25/o1_mf_nnnd1_BSS_j5rhcy26_.bkp tag=BSS comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-03-25:04:36:30
 
Starting Control File and SPFILE Autobackup at 2021-03-25:04:36:30
piece handle=/oracle/fra/RMANDB/autobackup/2021_03_25/o1_mf_s_1068093390_j5rhcz1j_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-03-25:04:36:31

Também é possível constatar isso observando que o Checkpoint SCN dos datafiles copy foram incrementados, se comparados com as informações que coletamos no início do artigo após o primeiro backup:

RMAN> LIST COPY OF DATABASE;
 
List of Datafile Copies
=======================
 
Key     File S Completion Time     Ckp SCN    Ckp Time            Sparse
------- ---- - ------------------- ---------- ------------------- ------
27      1    A 2021-03-25:04:36:28 2226592    2021-03-25:04:31:57 NO
        Name: /oracle/fra/RMANDB/datafile/o1_mf_system_j5rgh980_.dbf
        Tag: BSS
 
31      3    A 2021-03-25:04:36:28 2226592    2021-03-25:04:31:57 NO
        Name: /oracle/fra/RMANDB/datafile/o1_mf_sysaux_j5rgj29n_.dbf
        Tag: BSS
 
30      4    A 2021-03-25:04:36:28 2226592    2021-03-25:04:31:57 NO
        Name: /oracle/fra/RMANDB/datafile/o1_mf_undotbs1_j5rgjkfy_.dbf
        Tag: BSS
 
28      5    A 2021-03-25:04:36:28 2226592    2021-03-25:04:31:57 NO
        Name: /oracle/fra/RMANDB/datafile/o1_mf_ts_corte_j5rgjo05_.dbf
        Tag: BSS
 
29      7    A 2021-03-25:04:36:28 2226592    2021-03-25:04:31:57 NO
        Name: /oracle/fra/RMANDB/datafile/o1_mf_users_j5rgjqqp_.dbf
        Tag: BSS

Limpando todos os arquivos e backup gerados no artigo:

RMAN> DELETE COPY OF DATABASE;
 
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
List of Datafile Copies
=======================
 
Key     File S Completion Time     Ckp SCN    Ckp Time            Sparse
------- ---- - ------------------- ---------- ------------------- ------
27      1    A 2021-03-25:04:36:28 2226592    2021-03-25:04:31:57 NO
        Name: /oracle/fra/RMANDB/datafile/o1_mf_system_j5rgh980_.dbf
        Tag: BSS
 
31      3    A 2021-03-25:04:36:28 2226592    2021-03-25:04:31:57 NO
        Name: /oracle/fra/RMANDB/datafile/o1_mf_sysaux_j5rgj29n_.dbf
        Tag: BSS
 
30      4    A 2021-03-25:04:36:28 2226592    2021-03-25:04:31:57 NO
        Name: /oracle/fra/RMANDB/datafile/o1_mf_undotbs1_j5rgjkfy_.dbf
        Tag: BSS
 
28      5    A 2021-03-25:04:36:28 2226592    2021-03-25:04:31:57 NO
        Name: /oracle/fra/RMANDB/datafile/o1_mf_ts_corte_j5rgjo05_.dbf
        Tag: BSS
 
29      7    A 2021-03-25:04:36:28 2226592    2021-03-25:04:31:57 NO
        Name: /oracle/fra/RMANDB/datafile/o1_mf_users_j5rgjqqp_.dbf
        Tag: BSS
 
 
Do you really want to delete the above objects (enter YES or NO)? Y
deleted datafile copy
datafile copy file name=/oracle/fra/RMANDB/datafile/o1_mf_system_j5rgh980_.dbf RECID=27 STAMP=1068093388
deleted datafile copy
datafile copy file name=/oracle/fra/RMANDB/datafile/o1_mf_sysaux_j5rgj29n_.dbf RECID=31 STAMP=1068093388
deleted datafile copy
datafile copy file name=/oracle/fra/RMANDB/datafile/o1_mf_undotbs1_j5rgjkfy_.dbf RECID=30 STAMP=1068093388
deleted datafile copy
datafile copy file name=/oracle/fra/RMANDB/datafile/o1_mf_ts_corte_j5rgjo05_.dbf RECID=28 STAMP=1068093388
deleted datafile copy
datafile copy file name=/oracle/fra/RMANDB/datafile/o1_mf_users_j5rgjqqp_.dbf RECID=29 STAMP=1068093388
Deleted 5 objects
 
 
RMAN> DELETE BACKUPSET;
 
using channel ORA_DISK_1
 
List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
119     119     1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/autobackup/2021_03_25/o1_mf_s_1068092520_j5rgjsc1_.bkp
120     120     1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/backupset/2021_03_25/o1_mf_nnnd1_BSS_j5rh3fht_.bkp
121     121     1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/autobackup/2021_03_25/o1_mf_s_1068093118_j5rh3gwl_.bkp
122     122     1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/backupset/2021_03_25/o1_mf_nnnd1_BSS_j5rhcy26_.bkp
123     123     1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/autobackup/2021_03_25/o1_mf_s_1068093390_j5rhcz1j_.bkp
 
Do you really want to delete the above objects (enter YES or NO)? Y
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/autobackup/2021_03_25/o1_mf_s_1068092520_j5rgjsc1_.bkp RECID=119 STAMP=1068092521
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/backupset/2021_03_25/o1_mf_nnnd1_BSS_j5rh3fht_.bkp RECID=120 STAMP=1068093117
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/autobackup/2021_03_25/o1_mf_s_1068093118_j5rh3gwl_.bkp RECID=121 STAMP=1068093118
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/backupset/2021_03_25/o1_mf_nnnd1_BSS_j5rhcy26_.bkp RECID=122 STAMP=1068093390
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/autobackup/2021_03_25/o1_mf_s_1068093390_j5rhcz1j_.bkp RECID=123 STAMP=1068093391
Deleted 5 objects

Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.

2 thoughts on “Creating Database Incrementally Updated Backup”

  1. Pingback: Configuring Oracle Backup Retention Policy – Bruno Santos da Silva

  2. Pingback: Configuring Oracle Backup Retention Policy – SWIV

Leave a Comment

Your email address will not be published.

pt Portuguese
en Englishpt Portuguesees Spanish