Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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]1 2 3 4 5 6 | SQL> column filename format a10 SQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING; STATUS FILENAME ---------- ---------- DISABLED |
1 2 3 | SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING; Database altered. |
1 2 3 4 5 6 7 | 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 |
1 2 3 4 5 6 7 8 9 | 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.
