Migration from one OS Platform to another (with different endian formats)

A transportable tablespace allows you to quickly move a subset of an Oracle database from one Oracle database to another. Beginning with the Oracle10g database, a tablespace can always be transported to a database with the same or higher compatibility setting, whether the
target database is on the same or a different platform.

First check whether the tablespace is self contained i.e. all dependancies are within the ts itself.

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('research, another_ts', TRUE);
SQL> SELECT * FROM transport_set_violations;

Find the Oracle internal name and endian for the host & target OS using

SQL> select name, platform_id,platform_name from   v$database;
SQL> SELECT PLATFORM_ID, PLATFORM_NAME, ENDIAN_FORMAT
 2 FROM V$TRANSPORTABLE_PLATFORM;

The following CONVERT to PLATFORM is required only if the Host & target OS are of different ENDIANS
% rman TARGET /
RMAN> CONVERT TABLESPACE research
2> TO PLATFORM 'Microsoft Windows NT' 
3> FORMAT='/tmp/oracle/transport_windows/%U'
4> PARALLELISM = 4;

To preserve the filenames from host use the following instead of format:
db_file_name_convert '/usr/oradata/dw10/dw10','/home/oracle/rman_bkups' 

Copy the converted datafiles (binary FTP) research_file.dbf to the target machine
Use the Export utility to create the file of metadata information after making the tablespace ReadOnly

alter tablespace research read only; 
exp tablespaces=research transport_tablespace=y file=exp_ts_research.dmp

: if DATAPUMP is to be used

expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_ts_research.dmp \
LOGFILE=exp_ts_research.log TRANSPORT_TABLESPACES=research, another_ts \
TRANSPORT_FULL_CHECK=Y

The metadata information file should be moved from its location and the converted datafiles in directory as in FORMAT to their respective target directories on the destination
Then plug the tablespace(s) into the new database with the Import utility

imp tablespaces=research transport_tablespace=y file=exp_ts_research.dmp datafiles='research_file.dbf'

: importing Using datapump

impdp system/manager DIRECTORY=my_dir2 DUMPFILE=exp_ts_research.dmp
LOGFILE=imp_tbsp.log TRANSPORT_DATAFILES=\('research_file.dbf',
'another_ts_file.dbf'\) REMAP_SCHEMA=\(np:np\)
TRANSPORT_FULL_CHECK=Y 

If the busy OLTP source database cannot spare processor overhead for the conversion, the conversion may be carriedout at the Target database using the following:

RMAN> convert
2> datafile '/usr/oradata/dw10/dw10/users01.dbf'
3> format '/home/oracle/rman_bkups/%N_%f';

You can only use CONVERT DATAFILE when connected as TARGET to the destination database and converting datafile copies on the destination platform

Note: CONVERT DATABASE may be used on read only database as target only if the endian of the destination database and the source are the same.


Using External Tables, in 10g fast transport of selective column data (to Datawarehouses) is possible

create table trans_dump
organization external
(
   type oracle_datapump
   default directory dump_dir
   location ('trans_dump_1.dmp','trans_dump_2.dmp')
)
parallel 4
as
select * from trans
/

The files generated are readable across platform (like export dump)

Further readings on Transportable tablespaces
http://www.rampant-books.com/art_otn_transportable_tablespace_tricks.htm