Oracle 11g Data Recovery Advisor.

The data recovery advisor automatically detects corruption or loss of
data on disk and also provides advisory and execution functions for
repair or recovery of lost data.

Example: I lost my Users Tablespace datafile for some reason.


In a production database when the user session tries to
to fetch data from the missing datafile an error will
be reported. Which can be seen by LIST FAILURE command.


(Since mine is a test environment i will use VALIDATE DATABASE
command to detect the missing datafile.)

After that execute the following command.

RMAN> list failure;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
242        HIGH     OPEN      26-JAN-12     One or more non-system
                                            datafiles are missing

8          HIGH     OPEN      14-DEC-11     One or more non-system 
                                            datafiles need media
                                            recovery



RMAN> advise failure;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
242        HIGH     OPEN      26-JAN-12     One or more non-system
                                            datafiles are missing

8          HIGH     OPEN      14-DEC-11     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

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

Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/orcl/users01.dbf was
   unintentionally renamed or moved, restore it
2. If you restored the wrong version of data file /u01/app/oracle
   /oradata/orcl/users01.dbf, then replace it with the correct one

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


Preview the commands in order to repair the corrupt datafile.

RMAN> repair failure preview;

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

contents of repair script:
   # restore and recover datafile
   sql 'alter database datafile 4 offline';
   restore datafile 4;
   recover datafile 4;
   sql 'alter database datafile 4 online';
   # recover datafile
   sql 'alter database datafile 4 offline';
   recover datafile 4;
   sql 'alter database datafile 4 online'; 

Repair the failure without asking for any confirmation.

RMAN> repair failure noprompt;

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

contents of repair script:
   # restore and recover datafile
   sql 'alter database datafile 4 offline';
   restore datafile 4;
   recover datafile 4;
   sql 'alter database datafile 4 online';
   # recover datafile
   sql 'alter database datafile 4 offline';
   recover datafile 4;
   sql 'alter database datafile 4 online';
executing repair script

sql statement: alter database datafile 4 offline

Starting restore at 26-JAN-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 00004 to /u01/app/oracle/oradata
                    /orcl/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area
/ORCL/backupset/2012_01_26/o1_mf_nnndf_TAG20120126T102249_7l1q1395_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL
/backupset/2012_01_26/o1_mf_nnndf_TAG20120126T102249_7l1q1395_.bkp
 tag=TAG20120126T102249

channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 26-JAN-12

Starting recover at 26-JAN-12
using channel ORA_DISK_1

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

Finished recover at 26-JAN-12

sql statement: alter database datafile 4 online

sql statement: alter database datafile 4 offline

Starting recover at 26-JAN-12
using channel ORA_DISK_1

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

Finished recover at 26-JAN-12

sql statement: alter database datafile 4 online
repair failure complete