Goal

Starting with Oracle Database 10g, you can transport tablespaces across platforms. In this note there is a step by step guide about how to do it with ASM datafiles and with OS filesystem datafiles.

If your goal is to migrate a database to different endian platform, the following high-level steps describe how to migrate a database to a new platform using transportable tablespace:

1.- Create a new, empty database on the destination platform.
2.- Import objects required for transport operations from the source database into the destination database.
3.- Export transportable metadata for all user tablespaces from the source database.
4.- Transfer data files for user tablespaces to the destination system.
5.- Use RMAN to convert the data files to the endian format of the destination system.
6.- Import transportable metadata for all user tablespaces into the destination database.

You could also convert the datafiles at source platform and once converted transfer them to destination platform.

From 11.2.0.4, 12C and further, if converting to Linux x86-64 consider to follow this doc:

Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup [1389592.1]

For 12c you can also refer

12c How Perform Cross-Platform Database Transport to different Endian Platform with RMAN Backup Sets Document 2013271.1

Solution

Supported platforms

You can query the V$TRANSPORTABLE_PLATFORM view to see the platforms that are supported and to determine each platform’s endian format (byte ordering).

SQL> COLUMN PLATFORM_NAME FORMAT A32
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

If the source platform and the target platform are of different endianness, then an additional step must be done on either the source or target platform to convert the tablespace being transported to the target format. If they are of the same endianness, then no conversion is necessary and tablespaces can be transported as if they were on the same platform.

Transporting the tablespace
1. Prepare for export of the tablespace.

• Check that the tablespace will be self contained:

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

Note: these violations must be resolved before the tablespaces can be transported.

• The tablespaces need to be in READ ONLY mode in order to successfully run a transport tablespace export:

SQL> ALTER TABLESPACE TBS1 READ ONLY;
SQL> ALTER TABLESPACE TBS2 READ ONLY;

2. Export the metadata.

• Using the original export utility:

exp userid=\'sys/sys as sysdba\' file=tbs_exp.dmp log=tba_exp.log transport_tablespace=y tablespaces=TBS1,TBS2

• Using Datapump export:
  First create the directory object to be used for Datapump, like in:

CREATE OR REPLACE DIRECTORY dpump_dir AS '/tmp/subdir' ;
GRANT READ,WRITE ON DIRECTORY dpump_dir TO system;

Then initiate Datapump Export:

expdp system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir TRANSPORT_TABLESPACES = TBS1,TBS2

If you want to perform a transport tablespace operation with a strict containment check, use the TRANSPORT_FULL_CHECK parameter:

expdp system/password DUMPFILE=expdat.dmp DIRECTORY = dpump_dir TRANSPORT_TABLESPACES= TBS1,TBS2 TRANSPORT_FULL_CHECK=Y

If the tablespace set being transported is not self-contained then the export will fail.

3. Use V$TRANSPORTABLE_PLATFORM to determine the endianness of each platform. You can execute the following query on each platform instance:

SELECT tp.platform_id,substr(d.PLATFORM_NAME,1,30), ENDIAN_FORMAT
FROM VTRANSPORTABLE_PLATFORM tp, VDATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

If you see that the endian formats are different and then a conversion is necessary for transporting the tablespace set:

RMAN> convert tablespace TBS1 to platform="Linux IA (32-bit)" FORMAT '/tmp/%U';

RMAN> convert tablespace TBS2 to platform="Linux IA (32-bit)" FORMAT '/tmp/%U';

Then copy the datafiles as well as the export dump file to the target environment.

4. Import the transportable tablespace.

– Using the original import utility:

imp userid=\'sys/sys as sysdba\' file=tbs_exp.dmp log=tba_imp.log transport_tablespace=y datafiles='/tmp/....','/tmp/...'

• Using Datapump:

CREATE OR REPLACE DIRECTORY dpump_dir AS '/tmp/subdir';
GRANT READ,WRITE ON DIRECTORY dpump_dir TO system;

Followed by:

impdp system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir TRANSPORT_DATAFILES='/tmp/....','/tmp/...' REMAP_SCHEMA=(source:target) REMAP_SCHEMA=(source_sch2:target_schema_sch2)

You can use REMAP_SCHEMA if you want to change the ownership of the transported database objects.

5. Put the tablespaces in read/write mode:

SQL> ALTER TABLESPACE TBS1 READ WRITE;
SQL> ALTER TABLESPACE TBS2 READ WRITE;

Using DBMS_FILE_TRANSFER
You can also use DBMS_FILE_TRANSFER to copy datafiles to another host.

From 12c and in 11.2.0.4 DBMS_FILE_TRANSFER does the conversion by default. Using DBMS_FILE_TRANSFER the destination database converts each block when it receives a file from a platform with different endianness. Datafiles can be imported after they are moved to the destination database as part of a transportable operation without RMAN conversion.

In releases lower than 11.2.0.4 you need to follow the same steps specified above for ASM files. But if the endian formats are different then you must use the RMAN convert AFTER transfering the files. The files cannot be copied directly between two ASM instances at different platforms.

This is an example of usage:

RMAN> CONVERT DATAFILE
      '/path/tbs_31.f',
      '/path/tbs_32.f',
      '/path/tbs_41.f'
      TO PLATFORM="Solaris[tm] OE (32-bit)"
      FROM PLATFORM="HP TRu64 UNIX"
      DB_FILE_NAME_CONVERT= "/path_source/", "/path_dest/"
      PARALLELISM=5;

The same example, but here showing the destination being an +ASM diskgroup:

RMAN> CONVERT DATAFILE
      '/path/tbs_31.f',
      '/path/tbs_32.f',
      '/path/tbs_41.f'
      TO PLATFORM="Solaris[tm] OE (32-bit)"
      FROM PLATFORM="HP TRu64 UNIX"
      DB_FILE_NAME_CONVERT="/path_source/", "+diskgroup"
      PARALLELISM=5;

*** WARNING ***

    Index Organized Tables (IOT) can become corrupt when using Transportable Tablespace (TTS) from Solaris, Linux or AIX to HP/UX.
    This is a restriction caused by BUG:9816640.
    Currently there is no patch for this issue, the Index Organized Tables (IOT) need to be recreated after the TTS.

    See Document 1334152.1 Corrupt IOT when using Transportable Tablespace to HP from different OS.
    When using dropped columns, Bug:13001379 - Datapump transport_tablespaces produces wrong dictionary metadata for some tables can occur.See Document 1440203.1 for details on this alert.

Known issue Using DBMS_FILE_TRANSFER

=> Unpublished Bug 13636964 - ORA-19563 from RMAN convert on datafile copy transferred with DBMS_FILE_TRANSFER (Doc ID 13636964.8)
 Versions confirmed as being affected     
    11.2.0.3
 This issue is fixed in    
    12.1.0.1 (Base Release)
    11.2.0.4 (Future Patch Set)

Description

    A file transferred using DBMS_FILE_TRANSFER fails during an RMAN convert
    operation.
    eg:
     RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
     RMAN-00571: ===========================================================
     RMAN-03002: failure of conversion at target command at 01/24/2012 16:22:23
     ORA-19563: cross-platform datafile header validation failed for file +RECO/tbs_9.tf

    Rediscovery Notes:
     If RMAN convert fails on a file transferred using DBMS_FILE_TRANSFER
     then it may be due to this bug

    Workaround
     Transfer the file using OS facilities.

=> Dbms_file_transfer Corrupts Dbf File When Copying between endians (Doc ID 1262965.1)

Limitations on Transportable Tablespace Use

Transportable tablespace EXP/IMP of ASM files

Using DBMS_FILE_TRANSFER

References
BUG:9816640 – ORA-600 [6200] ORA-600 [KDDUMMY_BLKCHK] IOT CORRUPTION CODE 6401 AFTER TTS

NOTE:1493809.1 – How to Move a Database Using Transportable Tablespaces
http://www.oracle.com/technetwork/database/features/availability/maa-wp-11g-platformmigrationtts-129269.pdf

BUG:13001379 – DATAPUMP TRANSPORT_TABLESPACES PRODUCES WRONG METADATA FOR SOME TABLES
http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_ftran.htm#CHDEFEGG

NOTE:1389592.1 – 11G – Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup
NOTE:1989198.1 – TTS changed XMLTYPE from CLOB to Binary, XML Tag Syntax are Changed