3
When to Perform Backups

This chapter offers guidelines and strategies to follow when planning backups, and includes the following topics:

Guidelines for Database Backups

This section describes guidelines that can help you decide when to perform database backups, what parts of a database need backing up, and includes the following topics:

Before you create an Oracle database, you should decide how you plan to protect the database against potential disk failures, and whether or not to enable point-in-time recovery. If such planning is not considered before database creation, database recovery may not be possible if a disk failure damages the datafiles, online redo log files, or control files of a database.

Perform Backups Frequently and Regularly

Frequent and regular whole database or tablespace backups are essential for any recovery scheme. The frequency of backups should be based on the rate or frequency of changes to database data (such as insertions, updates, and deletions of rows in existing tables, and addition of new tables). If a database's data is changed at a high rate, database backup frequency should be proportionally high. Alternatively, if a database is mainly read-only, and updates are issued only infrequently, the database can be backed up less frequently.

Backup Appropriate Portions of the Database When Making Structural Changes

If you make any of the following structural changes, perform a backup of the appropriate portion of your database immediately before and after completing the alteration:

create or drop a tablespace
add or rename (relocate) a datafile in an existing tablespace
add, rename (relocate), or drop an online redo log group or member

Backing up the appropriate portion of the database depends on the archiving mode of the database, as described below:

If a database is operated in ARCHIVELOG mode, only a control file backup (using the ALTER DATABASE command with the BACKUP CONTROLFILE option) is required before and after a structural alteration. However, you can back up other parts of the database.
If the database is operated in NOARCHIVELOG mode, a consistent whole database backup should be taken immediately before and after the modification, including all datafiles and control files.

Back Up Often-used Tablespaces Frequently

If a database is operated in ARCHIVELOG mode, it is acceptable to back up the datafiles of an individual tablespace or even a single datafile. This option is useful if a portion of a database is used more extensively than others, such as the SYSTEM tablespace and tablespaces that contain rollback segments. By taking more frequent backups of the extensively used datafiles of a database, you gather more recent copies of the datafiles. As a result, if a disk failure damages the extensively used datafiles, the more recent backup can restore the damaged files. Only a small number of changes to data need to be applied to roll the restored file forward to the time of the failure, or desired point-in-time recovery, thereby reducing database recovery time.

Back Up after Performing Unrecoverable/Unlogged Operations

If users are creating tables or indexes using the UNRECOVERABLE option, consider taking backups after the objects are created. When tables and indexes are created as UNRECOVERABLE, no redo is logged, and these objects cannot be recovered from existing backups.

Note: If using Recovery Manager, you can take an incremental backup.

See Also: For information about the UNRECOVERABLE option, see the CREATE TABLE...AS SELECT and CREATE INDEX commands in the Oracle8 Server SQL Reference

Keep Older Backups

How long you should keep an older database backup depends on the choices you want for database recovery. If you want to recover to a past time, you need a database backup which completed before that time. For a database operating in NOARCHIVELOG mode, this means a consistent whole database backup. For a database operating in ARCHIVELOG mode, this means a whole database backup which need not be consistent, which completed before that time (the control file should reflect the database's structure at the point-in-time of recovery), and all archived logs necessary to recover the datafiles to the required point-in-time.

For added protection, consider keeping two or more backups (and all archive logs that go with these backups) previous to the current backup. Thus, if your most recent backups are not usable (for example, the tape drive used for backups writes bad backups), you will not lose all of your data.

Warning: After opening the database with the RESETLOGS option, existing backups cannot be used for subsequent recovery beyond the time when the logs were reset. You should therefore shutdown the database and make a consistent whole database backup. Doing so will enable recovery of database changes subsequent to using the RESETLOGS option.

Database Backups After Using the RESETLOGS Option

Once you have opened a database with the RESETLOGS option, Oracle recommends you immediately perform a whole database backup. If this is not done, and a further disaster occurs, requiring a restore of all or part of your database, you will lose all work performed since opening the database.

Note: There is one exception to this rule. See "Recovery After Using the RESETLOGS Option" on page 3-9 for details.

When a database is opened with the RESETLOGS option, Oracle automatically:

creates a new "incarnation" of the database
resets the log sequence number to 1
reformats the online redo log files if they exist (otherwise they are created)

The reason Oracle performs these three actions is to be able to identify which archived redo logs apply to which incarnations of the database.

Backup options in NOARCHIVELOG mode

If you are in NOARCHIVELOG mode, your only option is to make a cold consistent whole database backup.

Backup options in ARCHIVELOG mode

If you are in ARCHIVELOG mode, you can either perform a consistent (that is, closed) whole database backup, or an inconsistent (that is, open) database backup. The option you choose depends on:

the time available before the database absolutely must be available
the importance of data entered after opening the database with the RESETLOGS option

If your most important criteria is getting the database up and available, your only option is to perform open-database backups.

The risk involved in performing an open backup is that if the backups do not complete before you have another media failure, you lose all changes made since opening the database with the RESETLOGS option, as you cannot use a backup taken before opening the database with that option to recover this incarnation of the database.

Note: There is one and only one exception to this. See "Recovery After Using the RESETLOGS Option" on page 3-9

If the most important criteria is to be able to restore in case of another failure, then it may be more prudent to take a consistent (closed) database backup.

If time permits, Oracle recommends a consistent whole database backup.

Export Database Data for Added Protection and Flexibility

Because the Oracle Export utility can selectively export specific objects, you might consider exporting portions or all of a database for supplemental protection and flexibility in a database's backup strategy. Database exports are not a substitute for whole database backups and cannot provide the same complete recovery advantages that the built-in functionality of Oracle offers.

See Also: For more information on the Export utility, see the Oracle8 Server Utilities guide.

Consider Distributed Database Backups

If a database is a node in a distributed database, consider the following guidelines:

All databases in the distributed database system should be operated in the same archiving mode.
If the databases in a distributed database system are operating in ARCHIVELOG mode, backups at each node can be performed autonomously (individually, without time coordination).
If the databases in a distributed database system are operating in NOARCHIVELOG mode, consistent whole database backups must be performed at the same (global) time, to plan for global distributed database recovery. For example, if a database in New York is backed up at midnight EST, the database in San Francisco should be backed up at 9 PM PST.

Test Backup and Recovery Strategies

Test your backup and recovery strategies in a test environment before and after you move to a production system. By doing so, you can test the thoroughness of your strategies and minimize problems before they occur in a real situation.

Performing test recoveries regularly ensures that your archiving, backup, and recovery procedures work. It also helps you stay familiar with recovery procedures, so that you are less likely to make a mistake in a crisis.

Creating a Backup Strategy

Before you create an Oracle database, decide how you plan to protect the database against potential failures. Answer the following questions before developing your backup strategy:

Is it acceptable to lose any data if a disk failure damages some of the files that constitute a database? If it is not acceptable to lose any data, the database must be operated in ARCHIVELOG mode, ideally with a multiplexed online redo log. If it is acceptable to lose a limited amount of data if there is a disk failure, you can operate the database in NOARCHIVELOG mode and avoid the extra work required to archive filled online redo log files.
Will you ever need to recover to arbitrary past points in time? If you need to recover to a past point in time to correct an erroneous operational or programmatic change to the database, be sure to run in ARCHIVELOG mode and perform control file backups whenever making structural changes. Recovery to a past point in time is facilitated by having a backup control file that reflects the database structure at the desired point-in-time.
Does the database need to be available at all times (twenty-four hours per day, seven days per week)? If so, do not operate the database in NOARCHIVELOG mode because the required whole database backups, taken while the database is shutdown, cannot be made frequently, if at all. Therefore, high-availability databases always operate in ARCHIVELOG mode to take advantage of open datafile backups.

Backup Strategies in NOARCHIVELOG Mode

If a database is operated in NOARCHIVELOG mode, filled groups of online redo log files are not archived. Therefore, the only protection against a disk failure is the most recent whole backup of the database.

Plan to take whole database backups regularly, according to the amount of work that you can afford to lose. For example, if you can afford to lose the amount of work accomplished in one week, make a whole database, closed backup once per week. If you can afford to lose only a day's work, make a whole database, closed backup every day. For large databases with a high amount of activity, it is usually unacceptable to lose work. Therefore, the database should be operated in ARCHIVELOG mode, and the appropriate backup strategies should be used.

Whenever you alter the physical structure of a database operating in NOARCHIVELOG mode, immediately take a consistent whole database backup. A whole database backup fully reflects the new structure of the database.

Backup Strategies in ARCHIVELOG Mode

If a database is operating in ARCHIVELOG mode, filled groups of online redo log files are being archived. Therefore, the archived redo log coupled with the online redo log and datafile backups can protect the database from a disk failure, providing for complete recovery from a disk failure to the instant that the failure occurred (or, to the desired past point-in-time). Following are common backup strategies for a database operating in ARCHIVELOG mode:

When the database is initially created, perform a whole database, closed backup of the entire database. This initial whole database backup is the foundation of your backups because it provides backups of all datafiles and the control file of the associated database.

Note: When you perform this initial whole database backup, make sure that the database is in ARCHIVELOG mode first. Otherwise, the backed up control files will contain the NOARCHIVELOG mode setting.

Subsequent whole database backups are not required, and if a database must remain open at all times, whole database, closed backups are not feasible. Instead, you can take open database or tablespace backups to keep your database backups up-to-date.
Take open or closed datafile backups to update backed up information for the database (supplementing the initial whole database backup). In particular, the datafiles of extensively used tablespaces should be backed up frequently to reduce database recovery time, should recovery ever be required. If a more recent datafile backup restores a damaged datafile, less redo (or incremental backups) need to be applied to the restored datafile to roll it forward to the time of the failure.

Whether you should take open or closed datafile backups depends on the availability requirements of the data. Open datafile backups are the only choice if the data being backed up must always be available.

You can also use a datafile copy, taken while the database is open and the tablespace is online, to restore datafiles. However, the data in the restored datafiles is inconsistent. Therefore, the appropriate redo log files (online and archived) must be reapplied to these restored datafiles to make the data consistent and bring it forward to the specified point in time.

Every time you make a structural change to the database, take a control file backup. If operating in ARCHIVELOG mode and the database is open, use either Recovery Manager or the ALTER DATABASE command with the BACKUP CONTROLFILE option.

Note: After backing up the control file, apply redo to it up until the point when the datafile was added. Then issue an ALTER DATABASE CREATE DATAFILE statement, and continue with recovery. Do not use operating system utilities to back up the control file in ARCHIVELOG mode, unless you are performing a closed backup.

Backing Up Online Redo Logs

You should never need to backup online logs. The reasons are as follows:

The way you protect the online logs against media failure is by multiplexing them (having multiple log members per group, on different disks and different disk controllers).
If your database is in ARCHIVELOG mode, the logs are being backed up when they are archived.
If your database is in NOARCHIVELOG mode, the only type of backups that should be performed are closed, consistent, whole database backups. To make the backup consistent, the database must be closed by either a shutdown immediate, shutdown transactional, or a shutdown normal. The files in this type of backup are all consistent, and do not need recovery, hence the online logs are not needed.

If a database crashes, and all multiplexed copies of the online redo log are lost, then:

If your database is in ARCHIVELOG mode, you should restore the whole database and recover the database up until the last archived log. You must then open the database with the RESETLOGS option.

Having an old backup of online logs in this situation is of no use at all, because the information in the online logs will not be needed for recovery (in fact, the information in the online log files already exists in an archived log).

If your database is in NOARCHIVELOG mode, your backup method should be to perform consistent whole database backups. The database can then be opened with the RESETLOGS option; no recovery is required, as the database is already consistent.

Note: Recovery Manager will not backup a NOARCHIVELOG mode database unless it has been shutdown cleanly.

Recovery After Using the RESETLOGS Option

In earlier releases of Oracle7, DBAs backed up their online logs when performing cold consistent backups, to avoid opening the database with the RESETLOGS option, if they were planning to restore immediately. A classic example of this is performing disk maintenance which requires the database to be backed up, deleted, the disks reconfigured, and the database restored. The reason for avoiding RESETLOGS was to avoid having to perform a whole database backup immediately after the database was restored; the backup was required since it was impossible to use a backup taken before using RESETLOGS to recover from, if any errors occurred after resetting the logs.

This is no longer the case. There is one situation that if you have a consistent backup of the database, taken immediately before you open the database with the RESETLOGS option, and a control file which is valid after you open the database with RESETLOGS, you can still use this backup to roll forward from.

To illustrate when this is possible, an example of this situation follows:

The aim is to perform hardware striping reconfiguration, which requires the database files to be backed up and deleted, the hardware reconfigured, then the database restored.

On Friday night the following actions take place:

Cleanly shut down the database (SHUTDOWN IMMEDIATE)
Perform whole database backup (control files and datafiles)

Note: You must not reopen the database

Perform O/S maintenance
Restore database and control files
STARTUP MOUNT DATABASE
OPEN DATABASE RESETLOGS

Saturday morning:

Scheduled batch jobs run, generating a lot of records in the redo log

If on Saturday night a hardware error occurs which requires restoring the whole database, it is possible to restore the backup taken immediately before opening the database with the RESETLOGS option, and to roll forward using the logs produced on Saturday.

Saturday night:

SHUTDOWN ABORT INSTANCE (if it still exists)
Restore all damaged files from backup on Friday night

Note: If you have the current control file, do not restore it; otherwise you must restore a control file which was valid after opening the database with RESETLOGS.

Roll forward

Warning: It is only possible to recover after opening a database with the RESETLOGS option if you have:

A consistent backup taken immediately before opening the database, that is, you must not have opened the database subsequent to the backup and before opening it with the RESETLOGS option.
A control file that was valid after opening the database with the RESETLOGS option.

In the example above, if the DBA had opened the database after the Friday night backup and before opening the database with RESETLOGS, or did not have a control file from after opening the database, the DBA would not be able to use the Friday night backup to roll forward from. The DBA must have a backup after opening the database with the RESETLOGS option in order to be able to recover.

It is always good practice to perform a complete backup of your database after opening a database with the RESETLOGS option.

For additional information see "Database Backups After Using the RESETLOGS Option" on page 3-4.

The Danger in Backing Up Online Redo Logs

There is no danger in backing up online logs. The danger is in accidentally restoring them, without fully intending the consequences. There are a number of situations where restoring the online logs would cause quite significant detriment to the recovery process. Below are two scenarios in which restoring backed up online logs severely compromises recovery.

Unintentional Restore of Online Redo Logs

When a crisis is in progress, it is easy to make a simple mistake. One of the dangers which DBAs and System Administrators frequently encounter is during database restore. When restoring the whole database, it is possible that the online logs are also accidentally restored, thus overwriting the current logs with the older (and useless) backups. This action forces the DBA to perform an incomplete recovery, when the intention was to perform a complete recovery, thus losing the ability to recover valuable transactions.

Erroneously Creating Multiple Parallel Redo Log Time Lines

It is possible to unintentionally create multiple parallel redo log time lines for a single instance database. This possibility is avoided if the online logs can not be restored, as the database must be opened with the RESETLOGS option, which effectively creates the new logs, and also a new database incarnation.

If your site faces a problem which determines that the best course of action is to restore the database from a consistent backup (and not perform any recovery) you may think it is safe to restore the online logs, to avoid opening the database with the RESETLOGS option.

If this is done, the database will generate a log sequence number which was already generated by the database at the previous time line. If you then face another disaster and need to restore from this backup and roll forward, it would be difficult to identify which log sequence number is actually the correct one to apply. In this example, if the logs had been reset, a new incarnation of the database would be created. Any logs created by this new incarnation could only be applied to this incarnation.

Note: Recovery Manager and EBU do not backup online logs for the above reasons.

3 When to Perform Backups