| Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2) Part Number B14192-02 |
|
|
View PDF |
| Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2) Part Number B14192-02 |
|
|
View PDF |
Your plans for data recovery strategies are the basis of your plans for backup strategy. This discussion describes general guidelines that can help you decide when to perform database backups, which parts of a database you should back up, what tools Oracle provides for those backups, and how to configure your database to improve its robustness and make backup and recovery easier. Of course, the specifics of your strategy must balance the needs of your restore strategy with questions of cost, resources, personnel and other factors.
The set of files needed to recover an Oracle database from the failure of any of its files—a datafile, control file, or online redo log—is called the redundancy set. The redundancy set should contain:
The last backup of the control file and all the datafiles
All archived redo logs generated after the last backup was taken
Duplicates of the current control file and online redo log files, generated by Oracle database multiplexing, operating system mirroring, or both
Copies of configuration files such as the server parameter file, tnsnames.ora, and listener.ora
|
Caution: Do not store the redundancy set on the same disk that contains the datafiles, online logs and control files of the database. Otherwise, the disk becomes a single point of failure for the database. If this disk fails, you lose committed transactions. |
A minimal production-level database thus requires at least two disk drives: one to hold the files in the redundancy set and one to hold the database files. Ideally, separate the redundancy set from the primary files in every way possible: on separate volumes, separate file systems, and separate RAID devices.
The simplest way to manage your redundancy set is to use a flash recovery area, on a separate device from the working set files. However, whether or not you use a flash recovery area, Oracle recommends the following practices:
Multiplex the online redo log files and current control file at the database level. (For instance, configure the database to write its online logs to two or more destinations, so that each write is a separate operation carried out by the database, rather than by operating system-level or hardware-level redundancy.) If you multiplex at the database level, then an I/O failure or lost write should only corrupt one of the copies.
Ideally, the multiplexed files should be on different disks mounted under different disk controllers. The flash recovery area is an excellent location for one copy of these files.
You can also mirror the online redo logs and current control file at the operating system or hardware level, but this is not a substitute for multiplexing at the database level.
If running in ARCHIVELOG mode, archive the redo logs to multiple locations, ideally on different disks. If you are using a flash recovery area, use it as one of the archiving locations.
Use operating system or hardware mirroring for the control file. All copies of the control file multiplexed at the database level must be available at all times, or the instance will crash. If you use operating system or hardware mirroring for your control file, your database can continue to operate even if one copy of the control file mirrored at the operating system level is unavailable due to a disk failure.
Use operating system or hardware mirroring for the primary datafiles if possible, to avoid having to perform media recovery for simple disk failures.
Keep at least one copy of the entire redundancy set—including the most recent backup—on disk. The flash recovery area is the ideal location for the redundancy set.
If the target database is stored on a RAID device, then store the redundancy set on a set of disks that are not in the same RAID device.
If you store the redundancy set on tape, then maintain at least two copies of the data to protect against the risk of tape failure. Also, if you have more than one backup of the same data, then consider keeping backups from different points in time. In this way, if one backup or split mirror was created when the database was corrupted, you may still have a backup from a time when the database was not corrupted.
It is recommended that you take advantage of the flash recovery area to store as many backup and recovery-related files as as possible, including disk backups and archived redo logs.
Some features of Oracle database backup and recovery, such as Oracle Flashback Database and guaranteed restore points, require the use of a flash recovery area. However, having a flash recovery area for use by these features does not force you to use it to store all recovery-related files.
Even when its use is not required, however, the flash recovery area offers a number of advantages over other on-disk backup storage methods. Backups moved to tape from the flash recovery area are retained on disk until space is needed for other required files, reducing the need to restore backups from tape. At the same time, obsolete files no longer needed to meet your recoverability goals and files backed up to tape become eligible for deletion and are deleted when space is needed. The DBA no longer has to manually delete old backups, and, it is less likely that a DBA accidentally deletes redundancy set files.
|
See Also: "Setting Up a Flash Recovery Area for RMAN" for more about the uses and benefits of the flash recovery area. |
The redo logs of your database provide a complete record of changes to the datafiles of your database (with a few exceptions, such as direct path loads).
You can run your database in one of two modes: ARCHIVELOG mode or NOARCHIVELOG mode. In ARCHIVELOG mode, a used online redo log group must be copied to one or more archive destinations before it can be reused. Archiving the redo log preserves all transactions stored in that log, so that they can be used in recovery operations later. In NOARCHIVELOG mode, the online redo log groups are simply overwritten when the log is reused. All information about transactions recorded in that redo log group is lost.
Running your database in NOARCHIVELOG mode imposes severe limitations on your backup and recovery strategy.
You cannot perform online backups of your database. You must shut your database down cleanly before you can take a backup in NOARCHIVELOG mode.
You cannot use any data recovery techniques that require the archived redo logs. These include complete and point-in-time media recovery, as described in "Forms of Data Recovery", and more advanced recovery techniques such as point-in-time recovery of individual tablespaces and Flashback Database (described in Oracle Database Backup and Recovery Advanced User's Guide.).
If you are running in NOARCHIVELOG mode and you must recover from damage to datafiles due to disk failure, you have two main options for recovery:
Drop all objects that have any extents located in the affected files, and then drop the files. The remainder of the database is intact, but all data in the affected files is lost.
Restore the entire database from the most recent backup, and lose all changes to the database since the backup. (Recovering changes since the backup would require performing media recovery, which uses the archived redo logs.)
For most applications, running in ARCHIVELOG mode is preferable to running in NOARCHIVELOG mode because you have more flexible recovery options after a data loss, such as point-in-time recovery of the database or some tablespaces. There are, however, associated costs of running in ARCHIVELOG mode:
Space must be set aside for archiving destinations, locations on disk where the archived redo logs will be stored. These can become quite large in databases with large numbers of updates.
The stored archived redo logs must be managed. To limit the disk space used by archived redo logs, archived redo logs can be moved to tape for longer-term storage, and older logs no longer needed to meet your recoverability goals should be deleted. (RMAN can automate most of the management of archived redo logs, by recording the location and contents of all archived redo logs, making it easy to move archived logs to tape, and identifying and deleting redo logs no longer required to meet your recoverability objectives.)
Some performance overhead is associated with the background processes ARC0 through ARCn which copy filled online redo logs to the archiving destinations.
When performance requirements are extreme or disk space limitations are severe, it may be preferable to run in NOARCHIVELOG mode in spite of the limitations that this choice imposes upon your recovery options.
Using the flashback features of Oracle improves the availability of your database when repairing the effects of unwanted database changes. The logical-level flashback features allow the objects not affected to remain available, and Flashback Database allows for faster rewind of the entire database than point-in-time recovery.
If you intend to take advantage of the logical-level flashback features of Oracle, you must take into account how the database manages undo data. See Oracle Database Concepts and Oracle Database Administrator's Guide for more information about undo data and automatic undo management.
Incorporating Flashback Database or guaranteed restore points into your strategy requires that you enable a flash recovery area, as well as creating guaranteed restore points at the right points in time, or configuring flashback logging. See Chapter 5, "Data Protection with Restore Points and Flashback Database" for more information on the role that these features can play in your data protection strategy and the requirements for using them separately or together.
Your backup retention policy is the rule you set regarding which backups must be retained (whether on disk or other backup media) to meet your recovery and other requirements.
Backup retention policy can be based on redundancy or a recovery window. In a redundancy-based retention policy, you specify a number n such that you always keep at least n distinct backups of each file in your database. In a recovery window-based retention policy, you specify a time interval in the past (for example, one week, or one month) and keep all backups required to let you perform point-in-time recovery to any point during that window.
A backup no longer needed to satisfy the backup retention policy is said to be obsolete.
RMAN automates the implementation of a backup retention policy, using the following commands:
CONFIGURE RETENTION POLICY command lets you set the retention policy that will apply to all of your database files by default.
REPORT OBSOLETE command lets you list backups currently on disk that are obsolete under the retention policy. You can also specify parameters to see which files would be obsolete under different retention policies.
DELETE OBSOLETE command deletes the files which REPORT OBSOLETE lists as obsolete.
CHANGE... KEEP lets you set a separate retention policy for specific backups, such as long-term backups kept for archival purposes. You can specify that a given backup must be kept until a future time, or even specify that a backup be kept forever. CHANGE... NOKEEP is used to let the retention policy apply to a backup previously protected by CHANGE... KEEP.
If you use a flash recovery area to store your backups, the database will delete obsolete backups automatically as disk space is needed for newer backups, archived logs and other files. For backups stored on disk outside a flash recovery area and for backups stored on tape, you should periodically run the DELETE OBSOLETE command to remove obsolete backups.
A recovery window-based retention policy lets you guarantee that you can perform point-in-time recovery to any point in the past, up to a number of days that you specify. The earliest point in time to which you can recover your database under your retention policy is known as the point of recoverability. All backups required for recovery or point-in-time recovery back to that time will be retained.
Use a recovery window-based backup retention policy if your business requirements dictate that any possible logical damage to the database must be reparable as long as it is discovered within a given period of time. Set the recovery window to that period of time.
Note that satisfying a recovery window-based retention policy will generally require that you keep backups older than the beginning of the recovery window. A point-in-time recovery to the beginning of the recovery window would require a restore from this backup, and then applying all changes between the backup time and the point of recoverability. For example, you might configure a recovery window of three days:
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
If your last full database backup was six days ago, RMAN will keep the six-day-old backup, and all redo logs required to roll the database forward to the beginning of the recovery window three days ago, in addition to any backups and redo logs needed to recover the database to all points in time within the three day window.
A recovery window-based backup retention policy provides the most certain recoverability for your data. The disadvantage is that more careful disk space planning is required, since it may not be obvious how many backups of datafiles and archived logs must be retained to guarantee the recovery window.
A redundancy-based backup retention policy determines whether a backup is obsolete based on how many backups of a file are currently on disk. You might configure a redundancy level of 3:
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
In this case, RMAN keeps three backups of each database file, and all redo logs required to recover all retained datafile backups to the current time. Any older backups will be considered obsolete.
For example, assume that you make backups of a datafile every day, starting on a Monday. On Thursday, you make your fourth backup of the datafile, and the backup from Monday becomes obsolete because you have the backups from Tuesday, Wednesday and Thursday. On Friday, the backup from Tuesday becomes obsolete, because you have the backups from Wednesday, Thursday and Friday.
There are several reasons to keep older backups of datafiles and archived logs:
An older backup of datafiles and archived logs is necessary for performing point-in-time recovery to a time before your most recent backup.
If your most recent backup is corrupt, you can still recover your database using an older backup and the complete set of archived logs since that older backup.
You may want to keep a copy of the database for archival purposes.
To perform point-in-time recovery to a given target time earlier than your current point of recoverability, then you need a database backup that completed before the target time, as well as all of the archived logs created between the time the backup was started and the target time. For example, if you take full database backups starting at 1:00 AM on February 1 (at SCN 10000) and on February 14 (at SCN 20000), and if you decide on February 28 to use point-in-time recovery to bring your database to its state at 9:00AM February 7 (SCN 13500), then you must use the February 1 backup, plus all redo logs containing changes from between the beginning of the creation of the backup (SCN 10000) and 9:00AM February 7 (SCN 13500).
Frequent backups are essential for any recovery scheme. Base the frequency of backups on the rate or frequency of database changes such as:
Addition and deletion of tables
Insertions and deletions of rows in existing tables
Updates to data within tables
The more frequently your database is updated, the more often you should perform database backups. The scenario in "Backup Scripts When Blocks Change Frequently" backs up the database every week.
If database updates are relatively infrequent, then you can make whole database backups infrequently and supplement them with incremental backups (which will be relatively small because few blocks have changed). The scenario in "Backup Scripts When Few Data Blocks Change" describes how to develop a backup strategy based on a single whole database backup.
There are times when you will need to take a backup of your database independent of your regular backup schedule. If you make any of the following structural changes, then perform a backup of the appropriate portion of your database immediately before and after completing the following changes:
Create or drop a tablespace.
Add or rename a datafile in an existing tablespace.
Add, rename, or drop an online redo log group or member.
If you are in NOARCHIVELOG mode, then you must shut down the database and perform a consistent whole database backup after any such change. If you are running in ARCHIVELOG mode, then you must make a control file backup after any such change, using either RMAN's BACKUP CONTROLFILE command or the SQL ALTER DATABASE BACKUP CONTROLFILE statement.
If you run in ARCHIVELOG mode, then you can back up an individual tablespace or even a single datafile. You might want to do this for one or more tablespaces that are updated much more often than the rest of your database, as is sometimes the case for the SYSTEM tablespace and automatic undo tablespaces.
More frequent backups of heavily-used datafiles can shorten recovery times in some situations. You may have a database where most updates are restricted to a small set of tablespaces. If you take a full database backup each Sunday, then recovery from a media failure affecting the frequently updated tablespaces on Friday requires re-applying large amounts of redo. Daily backups of the frequently-updated tablespaces reduces the amount of redo to apply without requiring a daily full database backup.
When a direct path load is performed to populate a database, no redo data is logged for those database changes. You cannot recover these changes after a restore from backup using conventional media recovery. Likewise, when tables and indexes are created as NOLOGGING, the database does not log redo data for these objects, which means that you cannot recover these objects from existing backups. Therefore, you should back up your datafiles after operations for which no redo data is logged.
|
Note: You can use either a full backup of your datafiles or an incremental backup. Either one will capture all changed blocks, including blocks changed by unrecoverable operations. |
|
See Also: Oracle Database SQL Reference for information about theUNRECOVERABLE option of the CREATE TABLE ... AS SELECT and CREATE INDEX statements. |
Oracle database import and export utilities are used to export database objects (tables, stored procedures, and so forth) from databases to be stored as files, and re-import objects from those files. An export provides a logical-level snapshot of the exported objects at the time of the export, as a binary file that can be imported back into the source database or some other database. Consider exporting portions or all of a database for supplemental protection and flexibility in a database's backup strategy.
While useful, database exports are not a substitute for whole database backups. They cannot provide the same complete recovery advantages of physical-level backups. For example, you cannot apply archived logs to logical backups in order to update lost changes.
|
See also: Oracle Database Utilities for more details about exporting and importing data for logical backup |
Online redo logs, unlike archived logs, should never be backed up. The chief danger associated by having backups of online redo logs is that you may accidentally restore those backups without meaning to, and corrupt your database.
Online redo log backups are also not particularly useful, for the following reasons:
If your database is in ARCHIVELOG mode, then the archiver is already archiving the filled redo logs automatically.
If your database is in NOARCHIVELOG mode, then the only type of physical backups that you can perform are closed, consistent, whole database backups. The files in this type of backup are all consistent and do not need recovery, so the online logs are not useful after a restore from backup.
The best method for protecting the online logs against media failure is to multiplex them, with multiple log members in each group, on different disks attached to different disk controllers.
|
Note: RMAN does not permit you to back up online redo logs. You must archive a redo log before backing it up. |
During the stress of a recovery situation, it is important that you have all necessary information at your disposal. This is especially true if for some reason you need to contact Oracle Support because you run into a problem that you do not understand. You should have the following documentation about the hardware configuration:
The name, make, and model of the machine that hosts the database
The version and patch of the operating system
The number of disks and disk controllers
The disk capacity and free space
The names of all datafiles
The name and version of the media management software (if you use a third-party media manager)
You should also keep the following documentation about the software configuration:
The name of the database instance (SID)
The database identifier (DBID)
The version and patch release of the Oracle database server
The version and patch release of the networking software
The method (RMAN or user-managed) and frequency of database backups
The method of restore and recovery (RMAN or user-managed)
You should keep this information both in electronic and hardcopy form. For example, if you save this information in a text file on the network or in an email message, then if the entire system goes down, you may not have access to this data.
It is especially important to keep a record of the DBID. If you have to restore and recover your database including the loss of the SPFILE and control file, you will need the DBID during the recovery process. See "Basic Database Restore and Recovery Scenarios" for details on how the DBID is used during recovery.
