Oracle 11g:RMAN restore database with skip tablespace

Oracle 11g:RMAN restore database with skip tablespace

Dear User,

Sometime we need to restore particular tablespace from rman full backup.

Sometime we need some data from one particular tablespace not whole database.

Oracle allow selection of tablespaces to be excluded from restoration.

Follow below steps for do testing.

1.Restore database with different database.

Set db_name=orcl

set db_unique_name=orcl1.

2.Start database in nomount.Restore controlfile from backup.

rman target /

startup nomount

restore controlfile from '/u01/backup/ctl_c-1381347609-20140723-00';

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oradata/orcl1/control01.ctl
output file name=/u01/oradata/orcl1/control02.ctl
Finished restore at 24-JUL-14
RMAN> 

3.Catalog backup pieces.

rman target /

catalog start with '/u01/backup'

 4.Create restore recover script.

run{
set newname for datafile 1 to '/u01/oradata/orcl1/system01.dbf';
set newname for datafile 2 to '/u01/oradata/orcl1/sysaux01.dbf';
set newname for datafile 3 to '/u01/oradata/orcl1/undotbs01.dbf';
set newname for datafile 4 to '/u01/oradata/orcl1/users01.dbf';
set newname for datafile 5 to '/u01/oradata/orcl1/example01.dbf';
set newname for datafile 6 to '/u01/oradata/orcl1/include1.dbf';
set newname for datafile 7 to '/u01/oradata/orcl1/exclude1.dbf';
restore database skip tablespace "EXCLUDE1";
switch datafile all;
recover database skip tablespace "EXCLUDE1";
sql'alter database datafile 7 offline drop';
sql'alter database open resetlogs';
}

Starting restore at 24-JUL-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

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 00001 to /u01/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/oradata/orcl/include1.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/rman_l0_02pe5b38_1_1.rman
channel ORA_DISK_1: piece handle=/u01/backup/rman_l0_02pe5b38_1_1.rman tag=TAG20140723T232112
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:25
Finished restore at 24-JUL-14

Starting recover at 24-JUL-14
using channel ORA_DISK_1

Executing: alter database datafile 7 offline
Finished recover at 24-JUL-14

RMAN> sql'alter database open resetlogs';

sql statement: alter database open resetlogs

5.Check database.If its present then drop it manually.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
INCLUDE1

7 rows selected.

SQL>