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.

Tuesday, July 9, 2013

Cross platform transportable tablespaces in oracle 11g

Cross platform transportable tablespace is a term which is used when we have to transport tablespaces between databases having different endian formats between source and destination platforms. Endian Formats of the datafiles must be converted to match the destination platforms.

This conversion can be performed in two ways :-
1. RMAN convert tablespace , converting on source host.
2. RMAN convert datafile , converting on target host.

This feature is compatible from Oracle Database Release 10.1 and above.

Limitations :-

1. Source Database and Target Database must have same Character set and National Character set.
2. Target database must not have tablespace with same name which has to be transported from Source.
3. Target database version must have to be same or higher than source database version. (exporting using parameter VERSION in expdp does not work here)

PFB steps to transport a tablespace  :-

1. Conversion to be done at source DB from AIX 64 bit system to Microsoft Windows IA (32-bit) :-

 a. Operating system detail on source and target system :-

SQL > select  * from v$transportable_platform;

PLATFORM_ID PLATFORM_NAME                                 ENDIAN_FORMAT
----------- --------------------------------------------- ------------------------------------------
          1 Solaris[tm] OE (32-bit)                       Big
          2 Solaris[tm] OE (64-bit)                       Big
          7 Microsoft Windows IA (32-bit)                 Little
         10 Linux IA (32-bit)                             Little
          6 AIX-Based Systems (64-bit)                    Big
          3 HP-UX (64-bit)                                Big
          5 HP Tru64 UNIX                                 Little
          4 HP-UX IA (64-bit)                             Big
         11 Linux IA (64-bit)                             Little
         15 HP Open VMS                                   Little
          8 Microsoft Windows IA (64-bit)                 Little

PLATFORM_ID PLATFORM_NAME                                 ENDIAN_FORMAT
----------- --------------------------------------------- ------------------------------------------
          9 IBM zSeries Based Linux                       Big
         13 Linux x86 64-bit                              Little
         16 Apple Mac OS                                  Big
         12 Microsoft Windows x86 64-bit                  Little
         17 Solaris Operating System (x86)                Little
         18 IBM Power Based Linux                         Big
         19 HP IA Open VMS                                Little
         20 Solaris Operating System (x86-64)             Little
         21 Apple Mac OS (x86-64)                         Little

b. Check that tablespace which has to be transported contains any refrential objects or dependencies.

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('AMIT', TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

c. Make the tablespace read only.

SQL> alter tablespace amit read only;

Tablespace altered.

d. Connect to RMAN at source DB and convert tablespace AMIT

bash-3.2$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jul 3 15:38:44 2013

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

connected to target database: CDR (DBID=903650087)

RMAN> convert tablespace amit to platform 'Microsoft Windows IA (32-bit)' format ='/backup/%U';

Starting conversion at source at 03-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3237 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00033 name=/disk1/oracle/oradata/amit.dbf
converted datafile=/backup/data_D-CDR_I-903650087_TS-AMIT_FNO-33_6aodqt3b
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at source at 03-JUL-13

e. Export Metadata of the tablespace

bash-3.2$ expdp system/system directory=datapump dumpfile=transportable_tablespace.dmp transport_tablespaces = AMIT &
[1] 50855988

Export: Release 11.2.0.1.0 - Production on Wed Jul 3 12:34:57 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
bash-3.2$
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=datapump dumpfile=transportable_tablespace.dmp transport_tablespaces=AMIT
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /disk1/daapump/transportable_tablespace.dmp
******************************************************************************
Datafiles required for transportable tablespace AMIT:
  /disk1/oracle/oradata/amit.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 12:35:35

[1]+  Done                    expdp system/system directory=datapump dumpfile=transportable_tablespace.dmp transport_tablespaces = AMIT
bash-3.2$

f. Move the dumpfile and datafile to the target database where target database has permissions to access that

g. Plug the tablespace into target database by using IMPORT and RMAN

C:\Users\amit.rath\Downloads> rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jul 3 15:17:12 2013

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

connected to target database: ORCL (DBID=1345007053)

RMAN> convert datafile 'C:\Users\amit.rath\Downloads\data_D-CDR_I-903650087_TS-AMIT_FNO-33_6aodqt3b' format 'C:\app\amit.rath\oradata\orcl\amit.dbf' ;

Starting conversion at target at 03-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=6 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=C:\USERS\AMIT.RATH\DOWNLOADS\DATA_D-CDR_I-903650087_TS-AMIT_FNO-33_6AODQT3B
converted datafile=C:\APP\AMIT.RATH\ORADATA\ORCL\AMIT.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished conversion at target at 03-JUL-13

RMAN> exit

C:\Users\amit.rath\Downloads>impdp system/orcl directory=datapump dumpfile=transportable_tablespace.dmp transport_datafiles='c:\app\amit.rath\oradata\orcl\amit.dbf'

Import: Release 11.2.0.1.0 - Production on Wed Jul 3 15:17:36 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** directory=datapump dumpfile=transportable_tablespace.dmp transport_datafiles='c:\app\amit.rath\oradata\orcl\amit.dbf'
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 15:17:38

2. Conversion to be done at Target DB from Microsoft Windows IA (32-bit) to AIX 64 bit system :-

a. Check that tablespace which has to be transported contains any refrential objects or dependencies.

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('AMIT', TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

b. Make the tablespace read only.

SQL> alter tablespace amit read only;

Tablespace altered.

c. Export Metadata of the tablespace in source DB 

expdp system/system directory=datapump dumpfile=EXPDP_TRANS_EEEE.DMP transport_tablespaces = EEEE 

Export: Release 11.2.0.1.0 - Production on Wed Jul 3 12:34:57 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=datapump dumpfile=transportable_tablespace.dmp transport_tablespaces=AMIT
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  C:\app\datapump\EXPDP_TRANS_EEEE.DMP
******************************************************************************
Datafiles required for transportable tablespace AMIT:
  C:\app\EEEE.DBF
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 12:35:35

d. Move the datafile and export dump to the target DB where it has permission to access those.

e. Convert the datafile using RMAN utility

RMAN> convert datafile '/disk2/oracle/EEEE.DBF' DB_FILE_NAME_CONVERT '/disk2/oracle/' , '/disk2/oracle/oradata/pacs' FROM PLATFORM 'Microsoft Windows IA (32-bit)' ;

Starting conversion at target at 05-JUL-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input file name=/disk2/oracle/EEEE.DBF
converted datafile=/disk2/oracle/oradata/pacsEEEE.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 05-JUL-13

f. Use the IMPDP utility to import the metadata of the tablespace

bash-3.2$ impdp system/system directory=datapump dumpfile=EXPDP_TRANS_EEEE.DMP transport_datafiles= '/disk2/oracle/oradata/pacsEEEE.DBF'

Import: Release 11.2.0.1.0 - Production on Fri Jul 5 13:58:22 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** directory=datapump dumpfile=EXPDP_TRANS_EEEE.DMP transport_datafiles=
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 13:58:25

g. check that the tablespace has been successfully added to that target DB 

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EEEEE

6 rows selected.

SQL> select segment_name,segment_type,owner from dba_segments where tablespace_name='EEEEE';

SEGMENT_NAME              SEGMENT_TYPE       OWNER
------------------------- ------------------ ------------------------------
AAAAA                     TABLE              AMIT

NOTE :- If you want to change the owner of the objects associated with the tablespace then while doing import you can use remap_schema parameter to do that.

I hope this article helped you.

Regards,
Amit Rath

1 comment:

  1. Amit,

    It's really good. Can you help me if target is using ASM.

    ReplyDelete