CHAPTER 24

Restore and Recover with RMAN

Exam Objectives

• 63.1.10.2    Perform Complete and Incomplete Recovery

• 63.1.1.12    Recover Files Using RMAN

• 63.1.12.1    Perform Recovery for Spfile, Password file, Controlfile, and Redo Log Files

• 63.1.12.2    Perform Table Recovery from Backups

• 63.1.12.3    Perform Recovery of Index and Read-only Tablespaces, Tempfile

• 63.1.12.4    Restore a Database to a New Host

This chapter details using Recovery Manager (RMAN) in a recovery scenario. This is required if you have lost one or more datafiles, a controlfile, or possibly the entire database. Two terms must be defined first: restore and recover. To restore a file means to replace the damaged or missing file with a copy extracted from some form of backup. To recover a file means to apply redo change vectors to the restored file in order to bring it forward in time, ideally to the state it was in before the damage occurred. RMAN automates the entire restore and recover process.

Perform Complete and Incomplete Recovery

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. You’ll use incomplete recovery when either more recent archived redo logs are not available or you want to restore and recover to a time in the past before some kind of logical error occurred.

Restore and Recovery in One Page

Restore and recovery can be complete (meaning no loss of data) or incomplete (meaning you do lose data). The first decision a database administrator (DBA) makes after a problem is whether to try complete or incomplete recovery. Ninety-nine times out of a hundred, you should try for compete recovery. Loss of any number of any files is no reason to lose data—unless all copies of the current online log file group are gone. In that case, incomplete recovery is the only option.

Complete recovery following datafile damage requires four steps:

1.  Take the damaged or missing file (or files) offline.

2.  Restore it (or them).

3.  Recover it (or them).

4.  Bring it (or them) online.

Incomplete recovery also requires four steps:

1.  Mount the database.

2.  Restore all datafiles.

3.  Recover the database until some point.

4.  Open the database with RESETLOGS.

These are the differences: Complete recovery can (usually) be done while the database is open; incomplete recovery is done in mount mode. The granularity of restore for complete recovery is the damaged file (or files); the granularity of incomplete recovery is the entire database. Incomplete recovery uses the UNTIL keyword to stop recovery at a certain system change number (SCN), time, or log switch sequence number; complete recovery does not. Following complete recovery, no further action is required, it is as though nothing had ever gone wrong; incomplete recovery finishes by re-creating the online redo log files, with the RESETLOGS command.

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, which is the restore phase, and applying archived and online redo log files to bring the entire database or individual datafile up to the specified SCN (usually the most recent SCN, or the last committed transaction), which is the recovery phase.

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 changes in the archived and online redo log files to the restored datafiles. The process can be as simple as the following example (the command output is excluded for clarity):

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. Note that many operations can be accomplished at either the tablespace level or the datafile level but that tablespace operations are possible only if the database is open.

Performing Complete Recovery of a Noncritical Datafile

If you lose a datafile that is not 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 that datafile, and the database can remain open.

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 datafiles for the tablespace from the backup location.

3.  Use the RMAN RECOVER command to apply archived and online redo log files to the restored datafiles.

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 reenter any data for previously committed transactions. Note that it is, strictly speaking, not necessary to take the whole tablespace offline. If the tablespace consists of multiple datafiles and only one of the datafiles is damaged, it is possible to take only that one datafile offline. This will result in only some objects in the tablespace (those that have extents in the damaged file) being unavailable. How your software will react to this situation is hard to predict.

Exercise 24-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. It is assumed that previous exercises have been completed.

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.

6.  Repeat the exercise, performing restore and recover at the datafile level.

These are the commands:

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 (or datafiles) 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 reenter any data.

In mount mode (necessary when repairing damage to a critical file), ALTER TABLESPACE commands are not available, so restore and recovery must be done at the file level. The ALTER TABLESPACE commands can be used only when the database is open.

Performing 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 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 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 (user-managed recovery is not recommended and deprecated in Oracle Database 12c), 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 a different 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 24-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.  Connect with SQL*Plus and 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 using 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 reenter lost data.

•  The tolerance of users for database downtime.

Recovering 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.

To clarify, you’re creating image copies, but going forward you’re keeping that image copy up to date incrementally. You’re not actually using this image copy to recover your live database but instead using online and archived redo log files to recover the copy of a datafile so it’s ready to use if and when disaster strikes.

The “Oracle-suggested backup strategy” that Enterprise Manager can implement is based on an incrementally updated backup.

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:

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 invocation 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.

Switching 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 your entire 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 critical 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, having image copies available significantly reduces 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.

The steps to switch to a datafile copy are 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 (or incrementally updated). Here are the steps:

1.  Take 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 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 datafiles back online.

Exercise 24-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.  Prepare for the exercise by creating an image copy of the database to the fast recovery area. From the RMAN prompt, use this:

Use an operating system command to delete the datafile.

2.  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.

3.  Take the datafile offline at the SQL> prompt.

Alternatively, you can take the datafile offline from within an RMAN session.

4.  Switch to the datafile copy for the USERS tablespace.

5.  Recover the datafile using the recent archived and online redo log files.

6.  Bring the datafile back online.

Alternatively, you can bring the datafile back online using the SQL> prompt.

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 fast recovery area. To move the datafiles back to the original location, follow these steps:

1.  Create an image copy of the datafiles in the original location.

2.  Take the datafiles offline.

3.  Use the SWITCH TO . . . COPY command to switch back to the restored (re-created) datafile.

4.  Recover the datafiles.

5.  Bring the datafiles back online.

Exercise 24-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). This time, do the work at the tablespace level. The datafile locations for each tablespace are currently as follows:

1.  Create an image copy of the datafile at 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.

Perform Recovery for Spfiles, Controlfiles, and Online Redo Log Files

In rare instances, you may lose all copies of the current controlfile. This is rare because you should have the controlfile multiplexed to several locations. Even if you do lose all copies of the current controlfile, you should have at least one autobackup of the controlfile from the most recent RMAN backup. In addition, if you are using a recovery catalog, all metadata within your most recent controlfile 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 Automatic Storage Management (ASM) disk group. When RMAN performs a controlfile autobackup, both the current controlfile and the spfile are backed up.

Damage to online redo log files cannot be repaired by RMAN because RMAN never backs up online redo log files. They are intended to be protected by multiplexing. To repair damage to them, use SQL commands. These can be executed from SQL*Plus or from within an RMAN session, but in either case the recovery does not in any way involve RMAN channel processes.

Restoring the Spfile from the Autobackup

To restore the spfile from the autobackup, first set the database ID (DBID) if the instance is not running when the spfile is lost.

The DBID should be part of your most basic documentation and will have been displayed every time you connected with RMAN. Next, restart the database with a default spfile (you will do something similar later in the chapter when recovering to a new host).

Next, restore the spfile from the autobackup to the original location.

Finally, start the database.

Restoring the Controlfile

If the controlfile is multiplexed (as it should be), recovery from loss of one copy is trivial; simply copy a surviving controlfile copy over the damaged or missing controlfile copy. This must be done while the database is shut down or in NOMOUNT mode (which will always be the case because the instance will have failed when the controlfile copy was damaged and cannot subsequently be mounted). Then mount and open the database. Only if all copies of the controlfile are lost is anything more complex required. The routine is as follows:

1.  Start up the instance in NOMOUNT mode.

2.  Restore the controlfile from backup.

3.  Mount the database.

4.  Recover the database.

5.  Open the database with RESETLOGS.

The awkward part is the second step. Because the database cannot be mounted, RMAN has no access to its repository and does not therefore know where the backup pieces that contain your controlfile backups actually are. There are three ways around this.

First, use the RESTORE CONTROLFILE FROM AUTOBACKUP command. The autobackups go to a well-known filename, based on the DBID, and RMAN can find them automatically.

Second, use a recovery catalog. The catalog contains a copy of the RMAN repository, so if you are connected to it, RMAN can interrogate the repository to find the backup locations. Then use the command RESTORE CONTROLFILE;.

Third, nominate a backup piece that you know includes a controlfile backup. Provided you have kept the output of your previously executed backup commands, you will have this information: RESTORE CONTROLFILE FROM '……' ;.

Restoring the controlfile 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 controlfile, you have to open the database with NOMOUNT and then restore the controlfile. After you mount the database with the restored backup controlfile, you must recover the database because the backup controlfile contains information about an older version of the database. For the same reason, you must open the database with RESETLOGS.

RMAN restores the controlfile 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 controlfile to a different location.

Exercise 24-5: Restore the Controlfile from an Autobackup    In this exercise, all copies of the controlfile were accidentally deleted by an overly eager system administrator trying to free up disk space. Restore and recover the database with a controlfile restored from a controlfile and spfile autobackup.

1.  Identify the controlfile locations where all copies of the controlfile used to reside.

Use an operating system command to delete all copies of the controlfile.

2.  Shut down the instance (if it is not already down) and reopen it in NOMOUNT mode.

3.  Start RMAN and restore the controlfile 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 controlfile. RMAN finds the controlfile autobackup in the fast recovery area and writes it to the three controlfile destinations specified by the CONTROL_FILES initialization parameter.

4.  Mount the database, recover the database (to synchronize the datafiles with the restored controlfile), and open the database with RESETLOGS.

Recovering from a Lost Redo Log Group

The loss of a redo log group or a redo log group member can mean data loss and a significant recovery effort. It can also mean no data loss and a minimal recovery effort, depending on the status of the redo log group and whether you lose the entire log group or only a member of a log group. The following sections review how log groups work and how the different log group statuses change as redo is written to the group, how the database switches to the next log group, and how a filled log group is copied to an archive location. In most scenarios, data loss is nonexistent, especially if you mirror your log groups.

Table 24-1    Log File Status in V$LOG

A redo log group can have one of six statuses in the view V$LOG.

At any given point in time, the most common statuses are CURRENT, ACTIVE, and INACTIVE. A redo log group is in the UNUSED state after creation, and once it’s used, it will never return to that state. The CLEARING and CLEARING_CURRENT states exist when you re-create a corrupted log file, which ideally will not occur often!

The sample database has three redo log file groups, and this query of V$LOG shows the status of each log:

The two log file groups with a status of INACTIVE have been archived. Depending on the input/output (I/O) load of the system and other factors, the ARCHIVED status will be NO until the log file has been successfully written to all mandatory archived log file destinations.

Recovering from Log Group Member Failures

If one member of a log group becomes damaged or is lost, the Log Writer (LGWR) process continues to write to the undamaged member, and no data loss or interruption in service occurs. However, it is imperative that you correct this problem as soon as possible because the log group with only one member is now the single point of failure in your database. If it is lost, your recovery efforts will increase, and loss of committed transactions is likely.

In this example, the second member of the third redo log file group becomes damaged. These error messages should appear in the alert log. You will see similar messages on the Enterprise Manager Database Control home page if it is configured.

You can also identify the lost or damaged redo log file member using the V$LOGFILE view:

The solution to this problem is straightforward. Drop the invalid member and add a new member to the group, as in this example:

Note that the redundancy provided by the repaired redo log file group will not be available until the next time this log file group is active. If the destination disk itself is not damaged and the original redo log file is logically corrupted from user error or a rogue process, you can reuse the original redo log file by specifying the REUSE clause as follows:

Recovering from Loss of an Entire INACTIVE Redo Log Group

The loss of all members of a redo log group marked INACTIVE is the most benign redo log group failure, although you must act quickly before the Oracle database processes need to use the redo log group again. If Oracle needs to use the redo log group before it is repaired, the database halts until the problem is fixed. The group is not needed for crash recovery because it is INACTIVE. Therefore, you can clear the group using the ALTER DATABASE CLEAR LOGFILE command.

A damaged redo log group with a status of INACTIVE may or may not be archived yet. The archival status determines which form of the ALTER DATABASE CLEAR LOGFILE command to use.

If a damaged inactive redo log group has been archived, you can identify the group number of the damaged group from the alert log or from the dynamic performance view V$LOGFILE. Remember that you can look at the ARCHIVED column in the dynamic performance view V$LOG to determine whether the log group has been archived yet.

In this example, redo log group #1 is damaged but has been archived. Use the ALTER DATABASE command as follows:

If the instance is down, start the database in MOUNT mode and run this command. Otherwise, you can run the command when the database is OPEN. All members of the redo log file group are reinitialized. If any or all of the redo log group members are missing, they are then re-created, provided that the destination directories are available.

The redo log group has been archived. Thus, no data loss will result, and all backups in combination with archived redo log files can be used for complete recovery of the database. Until the database reuses the redo log file group, it has a status of UNUSED, as you can see in this query:

If you have a damaged nonarchived inactive redo log group, you will not lose any committed transactions. However, you must perform a full backup after clearing the redo log group to ensure that you can perform a complete recovery. If you do not, you will have a gap in archived redo log files. Therefore, you will be able to perform only incomplete recovery up to the SCN of the last transaction in the archived redo log file created before the missing log file.

To clear the second unarchived log group, start the database in MOUNT mode (if it is not already up) and use the following command:

Note the UNARCHIVED keyword in this command. It performs the same action that occurs when you cleared an archived redo log group, but this is Oracle’s way of forcing you to acknowledge that you will have a gap in your archived redo log files.

After clearing the log file group, perform a full backup. This provides a backup that you can use for complete recovery, along with all successive archived redo log files.

A complicating factor to consider when you’re clearing a damaged nonarchived inactive redo log group is whether an offline datafile needs the cleared log file group before it can be brought online. If it does, you may have to drop the datafile. Segments in the tablespace containing the offline datafile may exist in other online datafiles that belong to this tablespace and may be relocated to other tablespaces. The tablespace may have to be re-created using logical backups or some other method. You cannot recover the datafile, and therefore the tablespace containing the datafile, because the redo required to bring the datafile back online is gone. Oracle makes you acknowledge that your datafile is unrecoverable in this scenario as well, and you must use the UNRECOVERABLE DATAFILE keywords when you clear the log file group:

Recovering from a Lost ACTIVE Redo Log Group

If a damaged redo log group is in the ACTIVE state, Oracle is not currently writing to it, but it is needed for instance recovery. Execute ALTER SYSTEM CHECKPOINT to force the database writer process (DBWn) to write all changed buffers from buffer cache to datafiles, and the group will then become inactive. Clear the redo log file group as you did with an inactive redo log group. You will not lose any transactions. In addition, your archived redo log file stream will be intact, if the group had been archived at the time the problem occurred.

Recovering from a Lost CURRENT Redo Log Group

This is the one situation where you will lose data. A lost redo log group in the CURRENT state is currently being written to by the LGWR process—or it was being written to at the time of failure. The instance will crash, and your only option is to perform incomplete recovery applying archived and online redo log files up to but not including the damaged redo log group.

After performing incomplete recovery with the database in MOUNT mode, open the database with RESETLOGS.

If the location for the damaged online redo log file group is available, Oracle will reinitialize the log file group along with all other groups, resetting the log sequence number to 1 and starting a new incarnation. If the location is no longer available, rename the online redo log files and point them to a new location as in this example, while the database is mounted and before opening the database with RESETLOGS:

When you open the database with RESETLOGS, Oracle re-creates and initializes any missing online redo log files.

Backing Up and Restoring the Password File

The password file cannot be managed by RMAN. To protect it, copy it with any appropriate operating system utility. Ideally, this should be done after any grant or revoke of the administrative privileges (which are SYSDBA, SYSOPER, SYSBACKUP, SYSDG, and SYSKM in a database instance; SYSASM in an ASM instance) and subsequently after any change of password for any user granted these privileges. In practice, it usually sufficient simply to rely on whatever backups are made by your system administrator of the Oracle Home file system. Because the default location of the password file is in the ORACLE_HOME/dbs directory (or ORACLE_HOMEdatabase directory on Windows), it will be included in any such backup. It is possible to store the password file on an ASM disk group by specifying this when you run the ORAPWD utility to create the file. In this case, you cannot copy the file using an operating system utility, and it will not, of course, be included in an Oracle Home backup. In this case, use the ASMCMD utility to copy the file from the disk group to a file system.

If the password file is ever damaged, simply copy it back.

An alternative to backing up and restoring the password file is to re-create it. This takes only a couple of seconds. If users other than SYS have been granted administrative privileges, these grants will have to be made again because they will not exist in a freshly created file.

Recovering from a Lost Tempfile

Recovering from the loss of one or more tempfiles is a straightforward process. Remember that a tempfile is identical to a datafile except that it belongs to a temporary tablespace. The impact to a running database is minimal depending on the query mix. In all cases, you can recover the tempfile while the database is up, even if the original file location is not available.

Losing a Tempfile

One of the consequences of losing a tempfile is that any SQL statements that need temporary disk space for sorting (in other words, insufficient memory is available in Oracle’s memory space) will fail. If one or all of the datafiles for the TEMP tablespace is deleted at the operating system level, you can create a new tempfile in the same directory as the original one using the ALTER TABLESPACE command. If the original directory location is not available, you can create it in a different location. After that you can drop the original tempfile using a similar ALTER TABLESPACE command.

Exercise 24-6: Create a Replacement Tempfile for the TEMP Tablespace    In this exercise, the tempfile for the TEMP tablespace was accidentally deleted, so you must create another tempfile to replace it while the database is still running.

1.  Identify the name of the tempfile for the TEMP tablespace.

2.  Create a new tempfile with a different name for the TEMP tablespace.

3.  Drop the previous tempfile. This will update only the controlfile because the original tempfile is missing.

4.  Confirm that the TEMP tablespace contains only the newly created tempfile.

Starting a Database Without a Tempfile

Recovering from the loss of a tempfile is even easier if you start the database with a missing tempfile. The database starts, and if the original disk directory location is available, Oracle re-creates all missing tempfiles, as you can see in this excerpt from the alert log:

If the original disk directory location is no longer available, the database still starts, and you can use the steps from the preceding section to re-create the tempfiles manually for the TEMP tablespace.

Two-Minute Drill

Perform Complete and Incomplete Recovery

•  Use RMAN RESTORE and RECOVER for complete recovery from a critical and noncritical datafile 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.

•  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.

•  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 is available.

•  Use tags with an incrementally updated image copy strategy to ensure that the correct incremental backup updates the image copy.

•  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 controlfile and recovery catalog with the new datafile locations.

•  You can use an RMAN autobackup to restore either an spfile or controlfile when all online copies are lost.

•  RMAN restores the controlfile 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 controlfile.

•  When restoring a controlfile from autobackup, you must open the database with RESETLOGS.

•  You can optionally restore a copy of the controlfile to an alternative location.

Perform Recovery for Spfiles, Controlfiles, and Online Redo Log Files

•  A redo log group can have six statuses: CURRENT, ACTIVE, INACTIVE, UNUSED, CLEARING, or CLEARING_CURRENT. The most common statuses are CURRENT, ACTIVE, and INACTIVE.

•  You can use the dynamic performance view V$LOG to query the status of each redo log group.

•  If one member of a log group becomes damaged or is lost, the LGWR process continues to write to the undamaged member, and no data loss or interruption in service occurs.

•  The dynamic performance view V$LOGFILE shows the status of each individual member of each log file group.

•  If the status of a log file group member is INVALID in the view V$LOGFILE, it is damaged or unavailable and must be re-created.

•  Losing an inactive log file group that has not been archived will result in a gap in the archived redo log files and necessitates a full backup after recovering the log file group.

•  Losing a redo log file group with a status of ACTIVE will not cause the loss of committed transactions if you can successfully perform ALTER SYSTEM CHECKPOINT. If the checkpoint fails, you must perform incomplete recovery.

•  Losing a redo log file group with a status of CURRENT will crash the instance, and you must perform incomplete recovery.

•  Losing a password file prevents DBAs from connecting to an open or closed instance with the SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, or SYSKM privilege, unless using operating system authentication.

•  You use the orapwd command at an operating system prompt to re-create the password file.

•  The default location for the password file is $ORACLE_HOME/dbs on Linux and %ORACLE_HOME%database on Windows.

•  The dynamic performance view V$PWFILE_USERS lists all the database users who have SYSDBA, SYSOPER, or SYSASM privileges.

•  If you want to back up your controlfile while the database is open, you can do it with two different SQL commands: ALTER DATABASE BACKUP CONTROLFILE TO <filename> and ALTER DATABASE BACKUP CONTROLFILE TO TRACE.

•  ALTER DATABASE BACKUP CONTROLFILE TO < filename > creates an exact binary copy of the controlfile at the specified location.

•  ALTER DATABASE BACKUP CONTROLFILE TO TRACE creates an editable script that re-creates the controlfile in the directory $ORACLE_BASE/diag/rdbms/<database>/<instance>/trace.

•  Losing all copies of the online controlfile does not lose any committed transactions if you have a recent backup copy of the controlfile and both the datafiles and online redo log files are intact.

•  You do not have to open the database with RESETLOGS after restoring your controlfile if you manually create the replacement controlfile using CREATE CONTROLFILE or you use a version of the controlfile script that you created with ALTER DATABASE BACKUP CONTROLFILE TO TRACE.

Perform Recovery of Tempfiles

•  A tempfile can be recovered while the database is open.

•  The impact of a lost tempfile is noticed when users attempt to sort large result sets.

•  When a tempfile is lost, you can re-create it in the original location or specify a new location.

•  If the database starts without tempfiles, it creates them in the location specified in the controlfile.

Self Test

1.  What is the difference between a critical and a noncritical datafile in a recovery scenario? (Choose the best answer.)

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? (Choose the best answer.)

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 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.

4.  Which of the following methods can you use to retrieve the current system change number (SCN)? (Choose the best answer.)

A.  Query the CURRENT_SCN column from the 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? (Choose the best answer.)

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

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 will have been updated with an incremental level 1 backup? (Choose the best answer.)

A.  Once

B.  Twice

C.  Three times

D.  At least four times

8.  The RMAN SWITCH command is equivalent to what SQL command? (Choose the best answer.)

A.  ALTER DATABASE RENAME FILE

B.  ALTER DATABASE ARCHIVELOG

C.  ALTER DATABASE OPEN RESETLOGS

D.  ALTER SYSTEM SWITCH LOGFILE

9.  What is the correct order of the following commands for restoring a controlfile from an RMAN autobackup? (Choose the best answer.)

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

10.  When you run the RMAN RESTORE CONTROLFILE command, where does RMAN put the previous version of the controlfile? (Choose the best answer.)

A.  In all available locations defined by the CONTROL_FILES initialization parameter

B.  In the Fast Recovery Area

C.  In all locations defined by the CONTROL_FILES initialization parameter unless overridden with the TO '<filename>' clause

D.  In the first location defined by the CONTROL_FILES initialization parameter

11.  Which of the following is not a valid status for an online redo log group? (Choose the best answer.)

A.  CURRENT

B.  ACTIVE

C.  INVALID

D.  UNUSED

E.  CLEARING

12.  What is the difference between the V$LOG and V$LOGFILE views? (Choose the best answer.)

A.  V$LOG contains the status of all archived redo log files, and V$LOGFILE contains the status of all online redo log files.

B.  V$LOG contains the status of the online redo log group members, and V$LOGFILE contains the status of individual online redo log groups.

C.  V$LOG contains the status of all online redo log files, and V$LOGFILE contains the status of all archived redo log files.

D.  V$LOG contains the status of the online redo log groups, and V$LOGFILE contains the status of individual redo log group members.

13.  Which methods can you use to recover a lost or damaged password file? (Choose all that apply.)

A.  Use the orapwd command at an operating system prompt to re-create the password file.

B.  Restore the password file from backup and apply any archived and online redo log files to bring its contents to the present time.

C.  Use the orapwd SQL command to re-create the password file.

D.  Restore the password file from an operating system backup.

14.  Which of the following commands does not back up the current controlfile? (Choose the best answer.)

A.  SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

B.  SQL> ALTER SYSTEM BACKUP CURRENT CONTROLFILE;

C.  RMAN> BACKUP CURRENT CONTROLFILE;

D.  SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/U08/BACKUP/CTL.BAK';

15.  If you lose all of the tempfiles from your temporary tablespace, what is the most likely result noticed by your users? (Choose the best answer.)

A.  The database becomes unavailable and users cannot connect.

B.  The users can’t perform SELECT statements.

C.  The users cannot add or delete rows in any table.

D.  The users can’t use ORDER BY or GROUP BY in their queries.

16.  Which is the best method for recovering a tempfile? (Choose the best answer.)

A.  Drop the TEMP tablespace and re-create it with a datafile in a new location.

B.  Add another tempfile to the TEMP tablespace and drop the corrupted or missing tempfile while the database is running.

C.  Shut down the database, restore the tempfile from a backup, and recover it using archived and online redo log files.

D.  Add another tempfile to the TEMP tablespace and drop the corrupted or missing tempfile after the database has been shut down and restarted in MOUNT mode.

Self Test Answers

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 controlfile and make the datafile locations available to RMAN.
    A, B, and C are incorrect. A is incorrect because the entire database must be offline when recovering a critical datafile. B is incorrect because recovering a noncritical datafile requires only the damaged datafile to be offline. C is incorrect 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 incorrect 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 incorrect. These steps are all required.

4.      D. V$DATABASE contains the most recent SCN in the CURRENT_SCN column.
    A, B, C, and E are incorrect. A and B are incorrect because the column CURRENT_SCN does not exist in either V$DATAFILE_HEADER or V$INSTANCE. C is incorrect because V$DATABASE does not have a column named LAST_SCN. E is incorrect 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, B, and D are incorrect. A is incorrect because the keyword KEEP is not valid for the command. B is incorrect because PRESERVE was not specified. D is incorrect 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 incorrect. 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.

7.      C. The first time the script runs, there is no level 0 image copy or 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 successive time, the first incremental level 1 backup is applied to the image copy.
    A, B, and D are incorrect. They all specify the incorrect number of executions.

8.      A. Both the RMAN SWITCH and the SQL ALTER DATABASE RENAME FILE commands update the location of the datafile in both the controlfile and the recovery catalog.
    B, C, and D are incorrect. B is incorrect because this command puts the database into ARCHIVELOG mode. C is incorrect because the command is used only after incomplete recovery. D is incorrect because the command switches online redo log files, not datafile names.

9.      B. The specified order is correct. You must open the database with RESETLOGS since your restored controlfile has information about an older version of the database.
    A, C, and D are incorrect because they specify an incorrect sequence of commands.

10.      C. The command restores the controlfile 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, B, and D are incorrect. A is incorrect because the command fails if any of the locations defined by CONTROL_FILES are not available. B is incorrect because the autobackup of the controlfile will most likely originate from the fast recovery area. D is incorrect because RMAN restores the controlfile to all locations defined by CONTROL_FILES.

11.      C. The status INVALID is valid only for an online redo log group member, not for the entire group.
    A, B, D, and E are incorrect. They are valid statuses for an online redo log group.

12.      D. V$LOG contains the status of redo log groups, including whether the group is currently being written to. V$LOGFILE contains the status of individual redo log group members.
    A, B, and C are incorrect. The views V$LOG and V$LOGFILE do not contain information about archived redo log files, although the view V$LOG has a column to indicate whether the redo log file group has been archived or not.

13.      A and D. Either method can be used to recover the password file, but using the orapwd command requires that you re-create the privileged user accounts that need SYSDBA, SYSOPER, SYSADM, SYSBACKUP, SYSDG, and SYSKM privileges.
    B and C are incorrect. B is incorrect because you do not apply redo log files to the password file. C is incorrect because orapwd is valid only at an operating system command prompt.

14.      B. There is no such command.
    A, C, and D are incorrect. A is incorrect because it creates a text-based file containing two different CREATE CONTROLFILE commands, depending on the availability of your datafiles and online redo log files. C is incorrect because it is one of many ways that RMAN backs up the controlfile. D is incorrect because it creates a binary copy of the controlfile at the specified location.

15.      D. Temporary tablespaces provide sort space for queries that use ORDER BY and GROUP BY when the sort operation will not fit in memory. Other operations cause sorts as well: SELECT DISTINCT, index creations, and index rebuilds.
    A, B, and C are incorrect. A is incorrect because the database remains available for some queries and most DML activity even if the TEMP tablespace is unavailable. B is incorrect because users can still perform SELECT statements that don’t need sorting or the sort operation will fit into memory. C is incorrect because most DML activity does not require the TEMP tablespace.

16.      B. Once the missing tempfile is dropped and a new one added, the TEMP tablespace is automatically available to users.
    A, C, and D are incorrect. A is incorrect because dropping the tablespace is not necessary, and you cannot drop the default temporary tablespace. C is incorrect because you cannot recover a temporary tablespace; there are no permanent objects in a temporary tablespace. D is incorrect because the database does not need to be shut down to recover a temporary tablespace.