Cross Platform Transportable Tablespace using RMAN in Oracle Database

Cross Platform Transportable Tablespace with Different Endianness

Reference data for this Post:-
1. Tablespace to be moved : TEST
2. Source Operating system :- Solaris[tm] OE (64-bit) 
3. Target Operating system :- Linux x86 64-bit
4. Datafiles residing in :- ASM
5. Source Database version :- 11.2.0.2
6. Target Database Version :- 11.2.0.4

Steps to perform Cross platform Transportable Tablespace on Source Database:-

1. Pre Check the data for the tablespace to be moved(optional)

SQL> select table_name from user_tables;

TABLE_NAME         OWNER
--------------------        ----------------
AMIT_1 AMIT
AMIT_2 AMIT
AMIT_3 AMIT

SQL> select count(1) from AMIT_2;

  COUNT(1)
----------
  11496960
  
SQL> select distinct tablespace_name from dba_segments where owner='AMIT';

TABLESPACE_NAME
------------------------------
TEST

2. Check whether both Platform have different Endianness 

Check the target operating system detail from below query:-

Source :-

select d.name,d.PLATFORM_NAME,tp.ENDIAN_FORMAT  FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d  WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

NAME      PLATFORM_NAME                                                                                         ENDIAN_FORMAT
--------- ----------------------------------------------------------------------------------------------------- --------------
AMIT     Solaris[tm] OE (64-bit)                                                                                      Big

Target :-

select d.name,d.PLATFORM_NAME,tp.ENDIAN_FORMAT  FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d  WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

NAME      PLATFORM_NAME                                                                                         ENDIAN_FORMAT
--------- ----------------------------------------------------------------------------------------------------- --------------
TEST     Linux x86 64-bit                                                                                           Little

If yes , then we have to convert the endianness of datafile as per target database.

3. Change the Tablespace to Read Only mode 

SQL> alter tablespace test read only;

Tablespace altered.

4. Check whether tablespace can be transported without any errors

SQL> EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TEST', incl_constraints => TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM transport_set_violations;

no rows selected

5. Restrictions for Cross platform transportable tablespace

a. Both Source and target should use the same Characterset and National Character set

b. Tablespace has to be Self Contained means Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects are in the tablespace set

c. Compatibility must be greater than 10.0 for both

6. Export the metadata of tablespace using datapump

$ expdp directory=DP_DUMP dumpfile=expdp_transport1.dmp logfile=expdp.log transport_tablespaces=test exclude=xmlschema

Export: Release 11.2.0.2.0 - Production on Sat Jul 11 12:06:42 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: /as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  /******** AS SYSDBA directory=DP_DUMP dumpfile=expdp_transport1.dmp logfile=expdp.log transport_tablespaces=test exclude=xmlschema

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:

  /u00/oracle/admin/AMIT/dpdump/expdp_transport1.dmp

******************************************************************************

Datafiles required for transportable tablespace TEST:

  +DATADG/AMIT/datafile/test.349.884746017

Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 12:06:55

7. Convert the datafile endianness as per the target operating system .

As we see in Step 2 that both Source and target have different endianness, we have convert the endianness of datafile as per target

Start the conversion :-

$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sat Jul 11 12:09:47 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: AMIT (DBID=387143729)

RMAN> convert tablespace "TEST" to platform 'Linux x86 64-bit' FORMAT='/ora_backup/dpdump/AMIT/%U';

Starting conversion at source at 2015-07-11 12:10:08

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=112 device type=DISK

channel ORA_DISK_1: starting datafile conversion

input datafile file number=00045 name=+DATADG/AMIT/datafile/test.349.884746017

converted datafile=/ora_backup/dpdump/AMIT/data_D-AMIT_I-387143729_TS-TEST_FNO-45_02qbpap1

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55

Finished conversion at source at 2015-07-11 12:11:04

8. Once conversion completed, SCP the files to target platform( Converted datafile and Dumpfile for metadata of tablespace)

$ scp data_D-AMIT_I-387143729_TS-TEST_FNO-45_02qbpap1 server1:/ora_backup/test/restore_test/amit/backup

$ scp expdp_transport1.dmp server1:/ora_backup/test/restore_test/amit/backup

9. Change the tablespace to Read Write mode

SQL> alter tablespace test read write;

Tablespace altered.

Steps to perform Cross platform Transportable Tablespace on Target Database:-

1. Check the Version of target database(optional)

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

2. Convert the datafile as per the target Database file details 

$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jul 11 14:20:12 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST1 (DBID=1261817935)

RMAN> convert datafile '/ora_backup/test/restore_test/amit/backup/data_D-AMIT_I-387143729_TS-TEST_FNO-45_02qbpap1' db_file_name_convert='/ora_backup/test/restore_test/amit/backup','+DATA1_test/test1/datafile/';

Starting conversion at target at 2015-07-11 14:20:55

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=235 device type=DISK

channel ORA_DISK_1: starting datafile conversion

input file name=/ora_backup/test/restore_test/amit/backup/data_D-AMIT_I-387143729_TS-TEST_FNO-45_02qbpap1

converted datafile=+DATA1_test/test1/datafile/data_d-AMIT_i-387143729_ts-test_fno-45_02qbpap1

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15

Finished conversion at target at 2015-07-11 14:21:11

3. Create the appropriate Users and roles as they were present in Source database

Create user Amit identified by *****;

Grant login, schema to amit;

If this tablespace is to be associated to different user then remap_schema has to be used while importing the metadata.

4. Import the metadata of the tablespace in target Database 

$ impdp directory=datapump dumpfile=expdp_transport1.dmp logfile=impdp1.log transport_datafiles=+DATA1_test/test1/datafile/TEST.348.884787657

Import: Release 11.2.0.4.0 - Production on Sat Jul 11 14:23:53 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: /as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Source time zone is -05:00 and target time zone is +00:00.

Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  /******** AS SYSDBA directory=datapump dumpfile=expdp_transport1.dmp logfile=impdp1.log transport_datafiles=+DATA1_test/test1/datafile/TEST.348.884787657

Processing object type TRANSPE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORORTABLT/TABLE

Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Sat Jul 11 14:23:59 2015 elapsed 0 00:00:03

5. Check the new datafile details in target database 

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

+DATA1_test/test1/datafile/system.365.884775499

+DATA1_test/test1/datafile/sysaux.326.884775501

+DATA1_test/test1/datafile/undotbs1.380.884775501

+DATA1_test/test1/datafile/users.381.884775501

+DATA1_test/test1/datafile/test.348.884787657

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

------------------------------

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

TEST

6 rows selected.

6. Make the tablespace read write in Target database 

SQL> alter tablespace test read write;

Tablespace altered.

7. Check the data associated with tablespace in target and compare it with source for verification(Optional)

SQL> conn amit/Passw0rd

Connected.

SQL> select table_name from user_tables;

TABLE_NAME

------------------------------

AMIT_2

AMIT_3

AMIT_1

SQL> select count(1) from AMIT_2;

  COUNT(1)

----------

  11496960

Cross Platform Transportable Tablespace with different Endianness completed.

I hope this article helped you.

Thanks

Amit Rath