Backup Incremental x Restore x Recover

Um Backup Incremental (LEVEL 1 ou superior), diferencial ou não, NÃO é utilizado durante um RESTORE no Oracle, e sim em um RECOVER, em um mecanismo similar à aplicação de ARCHIVEs (Archived Redo Logs). Caso haja um Backup Incremental que contenha os blocos alterados necessários para um RECOVER, o Oracle privilegia este ao invés dos ARCHIVEs.

Ou seja, os Backups Incrementais não são necessários se você tem os ARCHIVEs.

Mas o mecanismo (RECOVER de blocos alterados) e o tempo é similar nos dois casos. Vendo assim, parece que o Backup Incremental é mais uma Feature que foi implementada por pressão do mercado do que por necessidade técnica.

Veja no exemplo abaixo: eu executo um Backup Incremental Level 0 (ou seja, um backup completo), crio uma tabela de 500MB, e em seguida executo um Backup Incremental Level 1. Durante o RESTORE, só são utilizados os Backup Pieces do Level 0. Durante o RECOVER sim, são utilizados os Backup Pieces do Level 1. E mesmo após tornar os Backups de Level 1 inacessíveis (com o comando CHANGE) um RECOVER completo ainda é possível com o Oracle escolhendo automaticamente os ARCHIVEs.

Uma vantagem que vejo é que nem todos os Backup Pieces do Level 1 são necessários para um RECOVER feito após um RESTORE parcial: no exemplo abaixo, foi apenas o RESTORE de uma TABLESPACE, e apenas 2 de 3 Backup Pieces Level 1 foram utilizados. Em caso de ARCHIVEs, todos são necessários.

Resumo: Backup Incremental é um  “Archivezão”, é uma segurança adicional.

[oracle@OBR ~]$ rman CATALOG=USER_CATALOGO/USER_CATALOGO@CATALOGO TARGET /
Recovery Manager: Release 11.2.0.2.0 - Production on Fri Jan 7 15:10:27 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1267239156)
connected to recovery catalog database
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;
Starting backup at 07-JAN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=38 device type=DISK
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=00002 name=/u01/oradata/ORCL/sysaux01.dbf
input datafile file number=00005 name=/home/oracle/example01.NEWNAME.dbf
input datafile file number=00007 name=/u01/oradata/ORCL/users03.dbf
input datafile file number=00008 name=/u01/oradata/ORCL/users04.dbf.VERYNEW
channel ORA_DISK_1: starting piece 1 at 07-JAN-11
channel ORA_DISK_2: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/oradata/ORCL/system01.dbf
input datafile file number=00003 name=/u01/oradata/ORCL/undotbs01.dbf
input datafile file number=00004 name=/u01/oradata/ORCL/users01.dbf
input datafile file number=00006 name=/u01/oradata/ORCL/users02.dbf
channel ORA_DISK_2: starting piece 1 at 07-JAN-11
channel ORA_DISK_1: finished piece 1 at 07-JAN-11
piece handle=/home/oracle/ORCL_20110107_137_1.bkp tag=TAG20110107T151107 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:23
channel ORA_DISK_2: finished piece 1 at 07-JAN-11
piece handle=/home/oracle/ORCL_20110107_138_1.bkp tag=TAG20110107T151107 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:06:43
Finished backup at 07-JAN-11
Starting Control File and SPFILE Autobackup at 07-JAN-11
piece handle=/home/oracle/c-1267239156-20110107-00 comment=NONE
Finished Control File and SPFILE Autobackup at 07-JAN-11
RMAN> EXIT
Recovery Manager complete.
[oracle@OBR ~]$ sqlplus / AS SYSDBA
SQL*Plus: Release 11.2.0.2.0 Production on Fri Jan 7 15:18:55 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> CREATE TABLE T TABLESPACE USERS AS SELECT * FROM ALL_OBJECTS;
Table created.
SQL> INSERT INTO T SELECT * FROM T;
72156 rows created.
SQL> COMMIT;
Commit complete.
SQL> INSERT INTO T SELECT * FROM T;
144312 rows created.
SQL> COMMIT;
Commit complete.
SQL> INSERT INTO T SELECT * FROM T;
288624 rows created.
SQL> COMMIT;
Commit complete.
SQL> INSERT INTO T SELECT * FROM T;
577248 rows created.
SQL> COMMIT;
Commit complete.
SQL> INSERT INTO T SELECT * FROM T;
1154496 rows created.
SQL> COMMIT;
Commit complete.
SQL> INSERT INTO T SELECT * FROM T;
2308992 rows created.
SQL> COMMIT;
Commit complete.
SQL> SELECT TO_CHAR(SUM(BYTES)) FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'T' AND OWNER = 'SYS';
TO_CHAR(SUM(BYTES))
----------------------------------------
545259520
SQL> EXIT
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[oracle@OBR ~]$ rman CATALOG=USER_CATALOGO/USER_CATALOGO@CATALOGO TARGET /
Recovery Manager: Release 11.2.0.2.0 - Production on Fri Jan 7 15:29:36 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1267239156)
connected to recovery catalog database
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;
Starting backup at 07-JAN-11
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=48 device type=DISK
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=/u01/oradata/ORCL/system01.dbf
input datafile file number=00003 name=/u01/oradata/ORCL/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 07-JAN-11
channel ORA_DISK_2: starting compressed incremental level 1 datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/oradata/ORCL/sysaux01.dbf
input datafile file number=00007 name=/u01/oradata/ORCL/users03.dbf
input datafile file number=00008 name=/u01/oradata/ORCL/users04.dbf.VERYNEW
channel ORA_DISK_2: starting piece 1 at 07-JAN-11
channel ORA_DISK_1: finished piece 1 at 07-JAN-11
piece handle=/home/oracle/ORCL_20110107_140_1.bkp tag=TAG20110107T153005 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:38
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=00005 name=/home/oracle/example01.NEWNAME.dbf
input datafile file number=00004 name=/u01/oradata/ORCL/users01.dbf
input datafile file number=00006 name=/u01/oradata/ORCL/users02.dbf
channel ORA_DISK_1: starting piece 1 at 07-JAN-11
channel ORA_DISK_2: finished piece 1 at 07-JAN-11
piece handle=/home/oracle/ORCL_20110107_141_1.bkp tag=TAG20110107T153005 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:03:44
channel ORA_DISK_1: finished piece 1 at 07-JAN-11
piece handle=/home/oracle/ORCL_20110107_142_1.bkp tag=TAG20110107T153005 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:18
Finished backup at 07-JAN-11
Starting Control File and SPFILE Autobackup at 07-JAN-11
piece handle=/home/oracle/c-1267239156-20110107-01 comment=NONE
Finished Control File and SPFILE Autobackup at 07-JAN-11
RMAN> EXIT
Recovery Manager complete.
[oracle@OBR ~]$ rman CATALOG=USER_CATALOGO/USER_CATALOGO@CATALOGO TARGET /
Recovery Manager: Release 11.2.0.2.0 - Production on Fri Jan 7 15:38:20 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1267239156)
connected to recovery catalog database
RMAN> SQL 'ALTER TABLESPACE USERS OFFLINE';
sql statement: ALTER TABLESPACE USERS OFFLINE
starting full resync of recovery catalog
full resync complete
RMAN> RESTORE TABLESPACE USERS;
Starting restore at 07-JAN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=47 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /u01/oradata/ORCL/users03.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/oradata/ORCL/users04.dbf.VERYNEW
channel ORA_DISK_1: reading from backup piece /home/oracle/ORCL_20110107_137_1.bkp
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00004 to /u01/oradata/ORCL/users01.dbf
channel ORA_DISK_2: restoring datafile 00006 to /u01/oradata/ORCL/users02.dbf
channel ORA_DISK_2: reading from backup piece /home/oracle/ORCL_20110107_138_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/ORCL_20110107_137_1.bkp tag=TAG20110107T151107
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:17
channel ORA_DISK_2: piece handle=/home/oracle/ORCL_20110107_138_1.bkp tag=TAG20110107T151107
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:17
Finished restore at 07-JAN-11
RMAN> RECOVER TABLESPACE USERS;
Starting recover at 07-JAN-11
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00007: /u01/oradata/ORCL/users03.dbf
destination for restore of datafile 00008: /u01/oradata/ORCL/users04.dbf.VERYNEW
channel ORA_DISK_1: reading from backup piece /home/oracle/ORCL_20110107_141_1.bkp
channel ORA_DISK_2: starting incremental datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /u01/oradata/ORCL/users01.dbf
destination for restore of datafile 00006: /u01/oradata/ORCL/users02.dbf
channel ORA_DISK_2: reading from backup piece /home/oracle/ORCL_20110107_142_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/ORCL_20110107_141_1.bkp tag=TAG20110107T153005
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
channel ORA_DISK_2: piece handle=/home/oracle/ORCL_20110107_142_1.bkp tag=TAG20110107T153005
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:01:26
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 07-JAN-11
RMAN> SQL 'ALTER TABLESPACE USERS ONLINE';
sql statement: ALTER TABLESPACE USERS ONLINE
RMAN> CHANGE BACKUP TAG TAG20110107T153005 UNAVAILABLE;
changed backup piece unavailable
backup piece handle=/home/oracle/ORCL_20110107_140_1.bkp RECID=123 STAMP=739812609
changed backup piece unavailable
backup piece handle=/home/oracle/ORCL_20110107_141_1.bkp RECID=124 STAMP=739812611
changed backup piece unavailable
backup piece handle=/home/oracle/ORCL_20110107_142_1.bkp RECID=125 STAMP=739812652
Changed 3 objects to UNAVAILABLE status
RMAN> SQL 'ALTER TABLESPACE USERS OFFLINE';
sql statement: ALTER TABLESPACE USERS OFFLINE
starting full resync of recovery catalog
full resync complete
RMAN> RESTORE TABLESPACE USERS;
Starting restore at 07-JAN-11
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /u01/oradata/ORCL/users03.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/oradata/ORCL/users04.dbf.VERYNEW
channel ORA_DISK_1: reading from backup piece /home/oracle/ORCL_20110107_137_1.bkp
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00004 to /u01/oradata/ORCL/users01.dbf
channel ORA_DISK_2: restoring datafile 00006 to /u01/oradata/ORCL/users02.dbf
channel ORA_DISK_2: reading from backup piece /home/oracle/ORCL_20110107_138_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/ORCL_20110107_137_1.bkp tag=TAG20110107T151107
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_2: piece handle=/home/oracle/ORCL_20110107_138_1.bkp tag=TAG20110107T151107
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:25
Finished restore at 07-JAN-11
RMAN> RECOVER TABLESPACE USERS;
Starting recover at 07-JAN-11
using channel ORA_DISK_1
using channel ORA_DISK_2
starting media recovery
archived log for thread 1 with sequence 46 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2011_01_07/o1_mf_1_46_6lglw49t_.arc
archived log for thread 1 with sequence 47 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2011_01_07/o1_mf_1_47_6lglwyr7_.arc
archived log for thread 1 with sequence 48 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2011_01_07/o1_mf_1_48_6lglyxmr_.arc
archived log for thread 1 with sequence 49 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2011_01_07/o1_mf_1_49_6lglzbdw_.arc
archived log for thread 1 with sequence 50 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2011_01_07/o1_mf_1_50_6lglzros_.arc
archived log for thread 1 with sequence 51 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2011_01_07/o1_mf_1_51_6lgm62rz_.arc
archived log for thread 1 with sequence 52 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2011_01_07/o1_mf_1_52_6lgm6hjh_.arc
archived log for thread 1 with sequence 53 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2011_01_07/o1_mf_1_53_6lgm6z5k_.arc
archived log for thread 1 with sequence 54 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2011_01_07/o1_mf_1_54_6lgm7gcf_.arc
archived log for thread 1 with sequence 55 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2011_01_07/o1_mf_1_55_6lgm7yk7_.arc
archived log for thread 1 with sequence 56 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2011_01_07/o1_mf_1_56_6lgm8lro_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2011_01_07/o1_mf_1_46_6lglw49t_.arc thread=1 sequence=46
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2011_01_07/o1_mf_1_47_6lglwyr7_.arc thread=1 sequence=47
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2011_01_07/o1_mf_1_48_6lglyxmr_.arc thread=1 sequence=48
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2011_01_07/o1_mf_1_49_6lglzbdw_.arc thread=1 sequence=49
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2011_01_07/o1_mf_1_50_6lglzros_.arc thread=1 sequence=50
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2011_01_07/o1_mf_1_51_6lgm62rz_.arc thread=1 sequence=51
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2011_01_07/o1_mf_1_52_6lgm6hjh_.arc thread=1 sequence=52
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2011_01_07/o1_mf_1_53_6lgm6z5k_.arc thread=1 sequence=53
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2011_01_07/o1_mf_1_54_6lgm7gcf_.arc thread=1 sequence=54
media recovery complete, elapsed time: 00:01:36
Finished recover at 07-JAN-11
starting full resync of recovery catalog
full resync complete
RMAN> SQL 'ALTER TABLESPACE USERS ONLINE';
sql statement: ALTER TABLESPACE USERS ONLINE
starting full resync of recovery catalog
full resync complete
RMAN>

2 comments

  1. Boa tarde Ricardo.

    Vi uma pequena diferença entre a execução do recover usando o piece incremental em comparação ao recover utilizando os archives.

    Você acha que, para um volume de dados maior, essa diferença pode aumentar significativamente, sendo viável fazer o recover do backup incremental?

    Obrigado.

    1. Oi Anderson.
      Sim, o INCREMENTAL pode ser vantajoso no RECOVER em comparação com os ARCHIVEs.

      Como coloquei aqui neste Post:
      “E quando o INCREMENTAL é vantajoso? Imagine que seu banco de dados tem uma tabela onde uma linha é inserida, e depois alterada, e depois alterada, e depois alterada, e depois alterada, e depois apagada. Nos ARCHIVEs contarão todas as alterações, enquanto no INCREMENTAL constará apenas a última imagem do bloco onde está esta linha, no momento que o backup INCREMENTAL é feito.
      E como você sabe se isto acontece no seu banco de dados, se o INCREMENTAL é vantagem ou não? O mais fácil é comparar o volume de ARCHIVEs gerados durante o dia com o INCREMENTAL de um dia. Quanto menor o volume, menor será o tempo de um eventual RECOVER, ou seja, vai reduzir o RTO. E ainda tem a respeito da vantagem do INCREMENTAL Diferencial x Cumulativo…”

Deixe uma resposta para ricardo@nervinformatica.com.br Cancelar resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.