Does Oracle’s Block Change Tracking File Shrink?

Posted in: Technical Track

Just a quick post to get myself back into blogging mode. Recently in IRC (#oracle on freenode, to be precise), a fresh face asked if the Block Change Tracking file ever shrinks. She had been worrying about the file in her instance continuing to grow. A number of us speculated (non-BAAG!) that perhaps taking an RMAN backup would somehow purge the file of what it was keeping track of, and then the magical Oracle fairies would promptly resize it for us. Needless to say, I was hesitant to take this theory forward with Alex Gorbachev aware of my home address.

After setting up Oracle on a nice VirtualBox image
(more on that in another post) running CentOS 5, I began to do some reading. For some reason, actually reading the official tahiti docs was last on my list. A search of the 10gR2 docs quickly yielded this (from RMAN Incremental Backups): 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. Note, however, the following two factors that may cause the file to be larger than this estimate suggests:

  • To avoid overhead of allocating space as your database grows, the change tracking file size starts at 10MB, and new space is allocated in 10MB incremenents [sic]. 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.

So (if the docs are to be trusted), it would seem that whether or not a backup is taken has no effect on the size of the file, or at least wouldn’t cause it to be shrunk. The size is tied to the amount of data in the database itself, not necessarily the changes in the database waiting to be included in the next incremental RMAN backup.

The documentation does suggest, however, that file size might be affected if (for example) a tablespace and its datafiles were dropped from the database. I’ll save this test for another day!

Interested in working with Don? Schedule a tech call.

About the Author

Oracle database administrator for The Pythian Group, headquartered in Ottawa, Ontario, Canada. I am located in Manitowoc, Wisconsin, USA.OCP 10gR2 DBA

2 Comments. Leave new

Does Oracle’s Block Change Tracking File Shrink? « die Seilerwerks
October 1, 2008 11:10 pm

[…] leave a comment » Reposted from The Pythian Group blog. […]

Alex Gorbachev
October 3, 2008 1:27 am

As I recall (and my memory is not too good for such details…), the space in the BCT file is not allocated immediately but rather “on demand”. I.e. when you take *incremental* backups and space for the new bitmaps is needed. After datafile has been backed up 8 time, the space is allocated and then reused after bitmaps are aged out.

I might have oversimplified it but this is as far as I could see. Again, this might not be true – just the result of my observation.


Leave a Reply

Your email address will not be published. Required fields are marked *