transport a tablespace (cross platform)

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
          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 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

:-)