To list all OS and their endian formats
SELECT * FROM V$TRANSPORTABLE_PLATFORM;
To find the endian format of the target and source DB
SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
If the endian formats are different then datafiles need to be converted to target DB version before transporting.
2. Check if the tablespaces that are transported are self contained with referential integrity constraints taken into consideration (indicated by TRUE).
exec DBMS_TTS.TRANSPORT_SET_CHECK('dumps',TRUE);
Any violations are listed in the TRANSPORT_SET_VIOLATIONS view.
SELECT * FROM TRANSPORT_SET_VIOLATIONS;
If the set of tablespaces is self-contained, this view is empty.
3. Generate a Transportable Tablespace Set with the following steps.
1. put the source tablespaces in read only mode.
alter tablespace dumps read only;
2. Invoke the Data Pump export utility on the host system and specify which tablespaces are in the transportable set.
expdp uname/pw directory=dumpdir dumpfile=tbs.dmp logfile=tbs.log TRANSPORT_TABLESPACES =dumps
3. convert the tablespace for transport using rman if the endian types are different. If compressed backupset is enabled disable it before converting.
convert tablespace dumps to platform 'Linux 64-bit for AMD' format '/home/orace/dump%U';
4. Transport the tablespace set which includes the datafile and the expdp dumpfile to the target DB host.
5. Import the tablespace metadata using impdp
impdp system/testdb directory=dumpdir dumpfile=tbs.dmp logfile=tbs.log TRANSPORT_DATAFILES=/oracle/oradata/test/dump.dbf
6. After the import tablespace will be in read only mode.
select tablespace_name,status from dba_tablespaces;
DUMPS READ ONLY
7. Make the tablespace read write if needed.
alter tablespace dumps read write;
Instead of convert tablespace which does the conversion at the source DB convert datafile can be used to do the conversion at the target DB.
All the steps are similar to above except for the fact the step 3.3 convert tablespace ... is omitted.
Instead after the export copy the datafiles which are in read only mode to the target DB. Before doing the import use datafile convert
convert datafile '/oracle/oradata/test/dump.dbf' from platform 'Solaris Operating System (AMD64)';
Restrictions on CONVERT TABLESPACE and CONVERT DATAFILE
* Both source and destination databases must be running with the COMPATIBLE initialization parameter set to 10.0 or higher.
* Not all combinations of source and destination platforms are supported. To determine whether your source and destination platforms are supported, query V$TRANSPORTABLE_PLATFORM. If both the source and destination platforms are listed in this view, then CONVERT can be used to prepare datafiles from one platform for use on the other.
* A tablespace must be made read-write at least once in release 10g before it can be transported to another platform using CONVERT. Hence, any read-only tablespaces (or previously transported tablespaces) from a previous release must first be made read-write before they can be transported to another platform.
* RMAN does not process user datatypes that require endian conversions.
* Prior to release 10g, CLOBs were created with a variable-width character set and stored in an endian-dependent format. The CONVERT command does not perform conversions on these CLOBs. Instead, RMAN captures the endian format of each LOB column and propagates it to the target database. Subsequent reads of this data by the SQL layer interpret the data correctly based upon either endian format, and write the data out in an endian-independent format if the tablespace is writeable.
CLOBs created in Oracle Database Release 10g are stored in character set AL16UTF16, which is platform-independent.
