Author: Sean McCown

It is the aim of every database administrator to provide maximum availability of data whenever some problem arises. If your backup strategy is efficient then it will ensure quick database recovery with minimal downtime. In this article we will discuss an efficient way to minimize the recovery time of your database.

Oracle Recovery with RMAN:

Oracle Recovery Manager (RMAN) is a command-line and Enterprise Manager-based tool that can help us perform a quick and efficient backup and recovery of our Oracle databases or more accurately their datafiles. RMAN allows us to take two backup types i.e., Backupset and Image copy among which backupset is the default backup type by RMAN. Using BACKUP AS COPY DATABASE command will help you take image copy of backup. If you use a flash recovery area for your backup storage then based on retention policy the backups reside in flash recovery area.

Recovering Corrupt Datafile using RMAN Backupset:

Consider a scenario where a datafile gets corrupted on a large database. Below steps will be used if we are using the default RMAN backup approach and take backup as a backupset.

As shown in figure Restore corrupted datafile from BACKUPSET is the most time consuming step in this approach and on slow media it may take more than a day restoring a datafile of about 100G size.

Recovering Corrupt Datafile using RMAN Image Copy ( Flash Recovery Area ) :

However RMAN makes your life easy by providing the IMAGE COPY backup approach whereby a bit by bit copy or OS copy of your datafile can reduce your downtime tremendously by minimizing the recovery time from days to seconds easily. You just need to issue the RMAN>RUN {BACKUP AS COPY DATABASE;} and RMAN will create the copies of your datafiles in the Flash Recovery Area. Please note that the copies o f datafiles will have automatically generated names such as o1_mf_users_ cmr7t30p .dbf for users tablespace.

Consider a scenario where a datafile gets corrupted on a large database. Below steps will be used if we are using the default RMAN backup approach and take backup as an Image Copy. Note that this approach will minimize our recovery time greatly by saving the restoring time.

Once you have made the tablespace ONLINE then you can run below command to ensure that the location of datafile with file_id 6 is pointing to the flash recovery area.

SQL>SELECT NAME FROM V$DATAFILE WHERE file# = 6;

Recovering Corrupt Datafile using RMAN Image Copy (Original Location) :

In some cases our recovery area may contain slow disk storage. In such scenarios we can get back the datafile as of original location by following below approach.

Final Words:

Using above approach can help you achieve the goal of efficient database recovery by minimizing the downtime tremendously. If you follow above approach then you can still achieve the same goal even if you are not using RMAN or FRA for your recovery process. Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Beware of Worse Oracle performance after Migration!!
   A Quick Guide to determine Oracle RAM Size!!
   Performance Boost by improving Optimizer Statistics!
   The Secrets of Oracle Scalar subqueries!!