RMAN - Incremental Backup - Beginners Guide (Part 2)

§  Incremental backups capture the block level changes (Block being changed/used) from the previous incremental backup (either Level 0 – Cumulative Incremental Backup or Level 1 – Differential Incremental Backup)

§  Command to use for Incremental backup in RMAN prompt – BACKUP INCREMENTAL

§  Backup Levels in Backup Strategy –

Level 0 Incremental Backup: A full database backup which is considered as starting point in the backup strategy

Level 1 Incremental Backup: A backup taken after the Level 0 backup and has only the blocks changes since the level 0 backup. If no level 0 backup exists in when we run a level 1 backup, then RMAN makes a level 0 backup automatically.

Note:

Incremental backups are allowed for both Cold (Consistent) & Hot (In-Consistent) backups.

But for Cold (NonArchiveLog database), incremental backup is possible only the database is in mounted state after a consistent shutdown

Types of backups in Level 1 Incremental Backup:

Differential Incremental Backups:

In a differential level 1 backup, RMAN backs up all blocks that have changed since the most recent incremental backup at level 1 (cumulative or differential) or level 0. For example, in a differential level 1 backup, RMAN determines which level 1 backup occurred most recently and backs up all blocks modified after that backup. If no level 1 is available, then RMAN copies all blocks changed since the base level 0 backup.

Incremental backups are differential by default (one need to mention CUMULATIVE keyword in BACKUP command to have a Level 1 cumulative backup, else RMAN considers it as Differential level 1 backup)

Cumulative backups are preferable to differential backups when recovery time is more important than disk space, because fewer incremental backups must be applied during recovery.

To make incremental backups of the database:

    1. BACKUP INCREMENTAL LEVEL 0 DATABASE; - Full backup which serves as base/starting point for incremental backup strategy

RMAN> backup incremental level 0 database;

Starting backup at 27-OCT-13

using channel ORA_DISK_1

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

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

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

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

input datafile file number=00005 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/example.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/Encrypted.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/example1.dbf

channel ORA_DISK_1: starting piece 1 at 27-OCT-13

channel ORA_DISK_1: finished piece 1 at 27-OCT-13

piece handle=/u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_27/o1_mf_nnnd0_TAG20131027T095528_96s5fxl3_.bkp tag=TAG20131027T095528 comment=NONE

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

channel ORA_DISK_1: starting incremental level 0 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 27-OCT-13

channel ORA_DISK_1: finished piece 1 at 27-OCT-13

piece handle=/u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_27/o1_mf_ncsn0_TAG20131027T095528_96s5gvf9_.bkp tag=TAG20131027T095528 comment=NONE

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

Finished backup at 27-OCT-13


2. BACKUP INCREMENTAL LEVEL 1 DATABASE; -  This command creates the level 1 differential incremental backup (as default RMAN creates the differential unless the cumulative keyword is mentioned)

RMAN> backup incremental level 1 database;

Starting backup at 27-OCT-13

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/MyDB/MyDBDataFiles/system01.dbf

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

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

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

input datafile file number=00005 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/example.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/Encrypted.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/example1.dbf

channel ORA_DISK_1: starting piece 1 at 27-OCT-13

channel ORA_DISK_1: finished piece 1 at 27-OCT-13

piece handle=/u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_27/o1_mf_nnnd1_TAG20131027T100718_96s643f8_.bkp tag=TAG20131027T100718 comment=NONE

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

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 27-OCT-13

channel ORA_DISK_1: finished piece 1 at 27-OCT-13

piece handle=/u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_27/o1_mf_ncsn1_TAG20131027T100718_96s64hs3_.bkp tag=TAG20131027T100718 comment=NONE

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

Finished backup at 27-OCT-13

3. BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE; - This command creates the level 1 cumulative incremental backup

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE; 

Starting backup at 27-OCT-13

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/MyDB/MyDBDataFiles/system01.dbf

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

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

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

input datafile file number=00005 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/example.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/Encrypted.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/MyDB/MyDBDataFiles/example1.dbf

channel ORA_DISK_1: starting piece 1 at 27-OCT-13

channel ORA_DISK_1: finished piece 1 at 27-OCT-13

piece handle=/u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_27/o1_mf_nnnd1_TAG20131027T101742_96s6qjxg_.bkp tag=TAG20131027T101742 comment=NONE

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

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 27-OCT-13

channel ORA_DISK_1: finished piece 1 at 27-OCT-13

piece handle=/u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_27/o1_mf_ncsn1_TAG20131027T101742_96s6qyqx_.bkp tag=TAG20131027T101742 comment=NONE

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

Finished backup at 27-OCT-13

To displayed the details of the backups use below command, look for Ckp SCN which useful to identify whether a particular datafile is backed up using Level 1 Differential or Cummulative backup using the below sql statement(Details with example will upload in other post)

select file#,incremental_level,incremental_change#,checkpoint_change# , checkpoint_time
from v$backup_datafile
order by 1,4;

RMAN> list backup summary;

List of Backups

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

Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

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

24      B  0  A DISK        27-OCT-13       1       1       NO         TAG20131027T095528

25      B  0  A DISK        27-OCT-13       1       1       NO         TAG20131027T095528

26      B  1  A DISK        27-OCT-13       1       1       NO         TAG20131027T100718

27      B  1  A DISK        27-OCT-13       1       1       NO         TAG20131027T100718

28      B  1  A DISK        27-OCT-13       1       1       NO         TAG20131027T101742

29      B  1  A DISK        27-OCT-13       1       1       NO         TAG20131027T101742

RMAN> list backup;

List of Backup Sets

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

24      Incr 0  490.35M    DISK        00:00:20     27-OCT-13     

        BP Key: 24   Status: AVAILABLE  Compressed: NO  Tag: TAG20131027T095528

        Piece Name: /u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_27/o1_mf_nnnd0_TAG20131027T095528_96s5fxl3_.bkp

  List of Datafiles in backup set 24

  File LV Type Ckp SCN    Ckp Time  Name

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

  1    0  Incr 792794     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/system01.dbf

  2    0  Incr 792794     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/sysaux01.dbf

  3    0  Incr 792794     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/undotbs01.dbf

  4    0  Incr 792794     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/users01.dbf

  5    0  Incr 792794     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/example.dbf

  6    0  Incr 792794     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/example1.dbf

  8    0  Incr 792794     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/Encrypted.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

25      Incr 0  8.36M      DISK        00:00:09     27-OCT-13     

        BP Key: 25   Status: AVAILABLE  Compressed: NO  Tag: TAG20131027T095528

        Piece Name: /u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_27/o1_mf_ncsn0_TAG20131027T095528_96s5gvf9_.bkp

  SPFILE Included: Modification time: 27-OCT-13

  SPFILE db_unique_name: MYDB

  Control File Included: Ckp SCN: 792803       Ckp time: 27-OCT-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

26      Incr 1  336.00K    DISK        00:00:05     27-OCT-13     

        BP Key: 26   Status: AVAILABLE  Compressed: NO  Tag: TAG20131027T100718

        Piece Name: /u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_27/o1_mf_nnnd1_TAG20131027T100718_96s643f8_.bkp

  List of Datafiles in backup set 26

  File LV Type Ckp SCN    Ckp Time  Name

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

  1    1  Incr 793087     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/system01.dbf

  2    1  Incr 793087     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/sysaux01.dbf

  3    1  Incr 793087     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/undotbs01.dbf

  4    1  Incr 793087     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/users01.dbf

  5    1  Incr 793087     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/example.dbf

  6    1  Incr 793087     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/example1.dbf

  8    1  Incr 793087     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/Encrypted.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

27      Incr 1  8.36M      DISK        00:00:09     27-OCT-13     

        BP Key: 27   Status: AVAILABLE  Compressed: NO  Tag: TAG20131027T100718

        Piece Name: /u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_27/o1_mf_ncsn1_TAG20131027T100718_96s64hs3_.bkp

  SPFILE Included: Modification time: 27-OCT-13

  SPFILE db_unique_name: MYDB

  Control File Included: Ckp SCN: 793091       Ckp time: 27-OCT-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

28      Incr 1  520.00K    DISK        00:00:04     27-OCT-13     

        BP Key: 28   Status: AVAILABLE  Compressed: NO  Tag: TAG20131027T101742

        Piece Name: /u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_27/o1_mf_nnnd1_TAG20131027T101742_96s6qjxg_.bkp

  List of Datafiles in backup set 28

  File LV Type Ckp SCN    Ckp Time  Name

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

  1    1  Incr 793371     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/system01.dbf

  2    1  Incr 793371     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/sysaux01.dbf

  3    1  Incr 793371     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/undotbs01.dbf

  4    1  Incr 793371     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/users01.dbf

  5    1  Incr 793371     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/example.dbf

  6    1  Incr 793371     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/example1.dbf

  8    1  Incr 793371     27-OCT-13 /u01/app/oracle/oradata/MyDB/MyDBDataFiles/Encrypted.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

29      Incr 1  8.36M      DISK        00:00:08     27-OCT-13     

        BP Key: 29   Status: AVAILABLE  Compressed: NO  Tag: TAG20131027T101742

        Piece Name: /u01/app/oracle/MyDB_fast_recovery_area/MYDB/backupset/2013_10_27/o1_mf_ncsn1_TAG20131027T101742_96s6qyqx_.bkp

  SPFILE Included: Modification time: 27-OCT-13

  SPFILE db_unique_name: MYDB

  Control File Included: Ckp SCN: 793374       Ckp time: 27-OCT-13

List of useful data dictionary view related to RMAN backups

SQL> select table_name from dict where table_name like '%BACKUP%';

TABLE_NAME

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

V$RMAN_BACKUP_JOB_DETAILS

V$RMAN_BACKUP_SUBJOB_DETAILS

V$RMAN_BACKUP_TYPE

GV$BACKUP

GV$BACKUP_ASYNC_IO

GV$BACKUP_CORRUPTION

GV$BACKUP_DATAFILE

GV$BACKUP_DEVICE

GV$BACKUP_NONLOGGED

GV$BACKUP_PIECE

GV$BACKUP_REDOLOG

GV$BACKUP_SET

GV$BACKUP_SPFILE

GV$BACKUP_SYNC_IO

V$BACKUP

V$BACKUP_ARCHIVELOG_DETAILS

V$BACKUP_ARCHIVELOG_SUMMARY

V$BACKUP_ASYNC_IO

V$BACKUP_CONTROLFILE_DETAILS

V$BACKUP_CONTROLFILE_SUMMARY

V$BACKUP_COPY_DETAILS

V$BACKUP_COPY_SUMMARY

V$BACKUP_CORRUPTION

V$BACKUP_DATAFILE

V$BACKUP_DATAFILE_DETAILS

V$BACKUP_DATAFILE_SUMMARY

V$BACKUP_DEVICE

V$BACKUP_FILES

V$BACKUP_NONLOGGED

V$BACKUP_PIECE

V$BACKUP_PIECE_DETAILS

V$BACKUP_REDOLOG

V$BACKUP_SET

V$BACKUP_SET_DETAILS

V$BACKUP_SET_SUMMARY

V$BACKUP_SPFILE

V$BACKUP_SPFILE_DETAILS

V$BACKUP_SPFILE_SUMMARY

V$BACKUP_SYNC_IO

V$UNUSABLE_BACKUPFILE_DETAILS

40 rows selected.

SQL> select Table_name from dict where table_name like '%RMAN%';

TABLE_NAME

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

V$RMAN_BACKUP_JOB_DETAILS

V$RMAN_BACKUP_SUBJOB_DETAILS

V$RMAN_BACKUP_TYPE

V$RMAN_COMPRESSION_ALGORITHM

V$RMAN_CONFIGURATION

V$RMAN_ENCRYPTION_ALGORITHMS

V$RMAN_OUTPUT

V$RMAN_STATUS

GV$RMAN_COMPRESSION_ALGORITHM

GV$RMAN_CONFIGURATION

GV$RMAN_OUTPUT

11 rows selected.