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