Backup Incremental x Restore x Recover

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>