Data Recovery Advisor

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:

[code language=”css”]
[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
[/code]

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:

[code language=”css”]
RMAN> validade database;
[/code]

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

[code language=”css”]
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
[/code]

Now run the advise failure:

[code language=”css”]
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
[/code]

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

[code language=”css”]
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
[/code]

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

[code language=”css”]
RMAN> list failure;

Database Role: PRIMARY

no failures found that match specification
[/code]

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

list failure
advise failure
repair failure

I hope that you enjoy!