Restore RMAN full backup on different server skiping set of tablespaces

This is a 10 step guide on restoring/recovering a set tablespaces on different server from RMAN full backup.
OS – RHEL -4

DB - 10.2.0.4.0

Step 1 – Start from dummy parameter file

 RMAN> startup force  

Step 2 – Restore spfile from backup set

 RMAN> restore spfile from '<FULL_PATH_TO_BACKUP_PIECE>';  

Here you can find the backup piece containing parameter file by looking at the backup log or simply it is the smallest backup piece in backupset.

Step 3 – Create pfile from spfile and do the required changes

 SQL> create pfile from spfile;  

After creating the pfile you can edit the settings of parameters so that it  would  suite the new server. Most of these parameter values are PATHs that need to be changed according to new server Oracle Home.

1.audit_file_dest

2.background_dump_dest

3.control_files

4.core_dump_dest

5.log_archive_dest_1

6.user_dump_dest

Step 4 – Create spfile from pfile, Shut Down, Startup Nomount

 SQL> create spfile from pfile;  
 SQL> shutdown abort;  
 SQL> startup nomount;  

Step 5 – Restore Controlfile

 RMAN> restore controlfile from '<FULL_PATH_TO_BACKUP_PIECE>';  

Again find the correct backup piece and provide the full path.

Step 6 – Mount the database

 SQL> alter database mount;  

Step 7 – Catalog backup pieces.

 Assuming you don’t have a rman catalog and the backup files are in different disk location than the original backup location, You need to catalog the backup pieces so that rman identifies required ones.

 RMAN> catalog backup piece <FULL_PATH_TO_BACKUP_PIECE>  

 Step 8 – Restore database (Skipping  tablespaces)

 Below is a example rman script that can be used to complete the above step,

 run {  
 allocate channel ch01 device type disk;  
 allocate channel ch02 device type disk;  
 set newname for datafile '/disk1/oradata/PACS/system01.dbf' TO '/oradata2/PACS/system01.dbf';  
 set newname for datafile '/disk1/oradata/PACS/undotbs01.dbf' TO '/oradata2/PACS/undotbs01.dbf';  
 set newname for datafile '/disk1/oradata/PACS/sysaux01.dbf' TO '/oradata2/PACS/sysaux01.dbf';  
 set newname for datafile '/disk3/oradata/PACS/pacs_cust_idx01.dbf' TO '/oradata2/PACS/pacs_cust_idx01.dbf';  
 set newname for datafile '/disk3/oradata/PACS/pacs_cust_idx02.dbf' TO '/oradata2/PACS/pacs_cust_idx02.dbf';  
 set newname for datafile '/disk2/oradata/PACS/PACS_REF_INDX1_1.dbf' TO '/oradata2/PACS/PACS_REF_INDX1_1.dbf';  
 set newname for datafile '/disk3/oradata/PACS/pacs_cust_idx03.dbf' TO '/oradata2/PACS/pacs_cust_idx03.dbf';  
 set newname for datafile '/disk3/oradata/PACS/pacs_cust_idx04.dbf' TO '/oradata2/PACS/pacs_cust_idx04.dbf';  
 set newname for datafile '/disk3/oradata/PACS/pacs_cust_idx05.dbf' TO '/oradata2/PACS/pacs_cust_idx05.dbf';  
 set newname for datafile '/disk3/oradata/PACS/pacs_cust_idx06.dbf' TO '/oradata2/PACS/pacs_cust_idx06.dbf';  
 set newname for datafile '/disk3/oradata/PACS/pacs_cust_idx07.dbf' TO '/oradata2/PACS/pacs_cust_idx07.dbf';  
 set newname for datafile '/disk3/oradata/PACS/pacs_cust_idx08.dbf' TO '/oradata2/PACS/pacs_cust_idx08.dbf';  
 set newname for datafile '/disk3/oradata/PACS/pacs_cust_idx09.dbf' TO '/oradata2/PACS/pacs_cust_idx09.dbf';  
 set newname for datafile '/disk1/oradata/PACS/undotbs02.dbf' TO '/oradata2/PACS/undotbs02.dbf';  
 set newname for datafile '/disk1/oradata/PACS/undotbs03.dbf' TO '/oradata2/PACS/undotbs03.dbf';  
 set newname for datafile '/disk9/oradata/PACS/pacs_cust_idx10.dbf' TO '/oradata2/PACS/pacs_cust_idx10.dbf';  
 set newname for datafile '/disk9/oradata/PACS/pacs_cust_idx11.dbf' TO '/oradata2/PACS/pacs_cust_idx11.dbf';  
 set newname for datafile '/disk9/oradata/PACS/pacs_cust_idx12.dbf' TO '/oradata2/PACS/pacs_cust_idx12.dbf';  
 set newname for datafile '/disk1/oradata/PACS/undotbs04.dbf' TO '/oradata2/PACS/undotbs04.dbf';  
 set newname for datafile '/disk8/oradata/PACS/pacs_cust_idx13.dbf' TO '/oradata2/PACS/pacs_cust_idx13.dbf';  
 set newname for datafile '/disk8/oradata/PACS/pacs_cust_idx14.dbf' TO '/oradata2/PACS/pacs_cust_idx14.dbf';  
 set newname for datafile '/disk8/oradata/PACS/pacs_cust_idx15.dbf' TO '/oradata2/PACS/pacs_cust_idx15.dbf';  
 set newname for datafile '/disk8/oradata/PACS/pacs_cust_idx16.dbf' TO '/oradata2/PACS/pacs_cust_idx16.dbf';  
 set newname for datafile '/disk8/oradata/PACS/pacs_cust_idx18.dbf' TO '/oradata2/PACS/pacs_cust_idx18.dbf';  
 restore database skip tablespace PACS_CUST_DATA1,TEMP1,PACS_CUST_INDX1,PACS_HIST_DATA1,PACS_HIST_INDX1,PACS_REF_DATA1,PACS_REF_DATA_NEW,PACS_REF_IDX,PACS_REF_INDX1,CMS_MIS_NEW,PACS_REF_SEQ1,PACS_REF_SEQ_NEW,PACS_TKT_DATA1,PACS_TKT_INDX1,PACS_TRANS_DATA1,PACS_TRANS_INDX1,USERS;  
 }  

As shown in the above script, since we are restoring to a different  server (Mount point structure) we need to set new location so datafiles of the tables spaces which we does not skip. To do that “set new name for datafile” is used inside a run block.

 set newname for datafile '<ORIGINAL_DATAFILE>' TO '<DATAFILE_LOCATION_ON_NEWSERVER>';  

Then comes the “restore database skip tablespace” since we are skipping some tablespaces.

When you have decided which tablespaces to be skipped you can use a simple select statement to create the “set newname for datafile” commands.

Eg – Below  can be used to set newname for datafiles when skipping USERS tablespace only, (Have to change the replace statement according to mount point setup)

 SELECT 'set newname for datafile '  
  ||chr(39)  
  ||name  
  ||chr(39)  
  ||' to '  
  ||chr(39)  
  || replace(name,'/oradata_old','/oradata_new')  
  ||chr(39)  
  ||';'  
 FROM v$datafile  
 WHERE ts# IN  
  (SELECT ts# FROM  
  v$tablespace where name not in('USERS'));  

 Step 9 – Rename/Offline Drop data files according to newly restored database.

After the database is restored we need to change the control file, by updating it on the new datafile locations and skipped table spaces (Since we restored the Control file from original database it will look for original setup if this step is not completed).

 To do this first we need to rename all the relocated file as below (Relocated from ‘set newname’ Rman command),

 SQL>alter database rename file '<ORIGINAL_DATAFILE>' TO '<DATAFILE_LOCATION_ON_NEWSERVER>';  

 Then We have to  offline drop the datafiles of skipped tablespaces.

 SQL> alter database datafile '’<ORIGINAL_DATAFILE>’ offline drop;  

 Again you can use a select statement to generate these similar to above set new name example.

 Step 10 – Do the Forward Recovery and open reset logs.

Now we can recover the database skipping the table spaces. If asked apply archive logs from the backups. (Copy the archive logs from the backups to the specified location and do the recovery again)

 run{  
 ALLOCATE CHANNEL CH01 DEVICE TYPE DISK;  
 recover database skip tablespace <List of Skipped Tablespaces>;   
 }  

 After recovery is completed open the database with resetlogs options,

 SQL>alter database open resetlogs;  

 That is it.