How to transport a tablespace from Linux(32 bit) to Windows(32 bit) in Oracle 10g database?

August 3, 2009
1. Prerequest
   Both source and destination Oracle database should have the same database characterset and National character set.
   Because the data files of the tablespaces which to be tranformed are copied to the distination directory through os command, e.g., ftp, rcp, cp.
   When importing these data files and creating the tablespaces on destination database,
   Oracle won't import the logical content of these tablespaces one by one.
   Oracle just import the metadata of the objects inside these tablespaces, and reuse these data files, don't clean the content of these data files, and creates these tablespaces.
  
2. Operations on source database  
2.1. Alter the tablespaces to read only model.    
     SQL>ALTER TABLESPACE EXAMPLE READ ONLY;
     SQL>SELECT TABLESPACE_NAME,STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='EXAMPLE';
     Find out data files of these tablespaces.
     SQL>SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='EXAMPLE';

2.2. Check if these tablespaces can be transported?
     SQL>CONN SYSTEM/ORACLE@SRC_DB1;
     SQL>EXECUTE SYS.DBMS_TTS.TRANSPORT_SET_CHECK('EXAMPLE',TRUE,TRUE);
     SQL>SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS;

2.3. Check the users who owning objects on these tablespaces.
     SQL>SELECT DISTINCT OWNER FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='EXAMPLE';

2.4. Export metadata of these tablespaces to be transported.
     SQL>SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME='DATA_PUMP_DIR';
     expdp system/oracle@SRC_DB1 dumpfile=expdp_example.dmp transport_tablespaces=example
     You will find the dump file in the DATA_PUMP_DIR.

3. Using OS command to copy the expdp_example.dmp file to destination machine.
     Using OS command to copy these data files of these tablespaces to destination machine.   
     For example, the example01.dbf is the only data file of the example tablespace. It is copied to the destination machine and is put in the d:\ directory.
     The expdp_example.dmp file is copied the DATA_PUMP_DIR of the destination database.

4. Operations on destination database.
4.1. Create users from 2.3. on the destination database.
   SQL>create user hr identified by hr;
   SQL>grant dba to hr;

4.2. Import the metadata of the transformed tablespaces and create the tablespaces on destination database.
     SQL>select * from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';
     impdp system/oracle@DEST_DB1 DUMPFILE=expdp_example.dmp TRANSPORT_DATAFILES='D:\example01.dbf' LOGFILE=impdp_example.log

4.3. Check if the tablespaces have been imported.
     SQL>SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='EXAMPLE';
     SQL>SELECT * FROM HR.COUNTRIES;

5. Endian Transformation
   Check the platform of both destination and source database from their v$database views.
   Execute the following SQL on both source and destination database to determine if data files need to be transformed.
   SQL> SELECT PLATFORM_ID,PLATFORM_NAME FROM V$DATABASE;
   SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;
   If needed, use RMAN to tranform the data files' endian.
   RMAN> CONVERT DATAFILE
      2> '/hq/finance/work/tru/tbs_31.f',
      3> '/hq/finance/work/tru/tbs_32.f',
      4> '/hq/finance/work/tru/tbs_41.f'
      5> TO PLATFORM="Solaris[tm] OE (32-bit)"
      6> FROM PLATFORM="HP TRu64 UNIX"
      7> DB_FILE_NAME_CONVERT=
      8> "/hq/finance/work/tru/", "/hq/finance/dbs/tru"
      9> PARALLELISM=5;

6. Solve data pump ORA-29341 error caused by seperately located indexes
   When there are many index violations in SYS.TRANSPORT_SET_VIOLATIONS, you can add EXCLUDE=CONSTRAINT parameter in your expdp command line.
   This will cause expdp skip ORA-29341 error and expdp will work well.
   But in doing so, you should add necessary constrains on the destination database after you have imported these tablespaces.