oracle Transportable Tablespace

Limitations on Transportable Tablespace
=========================================
The following are the limitations of using Transportable tablespace.
1.   The source and target database must use the same character set and national character set.
2.   We cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or the destination tablespace before the transport operation.
3.   Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects are in the tablespace set.
4.   Tablespaces that do not use block encryption but that contain tables with encrypted columns cannot be transported. You must use Oracle Data Pump to export and import the tablespace's schema objects. You can take advantage of Oracle Data Pump features that enable you to maintain encryption for the data while it is being exported and imported.
5.   Beginning with Oracle Database 10g Release 2, we can transport tablespaces that contain XMLTypes. Beginning with Oracle Database 11g Release 1, you must use only Data Pump to export and import the tablespace metadata for tablespaces that contain XMLTypes.
6.   We cannot transport the SYSTEM tablespace or objects owned by the user SYS. Some examples of such objects are PL/SQL, Java classes, callouts, views, synonyms, users, privileges, dimensions, directories, and sequences.
7.   Transportable tablespaces do not support 8.0-compatible advanced queues with multiple recipients

steps for Oracle TTS:
===========================

1. first need to Find the Endian fromat for target and source DB using below query.

 SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;


2. on the source DB : Check any self contained violations present in the tablespaces 
SQL> select name from v$tablespace;
NAME
 ------------------------------
 SYSTEM
 SYSAUX
 UNDOTBS1
 USERS
 TEMP
 SAMPLE

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('USERS,SAMPLE',TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;


3. on the source DB

Invoke the Data Pump export utility on the host system and specify which tablespaces are in the transportable set.

$ expdp system/sys DUMPFILE=expdata.dmp DIRECTORY=expdp_dir TRANSPORT_TABLESPACES =USERS,SAMPLE LOGFILE=expdata.log
Result: Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed 

Collect the required users,roles,grants,profile  to  create again in the target database

$ ./expdp system/sys DUMPFILE=exp_meta_dmp.dmp DIRECTORY=expdp_dir full=y INCLUDE=USER,ROLE,ROLE_GRANT,PROFILE content=METADATA_ONLY
Result: Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed 

Convert the datafiles into a backup location on the source platform. (its based on the indianess)

$ RMAN TARGET /
RMAN> CONVERT TABLESPACE 'USERS','SAMPLE' TO PLATFORM 'Linux IA (32-bit)' FORMAT='/u02/backup/%U'

RMAN> exit

4. copy the dump and backup file to target server.

$ RMAN TARGET /
RMAN> CONVERT DATAFILE '/u02/backup/data_D-RAN_I-2884613691_TSSAMPLE_FNO-5_0em3mj05'  format "/*existing database data file path*/sample01.dbf";

Result:
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
Finished conversion at target at 04-FEB-11

RMAN> CONVERT DATAFILE '/u02/ran/data_D-RAN_I-2884613691_TS-USERS_FNO4_0fm3mj0r' format '/* existing database data file path* /users01.dbf';
Result:
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished conversion at target at 04-FEB-11

RMAN> exit
Recovery Manager complete.

Import the required users, roles, grants, profiles   metadata using the Data Pump Import utility, impdp.

$ impdp system/sys DIRECTORY=expdp_dir    DUMPFILE=exp_meta_dmp.dmp full=y 

Result:
Job "SYSTEM"."SYS_IMPORT_FULL_02" successfully completed

Now Import the tablespace metadata using Data Pump Import utility, impdp.

impdp system/sys DIRECTORY=expdp_dir DUMPFILE=expdata.dmp TRANSPORT_DATAFILES='/ existing database data file path/users01.dbf','/ existing database data file path/sample01.dbf';

Result:
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed

List the tablespaces for verify the imported tablespace

SQL> select name from v$tablespace;

Enable read/write mode for the specific imported tablespaces.

SQL> alter tablespace USERS read write; Tablespace altered.
SQL> alter tablespace SAMPLE read write; Tablespace altered.
SQL> exit