Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Monday, May 07, 2012

Enable block change tracking in oracle 11g

The block change tracking (BCT) feature for incremental backups improves incremental backup performance by recording changed blocks in each datafile in a block change tracking file. This file is a small binary file called block change tracking (BCT) file stored in the database area. RMAN tracks changed blocks as redo is generated.
If we enable block change tracking, then RMAN uses the change tracking file(BCT)  to identify changed blocks for an incremental backup, thus avoiding the need to scan every block in the datafile. RMAN only uses block change tracking when the incremental level is greater than 0 because a level 0 incremental backup includes all blocks.
Enable block change tracking (BCT) 
SQL> alter database enable block change tracking  using file '+DBDATA/TESTRAC/bct.dbf' ;
Database altered.

When data blocks change, shadow processes track the changed blocks in a private area of memory at the same time they generate redo . When a commit is issued, the BCT information is copied to a shared area in Large Pool called 'CTWR dba buffer' . At the checkpoint, a new background process, Change Tracking Writer (CTWR) , writes the information from the buffer to the change-tracking file . If contention for space in the CTWR dba buffer occurs, a wait event called , 'Block Change Tracking Buffer Space'  is recorded. Several causes for this wait event are poor I/O performance on the disk where the change-tracking file resides , or the CTWR dba buffer is too small to record the number of concurrent block changes .By default, the CTWR process is disabled because it can introduce some minimal performance overhead on the database. 

You will find below in alert log
Sun May 06 12:47:21 2012
alter database enable block change tracking  using file '+DBDATA/TESTRAC/bct.dbf'
Block change tracking file is current.
Sun May 06 12:47:22 2012
Starting background process CTWR
Sun May 06 12:47:22 2012
CTWR started with pid=42, OS id=8440
Block change tracking service is active.
Completed: alter database enable block change tracking  using file '+DBDATA/TESTRAC/bct.dbf'

The v$block_change_tracking  views contains the name and size of the block change tracking file plus the status of change tracking:

To check whether the block change tracking file is being used or not, use the below command .

SQL> select  file#,  avg(datafile_blocks), avg(blocks_read),  avg(blocks_read/datafile_blocks) * 100 as  "% read for backup"  from v$backup_datafile  where incremental_level > 0  and  used_change_tracking = 'YES'  group by file#   order by file# ;
 To disable/enable Block Change Tracking (BCT)   issue the below command
SQL> alter database disable block change tracking  ;
Note: Disabling will not delete the bct file if you are on ASM/RAC and trying to add the same file will generate the below error.
SQL> alter database enable block change tracking  using file '+DBDATA/TESTRAC/bct.dbf' ;
alter database enable block change tracking  using file '+DBDATA/TESTRAC/bct.dbf'
ERROR at line 1:
ORA-19751: could not create the change tracking file
ORA-19750: change tracking file: '+DBDATA/TESTRAC/bct.dbf'
ORA-17502: ksfdcre:4 Failed to create file +DBDATA/TESTRAC/bct.dbf
ORA-15005: name "TESTRAC/bct.dbf" is already used by an existing alias

when you disable bct following will be written in alert log (on ASM/RAC)
WARNING: Cannot delete file +DBDATA/testrac/bct.dbf
Errors in file D:\APP\INAM\diag\rdbms\testrac\testrac1\trace\testrac1_ora_1916.trc:
ORA-01265: Unable to delete CHANGE TRACKING +DBDATA/testrac/bct.dbf
ORA-15028: ASM file '+DBDATA/testrac/bct.dbf' not dropped; currently being accessed

If you enable bct on ASM/RAC without path like below then OMF bct will be generated.
alter database enable block change tracking  ;

No comments: