Oracle8 Backup and Recovery Guide Release 8.0 A58396-01 |
Library | Product | Contents | Index

# 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
- Creating a Backup Strategy
- Backing Up Online Redo Logs

## 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:

- Perform Backups Frequently and Regularly
- Backup Appropriate Portions of the Database When Making Structural Changes
- Back Up Often-used Tablespaces Frequently
- Keep Older Backups
- Database Backups After Resetlogs
- Export Database Data for Added Protection and Flexibility
- Consider Distributed Database Backups
- Back Up after Performing Unrecoverable/Unlogged Operations
- Test Backup and Recovery Strategies

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 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

After you have opened a database with the RESETLOGS option, Oracle recommends that you immediately perform a whole database backup. If you do not, and a disaster occurs, you will lose all work performed since opening the database (because it requires a restore of all or part of your database).

Note: There is one exception to this rule. See "Recovery After Using the RESETLOGS Option" 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)

Oracle performs these actions so that it can identify which archived redo logs apply to which incarnations of the database.

#### Backup Options While in NOARCHIVELOG mode

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

#### Backup Options While in ARCHIVELOG mode

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

- the amount of time 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.

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

Note: There is one and only one exception to this. See "Recovery After Using the RESETLOGS Option".

If the most important criteria is to restore in case of another failure, then you may elect to take a consistent (closed) database backup.

Time permitting, Oracle recommends that you perform 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 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 back up online logs for the following reasons:

- 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 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 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 because 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 releases prior to Oracle8, DBAs typically backed up 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 occurred during disk maintenance, which requires the database to be backed up, deleted, the disks reconfigured, and the database restored. If you avoided RESETLOGS, you would not have to perform a whole database backup immediately after the database was restored. The backup was required since it was impossible to perform recovery using a backup taken before using RESETLOGS (especially if any errors occurred after resetting the logs).

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

The following scenario illustrates a situation when this is possible.

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

On Friday night the DBA performs the following actions:

- Cleanly shut down the database (SHUTDOWN IMMEDIATE)
- Whole database backup (control files and datafiles)

  Note: At this point you (the DBA) must not reopen the database.

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

On Saturday morning the following occurs:

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

If a hardware error occurs on Saturday night, and requires restoration of 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.

On Saturday night the following takes place:

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

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

- Roll forward

  warning: It is possible to recover after opening a database with the RESETLOGS option only 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 this scenario, 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. 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.

See Also: For additional information see "Database Backups After Using the RESETLOGS Option".

### The Dangers Associated with Backing Up Online Redo Logs

There is no real danger in backing up online logs. The real danger is that you may accidentally restore them while not intending to. There are a number of situations where restoring the online logs would cause very significant problems in the recovery process. Following are two scenarios that illustrate how restoring backed up online logs severely compromises recovery.

#### Unintentional Restore of Online Redo Logs

When a crisis occurs, it is easy to make a simple mistake. DBAs and system administrators frequently encounter dangers is during a database restore. When restoring the whole database, it is possible to also accidentally restore the online redo logs, 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, and ultimately losing the ability to recover valuable transactions.

#### Erroneously Creating Multiple Parallel Redo Log Time Lines

You can unintentionally create multiple parallel redo log timelines for a single instance database. However, you can avoid this mistake if the online logs cannot be restored (the database must be opened with the RESETLOGS option, which effectively creates the new logs, and also a new database incarnation).

If you faces a problem where 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, and avoid opening the database with the RESETLOGS option.

If you do this, the database will generate a log sequence number that was already generated by the database during the previous timeline. 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 back up online logs for the reasons given in these scenarios.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index