ORACLE 10G BLOCK CHANGE TRACKING INSIDE OUT
(Doc ID 1528510.1)
Last updated on AUGUST 04, 2018
Applies to:Oracle Database - Enterprise Edition - Version 10.1.0.3 and later
Information in this document applies to any platform.
***Checked for relevance on 10-Dec-2015***
About the Author
Alexander Gorbachev, CTO The Pythian Group
Alex joined Pythian in Canada in 2006 and lead a team of DBAs for two year. Alex then moved to Australia as Vice President
Asia Pacific and successfully founded Pythian Australia. In 2009, Alex returned to Canada to take on the Chief Technology
Officer responsibilities. Before joining Pythian, Alex developed and administered Oracle databases and applications for Lukoil,
the Russian petrochemicals giant. Later, he lead design and implementation of high-availability mission-critical databases for
Amadeus, the leading Global distribution system and biggest processor of travel bookings in the world. In 2012 Alex joined the
Board of Directors of IOUG (Independent Oracle User Group).
Alex is a respected figure in the Oracle world and a sought-after leader and speaker at Oracle conferences around the globe. He
also regularly publishes articles on the Pythian blog.
Alex is a member of the OakTable Network and has been awarded the prestigious Oracle ACE Director title by the Oracle Corporation.
Alex is the founder of the Battle Against Any Guess, a movement promoting scientific troubleshooting techniques. During his time in
Australia he also founded Sydney Oracle Meetup, a vibrant local community of passionate Oracle professionals.
Alex has special interest in high availability, performance, storage, cloud computing, big data and manageability. Searching for
right fit between data, business and technology is what keeps him up at night in this age of Big Data and datafication.
Oracle RMAN was able to take incremental backups already in 9i. However, prior to introduction of Oracle 10g block change tracking (BCT), RMAN had to scan the whole datafile to and filter out the blocks that were not changed since base incremental backup and overhead or incremental backup was as high as full backup. Oracle 10g new feature, block change tracking,
minimizes number of blocks RMAN needs to read to a strict minimum. With block change tracking enabled RMAN accesses on disk only blocks that were changed since the latest base incremental backup.
This feature is widely known in the world of Oracle database administrators. However, hardly anything is available on internal implementation of block change tracking. This makes it difficult to evaluate the impact of enabling BCT in Oracle databases and quantify performance overhead.
This paper and presentation try to uncover internals of block change tracking and show which areas of Oracle database are involved, how processes work together, what are hidden limitations and impact of enabling block change tracking.
I cannot provide any guarantee that presented material is absolutely correct. There is no publicly available documentation on change tracking internals (at least, I’m not aware of any) so this paper is based purely on experiments and research plus few hints from more knowledgeable peers that I’m very grateful for. Please take this material carefully and make sure you validate my assumptions before making critical decisions – either with your own tests or request Oracle support to provide more information if your business requires it.
X$ tables provide wealth of information about Oracle internals and block change tracking in particular. The tables starting with X$KRC provide easy access to block change tracking file. Those tables are not simply exposing memory regions. When selecting from X$KRC tables, block change tracking file is accessed behind the scenes.
Extended SQL tracing using event 10046 was used extensively on foreground sessions as well as background processes such as DBWx, LGWR, CKPT, and CTWR. It's worth to mention here that DBWR doesn't post “db file parallel write” wait event with Async IO enabled (Oracle 10.2.0.2 on Linux 32 bit). Here is where strace utility comes handy and let researcher see all system
calls. In addition to strace, lsof utility can show open file descriptors for a given process.
To match content of X$KRC tables to the content of block change tracking file, hex dump or hex editor such as bvi is handy. There are two events reserved for block change tracking tracing – 19774 and 19775. ORADEBUG utility is also very helpful.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!