What Is Change Block Tracking

Onur ARDAHANLI October 5, 2022 ORACLE

In a system where Incremental Backup is included in the Backup Strategies, it is a very troublesome task to find which blocks have changed by scanning the full datafiles each time.

Instead, knowing the changed blocks and taking a backup just by reading them will provide us an incredible benefit in terms of time.

Oracle provides this solution with BLOCK CHANGE TRACKING.

• Block Change Tracking is enabled in the database, and only the information of the changed blocks is written to a BINARY file. RMAN reads this file during backup and takes backup of those blocks.

• This file is created to the location specified in the DB_CREATE_FILE_DEST parameter.

• Initially, its size is 10MB. When it is full, an increase of 10 MB is provided. The control is completely in Oracle. The DBA does not need any intervention.

• If we want to change the name then we can intervene. Its name can be changed while in MOUNT mode.

• It is turned off in Default.

• The working logic is as follows:

○ When DML operations are performed, the data is brought to the Database Buffer Cache from the physical data blocks on the disk and changed there.

These changed dirty blocks are written to the Change Tracking File with the CTWR process activated when block change tracking is enabled. This file is also read during backup.

BLOCK CHANGE TRACKING is ENABLED as follows.

1. First of all, the status of block change tracking is checked.

[Primary-1 & Standby-1]

SQL> column filename format a10
SQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;

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

SQL> column filename format a10

SQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;

STATUS FILENAME

---------- ----------

DISABLED

2. Enabled.

[Primary-1 & Standby-1]

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Database altered.

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Database altered.

3. The status is checked again.

[Primary-1 & Standby-1]

SQL> set linesize 500
SQL> column filename format a60
SQL> SELECT status, filename, trunc(bytes/1024/1024) FROM V$BLOCK_CHANGE_TRACKING;

STATUS     FILENAME                                                     TRUNC(BYTES/1024/1024)
---------- ------------------------------------------------------------ ----------------------
ENABLED    +DATA/primary/changetracking/ctf.273.932464389                                   11

SQL> set linesize 500

SQL> column filename format a60

SQL> SELECT status, filename, trunc(bytes/1024/1024) FROM V$BLOCK_CHANGE_TRACKING;

STATUS FILENAME TRUNC(BYTES/1024/1024)

---------- ------------------------------------------------------------ ----------------------

ENABLED +DATA/primary/changetracking/ctf.273.932464389 11

4. We can see the return of Block Change Tracking in Incremental Backup as follows.

	SELECT file#, avg(datafile_blocks),
	avg(blocks_read),
	avg(blocks_read/datafile_blocks)
	* 100 AS PCT_READ_FOR_BACKUP,
	avg(blocks)
	FROM v$backup_datafile
	WHERE used_change_tracking = 'YES'
	AND incremental_level > 0
	GROUP BY file#;

SELECT file#, avg(datafile_blocks),

avg(blocks_read),

avg(blocks_read/datafile_blocks)

* 100 AS PCT_READ_FOR_BACKUP,

avg(blocks)

FROM v$backup_datafile

WHERE used_change_tracking = 'YES'

AND incremental_level > 0

GROUP BY file#;

In this output, the PCT_READ_FOR_BACKUP column shows us how many % of the blocks in the datafile will be read during backup.

For example, it provides tremendous performance by reading 7% of datafile #1.

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

What Is Change Block Tracking

About Onur ARDAHANLI

Leave a Reply Cancel reply