In a busy DBA's schedule, you may be aware of a database failure (usually from a user's phone call or e-mail), but you don't know the specific cause; using the Data Recovery Advisor, new to Oracle Database 11g, you can zoom in on the failure without checking the alert log or trace files.

The Data Recovery Advisor is available at the RMAN command line or in Oracle Enterprise Manager, as you'll see in the following paragraphs.
In this scenario, the datafile for the tablespace XPORT_DW was accidentally deleted by the system administrator; the next time one of the users tries to create a table in this tablespace, they get this message:

SQL>CREATE TABLE DAILY_LINEITEM TABLESPACE XPORT_DW
AS SELECT * FROM OE.LINEITEM_TABLE;
ERROR AT LINE 3:
ORA-01658: UNABLE TOCREATE INITIAL EXTENT FOR SEGMENT IN TABLESPACE XPORT_D

You get an instant message from the user notifying you of the failure, and since you're already at the RMAN command-line interface, you use the list failure command to see what the problem might be:

RMAN> LIST FAILURE;
List of DATABASE Failures =========================
Failure ID Priority STATUS Time Detected Summary
---------- -------- --------- ------------- -------
1022 HIGH OPEN 05-SEP-07 One OR more non-system datafiles are corrup

There is only one failure, so you drill down into the failure using the Failure ID and the detail option of the list failure command:

RMAN> LIST FAILURE 1022 DETAIL;
RMAN> ADVISE FAILURE;
RMAN> SQL 'ALTER DATABASE DATAFILE 9 OFFLINE';
RMAN> RESTORE DATAFILE 9;
RMAN> RECOVER DATAFILE 9;
RMAN> SQL 'ALTER DATABASE DATAFILE 9 ONLINE';
Go to top
JSN Boot template designed by JoomlaShine.com