Oracle Scratchpad

November 15, 2006

Change Tracking

Filed under: Infrastructure — Jonathan Lewis @ 8:55 pm BST Nov 15,2006

Posting from the UKOUG (UK Oracle User Group) conference. The most interesting detail I picked up today came from a presentation by Alex Gorbachev of the Pythian Group, a company that specialises in remote DBA operations – and probably does it very well judging from this blog entry from Doug Burns

I’ll try to find a public link to the presentation, but the critcal point is this. The Change Tracking log (produced by CTWR) is a bitmap representing the entire database – (probably) using one bit for each 32KB chunk of the database. The bitmap has an associated SCN, which is the SCN as at the last backup, and a bit gets set in the bitmap to show that the corresponding chunk (32KB) of the database has changed since that SCN.

There are 7 versions of the bitmap; and each time you take a backup of the database using rman, you starting using the next available bitmap and stamp it with the latest backup SCN. So if you take 8 backups, of the database the first bitmap has to be overwritten.

But the point of the Change Tracking log is that rman can read the backup to determine which blocks need to backed up, and avoid scanning entire data files to check the SCN (System Change Number) of every block; so if a bitmap simply disappears, rman can no longer identify all of the blocks that have changed since that early backup.

This doesn’t really seem to matter – until you realise that rman allows you to implement different “levels” of backups, and then choose to do incremental or cumulative backups. The upshot of this limit of seven bitmaps is that if you do eight incremental backups in a row, then the next cumulative backup will have lost some information about the last cumulative backup – so you may find your backup process scanning entire files when you thought it was going to use the change tracking log to do some high precision block selection.

Moral: think very carefully about how you sequence the different backup options through rman. And thank you, Alex, of the Pythian Group, for investigating what goes into the log.

Footnote: As Alex made clear during his presentation – this investigation is an ongoing activity. There may be extra features and details that will be revealed with further work. But I’d rather be over-cautious now, rather than have a nasty surprise when I least expect it.

14 Comments »

  1. Jonathan,

    Since 10g you can only have an incremental or cummulative level of 0 (full backup) and 1, no further levels are allowed anymore.

    http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14194/rcmsynta009.htm#i1009966

    This would mean that the problem appears only with a cummulative backup.

    Regards,

    Freek

    Comment by Freek — November 16, 2006 @ 7:10 am BST Nov 16,2006 | Reply

  2. Jonathan, thanks for warm response and for chairing my session.

    Freek, thank you for the reference to the doc. Interesting that levels higher than 1 are still working in 10.2.0.2 – up to level 4 if I am not mistaken. It’s either a documentation bug or Oracle is depreciating levels 2-4 incremental backups.

    Comment by Alex Gorbachev — November 16, 2006 @ 2:07 pm BST Nov 16,2006 | Reply

  3. Alex,

    I did a quick test on 10.1.0.4 (according to the documentation the levels 2,3 and 4 are also not available in 10gR1) and it seems indeed that you still can use the deeper levels.
    For this test the block change tracking was not enabled, I will do the same test later on with the block change tracking enabled.
    The documentation seems to be rather consistent in limiting the incremental levels to 0 and 1. Here is a link from the 10gR1 documentation talking about the use of incremental backups: http://download-uk.oracle.com/docs/cd/B14117_01/server.101/b10735/bkup.htm#1032060

    Regards,

    Freek

    Comment by Freek — November 17, 2006 @ 8:05 am BST Nov 17,2006 | Reply

  4. “I’ll try to find a public link to the presentation…”
    I uploaded the presentation and posted the link here if someone interested.
    10g Block Change Tracking – http://www.pythian.com/documents/UKOUG06-10gBCT.ppt
    Alex

    Comment by Alex Gorbachev — November 21, 2006 @ 5:40 pm BST Nov 21,2006 | Reply

  5. Jonathan,

    I could not find any information on the mechanism used for the CTWR to capture the block id’s it needs to write to the change tracking file. Does the implementation use a post by the LGWR to CTWR of the blocks being changed or does it use a log miner/streams mechanism to capture the block id’s?

    Regards,

    Mark

    Comment by Mark Burgess — November 22, 2006 @ 1:29 am BST Nov 22,2006 | Reply

  6. Mark, I think this is covered in Alex’ presentation. I think his current conclusions are that there is an in-memory bitmap of the database, protected by a couple of latches, that gets updated by DBWR as part of the process of writing blocks to disc. CTWR picks up the bitmap and dumps it to disc – hence the need for latch protection.

    Comment by Jonathan Lewis — November 22, 2006 @ 7:07 am BST Nov 22,2006 | Reply

  7. Very interesting – looking at one of our 10g databases that sees two incremental’s a day and one weekly full backup.

    truely enough, after day 3, backup duration increases more than 2x – and it stays like that until the next full backup.

    a dba

    Comment by dba — November 23, 2006 @ 1:32 pm BST Nov 23,2006 | Reply

  8. Mark,
    I figured from Tanel how debug latch activity and plan to experiment with it when I have time. Just to confirm.
    a dba,
    Thanks for sharing you experience. Sounds like a good real life example.

    Comment by Alex Gorbachev — November 24, 2006 @ 1:48 am BST Nov 24,2006 | Reply

  9. My guess about change tracking state change latch was incorrect. Here is a small note about it.

    Comment by Alex Gorbachev — November 25, 2006 @ 11:14 pm BST Nov 25,2006 | Reply

  10. Alex, Jonathan

    Haven’t tested this out but the number of bitmaps to be stored for a datafile could be related to _bct_bitmaps_per_file (the default value is 8 )

    Comment by Fairlie Rego — November 27, 2006 @ 1:41 am BST Nov 27,2006 | Reply

  11. Thanks Fairlie. Indeed, 7 is not fixed anywhere in the structure. I was looking to find this parameter but probably didn’t try hard enough. I’ll test it next days to be sure it controls number of versions.

    Comment by Alex Gorbachev — November 27, 2006 @ 4:03 am BST Nov 27,2006 | Reply

  12. I see that Alex has done some further work on the latch activity relating to change tracking, reported at http://www.pythian.com/blogs/317/change-tracking-state-change-latch

    Comment by Jonathan Lewis — December 15, 2006 @ 2:04 pm BST Dec 15,2006 | Reply

  13. Here’s an important alert from Oracle for users of 11.1.0.7:

    note 756435.1 Block change tracking on physical standby can cause incorrect backups.

    Make sure you read the note to cross-check my interpretation – but the gist of it is that if you are using a standby database as the source of your backups, and use block change tracking on the standby to allow fast incremental backups, then those backups can be incorrect.

    On the standby server execute sqlplus and run:

    SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
    

    Comment by Jonathan Lewis — February 3, 2010 @ 11:34 am BST Feb 3,2010 | Reply

  14. [...] really. I’ll use, as an example, another hero of mine Jonathan Lewis (who as a side note said something nice about Pythian recently). You might remember the numerous evidentiary battles championed by Jonathan? Not to [...]

    Pingback by The Fine Art of Baloney Detection, in honour of Dr. Carl Sagan | The Pythian Blog — June 27, 2011 @ 1:06 pm BST Jun 27,2011 | Reply


RSS feed for comments on this post. TrackBack URI

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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,266 other followers