Database down! DBA ninjas to the rescue

Protecting yourself with incremental backups

Incremental backups are a good way to keep you protected while minimising the performance impact on the database. They work by backing up only the changes made to the database since the last backup, and they form part of a backup strategy suggested by Oracle. It suggests daily backups for this.

There are two types of incremental backup; level 0, and level 1. Level 0 is a full backup, but it's designed to be added to incrementally. Level 1 backups are simply the deltas from the previous incremental backup.

Start by creating an incremental level 0 datafile copy backup. That gives you the entire database's contents in a separate set of files.

Then, the next day, you'll create a level 1 incremental backup, containing the blocks changed since the first backup. That gives you the ability to recover your database to the point when that backup was made. Now, you'll have two sets of files: the level 0 backup, and the level 1 backup.

From now on, each day, you apply yesterday's incremental backup to the level 0 backup. That will create a level 0 backup file that you can use to restore your database to yesterday's state. Then, you create a new incremental level 1 backup, containing the changes made to the database from yesterday.

That way, if your database goes down, you can apply the latest level 1 backup to the level 0 backup, restoring it to the start of the current day.

Salvation in the REDO logs

That's database restoration covered. That's all well and good, but a restored database isn't necessarily up to date. What about if you business can't afford to lose the data stored since the last incremental backup, and needs you to recover your data to some point after the backup that happened at the beginning of today? Luckily, REDO logs are there to help.

REDO logs store changes that are made to the database. When backing up a database, you can run in ARCHIVELOG mode to back these up, too. When you're performing a hot backup, you are forced to do this.

REDO logs cover the gap between the last incremental backup, and the current point in time. Once you've restored your database, you can use these files to roll forward all changes made to the database. Then, you'll roll back those changes that were requested, but which hadn't been committed to the database when it went down. This recovers the data lost after your restoration. It gets the database to the exact state that it was in when it went down.

Protecting these REDO logs is important. ARCHIVELOG will archive those that have been filled with changes, but multiple copies of online ones should be stored on separate disk spindles.

Make this recovery process faster by using the Fast Recovery Area (FRA). This is a single disk location where Oracle will store and automatically manage all recovery-related files (including RMAN backups).

Tools

There are user-managed options for backing up Oracle data, but RMAN is really your go-to tool here. There are third party tools that can help you to work with this utility, though.

Quest Backup Reporter for Oracle, a component of the Toad tool for Oracle DBAs, increases the visibility of your Oracle backup jobs. It's a read-only reporting tool that allows you to see all of your executed RMAN backups in one screen, along with their execution status. You can look at a backup schedule, and also check your RMAN policy definition, seeing if there are any violations of your backup policies.

Finally, don't forget to validate your backups using BACKUP VALIDATE, which will check to ensure that all of the necessarily files needed during a backup have been copied, and are intact. And perform regular test restorations to separate non-production hardware, so that you can sure the database would actually restore in the event of a disaster.

You can script that test, too - and with all of this in place, you'll be sleeping a lot more soundly at night. ®

The Register is running a series of Oracle DBA workshop articles in association with Dell Software.