RMAN Incremental with Block Change Tracking & Demo

Posted on by Standard

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Block Change Tracking

Block change tracking (BCT) improves incremental backup performance by recording the changed blocks in each datafile in the block change tracking file, thus avoiding the need to scan every block in the datafile for changes.  Only applicable for level 1 backups.  Block change tracking file is 1/30,000 the size of the data blocks being tracked, in 10MB chunk, which would track 300GB.  So to track 1TB database would only be 40MB.

Incremental with Block Change Tracking Demo

We take an incremental level 1 backup with block change tracking using my script 7_incremental_level_1_with_BCT.sh:

[oracle@dc1sbxdb001 demo]$ ./7_incremental_level_1_with_BCT.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, then next we create some “junk” data in the database to bloat the backups to show the difference in performance with BCT:

-----------------------------------------------------------
Step 2: Take Incremental Level 1 with Block Change Tracking
-----------------------------------------------------------

Clearing all backups...

Creating junk!

Tablespace created.

Table created.

Table altered.

151956 rows created.

Commit complete.

303912 rows created.

Commit complete.

607824 rows created.

Commit complete.

1215648 rows created.

Commit complete.

2431296 rows created.

Commit complete.

4862592 rows created.

Commit complete.

insert /*+ append */ into junk value (select * from junk)
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.JUNK by 8192 in tablespace JUNK

Commit complete.

insert /*+ append */ into junk value (select * from junk)
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.JUNK by 8192 in tablespace JUNK

Commit complete.

BYTES/1024/1024
---------------
1984

Press Enter to continue

You can see 1,984MB of data has been created that will be backed up.

Next we will take an incremental level 0 backup followed by level 1, then repeated with BCT enabled and then finally repeat again with BCT disabled to show that caching or the like wasn’t the reason for the performance increase:

Content of 7_incremental_level_1_with_BCT.cmd file:
BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'INCR LEVEL 0';
BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1';
HOST 'read Press Enter to enable Block Change Tracking';
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
SELECT STATUS, FILENAME FROM V$BLOCK_CHANGE_TRACKING;
HOST 'read Press Enter to take backup with BCT';
BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'INCR LEVEL 0';
BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1';
HOST 'read Press Enter to disable Block Change Tracking';
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'INCR LEVEL 0';
BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1';
HOST 'read Press Enter to LIST BACKUP';
LIST BACKUP;

Press Enter to continue

Next an incremental level 0 backup followed by level 1 is taken:

Calling 'rman target / cmdfile=/media/sf_Software/scripts/demo/7_incremental_level_1_with_BCT.cmd'

Recovery Manager: Release 12.2.0.1.0 - Production on Wed May 22 12:51:57 2019

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

connected to target database: ZEDDBA (DBID=3520942925)

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'INCR LEVEL 0';
Starting backup at 22-MAY-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
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=00005 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_junk_ggbfxdb4_.dbf
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 22-MAY-19

channel ORA_DISK_1: finished piece 1 at 22-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_22/o1_mf_nnnd0_INCR_LEVEL_0_ggbfz0nn_.bkp tag=INCR LEVEL 0 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 22-MAY-19

Starting Control File and SPFILE Autobackup at 22-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_22/o1_mf_s_1008939154_ggbg03bn_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-MAY-19

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1';
Starting backup at 22-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=00005 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_junk_ggbfxdb4_.dbf
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 22-MAY-19
channel ORA_DISK_1: finished piece 1 at 22-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_22/o1_mf_nnnd1_INCR_LEVEL_1_ggbg052p_.bkp tag=INCR LEVEL 1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
Finished backup at 22-MAY-19

Starting Control File and SPFILE Autobackup at 22-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_22/o1_mf_s_1008939164_ggbg0df4_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-MAY-19

host command complete

Please Note: It took 35 seconds to take the level 0 backup and 8 seconds to take level 1 backup.

Now we enable block change tracking:

RMAN> HOST 'read Press Enter to enable Block Change Tracking';
1> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
2> SELECT STATUS, FILENAME FROM V$BLOCK_CHANGE_TRACKING;
Statement processed

STATUS 
----------
FILENAME 
--------------------------------------------------------------------------------
ENABLED 
/u01/app/oracle/oradata/ZEDDBA/changetracking/o1_mf_ggbg0g1y_.chg

Next an incremental level 0 backup followed by level 1 is taken again to show the performance improvement:

RMAN> HOST 'read Press Enter to take backup with BCT';

host command complete

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'INCR LEVEL 0';
Starting backup at 22-MAY-19
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=00005 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_junk_ggbfxdb4_.dbf
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 22-MAY-19
channel ORA_DISK_1: finished piece 1 at 22-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_22/o1_mf_nnnd0_INCR_LEVEL_0_ggbg0nl4_.bkp tag=INCR LEVEL 0 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 22-MAY-19

Starting Control File and SPFILE Autobackup at 22-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_22/o1_mf_s_1008939207_ggbg1qsp_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-MAY-19

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1';
Starting backup at 22-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=00005 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_junk_ggbfxdb4_.dbf
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 22-MAY-19
channel ORA_DISK_1: finished piece 1 at 22-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_22/o1_mf_nnnd1_INCR_LEVEL_1_ggbg1snf_.bkp tag=INCR LEVEL 1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-MAY-19

Starting Control File and SPFILE Autobackup at 22-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_22/o1_mf_s_1008939210_ggbg1txm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-MAY-19

Now we can see the level 1 backup with block change tracking took only 1 second compared to 8 seconds without BCT 🙂

Now we disable block change tracking to prove the performance increase was due to BCT:

RMAN> HOST 'read Press Enter to disable Block Change Tracking';

host command complete

RMAN> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
Statement processed

Next an incremental level 0 backup followed by level 1 is taken again to show the performance improvement has reverted:

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'INCR LEVEL 0';
Starting backup at 22-MAY-19
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=00005 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_junk_ggbfxdb4_.dbf
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 22-MAY-19
channel ORA_DISK_1: finished piece 1 at 22-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_22/o1_mf_nnnd0_INCR_LEVEL_0_ggbg243o_.bkp tag=INCR LEVEL 0 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 22-MAY-19

Starting Control File and SPFILE Autobackup at 22-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_22/o1_mf_s_1008939255_ggbg37gs_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-MAY-19

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INCR LEVEL 1';
Starting backup at 22-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=00005 name=/u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_junk_ggbfxdb4_.dbf
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 22-MAY-19

channel ORA_DISK_1: finished piece 1 at 22-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_22/o1_mf_nnnd1_INCR_LEVEL_1_ggbg3997_.bkp tag=INCR LEVEL 1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 22-MAY-19

Starting Control File and SPFILE Autobackup at 22-MAY-19
piece handle=/u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_22/o1_mf_s_1008939264_ggbg3js2_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-MAY-19

Now we can see the level 1 backup without block change tracking took 7 seconds, similar to the original 8 seconds without BCT 🙂

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
------- ---- -- ---------- ----------- ------------ ---------------
12 Incr 0 2.44G DISK 00:00:32 22-MAY-19 
BP Key: 12 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 0
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_22/o1_mf_nnnd0_INCR_LEVEL_0_ggbfz0nn_.bkp
List of Datafiles in backup set 12
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 0 Incr 578031 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
2 0 Incr 578031 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
3 0 Incr 578031 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
4 0 Incr 578031 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
5 0 Incr 578031 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_junk_ggbfxdb4_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
13 Full 8.31M DISK 00:00:01 22-MAY-19 
BP Key: 13 Status: AVAILABLE Compressed: NO Tag: TAG20190522T125234
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_22/o1_mf_s_1008939154_ggbg03bn_.bkp
SPFILE Included: Modification time: 22-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 578048 Ckp time: 22-MAY-19

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14 Incr 1 72.00K DISK 00:00:06 22-MAY-19 
BP Key: 14 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 1
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_22/o1_mf_nnnd1_INCR_LEVEL_1_ggbg052p_.bkp
List of Datafiles in backup set 14
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 1 Incr 578067 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
2 1 Incr 578067 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
3 1 Incr 578067 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
4 1 Incr 578067 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
5 1 Incr 578067 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_junk_ggbfxdb4_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
15 Full 8.31M DISK 00:00:00 22-MAY-19 
BP Key: 15 Status: AVAILABLE Compressed: NO Tag: TAG20190522T125244
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_22/o1_mf_s_1008939164_ggbg0df4_.bkp
SPFILE Included: Modification time: 22-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 578078 Ckp time: 22-MAY-19

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16 Incr 0 2.44G DISK 00:00:32 22-MAY-19 
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 0
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_22/o1_mf_nnnd0_INCR_LEVEL_0_ggbg0nl4_.bkp
List of Datafiles in backup set 16
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 0 Incr 578123 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
2 0 Incr 578123 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
3 0 Incr 578123 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
4 0 Incr 578123 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
5 0 Incr 578123 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_junk_ggbfxdb4_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17 Full 8.31M DISK 00:00:00 22-MAY-19 
BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20190522T125327
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_22/o1_mf_s_1008939207_ggbg1qsp_.bkp
SPFILE Included: Modification time: 22-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 578142 Ckp time: 22-MAY-19

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18 Incr 1 72.00K DISK 00:00:00 22-MAY-19 
BP Key: 18 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 1
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_22/o1_mf_nnnd1_INCR_LEVEL_1_ggbg1snf_.bkp
List of Datafiles in backup set 18
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 1 Incr 578162 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
2 1 Incr 578162 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
3 1 Incr 578162 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
4 1 Incr 578162 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
5 1 Incr 578162 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_junk_ggbfxdb4_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
19 Full 8.31M DISK 00:00:00 22-MAY-19 
BP Key: 19 Status: AVAILABLE Compressed: NO Tag: TAG20190522T125330
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_22/o1_mf_s_1008939210_ggbg1txm_.bkp
SPFILE Included: Modification time: 22-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 578169 Ckp time: 22-MAY-19

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
20 Incr 0 2.44G DISK 00:00:30 22-MAY-19 
BP Key: 20 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 0
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_22/o1_mf_nnnd0_INCR_LEVEL_0_ggbg243o_.bkp
List of Datafiles in backup set 20
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 0 Incr 578206 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
2 0 Incr 578206 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
3 0 Incr 578206 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
4 0 Incr 578206 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
5 0 Incr 578206 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_junk_ggbfxdb4_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21 Full 8.31M DISK 00:00:00 22-MAY-19 
BP Key: 21 Status: AVAILABLE Compressed: NO Tag: TAG20190522T125415
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_22/o1_mf_s_1008939255_ggbg37gs_.bkp
SPFILE Included: Modification time: 22-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 578223 Ckp time: 22-MAY-19

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
22 Incr 1 72.00K DISK 00:00:06 22-MAY-19 
BP Key: 22 Status: AVAILABLE Compressed: NO Tag: INCR LEVEL 1
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_22/o1_mf_nnnd1_INCR_LEVEL_1_ggbg3997_.bkp
List of Datafiles in backup set 22
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 1 Incr 578242 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_system_gftkr3fv_.dbf
2 1 Incr 578242 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_sysaux_gftkr792_.dbf
3 1 Incr 578242 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_undotbs1_gftkr944_.dbf
4 1 Incr 578242 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_users_gftkr9fc_.dbf
5 1 Incr 578242 22-MAY-19 NO /u01/app/oracle/oradata/ZEDDBA/datafile/o1_mf_junk_ggbfxdb4_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
23 Full 8.31M DISK 00:00:00 22-MAY-19 
BP Key: 23 Status: AVAILABLE Compressed: NO Tag: TAG20190522T125424
Piece Name: /u01/app/oracle/fast_recovery_area/ZEDDBA/autobackup/2019_05_22/o1_mf_s_1008939264_ggbg3js2_.bkp
SPFILE Included: Modification time: 22-MAY-19
SPFILE db_unique_name: ZEDDBA
Control File Included: Ckp SCN: 578250 Ckp time: 22-MAY-19

RMAN>

Recovery Manager complete.

Press Enter to continue

Next we show the size of the backups to show they were consistently the same size throughout:

Files size on disk:
/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_16:
total 0

/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_20:
total 0

/u01/app/oracle/fast_recovery_area/ZEDDBA/backupset/2019_05_22:
total 7.4G
-rw-r-----. 1 oracle oinstall 2.5G May 22 12:52 o1_mf_nnnd0_INCR_LEVEL_0_ggbfz0nn_.bkp
-rw-r-----. 1 oracle oinstall 80K May 22 12:52 o1_mf_nnnd1_INCR_LEVEL_1_ggbg052p_.bkp
-rw-r-----. 1 oracle oinstall 2.5G May 22 12:53 o1_mf_nnnd0_INCR_LEVEL_0_ggbg0nl4_.bkp
-rw-r-----. 1 oracle oinstall 80K May 22 12:53 o1_mf_nnnd1_INCR_LEVEL_1_ggbg1snf_.bkp
-rw-r-----. 1 oracle oinstall 2.5G May 22 12:54 o1_mf_nnnd0_INCR_LEVEL_0_ggbg243o_.bkp
-rw-r-----. 1 oracle oinstall 80K May 22 12:54 o1_mf_nnnd1_INCR_LEVEL_1_ggbg3997_.bkp

Press Enter to continue

Next we drop the junk as we no longer require this:

Dropping junk!

Tablespace dropped.

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

7 rows selected.

Press Enter to exit shell script

[oracle@dc1sbxdb001 demo]$

License Implications

BCT is part of Enterprise Edition and is not available on Standard Edition.  In a non Data Guard Environment, there no issue with enabling BCT on Enterprise Edition.  However, in a Data Guard environment, BCT can not be used on a Physical Standby unless you have Active Data Guard as BCT on Physical Standby is part of the Active Data Guard, which is a licensable product.

This is explained well in this blog post by Bastiaan Bak:
Using Block Change Tracking with Oracle 12c Data Guard

Reference

Scripts

  1. 7_incremental_level_1_with_BCT.sh
  2. 7_incremental_level_1_with_BCT.cmd
  3. 7_create_junk.sql
  4. 7_drop_junk.sql
  5. delete_all_backups.cmd

To download all 5 in one zip: 7_incremental_level_1_with_BCT.zip

The rest of the series

  1. Oracle Database File Placement Best Practice & Create Database Demo
  2. RMAN Full Backup & Demo
  3. RMAN Image Copy & Demo
  4. RMAN Incremental & Demo Part 1 (Level 0)
  5. RMAN Incremental & Demo Part 2 (Level 1)
  6. RMAN Incremental with Block Change Tracking & Demo
  7. RMAN Incremental Differential vs Cumulative & Demo
  8. RMAN Incremental Updating Backup & Demo Part 1
  9. RMAN Incremental Updating Backup & Demo Part 2
  10. Flashback
  11. RMAN Block Media Recovery
  12. RMAN Recover database with only FRA
  13. RMAN Obsolete

Please Note: Links to the blog posts will be released daily and updated here.

Updates

30th May 2019: Added “License Implications” as requested in the comments.

 

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)

12 thoughts on “RMAN Incremental with Block Change Tracking & Demo”

  1. Pingback: RMAN Back to Basics Series | Zed DBA's Oracle Blog

  2. Pingback: Oracle Database File Placement Best Practice & Create Database Demo | Zed DBA's Oracle Blog

  3. Pingback: RMAN Full Backup & Demo | Zed DBA's Oracle Blog

  4. Pingback: RMAN Incremental & Demo Part 2 (Level 1) | Zed DBA's Oracle Blog

  5. Pingback: RMAN Image Copy & Demo | Zed DBA's Oracle Blog

  6. Pingback: RMAN Incremental & Demo Part 1 (Level 0) | Zed DBA's Oracle Blog

  7. Pingback: RMAN Incremental Differential vs Cumulative & Demo | Zed DBA's Oracle Blog

  8. Great article but please highlight that BCT is separately licensed and in the case of dataguard backups, level 1 iincremental backups cannot be done unless you purchase the BCT license or use ADG (active data guard)

    Like

    Reply

    • Thanks Ravin 🙂 BCT is actually part of Enterprise Edition. It is not available on Standard Edition. In a non Data Guard Environment, there no issue with enabling BCT. However, in a Data Guard environment, BCT can not be used on Physical Standby unless you have Active Data Guard as BCT on Physical Standby is part of the Active Data Guard, which is licensable product.

      Like

      Reply

  9. Pingback: RMAN Incremental Updating Backup & Demo Part 1 | Zed DBA's Oracle Blog

Leave a comment