DRA (Data Recovery Advisor)

Data Recovery Advisor:

• list failure
• advise failure
• repair failure

After dropping one datafile:

RMAN> list failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
848        HIGH     OPEN      21-MAR-12     Tablespace 9: 'DROP_TBS' is offline
722        HIGH     OPEN      21-MAR-12     One or more non-system datafiles need media recovery

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
848        HIGH     OPEN      21-MAR-12     Tablespace 9: 'DROP_TBS' is offline
722        HIGH     OPEN      21-MAR-12     One or more non-system datafiles need media recovery

analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete

Not all specified failures can currently be repaired.
The following failures must be repaired before advise for others can be given.

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
722        HIGH     OPEN      21-MAR-12     One or more non-system datafiles need media recovery

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If you restored the wrong version of data file +DATA/oratest/datafile/drop_tbs.269.778508815, then replace it with the correct one

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 7
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/oratest/oratest/hm/reco_2369864291.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/oratest/oratest/hm/reco_2369864291.hm

contents of repair script:
   # restore and recover datafile
   restore datafile 7;
   recover datafile 7;

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

Starting restore at 21-MAR-12
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to +DATA/oratest/datafile/drop_tbs.269.778508815
channel ORA_DISK_1: reading from backup piece +FRA/oratest/backupset/2012_03_21/nnndf0_tag20120321t123122_0.267.778509083
channel ORA_DISK_1: piece handle=+FRA/oratest/backupset/2012_03_21/nnndf0_tag20120321t123122_0.267.778509083 tag=TAG20120321T123122
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 21-MAR-12

Starting recover at 21-MAR-12
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 21-MAR-12
repair failure complete


RMAN> exit


Recovery Manager complete.
-bash-3.2$
-bash-3.2$
-bash-3.2$
-bash-3.2$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 21 12:36:22 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> conn drop_user
Enter password:
Connected.
SQL> select count(*) from drop_table;
select count(*) from drop_table
                     *
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '+DATA/oratest/datafile/drop_tbs.269.778509333'


SQL> conn / as sysdba
Connected.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
-bash-3.2$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Mar 21 12:37:18 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORATEST (DBID=3309052188)

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
862        HIGH     OPEN      21-MAR-12     One or more non-system datafiles are offline
848        HIGH     OPEN      21-MAR-12     Tablespace 9: 'DROP_TBS' is offline

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
862        HIGH     OPEN      21-MAR-12     One or more non-system datafiles are offline
848        HIGH     OPEN      21-MAR-12     Tablespace 9: 'DROP_TBS' is offline

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Online tablespace DROP_TBS
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/oratest/oratest/hm/reco_1895925774.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/oratest/oratest/hm/reco_1895925774.hm

contents of repair script:
   # online a offline tablespace
   sql "begin sys.dbms_ir.execsqlscript(filename => ''/u01/app/oracle/diag/rdbms/oratest/oratest/hm/reco_1466333412.hm'' ); end;";

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

sql statement: begin sys.dbms_ir.execsqlscript(filename => ''/u01/app/oracle/diag/rdbms/oratest/oratest/hm/reco_1466333412.hm'' ); end;
repair failure complete

RMAN> exit


Recovery Manager complete.