Author:  Arju

Being database administrator we need to fulfill many different requirements based on the type of recovery we need to perform. Sometimes we need to perform full database recovery, sometimes we need to recover a certain tablespace or data block, sometimes we need to recover a certain data and the list goes on. In this article we will help you minimize restore time of your database when you want to restore full database while skipping a certain tablespace. The reason behind skipping a certain tablespace may vary from database to database. You might want to skip it because it contains temporary data and restoring that data will be just a waste of time. Or it may be possible that your tablespace contains a large number of data that is not required and you want to save recovery time by skipping time.

Oracle RESTORE DATABASE command:

We know that the datafiles must be restored before you can recover them. Oracle RESTORE DATABASE command performs restore operation for all datafiles except those datafiles that are offline or are read-only. The RESTORE DATABASE command typically works for restoring, validating or previewing RMAN backups. One thing that you should keep in mind is that RESTORE DATABASE command makes RMAN restore datafiles only and not controlfiles and spfile.

Skipping Tablespace for RESTORE Operation:

You can skip certain tablespace for restore operation by using below command

RMAN> RESTORE DATABASE SKIP TABLESPACE my_TBS_name

Skipping Indexed Tablespace for RESTORE Operation:

You can skip an indexed tablespace for restore operation by using below command

RMAN>RESTORE DATABASE SKIP TABLESPACE MY_INDEX01, MY_INDEX 01_32K, MY_INDEX 02;

Skip FOREVER:

Using FOREVER keyword does not change the behavior of skip operation; rather it just excludes the specified tablespaces from the restore operation. FOREVER keyword is used with RESTORE operation is helpful in the situations where you want to avoid restoring tablespaces containing temporary data.

Specifying SKIP FOREVER TABLESPACE makes RMAN specify the DROP option of ALTER DATABASE DATAFILE...OFFLINE when RMAN makes the datafiles belonging to tablespace offline before the restore operation is performed. The DROP option prevents RMAN from recovering these files for ever and aims to drop their tablespaces once the database is opened again.

You can skip certain tablespaces from restore operation forever so that RMAN does not do anything with the skipped tablespaces again by using below command

RMAN>RESTORE DATABASE SKIP FOREVER TABLESPACE MY_INDEX01, MY_INDEX 01_32K, MY_INDEX 02; Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Oracle Recovery from an Unplanned Outage!!
   Beware of Oracle Outage with database growth!!
   Writing Portable SQL - A Guideline
   The power of Oracle table index rebuilding!!