About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Saturday, August 15, 2015

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