Disaster Recovery optimize

Enable Change Tracking in Oracle for faster incremental backups

DBAs can do incremental backups made via Recovery Manager significantly faster by enabling a new Oracle 10g feature called Change Tracking. Bob Watkins explains how to enable the feature.

Recovery Manager (RMAN) was introduced in Oracle 8.0 and enabled true incremental backups for the first time. Prior to RMAN, database exports could be taken incrementally, but if any block in a table was changed, the entire table was written. RMAN incremental backups write only the changed blocks from a given datafile, thus saving space.

Prior to version 10g, however, incremental backups required a scan of an entire datafile to detect which blocks in it had changed. For very large databases, this made the time required for incrementals not much better than for full backups, even though space was saved.

Weekly Oracle tips in your inbox
TechRepublic's free Oracle newsletter, delivered each Wednesday, covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more.
Automatically sign up today!

Change Tracking, sometimes called Block Change Tracking, provides a solution by recording to a separate Change Tracking file which blocks have changed. The changes are written in real time by a background process.

The first level 0 incremental backup still requires a complete scan. Thereafter, the Change Tracking file is consulted during backups and the required blocks are accessed directly.

To create the Change Tracking file and enable Change Tracking, make sure the database is either open or mounted. Then issue an ALTER DATABASE command:

   USING FILE <pathname>;

where <pathname> is the operating system pathname to the location where you want the Change Tracking file to be stored. (If you have Oracle Managed Files enabled, the USING clause is optional, and the file will be created in the directory pointed to by DB_CREATE_FILE_DEST.)

Regardless of how the path is determined, the file begins at 10MB in size and grows by 10MB if it needs to be extended.

There is a performance impact to enabling Change Tracking, so unless you are using RMAN's incremental backup feature, it is best left disabled (the default). But if you are doing incremental backups, the time lost recording changes will be more than made up in faster backups.

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.