Oracle RMAN 11g Backup and Recovery (103 page)

Loss of an Active but Not Current Online Redo Log Group

If you suffer the loss of an
active online redo log group,
you will need to use the
alter database
clear unarchived logfile
command as shown in the previous section. This is because the active online redo log will not have been archived, and you need to indicate to Oracle that this is okay.

This command will rebuild the online redo log and allow Oracle to proceed with normal operations.

You should always back up the database after this operation.

Loss of the Current Online Redo Log Group

If you want to have a bad day, losing the
current online redo log group
probably would do it for you. If you have lost the current online redo log group, you probably will experience some loss of data. When you lose the current online redo log group, you can expect that the database will shut down, and not in the normal, pleasant kind of way that you would like.

If you are lucky and the database has not yet shut down, you should immediately attempt to reduce the overall loss of data by checkpointing the database using the
alter system checkpoint
command, and then shut down the database afterward as soon as practical. The
alter system
checkpoint
command forces the database to write any dirty blocks from the database buffer cache to the database datafiles.

You may be able to open the database without any recovery being required. To try to restart the database:

1.
Issue the
startup mount
command.

2.
Issue the
alter database clear unarchived logfile
command for the redo log group that was lost. Examples of this command can be seen in earlier sections of this chapter.

3.
Issue the
alter database open
command.

If the database opens successfully, you are in luck and a celebration is warranted.

Chapter 12: RMAN Restore and Recovery
297

If the database fails to open, you are in a bad way. You will need to perform incomplete recovery of the database. Incomplete recovery with RMAN is covered in Chapter 14 of this book.

The Data Recovery Advisor

In Oracle Database 11
g
, Oracle introduced a new feature called Automatic Diagnostic Repository (ADR)
.
The ADR provides a great deal of new information and many new tools that ease database administration. In this section, we want to discuss one of these new tools, the Data Recovery Advisor.

The Data Recovery Advisor has both an OEM interface and a command-line interface. In this section, we will cover the command-line interface. If you wish to use the OEM interface, please refer to Chapter 13, which contains much more information about using OEM and RMAN.

Using the Data Recovery Advisor Through RMAN

To use the manual interface into the Data Recovery Advisor, you simply use the RMAN

command-line interface. Oracle has added new RMAN commands to allow you to execute the Data Recovery Advisor from the command line. These commands are

■
list failure

■
advise failure

■
repair failure

■
change failure

All of the Data Recovery Advisor (DRA) commands will work when the database instance is started. If the failure is new and the database has been shut down, you will often not get any results until you have tried to open the database and a failure has been detected. Once a failure has been detected, though, the DRA will remember the failures, and you can access those failures with the
advice failure
command with the database in nomount, mount, or open stage.

The state that the database needs to be in when repairing failures depends on the nature of the failure. For example, for a control file recovery, the database will be in nomount stage. If you are repairing missing data files, the
repair failure
command will require the database to be mounted or open.

We have also found cases where the
repair failure
command does not quite complete the job.

The most common case seems to be where control file recoveries occur. Often, the
repair failure
command has to be followed up with a
recover database
command and then with an
alter database
open resetlogs
command for the restore to complete. Still, the DRA is a good start to helping the Junior DBA figure out how to deal with a database in trouble.

Typically, when dealing with a data corruption error, the workflow will be to use the
list
failure
command, then the
advise failure
command, and finally, the
repair failure
command, in that order. Let’s look at the use of these commands in a bit more detail.

The list failure Command
The RMAN
list
command now has a new
failure
parameter that will list detected failures and their priorities (Critical, High, or Low), status (Open or Closed), when they occurred, and a summary of the failure. In this context, a failure is any persistent data corruption that currently exists on your system. Here is an example of the
list failure
command: RMAN> list failure;

using target database control file instead of recovery catalog

298
Part II: Setup Principles and Practices

List of Database Failures

Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

187 HIGH OPEN 05-JUN-09 One or more non-system

datafiles are missing

Note that in the preceding sample output, the datafiles that are missing are not listed. You can use the
list failure detail
command to generate additional details on the failure. Additionally, the
list failure exclude failure
n
command allows you to exclude specific failure numbers from the report output. Other options include listing only closed failures, only critical failures, only failures with high or low priorities, and listing or excluding failures by failure ID. Here are some examples of the use of these options:

RMAN> list failure detail;

List of Database Failures

Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

187 HIGH OPEN 09-JUN-09 One or more non-system

datafiles are missing

List of child failures for parent failure ID 187

Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

2075 HIGH OPEN 09-JUN-09 Datafile 6:

'C:\ORACLE\ORADATA\ROB11GR4\ROB11GR4\BOOGLE01.DBF' is missing

Impact: Some objects in tablespace BOOGLE might be unavailable

-- Let's exclude failure id 187…

RMAN> list failure exclude failure 187;

no failures found that match specification

The following table gives a complete list of all the options available on the
list failure
command:

Option

Description

ALL

List all failures.

CRITICAL

List only failures with a priority of critical.

HIGH

List only failures with a priority of high.

LOW

List only failures with a priority of low.

CLOSED

List only failures that are closed.

EXCLUDE FAILURE

From the list, remove the failure numbers listed. This option is followed by a comma-delimited list of failure IDs that you want to exclude.

DETAIL

Provide additional detail for the failure listed.

#

This is the actual failure number to be listed.

Chapter 12: RMAN Restore and Recovery
299

Here are some additional examples (note that some unimportant output was removed for brevity’s sake…we do like trees, after all!):

RMAN> list failure closed 254;

List of Database Failures

Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

254 HIGH CLOSED 04-JUN-09 Redo log file

C:\ORACLE\ORADATA\DOODLE\REDO03.LOG is corrupt

RMAN> list failure closed exclude failure 242,248;

List of Database Failures

Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

8298 CRITICAL CLOSED 03-AUG-09 System datafile 1:

'C:\ORACLE\ORADATA\DOODLE\SYSTEM01.DBF' needs media recovery

8295 CRITICAL CLOSED 03-AUG-09 Control file needs media recovery 8104 CRITICAL CLOSED 03-AUG-09 Control file

C:\ORACLE\ORADATA\DOODLE\CONTROL03.CTL is missing

8101 CRITICAL CLOSED 03-AUG-09 Control file

C:\ORACLE\ORADATA\DOODLE\CONTROL02.CTL is missing

8098 CRITICAL CLOSED 03-AUG-09 Control file

C:\ORACLE\ORADATA\DOODLE\CONTROL01.CTL is missing

NOTE

The
list failure
command can only be run on a single instance
database (thus, the RAC cluster must now be brought to single