This blog post is part of the “RMAN Back to Basics” series, which can be found here.
Differential Incremental Backups (default)
A differential incremental backup, backs up all the blocks that have changed after the most recent incremental backup, which can be either level 1 or 0.
RMAN determines which is the most recent level 1 backup and then backs up all the blocks changed after that backup, if no level 1 then all the blocks changed after the last level 0 backup are backed up. If no level 0 backup exist, then a level 0 backup is taken.
The diagram below shows how the level 1 differential incremental backups are just backing up from the last level 1:
Cumulative Incremental Backups
A cumulative incremental backup, backs up all the blocks that have changed after the most recent incremental backup at level 0 only.
This reduces the work needed when recovering after a restore as only the desired level 1 is required to be applied instead of several level 1 in sequence. The trade off is as every level 1 backup needs to backup all the blocks changed from the last level 0, it is repeated the work done by the previous level 1, thus require more space and more time.
The diagram below shows how the level 1 cumulative incremental backup are backing up from the last level 0:
Cumulative Incremental Backup Demo
We take an cumulative incremental level 1 backup using my script 8_incremental_level_1_cumulative.sh:
[oracle@dc1sbxdb001 demo]$ ./8_incremental_level_1_cumulative.sh ----------------------- Step 1: Set environment ----------------------- Setting the Database Environment using oraenv... The Oracle base remains unchanged with value /u01/app/oracle ORACLE_SID: ZEDDBA ORACLE_HOME: /u01/app/oracle/product/12.2.0/dbhome_1 Press Enter to continue
The environment is set to my ZEDDBA database, next we will take an differential backup twice, followed by cumulative incremental backup twice:
-------------------------------------------------
Step 2: Take Incremental Level 1 Cumlative Backup
-------------------------------------------------
Content of 8_incremental_level_1_cumulative.cmd file:
BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1';
BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1';
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'INCR LEVEL 1 CUM';
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'INCR LEVEL 1 CUM';
HOST 'read Press Enter to LIST BACKUP';
LIST BACKUP;
Press Enter to continue
Next a differential incremental backup is taken:
Calling 'rman target / cmdfile=/media/sf_Software/scripts/demo/8_incremental_level_1_cumulative.cmd' Recovery Manager: Release 12.2.0.1.0 - Production on Fri May 24 18:27:49 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: ZEDDBA (DBID=3520942925) RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1'; Starting backup at 24-MAY-19 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=157 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=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf channel ORA_DISK_1: starting piece 1 at 24-MAY-19 channel ORA_DISK_1: finished piece 1 at 24-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_ggjbdsg0_.bkp tag=INCR LEVEL 1 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 24-MAY-19 Starting Control File and SPFILE Autobackup at 24-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132076_ggjbdwoh_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 24-MAY-19
Next another differential incremental backup is taken:
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1'; Starting backup at 24-MAY-19 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/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf channel ORA_DISK_1: starting piece 1 at 24-MAY-19 channel ORA_DISK_1: finished piece 1 at 24-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_ggjbdys0_.bkp tag=INCR LEVEL 1 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 24-MAY-19 Starting Control File and SPFILE Autobackup at 24-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132081_ggjbf23y_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 24-MAY-19
Next a cumulative incremental backup is taken:
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'INCR LEVEL 1 CUM'; Starting backup at 24-MAY-19 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/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf channel ORA_DISK_1: starting piece 1 at 24-MAY-19 channel ORA_DISK_1: finished piece 1 at 24-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_CUM_ggjbf3rn_.bkp tag=INCR LEVEL 1 CUM comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 24-MAY-19 Starting Control File and SPFILE Autobackup at 24-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132084_ggjbf516_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 24-MAY-19
Next another cumulative incremental backup is taken:
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'INCR LEVEL 1 CUM'; Starting backup at 24-MAY-19 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/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf channel ORA_DISK_1: starting piece 1 at 24-MAY-19 channel ORA_DISK_1: finished piece 1 at 24-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_CUM_ggjbf6rv_.bkp tag=INCR LEVEL 1 CUM comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 24-MAY-19 Starting Control File and SPFILE Autobackup at 24-MAY-19 piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132087_ggjbf81t_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 24-MAY-19
Next we list the backup within RMAN using ‘LIST BACKUP‘:
RMAN> HOST 'read Press Enter to LIST BACKUP'; host command complete RMAN> LIST BACKUP; List of Backup Sets =================== ... ... ... BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 25 Incr 1 38.23M DISK 00:00:02 24-MAY-19 BP Key: 25 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 1 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_ggjbdsg0_.bkp List of Datafiles in backup set 25 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 1 1 Incr 603534 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf 2 1 Incr 603534 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf 3 1 Incr 603534 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf 4 1 Incr 603534 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 26 Full 8.31M DISK 00:00:00 24-MAY-19 BP Key: 26 Status: AVAILABLE Compressed: NO Tag: TAG20190524T182756 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132076_ggjbdwoh_.bkp SPFILE Included: Modification time: 24-MAY-19 SPFILE db_unique_name: ZEDDBA Control File Included: Ckp SCN: 603541 Ckp time: 24-MAY-19 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 27 Incr 1 64.00K DISK 00:00:03 24-MAY-19 BP Key: 27 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 1 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_ggjbdys0_.bkp List of Datafiles in backup set 27 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 1 1 Incr 603560 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf 2 1 Incr 603560 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf 3 1 Incr 603560 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf 4 1 Incr 603560 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 28 Full 8.31M DISK 00:00:01 24-MAY-19 BP Key: 28 Status: AVAILABLE Compressed: NO Tag: TAG20190524T182801 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132081_ggjbf23y_.bkp SPFILE Included: Modification time: 24-MAY-19 SPFILE db_unique_name: ZEDDBA Control File Included: Ckp SCN: 603567 Ckp time: 24-MAY-19 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 29 Incr 1 38.23M DISK 00:00:01 24-MAY-19 BP Key: 29 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 1 CUM Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_CUM_ggjbf3rn_.bkp List of Datafiles in backup set 29 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 1 1 Incr 603585 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf 2 1 Incr 603585 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf 3 1 Incr 603585 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf 4 1 Incr 603585 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 30 Full 8.31M DISK 00:00:01 24-MAY-19 BP Key: 30 Status: AVAILABLE Compressed: NO Tag: TAG20190524T182804 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132084_ggjbf516_.bkp SPFILE Included: Modification time: 24-MAY-19 SPFILE db_unique_name: ZEDDBA Control File Included: Ckp SCN: 603592 Ckp time: 24-MAY-19 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 31 Incr 1 38.23M DISK 00:00:01 24-MAY-19 BP Key: 31 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 1 CUM Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24/o1_mf_nnnd1_INCR_LEVEL_1_CUM_ggjbf6rv_.bkp List of Datafiles in backup set 31 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 1 1 Incr 603610 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf 2 1 Incr 603610 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf 3 1 Incr 603610 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf 4 1 Incr 603610 24-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 32 Full 8.31M DISK 00:00:01 24-MAY-19 BP Key: 32 Status: AVAILABLE Compressed: NO Tag: TAG20190524T182807 Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_24/o1_mf_s_1009132087_ggjbf81t_.bkp SPFILE Included: Modification time: 24-MAY-19 SPFILE db_unique_name: ZEDDBA Control File Included: Ckp SCN: 603617 Ckp time: 24-MAY-19 Recovery Manager complete. Press Enter to continue
Next we can see the first differential incremental backup backed up 39M of changed blocks, however the second differential incremental backup only backed up 72K as only backed up the blocks changed from the last level 1 backup. Where as the cumulative incremental on both the first and second backup, backed up 39M of changed blocks:
Files size on disk:
...
...
...
/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_24:
total 115M
-rw-r-----. 1 oracle oinstall 39M May 24 18:27 o1_mf_nnnd1_INCR_LEVEL_1_ggjbdsg0_.bkp
-rw-r-----. 1 oracle oinstall 72K May 24 18:28 o1_mf_nnnd1_INCR_LEVEL_1_ggjbdys0_.bkp
-rw-r-----. 1 oracle oinstall 39M May 24 18:28 o1_mf_nnnd1_INCR_LEVEL_1_CUM_ggjbf3rn_.bkp
-rw-r-----. 1 oracle oinstall 39M May 24 18:28 o1_mf_nnnd1_INCR_LEVEL_1_CUM_ggjbf6rv_.bkp
Press Enter to continue
Finally, we update the demo log table:
-------------------------------------
Step 3: Updating and viewing demo log
-------------------------------------
Calling 'sqlplus / as sysdba'
To updated and view demo log
1 row created.
Commit complete.
WHEN COMMENTS
------------------------------ --------------------------------------------------
16-MAY-19 05.31.24.000000 PM Enable Archive Log Mode
16-MAY-19 05.40.23.000000 PM Full Backup
16-MAY-19 05.46.17.000000 PM Full Backup plus Archive Logs
17-MAY-19 04.55.12.000000 PM Image Copy
20-MAY-19 05.13.44.000000 PM Incremental Level 0
22-MAY-19 12.09.17.000000 PM Incremental Level 1
22-MAY-19 12.54.43.000000 PM Incremental Level 1 with BCT
24-MAY-19 06.28.33.000000 PM Incremental Level 1 Cumulative
8 rows selected.
Press Enter to exit shell script
[oracle@dc1sbxdb001 demo]$
Reference
- 8.8 About RMAN Incremental Backups
- 8.8.1.1 About Differential Incremental Backups
- 8.8.1.2 About Cumulative Incremental Backups
- Database Backup and Recovery User’s Guide
- Database Backup and Recovery Reference
Scripts
To download all 2 in one zip: 8_incremental_level_1_cumulative.zip
The rest of the series
- Oracle Database File Placement Best Practice & Create Database Demo
- RMAN Full Backup & Demo
- RMAN Image Copy & Demo
- RMAN Incremental & Demo Part 1 (Level 0)
- RMAN Incremental & Demo Part 2 (Level 1)
- RMAN Incremental with Block Change Tracking & Demo
- RMAN Incremental Differential vs Cumulative & Demo
- RMAN Incremental Updating Backup & Demo Part 1
- RMAN Incremental Updating Backup & Demo Part 2
- Flashback
- RMAN Block Media Recovery
- RMAN Recover database with only FRA
- RMAN Obsolete
Please Note: Links to the blog posts will be released daily and updated here.
If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.
Thanks
Zed DBA (Zahid Anwar)
Zed DBA's Oracle Blog 
Pingback: RMAN Back to Basics Series | Zed DBA's Oracle Blog
Pingback: Oracle Database File Placement Best Practice & Create Database Demo | Zed DBA's Oracle Blog
Pingback: RMAN Full Backup & Demo | Zed DBA's Oracle Blog
Pingback: RMAN Image Copy & Demo | Zed DBA's Oracle Blog
Pingback: RMAN Incremental & Demo Part 1 (Level 0) | Zed DBA's Oracle Blog
Pingback: RMAN Incremental Updating Backup & Demo Part 1 | Zed DBA's Oracle Blog
Pingback: RMAN Incremental & Demo Part 2 (Level 1) | Zed DBA's Oracle Blog
Pingback: RMAN Incremental with Block Change Tracking & Demo | Zed DBA's Oracle Blog
Nice article
LikeLike
Thanks 🙂
LikeLike
The link is not working to download the scripts, please share the scripts.
https://raw.githubusercontent.com/zeddba/scripts/master/8_incremental_level_1_cumulative.sh
LikeLike
It’s definitely work. Can you check your connection and try again.
LikeLike
Thanks it is working now.
LikeLike
Excellent 🙂
LikeLike
very informative… well explained
LikeLike
Thank you, glad you found informative 🙂
LikeLike