|Oracle8 Backup and Recovery Guide
This chapter offers guidelines and strategies to follow when planning backups, and includes the following topics:
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.
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.
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:
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, 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.
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.
See Also: For information about the UNRECOVERABLE option, see the CREATE TABLE...AS SELECT and CREATE INDEX commands in the Oracle8 SQL Reference.
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.
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.
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).
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:
Oracle performs these actions so that it can identify which archived redo logs apply to which incarnations of the database.
If you are operating in NOARCHIVELOG mode, your only option is to make a cold consistent whole database backup.
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:
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).
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.
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.
If a database is a node in a distributed database, consider the following guidelines:
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.
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:
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.
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:
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.
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.
You should never back up online logs for the following reasons:
If a database crashes, and all multiplexed copies of the online redo log are lost, then:
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).
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:
On Saturday morning the following occurs:
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:
It is possible to recover after opening a database with the RESETLOGS option only if you have:
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".
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.
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.
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.