Oracle7 Server Administrator's Guide
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 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 full 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 online datafile backups.
Backup Strategies in NOARCHIVELOG Mode
If a database is operated in NOARCHIVELOG mode, filled groups of online redo log files are not being archived. Therefore, the only protection against a disk failure is the most recent full backup of the database.
Plan to take full 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 full offline backup once per week. If you can afford to lose only a day's work, make a full offline 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 full database backup. An immediate full backup protects the new structure of the database not reflected in the previous full backup.
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 full offline backup of the entire database. This initial full backup is the foundation of your backups because it provides copies of all datafiles and the control file of the associated database.
Note: When you perform this initial full backup, make sure that the database is in ARCHIVELOG mode first. Otherwise, the backed up database files will contain the NOARCHIVELOG mode setting.
- Subsequent full backups are not required, and if a database must remain open at all times, full offline backups are not feasible. Instead, you can take partial online backups to update the backups of a database.
- Take online or offline datafile backups to update backed up information for the database (supplementing the full, initial 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, fewer archived redo logs need to be applied to the restored datafile to roll it forward to the time of the failure.
Whether you should take online or offline datafile backups depends on the availability requirements of the data. Online datafile backups are the only choice if the data being backed up must always be available.
- Every time you make a structural change to the database, take a control file backup, using the ALTER DATABASE command with the BACKUP CONTROLFILE option.
Warning: If the control file does not contain the name of a datafile, and you have no backup of that datafile, you cannot recover the file if it is lost. Also, do not use operating system utilities to backup the control file in ARCHIVELOG mode, unless you are performing a full, offline backup.
- If you want a copy of the current online log, then archive it. Archiving means the online log will no longer be the current log. If you end up copying a current online log, the copy will appear as the end of the redo thread. However, additional redo may have been generated in the thread. If you ever attempt to execute recovery supplying the redo log copy, recovery will erroneously detect the end of the redo thread and prematurely terminate, possibly corrupting the database.