While it is prudent that every database has a good backup, consider your Recovery Time Objective (RTO) and Recovery Point Objective (RPO) when designing a backup and recovery strategy. While many recoveries involve restoring a backup, Oracle provides other database features such as Oracle Data Guard and Flashback Technology to minimize the recovery time from a database outage.
This section discusses the best practices for maintaining a good database backup, and other backup options and strategies made possible by the available Oracle database features.
Oracle has multiple database features and products to facilitate Backup and Recovery operations, including Recovery Manager (RMAN), Oracle Secure Backup, the flash recovery area, Flashback Database and restore points.
Recovery Manager (RMAN) is Oracle's utility to backup and recover the Oracle Database. Because of its tight integration with the database, RMAN determines automatically what files must be backed up. But more importantly, RMAN knows what files must be restored for media-recovery operations. RMAN uses server sessions to perform backup and recovery operations and stores metadata about backups in a repository. RMAN offers many advantages over typical user-managed backup methods, including:
Online database backups without placing tablespaces in backup mode
Data block integrity checks during backup and restore operations
Test backups and restores without actually performing the operation
RMAN automates backup and recovery. User-managed methods require you to locate backups for each data file, copy them to the correct place using operating system commands, and choose which logs to apply. RMAN manages these tasks automatically.
There are also capabilities of Oracle backup and recovery that are only available when using RMAN, such as automated tablespace point-in-time recovery and block media recovery.
See Also:The following chapters in Oracle Database Backup and Recovery User's Guide:
"Performing Block Media Recovery"
"Performing RMAN Tablespace Point-in-Time Recovery (TSPITR)"
Oracle Secure Backup provides data protection for heterogeneous UNIX, Linux, Windows, and Network Attached Storage (NAS) environments. Oracle Secure Backup provides tape data protection for the entire Oracle environment:
Oracle Database through integration with RMAN
Seamless support of Oracle Real Application Clusters (Oracle RAC)
File system data protection of distributed servers including:
Oracle Application Servers
Oracle Collaboration Suites
Oracle home and binaries
The combination of RMAN and Oracle Secure Backup provides an end-to-end tape backup solution, eliminating the need for third-party backup software.
See Also:The Oracle Secure Backup Web site at
Oracle restore points protect against logical failures at risky points during database maintenance. Creating a normal restore point assigns a restore point name to a specific point in time or SCN. The restore point name is used with Flashback Table, Flashback Database, and all RMAN recovery-related operations.
Guaranteed restore points are recommended for database-wide maintenance such as database or application upgrades, or running batch processes. Guaranteed restore points enable Flashback Database and retain all flashback logs that are necessary to ensure the database can be flashed back to the restore point. After maintenance activities complete and the results are verified, you should delete guaranteed restore points that are no longer needed.
See Also:Oracle Database Backup and Recovery User's Guide for more information about Flashback Database
This section describes best practices for using backups, performing backups, using the RMAN recovery catalog, enabling block change tracking, and so on.
Using backups to resolve an unscheduled outage of a production database may not allow you to meet your RTO or service-level requirements. For example, some outages are handled best by using Flashback Database or a standby database. However, some situations require using database backups, including the following:
Setting Up the Initial Data Guard Environment During initial setup of a standby database, you can either use a backup of the primary database at the secondary site to create the initial standby database., or use network-enabled database duplication to perform active database duplication without the need for pre-existing database backup. In either case, you connect to the primary database and use the
DUPLICATE command to create a physical standby database. To trigger an over-the-network duplication, you must also include the FROM
ACTIVE DATABASE option.
Recovering from Data Failures Using File or Block Media Recovery When a block corruption, media failure, or other physical data failure occurs in an environment that does not include Data Guard, the only method of recovery is to restore from existing backups.
Resolving a Double Failure A double failure scenario affects the availability of both the production and standby databases. An example of a double failure scenario is a site outage at the secondary site, which eliminates fault tolerance, followed by a media failure on the production database. Whether the standby must be re-created depends on the type of outage at the secondary site. If the secondary site outage was temporary and did not involve the physical destruction of files, then after the secondary site is brought back online it can continue to receive redo data from the production database. Otherwise, the resolution of this situation is to re-create the production database from an available backup and then re-create the standby database.
Some multiple failures, or more appropriately disasters (such as a primary site outage followed by a secondary site outage), might require the use of backups that exist only in an offsite location. Developing and following a process to deliver and maintain backup tapes at an offsite location is necessary to restore service in the most dire of circumstances.
It is important to determine a backup frequency policy and to perform regular backups. A backup retention policy helps ensure that needed data is not destroyed too soon.
Criticality of the data: The RPO determines how much data your business can acceptably lose in the event of a failure. The more critical the data, the lower the RPO and the more frequent data should be backed up. You must determine which part of the database is most critical to the business.
Estimated repair time: The RTO determines the acceptable amount of time needed for recovery. Repair time is dictated by restore time plus recovery time. The lower the RTO the higher the frequency of backups to reduce the time it takes to recover.
Volume of changed data: The rate of database change effects how often data is backed up:
For read-only data, perform backups frequently enough to adhere to retention policies.
For frequently changing data, perform backups more often to reduce the RTO.
To simplify database backup and recovery, the Oracle suggested backup strategy implements the flash recovery area while using incremental backups and incrementally updated backup features.
See Also:"Using the Oracle Suggested Backup Strategy" in Oracle Database 2 Day DBA
Establishing a Backup Retention Policy A backup retention policy is a rule set regarding which backups must be retained (on disk or other backup media) to meet recovery and other requirements. It may be safe to delete a specific backup because it has been superseded by more recent backups or because it has been stored on tape. You may also have to retain a specific backup on disk for other reasons such as archival or regulatory requirements. A backup that is no longer needed to satisfy the backup retention policy is said to be obsolete.
Base your backup retention policy on redundancy or on a recovery window:
In a redundancy-based retention policy, 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, specify an earlier time interval (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.
Keeping Archival Backups Some businesses must maintain archival (long-term) backups that may be needed years into the future. Rather than becoming obsolete according to the database's backup retention policy, archival backups become obsolete when their time limit expires.
Moreover, you can use the RMAN
BACKUP command with the
KEEP FOREVER option to retain backups that are exempt from the retention policy and never expire, providing the ability to restore and recover the database to any point in time. It is required that you use a recovery catalog for the RMAN repository so that backup metadata is not lost due to lack of space, which may occur when using the target database control file for the RMAN repository. Beginning in Oracle Database in 11g, only the archived redo log files required to make an archival backup consistent are retained.
See Also:The section about "Making Database Backups for Long-Term Storage" in Oracle Database Backup and Recovery User's Guide
To protect and keep backup metadata for even longer periods of time, you can create an additional RMAN repository in a separate database schema called the recovery catalog. You should create the recovery catalog schema in a standalone database that is dedicated to this purpose. Do not locate the recovery catalog with other production data. If you use Oracle Enterprise Manager, Oracle recommends creating the recovery catalog schema in the Enterprise Manager repository database.
The advantages of using a recovery catalog include:
Stores backup information long term.
Stores metadata for multiple databases.
Restores an available backup onto another system.
Allows you to offload backups to a physical standby database and use those backups to restore and recover the primary database. Similarly, you can back up a tablespace on a primary database and restore and recover it on a physical standby database. Note that backups of logical standby databases are not usable at the primary database.
Another reason to use a recovery catalog is the limited maximum size of the target database control file. If the control file is too small to hold additional backup metadata, then existing backup information is overwritten, making it difficult to restore and recover using those backups.
See Also:Oracle Database Backup and Recovery User's Guide for more information on RMAN repository
When creating backups to disk or tape, use the target database control file as the RMAN repository, so that backup success does not depend on the availability of the database holding the RMAN repository. To use the target database control file as the RMAN repository, run RMAN with the
NOCATALOG option. Immediately after the backup is complete, the new backup information stored in the target database control file should be resynchronized with the recovery catalog using the
RESYNC CATALOG command.
See Also:Oracle Database Backup and Recovery Reference for more information about the
Oracle Database includes a change tracking feature for incremental backups, which improves incremental backup performance by recording changed blocks in each data file in a change tracking file. If change tracking is enabled, then RMAN uses the change tracking file to identify which blocks to include in an incremental backup. This avoids the need to scan every block in the data file, reducing the number of disk reads during backup.
Starting with Oracle Database 11g, you can enable change tracking on both the primary and standby databases. You should enable change tracking for any database where incremental backups are being performed. For example, if backups have been completely offloaded to a physical standby database, then block change tracking should be enabled for that database. If backups are being performed on both the primary and standby databases, then enable change tracking for both databases.
See Also:Oracle Database Backup and Recovery Basics for more information about block change tracking
Configure RMAN to automatically back up the control file and server parameter file (SPFILE) whenever the database structure metadata in the control file changes or when a backup record is added. The autobackup enables RMAN to recover the database even if the current control file, catalog, and SPFILE are lost. The RMAN autobackup feature is enabled with the
CONFIGURE CONTROLFILE AUTOBACKUP ON statement.
You should enable autobackup for both the primary and standby databases. For example, after connecting to the primary database (as the target database) and the recovery catalog, issue the following command:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
See Also:The MAA white paper "Using Recovery Manager with Oracle Data Guard"and Oracle Database Backup and Recovery User's Guide for more information about autobackup
In an Oracle Data Guard configuration, you can offload the process of backing up control files, data files, and archived redo log files to a physical standby database system, thereby minimizing the effect of performing backups on the primary system. These backups can be used to recover the primary or standby database. Note that backups of logical standby databases are not usable on the primary database.
See Also:The chapter about using RMAN to back up and restore files in Oracle Data Guard Concepts and Administration
When selecting a backup mechanism, use the following priorities to drive your backup strategy:
Overall backup time
Impact to resource consumption
Space used by the backup
Table 2-8 compares different backup alternatives against the different priorities you might have. The table guides you to choose the best backup approach for your specific business requirements. You might want to minimize backup space while sacrificing recovery time. Alternatively, you might choose to place a higher priority on recovery and backup times while space is not an issue.
|Backup Option||Overall Backup Time
|Impact on Resource Consumption
|Space Used by Backup
1 Foot 1
Footnote 1 No restore (switch to flash recovery area copy)
Best Practices for Optimizing Recovery Times If restore time is your primary concern, then perform either a database copy or an incremental backup with roll forward immediately. These are the only options that provide an immediately usable backup of the database, which you then need to recover only to the time of the failure using archived redo log files created since the last backup was performed.
Best Practices for Minimizing Space Usage If space usage is your primary concern, then perform an incremental backup with a deferred roll forward. If you perform a cumulative level 1 incremental backup, then it stores only those blocks that have been changed since the last level 0 backup:
With a cumulative incremental backup, apply only the last level 1 backup to the level 0 backup.
With a differential incremental backup, apply all level 1 backups to the level 0 backup.
A cumulative incremental backup usually consumes more space in the flash recovery area than a differential incremental backup.
Best Practices for Minimizing System Resource Consumption (I/O and CPU) If system resource consumption is your primary concern, then an incremental backup with a block change tracking enabled consumes the least amount of resources on the database.
For many applications, only a small percentage of the entire database is changed each day even if the transaction rate is very high. Frequently, applications modify a same set of blocks frequently; so, the total dirty block set is small.
For example, a database contains about 600 GB of user data, not including temp files and redo logs. Every 24 hours, approximately 2.5% of the database is changed, which is approximately 15 GB of data. In this example, MAA testing recorded the following results:
Level 0 backup takes 180 minutes, including
READs from the data area and
WRITEs to the flash recovery area
Level 1 backup takes 20 minutes, including
READs from the data area and
WRITEs to the flash recovery area
Rolling forward and merging an existing image copy in the flash recovery area with a newly created incremental backup takes only 45 minutes, including
WRITEs from the flash recovery area.
In the last case in which a level 0 backup is taken and then rolled forward with incremental backups, the initial backup takes 180 minutes (which is the same amount of time it takes to perform a full backup). Subsequent backups are level 1 (incremental), which take 20 minutes, so the potential impact on the data area is reduced. That backup is then applied to the existing level 0 backup, which takes 45 minutes. This process does not perform I/O to the data area, so there is no impact (if the flash recovery area and data area use separate storage). The total time to create the incremental backup and apply it to the existing level 0 backup is 65 minutes (20+45).The result is the same is both cases—a full image backup of the database is performed. The incremental approach takes 115 minutes less time (64% less) than simply creating a full backup. And the amount of I/O performed to reach the same end point is less, particularly against the data area, which should have less detrimental effect on performance of the production database.
Thus, for this example, when you compare always taking full backups versus starting with a level 0 backup, performing only incremental backups, and then rolling forward the level 0 backup, the net savings are:
115 minutes or 64% time savings to create a complete backup
Reduced I/O on the database during backups
For bigger databases, MAA testing recorded even larger gains.
See Also:The "Backing Up the Database" chapter in Oracle Database Backup and Recovery User's Guide
This section describes how to create tape backups, Oracle Secure backup, and maintaining offsite backups.
Use the RMAN command
BACKUP RECOVERY FILES to copy disk backups created in the flash recovery area to tape. Using a single command, all files that have not been backed up to tape are backed up. This prevents you from backing up files more than once and wasting tape, or keeping track of files that were not backed up previously. Use tape backups to handle certain outage scenarios and for offsite and long-term storage.
Oracle Secure Backup delivers the fastest Oracle database backup to tape. Oracle Secure Backup has intimate access and integration with Recovery Manager (RMAN) that is not available with non Oracle tape backup systems. Using Oracle Secure Backup Release 10.2 and Oracle Database 11g provides the following key performance optimizations:
Eliminates backup of committed undo, thus increasing backup performance and reducing tape consumption. Only noncommitted undo is backed up.
Optimizes SBT buffer allocation using a shared buffer between SBT and tape (Oracle Secure Backup). In past releases, RMAN writes data to the SBT buffer then the media manager copies data from the SBT buffer to the tape buffer. Oracle testing results indicate that using a shared buffer (Oracle Secure Backup and RMAN only) reduces CPU overhead by up to 30%.
For configurations running Oracle Database 10g Release 2 (10.2) and Oracle Secure Backup Release 10.1, Oracle Secure Backup backs up and reads only the blocks that are currently allocated to database objects. Blocks that are not allocated are neither read nor backed up.
See Also:The Oracle Secure Backup documentation set available on the Oracle Technology Network at
Regardless of the architecture deployed—including the existence of a standby database—it is still important to have offsite backups for business requirements, to protect against disasters, and to follow legal and regulatory requirements such as the Securities and Exchange Commission (SEC) and Health Insurance Portability and Accountability Act (HIPPA).
This section describes checking data files for corruption, using Data Recovery Advisor, testing recovery procedures, and backing up the recovery catalog database.
Use the RMAN
VALIDATE command to regularly check database files for block corruption that has not yet been reported by a user session or by normal backup operations. RMAN scans the specified files and checks for physical and logical errors, but does not actually perform the backup or recovery operation. Oracle Database records the address of the corrupt block and the type of corruption in the control file. Access these records through the
V$DATABASE_BLOCK_CORRUPTION view, which can be used by RMAN block media recovery.
To detect all types of corruption that are possible to detect, specify the
CHECK LOGICAL option.
See Also:The chapter in Oracle Database Backup and Recovery User's Guide that describes validating database files and backups
Complete, successful, and tested backups are fundamental to the success of any recovery. Create test plans for different outage types. Start with the most common outage types and progress to the least probable. Using the RMAN
DUPLICATE command is a good way to perform recovery testing, because it requires restoring from backups and performing media recovery.
Monitor the backup procedure for errors, and validate backups by testing your recovery procedures periodically. Also, validate the ability to restore the database using the RMAN command
Include the recovery catalog database in your backup and recovery strategy. If you do not back up the recovery catalog and a disk failure occurs that destroys the recovery catalog database, then you may lose the metadata in the catalog. Without the recovery catalog contents, recovery of your other databases is likely to be more difficult.
See Also:The chapter in Oracle Database Backup and Recovery User's Guide that describes managing a recovery catalog