Tuesday 27 September 2016

RMAN incremental backups and change tracking file

The common misconception I observe is how the block change tracking file (BCTF) works. One of oracle published books on backup and recovery says:
"The Block Change Tracking File . By default, when doing an incremental backup, any datafile that has changed in any way will be backed up. This can make incremental backups take longer and will make them larger. RMAN offers the ability to just back up changed database blocks. This can make your incremental database backups much smaller and shorter. " (Freeman, Hart - Oracle RMAN 11g Backup and Recovery)
This stating that the whole datafile will be backed up, which is not very correct. The incremental backup works on block level disregarding BCTF.

Lets do a test:

The environment is one big table called T1 with 6.5  million of records (6 499 987) and about 7GB of space.

1. Lets update fraction of records and do incremental backup without change tracking
SELECT STATUS, FILENAME
FROM   V$BLOCK_CHANGE_TRACKING;

STATUS     FILENAME
---------- ------------------------------------------
DISABLED

update t1 set t='TEST1' where c>100000 and c<101000;

12987 rows updated.
commit;

Commit complete.


The incremental backup took 7 minutes. And the size is 30MB (the whole datafile size is 7GB)

2. Lets update fraction of records again and do incremental backup with change tracking

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
 USING FILE '/u01/oracle/oradata/fast_recovery_area/ED12/chtf01.dbf' REUSE;

Database altered.

SQL> update t1 set t='TEST1' where c>100000 and c<101000
12987 rows updated.

SQL> commit;
Commit complete.
We have to reconnect the rman in order to pick up the change tracking settings.

The backup took 3 minutes and the filesize still was 30MB.

CONCLUSION:

The change tracking file is a performance enhancing feature, Instead of scanning the whole datafile for changed blocks it will only scan bitmap of recorder changes from the change tracking file. As we seen in our example, the incremental backup size hasn't changed, but the backup time improved 2 times. In both cases (without BCTF or with BCTF) The backup size was 30MB compared to 7GB table size.



No comments:

Post a Comment