Author: Sanjay Kumar

On Premise Optimization


This post explains the best possible way for cross platform Oracle database migration


It depends upon the situation and available resources. There are many ways in which an Oracle database can be migrated across platforms.  The most common methods for cross platform migration are:

  1. Export and Import
  2. Goldengate/Stream
  3. Transportable Tablespaces
  4. Transportable Database

This document will cover Transportable Tablespaces.  The Oracle Goldengate and Transportable Database methods will be covered in a future document.

Transportable Tablespaces

Now, I will explain how tablespaces can now be transported from any platform to any platform in 10g, provided the platforms belong to the list below:

SQL> select * from v$transportable_platform;
----------- ------------------------------   --------------
1           Solaris[tm] OE (32-bit)          Big
2           Solaris[tm] OE (64-bit)          Big
3           HP-UX (64-bit)                   Big
4           HP-UX IA (64-bit)                Big
5           HP Tru64 UNIX                    Little
6           AIX-Based Systems (64-bit)       Big
7           Microsoft Windows NT             Little
8           Microsoft Windows IA (64-bit)    Little
9           IBM zSeries Based Linux          Big
10          Linux IA (32-bit)                Little
11          Linux IA (64-bit)                Little
12          Microsoft Windows 64-bit for AMD Little
13          Linux 64-bit for AMD             Little
15          HP Open VMS                      Little
16          Apple Mac OS                     Big

The output of this query can change. So please use the query above to find the current support platforms. In previous releases, the transportable tablespace feature allowed the transfer between platforms of the same architecture only.


1. Check for restrictions 

Review the “Limitations on Transportable Tablespace Use” section in Note 371556.1.  Among other things, objects that reside in the SYSTEM tablespace and objects owned by SYS will not be transported. This includes but is not limited to users, privileges, PL/SQL stored procedures, and views. If you use spatial indexes, apply the solution in Note 579136.1 “IMPDP TRANSPORTABLE TABLESPACE FAILS for SPATIAL INDEX)” before continuing.

2. Check that the tablespace will be self-containe

SQL> execute sys.dbms_tts.transport_set_check('TBS1,TBS2', true);
SQL> select * from sys.transport_set_violations;

These violations must be resolved before the tablespaces can be transported

3. Set the tablespace to READ ONLY

SQL> alter tablespace REPOSIT read only;

4. Export metadata

exp userid=\'/ as sysdba\' transport_tablespace=y tablespaces=reposit file=tts.dmp log=exp_tts.log statistics=none

5. Check the endianness of the target database and convert, if necessary

Case 1:

The source platform is Sun SPARC Solaris: endianness Big

The target platform is HP-UX (64-bit): endianness Big

 SQL> select PLATFORM_ID , PLATFORM_NAME from v$database;
--------------------  ------------------------------
3                     HP-UX (64-bit)

No conversion is needed for files that (1) do NOT contain UNDO/Rollback segments and (2) have a source and target OS with the same endianness. Refer to Note 415884.1 “Cross Platform Database Conversion with same Endian” to determine which files contain UNDO/Rollback segments.

Case 2:

The source platform is Microsoft WIndows NT:      endianness Little

The target platform is HP-UX (64-bit):                     endianness Big

If we move the files and import the tablespace it will error out see below importing SYS’s objects into SYS

IMP-00017: following statement failed with ORACLE error 1565:
"BEGIN sys.dbms_plugts.beginImpTablespace('TBS_TTS',37,'SYS',1,0,8192,2,57"
"); END;"
IMP-00003: ORACLE error 1565 encountered
ORA-01565: error in identifying file '/database/db101b2/V101B2/datafile/reposit01.dbf'
ORA-27047: unable to read the header block of file
HP-UX Error: 2: No such file or directory
Additional information: 2
ORA-06512: at "SYS.DBMS_PLUGTS", line 1540
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully

6. You have to convert the files locally before the import step so that the files are endian compatible

rman target /
Recovery Manager: Release - 64bit Beta
connected to target database: V101B2 (DBID=3287908659)
RMAN> convert tablespace 'REPOSIT' to platform="Linux IA (32-bit)"
 db_file_name_convert='/database/db101b2/V101B2/datafile/reposit01.dbf', '/tmp/reposit01.dbf';
Starting backup at 24-NOV-03
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=8 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00006 name=/database/db101b2/V101B2/datafile/reposit01.dbf
converted datafile=/tmp/reposit01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 24-NOV-03

The converted datafile is staged in /tmp directory until it is copied to the target server. It is available remotely on the target server after having copied them onto the server.

7. Move datafiles and export dump file

 $ftp tts.dmp


/database/db101b2/V101B2/datafile/reposit01.dbf (no conversion)


/tmp/reposit01.dbf (converted file if conversion had been required)

8. Import metadata

$ imp userid=\'/ as sysdba\' TRANSPORT_TABLESPACE=Y datafiles=/database/db101b2/V101B2/datafile/reposit01.dbf (or /tmp/reposit01.dbf ) 
file=tts.dmp log=imp_tts.log
Import: Release - Beta on Mon Nov 24 03:37:20 2003
Export file created by EXPORT:V10.01.00 via conventional path
About to import transportable tablespace(s) metadata......
. importing SYS's objects into SYS
. importing OMWB's objects into OMWB
. importing table "MTG_COL_DEP_CHG"
. importing table "SYBASE11_SYSUSERS"
Import terminated successfully without warnings.

Review the import log for warnings and errors and resolve issues before continuing. Failure to do so can result in data loss.

9. Set the imported tablespace to READ WRITE

SQL> alter tablespace reposit read write;

Tablespace altered.



All notes referenced are provided by Oracle support. Please review for the latest updates on Oracle’s support site. Here are the steps:

  1. Login to My Oracle Support (
  2. Search for “Cross Platform Database Conversion”.