Cross Platform Transportable Tablespace using RMAN

When transporting tablespaces between databases where the endian format is different between source and destination platforms, the endian format of the datafiles must be converted to match the destination platform.

This conversion can be performed in two ways

1. Using RMAN CONVERT TABLESPACE command (converting on the source host)
2. Using RMAN CONVERT DATAFILE command(converting on the destination host)

Transportable tablespaces export is manageable across platforms and only meta data will be exported. In Cross platform transportable tablespace the data movement is simpler and faster.

This mode requires that you have the EXP_FULL_DATABASE role.

Please note that the
1. source and target database must use the same character/national character set.
2. You cannot transport a tablespace to a target database which already exists.
3. Transportable tablespace exports cannot be restarted once stopped
4. Target database must at same or higher release level as the source database.

For example, if you want to transport a tablespace “test_user_tbs” from a Linux 64 bit (Little Endian) machine TESTLINUX to Solaris 64 bit (Big Endian) machine TESTSOLARIS. Both the source and target platforms are of different endian type. The data file for the tablespace “test_user_tbs” is “test_user_tbs01.dbf.

Follow the below steps

Step 1: Find the Operating system byte order on Source and Target Database
SQL > select * from v$transportable_platform order by platform_id;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ----------------------------------- ----------------------
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 IA (32-bit) 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

Step 2: Make the tablespace “READ ONLY”
SQL> alter tablespace test_user_tbs read only;

Step 3: Export metadata
(i) Using export utility
$ exp testuser/test123 tablespaces=test_user_tbs transport_tablespace=y file=exp_test_user_tbs.dmp log=exp_test_user_tbs.log

(ii) Using Export data pump utility
$ expdp system/password TRANSPORT_TABLESPACES=test_user_tbs TRANSPORT_FULL_CHECK=Y DIRECTORY=export_dir DUMPFILE=expdp_test_user_tbs.dmp logfile= expdp_test_user_tbs.log

TRANSPORT_FULL_CHECK= Y Specifies that check for dependencies between those objects inside the transportable Tablespace and those outside the transportable Tablespace

The file “exp_test_user_tbs.dmp” or ““expdp_test_user_tbs.dmp” contains only metadata.

Step 4: Different endian formats and convert using RMAN
The endian formats are different and then a conversion is necessary for transporting the tablespace.

RMAN Cross platform transportable tablespace.

Run the below command to covert the tablespace to Source (Linux 64 bit) to Target (Solaris 64 bit) platform.

Method 1: Using CONVERT Tablespace... FROM PLATFORM on Source host
RMAN> convert tablespace test_user_tbs
2> to platform ‘Solaris[tm] OE (64-bit)'
3> format='/oradata/rman_backups/%N_%f';

The data file “test_user_tbs01.dbf” is not touched and a new file will be created for Solaris platform under “/oradata/rman_backups“and copy the file to Target platform.

Method 2: Using CONVERT DATAFILE... FROM PLATFORM on Destination host
RMAN> convert datafile test_user_tbs01.dbf
2> from platform ‘Linux IA (64-bit)'
3> db_file_name_convert ‘/linux/oradata/’ ‘/solaris/oradata’

Use RMAN's CONVERT command to convert the datafiles to be transported to the destination host's format. The converted datafiles are stored in “/solaris/oradata”.

Step 5: Initiate import command to plug the tablespace into the database.
(i) Using import utility
$ imp test_user/test123 tablespaces=test_user_tbs transport_tablespace=y file=exp_test_user_tbs.dmp datafiles='test_user_tbs01.dbf' log=imp_test_user_tbs.log

(ii) Using impdp utility
$ impdp test_user/test123 TRANSPORT_DATAFILES='test_user_tbs01.dbf' DIRECTORY=import_dir DUMPFILE=expdp_test_user_tbs.dmp log= impdp_test_user_tbs.log

You can use REMAP_SCHEMA=(source:target), if you want to import into another schema.

Step 6: Put the tablespace in read/write mode.
SQL> ALTER TABLESPACE TEST_USER_TBS READ WRITE;

Now the database has a tablespace named “test_user_tbs” and the objects of the tablespace will be available.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/