How easy is this?

Ever found database recovery challenging? Well that's not the case anymore with 11g if you have proper backups. Oracle 11g makes it easier for DBA's to do the most important task that cannot be allowed to go wrong: RECOVERY!

Let's see an example of 11g Data Recovery Advisor in action:

First let's simulate a disk break:

[oracle@11g orcl]$ mv taulualue1.dbf taulualue1.bad

Starting database gives the error:

SQL> startup
ORACLE instance started

Database mounted.
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/opt/app/oracle/oradata/orcl/taulualue1.dbf'

This was where the DBA would get nervous, how do I proceed from here?

Well, 11g to the rescue :)

First let's see what wrong:

RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
222 HIGH OPEN 26-FEB-08 One or more non-system datafiles are missing

Let's drill down to the problem in little more detail:

RMAN> list failure 222 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
222 HIGH OPEN 26-FEB-08 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 222
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
5410 HIGH OPEN 26-FEB-08 Datafile 8: '/opt/app/oracle/oradata/orcl/taulualue1.dbf' is missing
Impact: Some objects in tablespace TAULUALUE1 might be unavailable

That looks nice. Now let's ask RMAN to advise the failure:

RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
222 HIGH OPEN 26-FEB-08 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 222
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
5410 HIGH OPEN 26-FEB-08 Datafile 8: '/opt/app/oracle/oradata/orcl/taulualue1.dbf' is missing
Impact: Some objects in tablespace TAULUALUE1 might be unavailable
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=649 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /opt/app/oracle/oradata/orcl/taulualue1.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 8
Strategy: The repair includes complete media recovery with no data loss
Repair script: /opt/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2754197347.hm

Let's look what kind of script RMAN has created for us:

cat /opt/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2754197347.hm

# restore and recover datafile

restore datafile 8;

recover datafile 8;

That looks perfect! Let's do what RMAN suggests us:

RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /opt/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2754197347.hm
contents of repair script:
# restore and recover datafile
restore datafile 8;
recover datafile 8;
Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script
Starting restore at 26-FEB-08
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 00008 to /opt/app/oracle/oradata/orcl/taulualue1.dbf
channel ORA_DISK_1: reading from backup piece /rman_backups/ORCL_backups/0dj9mte5_1_1
channel ORA_DISK_1: piece handle=/rman_backups/ORCL_backups/0dj9mte5_1_1 tag=TAG20080226T185357
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 26-FEB-08
Starting recover at 26-FEB-08
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 26-FEB-08
repair failure complete
Do you want to open the database (enter YES or NO)? yes
database opened

I think recovery cannot be made a lot easier than this!