Skip to main content
Delphix

Snapsync Troubleshooting - Consider Enabling Block Change Tracking (BCT)

Oracle Block Change Tracking (BCT) is an enhancement to improve the performance of incremental database backups.  Without BCT, incremental backups must scan every block in every datafile within the database in order to locate blocks changed since the previous backup.  With BCT, a list of changed blocks is maintained within a Block Change file outside the database, and incremental backups merely read from the list to locate the changed blocks, reducing the elapsed time of incremental backups substantially.

For a more detailed explanation, here are the links to the Oracle documentation on block change tracking...

Show overall usage and effectiveness of BCT during incremental backups

If BCT is enabled, then the question may still be asked, "Is it being used"?  This query can be executed on the source database to display overall usage of BCT during incremental backups...

alter session set nls_date_format = 'DD-MON-YY HH24:MI:SS';
set pagesize 100 linesize 130 pause off trimout on trimspool on

select   used_change_tracking, count(*), sum(blocks_read), sum(datafile_blocks), 
         trunc(sum(blocks_read)/sum(datafile_blocks)*100) "% Read"

from     v$backup_datafile 
where    trunc(completion_time) >= trunc(sysdate-1) and file# > 0
group by used_change_tracking;

List datafiles not using BCT during incremental backups

If the query above shows that some datafiles are not using BCT, then it may be useful to know which ones, as their identity might provide a clue as to why.  This query can be executed on the source database to list datafiles not using BCT during incremental backups...

alter session set nls_date_format = 'DD-MON-YY HH24:MI:SS';
set pagesize 100 linesize 130 pause off trimout on trimspool on

select   file#, completion_time, blocks_read, datafile_blocks,
         trunc(blocks_read/datafile_blocks*100) "% Read"
from     v$backup_datafile
where    used_change_tracking='NO'
and      trunc(completion_time) >= trunc(sysdate-1)
order by file#, completion_time;

  • Was this article helpful?