Transportable tablespace from rman backupset

In this blog i'm going to show how you can transport a set of tablespace to another database on
an other server.

Source database ORP310  Server ota5070
Targer database ORA310  Server ota5071

logon to the server ota5070

First create directory where the export file and the clone database are going to be.
in our case /oracle/home/exp
Now use the create directory command in sqlplus

SQL> create directory export_dir as '/oracle/home/exp';
SQL> grant read,write on directory export_dir to migusr;

Create the rman transportable tablespace script.

run{
transport tablespace
nja_idx_l,
nja_idx_m,
nja_idx_s,
nja_tbl_l,
nja_tbl_m,
nja_tbl_s,
nja_tbl_x
transport destination '/oracle/home/exp'
auxiliary destination '/oracle/home/exp'
datapump directory export_dir
dumpfile 'nja_test_dmp'
import script 'nja_test_imp'
export log  'exp_nja_test.log'
until time "to_date('10 MAY 2010 14:26:00','DD MON YYYY hh24:mi:ss')";
}

After sucessfully runing the script you see the following file in /oracle/home/exp

$ ls –altr

-rw-rw----   1 oracle   dba      7550803968 Dec 10 16:12 nja_idx_l_01.dbf

-rw-rw----   1 oracle   dba      3775930368 Dec 10 16:12 nja_idx_l_02.dbf

-rw-rw----   1 oracle   dba      3146784768 Dec 10 16:12 nja_idx_m_01.dbf

-rw-rw----   1 oracle   dba      1153441792 Dec 10 16:12 nja_idx_s_01.dbf

-rw-rw----   1 oracle   dba      10486816768 Dec 10 16:12 nja_tbl_l_01.dbf

-rw-rw----   1 oracle   dba      10486816768 Dec 10 16:12 nja_tbl_l_02.dbf

-rw-rw----   1 oracle   dba       944775168 Dec 10 16:12 nja_tbl_l_03.dbf

-rw-rw----   1 oracle   dba      6292512768 Dec 10 16:12 nja_tbl_m_01.dbf

-rw-rw----   1 oracle   dba       629153792 Dec 10 16:12 nja_tbl_s_01.dbf

-rw-rw----   1 oracle   dba      8127520768 Dec 10 16:12 nja_tbl_x_01.dbf

-rw-rw----   1 oracle   dba       787488768 Dec 10 16:10 nja_tbl_x_02.dbf

in the /oracle/home/exp you see also two other file

$ ls –altr

-rw-rw----   1 oracle   dba           77824 Dec 10 14:48 nja_test.dmp

-rw-rw-r--   1 oracle   dba            1947 Dec 10 14:48 nja_test.imp

copy the file to the server where the target database  ORA310 is running

scp nja*.dbf oracle@ota5071:/oracle/ORA310/data01

copy the dmp file and imp file to the ota5071

scp nja_test.* oracle@ota5071:/oracle/home/exp

logon to the ota5071 and in sqlplus logon to the ora310 and do the following

SQL>create directory export_dir as '/oracle/home/exp'
SQL>grant read,write on directory export_dir to migusr;

open the script nja_test_imp and remove everything execpt this

impdp  / directory=export_dir dumpfile= 'nja_test.dmp' transport_datafiles= (

'/oracle//ORA310/data01/nja_idx_l_01.dbf',

'/oracle/ORA310/data01/nja_idx_l_02.dbf',

'/oracle/ORA310/data01/nja_idx_m_01.dbf',

'/oracle/ORA310/data01/nja_idx_s_01.dbf',

'/oracle/ORA310/data01/nja_tbl_l_01.dbf',

'/oracle/ORA310/data01/nja_tbl_l_02.dbf',

'/oracle/ORA310/data01/nja_tbl_l_03.dbf',

'/oracle/ORA310/data01/nja_tbl_m_01.dbf',

'/oracle/ORA310/data/nja_tbl_s_01.dbf',

'/oracle/ORA310/data01/nja_tbl_x_01.dbf',

'/oracle/ORA310data01/nja_tbl_x_02.dbf')

Check fist if the datafiles don't already exsist in the ORA310 database on the ota5071 server
If the exsist you got to delete them first

SQL> drop tablespace nja_idx_l including contents and datafiles cascade constraints;
SQL> drop tablespace nja_idx_m including contents and datafiles cascade constraints;
SQL> drop tablespace nja_idx_s including contents and datafiles cascade constraints;
SQL> drop tablespace nja_tbl_l including contents and datafiles cascade constraints;
SQL> drop tablespace nja_tbl_mincluding contents and datafiles cascade constraints;
SQL> drop tablespace nja_tbl_s including contents and datafiles cascade constraints;
SQL> drop tablespace nja_tbl_x including contents and datafiles cascade constraints;

start script nja_test_imp

$ cd /oracle/home/exp
$ ./nja_test_imp

this start the import of the tablespaces.
When the script is done check if the tablespaces are pluged in the ORA310 database.

SQL> select tablespace_name from dba_tablespaces
          where tablespace_name like 'NJA%';

TABLESPACE_NAME

------------------------------

NJA_IDX_L

NJA_IDX_M

NJA_IDX_S

NJA_TBL_L

NJA_TBL_M

NJA_TBL_S

NJA_TBL_X

And thats it.