Using the Data Recovery Advisor

Purpose

This tutorial shows you how you can use the Data Recovery Advisor to diagnose and repair data failures. You can work through several scenarios (see below).

Time to Complete

Approximately 60 minutes

Topics

This tutorial covers the following topics:

 Overview
 Scenarios
 Prerequisites
 Repairing a "down" Database with Enterprise Manager
 Repairing Block Corruption with Enterprise Manager
 Repairing a "down" Database with RMAN Command-Line

Cleanup

Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

 

Overview

What Is the Data Recovery Advisor?

The Data Recovery Advisor is a tool that helps you to diagnose and repair data failures and corruptions. The Data Recovery Advisor analyzes failures based on symptoms and intelligently determines optimal repair strategies. The tool can also automatically repair diagnosed failures.

Back to Topic List

Scenarios

1. Repairing a "down" Database with Enterprise Manager

The first scenario assumes that your database is "down." In a production environment, this would be a highly critical situation, which you have to resolve as quickly as possible to "save the business." You attempt to re-open the database, but this fails. You then use the Data Recovery Advisor from Enterprise Manager (EM) DB Control to diagnose the failures. The diagnosis reveals inaccessibility of two data files. For the first data file issue, you use the manual repair option (rename a data file). This issue could have resulted from a DBA executing the ALTER DATABASE ... RENAME FILE ... command and the System Administrator neglecting to properly rename the data file in the operating system.

After fixing the first data file issue by renaming the file, you again request repair advice from the Data Recovery Advisor. For the second data file issue, you use the automated repair option to recover the lost data file. Finally, you check that the database is up and running again.

2. Repairing Block Corruption with Enterprise Manager

In this scenario, you encounter a few corrupted blocks. You utilize the Data Recovery Advisor, which selects block media recovery as the optimal recovery strategy (rather than full database or data file recovery).

3. Repairing a "down" Database with RMAN Command Line

This scenario is identical to the first one, but you go through the steps of diagnosing and repairing failures with RMAN commands.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Install the database by completing the Installing Oracle Database 11g on Linux tutorial.

2.

Download and unzip the dra.zip file into your working directory.

3.

In a terminal window, navigate into your working directory and execute the following command:

                                 
./dra_setup.sh
                              

 

4. Note that you will be working with one scenario at a time, but you do not have to choose them sequentially. Each scenario has its own additional setup that produces failures, and the remaining steps have to be completed to repair the failures.

Back to Topic List

Repairing a "down" Database with Enterprise Manager

To diagnose and repair a "down" database, perform the following steps:

1.

To set up a data failure and shut down the database, use a terminal window, navigate into your working directory, and execute the following command:

./dra1_setup.sh

The users01.dbf data file is deleted and the example01.dbf file renamed. You will diagnose and repair these failures by using both manual repair and automated repair options.

 

2.

Enter the following URL to log in to Enterprise Manager Database Control (change the <hostname> to localhost or your specific hostname).

                               
https://<hostname>:1158/em
                            

 

3.

As described in scenario 1, you encounter a "down" database. Your task is to diagnose the failure and repair it as quickly as possible. Click Startup.

 

4.

Enter the following details, and click OK.

Host Credentials
Username: oracle
Password: oracle

Database Credentials
Username: sys
Password: oracle
Connect as: SYSDBA

 

5.

Click Yes on the Startup/Shutdown Confirmation page.

 

6.

View the progress page.

 

7.

Note that the database is now in a mounted state. Click View Details for more information.

 

8.

View the database change status details and error messages, and click OK.

 

9.

Click Perform Recovery to diagnose the failures and perform a repair.

 

10.

On the Database Login page, enter the following details, and click Login.

Database Credentials
Username: sys
Password: oracle
Connect as: SYSDBA

 

11.

Enter the following details, and click Continue.

Username: oracle
Password: oracle

 

12.

Scroll down to the bottom of the page and make sure the following credentials are available. Then scroll up to the top of the page and click Advise and Recover.

Username: oracle
Password: oracle

 

13.

When the database didn't open, the Data Recovery Advisor automatically diagnosed the problem. View the result of this diagnosis. Click the + icon under Failure Description to view more details.

 

14.

To view suggested repair strategies, leave all failures selected and click Advise.

 

15.

The Manual Actions page appears, in which the Data Recovery Advisor prompts you to consider manual repairs. Do not select Continue. Perform the next step.

 

16.

This demo assumes that the example01.dbf data file was renamed by error. In a terminal window, enter the following commands to manually repair the failure:

a) To navigate into the directory that contains your data files, enter: cd $ORACLE_BASE/oradata/orcl

b) To view the content of the directory, enter: ls

c) To rename the erroneous example01 data file, enter: mv example01.dbf.old example01.dbf

d) To view your corrected file name, enter: ls

e) To exit from the terminal window, enter: exit

 

17.

Return to the Manual Actions page in Enterprise Manager. Click Re-assess Failures.

 

18.

The Data Recovery Advisor revalidates all failures and closes the ones that you manually repaired. Click the '+' icon under Failure Description to expand the node and to see any other outstanding failures.

 

19.

Note that the failure which you manually repaired is closed, but there is still another open failure. Click Advise.

 

20.

Click Continue with Advise.

 

21.

The Data Recovery Advisor generates an RMAN repair script. Click Continue.

 

22.

On the Review page, you can review the failure and the repair script which will address this failure. Click Submit Recovery Job.

 

23.

Wait for the repair execution to complete. When the Data Recovery Advisor is finished, it displays the Repair Results page. Review all repair results. Click the vertical scroll bar.

 

24.

Click and drag the vertical scroll bar until you reach the end. Note, that after a successful repair, the database is opened. Click OK.

 

25.

Enterprise Manager displays the Login page. Enter the following details, and click Login.

User Name: sys
Password: oracle
Connect as: SYSDBA

 

26.

The Availability page appears. Click the Home tab.

 

27.

On the Database Instance home page, confirm that the database instance is up and running. (If a page similar to this does not immediately appear, wait a couple of minutes and refresh the page. Also note that the graphic elements may change before the production release of the software).

Click Logout, because you completed repairing a down database by executing both a manual and an automated repair.

 

Back to Topic List

Repairing Block Corruption with Enterprise Manager

To diagnose and repair block corruption, perform the following steps:

1.

To set up block corruptions in your example01.dbf data file, use a terminal window, navigate into your working directory, and execute the following command:

./dra2_setup.sh

Block corruption errors typically appear in this form to application users. DBAs can see the same error on the Enterprise Manager DB Control home page (see several steps below). However, there is a slight delay, due to internal processing between error discovery and Enterprise Manager displaying the error.

 

2.

Enter the following URL to log in to Enterprise Manager Database Control (change the <hostname> to localhost or your specific hostname).

https://<hostname>:1158/em

 

3.

To log in to Enterprise Manager, enter the following details, and click Login.

User Name: sys
Password: oracle
Connect as: SYSDBA

 

4.

Click the Availability tab .

 

5.

Click Perform Recovery.

 

6.

On the Perform Recovery page, you see that the Data Recovery Advisor detected the corrupted blocks. Click Advise and Recover.

 

7.

Click the + icon under Failure Description to expand the node for more details.

 

8.

View the details of the block corruption. Leave all failures selected and click Advise.

 

9.

View the repair script and click Continue.

 

10.

On the Review page, you can review the failures and the suggested repairs. Click Submit Recovery Job.

 

11.

You should receive a confirmation that your job was created successfully. Click View Results.

 

12.

Confirm that your job executed successfully, and click the Database tab on the top-right part of the page.

 

13.

View the block corruption alerts on the Database Instance home page. DBAs monitor this page regularly, so they would be able to see the block corruption alerts (which appear with a slight delay).

 

14.

To confirm that the block corruption is repaired, execute the following commands:

sqlplus sys/oracle as sysdba
select * from hr.regions;

 

Back to Topic

Repairing a "down" Database with RMAN Command-Line

To diagnose and repair a "down" database, perform the following steps:

1.

To set up a data failure and shut down the database, use a terminal window, navigate into your working directory and execute the following command:

./dra1_setup.sh

The users01.dbf data file is deleted and the example01.dbf file renamed. You will diagnose and repair these failures, by using both manual repair and automated repair options.

When you see the following lines, you know that your setup is completed:


2.

Because you find your database in a "down" state, you try to start it with the following commands:

sqlplus / as sysdba
startup

Note that the database is mounted and has a data file error.

 

3.

Exit SQL*Plus and start RMAN with the following commands:

exit
rman target / nocatalog


4.

List failures and failure details by executing the following commands:

LIST FAILURE;
LIST FAILURE DETAIL;

You can enter commands in uppercase or lowercase.

Note: While working with beta versions of the software, your output might vary from the proceeding screenshot.


5.

Enter the following command to diagnose the failure:

ADVISE FAILURE;

You will return to this window after the next step.

 

6.

This demo assumes that the example01.dbf data file was renamed by error. Open another terminal window and enter the following commands to manually repair a failure.

a) To navigate into the directory that contains your data files, enter: cd $ORACLE_BASE/oradata/orcl

b) To view the content of the directory, enter: ls

c) To rename the erroneous example01 data file, enter: mv example01.dbf.old example01.dbf

d) To view your corrected file name, enter: ls

e) To exit from the terminal window, enter: exit

 

7.

Return to your RMAN window and enter the following command to see the results of your manual repair:

ADVISE FAILURE;

 

8.

Enter the following command to view the repair script:

REPAIR FAILURE PREVIEW;


9.

Enter the following commands to initiate an automated repair, to restart your database, and to exit RMAN:

REPAIR FAILURE;

. . . Do you really want to execute the above repair (enter YES or NO)? YES

. . . Do you want to open the database (enter YES or NO)? YES

exit

The partial output can look like this:



10.

Enter the following commands in SQL*Plus, to test that your database is up and running:

sqlplus / as sysdba
select open_mode from v$database;
exit

 

Back to Topic

Cleanup

When you are finished with this OBE or when you wish to start again with the Prerequisites, execute the following command:

1.

Enter the following commands in a terminal window:

./dra_cleanup.sh

A partial output follows:

 

Back to Topic List

Summary

In this tutorial, you learned how to:

 Repair a "down" Database with Enterprise Manager
 Repair Block Corruption with Enterprise Manager
 Repair a "down" Database with RMAN Command Line

Back to Topic List

 Place the cursor over this icon to hide all screenshots.

 

 

 

Left Curve
Popular Downloads
Right Curve
Untitled Document