Home > Oracle > Oracle 11g block change tracking for fast incremental backups

Oracle 11g block change tracking for fast incremental backups

Enable/disable first:
ALTER DATABASE [ENABLE|DISABLE] BLOCK CHANGE TRACKING

The dynamic performance view V$BLOCK_CHANGE_TRACKING contains the name and size of the block change tracking file plus the status of change tracking:
SQL> select filename, status, bytes from v$block_change_tracking;

To see the benefits of block change tracking, you can use the following query on the dynamic performance view V$BACKUP_DATAFILE to calculate the percentage of blocks read and the number of blocks backed up during an incremental level 1 backup:

select file#, avg(datafile_blocks) blocks,
avg(blocks_read) blocks_read,
avg(blocks_read/datafile_blocks)*100 pct_read,
avg(blocks) blocks_backed_up
from v$backup_datafile
where used_change_tracking = ‘YES’
and incremental_level = 1
group by file#
order by file#
;

Categories: Oracle
  1. December 23, 2011 at 8:06 am

    nice info…thanks

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: