C H A P T E R  19

RMAN Restore and Recovery

I was out on a long Saturday morning bike ride last year. About half way through the ride, my cell phone rang. It was one of the data center operational support technicians. He told me that a mission critical database server was acting strange and that I should logon as soon as possible and make sure things were okay. I told him that I was about 15 minutes from being able to logon. So I scurried home as fast as I could to check out the production box. When I got home and logged onto the database servers, I tried to start SQL*Plus and immediate got an error indicating that the SQL*Plus binary file had corruption. Great. I couldn't even log into SQL*Plus. This was not good.

I had the SA restore the Oracle binaries from an operating system backup. I started SQL*Plus. The database had crashed so I attempted to start the database. The output indicated that there was a media failure with all of the datafiles. After some analysis, it was discovered that there had been some file system issues and that all of these files on disk were corrupt:

• Data files
• Control files
• Archive redo logs
• Online redo log files
• RMAN backup pieces on disk

This was almost a total disaster. My director asked about our options. I responded, “All we have to do is restore the database from our last tape backup and we'll lose whatever data is in archive redo logs that hadn't been backed up to tape yet.”

The storage administrators were called in and instructed to restore the last set of RMAN backups that had been written to tape. About 15 minutes later, we could hear the tape guys talking to each other in hushed voices. One of them said, “We are sooooo hosed. We don't have any tape backups of RMAN for any databases on this box.”

That was a dark moment. The worst case scenario was to rebuild the database from DDL scripts and lose three years of production data. Not a very palatable option.

After looking around the production box, I discovered that the prior production support DBA (ironically, this DBA had just been let go a few days prior due to budget cuts) had implemented a job to copy the RMAN backups to another server in the production environment. The RMAN backups on this other server were intact. I was able to restore and recover the production database from these backups. We lost about a days worth of data (between corrupt archive logs and downtime when no incoming transactions were allowed), but we were able to get the database restored and recovered about 20 hours after the initial phone call. That was a long day.

Most situations in which you need to restore and recovery will not be as bad as the one just described. However, the previous scenario does highlight the need for:

• A Backup strategy.
• A DBA with backup & recovery skills.
• A restore and recovery strategy, including a requirement to periodically test the restore and recovery.

This chapter walks you through restore and recovery using RMAN. It covers many of the common tasks you will have to perform when dealing with media failures.

Determining Media Recovery Required

The term “media recovery” refers to the need to restore files that have been lost or damaged due to failure of the underlying storage media (usually a disk of some sort). Usually, you know that media recovery is required by some sort of an error like the following:

The error may be displayed on your screen when performing DBA tasks such as stopping and starting the database. Or you might see such an error in a trace file or the alert.log file. If you don't notice the issue right away, with a severe media failure, the database will stop processing transactions and users will start calling you.

To understand how Oracle is determining that media recovery is required, you must understand how Oracle determines that everything is okay. When Oracle shuts down normally (IMMEDIATE, TRANSACTIONAL, NORMAL), part of the shutdown process is to flush all modified blocks to disk and mark the header of each datafile with the current SCN and to update the control file with the current SCN information.

Upon startup, Oracle checks to see if the SCN in the control file matches the SCN in the header of the datafile. If there is a match, then Oracle attempts to open the datafiles and online redo log files. If all files are available and can be opened, Oracle starts normally. The following query compares the SCN in the control file (for each datafile) with the SCN in the datafile header:

If the control file SCN values are greater than the datafile SCN values, then media recovery is most likely required.

Determining What to Restore

Media recovery requires that you perform manual tasks to get your database back in one piece. These tasks usually involve a combination of RESTORE and RECOVER commands. You will have to issue an RMAN RESTORE command if your datafiles have experienced media failure. This could be because of somebody accidentally deleting files or a disk failure.

How the Process Works

When you issue the RESTORE command, RMAN will automatically determine how to extract the datafiles from any of the following available backups:

• Full database backup
• Incremental level 0 backup
• Image copy backup generated by BACKUP AS COPY command

After the files are restored from a backup, you are required to apply redo to them via the RECOVER command. When you issue the RECOVER command, Oracle will examine the SCNs in the affected datafiles and determine whether any of them need to be recovered. If the SCN in the datafile is less than the corresponding SCN in the control file, then media recovery will be required.

Oracle will retrieve the datafile SCN and then look for the corresponding SCN in the redo stream to determine where to start the recovery process. If the starting recovery SCN is in the online redo log files, the archived redo log files are not required for recovery.

During a recovery, RMAN will automatically determine how to apply redo. First, RMAN will apply any incremental backups available that are greater than zero, such as the incremental level 1. Next, any archived redo log files on disk will be applied. If the archived redo log files do not exist on disk,  RMAN will attempt to retrieve them from a backup set.

To be able to perform a complete recovery, all of the following conditions need to be true:

• Your database is in archivelog mode.
• You have a good baseline backup of your database.
• You have any required redo that has been generated since the backup (archived redo log files, online redo log files, or incremental backups that RMAN can use for recovery instead of applying redo).

There are a wide variety of restore and recovery scenarios. How you restore and recover depends directly on your backup strategy and what files have been damaged. Listed next are the general steps to follow when facing a media failure:

1. Determine what files need to be restored.
2. Depending on the damage, set your database mode to nomount, mount, or open.
3. Use the RESTORE command to retrieve files from RMAN backups.
4. Use the RECOVER command for datafiles requiring recovery.
5. Open your database.

Your particular restore and recovery scenario may not require that all of the previous steps be performed. For example, you may just want to restore your spfile, which doesn't require a recovery step.

The first step in a restore and recovery process is to determine what files have experienced media failure. You can usually determine what files need to be restored from the following sources:

• Error messages displayed on your screen, either from RMAN or SQL*Plus
• Alert.log file and corresponding trace files
• Data dictionary views

If you're using Oracle Database 11g or higher, then in addition to the previously listed methods you should consider the Data Recovery Advisor for obtaining information about the extent of a failure and corresponding corrective action.

Using Data Recovery Advisor

The Data Recovery Advisor tool was introduced in Oracle Database 11g. In the event of a media failure, this tool will display the details of the failure, recommend corrective actions, and it will perform the recommended actions if you specify it to do so. It's like having another set of eyes to provide feedback when in a restore and recovery situation. There are three modes to Data Recovery Advisor:

• Listing failures.
• Suggesting corrective action.
• Running commands to repair failures.

The Data Recovery Advisor is invoked from RMAN. You can think of the Data Recovery Advisor as a set of RMAN commands that can assist you when dealing with media failures.

Listing Failures

When using the Data Recovery Advisor, the LIST FAILURE command is used to display any issues with the datafiles, control files, or online redo logs:

If there are no detected failures, you'll see a message indicating that there are no failures. Here is some sample output indicating that there may be an issue with a datafile:

The prior message doesn't indicate which specific file may be experiencing a failure. To dig a little deeper, use the DETAIL clause:

This output details which file has experienced a failure and the nature of the problem (file missing).

Suggesting Corrective Action

The ADVISE FAILURE command gives advice about how to recover from potential problems detected by the Data Recovery Advisor. If you have multiple failures with your database, you can directly specify the failure ID to get advice on a given failure like so:

Here is some sample output for this particular issue:

In this case, the Data Recovery Advisor created a script that can be used to potentially fix the problem. The contents of the repair script can be viewed with an operating system editor. For example:

Here are the contents of the script (for this particular example):

After reviewing the script, you can decide to manually run the suggested commands, or you can have the Data Recovery Advisor run the script via the REPAIR command (see the next section for details).

Repairing Failures

If you have identified a failure and viewed the recommended advice, you can proceed to actually repairing a failure. If you want to inspect what the REPAIR FAILURE command will do without actually running the commands, use the PREVIEW clause. Before you run the command, make sure you first run the LIST FAILURE and ADVISE FAILURE commands from the same connected session. In other words, the RMAN session that you're in must run the LIST and ADVISE commands within the same session before running the REPAIR command.

If you're satisfied with the repair suggestions, then run the REPAIR FAILURE command.

You'll be prompted at this point for confirmation.

Type in YES to proceed.

If all goes well, you should see a final message like this:

In this way, you can use the RMAN commands of LIST FAILURE, ADVISE FAILURE, and REPAIR FAILURE to resolve media failures. The Data Recovery Advisor can assist with most issues that you'll run into. I did have a couple scenarios where the following recommendation was provided:

This output indicates that you (the DBA) aren't quite out of a job yet. ;)

Using RMAN to Stop/Start Oracle

You can use RMAN to stop and start your database with methods that are almost identical to those available through SQL*Plus. When performing restore and recovery operations, it's often more convenient to stop and start your database from within RMAN. The following RMAN commands can be used to stop and start your database:

• SHUTDOWN
• STARTUP
• ALTER DATABASE

Shutting Down

The SHUTDOWN command works the same from RMAN as it does from SQL*Plus. There are four types of shutdown: ABORT, IMMEDIATE, NORMAL, and TRANSACTIONAL. I usually first attempt to use SHUTDOWN IMMEDIATE to stop a database. If that doesn't work, don't hesitate to use SHUTDOWN ABORT. Here are some examples:

If you don't specify a shutdown option, NORMAL is the default. Shutting a database down with NORMAL is rarely viable as this mode waits for currently connected users to disconnect at their leisure. I never use NORMAL when shutting down a database.

Starting Up

As with SQL*Plus, you can use a combination of STARTUP and ALTER DATABASE commands to step the database through startup phases using RMAN, like so:

Here's another example:

If you want to start the database with restricted access, use the DBA option:

Complete Recovery

Complete recovery means that you can restore all transactions that were committed before the failure occurred. Complete recovery does not mean that you are restoring and recovering all datafiles in your database. For example, you can perform a complete recovery if you have a media failure with one datafile, and you restore and recover the one datafile. For complete recovery, the following conditions must be true:

• Your database is in archivelog mode.
• You have a good baseline backup of your database.
• You have any required redo that has been generated since the last backup.
• All archive redo logs start from the point that the last online backup began.
• If using, any incremental backups that RMAN can use for recovery must be available
• Online redo logs that contain transactions that have not yet been archived must be available.

If you've experienced a media failure and you have the required files to perform a complete recovery, then you can restore and recover your database.

Testing Restore and Recovery

You can determine which files RMAN will use for restore and recovery before you actually perform the restore and recovery. You can also instruct RMAN to verify the integrity of the backup files that will be used for restore and recovery.

Previewing Backups Used for Recovery

Use the RESTORE...PREVIEW command to list the backups and archive redo log files that RMAN will use to restore and recover database datafiles. The RESTORE...PREVIEW does not actually restore any files; rather, it lists out the backup files that will be used for a restore operation. This example previews in detail the backups required for restore and recovery for the entire database:

You can also preview require backup files at a summarized level of detail:

Here is a snippet of the output:

Here are some more examples of how to preview backups required for restore and recovery:

Validating Backup Files Before Restoring

There are several levels of verification that you can perform on backup files without actually restoring anything. If you just want RMAN to verify that the files exist and check the file headers, the use the RESTORE...VALIDATE HEADER command like so:

This command only validates the existence of files and the file headers. You can further instruct RMAN to verify the integrity of blocks within backup files required to restore the database datafiles via the RESTORE...VALIDATE command (sans the HEADER clause). Again, RMAN will not restore any datafiles in this mode:

This command only checks for physical corruption within the backup files. You can also check for logical corruption (along with physical corruption) as follows:

Here are some other examples of using RESTORE...VALIDATE:

Testing Media Recovery

The prior sections covered reporting and verifying the restore operations. You can also instruct RMAN to verify the recovery process via the RECOVER...TEST command. Before performing a test recovery, you need to ensure that the datafiles being recovered are offline. Oracle will throw an error for any online datafiles being recovered in test mode.

In this example, the tablespace USERS is restored first, and then a trial recovery is performed:

If there are any missing archive redo logs that are required for recovery, the following error is thrown:

If the testing of the recovery succeeded, you will messages like the following, indicating the application of redo was tested but not applied:

Here are some other examples of testing the recovery process:

Restoring Entire Database

The RESTORE DATABASE command will restore every datafile in your database. The exception to this is when RMAN detects that datafiles have already been restored; in that case, it will not restore them again. If you want to override that behavior, use the FORCE command.

When you issue the RECOVER DATABASE command, RMAN will automatically apply redo to any datafiles that need recovery. The recovery process includes applying changes found in the following:

• Incremental backup pieces (applicable only if using incremental backups)
• Archived redo log files (generated since the last backup or last incremental backup that is applied)
• Online redo log files (current and unarchived)

You can open your database after the restore and recovery process is complete. Complete database recovery works only if you have good backups of your database and have access to all redo generated after the backup was taken. You need all the redo required to recover the database datafiles. If you don't have all the required redo, then you'll most likely have to perform an incomplete recovery (covered later in this chapter).

You can perform a complete database-level recovery with either the current control file or a backup control file.

Using Current Control File

You must first put your database in mount mode to perform a database-wide restore and recovery. This is because the SYSTEM tablespace datafile(s) must be offline when being restored and recovered. Oracle won't allow you to operate your database in open mode with datafiles associated with the SYSTEM tablespace offline. In this situation, start up the database in mount mode, issue the RESTORE and RECOVER commands, and then open the database like so:

If everything went as expected, the last message you should see is this:

Using Backup Control File

This solution uses a backup of the control file retrieved from the fast recovery area. For more examples of how to restore your control file, see the “Restoring Control Files” section of this chapter. In this scenario, the control file is first retrieved from a backup before restoring and recovering the database:

If everything went as expected, the last message you should see is this:

Restoring Tablespaces

Sometimes you'll have media failures that are localized to a particular tablespace or set of tablespaces. In these circumstances, it's appropriate to restore and recover at the tablespace level of granularity. The RMAN RESTORE TABLESPACE and RECOVER TABLESPACE commands will restore and recover all datafiles associated with the specified tablespace(s).

Restoring Tablespace While Database is Open

If your database is open, then you must take offline the tablespace you want to restore and recover. You can do this for any tablespaces except for SYSTEM and UNDO. This example restores and recovers the USERS tablespace while database is open:

After the tablespace is brought online, you should see a message similar to this:

Restoring Tablespace While Database in Mount Mode

Usually when performing a restore and recovery, DBAs will shut down the database re-start the database in mount mode in preparation to perform the recovery. While a database is mount mode this ensures that no users are connecting to the database and also ensures that no transactions are transpiring. This next example restores the SYSTEM tablespace while the database is in mount mode:

If everything was successful, the last message you should see is this:

Restoring Read-Only Tablespaces

RMAN will restore read-only tablespaces along with the rest of the database when you issue a RESTORE DATDABASE command. For example, the following command will restore all datafiles (including those in read-only mode):

Prior to Oracle Database 11g, you were required to issue RESTORE DATABASE CHECK READONLY to instruct RMAN to restore read-only tablespaces along with tablespaces in read-write mode. This is no longer a requirement in Oracle Database 11g or higher.

Restoring Temporary Tablespaces

Starting with Oracle Database 10g, you don't have to restore or re-create missing locally managed temporary tablespace tempfiles. When you open your database for use, Oracle automatically detects and re-creates locally managed temporary tablespace tempfiles.

When Oracle automatically re-creates a temporary tablespace, it will log a message to your target database alert.log similar to the following:

If for any reason your temporary tablespace becomes unavailable, you can also re-create it yourself. Since there are never any permanent objects in temporary tablespaces, you can simply re-create them as needed. Here is an example of how to create a locally managed temporary tablespace:

If your temporary tablespace exists but the temporary datafiles are missing, you can simply add the temporary datafile(s) as shown here:

Restoring Datafiles

A datafile-level restore and recovery works well when a media failure is isolated to a small set of datafiles. With datafile-level recoveries, you can instruct RMAN to restore and recover either with datafile number or the datafile name. For datafiles not associated with the SYSTEM or UNDO tablespaces, you have the option of restoring and recovering while the database remains open. While the database is open, you have to first take offline any datafiles being restored and recovered.

Restoring Datafile While Database Is Open

Use the RESTORE DATAFILE and RECOVER DATAFILE commands to restore and recover at the datafile level. When your database is open, you're required to take offline any datafiles you're attempting to restore and recover. This example restores and recovers datafiles 32 and 33 while the database is open:

You can also specify the name of the datafile that you want to restore and recover. In this example, the mvdata01.dbf datafile is restored and recovered:

Restoring Datafile While Database Is Not Open

In this scenario, the database is first shut down and then started in mount mode. You can restore and recover any datafile in your database while the database is not open. This example shows restoring the datafile 1, which is associated to the SYSTEM tablespace:

You can also specify the filename when performing a datafile recovery:

Restoring Datafiles to Non-Default Locations

Sometimes a failure will occur that renders the disks associated with a mount point inoperable. In situations like this, you will need to restore and recover the datafiles to a different location from where they originally resided. Another typical need for restoring datafiles to non-default locations is that you're restoring to a different database server where the mount points are completely different from the server where the backup originated.

Use the SET NEWNAME and SWITCH commands to restore datafiles to non-default locations. Both of these commands must be run from within an RMAN run{} block. You can think of using SET NEWNAME and SWITCH as a way to rename datafiles (similar to the SQL*Plus ALTER DATABASE RENAME FILE statement).

This example changes the location of datafiles 32 and 33:

This is a partial listing of the output:

If the database is open, you can place the datafiles offline and then set their new names for restore and recovery like so:

You should now see a message similar to the following:

Performing Block Level Recovery

Block-level corruption is rare and is usually caused by some sort of I/O error. However, if you do have an isolated corrupt block within a large datafile, it's nice to have the option of performing a block-level recovery. Block-level recovery is useful when a small number of blocks are corrupt within a datafile. Block recovery is not appropriate if the entire datafile needs media recovery.

RMAN will automatically detect corrupt blocks whenever a BACKUP, VALIDATE, or BACKUP VALIDATE command is run. Details on corrupt blocks can be viewed in the V$DATABASE_BLOCK_CORRUPTION view. In the following example, the regular backup job has reported a corrupt block in the output:

Querying the V$DATABASE_BLOCK_CORRUPTION view indicates which file contains corruption:

Your database can be either mounted or open when performing block level recovery. You do not have to take the datafile being recovered offline. You can instruct RMAN to recover all blocks reported in V$DATABASE_BLOCK_CORRUPTION as shown:

If successful, the following message is displayed:

Another way to recover the block is to specify the datafile and block number like so:

It's preferable to use the RECOVER CORRUPTION LIST syntax because it will clear out any blocks recovered from the V$DATABASE_BLOCK_CORRUPTION view.

Block-level media recovery allows you to keep your database available and also reduces the mean time to recovery since only the corrupt blocks are offline during the recovery. Your database must be in archivelog mode for performing block-level recoveries. In Oracle Database 11g, RMAN can restore the block from the flashback logs (if available). If the flashback logs are not available, then RMAN will attempt to restore the block from a full backup, a level 0 backup, or an image copy backup generated by BACKUP AS COPY command. After the block has been restored, any required archived redo logs must be available to recover the block. RMAN can't perform block media recovery using incremental level 1 (or higher) backups.

Restoring Archive Redo Log Files

RMAN will automatically restore any archived redo log files that it needs during a recovery process. You normally don't need to manually restore archived redo log files. However, you may want to manually restore the archived redo log files if any of the following situations apply:

• You need to restore archived redo log files in anticipation of later performing a recovery; the idea is that if the archived redo log files are already restored, it will speed up the recovery operation.
• You're required to restore the archived redo log files to a non-default location, either because of media failure or because of storage space issues.
• You need to restore specific archived redo log files because you want to inspect them via LogMiner.

If you've enabled a flash recovery area, then RMAN will by default restore archived redo log files to the destination defined by the initialization parameter DB_RECOVERY_FILE_DEST. Otherwise, RMAN uses the LOG_ARCHIVE_DEST_1 initialization parameter to determine where to restore the archived redo log files.

If you restore archived redo log files to a non-default location, RMAN knows the location they were restored to and automatically finds these files when you issue any subsequent RECOVER commands. RMAN will not restore archived redo log files that it determines are already on disk. Even if you specify a non-default location, RMAN will not restore an archived redo log file to disk if the file already exists. In this situation, RMAN will simply return a message stating that the archived redo log file has already been restored. Use the FORCE option to override this behavior.

If you are uncertain of the sequence numbers to use during a restore of log files, you can query the V$LOG_HISTORY view or issue an RMAN LIST BACKUP command for more information.

Restoring to the Default Location

The following command will restore all archived redo log files that RMAN has backed up:

If you want to restore from a specified sequence, use the FROM SEQUENCE clause. You may want to run this query first to determine the most recent log files and sequence numbers that have been generated:

This example restores all archived redo log files from sequence 68:

If you want to restore a range of archived redo log files, use the FROM SEQUENCE and UNTIL SEQUENCE clauses or the SEQUENCE BETWEEN clause, as shown here. The following commands restore archived redo log files from sequence 68 through (and including) sequence 78 using thread 1:

By default, RMAN won't restore an archived redo log file if it is already on disk. You can override this behavior if you use the FORCE like so:

Restoring to a Nondefault Location

Use the SET ARCHIVELOG DESTINATION clause if you want to restore archived redo log files to a different location than the default. The following example restores to the non-default location of /ora01/archtemp. This option of the SET command must be executed from within an RMAN run RMAN run{} block.

Restoring the Spfile

You might want to restore a spfile for several different reasons:

• You accidentally set a value in the spfile that keeps your instance from starting.
• You accidentally deleted the spfile.
• You are required to see what it looked like at some time in the past.

One scenario (this has happened to me more than once) is that you're using a spfile and one of the DBAs on your team does something inexplicable like this:

The parameter is changed in the pfile on disk but not in memory. Several months later, the database is stopped for some maintenance. When attempting to start the database, you can't even get the instance to start in a NOMOUNT state. This is because a parameter has been set to a ridiculous value that will consume all memory on the box. In this scenario, the instance may hang or you might see this message:

If you are using a recovery catalog, it's a fairly simple procedure to restore the spfile:

If you're not using a recovery catalog, there are a number of ways to restore your spfile. The approach you take depends on several variables such as:

• If you're using a FRA
• If you've configured a channel backup location for the autobackup
• If you're using the default location for autobackups

I'm not going to show every detail of all of these scenarios. Usually I  determine the location of the backup piece that contains the backup of the spfile and do the restore like this:

You should see a message similar to this:

In this example, I knew that a FRA was in use and located the latest backup file in the autobackup directory and used it.

Restoring a Control File

If you are missing one control file and you have multiple copies, then you can shutdown your database, and simply restore the missing or damaged control file by copying a good control file to the correct location and name of the missing control file (see Chapter 5 for details).

The following sections cover these specific scenarios when restoring a control file:

• Using a Recovery Catalog.
• Using an autobackup.
• Specifying a backup file name.

Using a Recovery Catalog

When you're connected to the recovery catalog, you can view backup information about your control files even while your target database is in NOMOUNT mode. To list backups of your control files, use the LIST command as shown here:

If you are missing all of your control files and you are using a recovery catalog, then issue STARTUP NOMOUNT and issue the RESTORE CONTROLFILE command. In this example, the recovery catalog owner and password are both rcat and the name of the recovery catalog is recov. You'll have to change those values to match the username/password@service in your environment.

RMAN restores the control files to the location defined by your CONTROL_FILES initialization parameter. You should see a message indicating that your control files have been successfully copied back from an RMAN backup piece. Here's a partial listing of RMAN's message stack after a successful control file restore:

You can now alter your database into mount mode and perform any additional restore and recovery commands required for your database.

Using an Autobackup to Restore

When you enable the autobackup of your control file and are using a fast recovery area, restoring your control file is fairly simple. First, connect to your target database, then issue a STARTUP NOMOUNT command, and lastly issue the RESTORE CONTROLFILE FROM AUTOBACKUP command like so:

RMAN restores the control files to the location defined by your CONTROL_FILES initialization parameter. You should see a message indicating that your control files have been successfully copied back from an RMAN backup piece. Here is a partial snippet of the output:

You can now alter your database into mount mode and perform any additional restore and recovery commands required for your database.

Specifying a Filename

When restoring a database to a different server, the first few steps in the process usually are: take a backup of the target database, copy to the remote server, and then restore the control file from the RMAN backup. In these scenarios, I usually know the name of the backup piece that contains the control file. Here is an example where you instruct RMAN to restore a control file from a specific backup piece file:

The control file will be restored to the location defined by the CONTROL_FILES initialization parameter.

Incomplete Recovery

Incomplete database recovery means that you cannot recover all committed transactions. Incomplete means that you do not apply all redo to restore to the point of the last committed transaction that occurred in your database. In other words, you are restoring and recovering to a point in time in the past. For this reason, incomplete database recovery is also called database point-in-time recovery (DBPITR). Usually you perform incomplete database recovery because of one of the following reasons:

• You don't have all the redo required to perform a complete recovery. You're missing either the archived redo log files or online redo log files that are required for complete recovery. This situation could arise because the required redo files are damaged or missing.
• You purposely want to roll the database back to a point in time. For example, you would do this if somebody accidentally truncated a table and you intentionally wanted to roll the database back to just before the truncate table command was issued.

Incomplete database recovery consists of two steps: restore and recovery. The restore step will re-create datafiles, and the recover step will apply redo up to the specified point in time. The restore process can be initiated from RMAN in several ways:

• RESTORE DATABASE UNTIL
• RESTORE TABLESPACE UNTIL
• FLASHBACK DATABASE

For the majority of incomplete database recovery circumstances, you use the RESTORE DATABASE UNTIL command to instruct RMAN to retrieve datafiles from the RMAN backup files. This type of incomplete database recovery is the main focus of this chapter. The UNTIL portion of the RESTORE DATABASE command instructs RMAN to retrieve datafiles from a point in the past based on one of the following methods:

• Time
• Change (sometimes called system change number or SCN)
• Log sequence number
• Restore point

The RMAN RESTORE DATABASE UNTIL command will retrieve all datafiles from the most recent backup set or image copy. RMAN will automatically determine from the UNTIL clause which backup set contains the required datafiles. If you omit the UNTIL clause of the RESTORE DATABASE command, RMAN will retrieve datafiles from latest available backup set or image copy. In some situations, this may be the behavior you desire. I recommend you use the UNTIL clause to ensure that RMAN restores from the correct backup set. When you issue the RESTORE DATABASE UNTIL command, RMAN will determine how to extract the datafiles from any of the following:

• Full database backup
• Incremental level 0 backup
• Image copy backup generated by the BACKUP AS COPY command

You cannot perform an incomplete database recovery on a subset of your database's online datafiles. When performing incomplete database recovery, all of the checkpoint SCNs for all online datafiles must be synchronized before you can open your database with the alter database open resetlogs command. You can view the datafile header SCNs and the status of each datafile via this SQL query:

The only exception to this rule of not performing an incomplete recovery on a subset of online database files is a tablespace point-in-time recovery (TSPITR), which uses the RECOVER TABLESPACE UNTIL command. TSPITR is used in rare situations; it restores and recovers only the tablespace(s) you specify.

The recovery portion of an incomplete database recovery is always initiated with the RECOVER DATABASE UNTIL command. RMAN will automatically recover your database to the point specified with the UNTIL clause. Just like the RESTORE command, you can recover until time, change/SCN, log sequence number, or restore point. When RMAN reaches the specified point, it will automatically terminate the recovery process.

During a recovery, RMAN will automatically determine how to apply redo. First, RMAN will apply any incremental backups available. Next, any archived redo log files on disk will be applied. If the archived redo log files do not exist on disk, then RMAN will attempt to retrieve them from a backup set. If you want to apply redo as part of an incomplete database recovery, the following conditions must be true:

• Your database must be in archivelog mode.
• You must have a good backup of all datafiles.
• You must have all redo required to restore up to the specified point.

When performing an incomplete database recovery with RMAN, you must have your database in mount mode. RMAN needs the database in mount mode to be able to read and write to the control file. Also, with an incomplete database recovery, the system datafile is always one of the datafiles being recovered. The SYSTEM tablespace's datafile(s) must be offline while it is being recovered. Oracle will not allow your database to be open while this is happening.

Depending on your scenario, you can use RMAN to perform a variety of incomplete recovery methods. The next section discusses how to determine what type of incomplete recovery to perform.

Determining the Type of Incomplete Recovery

Time-based restore and recovery is commonly used when you know the approximate date and time to which you want to recover your database. For example, you may know approximately the time you want to stop the recovery process but not a particular SCN.

Log sequence–based and cancel-based recovery work well in situations where you have missing or damaged log files. In such scenarios, you can recover only up to your last good archived redo log file.

SCN-based recovery works well if you can pinpoint the SCN at which you want to stop the recovery process. You can retrieve SCN information from views such as V$LOG and V$LOG_HISTORY. You can also use tools such as LogMiner to retrieve the SCN of a particular SQL statement.

Restore point recoveries work only if you have established restore points. In these situations, you restore and recover up to the SCN associated with the specified restore point.

Tablespace point-in-time recovery is used in situations where you can restore and recover just a few tablespaces. You can use RMAN to automate many of the tasks associated with this type of incomplete recovery.

Performing Time-Based Recovery

To restore and recover your database back to a point in time, you can use either the UNTIL TIME clause of the RESTORE and RECOVER commands or the SET UNTIL TIME clause within a run{} block. RMAN will restore and recover the database up to, but not including, the specified time. In other words, RMAN will restore any transactions committed prior to the time specified. RMAN automatically stops the recovery process when it reaches the time you specified.

The default date format that RMAN expects is YYYY-MM-DD:HH24:MI:SS. However, I recommend using the TO_DATE function and specifying a format mask. This eliminates ambiguities with different national date formats and having to set the operating system NLS_DATE_FORMAT variable. The following example specifies a time when issuing the restore and recover commands:

If everything went well, you should now see output similar to this:

Performing Log Sequenced-Based Recovery

Usually this type of incomplete database recovery is initiated because you have a missing or damaged archived redo log file. If that's the case, you can recover only up to your last good archived redo log file, because you cannot skip a missing archived redo log.

How you determine which archived redo log file to restore up to (but not including) will vary by situation. For example, if you are physically missing an archived redo log file and if RMAN can't find it in a backup set, then you'll receive the following message when trying to apply the missing file:

Based on the previous error message, you would restore up to (but not including) log sequence 45.

If everything went well, you should now see output similar to this:

Performing Change/SCN-Based Recovery

SCN-based incomplete database recovery works in situations where you know the SCN value up to where you want to end the restore and recovery session. RMAN will recover up to, but not including, the specified SCN. RMAN automatically terminates the restore process when it reaches the specified SCN.

You can view your database SCN information in several ways:

• By using LogMiner to determine an SCN associated with a DDL or DML statement.
• By looking in the alert.log file.
• By looking in your trace files.
• By querying the FIRST_CHANGE# column of V$LOG, V$LOG_HISTORY, and V$ARCHIVED_LOG.

After establishing the SCN to which you want to restore, use the UNTIL SCN clause to restore up to, but not including, the SCN specified. The following example restores all transactions that have an SCN that is less than 950:

If everything went well, you should now see output similar to this:

Restoring to a Restore Point

There are two types of restore points: normal and guaranteed. Guaranteed restore points require that you have the flashback database feature enabled. You can create a normal restore point using SQL*Plus as follows:

This command creates a restore point named MY_RP that is associated with the SCN of the database at the time the command was issued. You can view the current SCN of your database as shown here:

You can view restore point information in the V$RESTORE_POINT view like so:

The restore point acts like a synonym for the particular SCN. It allows you to restore and recover to an SCN without having to specify a number. RMAN will restore and recover up to, but not including, the SCN associated with the restore point.

This example restores and recovers to the MY_RP restore point:

Restoring and Recovering to Different Server

When you think about backups, you must as the same time think about restore and recovery. Your backups are only as good as the last time you tested a restore and recovery. A backup strategy can be rendered worthless without a good restore and recovery strategy. The last thing you want to happen is to have a media failure, go to restore your database, and then find out you're missing critical pieces, don't have enough space to restore, learn that something is corrupt, and so on.

One of the best ways to test an RMAN backup is to restore and recover it to a different database server. This will exercise all of your backup, restore, and recovery DBA skills. If you can restore and recover an RMAN backup on a different server, it will give you confidence when a real disaster hits. You can think of all of the prior material in this book as the building blocks for performing technically challenging tasks. Moving a database from one server to another using an RMAN backup requires an expert level understanding of the Oracle architecture and how backup and recovery works.

In this example, the originating server and destination server have completely different mount points and disk layouts. Listed next are the high-level steps required to take an RMAN backup and use it to recreate a database on a separate server:

1. Create an RMAN backup on the originating database.
2. Copy RMAN backup to the destination server. All steps after this step are performed on the destination database server.
3. Ensure that Oracle is installed.
4. Source the required OS variables.
5. Create an init.ora file for the database to be restored.
6. Create any required directories for datafiles, control files, and dump/trace files.
7. Startup the database in NOMOUNT mode.
8. Restore a control file from the RMAN backup.
9. Startup the database in MOUNT mode.
10. Make the control file aware of the location of the RMAN backups.
11. Rename and restore the datafiles to reflect new directory locations.
12. Recover the database.
13. Set the new location for the online redo logs.
14. Open the database.
15. Add tempfile.
16. Rename the database.

Each of the prior steps is covered in detail in the next several sections. Steps 1 and 2 occur on the source database server. All other steps are performed on the destination server. For this example, the source database is named E64202, and the destination database will be named O11DEV.

On the source database, the location of the datafiles, control files, and online redo logs are all in this directory:

The source database archive redo log file location is here:

The RMAN backup location is determined by the following configurations:

On the destination database, the datafiles and control files will be renamed and restored to this directory:

The destination database online redo logs will be recreated in this directory:

The destination database archive redo log file location will be set as follows:

Step 1: Create an RMAN Backup on the Originating Database

When backing up a database, make sure you have the autobackup control file feature turned on. Also include the archive redo logs as part of the backup, like so:

I usually configure a channel and have both the backup pieces and the control file autobackup go to the same directory. For example, this is what the backup pieces look like for the source database:

Step 2: Copy RMAN Backup to Destination Server

For this step, I usually use a utility such as rsync or scp to copy the backup pieces from one server to another. This example uses the scp command:

In this example, the /ora02/rman/O11DEV directory must be created on the destination server before copying the backup files.

Step 3: Ensure that Oracle is Installed

Make sure you have the same version of Oracle binaries installed on the destination as you do on the originating database. In this example, Oracle Database 11g release 2 is used for both the source and destination databases.

Step 4: Source the Required OS Variables

You need to establish the operating system variables such as ORACLE_SID, ORACLE_HOME, and PATH. I usually set the ORACLE_SID variable to match what it was on the original database. The database name will be changed as part of the last step in this section. Here are the settings for ORACLE_SID and ORACLE_HOME on the destination server:

Step 5: Create an init.ora File for the Database to be Restored

Copy the init.ora file from the original server to the destination server and modify it so that it matches the destination box in terms of any directory paths. For example, make sure you modify the CONTROL_FILES parameter so that the path names are reflective of where the control files will be placed on the new server.

For now, the name of the init.ora file is ORACLE_HOME/dbs/initE64202.ora. This file will be renamed when the database is renamed to O11DEV in a later step. For now, the name of the database is E64202; this will be renamed in a later step.

Here are the contents of the init.ora file:

Notice that the CONTROL_FILES, DIAGNOSTIC_DEST, and LOG_ARCHIVE_DEST_1 reflect the new path directories on the destination server.

Step 6: Create any Required Directories for Datafiles, Control Files, and Dump/Trace Files

For this example, the directories of /ora02/dbfile/O11DEV, /oracle/app/oracle, and /ora02/oraarch/O11DEV are created:

Step 7: Startup the Database in NOMOUNT Mode

You should now be able to startup the database in NOMOUNT mode:

Step 8: Restore a Control File from the RMAN Backup

Now restore the control file from the backup that was previously copied. In this example, the backup piece that contains the control file backup is c-1984315547-20100923-00.bk:

The control file will be restored to all locations specified by the CONTROL_FILES initialization parameter. Here is some sample output:

Step 9: Startup Database in Mount Mode

You should be able to startup your database in mount mode now:

Step 10: Make the Control File Aware of the Location of the RMAN Backups

First, use the CROSSCHECK command to let the control file know that none of the backups or archive redo logs are in the same location that they were on the original server:

Now use the CATALOG command to make the control file aware of the location and names of the backup pieces that were copied to the destination server. In this example, any RMAN files that are in the /ora02/rman/O11DEV directory will be cataloged in the control file:

Here is some sample output:

Now, type in YES (if everything looks okay). You should be able to use the RMAN LIST BACKUP command now to view the newly cataloged backup pieces:

Step 11: Rename and restore the datafiles to Reflect New Directory Locations

If your destination server has the exact same directory structure as the original server directories, you can issue the RESTORE command directly:

However, when restoring datafiles to locations are different from the original directories, you'll have to use the SET NEWNAME command. Create a file that uses an RMAN run{} block that contains the appropriate SET NEWNAME and RESTORE commands. I like to use a SQL script that generates SQL to give me a starting point. Here is a sample script:

After running the script, here are the contents of the newname.sql script that was generated:

Now, modify the contents of the newname.sql script to reflect the directories on the destination database server. Here is what the final newname.sql script looks like for this example:

Now, connect to RMAN and run the prior script to restore the datafiles to the new locations:

Here is a snippet of the output for this example:

All of the datafiles have been restored to the new database server. You can use the RMAN REPORT SCHEMA command to verify that the files have been restored and are in the correct locations:

Here is some sample output:

From the prior output, the database name and temporary tablespace datafile still don't reflect the destination database. Those will be modified in subsequent steps.

Step 12: Recover the Database

Next, you need to apply any archive redo files that were generated during the backup. These should be included in the backup because the ARCHIVELOG ALL clause was used to take the backup. Initiate the application of redo via the RECOVER DATABASE command:

RMAN will restore and apply as many archive redo logs as it has in the backup pieces and then will throw an error when it reaches an archive redo log which doesn't exist:

Now is a good time to verify that your datafiles are online and not in a fuzzy state. Run the following query:

Step 13: Set the New Location for the Online Redo Logs

Set the names for the online redo logs to reflect the new directory structures on the destination database server. I sometimes use a SQL script that generates SQL to assist with this step:

For this example, here are the contents of the renlog.sql file that was generated:

The contents of renlog.sql need to be modified to reflect the directory structure on the destination server. Here is what renlog.sql looks like after being edited for this example:

Update the control file by running the prior script:

You can select from V$LOGFILE to verify that the online redo log names are correct:

Here is the output for this example:

Make sure the directories exist on the new server that will contain the online redo logs. For this example, here's the mkdir command:

Step 14: Open the Database

You must open the database with the OPEN RESETLOGS command (because there are no redo logs and they must be recreated at this point):

If successful, you should see this message:

Step 15: Add tempfile

When you start your database, Oracle will automatically try to add any missing tempfiles to the database. Oracle won't be able to do this if the directory structure on the destination server is different from the source server. In this scenario you will have to manually add any missing tempfile(s). To do this, first take offline the temporary tablespace tempfile. The file definition from the originating database is taken offline like so:

Next, add a temporary tablespace file to the TEMP tablespace that matches the directory structure of the destination database server:

Step 16: Rename the Database

If you need to rename the database to reflect the name for a development or test database, create a trace file that contains the CREATE CONTROLFILE statement and use it to rename your database. These steps are covered in detail in Chapter 4. The basic steps involved are:

1. Generate a trace file.
   SQL> oradebug setmypid
SQL> alter database backup controlfile to trace resetlogs;
SQL> oradebug tracefile_name
2. Modify the trace file to include the SET DATABASE.
   CREATE CONTROLFILE SET DATABASE "O11DEV" RESETLOGS...
3. Create an init.ora file that matches the new name.
4. ORACLE_HOME/dbs/init<newSID>.ora.
5. Modify the DB_NAME variable within the new init.ora file (in this example, it's set to O11DEV).
6. Set ORACLE_SID to reflect the new SID name (in this example, it's set to O11DEV),
7. SQL> startup nomount;
8. Run the trace file to recreate the control file (trace file from Step 2).
9. SQL> alter database open resetlogs;

If successful, you should have a database that is a copy of the original database. All of the datafiles, control files, archive redo logs, and online redo logs are in the new locations, and the database has a new name.

Summary

RMAN is an acronym short for Recovery Manager. It's worth noting that Oracle did not name this tool Backup Manager. The Oracle team recognized that while backups are important, the real value of a B&R tool is its ability to restore and recover the database. Being able to managing the recovery process is the critical skill. When a database is damaged and needs to be restored, everybody looks to the DBA to perform a smooth and speedy recovery of the database. Oracle DBAs should use RMAN to protect, secure, and ensure the availability of the company's data assets.

Restore and recovery are analogous to the healing process when you break a bone. Restoring is similar to the process of setting the bone back to its original position. This is like restoring datafiles from a backup and placing them in their original directories. Recovering a datafile is similar to the healing process of a broken bone—returning the bone back to its state before it was broken. When you recover datafiles, you apply transactions (obtained from archive redo and online redo) to transform the restored datafiles back to the state they were in before the media failure occurred.

RMAN can be used for any type of restore and recovery scenario. Depending on the situation, RMAN can be used to restore the entire database, specific datafiles, control files, server parameter files, archive redo logs, or just specific data blocks. You can instruct RMAN to perform a complete recovery or incomplete.

The last section in this chapter details how to use RMAN to restore and recover a database to a remote server. I recommend that you periodically attempt to test this type of recover. This will fully exercise your backup and recovery strategy. You will gain much confidence and fully understand backup and recovery internals once you can successfully restore a database to a different server from the original.