Os backups incrementais nos dão uma opção a mais para definirmos a nossa estratégia de backup. Em vez de realizarmos um backup FULL toda vez, podemos ter backups do tipo Diferencial e Cumulativos. Durante o artigo vou tentar mostrar a diferença deles. Mas o ponto de partida é termos um backup do banco inteiro, o que é feito no caso abaixo. Além disso, é indicado o uso de TAGs nessas operações para que o RMAN possa aplicar seu algoritmo de forma plena:
[oracle@oel8 OPatch]$ rman target /
Recovery Manager: Release 18.0.0.0.0 - Production on Wed Mar 24 04:47:05 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> BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'BSS';
Starting backup at 2021-03-24:04:47:30
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=73 device type=DISK
channel ORA_DISK_1: starting incremental level 0 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-24:04:47:32
channel ORA_DISK_1: finished piece 1 at 2021-03-24:04:48:37
piece handle=/oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd0_BSS_j5ovnncl_.bkp tag=BSS comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
Finished backup at 2021-03-24:04:48:37
Starting Control File and SPFILE Autobackup at 2021-03-24:04:48:37
piece handle=/oracle/fra/RMANDB/autobackup/2021_03_24/o1_mf_s_1068007717_j5ovpq5m_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-03-24:04:48:40
RMAN>
Algumas observações sobre o backup acima: apesar do mesmo ser level 0, ou seja, realizar a operação do banco de dados inteiro, ele é diferente do backup FULL tradicional. Não é possível realizar backups incrementais a partir de um backup FULL tradicional, mas sim do backup que fizemos acima. Outro ponto que é importante notarmos é o tempo decorrido (elapsed time: 00:01:05) e o tamanho dos arquivos listados abaixo (1.21G):
RMAN> LIST BACKUP OF DATABASE;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
102 Incr 0 1.21G DISK 00:00:54 2021-03-24:04:48:26
BP Key: 102 Status: AVAILABLE Compressed: NO Tag: BSS
Piece Name: /oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd0_BSS_j5ovnncl_.bkp
List of Datafiles in backup set 102
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 0 Incr 2001021 2021-03-24:04:47:32 NO /oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
3 0 Incr 2001021 2021-03-24:04:47:32 NO /oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
4 0 Incr 2001021 2021-03-24:04:47:32 NO /oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
5 0 Incr 2001021 2021-03-24:04:47:32 NO /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
7 0 Incr 2001021 2021-03-24:04:47:32 NO /oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
Para simularmos a mudança de blocos de dados no banco de dados, vamos criar um procedure simples e executá-la, para realizar algumas DMLs no schema HR:
[oracle@oel8 OPatch]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 24 04:53:53 2021
Version 18.13.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
SQL> SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE HR.TEST_UPDATE( N NUMBER )
IS
/* This procedure performs sequential update on HR.EMPLOYEES table for testing
purposes. It doesn’t make any real changes on data but real transactions
will be generated
*/
T1 DATE;
TYPE EMP_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
EMPS EMP_TABLE;
BEGIN
IF N IS NULL OR N < 1 THEN
DBMS_OUTPUT.PUT_LINE('INVALID VALUE OF PASSED PARAMETER.');
RETURN;
END IF;
SELECT SYSDATE INTO T1 FROM DUAL;
SELECT EMPLOYEE_ID
BULK COLLECT
INTO EMPS
FROM HR.EMPLOYEES
ORDER BY 1;
FOR I IN 1..N LOOP
FOR J IN 1..EMPS.COUNT LOOP
UPDATE HR.EMPLOYEES SET SALARY = SALARY * 1 WHERE EMPLOYEE_ID=EMPS(J) ;
COMMIT;
END LOOP;
END LOOP;
DBMS_OUTPUT.PUT_LINE(TO_CHAR( (SYSDATE-T1)*24*60*60 , '999,999.99') || ' s');
END TEST_UPDATE;
/SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
Procedure created.
SQL> set serveroutput on
SQL> exec HR.TEST_UPDATE(1000);
13.00 s
PL/SQL procedure successfully completed.
Agora podemos realizar o backup incremental DIFERENCIAL. Notem que por ser o tipo default no rman, omitimos essa opção no script. E para observação também, os backups incrementais são de nível 1:
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'BSS';
Starting backup at 2021-03-24:04:56:53
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=78 device type=DISK
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-24:04:56:54
channel ORA_DISK_1: finished piece 1 at 2021-03-24:04:56:57
piece handle=/oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd1_BSS_j5ow681r_.bkp tag=BSS comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2021-03-24:04:56:57
Starting Control File and SPFILE Autobackup at 2021-03-24:04:56:57
piece handle=/oracle/fra/RMANDB/autobackup/2021_03_24/o1_mf_s_1068008218_j5ow6bsh_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-03-24:04:57:01
Após realizar a consulta deste backup, é possível notar que seu tempo de execução foi de 3 segundos, com tamanho de 16.59M:
RMAN> LIST BACKUP OF DATABASE;
List of Backup Sets
===================
...
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
104 Incr 1 16.59M DISK 00:00:03 2021-03-24:04:56:57
BP Key: 104 Status: AVAILABLE Compressed: NO Tag: BSS
Piece Name: /oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd1_BSS_j5ow681r_.bkp
List of Datafiles in backup set 104
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 1 Incr 2216672 2021-03-24:04:56:54 NO /oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
3 1 Incr 2216672 2021-03-24:04:56:54 NO /oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
4 1 Incr 2216672 2021-03-24:04:56:54 NO /oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
5 1 Incr 2216672 2021-03-24:04:56:54 NO /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
7 1 Incr 2216672 2021-03-24:04:56:54 NO /oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
Realizando novamente um backup diferencial:
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'BSS';
Starting backup at 2021-03-24:05:04:27
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-24:05:04:28
channel ORA_DISK_1: finished piece 1 at 2021-03-24:05:04:31
piece handle=/oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd1_BSS_j5ownd3g_.bkp tag=BSS comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2021-03-24:05:04:31
Starting Control File and SPFILE Autobackup at 2021-03-24:05:04:31
piece handle=/oracle/fra/RMANDB/autobackup/2021_03_24/o1_mf_s_1068008671_j5ownhqy_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-03-24:05:04:34
Listando o backup, vemos que o tempo foi o mesmo (3 segundos) mas o backup piece foi muito menor (288K):
RMAN> LIST BACKUP OF DATABASE;
List of Backup Sets
===================
...
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
106 Incr 1 288.00K DISK 00:00:01 2021-03-24:05:04:28
BP Key: 106 Status: AVAILABLE Compressed: NO Tag: BSS
Piece Name: /oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd1_BSS_j5ownd3g_.bkp
List of Datafiles in backup set 106
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 1 Incr 2216915 2021-03-24:05:04:28 NO /oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
3 1 Incr 2216915 2021-03-24:05:04:28 NO /oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
4 1 Incr 2216915 2021-03-24:05:04:28 NO /oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
5 1 Incr 2216915 2021-03-24:05:04:28 NO /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
7 1 Incr 2216915 2021-03-24:05:04:28 NO /oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
Agora vamos realizar um backup incremental cumulativo. Este tipo de backup contempla todos os blocos alterados a partir do último backup level 0:
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'BSS';
Starting backup at 2021-03-24:05:09:14
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-24:05:09:15
channel ORA_DISK_1: finished piece 1 at 2021-03-24:05:09:18
piece handle=/oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd1_BSS_j5owxcp5_.bkp tag=BSS comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2021-03-24:05:09:18
Starting Control File and SPFILE Autobackup at 2021-03-24:05:09:18
piece handle=/oracle/fra/RMANDB/autobackup/2021_03_24/o1_mf_s_1068008958_j5owxh8s_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-03-24:05:09:19
Ao listar as informações, vemos que o seu tamanho foi de 16.74M:
RMAN> LIST BACKUP OF DATABASE;
List of Backup Sets
===================
...
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
108 Incr 1 16.74M DISK 00:00:01 2021-03-24:05:09:16
BP Key: 108 Status: AVAILABLE Compressed: NO Tag: BSS
Piece Name: /oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd1_BSS_j5owxcp5_.bkp
List of Datafiles in backup set 108
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 1 Incr 2217335 2021-03-24:05:09:15 NO /oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
3 1 Incr 2217335 2021-03-24:05:09:15 NO /oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
4 1 Incr 2217335 2021-03-24:05:09:15 NO /oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
5 1 Incr 2217335 2021-03-24:05:09:15 NO /oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf
7 1 Incr 2217335 2021-03-24:05:09:15 NO /oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
Limpando todos os backupsets do artigo:
RMAN> DELETE BACKUPSET;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
102 102 1 1 AVAILABLE DISK /oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd0_BSS_j5ovnncl_.bkp
103 103 1 1 AVAILABLE DISK /oracle/fra/RMANDB/autobackup/2021_03_24/o1_mf_s_1068007717_j5ovpq5m_.bkp
104 104 1 1 AVAILABLE DISK /oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd1_BSS_j5ow681r_.bkp
105 105 1 1 AVAILABLE DISK /oracle/fra/RMANDB/autobackup/2021_03_24/o1_mf_s_1068008218_j5ow6bsh_.bkp
106 106 1 1 AVAILABLE DISK /oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd1_BSS_j5ownd3g_.bkp
107 107 1 1 AVAILABLE DISK /oracle/fra/RMANDB/autobackup/2021_03_24/o1_mf_s_1068008671_j5ownhqy_.bkp
108 108 1 1 AVAILABLE DISK /oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd1_BSS_j5owxcp5_.bkp
109 109 1 1 AVAILABLE DISK /oracle/fra/RMANDB/autobackup/2021_03_24/o1_mf_s_1068008958_j5owxh8s_.bkp
Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd0_BSS_j5ovnncl_.bkp RECID=102 STAMP=1068007652
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/autobackup/2021_03_24/o1_mf_s_1068007717_j5ovpq5m_.bkp RECID=103 STAMP=1068007719
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd1_BSS_j5ow681r_.bkp RECID=104 STAMP=1068008216
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/autobackup/2021_03_24/o1_mf_s_1068008218_j5ow6bsh_.bkp RECID=105 STAMP=1068008218
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd1_BSS_j5ownd3g_.bkp RECID=106 STAMP=1068008668
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/autobackup/2021_03_24/o1_mf_s_1068008671_j5ownhqy_.bkp RECID=107 STAMP=1068008671
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/backupset/2021_03_24/o1_mf_nnnd1_BSS_j5owxcp5_.bkp RECID=108 STAMP=1068008955
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/autobackup/2021_03_24/o1_mf_s_1068008958_j5owxh8s_.bkp RECID=109 STAMP=1068008959
Deleted 8 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.
Portuguese
English
Spanish