Introduction:
Oracle’s transportable tablespace feature allows users to quickly move a user tablespace across Oracle databases. It is the most efficient way to move bulk data between databases.
Moving data using transportable tablespaces can be much faster than performing either an export/import or unload/load of the same data. This is because transporting a tablespace only requires the copying of datafiles and integrating the tablespace structural information. You can also use transportable tablespaces to move both table and index data, thereby avoiding the index rebuilds you would have to perform when importing or loading table data
Prior to Oracle Database 10g, if you want to transport a tablespace, both source and target databases need to be on the same platform
Oracle Database 10g adds the cross platform support for transportable tablespaces. With the cross platform transportable tablespace, you can transport tablespaces across platforms.
Endian Formats:
Even if your source and target platforms are compatible or same, you wont be able to transport the tablespace from source platform to target platform directly. Before you start transporting or copying the datafile, you need to check the endian format of the data files in the source and target platform. Endian format refers to the byte ordering in the datafile. It is just the way bytes are stored in the datafile. There are 2 types of endian formats – Big and Small.
If your target platform is not compatible with your source platform in terms of endian format, then you have to make the endian format of all the datafiles belonging to the tablespace you want to transport same. You can accomplish this using RMAN. I will let you know later how to convert the endian format. For now, we will see the platform compatibility and other details.
Determining the supported platform
First step before transporting the tablespace from source platform to target platform, you need to first check if tablespace transport is compatible. You can check the list of supported platform using following query in the database.
SQL> select * from v$transportable_platform; PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT ----------- ------------------------------------ -------------- 1 Solaris[tm] OE (32-bit) Big 2 Solaris[tm] OE (64-bit) Big 7 Microsoft Windows IA (32-bit) Little 10 Linux IA (32-bit) Little 6 AIX-Based Systems (64-bit) Big 3 HP-UX (64-bit) Big 5 HP Tru64 UNIX Little 4 HP-UX IA (64-bit) Big 11 Linux IA (64-bit) Little 15 HP Open VMS Little 8 Microsoft Windows IA (64-bit) Little PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT ----------- ------------------------------------ -------------- 9 IBM zSeries Based Linux Big 13 Linux 64-bit for AMD Little 16 Apple Mac OS Big 12 Microsoft Windows 64-bit for AMD Little 17 Solaris Operating System (x86) Little 16 rows selected.
In our case we are going to transport a tablespace from HP Itanium 64 bit platform to Linux 32 bit platform. These platforms are made bold in the above query output. As you can see the endian format of these platforms are different (HP IA 64 has big endian format and Linux IA 32 has little endian format).
You can check your database server platform using following query.
SQL> select platform_name from v$database; PLATFORM_NAME ----------------------------------------------- HP-UX IA (64-bit)
General requirements for transporting tablespaces between 2 databases
- Both platform should have same character set.
you can check the character set of both platform using following query
SQL> select * from nls_database_parameters where parameter like '%SET%'; PARAMETER VALUE ------------------------------ ---------------------------------------- NLS_CHARACTERSET WE8ISO8859P1 NLS_NCHAR_CHARACTERSET AL16UTF16
- Both database must be using Oracle 8i or higher version, but database version need not be identical
- You cannot transport SYSTEM tablespace or any object owned by SYS
- If you want to transport partitioned table, all the partitions must be included in transportable table set. If you are transporting index, all the tablespace containing respective tables also needs to be transported
- You can transport the tablespace to a target database only if it has same or higher compatibility settting
Following are the brief steps for transporting the tablespace.
1. Ensure that the tablespace is self-contained
You can check if the tablespace you are transporting is self-contained or not using TRANSPORT_SET_PROCEDURE in DBMS_TTS package. This is shown below
DBMS_TTS.TRANSPORT_SET_CHECK (
ts_list IN CLOB,
incl_constraints IN BOOLEAN DEFAULT FALSE,
full_check IN BOOLEAN DEFAULT FALSE);
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('IAS_META',TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
If you see no rows selected, that mean the tablespace is self-contained.
2. Make the tablespace read-only
Alter the tablepace to make it read-only as shown below.
SQL> alter tablespace IAS_META read only;
Tablespace altered.
You can make the tablespace read write once you export the metadata about this tablespace and convert the endian format of the tablespace (if needed).
3. Export metadata using TRANSPORTABLE_TABLESPACE data pump option
Once you make tablespace as readonly, export the metadata information about the tablespace using data pump export as shown below.
-bash-3.00$ expdp system/welcome1 TRANSPORT_TABLESPACES=IAS_META DUMPFILE=test_dir:test_exp.dmp LOGFILE=test_dir:exp_01.log
Export: Release 10.1.0.5.0 - 64bit Production on Tuesday, 22 December, 2009 17:5
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** TRANSPORT_TABL
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/CLUSTER
Processing object type TRANSPORTABLE_EXPORT/TTE_CLU_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/TTE_TABLE_OWNER_OBJGRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/TTE_FUNC_BITMAP_INDEX/TTE_FBM_INDEX_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/TTE_FUNC_BITMAP_INDEX/STATISTICS/TTE_FBM_IND_STATS/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/TABLE
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/RLS_POLICY
Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PROCDEPOBJ
Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/slot/ems6024/oracle/test_exp.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 17:56
4. Convert the datafile to match endian format
You can use RMAN to convert the data file to the endian format of the target. Following is the command for the same
-bash-3.00$ rman TARGET SYS/welcome1
Recovery Manager: Release 10.1.0.5.0 - 64bit Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database: HPTSTPW1 (DBID=2764844932)
RMAN> convert tablespace IAS_META to platform 'Linux IA (32-bit)' format '/slot/ems6024/oracle/%U';
Starting backup at 22-DEC-09
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=378 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00009 name=/slot/ems6024/oracle/AS10g/oradata/hptstpw1/ias_meta01.dbf
converted datafile=/slot/ems6024/oracle/data_D-HPTSTPW1_I-2764844932_TS-IAS_META_FNO-9_01l1igm6
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:02:10
Finished backup at 22-DEC-09
It generated the file with name "data_D-HPTSTPW1_I-2764844932_TS-IAS_META_FNO-9_01l1igm6". If you want to preserve the name or give custom name to the datafile, you can use db_file_name_convert parameter while converting the endian format.
5. Copy the file to target database
Here you need to copy both the converted file from step 4. (tablespace datafile) as well as export dump file from step 3.
6. Run import command to import the transportable tablespace
impdp system/welcome1 TRANSPORT_DATAFILES='/slot/ems5918/oracle/data_D-HPTSTPW1_I-2764844932_TS-IAS_META_FNO-9_01l1igm6' DUMPFILE=test_dir:test_exp.dmp LOGFILE=test_dir:imp_01.log
You might hit the error "UDI-00011: parameter dumpfile is incompatible with parameter transport_tablespaces" in case you are using TRANSPORT_TABLESPACES parameter in above impdp command. Please DO NOT use TRANSPORT_TABLESPACES parameter in impdp. Refer to official release metalink note ID 444756.1
Hope this helps !!
Persistent Storage Solutions 
Hi,
Very good article. Thanks for providing such great tip.
Was very helpful. please continue the good work
I’ve read some good stuff here. Definitely worth bookmarking for revisiting. I wonder how much effort you set to make the sort of magnificent informative site.