Enable BCT (Block Change Tracking)

Hi,

In my previous post (https://querycsm.wordpress.com/2013/01/02/why-rman-daily-incremental-backups-are-taking-so-long/) I’ve talked about investigating the potential problems and stated that one of the improvement initiatives we could go ahead with is ‘Enabling BCT’.

Now we’ll see how to do that and are there any other things we need to perform after enabling.

First I recommend you to go through the document prepared by my favorite author Alexander Gorbachev to get an idea of what’s BCT and how it works(at least go through the first few pages).

To enable BCT we have two ways:

Specifying the BCT file location manually like this

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘/dbfile/bct_file.ora’;

or the other way of doing is

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

but you need to set db_create_file_dest to the location where we want the BCT file to be created before enabling it.

SQL> alter system set db_create_file_dest=’location’ SCOPE=BOTH;

To check if the BCT is enabled or not fire the below query

SQL> select * from v$block_change_tracking;

STATUS        FILENAME                                                                        BYTES

ENABLED   /ora11dbdata2/oracle/NIKU/blk_chg_trckng.dbf    11599872

I did these steps and was not seeing any improvements in the PCT_READ(percentage of blocks read) from the below query.

SQL> select file#, avg(datafile_blocks) blocks,

2  avg(blocks_read) blocks_read,

3  avg(blocks_read/datafile_blocks)*100 pct_read,

avg(blocks) blocks_backed_up,

4    5  (avg(blocks)/avg(datafile_blocks))*100 pct_backed_up

from v$backup_datafile

6    7  where incremental_level = 1

8  group by file#

9  order by file#

So after doing lot of research, I found that we need to take a FULL backup of the Database, after enabling BCT, to leverage it’s benefits. We should do this because when we do a FULL backup, the database will create a baseline bitmap and from there it’ll start updating the bitmap based upon the blocks that are changed.

I’ve later observed that lot of people are facing similar statistics i.e. no improvement after enabling the BCT and this is the answer for that.

BTW, the performance improvement after enabling BCT in our production Database is humongous and we’re now seeing only 2%-3% of the blocks are getting read by RMAN. thanks to BCT!!!

You’re always welcome to any queries and questions.

Regards,

CSM

Advertisements

One Response to Enable BCT (Block Change Tracking)

  1. Pingback: Backup optimization in RMAN | queryCSM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: