Thursday, December 1, 2011

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 'C:\app\neerajs\admin\noida\bct.dbf' ;

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. 

The v$block_change_tracking  views contains the name and size of the block change tracking file plus the status of change tracking: We can check by the below command :  

SQL> select filename, status, bytes from v$block_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 Block Change Tracking (BCT)   issue the below command
SQL> alter database disable block change tracking  ;



Enjoy    :-) 


3 comments:

Abhijit Moharil said...

Neeraj,

We enabled block change tracking and our database shutdown with memeory errors after 3 days. Anything else we need to consider changing?

Thanks

Abhijit Moharil said...

Neeraj,

Our database came down with memeory dump after we enabled block change tracking to expedite the INC backup in 3 days. backup did finish in ~50 mins instead of 5 hours but it memoery dump.
We have large pool size of 1GB.

Any thing we need to change so db won't come down?

Thanks for your help!

NEERAJ VISHEN said...

Hi Abhijit ...

It is very tough to guess what leads your db down. Check your Alert logfile and trace file . This may give you some clue .

Few possibilities of db down may be

1.) You don't have sufficient memory .

2.) It may be bug .


Thanks ,