[Laskey99] Chapter 4. Preventing Data Loss

Chapter 4.

Once the database is operational, your most important responsibility is to ensure the availability of the data in that database. You need to develop a backup and recovery plan in advance. This chapter describes the various situations you may encounter and introduces the basic tools you can use to recover your data. Chapter 16, gives the detailed syntax for using the tools described in this section.

4.1. Types of Backups

The time to plan for a recovery is before you need to recover data. There are several ways in which your data can be backed up, and each way has its limitations. No two installations will have the same requirements, nor will they necessarily back up data in the same way. In Table 4.1, we list the most common scenarios and show the appropriate backup strategies.

Table 4.1. Common Backup and Recovery Scenarios

If You Need to...	Use This Mechanism
Recover up to the point of failure	Hot backups Cold backups with archivelog mode
Recover individual tables	Exports Tablespace point-in-time recovery
Recover on a different operating system	Exports
Create a point-in-time view of the data for historical records	Exports Cold backups
Recover the database to a point prior to a failure	Hot backups Cold backups with or without archivelog mode Exports
Not have downtime for backups	Hot backups Exports

4.1.1. Physical Backups

A physical backup of the database is one in which the datafiles are backed up. Most installations copy the datafiles to tape, but copies can be made to disk or to another computer system. Unless otherwise noted, we'll assume in our discussion that the datafiles have been copied to tape.

4.1.1.1. Benefits

With physical backups, the recovery process is usually faster than with logical backups, since you only need to restore files from the tape backup.

4.1.1.2. Limitations

You can usually only restore to a computer system that's running the same operating system as the one from which the backups were made. There may also be limitations on operating system and Oracle release levels.

The backup process doesn't take into account the database objects that are stored within the database. The granularity of the recovery in this case is the entire database.

Some techniques allow individual objects to be recovered from a physical backup (these are discussed in later sections). However, these techniques are time consuming, require additional disk space, and need to be practiced before they are put into operation.

4.1.2. Logical Backups

A logical backup is one in which individual database objects are backed up. The information is stored in a way that allows objects to be created and data inserted into tables.

4.1.2.1. Benefits

With logical backups, individual objects can be recovered separately. You can usually restore to different computer systems, operating systems, and release levels.

4.1.2.2. Limitations

With logical backups, as objects are being created and rows inserted through the SQL buffer, indexes have to be rebuilt as well. Since individual tables can be recreated through these backups, you need to be careful to ensure that referential integrity rules are maintained.

4.1.3. Archivelog Mode

Archivelog is an optional mode for the database. You can set it with the INIT.ORA parameter ARCHIVELOG or in the ALTER DATABASE statement. In this mode, the ARCH background process copies the contents of each redo log to an archive file at the time of a log switch. Be sure to allocate enough redo logs so that ARCH has sufficient time to copy the redo log to the archive file before the redo log is reused. Archivelog mode comes at a cost of increased overhead. The ARCH process reads from the most recently used redo log at the same time that the LGWR process is writing to the current redo log.

Some Oracle manuals use the term media recovery enabled as a synonym for archivelog mode.

4.1.3.1. Benefits

The contents of the archive files are used in conjunction with a physical backup to roll the database forward during recovery.

4.1.3.2. Limitations

If the database is in archivelog mode and the ARCH process cannot copy the redo logs to the archive files, the database will hang until ARCH is successful.

Many installations use a user-written daemon process to track the generation of archive files. As they are created, they are moved off the computer to secondary storage. This helps ensure availability of the archive files if they are needed for database recovery after a hardware failure.

4.1.4. Incremental Backups

An incremental backup makes a copy only of what has changed since the last backup. With physical backups, this means either the datafiles or the individual data blocks that have changed. With logical backups, this means the objects that have been changed.

4.1.4.1. Benefits

With incremental backups, the amount of tape or disk storage is less than that needed for a full backup.

4.1.4.2. Limitations

There are several limitations on performing incremental backups:

• If you have performed a full backup followed by one or more incremental backups, you will have to restore using all of the backups.

• Depending upon the specific backup utility you use, there may be restrictions on the order in which backups are restored.

• If you are missing a backup, you may have holes or inconsistencies in your data.