Incremental Backups of Oracle Databases

Overview

Databases are getting larger by the year, by the month, by the week. For the most part, technology is keeping pace with this growth. Sophisticated search engines and parallel processing provide adequate performance for larger databases. Faster, larger and less expensive disk devices ease some of the management problems associated with administering so much storage.

One area lagging in support for very large databases is backup and recovery technology. Even with the increases in tape-drive speed, the problem of writing over a hundred gigabytes of data as a backup is a daunting prospect. In fact, some data warehouses choose to go without backups simply because of the difficulty of managing backups of that size.

One solution to this problem is to be smarter about the data that you back up. If only 2%
of a database changes on a daily basis, then writing the full database each time is terribly expensive in terms of time and media relative to the amount of changed data you actually need. A good solution in this case is to take an initial full backup and then write only the changed data on subsequent backups. The concept of writing only changed data is called an incremental backup.

Incremental backups are nothing new to database administration; they have been used for years in mainframe databases, which have been known to handle terabytes of data. Informix provides an incremental backup with its database engine, and BMC Software has provided one for Sybase that has been widely used and accepted in that community for three years.

The SQL-BackTrack™ for Oracle product from BMC Software provides an incremental backup for Oracle databases. The purpose of this white paper is to describe how that backup works, its benefits and how you can fit it into your backup and recovery plans.

What Is an Incremental Backup?

The first question to ask about a product's incremental backup is: What is the unit of change? In general, the smaller the level of granularity, the more useful incremental backups will be; the larger the level of granularity, the less useful incremental backups will be.

For example, if a product backs up the entire physical file when any data in the file has changed, the product's incremental backups will not be very useful. Because chances are high that some amount of data has changed in many or all of the database files, the product will probably back up most or all of the database each time.

Oracle provides incremental exports with its exp program. Incremental exports write all tables that have changed since the last export. Although the table represents a smaller level of granularity than the database file, an incremental export can still write large amounts of unchanged data. In addition, exports are always slower than physical backups.

SQL-BackTrack for Oracle physical incremental backups write only the Oracle data blocks that have changed since the last backup. This is a very fine level of granularity, defined by the block size determined by the Oracle DBA, typically around 2 kilobytes.

SQL-BackTrack for Oracle uses its own proprietary algorithms to read data from the disk on which the database is stored (whether in a file system file or on a raw partition). It examines, and then writes, the data to the backup file. At the same time, it records characteristics of the backup file, block by block.

When SQL-BackTrack performs an incremental backup, it again reads the data from the device, then compares its characteristics, block by block, against those recorded for the last backup. Data blocks that have not changed are not written to the backup file. Only data blocks that have changed are written to the backup file.

On recovery, SQL-BackTrack recovers the full backup, then swaps in the changed blocks from the incremental backups. This is transparent to the user who is recovering the database.

SQL-BackTrack does not require any special action to recover incremental backups.

Incremental backups can be combined with other features. For example, the user can perform an incremental backup on the entire database, or on just selected database files. Incremental backups can be used with either online or offline backups, with or without compression, with or without encryption.

BMC Software has performed extensive testing with incremental backups and built-in safety checks to ensure that changed data is always backed up. BMC Software has proved its incremental backup methods with other databases.

What Are the Benefits of Incremental Backups?

Why use incremental backups in an Oracle database backup/recovery scheme? Performance is usually the driving reason; backup performance can be improved considerably using incremental backups. Reducing the amount of backup data to manage reduces media and operations costs, as well as network traffic.

Backup performance

Writing incremental backups, although it does incur some processing time, will save considerably on elapsed backup time by writing less data. Tests performed by SoluTech Consulting indicate that using incremental backups to tape should be between three and four-and-a-half times faster than performing full backups to tape with tar, and one and a half times faster than a full SQL-BackTrack backup. The actual backup performance depends on many things: CPU speed, backup device speed and the amount of data changed in the database.

Recovery performance

Recovery can be divided into the components of restoring the necessary files and recovering the database (which includes executing redo logs). The use of incremental backups may make the file restoration longer because you need to read both the full and incremental backup from the backup media. However, incremental backups taken frequently will reduce the amount of redo logs to play, resulting in a faster database recovery. So the actual performance impact will depend on how frequently you are performing incremental backups.

Reduced media/operations costs

Incremental backups, by writing less data, save backup media and the operational costs involved with maintaining and changing backup media.

Reduced risks for backup media failure

Magnetic tapes are not an infallible storage media. When you rely on many tapes full of archived redo logs to restore a database, you are vulnerable to a tape failure at any point in the chain. When you run incremental backups instead, you reduce the number of tapes on which you rely.

How Do Incremental Backups
Fit into a Backup Scheme?

Because incremental backups are new to the Oracle community, the question arises of how they fit into a backup/recovery scheme. The answer depends on a number of factors:

Given these general variables, here are a few guidelines:

SQL-BackTrack for Oracle supports the use of true incremental and differential-level backups. True incrementals (invoked by selecting incremental in obacktrack, or using the -incremental option in an obacktrack script) record all changes since the last backup of any kind. Numbered differential levels (invoked with the -level n option to an obacktrack script) back up all changes since the last backup of a lower level. A full backup is the same as level 0.

Using numbered levels, you can implement complex schemes of incremental backups, rolling up intermediate incrementals into broader incrementals, etc. Most customers simply use the true incremental backups.

In recovery, SQL-BackTrack automatically reapplies the appropriate incrementals in the appropriate order; there is no additional burden on the DBA in recovery.

Incremental backups combined with the other performance features of SQL-BackTrack (parallel backup streams, Database Aware compression) provide a high-performance backup capability.

The SQL-BackTrack product's safety and convenience features (guided recovery, dry-run recovery) help assure you of a secure database recovery, whether you use incremental backups or not.

Conclusion

As databases grow in size, both database vendors and third-party developers like BMC Software are developing ways to manage large volumes of data. Incremental backups are one way to write less data on backups without compromising backup coverage. In many cases, incrementals can increase backup coverage by making it easier to take more frequent backups.

Helping You Maintain Advantage

BMC Software Professional Services helps companies maintain their advantage through a comprehensive suite of consulting services and education offerings designed to ensure ongoing business availability of critical applications. Our packaged and custom service offerings maximize product potential, reduce project risk, accelerate the time to value, and improve operations. The Service Assurance Center™ by BMC Software is our premier custom solution, helping customers achieve service-level management. In addition, our Support organization provides the crucial documentation and responsive problem resolution to keep your business on track.