# Data Recovery Advisor - Oracle 11g

The Data Recovery Advisor (DRA) tool is a new integrated solution that detects and analyzes data base failures,determines the optimal repair strategies,optionally executes the repairs,and verifies their success.

A data base failure is broadly defined here as a corruption of data or the loss of data,including the loss of entire data files. Since the DRA can perform even proactive failure diagnosis and repair,it can potentially help limit the damage that could be caused by a data failure.You can access DRA through the RMAN client or Enterprise Manager.

The ultimate goal of the DRA is to reduce the mean time to recovery (MTTR) by the provision of an automated data repair tool that enhances the Oracle Data base’s reliability.

The DRA performs an automatic diagnosis of data base failures,presents you with the repair options,and carries out the repair upon your approval of its recommendations.

The DRA consolidates multiple related failures so you can repair them efficiently.Since the DRA recommends optimal repair options,it’s pre ferable to a manual determination of the correct repair options by the DBA, which is prone to judgmental errors.

The DRA is built on the new approach to problem diagnosis and repair invol ving the Oracle Data base 11g concepts of problems, incidents, and failures.

You can use the DRA to diagnose and fix failures such as a lost or mis named datafile,physical data block corruption,inconsis tencies in the data base(one data file is older than the others,for example),I/O failures,and a down data base.The main goals of the DRA are as follows:

1. Detection of data failures before a database process discov ers the error,thus limiting potential damage by extensive corrup tion of data.
2. Automatic detection,reporting and repair of data base failures.
3. Cutting back on database downtime.
4. Minimizing disruptions for users.

The Health Monitor performs an automatic check (data integrity check) when ever it encounters a data base error.The data integrity check looks for any data failures that resulted from the data base error that initiated the auto matic check.If the check diagno ses a failure,it records pertinent information such as the errors,the symptoms,and its findings in the ADR.

The failure assessments are executed and also stored in the ADR.The DRA gro ups related findings into a data base failure,with each failure being assigned a severity level(critical or high,for example the DRA maps the failures to both automatic and manual repair options.When the DBA requests advice on the repair of a particular failure,the Data Recov ery Advisor presents the choices to you along with its advice.You can then perform the repair manually or have the DRA perform the repairs for you.

It’s important to under stand that all database errors logged in the alert log,such as an ORA-600 error,don’t automatically qualify as a failure.A failure represents a problem such as persistent data corruption that is diagnosed by a diagnostic procedure (checker) registered with the Health Monitor.

A serious database error will invoke a data integrity check, which looks for failures related to that error.Once the data base detects a failure,it records it in the ADR.Only then can you call on the Data Recovery Advisor to generate repair advice.Of course,

you can launch a proactive data integrity check anytime through the Health Monitor.Any failures that are detected by such proactive checks are treated exactly the same as if the check were issued by the data base following an error.

In addition,you can also employ the validate command (using RMAN) to check for block corruption,as shown earlier in this chapter and in Chapter 6(backup and recovery).Both the validate and backup commands in RMAN invoke an auto matic data integrity check.

You can use the DRA both through RMAN as well as through Enterprise Manager.In Enterprise Manager,the DRA is integrated with the Support Work bench as well as the Health Monitor,both of which we discussed earlier in this chapter We’ll show you how to use the DRA through both of these interfaces in the following sections.

Managing the Data Recovery Advisor Through RMAN

RMAN provides several new commands that help you perform vari ous tasks related to the management and resolution of database failures through the help of the Data Recovery Advisor.We briefly discuss these commands in the following sections.

Viewing Failures

Use the list failure command to view a list of data base failures,as shown here:

RMAN> list failure;

The results of the list failure command are captured in the V$IR_FAILURE view.The DRA consolidates related failures into a single failure. If ten data blocks are corrupted in the same datafile, the DRA will show you a single consolidated failure,although you can drill down to the sub failure level if you want.You can list a failure individually by issuing the list failure... detail command. You can restrict the list failure command’s output by specifying options such as critical,high,low,or closed,which results in the output being limited to only those failures with a particular priority level or status.By default, the list failure comm and shows all failures with the critical and high prior ities.You can also exclude specific failures from the output.Here are some examples showing how you can customize the output of the list failure command: The first command shows only those failures that have a priority level of critical,and the second command shows only currently open failures.The failure status remains open until you invoke a repair action; once you repair the fai lure,its status will be closed. It’s important to under stand that the list failure command doesn’t initiate any data integrity checks but merely lists the results of automatically executing prior checks that were initi ated in response to errors in the data base. Dealing with Failures If the data base failure is critical, you of course have no choice but to fix it pronto.Once you fix a failure,the failure is closed automatically. However,some failure may really be trivial or irrelevant to your case. In such a situation, you can use RMAN’s change failure command to expli citly close an open failure even if you haven’t done any thing to remedy the situation.You can also use this command to change the priority level of a fai lure.You can change the priority from high to low,or vice versa.You can’t, however, change a prio rity level of critical, because this will result in an error. Here’s an example showing how to use the change failure command to change the priority level of a failure: RMAN>change failure 3 priority low; If you change the status of a failure to closed before the problem is actually solved, the Data Recovery Advisor will re-create the failure with a different ID when the checker that dete cted the failure the first time is run again. Getting Repair Advice The RMAN advise failure command advises on all failures that are recorded in the automatic diagnostic repository.By default, the command lists only those failures that have a critical or high level of priority.In addition to producing a summary of all input failures,the command also provides a single recommended repair option for each failure.Often, the advise repair command presents both manual as well as automated repair options. At the end of the output of the advise failure command, RMAN generates a script listing the details of the re commended repair option.If you want to perform the repair your self, you can use this script as is or make your own modifications to it. A careful perusal of the RMAN-produced script can often help you avoid a time-consuming repair job.The script,also referred to as a manual check list, is useful under circu mstances where you can fix the failure yourself with a minor change,instead of RMAN fixing the same problem through a laborious restore and recover operation.For example,you can easily recover a deleted reference table from a test/acceptance environment rather than have RMAN restore and recover the whole production database. After you run the advise failure command,you can view the manual advice by querying the V$IR_MANUAL_CHECKLIST view,as shown here:

The MESSAGE column shows a summary of the repair advice offered by the DRA.

The advise failure command may offer just manual options or automatic repair options as well.You must,of course,perform manual actions yourself,but the DRA can perform all the automatic re pair options that it suggests.Wher ever possible,the DRA consoli dates a set of repairs so it can fix multiple failures in a single repair job.

When the DRA recommends an automatic repair option to fix a failure,it always creates a repair script that shows the exact RMAN commands it’ll use to repair the failure.For example,a scri- pt for an automatic fix of a missing datafile failure will look like the following:

If you prefer to control the repair activity yourself,you can execute the repair script manually your self (even when you are offered an automatic re pair option),with out having the DRA auto matically execute it.

Repairing the Failures

Once RMAN provides its repair recommendations for failures,you can choose to run the repair failure command to fix and close a specific failure.

If the advise failure command recommends any manual repairs,first perform these repairs before asking the Data Recovery Advisor to automatically undo the failure.You must issue the report failure command only after using an advise failure command.Here’s the basic repair failure command:

RMAN> repair failure;

If you try to use the repair failure command with out issuing the advise failure command first,you’ll get the following error:

If you don’t use any options,by default the repair failure command will use the single recommen dation made by RMAN in the previously run advise failure command.

If you’d rather not have RMAN actually repair the failure but merely want to examine RMAN’s repair actions and comments,use the preview option with the repair failure command,as shown here:

RMAN> repair failure preview;

The repair preview command merely previews the repair process but doesn’t actually start the repair job itself.The results of the repair failure command are captured in the V\$IR_REPAIR view. The following query shows how:

The SUMMARY column indicates that the repair action for all three failures is NO DATA LOSS OPTION,meaning that the failure can be repaired with no data loss.

Repairing a Missing Datafile Problem

We’ll walk you through a missing data file problem so you can under stand how the Data base Repair Advisor per forms its job.In the following example, the data base can’t be opened because of a missing data file.

The following are the steps you must follow in order to repair the problem with the help of the Data Recovery Advisor:

1. Start the RMAN client and use the database repair commands you reviewed in the previous section to fix the missing datafile problem,starting with the list failure command:
2. You can also execute a variation of the list failure command to get the individual sub failures that may have been consolidated under a single failure by the DRA by passing the unique identifier for a specific failure to the list failure command and specifying the keyword detail.Here’s an example:

RMAN>list failure 4 detail

The list failure command shown here will list all the indivi dual failures consolidated into the failure with the unique identi fier 4.

3. Use the advise failure command to get recommendations regarding the failure:
4. Before repairing the problem, use the repair failure preview command to see how RMAN plans to repair the problem:
5. Finally,direct RMAN to repair the problems it found earlier:

When you choose to let the Data Recovery Advisor open the data base,as shown in this example, the database is opened automatically, since the recov ery has already been completed in the previous step.

Managing the DRA with Database Control

Oracle recommends using Data base Control to manage the Data Recovery Advisor.Once you log in to Data base Control,choose Availability -> Manage -> Perform Recovery -> Perform Automated Repair to get to the Perform Recovery page,shown in Figure.You can also get a list of all database failures (in decreasing order of priority) and a description of each failure from the Data base Recovery Advisor page,by going to Advisor Central and clicking on the Data Recovery Advisor link.

The Perform Recovery page of Database Control has two main sections: Oracle Advised Recovery and User Directed Recovery.You can always access the Custo mized Repair section to perform data restore and recovery at the data file, tablespace,or database level. Here,you’re interested in the new Oracle Advi sed Recovery section of the Perform Recovery page,which is how you access the new Data Recovery Advisor.

The Oracle Advised Recovery section of the Perform Recovery page has a button call Advise and Recover,which is gray if the Data Recovery Advisor hasn’t detected any failures in the data base. Click the Advise and Recover button,which will be high lighted when the DRA discovers any data errors such as corrupt data blocks, for example.You can also access the DRA from the Support Workbench when the problem you are dealing with involves data corruption or some other type of data failure. In fact,the DRA is automatically re commended when dealing with these types of problems.There are two ways you can access the DRA from the Support Workbench:

• From the Checker findings subpage on the Support Workbench home page.
• From the Problem Details page.

The Perform Recovery page

SQL Test Case Builder

When you’re reporting a problem to the Oracle Support folks, it’s often difficult to gather and reproduce the exact scenario under which the problem occurred.

The new SQL Test Case Builder lets you easily capture infor mation regar ding a SQL problem and upload it to Oracle Support,so Oracle Support can re produce the problem and test it.

The SQL Test Case Builder gathers data such as the SQL query particulars, object definitions, stored code such as packages, initialization para meters, and optimizer statistics.The SQL Test Case Builder doesn’t collect the actual data used by the query.

You can use either Data base Control or the new DBMS_SQLDIAG package to access the SQL Test Case Builder. Here are the key procedures in the DBMS_ SQLIDIAG package that deal with the SQL Test Case Builder functionality:

• The explain_sql_testcase function explains a SQL test case.
• The get_sql function imports a SQL test case.
• The export_sql_testcase procedure exports a SQL test case to a directory.
• The export_sql_testcase_dir_by_inc function generates SQL test cases that correspond to an incident ID.
• The export_sql_testcase_dir_by_txt function generates SQL test cases that correspond to a SQL statement.

It’s a snap to build a SQL test case from Data base Control. Note that you can use the SQL Test Case Builder from Data base Control only when a SQL-rel ated incident occurs.Here are the steps to access the SQL Test Case Builder from Database Control: