6
User-Managed Backup and Recovery
CERTIFICATION OBJECTIVES
6.01 Recover from a lost tempfile
6.02 Recover from a lost redo log group
6.03 Recover from the loss of the password file
6.04 Perform user-managed complete database recovery
6.05 Perform user-managed incomplete database recovery
6.06 Perform user-managed and server-managed backups
6.07 Identify the need for backup mode
6.08 Back up and recover a control file
In Chapter 5 you learned about the intricacies of using RMAN for recovering your database, which Oracle considers a server-managed recovery operation. In contrast, using operating system commands and SQL commands to restore and recover your database is known as user-managed backup and recovery. In this chapter, the focus is on user-managed backup and recovery, although some of the scenarios presented here will appear to be similar to the scenarios presented in Chapter 5 using the equivalent RMAN commands.
Data loss or corruption usually falls into one of three categories: user error, application error, or media failure. User error occurs when a DBA or system administrator inadvertently deletes or replaces a datafile or other Oracle-related file. Application errors, such as a backup script with a logic error, may back up the wrong file or delete a file that should be kept. Media failure applies to hardware failures that make a datafile either temporarily or permanently unavailable. This includes but is not limited to controller failures, disk failures, and network failures. For all three categories, the procedures for recovery are the same after any hardware problems are resolved.
You’ll recall the distinction between the restore and recover steps when a datafile is lost or damaged. From an industry point of view, bringing your database back online from a disaster is typically known as recovery. However, Oracle naming conventions subdivides this process into two steps, restore and recover. In summary, the restore process copies a backup file into place if, for example, a datafile is lost or damaged. Recovery then applies archived and perhaps online redo log information to bring the state of the database back to the point of time of the failure, or to any point before the point of failure.
Another important distinction when considering database failures is critical versus noncritical failures. For noncritical failures, the database can continue to operate for all or most users. For example, you may lose only one copy of a multiplexed redo log group or a datafile from a tablespace used by only one application. Critical failures prevent the database from running, which occurs with, for example, the loss of a datafile from the SYSTEM tablespace or all control files.
The first part of this chapter shows you how to recover from the loss of one or more datafiles in a temporary tablespace; you can accomplish this without shutting down the database. Similarly, you’ll learn how to recover from the loss of a redo log group member or an entire redo log group (a redo log group can have one or more members, each of which is a redo log file).
Next, you’ll learn the difference between complete and incomplete recovery and when each method is appropriate for your environment. Incomplete recovery is required when a media failure occurs and one or more archived or online redo log files are not available for the recovery process. In addition, you may perform incomplete recovery when you want to bring the database to a point in time before a user (or another DBA) incorrectly dropped or modified database objects and other less-draconian recovery methods are not available.
Finally, you’ll learn how to recover from the loss of one or more control files. Ideally, you have mirrored your control file several times, However, if the worst comes to pass, you can use a backup control file while minimizing or eliminating downtime and data loss.
CERTIFICATION OBJECTIVE 6.01
Recover 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 6-1
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 control file 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 tempfile(s) manually for the TEMP tablespace.
CERTIFICATION OBJECTIVE 6.02
Recover 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.
Let’s review the types of log group failures and how to recover from each one. In most scenarios, data loss is minimal or nonexistent, especially if you mirror your log groups.
Understanding Log Group Status
A redo log group can have one of six statuses in the view V$LOG, described in Table 6-1.
TABLE 6-1 Log File Status in V$LOG
Log File Status |
Status Description |
|
Oracle is writing to this log group, and this group is needed for instance recovery. |
|
This log group is needed for instance recovery, but Oracle is not writing to this log group. It may or may not be archived yet. |
|
The log group is not needed for instance recovery, may be in use for media recovery, and may or may not be archived. |
|
The log group has not been used yet. |
|
The log is being cleared by |
|
An error has occurred during |
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 hopefully will not occur very often!
Our 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 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 Log Group
The loss of all members of a redo log group may have no effect on the database or may cause loss of committed transactions, which depends on the state of the redo log group. The three possible states of a log file group are INACTIVE, ACTIVE, and CURRENT.
Recovering from a Lost 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.
Clearing an Archived Inactive Redo Log Group
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:
Clearing a Nonarchived Inactive Redo Log Group If you have a 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. You will have a gap in archived redo log files. Therefore, you will be able to perform only incomplete recovery up to the System Change Number (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 up 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 using operating system commands (remember, this is user-managed recovery, not system-managed recovery using RMAN). 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 nonarchived inactive redo log group is whether an offline datafile needs the cleared log file group before it can be brought online. In this scenario, you must drop the tablespace containing the offline datafile and re-create it 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:
The final step after clearing the redo log file group and creating a backup is to back up the control file to a specific directory or to the trace file directory:
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. Try this command:
If it runs successfully, all committed changes are written to the datafiles on disk.
Next, clear the redo log file group as you did with an inactive redo log group, and you will not lose any transactions. In addition, your archived redo log file stream will be intact.
If the checkpoint fails, you must perform an incomplete recovery using Flashback Database. You can also perform an incomplete recovery using all archived and online redo log files up to but not including the damaged redo log group.
Recovering from a Lost CURRENT Redo Log Group
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 using Flashback Database. Again, you can do likewise with all 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, before opening the database with RESETLOGS:
When you open the database with RESETLOGS, Oracle re-creates and initializes any missing online redo log files.
CERTIFICATION OBJECTIVE 6.03
Recover from the Loss of the Password File
The loss of an Oracle password file is rather trivial compared to the loss of a datafile, a redo log file, or a control file. It won’t cause the database to shut down but will prevent some or all DBAs from connecting to the database when it is not open. Although the password file is relatively easy to re-create, be sure that you make a backup copy of the password file using an operating system copy utility whenever it changes.
In the following sections, you’ll get a brief refresher course on how the password file authenticates privileged users; then you’ll learn how to re-create the password file when and if it becomes lost or corrupted.
Review of Authentication Methods
You can use two different methods to authenticate a database administrator: operating system (OS) authentication or password file authentication. Which method you use depends on whether you have a secure connection to the server (for remote administration) and whether you want to use OS groups to manage DBA privileges.
If you do not have a secure remote connection, you must use a password file. If you have a secure connection, or you’re administering the database locally, you have the option to use OS authentication or a password file. Figure 6-1 identifies the options for a DBA when deciding which method will work the best in a particular environment.
FIGURE 6-1 Authentication method flowchart
For connecting locally to the server, the main consideration is the convenience of using the same account for both the OS and the Oracle server, versus maintaining a password file. For a remote administrator, the security of the connection is the driving factor when choosing an authentication method. Without a secure connection, a malicious hacker could easily impersonate a user with the same account as that of an administrator on the server itself, and thereby gain full access to the database with OS authentication.
When using a password file for authentication, ensure that the password file itself is in a directory location that is accessible only by the OS administrators and the user or group that owns the Oracle software installation.
A user could be a member of the OSDBA or OSOPER group and have an entry in the password file. In this situation, OS authentication takes precedence and the user connects with the group-assigned privileges regardless of the username and password specified.
Along with the additional privileges available to the users who connect as SYSDBA or SYSOPER, the default schema is also different for these users when they connect to the database. Users who connect with the SYSDBA or SYSASM privilege connect as the SYS user. The SYSOPER privilege sets the user to PUBLIC:
Re-creating a Password File
A default installation of the Oracle database using the Oracle Universal Installer with a seed database, or using the Database Creation Assistant, will automatically create a password file. However, on some occasions you might need to re-create a password file if it is accidentally deleted or damaged. The orapwd command will create a password file with a single entry for the SYS user and other options when you run the orapwd command without any options:
The default location for the password file is $ORACLE_HOME/dbs on Unix or Linux, and %ORACLE_HOME%database on Windows. The name of the password file is the string “orapw” plus the name of the instance in lowercase. For example, the password file for the DW database would be $ORACLE_HOME/dbs/ orapwdw, and for Windows the default is PWD>sid<.ora.
Once you’ve re-created the password file, you will have to grant the SYSDBA, SYSOPER, or SYSASM privileges to database users who previously had those privileges. In addition, if the password you provided in the orapwd command is not the same password that the SYS account uses in the database, this is not a problem. When you connect using CONNECT / AS SYSDBA, you’re using operating system authentication. When you connect using CONNECT SYS/>syspassword< AS SYSDBA, the password >syspassword< is the password for SYS in the database. And just to reiterate, if the database is down or in MOUNT mode, you must use operating system authentication or the password file. Also worth noting again is that operating system authentication takes precedence over password file authentication, so as long as you fulfill the requirements for OS authentication, the password file will not be used for authentication even if it exists.
As of Oracle Database 11g, database passwords are case-sensitive. To disable case-sensitivity, set the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE.
The system initialization parameter REMOTE_LOGIN_PASSWORDFILE controls how the password file is used for the database instance. It has three possible values: NONE, SHARED, and EXCLUSIVE.
If the value is NONE, Oracle ignores any password file that exists. Any privileged users must be authenticated by other means, such as by OS authentication, which is discussed in the next section.
With a value of SHARED, multiple databases can share the same password file. However, only the SYS user is authenticated with the password file and the password for SYS cannot be changed (unless you re-create the password file). As a result, this method is not the most secure, but it does allow a DBA to maintain more than one database with a single SYS account.
If a shared password file must be used, ensure that the password for SYS is at least eight characters long and includes a combination of uppercase and lowercase alphabetic, numeric, and special characters to fend off a brute-force attack such as a dictionary attack.
A value of EXCLUSIVE binds the password file to one database only, and other database user accounts can exist in the password file. As soon as the password file is created, use this value to maximize the security of SYSDBA, SYSOPER, or SYSASM connections.
The dynamic performance view V$PWFILE_USERS lists all the database users who have SYSDBA, SYSOPER, or SYSASM privileges, as shown here:
EXERCISE 6-2
Re-create the Password File after Accidental Deletion
In this exercise, you’ll re-create the password file for the HR database using the orapwd command and add the user RJB to the list of users in the SYSDBA group. Additionally, you’ll give the user SCOTT the SYSOPER privilege:
1. Create the new password file with 10 entries and the new default password for the SYS account:
2. Connect as SYS and grant the SYSDBA privilege to the user RJB and the SYSASM privilege to SCOTT:
3. Confirm that the users RJB and SCOTT have the new privileges:
CERTIFICATION OBJECTIVE 6.04
Perform User-Managed Complete Database Recovery
Recovery falls into one of two general categories: complete and incomplete. Firstly, if media failure occurs in your database, you will want to use complete recovery to bring your database back up to the state it was in before the media failure. This would include recovery of all committed transactions. Secondly, and in contrast, the database media itself may be intact but a number of changes or logical corruptions to the database have occurred, and these are not easily reversible using logical backups or by users re-entering the correct data to reverse incorrect transactions. In this second scenario, you might consider using incomplete recovery, which is covered later in this chapter.
The following section provides a thorough review and hands-on exercises for complete database recovery when the database is closed (shut down) as well as when the database is open (available to users). You can recover the database when it is open as long as the SYSTEM tablespace is not damaged.
The certification exam contains questions for nearly every objective that require you to perform a series of steps to accomplish a task. This chapter describes how you perform either complete or incomplete recovery steps in the proper order.Whether the database is open or closed during complete recovery, you need to perform the same basic steps:
1. Identify files needed for recovery.
2. Restore recovery-related files that can contain both committed and uncommitted transactions.
3. Start up the database in MOUNT mode.
4. Bring datafiles online.
5. Apply archived and online redo log files to roll forward the database; this is also known as cache recovery.
6. Open the database to provide high availability.
7. Oracle automatically applies undo blocks to roll back uncommitted changes, which is also known as transaction recovery.
8. Open the database as read-write if it was opened as read-only. Undo data is applied for uncommitted transactions even if the database is open as read-only.
For either open or closed database recovery, you can recover the database all at once or recover a single tablespace or datafile at a time. Before you can perform step 3 in the preceding list, you must make sure all archived redo log files needed for complete or incomplete recovery are restored from tape or backup disk; they must also be available when you run the RECOVER command at the SQL< prompt.
Performing Complete Closed Database Recovery
The phrase “closed databaserecovery” is somewhat of a misnomer if you consider that the database instance may still be available when one or more datafiles become unavailable. This is an advantage, because you can then query the dynamic performance view V$RECOVER_FILE to see which files need media recovery and V$RECOVERY_LOG to see the archived log files needed to recover the restored datafile(s).
If one of the datafiles for the SYSTEM tablespace becomes unavailable or is damaged, the instance crashes or shuts down immediately. Even if the SYSTEM or UNDO tablespace is still available, the instance may still crash. In this scenario, you might be able to surmise which datafiles must be recovered by looking at the alert log or by inspecting the disk file system for missing files and restoring them from backup. If all else fails, you can restore all datafiles from the latest backup and recover the entire database.
Identifying Recovery-related Files
If your database is still running after media failure of one or more datafiles, the dynamic performance view V$RECOVER_FILE contains a list of the datafiles that need recovery.
In this example, a system administrator inadvertently deletes a datafile from disk. If she does not know which one was deleted, you can identify it by using V$RECOVER_FILE:
Joining V$RECOVER_FILE with V$DATAFILE and V$TABLESPACE helps you identify which tablespace is affected:
To recover this tablespace successfully, you’ll need archived and online redo log files. V$RECOVERY_LOG tells you which archived redo log files you’ll need:
In this example, only one archived redo log file is required to recover the CHGTRK tablespace.
Restoring Recovery-related Files
After you have identified the tablespace and datafiles that need recovery, you can shut down the database (if it is not already down) and repair the problem that caused the media failure. (This includes asking the system administrator not to delete your datafiles.) Copy the datafile from the backup location to the original location, if it’s available. If the original location is not available, you can copy the backup datafile to an alternative location. Once the database is started in MOUNT mode, rename the datafile as follows, identifying the new location of the datafile in the control file:
In addition, ensure that all archived redo log files are available in the default archiving location. If insufficient space is available for all required archived redo log files, you can restore them to a location that offers enough disk space. You can then specify the alternative location when you recover the datafile. After all required datafiles are in their original or new locations, you can bring the datafiles online, as in this example:
Applying Redo Log Files
Now that you have restored the missing or damaged datafiles and have verified that the archived redo log files are in place, you can recover the database to bring all datafiles to the same SCN before you open the database for users. The command for recovering the database is as you might expect: RECOVER. Here it is in an example:
If you do not use the AUTOMATIC keyword, you are prompted to apply each archived and online redo log file. For complete recovery, you want to use AUTOMATIC so that all archived and online redo log files are applied to the datafiles. You may, however, want to be prompted for each log file in two scenarios: when you want to specify an alternative location for a specific log file, or when you are performing cancel-based (incomplete) recovery, which is discussed later in this chapter. Another keyword available in the RECOVER command is the FROM keyword. This makes it easy to specify an alternative source location for all archived redo log files.
Finally, you can open the database because you have performed these tasks while the database was in MOUNT mode:
EXERCISE 6-3
Perform a Complete Database Recovery
In this exercise, you’ll create, populate, drop, restore, and recover the CHGTRK tablespace:
1. Create the CHGTRK tablespace:
2. Back up the tablespace after placing it in BACKUP mode:
Note that the exclamation mark can be used within SQL*Plus to execute operating system commands on Unix platforms.
3. Create a few tables in the CHGTRK tablespace so that the Data Definition Language (DDL) and redo for this tablespace appears in the online redo log files and eventually the archived redo log files. Here’s an example:
4. Force a log switch and archive the current online redo log file. You could also wait until Oracle generates enough internal redo to create the archived redo log files containing the redo for this tablespace:
5. "Accidentally” drop the datafile for the CHGTRK tablespace:
6. Verify that you can no longer create objects in the CHGTRK tablespace:
7. Shut down the database and restart it in MOUNT mode:
8. Use the views V$RECOVER_FILE and V$RECOVERY_LOG to identify the data-files and redo log files required for recovery:
9. The datafile’s original location is available, so you don’t need to specify a new location with ALTER DATABASE RENAME FILE. Copy the backup of the datafile to its original location:
10. Bring the restored datafile online:
11. Use the RECOVER command and automatically apply any necessary archived redo log files:
12. Open the database:
Performing Complete Open Database Recovery
In many situations, the database will continue to be available when datafiles for noncritical tablespaces become damaged or otherwise unavailable. Queries against tables in a damaged tablespace will return errors. Any attempt to write to the damaged tablespace will immediately bring the tablespace offline. As with closed database recovery, you need to query V$RECOVER_FILE and V$RECOVERY_LOG to find the files required for recovery.
While the database is open, take all tablespaces with damaged datafiles offline:
Specifying TEMPORARY issues a checkpoint for the remaining online datafiles in the tablespace, if any exist. This can save time in the recovery process if the hardware problem with the damaged datafile is temporary, because media recovery might not be necessary.
If the hardware problem is permanent, you can restore the damaged datafile from backup just as you can with complete closed database recovery. The difference with open database recovery lies in the RECOVER command—you recover a tablespace, and not the entire database:
Whether you use complete open or complete closed database recovery depends primarily on whether the database is open, as you might expect. However, if enough datafiles are missing or damaged, the database will not be of much use to most of your users, and the recovery effort will take less time if the database is not open. In addition, if the SYSTEM tablespace is damaged or unavailable, the database instance will crash and you will have to perform complete closed database recovery.
CERTIFICATION OBJECTIVE 6.05
Perform User-Managed Incomplete Database Recovery
Incomplete database recovery follows the same steps used in complete database recovery, except that you apply only the archived and online redo log files up to the desired point in time. As you might expect, incomplete recovery is also known as point-in-time recovery (PITR). You can recover a single tablespace or the entire database up to a specific SCN or point in time.
If you are interested in recovering only a small set of logically isolated tables to a previous point in time, you may be able to perform recovery using Oracle’s Flashback Table features. This assumes that you have a large enough undo tablespace containing all of a particular table’s transactions back to the desired point in time. Another method you can consider is Oracle’s Flashback Database functionality, which can dramatically reduce the recovery time if you have a large enough Flashback recovery area and you have enabled Flashback Database logging.
In other words, consider incomplete database as a last resort. It might restore most of the database to your desired state, but you might lose many important transactions. Thus, Flashback Table may be a better alternative. If you have Flashback logging enabled, Flashback Database may throw out some good transactions with the bad ones. However, the time to recover is dramatically reduced because you do not need to restore backup datafiles before the recovery operation. Additionally, flashing back using the Flashback logs will often take significantly less time than applying archived and redo log files to the restored datafiles.
Following are some typical scenarios for which you must perform incomplete recovery:
You lose a datafile and a required archive redo log file is missing.
You are using the database as a test database and you want to rewind the state of the database to a point in time in the past so that you can repeat your tests.
You want to roll back the database to a point in time before a serious user error.
The database has corrupt blocks.
The following sections provide some guidelines for the type of incomplete recovery to perform, which usually depends on the type of error that has occurred. In addition, you’ll see an example of cancel-based incomplete recovery. Recovery operations discussed prior to this didn’t specify a stopping point for redo log file application:
As you remember from earlier in the chapter, AUTOMATIC does not prompt for each archived or online redo log file. It does, however, apply all archived and online redo logs to the datafiles to bring the database up to the last committed transaction.
Choosing the PITR Method
You have three options when choosing a point-in-time, or incomplete, recovery method:
Specifying a time at which to stop
Choosing an SCN at which to stop
Specifying CANCEL during the recovery process
You would use time-based incomplete recovery if you knew the time of day when the corruption occurred. For example, suppose the data entry department started entering the wrong invoices into the system at 9 A.M., or a power surge from a storm at 11:00 A.M. caused a temporary disk controller failure that wrote random data to data blocks in the Oracle datafiles. Here’s an example of time-based recovery:
SCN-based incomplete recovery works well if your applications save the SCN at critical points in a process flow, or the alert or trace logs indicate a serious error as of a particular SCN. Here’s an example of how you would automatically apply all archived redo log files to a restored backup up to SCN 30412:
Finally, you would typically use cancel-based incomplete recovery if you have a gap in the archived redo log files during the recovery process. This allows you to stop the recovery process before the RECOVER command tries to apply the missing archived redo log file. Here is an example:
Note that you can use AUTOMATIC with all types of incomplete recovery just as you can with complete recovery. Leaving off AUTOMATIC gives you a little more control during the recovery process. For example, you still want to stop recovery at a particular point in time, but you may want to specify an alternative archived redo log file (from a duplexed archive log file location) for a damaged redo log file in the default location.
Regardless of the incomplete recovery method you use, you must open the database with RESETLOGS to start the log sequence over at 1 with a new database incarnation. Although you can easily recover a database through previous incarnations, it is unlikely you will use the orphaned backups from previous incarnations either because a gap exists in the archived redo log stream or because the data from the orphaned backup is unusable from a business perspective.
Performing User-Managed, Time-based Incomplete Recovery
You perform incomplete recovery much like you would complete recovery. You must have available backups of all datafiles along with all archived redo log files to bring all datafiles up to the desired point in time. Here are the steps:
1. Shut down the database if it is not already down.
2. Restore datafiles from a backup location to the current location.
3. Start up the database in MOUNT mode.
4. Recover the database using the RECOVER command.
5. Open the database with RESETLOGS.
EXERCISE 6-4
Perform Incomplete Time-based Recovery
For this exercise, several critical tables were dropped at 11:05 A.M. You have decided that the best recovery option is to perform time-based incomplete recovery on the entire database. Recover the database as of 11:00 A.M. using the most recent full backup.
1. Shut down the database:
2. Copy backups of all datafiles to the current datafile location:
3. Start up the database in MOUNT mode:
4. Perform a manual time-based incomplete recovery as of 11:00 A.M.:
Notice in this example that you were not prompted to apply any archived redo log files. The recovery operation started shortly after the corruption occurred in the database. So all redo information required to recover the database was present in the online redo log files. You can see this in the alert log with online redo log recoveries only, as shown next:
If the recovery process needed archived redo log files (which it didn’t), you would see these types of entries in the alter log:
5. Finally, open the database with the RESETLOGS option:
Note the SQL*Plus command ARCHIVE LOG LIST, which shows you that the log sequence number has been reset to 1 and the RESETLOGS operation has created a new database incarnation.
CERTIFICATION OBJECTIVE 6.06
Perform User-Managed and Server-Managed Backups
The types of user-managed backup you can perform are dependent on whether your database is running in ARCHIVELOG mode. This book has stressed the distinct advantages of running your database in ARCHIVELOG mode, with only minor disadvantages. If you are not running in ARCHIVELOG mode, you must shut down the database to perform a backup using operating system commands. If you are using ARCHIVELOG mode, you can put an individual tablespace or the entire database into BEGIN BACKUP mode, copy the datafiles to a backup location, and then take the database out of backup mode with END BACKUP. This can all occur while users are accessing the database, though the response time may decrease a bit while the backup occurs.
In this section, you’ll see how a user-managed backup can occur while the database is shut down (typically for a database in NOARCHIVELOG mode), and how it can occur while the database is open. Before you can perform the backup, you need to know which files to back up, which is also covered in this section. Although this objective references server-managed backups using RMAN, these were discussed thoroughly in Chapter 5 and RMAN is not covered here.
Identifying Files for Manual Backup
Whether you’re backing up your database in NOARCHIVELOG mode (closed database backup) or in ARCHIVELOG mode (open database backup), you need to identify the files you need to back up. While the database is open, you can query the dynamic performance views V$DATAFILE and V$CONTROLFILE. Here’s an example:
Note that you need to back up only one copy of the control file, because all multiplexed copies are identical. Also, you do not need to back up online redo log files.
Remember that you should never back up the online redo log files. This is very important to remember, because if you restore datafiles for complete recovery, then by restoring old copies of the redo log files you will almost certainly cause loss of committed transactions. If you lose online redo log file groups or an online redo log file group member, follow the steps at the beginning of this chapter!
Backing Up a Database in NOARCHIVELOG Mode
To perform full backup of a database in NOARCHIVELOG mode, start by shutting down the database:
Next, use operating system commands to copy the files identified in the dynamic performance views V$DATAFILE and V$CONTROLFILE:
The datafiles are in a consistent state because the database is shut down. In other words, the SCNs for all datafiles are the same. Note that you can run the operating system backup commands within SQL*Plus by using the escape character, !. Finally, restart the database:
Backing Up a Database in ARCHIVELOG Mode
A distinct advantage to using ARCHIVELOG mode is that you can perform online backups (also known as hot backups), while the database is available to users. Users could experience a slight decrease in response time, depending on the system load and whether you are backing up the entire database at once or one tablespace at a time. The backup of a given tablespace’s datafile could be inconsistent with other datafile backups (in other words, the SCNs are not the same). If you are running your database in ARCHIVELOG mode, the recovery process can use archived and online redo log files to recover all datafiles to the same SCN.
You can back up the entire database while the database is online (with ALTER DATABASE BEGIN BACKUP). Typically you would back up only one tablespace at a time to minimize response time delays for users accessing the database while it is online. In addition, you should perform online backups of tablespaces with frequent Data Manipulation Language (DML) activity more often than tablespaces with low activity, and far more often than read-only tablespaces.
To back up the datafiles for an individual tablespace, you use the data dictionary view DBA_DATA_FILES instead of the dynamic performance view V$DATAFILE. This is because DBA_DATA_FILES associates the tablespace name with its datafiles, as the association between the datafile name and its tablespace might not be obvious by viewing V$DATAFILE. Here is a query against DBA_DATA_FILES:
Note in this example that the USERS tablespace contains two datafiles and that both must be backed up to perform media recovery successfully for the USERS tablespace. To enable operating system backup of the USERS tablespace’s datafiles, put it in backup mode as follows:
Next, perform an operating system copy command, much as you did with a full database backup in NOARCHIVELOG mode:
Finally, terminate backup mode for the USERS tablespace:
It is important that you end backup mode for a tablespace. The database will not shut down if any tablespaces are in backup mode, and leaving a tablespace in backup mode continues to generate and retain unnecessary redo after you complete the backup of the tablespace.
CERTIFICATION OBJECTIVE 6.07
Identify the Need for Backup Mode
Backup mode is required for any backup of a tablespace or the entire database because of the nature of a data block: Copying a datafile at the same time that the Oracle Database Writer (DBWR) process is updating the block can cause a fractured block. In other words, the header and footer of the block are not consistent at a given SCN.
Oracle provides two solutions for this problem. If you use RMAN to back up your datafiles, RMAN automatically rereads the block over and over until it is consistent. If the block doesn’t become consistent after a fixed number of retries, then RMAN determines that the block is permanently corrupted and reports an error.
If you are not using RMAN, you must use ALTER DATABASE BEGIN BACKUP or ALTER TABLESPACE . . . BEGIN BACKUP. For both of these commands, Oracle generates additional redo for the database or for the individual tablespace, respectively. Each block modified while the datafile is in backup mode is written to the redo log before any changes are applied to the block. In other words, Oracle saves the before image of the block in the redo stream. And, of course, the changes to a block are written to the redo stream as well. During recovery, Oracle can use the copy of the block in the redo stream if the recovery process detects a fractured block.
CERTIFICATION OBJECTIVE 6.08
Back Up and Recover a Control File
Although the control file is one of the smaller files in your database environment, it is critical to the operation of the database because it contains the metadata for all objects in your database. The control file contains datafile locations, online redo log file locations, and so forth. Therefore, it is wise not only to multiplex the control file in several locations but to back it up frequently. In the following sections, you’ll learn how to back up the control file while the database is available to users. And when disaster strikes, you’ll learn how to recover from the loss of one or more copies of your control file.
Backing Up the Control File in ARCHIVELOG mode
Backing up the control file for a database running in ARCHIVELOG mode produces the same end result as the method you use in NOARCHIVELOG mode. The only difference is that you can use two different SQL commands to perform the backup. Earlier in the chapter, you learned how to back up the control file when the database is shut down.
As you recall from Chapter 5, you can back up the control file with RMAN using the BACKUP CURRENT CONTROLFILE command.
The first method for backing up a control file in ARCHIVELOG mode creates an exact copy of the current control file to a location you specify:
The other method creates an editable script that re-creates the control file. Here is the command:
Oracle creates the script in the location where all trace files reside, which by default for the HR database is $ORACLE_BASE/diag/rdbms/hr/hr/trace. Here is an excerpt from the generated script:
Recovering the Control File
Chapter 5 covered a scenario in which one of your multiplexed control files is lost. The recovery process in this scenario is very straightforward, because you can replace the missing copy by copying one of the multiplexed copies, and then restart the database. However, even if you multiplex your control file in several locations, it is still possible that all copies of the control file can be lost due to a catastrophic failure of all disks containing the control file. In this event, you will have to use a backup copy of the control file created with one of the methods discussed in the preceding section (with hopes that not all backups failed during the hypothetical catastrophic event).
Depending on the status of the online redo log files and the status of the datafiles, you perform slightly different actions. In most cases, you must open the database with RESETLOGS after the recovery operation. Table 6-2 describes the actions you must perform for each combination of online redo log and datafile availability.
Availability of Online Redo Log Files |
Availability of Datafiles |
Recovery Procedure |
Available |
Current |
Recover the database with a restored copy of the control file, applying online redo logs if necessary. Open the database with |
Unavailable |
Current |
Re-create the control file and open the database with |
Available |
Restored from Backup |
Restore a control file from backup, perform complete recovery, and then open the database with |
Unavailable |
Restored from Backup |
Restore a control file from backup, perform incomplete recovery, and then open the database with |
TABLE 6-2 Control File Recovery Scenarios
In each of the scenarios in Table 6-2, you perform the following steps:
1. Shut down the database with SHUTDOWN ABORT (if it has not already crashed).
2. Restore the control file from backup.
3. Start up the database in MOUNT mode.
4. Start database recovery and specify BACKUP CONTROLFILE in the RECOVER command.
5. Open the database with RESETLOGS.
If the following conditions are true, you do not have to open the database with RESETLOGS, and you will not lose any committed transactions:
You manually ran CREATE CONTROLFILE or have a backup of the control file created with ALTER DATABASE BACKUP CONTROLFILE TO TRACE.
All online redo log files are available.
All datafiles are current.
All other scenarios, including using a backup control file with undamaged online redo log files and datafiles, will require opening the database with RESETLOGS.
A RECOVER command using a backup copy of the control file will look like this:
Even if all archived and online redo log files are intact, the RECOVER command will prompt for a missing archived redo log file. This indicates that unarchived changes existed in the online redo log files. In this scenario, you must manually specify the locations of each online redo log file until the RECOVER command finds the required redo information.
EXERCISE 6-5
Recover from the Loss of All Control Files
In this exercise, you’ll use a backup copy of the control file to recover from the loss of all online control files.
1. Shut down the database if it has not already crashed after the inadvertent deletion of all online control files:
2. All online redo log files and datafiles appear to be intact, along with the disks containing the original copies of the control file. Use operating system copy commands to restore a backup of the control file to the original locations:
3. Open the database in MOUNT mode and perform non-AUTOMATIC recovery so that you can manually specify online redo log files, if so required:
4. Finally, open the database with RESETLOGS:
CERTIFICATION SUMMARY
This chapter covered user-managed backup and recovery—you did not use system-managed backup and recovery methods, such as RMAN. You performed most of these backup and recovery operations with operating system and SQL*Plus commands while the database was shut down or in MOUNT mode.
The first part of the chapter started with one of the more straightforward recovery techniques: replacing a lost tempfile. The impact to the user is temporary, the database remains open, and you can create another tempfile within minutes of discovering the problem.
Next, you saw a few more complicated scenarios involving redo log file failures. Losing one member of a redo log file group, even if it is active, is easy to repair and does not impact users and their transactions. Recovering from the loss of an entire redo log group is a bit more complicated. This is because loss of committed transactions is possible if the database is currently attempting to write to the lost redo log file group.
The password file is critical to the database in that it controls the authentication of DBAs who want to connect to the database as SYSDBA. It is easy to re-create the password file, but you should back it up using operating system commands whenever you add or remove SYSDBA, SYSOPER, or SYSASM privileges for a DBA.
You can perform some backups while the database is online, either backing up one tablespace at a time or backing up all datafiles in the database. If you are running your database in NOARCHIVELOG mode, the only way you can create a consistent backup without archived redo log files is to perform the backup while the database is shut down. You learned about several user-managed methods to back up and recover your database whether you are in ARCHIVELOG or NOARCHIVELOG mode.
Finally, you saw some techniques that can be used to recover your database with a backup control file if all current copies of the control file are lost or damaged. If you frequently back up your control file (for example, after every structural database change), there is no loss of committed transactions if the data files and online redo log files are intact.
TWO-MINUTE DRILL
Recover from a Lost Tempfile
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 control file.
Recover from a Lost Redo Log Group
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 (Log Writer) 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 a log file group with a status of INACTIVE will most likely not result in the loss of committed transactions as long as the other member(s) of the log file group remain intact.
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.
Recover from the Loss of the Password File
Losing a password file prevents DBAs from connecting to an open or closed instance with the SYSDBA, SYSOPER, or SYSASM privilege.
You must use a password file if you are connecting remotely and the connection is not secure.
Connecting with the SYSDBA or SYSASM privilege connects to the database as the SYS user. The SYSOPER privilege connects as PUBLIC.
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 Unix or 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.
Perform User-Managed Complete Database Recovery
If media failure occurs in your database, you will typically want to use complete recovery to bring your database back up to the state it was in before the media failure, which includes all committed transactions.
For either open or closed database recovery, you can recover the database all at once, a single tablespace at a time, or one datafile at a time.
You can query the dynamic performance view V$RECOVER_FILE to see which files need media recovery and V$RECOVERY_LOG to see the archived log files needed to recover the restored datafile(s) for complete database recovery.
You perform user-managed complete recovery after the database is restarted in MOUNT mode.
All archived redo log files required to recover the restored datafiles must be available in the default location to automate the recovery process with the AUTOMATIC clause of the RECOVER command.
If the archived redo log files required for recovery are in several locations, you can specify these locations manually during the recovery process when you do not specify the AUTOMATIC keyword.
You can perform complete open database recovery on one or more damaged tablespaces by taking the tablespace(s) offline with the ALTER TABLESPACE . . . OFFLINE TEMPORARY command.
To recover a tablespace while it is offline and the rest of the database is online, you use the RECOVER AUTOMATIC TABLESPACE . . . command after restoring the tablespace’s datafile(s) from the backup location.
Perform User-Managed Incomplete Database Recovery
For an incomplete database recovery, you follow the same steps used for complete database recovery, except that you apply only the archived and online redo log files up to the desired point in time.
For incomplete recovery, you can specify a time at which to stop or an SCN (System Change Number) at which to stop, or you can specify CANCEL during the recovery process.
You can use time-based incomplete recovery if you know the time of day when the corruption occurred.
SCN-based incomplete recovery works well if your applications save the SCN (and thus a COMMIT point) at critical points in a process flow.
You typically use cancel-based incomplete recovery if you have a gap in the archived redo log files during the recovery process.
Regardless of the incomplete recovery method, you must open the database with RESETLOGS to start the log sequence at 1 with a new database incarnation.
Perform User-Managed and Server-Managed Backups
If you are not running in ARCHIVELOG mode, you must shut down the database to perform a backup using operating system commands.
If you are using ARCHIVELOG mode, you can put an individual tablespace or the entire database into BEGIN BACKUP mode. You can then proceed to copy the datafiles to a backup location. After that, you can take the database out of backup mode with END BACKUP.
While the database is open, you can query the dynamic performance views V$DATAFILE and V$CONTROLFILE to identify the locations of all datafiles and all online controlfile copies.
When you perform a backup with the database closed, the backup is considered consistent because the SCNs for all datafiles match; all files are frozen in time.
To back up the datafiles for an individual tablespace, you use the data dictionary view DBA_DATA_FILES to see the association between tablespaces and datafile names.
The database will not shut down if any tablespaces are in backup mode.
Identify the Need for Backup Mode
Copying a datafile with an operating system command at the same time the Oracle DBWR process is updating the block can cause a fractured block.
If you use RMAN to back up your datafiles, RMAN automatically rereads the block over and over until it is consistent.
For ALTER DATABASE BEGIN BACKUP or ALTER TABLESPACE . . . BEGIN BACKUP, Oracle generates additional redo (the before-image of the block) for the database or for the individual tablespace until you take the database or tablespace out of backup mode.
Back Up and Recover a Control File
If you want to back up your control file 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 control file at the specified location.
ALTER DATABASE BACKUP CONTROLFILE TO TRACE creates an editable script that re-creates the control file in the directory $ORACLE_BASE/diag/ rdbms/>database</>instance</trace.
Losing all copies of the online control file does not lose any committed transactions if you have a recent backup copy of the control file and both the datafiles and online redo log files are intact.
You do not have to open the database with RESETLOGS after restoring your control file if you manually create the replacement control file using CREATE CONTROLFILE or you use a version of the control file script that you created with ALTER DATABASE BACKUP CONTROLFILE TO TRACE.
SELF TEST
The following questions will help you measure your understanding of the material presented in this chapter. Read all the choices carefully, because there might be more than one correct answer. Choose all correct answers for each question.
Recover from a Lost Tempfile
1. If you lose all of the tempfiles from your temporary tablespace, what is the most likely result noticed by your users?
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.
2. 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.
Recover from a Lost Redo Log Group
3. Which of the following is not a valid status for an online redo log group?
A. CURRENT
B. ACTIVE
C. INVALID
D. UNUSED
E. CLEARING
4. What is the difference between the V$LOG and V$LOGFILE views?
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.
Recover from the Loss of the Password File
5. 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.
Perform User-Managed Complete Database Recovery
6. You will perform complete closed database recovery. Put the following steps in the correct order.
1. Open the database to users.
2. Identify files needed for recovery.
3. Start up the database in MOUNT mode.
4. Bring the datafiles online.
5. Apply archived and online redo log files to roll forward.
6. Oracle applies undo to roll back uncommitted changes.
7. Restore recovery-related files containing committed and uncommitted transactions.
A. 1, 2, 7, 3, 4, 5, 6
B. 2, 7, 3, 4, 5, 1, 6
C. 2, 7, 4, 3, 5, 1, 6
D. 1, 2, 7, 3, 5, 6, 1
7. Inspect the following query against the dynamic performance views V$RECOVER_FILE, V$DATAFILE, and V$TABLESPACE:
For what type of backup or recovery related task is this view useful? (Choose the best answer.)
A. It identifies datafiles with errors along with their associated tablespaces.
B. It identifies tablespaces with errors along with all datafiles in the tablespace.
C. It identifies all tablespaces that are offline and have errors.
D. It identifies all tablespaces that are online and have errors.
E. It identifies tablespaces that have datafiles that are in need of a backup.
Perform User-Managed Incomplete Database Recovery
8. You want to perform user-managed, incomplete database recovery. Which of the following methods are not available for incomplete database recovery? (Choose all that apply.)
A. Recovery up to the last commit for a specified table
B. Recovery up to the last available archived redo log file
C. Recovery up to a specified SCN
D. Recovery up to a specified timestamp
E. Recovery until you cancel the recovery option
Perform User-Managed and Server-Managed Backups
9. Which two dynamic performance views can you use to identify files that need to be backed up, regardless of whether you are in ARCHIVELOG mode or NOARCHIVELOG mode?
A. V$DATAFILE and V$LOGFILE
B. V$DATAFILE and V$TEMPFILE
C. V$LOGFILE and V$LOG
D. V$DATAFILE and V$CONTROLFILE
Identify the Need for Backup Mode
10. Which of the following backup methods will alleviate the problem caused by fractured blocks during an online backup? (Choose all correct answers.)
A. Use ALTER DATAFILE BEGIN BACKUP.
B. Use RMAN to back up your datafiles.
C. Perform backups while in RESTRICTED mode.
D. Use ALTER DATABASE BEGIN BACKUP.
E. Use ALTER TABLESPACE BEGIN BACKUP.
Back Up and Recover a Control File
11. Which of the following commands does not back up the current control file?
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';
12. You have lost all online control files. Specify the correct order for the following tasks:
1. Restore the control file from backup or run CREATE CONTROLFILE.
2. Start database recovery and specify the keywords BACKUP CONTROLFILE.
3. Start up the database in MOUNT mode.
4. Open the database with RESETLOGS.
5. Shut down the database.
A. 5, 1, 3, 2, 4
B. 1, 5, 3, 2, 4
C. 5, 1, 3, 4, 2
D. 1, 5, 3, 4, 2
SELF TEST ANSWERS
Recover from a Lost Tempfile
1. 
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 is wrong because the database remains available for some queries and most DML activity even if the TEMP tablespace is unavailable. B is wrong because users can still perform SELECT statements that don’t need sorting or the sort operation will fit into memory. C is a wrong answer because most DML activity does not require the TEMP tablespace.
2. B. Once the missing tempfile is dropped and a new one added, the TEMP tablespace is automatically available to users.
A is wrong because dropping the tablespace is not necessary, and you cannot drop the default temporary tablespace. C is wrong because you cannot recover a temporary tablespace; there are no permanent objects in a temporary tablespace. D is wrong because the database does not need to be shut down to recover a temporary tablespace.
Recover from a Lost Redo Log Group
3. 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 valid statuses for an online redo log group.
4. 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 wrong. 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.
Recover from the Loss of the Password File
5. 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, and SYSADM privileges.
B is wrong because you do not apply redo log files to the password file. C is wrong because orapwd is valid only at an operating system command prompt.
Perform User-Managed Complete Database Recovery
6. B, Complete closed or open database recovery requires the same steps in this order.
A, C, and D are not in the correct order.
7. A. The query identifies all datafiles with errors, the tablespace in which they reside, and whether the tablespace is offline or online.
B is wrong because the error reporting is at the datafile level. C and D are wrong because the view shows datafiles with errors (not tablespaces), regardless of whether they are offline or online. E is wrong because these views are not indicative of the backup retention policy for the tablespace.
Perform User-Managed Incomplete Database Recovery
8. A and B. You cannot perform a recovery using archived and online redo log files to recover the database to a commit point for a table or as of the last archived redo log file.
C, D, and E are incorrect. All three of these options are available using the RECOVER command when you want to perform incomplete recovery.
Perform User-Managed and Server-Managed Backups
9. D. The view V$DATAFILE contains all datafiles, and V$CONTROLFILE contains a list of all copies of the control file.
A is wrong because you do not need to back up the online redo log files. B is wrong because you do not need to back up datafiles from temporary tablespaces that do not contain any permanent objects and can be easily recreated if lost. C is wrong because online redo log files do not need to be backed up and V$LOG contains information about online redo log file groups, not individual files.
Identify the Need for Backup Mode
10. B, D, and E. If you use RMAN to perform online backups, it rereads the block if the SCN in the header and footer do not match. You use ALTER DATABASE BEGIN BACKUP to perform online backups of the entire database all at once. Use ALTER TABLESPACE BEGIN BACKUP to perform a backup of one tablespace at a time.
A is wrong because you cannot put an individual datafile into backup mode. C is wrong because opening the database in RESTRICTED mode prevents non-SYSDBA users from connecting to the database but does not prevent block fracturing.
Back Up and Recover a Control File
11. B. There is no such command.
A 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 one of many ways that RMAN backs up the control file. D creates a binary copy of the control file at the specified location.
12. A. The first step is to shut down the database (with ABORT), and the last step is to open the database with RESETLOGS (if you used a backup control file or you do not have current online redo logs or datafiles).
B, C, and D are wrong, because all three of these sequences are out of order.