Incrementally updating rman backups

Incrementally updating backups is an important feature of Oracle database available from oracle 10g onwards.I am going to test some of the scenarios related to this backup strategy.We need to have enough space on the disk to keep the backups for this strategy.

• First day of the backup will be similar to L0 backup.We run 2 commands daily.First command will take the rman incremental backup and second will rollforward our existing backup with the backup we have just taken.

1. BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG WEEKLY DATABASE;
2. RECOVER COPY OF DATABASE WITH TAG WEEKLY;

RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG WEEKLY DATABASE;

Starting backup at 03-SEP-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 4 found
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/test/system01.dbf
output file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_system_84bmsbx4_.dbf tag=WEEKLY RECID=1 STAMP=793046771
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/test/sysaux01.dbf
output file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_sysaux_84bmts04_.dbf tag=WEEKLY RECID=2 STAMP=793046805
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/test/undotbs01.dbf
output file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_undotbs1_84bmvw32_.dbf tag=WEEKLY RECID=3 STAMP=793046815
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 03-SEP-12
channel ORA_DISK_1: finished piece 1 at 03-SEP-12
piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_ncsn1_WEEKLY_84bmw4gy_.bkp tag=WEEKLY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/test/users01.dbf
output file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_users_84bmw5hk_.dbf tag=WEEKLY RECID=4 STAMP=793046822
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 03-SEP-12

RMAN> RECOVER COPY OF DATABASE WITH TAG WEEKLY;

Starting recover at 03-SEP-12

using channel ORA_DISK_1

no copy of datafile 1 found to recover

no copy of datafile 2 found to recover

no copy of datafile 3 found to recover

no copy of datafile 4 found to recover

Finished recover at 03-SEP-12

RMAN> list backup;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

1       Incr 1  9.36M      DISK        00:00:02     03-SEP-12

        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: WEEKLY

        Piece Name: /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_ncsn1_WEEKLY_84bmw4gy_.bkp

  SPFILE Included: Modification time: 03-SEP-12

  SPFILE db_unique_name: TEST

  Control File Included: Ckp SCN: 954398       Ckp time: 03-SEP-12

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

/

System altered.

SQL> /

System altered.

SQL> /

System altered.

• On the second day it will rollforward the changes to existing database backup

RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG WEEKLY DATABASE;

Starting backup at 03-SEP-12

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=/u01/app/oracle/oradata/test/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/test/sysaux01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/test/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/test/users01.dbf

channel ORA_DISK_1: starting piece 1 at 03-SEP-12

channel ORA_DISK_1: finished piece 1 at 03-SEP-12

piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_nnnd1_WEEKLY_84bnfhol_.bkp tag=WEEKLY comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 03-SEP-12

channel ORA_DISK_1: finished piece 1 at 03-SEP-12

piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_ncsn1_WEEKLY_84bngxyo_.bkp tag=WEEKLY comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

Finished backup at 03-SEP-12

RMAN> RECOVER COPY OF DATABASE WITH TAG WEEKLY;

Starting recover at 03-SEP-12

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=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_system_84bmsbx4_.dbf

recovering datafile copy file number=00002 name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_sysaux_84bmts04_.dbf

recovering datafile copy file number=00003 name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_undotbs1_84bmvw32_.dbf

recovering datafile copy file number=00004 name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_users_84bmw5hk_.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_nnnd1_WEEKLY_84bnfhol_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_nnnd1_WEEKLY_84bnfhol_.bkp tag=WEEKLY

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished recover at 03-SEP-12

• If we add a tablespace to the database ,next rman backup will acknowledge the change and it will take the backup of new datafile.

SQL> create tablespace test1 datafile '/u01/app/oracle/oradata/test/test1.dbf' size 50m;

Tablespace created.

RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG WEEKLY DATABASE;

Starting backup at 03-SEP-12

using channel ORA_DISK_1

no parent backup or copy of datafile 5 found

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=/u01/app/oracle/oradata/test/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/test/sysaux01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/test/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/test/users01.dbf

channel ORA_DISK_1: starting piece 1 at 03-SEP-12

channel ORA_DISK_1: finished piece 1 at 03-SEP-12

piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_nnnd1_WEEKLY_84bnlr6s_.bkp tag=WEEKLY comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/u01/app/oracle/oradata/test/test1.dbf

output file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_test1_84bnmk9h_.dbf tag=WEEKLY RECID=9 STAMP=793047571

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 03-SEP-12

channel ORA_DISK_1: finished piece 1 at 03-SEP-12

piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_ncsn1_WEEKLY_84bnmoh4_.bkp tag=WEEKLY comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 03-SEP-12

RMAN>

RECOVER COPY OF DATABASE WITH TAG WEEKLY;

RMAN>

Starting recover at 03-SEP-12

using channel ORA_DISK_1

no copy of datafile 5 found to recover

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=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_system_84bmsbx4_.dbf

recovering datafile copy file number=00002 name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_sysaux_84bmts04_.dbf

recovering datafile copy file number=00003 name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_undotbs1_84bmvw32_.dbf

recovering datafile copy file number=00004 name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_users_84bmw5hk_.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_nnnd1_WEEKLY_84bnlr6s_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_nnnd1_WEEKLY_84bnlr6s_.bkp tag=WEEKLY

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished recover at 03-SEP-12

• Lets check the recovery.I am going to delete the users01 datafile belonging to the users tablespace.

SQL> !rm -rf /u01/app/oracle/oradata/test/users01.dbf

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  622149632 bytes

Fixed Size                  2215904 bytes

Variable Size             377487392 bytes

Database Buffers          239075328 bytes

Redo Buffers                3371008 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: '/u01/app/oracle/oradata/test/users01.dbf'

• To recover I have to restore the backup image copies and apply the archive log changes.

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Sep 3 19:03:31 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (DBID=2091192679, not open)

RMAN> restore database;

Starting restore at 03-SEP-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring datafile 00001

input datafile copy RECID=13 STAMP=793047598 file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_system_84bmsbx4_.dbf

destination for restore of datafile 00001: /u01/app/oracle/oradata/test/system01.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00001

output file name=/u01/app/oracle/oradata/test/system01.dbf RECID=0 STAMP=0

channel ORA_DISK_1: restoring datafile 00002

input datafile copy RECID=12 STAMP=793047598 file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_sysaux_84bmts04_.dbf

destination for restore of datafile 00002: /u01/app/oracle/oradata/test/sysaux01.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00002

output file name=/u01/app/oracle/oradata/test/sysaux01.dbf RECID=0 STAMP=0

channel ORA_DISK_1: restoring datafile 00003

input datafile copy RECID=11 STAMP=793047598 file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_undotbs1_84bmvw32_.dbf

destination for restore of datafile 00003: /u01/app/oracle/oradata/test/undotbs01.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00003

output file name=/u01/app/oracle/oradata/test/undotbs01.dbf RECID=0 STAMP=0

channel ORA_DISK_1: restoring datafile 00004

input datafile copy RECID=10 STAMP=793047598 file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_users_84bmw5hk_.dbf

destination for restore of datafile 00004: /u01/app/oracle/oradata/test/users01.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00004

output file name=/u01/app/oracle/oradata/test/users01.dbf RECID=0 STAMP=0

channel ORA_DISK_1: restoring datafile 00005

input datafile copy RECID=9 STAMP=793047571 file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_test1_84bnmk9h_.dbf

destination for restore of datafile 00005: /u01/app/oracle/oradata/test/test1.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00005

output file name=/u01/app/oracle/oradata/test/test1.dbf RECID=0 STAMP=0

Finished restore at 03-SEP-12

RMAN> recover database;

Starting recover at 03-SEP-12

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_7_84bnpcd5_.arc

archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_8_84bnpdcg_.arc

archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_9_84bnphy5_.arc

archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_10_84bnpjwf_.arc

archived log for thread 1 with sequence 11 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_11_84bnpkpo_.arc

archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_12_84bnpld7_.arc

archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_7_84bnpcd5_.arc thread=1 sequence=7

archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_8_84bnpdcg_.arc thread=1 sequence=8

archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_9_84bnphy5_.arc thread=1 sequence=9

archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_10_84bnpjwf_.arc thread=1 sequence=10

media recovery complete, elapsed time: 00:00:01

Finished recover at 03-SEP-12

• As an alternate and fast process we can switch the datafile to the backup copy

SQL> !rm /u01/app/oracle/oradata/test/system01.dbf

SQL> startup

ORACLE instance started.

Total System Global Area  622149632 bytes

Fixed Size                  2215904 bytes

Variable Size             377487392 bytes

Database Buffers          239075328 bytes

Redo Buffers                3371008 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/app/oracle/oradata/test/system01.dbf'

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Sep 3 19:10:25 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (DBID=2091192679, not open)

RMAN> switch database to copy;

using target database control file instead of recovery catalog

datafile 1 switched to datafile copy "/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_system_84bmsbx4_.dbf"

datafile 2 switched to datafile copy "/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_sysaux_84bmts04_.dbf"

datafile 3 switched to datafile copy "/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_undotbs1_84bmvw32_.dbf"

datafile 4 switched to datafile copy "/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_users_84bmw5hk_.dbf"

datafile 5 switched to datafile copy "/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_test1_84bnmk9h_.dbf"

RMAN> recover database;

Starting recover at 03-SEP-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_7_84bnpcd5_.arc

archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_8_84bnpdcg_.arc

archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_9_84bnphy5_.arc

archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_10_84bnpjwf_.arc

archived log for thread 1 with sequence 11 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_11_84bnpkpo_.arc

archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_12_84bnpld7_.arc

archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_7_84bnpcd5_.arc thread=1 sequence=7

archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_8_84bnpdcg_.arc thread=1 sequence=8

archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_9_84bnphy5_.arc thread=1 sequence=9

archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_10_84bnpjwf_.arc thread=1 sequence=10

media recovery complete, elapsed time: 00:00:01

Finished recover at 03-SEP-12

SQL>  alter database open;

Database altered.