5
Recovering Using RMAN Backups
CERTIFICATION OBJECTIVES
5.01 Perform complete recovery from a critical or noncritical data file loss using RMAN
5.02 Perform incomplete recovery using RMAN
5.03 Recover using incrementally updated backups
5.04 Switch to image copies for fast recovery
5.05 Restore a database onto a new host
5.06 Recover using a backup control file
5.07 Perform disaster recovery
In this chapter, you will learn how to use RMAN in a recovery scenario. This is required if you have lost a noncritical datafile, a critical datafile, a control file, or possibly the entire database.
This chapter addresses recovery scenarios for a database running in ARCHIVELOG mode. Of course, if it isn’t apparent by now, you should be running in ARCHIVELOG mode, considering all the advantages and the relatively few disadvantages presented in previous chapters. However, if you are not running in ARCHIVELOG mode, your recovery options are also presented as well as those for NOARCHIVELOG mode.
Image copies play an important role in your backup and recovery scenarios. You’ll learn how to recover an image copy to make it even faster to recover your database in case of media failure. In addition, you’ll see how to switch to an image copy and then switch back.
Once you create your backups, you want to make sure that you can use them to recover your database. You’ll learn how to restore a database to a new host either to test your recovery scenarios or move a production database to the new host.
Finally, you’ll learn how to perform incomplete recovery and recover your database up to a point in time in the past. This is useful when you have logical corruption in your database, including but not limited to user errors or DBA errors that would merely be re-created with a full recovery. An incomplete recovery also assumes that you can not use other less drastic options, such as using Oracle’s flashback table functionality or using SELECT . . . AS OF.
CERTIFICATION OBJECTIVE 5.01
Perform Complete Recovery from a Critical or Noncritical Data File Loss Using RMAN
In the following sections, you’ll learn how to use the RESTORE and RECOVER commands for a database running in ARCHIVELOG mode. First, you’ll read about the basic functions of RESTORE and RECOVER and how they work. Next, you’ll see how to recover both a noncritical and a critical datafile successfully.
Using the RMAN RESTORE and RECOVER Commands
In general, recovering from a database failure is a two-step process: restoring one or more database files from a backup location and applying archived and online redo log files to bring the entire database or individual datafile up to the specified System Change Number (SCN), which is usually the most recent SCN (the last committed transaction).
The RESTORE command is the first step in any recovery process. When you issue a RESTORE command, RMAN retrieves one or more datafiles from disk or tape along with any archived redo log files required during the recovery operation. If your backup files are on tape, you will need to allocate the necessary tape channels as well.
When you issue the RECOVER command, RMAN applies the committed changes in the archived and online redo log files to the restored datafiles. A disaster recovery scenario is called for when most database datafiles are lost or corrupted. The recovery process can be as simple as the following example (command output excluded for clarity):
Using the Database Control to perform a recovery operation is almost as easy. As you can see in Figure 5-1, the steps you perform are straightforward but might not be as easy as typing two or three SQL and RMAN commands.
FIGURE 5-1 Recovering a database using the Database Control
The recovery process is slightly different, depending on whether you lose a critical or a noncritical datafile. If you lose a critical datafile, you must shut down and start up the database in MOUNT mode before you can recover the database. For a noncritical datafile, you can perform the recovery while users are connected and using other available datafiles.
Performing Complete Recovery of a Noncritical Datafile
If you lose a datafile that is not a part of the SYSTEM or UNDO tablespace, the datafile is considered noncritical (although the users of the lost datafile might disagree with this assessment). When the database is in ARCHIVELOG mode, a corrupted or missing datafile that is not part of the SYSTEM or UNDO tablespace affects only objects in the corrupted or missing datafile.
The general steps to recover a datafile from a noncritical tablespace are as follows:
1. If the database is open, take the tablespace containing the corrupted or missing datafile offline with the ALTER TABLESPACE command.
2. Use the RMAN RESTORE command to load the datafile(s) for the tablespace from the backup location.
3. Use the RMAN RECOVER command to apply archived and online redo log files to the restored datafile(s).
4. Bring the tablespace back online.
Because the database is in ARCHIVELOG mode, recovery up to the last committed transaction is possible. In other words, users do not have to re-enter any data for previously committed transactions.
EXERCISE 5-1
Restore and Recover the USERS Tablespace
In this exercise, the datafile for the USERS tablespace was accidentally deleted by the system administrator. Restore and recover the tablespace while the database is still open for access to the other tablespaces.
1. Connect to RMAN and take the USERS tablespace offline:
Any users trying to access the tablespace while it is offline will receive a message similar to the following:
2. Restore the USERS tablespace:
3. Recover the USERS tablespace to apply the archived and online redo log files:
4. Bring the USERS tablespace back online:
5. Confirm that users can once again access the USERS tablespace:
Performing Complete Recovery of a Critical Datafile
The procedure for recovering a critical datafile is similar to that of a noncritical datafile, except that the database must be shut down and opened in the MOUNT state to perform the recovery operation. If the lost datafile is from the SYSTEM tablespace, the instance will most likely crash or shut down automatically. Here are the steps you use to recover a critical datafile:
1. Shut down the database with SHUTDOWN ABORT if it is not already shut down.
2. Reopen the database with STARTUP MOUNT.
3. Use the RMAN RESTORE command to copy (restore) the datafile(s) for the critical tablespace from the backup location.
4. Use the RMAN RECOVER command to apply any archived or online redo log files.
5. Reopen the database for users with ALTER DATABASE OPEN.
All committed transactions are recovered up until the time of failure, so users will not have to re-enter any data. In the case of the SYSTEM tablespace, you may not have any user transactions but you will not lose any new objects created since the last backup.
CERTIFICATION OBJECTIVE 5.02
Perform Incomplete Recovery Using RMAN
On occasion, you might need to restore a database to a point of time in the past. For example, applications could have made numerous erroneous changes to the database in the last 24 hours, and you may not be able to reverse errors easily with a table flashback, or perhaps you do not have flashback configured for the database.
Using restore points makes it easier to perform point-in-time recovery, whether you’re performing incomplete recovery in RMAN or flashback database. After you learn about restore points, you’ll perform an incomplete recovery using a restore point.
Creating Restore Points
You can create two types of restore points—either as of a specific time or with an SCN number in the past. Which type you use depends on your environment and which option is the more convenient. If you do not specify either option, Oracle uses the current SCN and assumes you want the restore time to be the current time. Remember that you can retrieve the current SCN from V$DATABASE:
To create a restore point for the present time or SCN, use this format of the CREATE RESTORE POINT command:
To create a restore point for a particular SCN, use the AS OF syntax:
Restore points are also useful when you want to use Oracle’s flashback technology to flash back a table or the database to a point in time in the past.
Oracle keeps restore points for at least as long as the time specified in the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter. If you explicitly want to keep a restore point longer, use the PRESERVE keyword when you create the restore point:
As you might expect, you can explicitly remove a restore point with the DROP RESTORE POINT command:
Performing Server-Managed Incomplete Recovery
To perform server-managed (RMAN) incomplete recovery (as opposed to user-managed recovery, covered in Chapter 6), use the following steps:
1. Determine the target point for the restore (SCN, time, restore point, or log sequence number).
2. Set the NLS variables at the operating system prompt if you are using time-based incomplete recovery:
NLS_LANG
NLS_DATE_FORMAT
3. Stop and restart the database in MOUNT mode.
4. Using an RMAN RUN block, use the SET UNTIL, RESTORE, and RECOVER commands.
5. Optionally, open the database in READ ONLY mode to verify that the restore point was the desired restore point.
6. Open the database using RESETLOGS.
It’s important to specify the correct NLS variables so that RMAN will interpret the date strings you provide correctly; here are some sample values:
Also note that opening the database as READ ONLY after your incomplete recovery gives you the opportunity to run another incomplete recovery for an SCN or a time before or after your original SCN, or time. Once you open the database for read/write with RESETLOGS, the current log sequence is set to 1 and any redo information not applied during recovery is discarded. This prevents you from performing another recovery using a redo generated after the SCN or timestamp of your incomplete recovery.
EXERCISE 5-2
Perform Incomplete Recovery to Restore the USERS Tablespace
In this exercise, you will create a restore point and use it later to recover from the inadvertent deletion of tables and views in the EXAMPLE tablespace.
1. Create a restore point for the current SCN:
2. "Accidentally” drop some tables and views in the EXAMPLE tablespace:
3. Shut down the instance and restart the database in MOUNT mode:
4. At the RMAN prompt, create a RUN block that uses the restore point created earlier to restore and recover the database to the time of the restore point:
5. Open the database with RESETLOGS:
6. Verify the existence of the dropped table:
Note that several less draconian methods are available to restore and recover these tables and views, such as flashback database, restoring and recovering each tablespace while the database is still online, or retrieving the tables from the recycle bin. Each recovery situation must be evaluated separately, balancing these factors:
The time required to obtain the backup files needed for recovery. If backup files are on a tape offsite, then the time required could be unacceptable.
The time to restore and recover the entire database once the recovery files are available.
The time the DBA must spend to perform the recovery.
The time the users must spend to re-enter lost data.
The tolerance for database downtime.
CERTIFICATION OBJECTIVE 5.03
Recover Using Incrementally Updated Backups
Using image copies in your backup and recovery strategy significantly reduces the time it takes to restore a datafile or the entire database. Image copies are already in the native Oracle datafile format and do not need to be re-created from a compressed or uncompressed RMAN backupset. RMAN can improve on this even more, because you can incrementally update an image copy using an incremental backup. In the following sections, you’ll learn more about how to recover an image copy, and you’ll review a sample image copy strategy.
Recovering Image Copies
When you update an image copy with an incremental backup, any recovery scenario that uses the image copy needs to apply only the archived and online redo log files since the last incremental backup. There is no longer any need to perform another full image copy of the datafile or database. The incremental recovery of each datafile is indistinguishable from a full image copy.
If more than one image copy of a datafile exists, RMAN automatically determines which one to use—usually the most recently created or incrementally updated version. If the recovery process for an image copy fails when applying an incremental backup, such as the temporary unavailability of the incremental backup, just restart the recovery process when the incremental backup is available again. RMAN picks up where it left off.
Implementing an Image Copy Strategy
Here is a sample RMAN script to implement an incrementally updated image copy strategy on a daily basis, after you’ve created the image copy of the entire database:
Here’s a breakdown of what happens in this RUN block. The first time you run it, there is no level 0 image copy to restore and, similarly, no level 0 incremental backup yet, so you get these messages:
RMAN automatically creates a level 0 backup whenever a level 1 backup occurs and there is no level 0 backup. The next time you run the script, the level 0 backup exists but no incremental level 0 backup exists yet. So the RECOVER command in the RUN block still generates these messages:
On the third and successive invocations of this RUN block, the RECOVER command updates the image copy with the latest level 1 backup, and another level 1 incremental backup occurs. This will be applied the next time this RUN block is executed. As a result, any recovery operation after the third invocation of this script will involve no more than the image copies, one incremental backup, and any archived and online redo logs generated since the last level 1 incremental backup.
Be sure to use tags with an incrementally updated image copy strategy. Without tags, a more recent, and possibly incorrect, incremental backup would be used to recover the image copies.
CERTIFICATION OBJECTIVE 5.04
Switch to Image Copies for Fast Recovery
Once you’ve starting making image copies, and even incrementally updating them, you can use them in a restore and recover operation to quickly recover some or all of your database. To recover your database even faster, you can perform a fast switch to image copies. In other words, you can use the image copies directly, skip the restore step, and apply only the recovery step. After the original datafiles are repaired or restored, you can easily switch back with little or no impact to users who are using other datafiles. The database does not need to be shut down unless you are switching to the image copies of the SYSTEM or UNDO tablespace datafiles.
Using the SET NEWNAME command within the RUN block to specify an alternative location for the replacement image copy allows RMAN to make the switch to image copies even easier.
Performing a Fast Switch to Image Copies
When disaster strikes and you lose a single datafile, or even all datafiles, image copies significantly reduce the time required to recover your database. Once you’ve switched to an image copy, you will most likely want to switch back to the original datafile locations after the media failure has been repaired. Here you’ll learn how to switch to an image copy and then switch back.
Switch to an Image Copy
The steps to switch to a datafile copy are very straightforward. This assumes, of course, that you have image copies of the damaged or lost datafile, as well as all archived and online redo log files since the image copy was created. Here are the steps:
1. Take the tablespace(s) containing the missing datafiles offline. You can use one of the dynamic performance views V$RECOVER_FILE, V$DATAFILE_HEADER, or V$TABLESPACE to identify which datafiles need recovery.
2. Use the RMAN SWITCH TO . . . COPY to point to the image copy of the missing datafiles.
3. Recover the datafiles using the RMAN RECOVER command.
4. Bring the tablespace(s) back online.
|
|
The RMAN EXERCISE 5-3
Use the SWITCH Command to Recover a Datafile Quickly
The datafile for the USERS tablespace mysteriously disappears. Users start to complain immediately, reporting this message when they try to create or update a table:
In addition, you see this message in the alert log. This alert should also be visible as an alert on the Enterprise Manager home page:
Find out what datafile number you need to restore, switch to an image copy, and then recover the datafile and bring the tablespace back online:
1. Since you already know that datafile #4 is having problems, query V$TABLESPACE to confirm that the USERS tablespace is the culprit:
The dynamic performance view V$DATAFILE_HEADER shows the error as well but does not always identify the tablespace name:
2. Take the USERS tablespace offline at the SQL prompt:
Alternatively, you can take the tablespace offline using the RMAN SQL command.
3. Switch to the datafile copy for the USERS tablespace:
Note that you can use the SWITCH command with either the DATAFILE or TABLESPACE parameter, whichever is easier or more convenient. Note also that you don’t need to know where your datafile copy is. RMAN knows where it is and will switch it and update the control file and recovery catalog automatically with the new location.
4. Recover the USERS tablespace using the recent archived and online redo log files:
5. Finally, bring the USERS tablespace back online:
Alternatively, you can of course bring the tablespace back online using the SQL prompt.
Switch Back to Original Location
Once your database is back up and running after switching to an image copy, you will likely want to switch the datafile back to its original location after the source disk has been repaired. This is especially true if the image copy you switched to resides in the flash recovery area. Flash recovery is used primarily for recovery and storage of multiplexed control files and archived redo log files, and it may reside on a slower disk. To move the tablespace and its associated datafiles back to the original location, follow these steps:
1. Create an image copy of the datafiles in the original location.
2. Take the tablespace offline.
3. Use the SWITCH TO . . . COPY command to switch back to the restored (re-created) datafile.
4. Recover the datafiles.
5. Bring the tablespace back online.
Note that you can perform most of these steps while the users are still using the original image copy. The tablespace will once again be unavailable during recovery. This step should be short if not many archived redo log files have been created since the image copy was made in the original location.
EXERCISE 5-4
Use the SWITCH Command after Creating the USERS Tablespace’s Datafile in the Original Location
In this exercise, you’ll switch the datafile for the USERS tablespace back to its original location after the source disk has been repaired (or you have figured out why datafiles are disappearing from the source disk). The datafile locations for each tablespace are currently as follows:
1. Create an image copy of the datafile back to the original location:
Note that you can name the image copy anything you want. In this case, you’ll use the original name of the datafile to be consistent with the other datafile names.
2. Take the USERS tablespace offline in preparation for the SWITCH command:
3. Switch to the newly created copy:
4. Recover the datafile in its new location:
5. Bring the USERS tablespace back online:
6. Confirm that the datafile for the USERS tablespace is back in its original location:
7. Create a new image copy to be ready when or if the datafile disappears again, although you could also use the image copy you just switched from:
Using the RMAN SET NEWNAME with Fast Switch
One of the many options for the SET command in RMAN is the SET NEWNAME command. Inside of a RUN block, SET NEWNAME makes it easy to specify one or more new datafile destinations in preparation for subsequent RESTORE and SWITCH commands. Here is an RMAN RUN block to specify a new location for the restored datafile of the USERS tablespace:
Note that the SWITCH command is used in much the same way as it’s used in the preceding section. The difference is that this example restores a datafile from a backup (most likely a backupset) to an alternative location instead of switching to an existing image copy. The result of the SWITCH command, whether in a RUN block or as a standalone command, is to update the control file (and the recovery catalog if you’re using one) with the new filenames.
If you did not specify the SET command in the preceding example, RMAN would restore the datafile for the USERS tablespace to its original location, and the SWITCH command would not perform any useful action.
CERTIFICATION OBJECTIVE 5.05
Restore a Database onto a New Host
RMAN can make it easy to restore a database to a new host in a number of ways, and a number of motives exist for restoring a database to a new host. The following sections delve into the reasons you should and should not restore and recover a database to a new host. In addition, you’ll learn the steps for preparing the host and performing the actual restore and recovery operation.
Understanding Restoration to a New Host
If you want to perform a disaster recovery test to another host, or you want to move a database permanently to another host, then using the RMAN RESTORE and RECOVER commands is the best method to use. However, using the procedures outlined in the following sections will keep the original database identifier (DBID) and will therefore cause a conflict in the RMAN repository, because the restored database with the same DBID will be considered the current target database. As a result, these procedures should not be used to create a permanent second database—use the RMAN DUPLICATE command for that. The procedures for using DUPLICATE are detailed in Chapter 7.
Preparing the New Host
To prepare the new host to receive the restored and recovered database, follow these steps:
1. Record the DBID of the source database. Use this command to query the DBID:
2. Copy the initialization parameter file from the source database to the new host.
3. Make sure that all source database backups are available to the new host. Do not connect to the recovery catalog.
Restoring and Recovering on the New Host
Although a lot of steps are required to restore and recover to the new host, each step is straightforward:
1. Configure environment variables on the new host.
2. Connect to the new target database with NOCATALOG.
3. Set the DBID.
4. Start the instance with NOMOUNT.
5. Restore the SPFILE from backup.
6. Shut down the new instance.
7. Edit the PFILE.
8. Start the instance with NOMOUNT (again).
9. Create an RMAN RUN block to restore the control file.
10. Create an RMAN RUN block to restore and recover the database.
11. Open the database with RESETLOGS.
The following sections offer a brief overview of each step.
1. Configure environment variables on the new host. Define the environment variable (on Unix or Linux systems) ORACLE_SID:
2. Connect to the new target database with NOCATALOG. Connect to the new target database but not the recovery catalog, as in this example:
3. Set the DBID. From within RMAN, set the DBID. You found the DBID earlier by querying V$DATABASE:
4. Start the instance in NOMOUNT mode. Start the database in NOMOUNT mode:
RMAN will return a warning message and use a default parameter file because the parameter file has not been restored yet.
5. Restore the SPFILE. Within an RMAN RUN block, restore the SPFILE using a command similar to the following:
Note that you’re restoring the SPFILE to a PFILE, so you can make edits later.
6. Shut down the new instance. Shut down the new instance with either SHUTDOWN IMMEDIATE or SHUTDOWN ABORT. Since ABORT requires recovery on restart, IMMEDIATE is the preferable option.
7. Edit the PFILE. Edit the newly created PFILE to change any server-specific locations for initialization parameters such as these:
IFILE
LOG_ARCHIVE_DEST_*
CONTROL_FILES
8. Start the instance in NOMOUNT mode. Start the instance in NOMOUNT mode again. You use the edited control file because you don’t have the control file restored yet:
9. Create an RMAN RUN block. Now that the database is using the restored and edited parameter file, you can execute an RMAN RUN block to restore the control file and change the restored database to MOUNT mode:
RMAN will restore the control file to the location specified for CONTROL_FILES in the initialization parameter file. Remember that many commands, such as ALTER DATABASE, work identically at both a SQL< prompt and an RMAN< prompt—convenient but sometimes confusing.
10. Create and run the RMAN recovery script. Create an RMAN RUN block that contains the appropriate SET NEWNAME commands to specify the new locations for each datafile, such as in this example:
The script should also include any ALTER DATABASE . . . RENAME commands for the online redo log files:
Limit the recovery to the last archived redo log file, because the online redo log files for the new instance are not valid. RMAN will fail if it tries to recover past the last archived redo log file:
Finally, restore the database. Then use the SWITCH command to switch the datafile names in the control file to their new names and locations, and then recover the database:
11. Open the database with RESETLOGS. Finally, open the database with the RESETLOGS option and you can proceed with testing the restored and recovered database:
CERTIFICATION OBJECTIVE 5.06
Recover Using a Backup Control File
In rare instances, you may lose all copies of the current control file. This is rare because you should have the control file multiplexed to several locations. Even if you do lose all copies of the current control file, you should have at least one autobackup of the control file from the most recent RMAN backup. In addition, if you are using a recovery catalog, all metadata within your most recent control file resides in the recovery catalog.
The SPFILE is also susceptible to loss if it does not reside on a mirrored external file system or on a mirrored ASM disk group. When RMAN performs a control file autobackup, both the current control file and SPFILE are backed up.
In the following sections, you’ll learn how to recover both the control file and the SPFILE if all online versions of either of these files are lost.
Restoring the SPFILE from the Autobackup
To restore the SPFILE from the autobackup, first set the DBID if the instance is not running when the SPFILE is lost:
Next, restart the database with a default SPFILE, as you did earlier in this chapter when you restored and recovered a database on a new host:
Next, restore the SPFILE from the autobackup to the original location:
Finally, start the database:
Restoring the Control File from the Autobackup
Restoring the control file from an autobackup is similar to the steps you use to restore an SPFILE from an autobackup. Here are the sample RMAN commands:
Note that since there is no control file, you have to open the database with NOMOUNT and then restore the control file. After you mount the database, you must recover the database, because the backup control file contains information about an older version of the database. For the same reason, you must open the database with RESETLOGS.
RMAN restores the control file to all locations specified by the initialization parameter CONTROL_FILES. If one or more of those locations are still not available, you will have to edit the CONTROL_FILES parameter to specify alternative locations or temporarily restore the control file to a different location:
EXERCISE 5-5
Restore the Control File from an Autobackup
In this exercise, all copies of the control file were accidentally deleted by an overly eager system administrator trying to free up disk space. Restore and recover the database with a control file restored from a control file and SPFILE autobackup:
1. Identify the control file locations where all copies of the control file used to reside:
2. Shut down the instance (if it is not already down), and reopen it in NOMOUNT mode:
3. Start RMAN and restore the control file from autobackup to the original locations:
A few points are worth noting here. RMAN can connect to the instance even if it is not mounted. In fact, RMAN has to connect to an unmounted database to be able to restore the control file. RMAN finds the control file autobackup in the flash recovery area and writes it to the three control file destinations specified by the CONTROL_FILES initialization parameter.
4. Mount the database, recover the database (to synchronize the datafiles with the restored control file), and open the database with RESETLOGS:
CERTIFICATION OBJECTIVE 5.07
Perform Disaster Recovery
Disaster recovery comes in many forms. If you are running your database in NOARCHIVELOG mode, you are more likely to suffer data loss when a datafile, control file, or all database-related files are lost. In other words, running in NOARCHIVELOG mode is a disaster waiting to happen.
If you have good reasons for running in NOARCHIVELOG mode (and there are few good reasons to run in NOARCHIVELOG mode), you can still recover some or most of the data, depending on how recently your backups were made. If you perform incremental backups in NOARCHIVELOG mode, you will be able to restore even more committed user data.
Ideally, your database is running in ARCHIVELOG mode, but you could still lose every database file from the target database. If you have an intact set of datafiles, archived redo log files, and at least one control file autobackup, you can recover all committed transactions up to and including the last archived redo log file.
In the following sections, you’ll see how to maximize the recoverability of a database in NOARCHIVELOG mode and learn the steps required to fully recover a database running in ARCHIVELOG mode.
Performing Recovery in NOARCHIVELOG Mode
As mentioned several times in this book, if not several times in this chapter, it is highly recommended that your database be running in ARCHIVELOG mode. If it is not and you lose even one datafile, you must restore the database from the most recent full backup, including all control files and datafiles. While the following procedure is straightforward, the database users must re-enter all changes made since the last backup:
1. Shut down the instance if it’s not already down.
2. Using operating system commands, restore all datafiles and control files to their original locations, or new locations if the original locations are no longer available.
3. Update the CONTROL_FILES parameter in the restored initialization parameter file if the restored control file is in a new location.
4. Start the database with STARTUP MOUNT.
5. Use ALTER DATABASE RENAME to change the location of datafiles and online redo log files if the original location is no longer available.
6. Run the RECOVER DATABASE UNTIL CANCEL command to mimic incomplete recovery and reset the online redo log files; specify CANCEL immediately.
7. Open the database with ALTER DATABASE OPEN RESETLOGS.
Using Incremental Backups in NOARCHIVELOG Mode
If you are running your database in NOARCHIVELOG mode, and you make regular incremental backups, you can take level 0 incremental backups and apply level 1 incremental backups to recover your database up to the point in time of your last incremental backup. The incremental backups must be consistent backups—in other words, you performed the backups while the database was in the MOUNT state. In addition, the database must be shut down with NORMAL, IMMEDIATE, or TRANSACTIONAL to ensure that all datafiles and the controlfile (and all multiplexed copies of the control files) have the synchronized System Change Numbers (SCNs).
Use the following RMAN commands to restore and recover the database with all incremental backups:
You need to specify NOREDO in the RECOVER DATABASE command only if the online redo log files are lost. If the current online redo log files contain all changes since the last incremental backup, you don’t need to specify NOREDO and all committed transactions from the online redo log files will be applied to the restored datafiles after the incremental backups are applied.
Performing Recovery of the Entire Database
In this scenario, your worst nightmare has come true: You have lost the entire target database, the recovery catalog database, all control files, all online redo log files, and all parameter files. However, all is not lost, because your database was in ARCHIVELOG mode and you have one of your control file autobackups, a backup of all datafiles, along with all archived redo log files created since the last backup. As a result, you can restore the database in much the same way as you restore and recover a database to a new host, as shown earlier in this chapter.
These steps assume that the Oracle database software is already installed on the new host and the directory structure on the new host is the same as the old host. Here are the steps with the relevant commands required:
1. Connect to RMAN without connecting to a recovery catalog using CONNECT TARGET /.
2. Set the DBID for the database using SET DBID.
3. Start the database with STARTUP NOMOUNT. RMAN uses a default SPFILE because it has not been restored yet.
4. Restore the SPFILE with RESTORE SPFILE FROM AUTOBACKUP.
5. Restart with the new SPFILE using STARTUP FORCE NOMOUNT.
6. Restore the control file from an autobackup using RESTORE CONTROLFILE FROM AUTOBACKUP.
7. Mount the database with ALTER DATABASE MOUNT.
8. Restore and recover the database with RESTORE DATABASE and RECOVER DATABASE.
9. Open the database with RESETLOGS.
CERTIFICATION SUMMARY
This chapter started out describing the most basic type of recovery operation for a database in ARCHIVELOG mode. For a noncritical datafile, you take the tablespace containing the datafile offline, restore the missing or damaged datafile, and then recover the datafile using archived and online redo log files. For a critical datafile, the procedure is much the same, except that you must shut down the database and start it in MOUNT mode before starting the recovery operation.
On some occasions, you’ll want to perform an incomplete recovery, typically because you want to roll back the database in time permanently due to numerous erroneous changes to the database that cannot be reversed using Oracle Flashback technology. You learned how to create a restore point as a marker for an SCN or a point in time, and then how to use that restore point later for an incomplete recovery operation.
Image copies should play an important role in your disaster recovery plan. To speed up the recovery process even more, you can apply subsequent incremental backups to an image copy so that any recovery operation using the image copy has to use only the archived and online redo log files to recover the database. You saw a script that you can use to keep your image copies up-to-date with every incremental backup.
Once you create your image copies and keep them updated with incremental updates, you want to be ready to use them. Typically, you will restore a backupset or image copy. An even faster method is to use the image copy in place and apply any recent incremental updates and archived redo log files. You learned how to switch to an image copy, recover the image copy, and how to switch it back to its original location.
When using an RMAN RUN block, you can use the SET NEWNAME command to specify an alternative location for a datafile during a restore operation. Once RMAN restores the datafile, you use the SWITCH command to update the control file and the recovery catalog with the new location for the datafile.
If you are not permanently moving a database to a new host, you can use the RMAN RESTORE and RECOVER commands to create a copy of the database on a new host quickly and easily. You use the original DBID on the copied database and make sure that you don’t connect to a recovery catalog with the copied database. Otherwise, you will compromise the integrity of the source database’s backup metadata.
Next, you saw the steps required to recover using a backup control file and a backup SPFILE. The procedure is much the same as, and reiterates the importance of, mirroring your control file and ensuring that you configure your RMAN backups with AUTOBACKUP set to ON.
Finally, you saw the high level steps required for recovering from the ultimate disaster: losing all datafiles, control files, and SPFILEs. The procedure is fairly straightforward and minimizes data loss, as long as your database is running in ARCHIVELOG mode. Many, if not all, of the steps required for performing disaster recovery were covered in separate sections earlier in the chapter.
TWO-MINUTE DRILL
Perform Complete Recovery from a Critical or Noncritical Data File Loss Using RMAN
Use RMAN RESTORE and RECOVER for complete recovery from a critical and noncritical data file loss.
Datafiles from the SYSTEM and UNDO tablespaces are critical datafiles.
When restoring and recovering a critical datafile, the database must be in MOUNT mode.
You can completely recover any datafile if the database is in ARCHIVELOG mode.
Perform Incomplete Recovery Using RMAN
You use restore points to recover a database to an SCN or a time in the past.
Use CREATE RESTORE POINT to create a restore point.
You must open the database with RESETLOGS if you perform incomplete recovery.
Recover Using Incrementally Updated Backups
You can recover image copies with more recent incremental level 1 backups.
RMAN automatically determines the best image copy to use if more than one are available.
Use tags with an incrementally updated image copy strategy to ensure that the correct incremental backup updates the image copy.
Switch to Image Copies for Fast Recovery
Using image copies skips the restore step and saves overall recovery time.
Use the RMAN command SWITCH TO . . . COPY to switch to the most recent image copy for a datafile, tablespace, or database.
RMAN automatically applies incremental backups and archived redo log files when you recover with an image copy.
Use the dynamic performance views V$TABLESPACE and V$DATAFILE_HEADER to determine the tablespace and datafile number needing recovery.
After switching to an image copy, you can switch back to an image copy at the original location when it becomes available.
You use the SET NEWNAME command in RMAN to identify new locations for restored datafiles.
After restoring one or more datafiles with RESTORE, you use the SWITCH command to update the control file and recovery catalog with the new datafile locations.
Restore a Database onto a New Host
Restoring a database to a new host is appropriate for disaster recovery testing or permanently moving the database to a new host.
The DUPLICATE command is more appropriate if you want to make a permanent copy of the database with a new DBID.
When connecting to the new database, do not connect to a recovery catalog.
The RMAN recovery script uses SET NEWNAME to specify new locations for each datafile.
Restore the database to the SCN of the last archived redo log file.
You must open the new database with RESETLOGS.
Recover Using a Backup Control File
You can use an RMAN autobackup to restore either an SPFILE or control file when all online copies are lost.
RMAN restores the control file to all locations specified by the initialization parameter CONTROL_FILES.
If the SPFILE is lost, RMAN uses a default SPFILE when you start the database with NOMOUNT.
Use RESTORE SPFILE FROM AUTOBACKUP to restore the SPFILE.
Use RESTORE CONTROLFILE FROM AUTOBACKUP to restore the control file.
When restoring a control file from autobackup you must open the database with RESETLOGS.
You can optionally restore a copy of the control file to an alternate location.
Perform Disaster Recovery
Data loss is likely to occur if you lose all datafiles and control files while in NOARCHIVELOG mode.
To perform disaster recovery in NOARCHIVELOG mode, use operating system commands to copy the closed database backup files to the original or alternative location.
Use RECOVER DATABASE UNTIL CANCEL to simulate incomplete recovery and to reinitialize the online redo log files.
After the recovery operation is complete, open the database with RESETLOGS.
You can use incremental backups in NOARCHIVELOG mode to minimize data loss.
Specify NOREDO in the RECOVER DATABASE command if all online redo log files are lost.
SELF TEST
The following questions will help you measure your understanding of the material presented in this chapter. Read all the choices carefully because there might be more than one correct answer. Choose all correct answers for each question.
Perform Complete Recovery from a Critical or Noncritical Data File Loss Using RMAN
1. What is the difference between a critical and a noncritical datafile in a recovery scenario?
A. To recover a critical datafile, only the tablespace containing the critical datafile must be offline.
B. To recover a noncritical datafile, both the SYSTEM tablespace and the tablespace containing the critical datafile must be offline.
C. To recover a critical datafile, the database must be in NOMOUNT mode. To recover a noncritical datafile, the database must be in MOUNT mode.
D. To recover a critical datafile, the database must be in MOUNT mode. To recover a noncritical datafile, the database can be open.
2. Which tablespaces contain critical datafiles that must be recovered when the database is offline?
A. SYSTEM and SYSAUX
B. SYSTEM and UNDO
C. SYSTEM, SYSAUX, and UNDO
D. SYSTEM and USERS
3. During complete recovery of a noncritical datafile, which of the following steps are not required? (Choose two answers.)
A. Use the RMAN RESTORE command to load the missing datafiles from backup.
B. Reopen the database with RESETLOGS.
C. Shut down the database and reopen in MOUNT mode.
D. Bring the tablespace containing the missing or damaged datafiles offline before the recovery operation and online after recovery is complete.
E. Use the RMAN RECOVER command to apply committed transactions from archived and online redo log files.
Perform Incomplete Recovery Using RMAN
4. Which of the following methods can you use to retrieve the current System Change Number (SCN)?
A. Query the CURRENT_SCN column from V$DATAFILE_HEADER.
B. Query the CURRENT_SCN column of the V$INSTANCE view.
C. Query the LAST_SCN column of the V$DATABASE view.
D. Query the CURRENT_SCN column of the V$DATABASE view.
E. Start RMAN and connect to the target database; the current SCN and the DBID are displayed.
5. Which of the following CREATE RESTORE POINT commands will preserve the restore point past the time specified by the initialization parameter CONTROL_FILE_RECORD_KEEP_TIME?
A. CREATE RESTORE POINT SAVE_IT_PAST KEEP
B. CREATE RESTORE POINT SAVE_IT_PAST AS OF SCN 3988943
C. CREATE RESTORE POINT SAVE_IT_NOW PRESERVE
D. CREATE RESTORE POINT SAVE_IT_NOW UNTIL FOREVER
6. Which operating system environment variables should be set when you use RMAN time-based incomplete recovery? (Choose two answers.)
A. ORACLE_SID
B. NLS_LANG
C. ORACLE_BASE
D. NLS_DATE_FORMAT
E. NLS_TIME_FORMAT
Recover Using Incrementally Updated Backups
7. You are implementing an incrementally updated backup strategy using the following RMAN script:
How many times do you need to run this script before the image copy is updated with an incremental level 1 backup?
A. Once
B. Twice
D. At least four times
Switch to Image Copies for Fast Recovery
8. The RMAN SWITCH command is equivalent to what SQL command?
A. ALTER SYSTEM RENAME FILE
B. ALTER DATABASE ARCHIVELOG
C. ALTER DATABASE OPEN RESETLOGS
D. ALTER SYSTEM SWITCH LOGFILE
9. You have these two commands within an RMAN RUN block:
What happens when the RESTORE command runs?
A. The command fails and the RUN block terminates because you need to run a SWITCH command first.
B. The control file is updated with the new location of the datafile.
C. The latest version of the datafiles for the USERS tablespace are restored to the location /u01/oradata/dw.
D. The latest version of the datafiles for the USERS tablespace are restored to the location /u06/oradata/dw.
Restore a Database onto a New Host
10. Which of the following are valid reasons to restore backups of your database to a new host? (Choose all that apply.)
A. Creating a new node in a RAC environment
B. Testing your disaster recovery plan
C. Creating another copy of your database
D. When the DUPLICATE command is not available
E. Permanently moving your database to a new host
11. When restoring a database to a new host, what is the first command you should run as part of the restoration process?
A. STARTUP NOMOUNT
B. SET DBID
C. RESTORE SPFILE FROM AUTOBACKUP
D. RESTORE CONTROLFILE FROM AUTOBACKUP
Recover Using a Backup Control File
12. Place the following commands in the correct order for restoring a control file from an RMAN autobackup:
1. RECOVER DATABASE
2. ALTER DATABASE OPEN RESETLOGS
3. STARTUP NOMOUNT
4. ALTER DATABASE MOUNT
5. RESTORE CONTROLFILE FROM AUTOBACKUP
A. 5, 3, 4, 1, 2
B. 3, 5, 4, 1, 2
C. 3, 5, 4, 2, 1
D. 5, 1, 3, 4, 2
13. When you run the RMAN RESTORE CONTROLFILE command, where does RMAN put the previous version of the control file? (Choose the best answer.)
A. To all available locations defined by the CONTROL_FILES initialization parameter
B. To the flash recovery area
C. To all locations defined by the CONTROL_FILES initialization parameter unless overridden with the TO ‘>filename<’ clause
D. To the first location defined by the CONTROL_FILES initialization parameter
Perform Disaster Recovery
14. Your database is running in NOARCHIVELOG mode, and you perform occasional incremental level 1 backups in MOUNT mode. To what point in time can you recover the database if you lose a datafile for the USERS tablespace?
A. You can recover the entire database up to the point in time of the last incremental level 1 backup.
B. You can recover the entire database up to the point in time of the last incremental level 0 backup.
C. You can recover the USERS tablespace up to the point in time of the last incremental level 1 backup.
D. You can recover the USERS tablespace up to the point in time of the last incremental level 0 backup.
SELF TEST ANSWERS
Perform Complete Recovery from a Critical or Noncritical Data File Loss Using RMAN
1. 
D. When you restore and recover a critical datafile, the entire database must be shut down and reopened in MOUNT mode to open the control file and make the datafile locations available to RMAN.
A is wrong because the entire database must be offline when recovering a critical datafile. B is wrong because recovering a noncritical datafile requires only the tablespace containing the missing or damaged datafile to be offline. C is wrong because the database must be in MOUNT mode to recover a critical datafile, and can be in OPEN mode to recover a noncritical datafile.
2. B. The SYSTEM and UNDO tablespaces contain critical datafiles and therefore require the database to be in MOUNT mode during the recovery process.
A, C, and D are wrong because the SYSAUX and USERS tablespaces do not contain critical datafiles.
3. B and C. The database does not need to be opened with RESETLOGS because you are not performing incomplete recovery. For a noncritical datafile, only the tablespace containing the missing or damaged datafile needs to be offline.
A, D, and E are wrong. These steps are all required.
Perform Incomplete Recovery Using RMAN
4. D. V$DATABASE contains the most recent SCN in the CURRENT_SCN column.
A and B are wrong because the column CURRENT_SCN does not exist in either V$DATAFILE_HEADER or V$INSTANCE. C is wrong because V$DATABASE does not have a column named LAST_SCN. E is wrong because when RMAN starts, it shows only the DBID and not the current SCN.
5. C. The keyword PRESERVE keeps the restore point past the time specified by CONTROL_FILE_RECORD_KEEP_TIME.
A is wrong because the keyword KEEP is not valid for the command. B is wrong because PRESERVE was not specified. D is wrong because UNTIL FOREVER is not valid for the command.
6. B and D. Both NLS_LANG and NLS_DATE_FORMAT must be set so that RMAN will correctly interpret date strings provided during a recovery operation.
A, C, and E are wrong. ORACLE_SID and ORACLE_BASE are required to connect to the correct database and database software, but they are not directly related to RMAN time-based recovery. NLS_TIME_FORMAT is not a valid environment variable.
Recover Using Incrementally Updated Backups
7. C. The first time the script runs, there is no level 0 image copy nor a level 1 incremental backup. The second time the script runs, the level 0 image copy exists but there is no incremental level 1 backup to apply to it. The third and successive time, the first incremental level 1 backup is applied to the image copy.
A, B, and D all specify the wrong number of executions.
Switch to Image Copies for Fast Recovery
8. A. Both the RMAN SWITCH and the SQL ALTER SYSTEM RENAME FILE commands update the location of the datafile in both the control file and the recovery catalog.
B is wrong because this command puts the database into ARCHIVELOG mode. C is wrong because the command is used only after incomplete recovery. D is wrong because the command switches online redo log files, not datafile names.
9. D. The SET NEWNAME specifies the new location for the datafile, and RESTORE puts the backup version of the datafile at the new location.
A is wrong because the datafile must be restored before the control file can be updated with SWITCH. B is wrong because only SWITCH will update the control file with the new location. C is wrong because the RESTORE command uses the new location specified with SET NEWNAME.
Restore a Database onto a New Host
10. B and E. Restoring your database to a new host is appropriate to test your disaster recovery plan or to move your database permanently to a new host since the procedure keeps the existing DBID.
A is wrong because you don’t need to restore copies of datafiles for new nodes in a RAC environment, only a new instance is created. C is wrong because the DBID remains the same on the new database, and this will cause conflicts in the recovery catalog. D is wrong because the DUPLICATE command is always available in RMAN to let you make a second copy of your database along with the required changes to the DBID in the new database.
11. B. The DBID must be set first so that the correct datafiles, SPFILE, and control file are restored from the source database backup location.
A, C, and D are wrong. All of these steps are valid for restoring a database to a new host but must be run after the DBID is set.
Recover Using a Backup Control File
12. B. The specified order is correct. You must open the database with RESETLOGS since your restored control file has information about an older version of the database.
A, C, and D specify an incorrect sequence of commands.
13. C. The command restores the control file from autobackup to all locations defined by the initialization parameter CONTROL_FILES. If any of those locations are unavailable, change the value of CONTROL_FILES or use the TO ‘>filename<’ option.
A is wrong because the command fails if any of the locations defined by CONTROL_FILES are not available. B is wrong because the autobackup of the control file will most likely originate from the flash recovery area. D is wrong because RMAN restores the control file to all locations defined by CONTROL_FILES.
Perform Disaster Recovery
14. A. If you are not running in ARCHIVELOG mode, you must restore the entire database, including control files. You can recover it up to the point in time of the last incremental level 1 backup in MOUNT mode.
B is wrong because you can apply incremental level 1 backups to the database, even in NOARCHIVELOG mode. C and D are wrong because you must recover the entire database when you are running in NOARCHIVELOG mode.