Oracle v$block_change_tracking tips Oracle Database Tips by Donald Burleson

When using Oracle block change tracking we see this procedure.  As data blocks change, the Change Tracking Writer (CTWR) background process tracks the changed blocks in a private area of memory.

When a commit is issued against the data block, the block change tracking information is copied to a shared area in Large Pool called the CTWR buffer. During the checkpoint, the CTWR process writes the information from the CTWR RAM buffer to the change-tracking file.

Also see there important related notes on Oracle block change tracking.

When doing incremental backups with Oracle 10g RMAN we need a mechanism to bypass those data blocks which have not changed.  In a typical tablespace, SQL insert activity places new data blocks (as the tablespace and data file extends) in "hot" areas of the data file.  (Of course, if you have a low PCTUSED, you may re-use blocks anywhere within the data files.)

Hints for block change tracking

To maximize the efficiency of incremental backups with block change tracking, try to use the APPEND hints with inserts or a low PCTUSED values for tables and indexes to ensure that all new rows are placed in new blocks.  This reduces overhead, since only new blocks are tracked with the block change tracking mechanism.

Oracle block change tracking syntax

The syntax for Oracle block level change tracking is simple:

ALTER DATABASE
ENABLE BLOCK CHANGE TRACKING
USING FILE os_file_name;

The "USING FILE os_file_name" syntax allows you to define the location of the change tracking file on the OS, or you can omit this clause by enabling OMF (Oracle-Managed Files).

Tips for using block-level tracking

By default, Oracle will not record block change information.  To enable this feature, you need to issue the following command:

SQL> alter database enable block change tracking;

SQL> alter database
enable block change tracking
using file '/u01/app/oracvle/mysid/data/block_change_tracking.dbf';

To disable this feature, you issue this command:

SQL> alter database disable block change tracking;

To monitor the status of block change tracking, you type:

select filename, status, bytes
   from   v$block_change_tracking
/

STATUS       FILE                          BYTES
-----------  ----------------------------  --------------
ENABLED      /dba/backup/01_mf_yzmrr7.chg  10,000,000

You can view the size of the CTWR dba buffer by looking at v$sgastat:

SELECT *
FROM v$sgastat
WHERE name like 'CTWR%';

Confio software notes that there is a hidden parameter that can increase or decrease the size of the CTWR dba buffer. However, you should always check with Oracle Support before using a hidden parameter value as Oracle may not support these types of changes.

_bct_public_dba_buffer_size = total size of all public change tracking dba buffers.
 

Dr. Hall has some great tips on using Oracle block level change tracking, and see his bestseller "Oracle PL/SQL Tuning Secrets":

"In Oracle 10g it is possible to track changed blocks using a change tracking file. Enabling change tracking does produce a small overhead, but it greatly improves the performance of incremental backups. The current change tracking status can be displayed using the following query:

SELECT status FROM v$block_change_tracking;

Change tracking is enabled using the ALTER DATABASE command:

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

By default the change tracking file is created as an Oracle Managed File (OMF) in the location pointed to by the DB_CREATE_FILE_DEST parameter. An alternate location can be specified using the following command:

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/u01/oradata/MYSID/rman_change_track.f' REUSE;"

"Using this feature all changes between the SCN of the original image copy and the SCN of the incremental backup are applied to the image copy, winding it forward to make the equivalent of a new database image copy without the overhead of such a backup. The following example shows how this can be used:

RUN {
RECOVER COPY OF DATABASE WITH TAG 'incr_backup' UNTIL TIME 'SYSDATE - 7';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE;
}"

Enabling, Disabling and Monitoring Block Change Tracking

By default, Oracle will not record block change information. To enable this feature, you need to issue the following command:

SQL> alter database enable block change tracking;

To disable this feature, you issue this command:

SQL> alter database disable block change tracking;

To monitor the status of block change tracking, you type:

SQL> select file, status, bytes
2 from v$block_change_tracking;

STATUS FILE BYTES
----------- ---------------------------- ---------------
ENABLED /dba/backup/01_mf_yzmrr7.chg 10,000,000