Oracle 11g RMAN Data Recovery Advisor

The data recovery advisor helps simplify recoveries. There are 2 flavors to the Data Recovery Advisor, command line and Oracle Enterprise Manager Database Control. This blog will cover the command line.

The command line offers improvements for scripting a recovery and scheduling it via cron or another scheduling utility.

The command line utility is used via rman command line.

Lets use a missing file for our example:

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Nov 27 14:45:28 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to an idle instance.

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Nov 27 15:13:53 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 209716116 bytes
Database Buffers 318767104 bytes
Redo Buffers 5844992 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'C:\ORACLE\ORADATA\ORCL11G\USERS01.DBF'

rman target=/

Recovery Manager: Release 11.1.0.6.0 - Production on Tue Nov 27 15:17:51 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: ORCL11G (DBID=744414708, not open)



RMAN can list any failures

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 27-NOV-07 One or more non-system datafiles are
missing

If there are no failures then Oracle will return a message:
using target database control file instead of recovery catalog
no failures found that match specification

The message indicates that I have a non-system tablespace databfile missing. A missing datafile is considered critical and therefore Priority is high.

Since it is a non-system tablespace datafile the database can stay up with the tablespace this datafile goes with offline.


Can list the detail on the failure.

RMAN> list failure 222 detail ;

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

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
222 HIGH OPEN 27-NOV-07 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
---------- -------- --------- ------------- -------
225 HIGH OPEN 27-NOV-07 Datafile 4: 'C:\ORACLE\ORADATA\ORC
L11G\USERS01.DBF' is missing
Impact: Some objects in tablespace USERS might be unavailable



RMAN will advise on automated repair options.

RMAN> advise failure ;

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

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
222 HIGH OPEN 27-NOV-07 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
---------- -------- --------- ------------- -------
225 HIGH OPEN 27-NOV-07 Datafile 4: 'C:\ORACLE\ORADATA\ORC
L11G\USERS01.DBF' is missing
Impact: Some objects in tablespace USERS might be unavailable

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 device type=DISK
analyzing automatic repair options complete

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

Optional Manual Actions
=======================
1. If file C:\ORACLE\ORADATA\ORCL11G\USERS01.DBF was unintentionally renamed or
moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 4
Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\oracle\diag\rdbms\orcl11g\orcl11g\hm\reco_1022222764.hm



Now we can have RMAN give us a preview of the failure repair.

RMAN> repair failure preview ;

Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\oracle\diag\rdbms\orcl11g\orcl11g\hm\reco_1022222764.hm

contents of repair script:
# restore and recover datafile
restore datafile 4;
recover datafile 4;




RMAN has the ability to automatically repair the failure.

RMAN> repair failure ;

Strategy: The repair includes complete media recovery with no data loss
Repair script: c:\oracle\diag\rdbms\orcl11g\orcl11g\hm\reco_1022222764.hm

contents of repair script:
# restore and recover datafile
restore datafile 4;
recover datafile 4;

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

Starting restore at 27-NOV-07
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 C:\ORACLE\ORADATA\ORCL11G\USERS0
1.DBF
channel ORA_DISK_1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\ORCL
11G\BACKUPSET\2007_11_27\O1_MF_NNNDF_TAG20071127T144712_3NRX264W_.BKP
channel ORA_DISK_1: piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL11G\BACKUPSET
\2007_11_27\O1_MF_NNNDF_TAG20071127T144712_3NRX264W_.BKP tag=TAG20071127T144712
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 27-NOV-07

Starting recover at 27-NOV-07
using channel ORA_DISK_1

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

Finished recover at 27-NOV-07
repair failure complete

Do you want to open the database (enter YES or NO)? YES
database opened