SELECT * FROM DBA_TIPS;

Hello everyone!

During my career as an Oracle DBA, I saw many DBAs using this tool, the Data Recovery Advisor.
This is a tool that can assist with diagnosing media failure, providing RMAN commands to solved the problems.

Let’s start:

The commands used on Data Recovery Advisor are:
list failure
advise failure
repair failure

Well, you suspect that have a media failure in your database (perhaps a bad guy removed your data file). You will use the Data Recovery Advisor to list the media failure.

Log into RMAN prompt:

[oracle@srvdb12c ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Thu May 26 23:31:17 2016

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

connected to target database: CDB1 (DBID=886153789)

RMAN> list failure;

using target database control file instead of recovery catalog
Database Role: PRIMARY

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
362        HIGH     OPEN      26-MAY-16     One or more non-system datafiles are missing

Ohh!! You have a great problem there! This output indicates that one or more datafile is missing.

When you are using the Data Recovery Advisor, the first command that you should run is the list failure.

In case of you suspect a failure in your database that Data Recovery Advisor do not detect you can run the following command:

RMAN> validade database;

Take care, depending on size of your database this command could take a long time running.

To take more details about the failure you can run command with the ID of the failure 362

RMAN> list failure 362 detail;

Database Role: PRIMARY

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
362        HIGH     OPEN      26-MAY-16     One or more non-system datafiles are missing
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 362
  Failure ID Priority Status    Time Detected Summary
  ---------- -------- --------- ------------- -------
  620        HIGH     OPEN      26-MAY-16     Datafile 24: '/u01/app/oracle/oradata/CDB1/PDB/APP01/dfapp01.dbf' is missing
    Impact: Some objects in tablespace TBSAPP01 might be unavailable

Now run the advise failure:

RMAN> advise failure;

Database Role: PRIMARY

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
362        HIGH     OPEN      26-MAY-16     One or more non-system datafiles are missing
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 362
  Failure ID Priority Status    Time Detected Summary
  ---------- -------- --------- ------------- -------
  620        HIGH     OPEN      26-MAY-16     Datafile 24: '/u01/app/oracle/oradata/CDB1/PDB/APP01/dfapp01.dbf' is missing
    Impact: Some objects in tablespace TBSAPP01 might be unavailable

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/CDB1/PDB/APP01/dfapp01.dbf was unintentionally renamed or moved, restore it
2. Automatic repairs may be available if you shutdown the database and restart it in mount mode

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

How you can see the tool already created the script for you, solve the problem, now run the repair failure:

RMAN> repair failure;

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

contents of repair script:
   # restore and recover datafile
   restore ( datafile 24 );
   recover datafile 24;

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

Starting restore at 26-MAY-16
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 00024 to /u01/app/oracle/oradata/CDB1/PDB/APP01/dfapp01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/PDBBKPS/backupset/2016_05_26/o1_mf_nnndf_TAG20160526T215516_cnhb847t_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/PDBBKPS/backupset/2016_05_26/o1_mf_nnndf_TAG20160526T215516_cnhb847t_.bkp tag=TAG20160526T215516
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 26-MAY-16

Starting recover at 26-MAY-16
using channel ORA_DISK_1

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

Finished recover at 26-MAY-16
repair failure complete

The output shows that the command was run with success, you can check if the problem remains:

RMAN> list failure;

Database Role: PRIMARY

no failures found that match specification

Always run these commands in order, without exiting RMAN between each command:

list failure
advise failure
repair failure

I hope that you enjoy!