Using PITR, we
can recover the database to a point in time right before the table was dropped.
Using PITR has the disadvantage of losing all other changes to the database from the point at
which the database was restored; this disadvantage needs to be weighed against the consequences
of the dropped table. If both options are undesirable, then another method such as Flashback
Table, Flashback Database, or tablespace point in time recovery (TSPITR) should be considered
as an alternative for recovering from these types of user errors.
Data Recovery Advisor
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.
466 Oracle Database 11g DBA Handbook
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
2 tablespace xport_dw
3 as select * from oe.lineitem_table;
as select * from oe.lineitem_table
*
ERROR at line 3:
ORA-01658: unable to create INITIAL extent for segment
in tablespace XPORT_DW
SQL>
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 corrupt
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;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1022 HIGH OPEN 05-SEP-07 One or more non-system
datafiles are corrupt
Impact: See impact for individual child failures
List of child failures for parent failure ID 1022
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
1025 HIGH OPEN 05-SEP-07 Datafile 9:
'/u02/oradata/xport_dw.
Pages:
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712