Cross-Platform Transportable Tablespaces

A transportable tablespace allows you to  move a tablespace data of an Oracle database from one Oracle database to another. In Oracle 10g allow you to move tablespace across different platforms.

Steps to move data accross Cross-Platform

1 ) Check Platform Support and File Conversion Requirement

The pattern for byte ordering in native types is called endianness. There are only two main patterns, big endian and little endian. Big endian means the most significant byte comes first, and little endian means the least significant byte comes first. If the source platform and the target platform are of different endianness, then an additional step must be taken on either the source or target platform to convert the tablespace being transported to the target format. If they are of the same endianness, then no conversion is necessary and tablespaces can be transported as if they were on the same platform

SQL> select platform_name , endian_format from v$transportable_platform

SQL> /

PLATFORM_NAME                  ENDIAN_FORMAT

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

Solaris[tm] OE (32-bit)        Big

Solaris[tm] OE (64-bit)        Big

Microsoft Windows IA (32-bit)  Little

Linux IA (32-bit)              Little

AIX-Based Systems (64-bit)     Big

HP-UX (64-bit)                 Big

HP Tru64 UNIX                  Little

HP-UX IA (64-bit)              Big

Linux IA (64-bit)              Little

HP Open VMS                    Little

Microsoft Windows IA (64-bit)  Little

IBM zSeries Based Linux        Big

Linux x86 64-bit               Little

Apple Mac OS                   Big

Microsoft Windows x86 64-bit   Little

Solaris Operating System (x86) Little

IBM Power Based Linux          Big

Solaris Operating System (x86- Little

64)

HP IA Open VMS                 Little

19 rows selected.

2)Identify Tablespaces to be Transported and Verify Self-containment

SQL> execute dbms_tts.transport_set_check('NEW',true);

PL/SQL procedure successfully completed.

OR

SQL> BEGIN

  2    SYS.dbms_tts.transport_set_check

  3     ('NEw', incl_constraints=>TRUE, full_check=>FALSE);

  4  END;

  5  /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM SYS.transport_set_violations;

no rows selected

SQL>

3)Make Tablespace read only

sql> alter tablespace NEw read only;

Tablespace altered.

4)Create Directory for EXPDUMP

SQL> create  directory dumdir  as '/home/oracle/dumpdir';

Directory created.

SQL> grant read on directory dumdir to system;

Grant succeeded.

SQL> grant write on directory dumdir to system;

Grant succeeded.

4)Take BACKUP OF TABLESPACE

[oracle@dbdatah-server ~]$ expdp dumpfile=new.dmp logfile=new.log directory=dumdir transport_tablespace=new

LRM-00101: unknown parameter name 'transport_tablespace'

[oracle@dbdatah-server ~]$ expdp dumpfile=new.dmp logfile=new.log directory=dumdir transport_tablespaces=new transport_full_check=y

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 09 August, 2011 13:30:15

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Username: system

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** dumpfile=new.dmp logfile=new.log directory=dumdir transport_tablespaces=new transport_full_check=y

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 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded

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

Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:

  /home/oracle/dumpdir/new.dmp

Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 13:31:37

5)Copy the datafiles on Destination Platform

6)convert name of the datafiles belongs to Transportable  tablespace

C:\oracle\product\10.2.0\db_1>rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Aug 9 16:04:08 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: GEN (DBID=1378351019)

RMAN> convert datafile "c:\dumpdir\new2.dbf" to platform="Microsoft Windows IA (32-bit)" db_file_nam

e_convert=('/home/oracle/oradata/dbdata/','c:\dumpdir');

Starting backup at 09-AUG-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=145 devtype=DISK

channel ORA_DISK_1: starting datafile conversion

input filename=C:\DUMPDIR\NEW2.DBF

converted datafile=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\DATA_D-DBDATA_I-504259222_TS-NEW_FNO-7_0

2MJIPC6

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

Finished backup at 09-AUG-11

RMAN> exit;

Recovery Manager complete.

7) Import tablespace

C:\oracle\product\10.2.0\db_1>impdp dumpfile=new.dmp logfile=new.log  directory=dumpdir transport_da

tafiles=c:\dumpdir\new1.dbf,c:\dumpdir\new2.dbf

Import: Release 10.2.0.4.0 - Production on Tuesday, 09 August, 2011 16:06:44

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Username: system

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP and Data Mining options

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

Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** dumpfile=new.dmp logfile=new.log d

irectory=dumpdir transport_datafiles=c:\dumpdir\new1.dbf,c:\dumpdir\new2.dbf

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

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 16:06:55

C:\oracle\product\10.2.0\db_1>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Aug 9 16:07:21 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME

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

FILE_NAME

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

USERS

C:\ORACLE\PRODUCT\10.2.0\ORADATA\GEN\USERS01.DBF

SYSAUX

C:\ORACLE\PRODUCT\10.2.0\ORADATA\GEN\SYSAUX01.DBF

UNDOTBS1

C:\ORACLE\PRODUCT\10.2.0\ORADATA\GEN\UNDOTBS01.DBF

TABLESPACE_NAME

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

FILE_NAME

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

SYSTEM

C:\ORACLE\PRODUCT\10.2.0\ORADATA\GEN\SYSTEM01.DBF

NEW

C:\DUMPDIR\NEW2.DBF

NEW

C:\DUMPDIR\NEW1.DBF

6 rows selected.

SQL> select tablespace_name ,status from dba_tablespaces where tablespace_name='NEW';

TABLESPACE_NAME                STATUS

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

NEW                            READ ONLY

8)Change tablespace in Read Write Mode

SQL> alter tablespace new  read write;

Tablespace altered.

SQL> select tablespace_name ,status from dba_tablespaces where tablespace_name='NEW';

TABLESPACE_NAME                STATUS

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

NEW                            ONLINE

SQL>