How to use the RMAN Advisory in Oracle

In ORACLE 11g, the new RMAN Data Recovery Advisory feature helps DBAs analyze data corruption and lost data files. It also offers solutions using existing backups. This feature greatly aids the recovery process in Oracle 11g.

Following is an example of a corrupted system file block. We used the RMAN Advisory feature to repair the corrupted block with a list of commands.

List Failure command in RMAN

List failure command list the existing failures in oracle database.

In example, the system datafile is having corrupted blocks

RMAN> LIST FAILURE;
List of Database Failures
=========================
Failure ID Priority Status Time Detected  Summary
---------- -------- ----------- --------- -------
151        HIGH     OPEN        18-APR-17 Datafile 1: 'E:\oracle\oradata\system01.dbf' contains one or more corrupt blocks

ADVICE FAILURE in RMAN

Advice failure command will provide the recovery solution which is best available. It analyzed and display the solution.

List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- ------ ------------- -------
151        HIGH     OPEN   18-APR-17     Datafile 1: 'E:\oracle\oradata\system01.dbf' contains one or more corrupt blocks
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

Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Perform block media recovery of block 28654 in file 1
Strategy: The repair includes complete media recovery with no data loss
Repair script: C:\oracle\scripts\reco_84879475.hm

Recovery Advisory generates a script that can be used to repair the datafile or resolve the issue. The script does all the work.

REPAIR FAILURE PREVIEW in RMAN

Repair failure preview command show us what command followed by oracle for recovery the corrupted blocks.

RMAN> REPAIR FAILURE PREVIEW;
Strategy: The repair includes complete media recovery with no data loss
Repair script: C:\oracle\scripts\reco_84879475.hm
contents of repair script:
# block media recovery
recover datafile 1 block 28654;

REPAIR FAILURE in RMAN

Execute the actual repair by issuing… it will prompt for YES..

RMAN> REPAIR FAILURE;
Strategy: The repair includes complete media recovery with no data loss
Repair script: C:\oracle\scripts\reco_84879475.hm
contents of repair script:

# block media recovery
recover datafile 1 block 28654;

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

Starting recover at 18-APR-17
using channel ORA_DISK_1
searching flashback logs for block images until SCN 35245
finished flashback log search, restored 1 blocks

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

Finished recover at 18-APR-17
repair failure complete