With Oracle 10gr1 Enterprise Edition Oracle introduced Block change tracking (BCT) feature which is used to FAST / Speed up RMAN Incremental Backup. In Oracle 9i we could create incremental backups with level 0 to 4, level 0 backup is a full backup. In Oracle 10g there are still these levels but we only use incremental level 0 and 1 backups with Oracle’s suggested backup strategy.
Before oracle 10g, During RMAN Incremental backup oracle used to read every block in database and compare the SCN in the block with the SCN in the base backup. If the block’s SCN is greater than the SCN in the backup then the block is a candidate for the New Incremental backup. But with this BCT feature oracle instead of reading the entire database blocks it just directly reads only changed blocks results in saving lot of time.
Once BCT is enabled; this new 10g feature records the modified since last backup and stores the log of it in a block change tracking file. During backups RMAN uses the log file to identify the specific blocks that must be backed up. This improves RMAN’s performance as it does not have to scan whole datafiles instead to detect changed blocks.
Logging of changed blocks is performed by the CTWR (change tracking writer) process which is also NEW background process introduced in 10g and responsible for writing data to the BCT file. By default, the Block change tracking file is created as Oracle managed file in DB_CREATE_FILE_DEST specified location.
By default, Oracle will not record block change information. You can Enable or disable the change tracking when the database is open or mounted.
Enable Block Change tracking and set location
SQL> alter system set db_create_file_dest=’location’ SCOPE=BOTH;
SQL> alter database enable block change tracking;
Manually specify location for the block change tracking
SQL>alter database enable block change tracking using file ‘location’;
Disable block change tracking
SQL> alter database disable block change tracking;
Moving Block Change tracking file without Database shutdown:
If your database is 24×7 critical production and you cannot shut down, then follow the below steps. Please note that you must disable change tracking and you will lose the old contents of the change tracking file, if you choose this method.
Step1: Disable the change tracking
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
Step2: Re-enable it at the new location
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘new_location’;
Moving Block Change tracking file with Database shutdown:
Step1: Determine the change tracking file:
SQL> SELECT filename FROM V$BLOCK_CHANGE_TRACKING;
Step2: shutdown and move or copy the tracking file.
SQL> SHUTDOWN IMMEDIATE
$ cp ‘/old_lockation/block_change_tracking.ora’ ‘/new_location/ block_change_tracking.ora’
Step3: Mount the database and rename change tracking file to new location.
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RENAME FILE ‘old_location’ TO ‘new_location’;
Step4: Open the database
SQL> ALTER DATABASE OPEN;
Block change tracking in RAC (Real Applications Clusters) environment, the file must be located on shared storage so that the file is accessible for all the nodes. BCT file is one per db, therefore BCT file is created in location defined by parameter db_create_file_dest
SQL> desc v$block_change_tracking;
Name Null? Type
——————- ————– ——————
Change Tracking Writer (CTWR)
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.
You can view the size of the CTWR dba buffer by looking at v$sgastat
SQL> SELECT * FROM v$sgastat WHERE name like ‘CTWR%’;