Database Technologies‎ > ‎Oracle‎ > ‎RMAN‎ > ‎

RMAN Single Tablespace Restoration

RMAN Specific Tablespace Restoration

From the available RMAN backup, we will restore the sytem,sysaux,undo and the required tablespace

What we have, is the following backuppieces, and we wish to restore user specifiec tablespsace from that.

1) SPFILEbkp
2) Controlfilebkp
3) databasebkp

>set oracle_sid=samsung
             // This will start the instance without pfile, its just for actual spfile restoration
RMAN>restore spfile from SPFILEbkp;   // This will restore the spfile

>set oracle_sid=samsung
>sqlplus "/ as sysdba"
sql>create pfile='~\init.ora' from spfile;
sql>shutdown immediate;

Now edit the init.ora according to server environment, then start the instance.

sql>startup pfile='init.ora' nomount;
sql>create spfile from pfile='init.ora';

>set oracle_sid=samsung
RMAN>restore controlfile from 'Controlbkp';   // This will restore the control file.

Now we will start the restoration of the tablespaces.
RMAN>run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
set newname for datafile 1 to '~\samsung\df\system01.dbf';
set newname for datafile 2 to '~\samsung\SYSAUX01.DBF';
set newname for datafile 3 to '~\samsung\UNOTBS01.DBF';
set newname for datafile 6 to '~\samsung\df\TWO.DBF';
restore tablespace SYSTEM;
restore tablespace SYSAUX;
restore tablespace UNDOTBS1;
restore tablespace two;
switch datafile all;


sqlplus>alter database datafile 2 offline drop;      //We will make all the not required tablespace files offile drop
sqlplus >alter database datafile 4 offline drop;
sqlplus> recover database using backup controlfile until cancel; (or until time)

(For recover of table, we could recover the database upto the time before the table was dropped using the archivelog logs set the log_archive_dest to the directory holding the arch logs)

sqlplus> alter database rename file ‘<old server locations>’ to ‘<new server locations>’;
sqlplus> alter database open resetlogs;

Drop the tablespace not required.

sql>drop tablespace users including contents;