Thursday, August 12, 2010

How to skip a tablespace for restore operation

Sometimes we want to restore full database except a certain tablespace. That is we need to skip a tablespace for restore operation. May be that the tablespace only contains tempoarary data and hence not useful for us. Or we are performing some database point in time recovery operation and we want to omit a big tablespace as the tablespace does not contain data needed for our requirement. In this way we can minimize our restore operation time.

With the RESTORE DATABASE command we perform all datafiles restore operation except those that are offline or read-only.

Note that RESTORE DATABASE does not work same as BACKUP DATABASE. With command BACKUP DATABASE, RMAN backs up datafiles along with controlfiles and spfile. But with RESTORE COMMAND operation, RMAN only restores datafiles.

To omit a certain tablespace for restore operation use RESTORE DATABASE SKIP TABLESPACE tablespace_name. Suppose I want to omit restore of indexed tablespace INDX01,INDX02 and INDX01_16K . Then my restore command will be,
RMAN>RESTORE DATABASE SKIP TABLESPACE INDX01,INDX02,INDX01_16K;

If you specify SKIP FOREVER TABLESPACE, then RMAN specifies the DROP option of ALTER DATABASE DATAFILE ... OFFLINE when taking the datafiles that belong to the tablespace offline before the restore. The DROP option indicates that RMAN does not intend to recover these files and intends to drop their tablespaces from the database after the database is opened again. In other words, FOREVER indicates that RMAN never intends to do anything with the skipped tablespaces again.

Suppose you want to skip forever to restore tablespace EXAMPLE,INDX01 and INDX02 then your command will be,
RMAN>RESTORE DATABASE SKIP FOREVER TABLESPACE EXAMPLE, INDX01, INDX02;

No comments: