Block Change Tracking

Block changing tracking improves the performance of incremental backups by recording changed blocks in the block change tracking file. During an incremental backup, instead of scanning all data blocks to identify which blocks have changed, RMAN uses this file to identify the changed blocks that need to be backed up.

You can enable block change tracking when the database is either open or mounted. This section assumes that you intend to create the block change tracking file as an Oracle Managed File in the database area, which is where the database maintains active database files such as data files, control files, and online redo log files.

To determine if block change tracking is enabled, check the STATUS and FILENAME columns in the V$BLOCK_CHANGE_TRACKING view, using the following statement from the SQL or RMAN prompt:

SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;

From Oracle 10g, the background process Block Change Tracking Writer (CTWR) will do the job of writing modified block details to block change tracking file.

 

In a Real Applications Clusters (RAC) environment, the change tracking file must be located on shared storage accessible from all nodes in the cluster.

 

Enabling and Disabling Oracle Change Tracking file

 

We can enable or disable change tracking when the database is either open or mounted. To alter the change tracking setting, we must use SQL*Plus to connect to the target database with administrator privileges.

 

To store the change tracking file in the database area, set DB_CREATE_FILE_DEST in the target database. Then issue the following SQL statement to enable change tracking:

 

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;  

We can also create the change tracking file in a desired location, using the following SQL statement:

 

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u02/rman/rman_change_track.f';

The REUSE option tells Oracle to overwrite any existing file with the specified name.

 

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u02/rman/rman_change_track.f' REUSE;

To disable change tracking, use this SQL statement:

 

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;  

If the change tracking file was stored in the database area, then it will be deleted when we disable change tracking.

 

Checking Whether Change Tracking is enabled

 

From SQL*Plus, we can query V$BLOCK_CHANGE_TRACKING to determine whether change tracking is enabled or not. 

 

SQL> select status from V$BLOCK_CHANGE_TRACKING;

        ENABLED   => block change tracking is enabled.

        DISABLED  => block change tracking is disabled.

 

Query V$BLOCK_CHANGE_TRACKING to display the filename.

 

SQL> select filename from V$BLOCK_CHANGE_TRACKING;

 

Moving the Change Tracking File in Oracle

 

If you need to move the change tracking file, the ALTER DATABASE RENAME FILE command updates the control file to refer to the new location.

1. If necessary, determine the current name of the change tracking file:

SQL> SELECT filename FROM V$BLOCK_CHANGE_TRACKING;

        /u01/app/rman/rman_change_track.f

2. Shutdown the database.

SQL> SHUTDOWN IMMEDIATE

3. Using host operating system commands, move the change tracking file to its new location.

$ mv /u02/rman/rman_change_track.f /u01/app/rman/rman_change_track.f

4. Mount the database and move the change tracking file to a location that has more space. For example:

SQL> STARTUP MOUNT

SQL> ALTER DATABASE RENAME FILE '/u01/app/rman/rman_change_track.f' TO '/u01/app/rman/rman_change_track.f';

5. Open the database.

SQL> ALTER DATABASE OPEN;

 

SQL> SELECT filename FROM V$BLOCK_CHANGE_TRACKING;

        /u01/app/rman/rman_change_track.f

If you cannot shutdown the database, then you must disable change tracking and re-enable it, at the new location:

 

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u01/app/rman/rman_change_track.f';

 

If you choose this method, you will lose the contents of the change tracking file. Until the next time you complete a level 0 incremental backup, RMAN will have to scan the entire file.

 

Estimating Size of the Change Tracking File on Disk

The size of the change tracking file is proportional to the size of the database and the number of enabled threads of redo. The size is not related to the frequency of updates to the database.

 

Typically, the space required for block change tracking is approximately 1/30,000 the size of the data blocks to be tracked. The following two factors that may cause the file to be larger than this estimate suggests:

·                     To avoid overhead of allocating space as database grows, the change tracking file size starts at 10MB, and new space is allocated in 10MB increments. Thus, for any database up to approximately 300GB the file size is no smaller than 10MB, for up to approximately 600GB the file size is no smaller than 20MB, and so on.

·                     For each datafile, a minimum of 320K of space is allocated in the change tracking file, regardless of the size of the file. Thus, if you have a large number of relatively small datafiles, the change tracking file is larger than for databases with a smaller number of larger datafiles containing the same data.

SELECT FILE#, INCREMENTAL_LEVEL, COMPLETION_TIME, BLOCKS, DATAFILE_BLOCKS FROM V$BACKUP_DATAFILE WHERE INCREMENTAL_LEVEL > 0 AND BLOCKS / DATAFILE_BLOCKS > .5 ORDER BY COMPLETION_TIME;