Transport Tablespace from Linux to Windows in ORACLE 11g & 10g

Transport tablespace from Linux to windows platform step by step

In this tutorial, I’m going to show you how Transport tablespace from Linux to windows platform, in oracle Database we can transport tablespace and Database from one platform to another platform. In this topic, I’m using Redhat Linux 6 (64 bit) and Microsoft Windows 7 (64 bit) with ORACLE 11G (64 bit), following commands.

Create a new tablespace then create some tables which is store in this tablespace see commands.

Now start Transport tablesapce from Linux to windows

NOTE:- SYS user contents we can not move through this tablespace.

SQL> create tablespace komal
          datafile '/u01/app/oracle/oradata/db11g/komal01.dbf'
          size 10m;

 Tablespace created.

Now Create a sample table for example.

SQL> create table scott.komal tablespace komal
          as select * from scott.emp;

 Table created.

Make your tablespace read only because read write tablespace we cannot move.

SQL> alter tablespace komal read only;

 Tablespace altered.

transport_set_check parameter value must make TRUE.

SQL> exec dbms_tts.transport_set_check('komal',true);

 PL/SQL procedure successfully completed.

Now check violations to your tablespace if it’s returned no rows selected it means everything is ok and you can move this tablespace successfully if it’ll be returning some rows then check you done all steps or not.

SQL> select * from transport_set_violations;
 
no rows selected

How to check ENDIAN FORMAT

These all are the transportable platforms where you can transport your tablespace, keep in mind ENDIAN FORMAT must be the same.

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

21 rows selected.

Now going on HOST using ‘host’ command.

SQL> host

Then going on RMAN prompt using below command, then convert datafile by using RMAN.

$ rman target/

RMAN> convert tablespace komal
              to platform='Microsoft Windows x86 64-bit' db_file_name_convert='/u01/app/oracle/oradata/db11g/komal01.dbf','/u01/komal01_convert.dbf';

RMAN> exit

Export tablespace by using EXPDP command, it’ll be asking username and password enter username system and system user password.

$ export DATA_PUMP_DIR=DATA_PUMP_DIR

$ expdp transport_tablespaces=komal dumpfile=komal.dmp

 Export: Release 11.2.0.3.0 - Production on Sat Aug 1 11:16:33 2015
 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
 Username: system
 Password:********

Now copy both files (RMAN converted file and expdp .dump file) into your pen drive then going on your WINDOWS system and past both files to the appropriate location.

Login in Database and check datafile location and dump file location using following commands.

Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production

SQL> select name from v$datafile;

 NAME
 -----------------------------------------------------
 D:\APP\KIRANSRAN\ORADATA\DB11G\SYSTEM01.DBF
 D:\APP\KIRANSRAN\ORADATA\DB11G\SYSAUX01.DBF
 D:\APP\KIRANSRAN\ORADATA\DB11G\UNDOTBS01.DBF
 D:\APP\KIRANSRAN\ORADATA\DB11G\USERS01.DBF
 D:\APP\KIRANSRAN\ORADATA\DB11G\EXAMPLE01.DBF

Check Directory path.

SQL> select * from dba_directories

Going on host using host command and follow steps.

SQL>host
D:\>set DATA_PUMP_DIR=DATA_PUMP_DIR
D:\>impdp transport_datafiles='D:\app\kiransran\oradata\db11g\komal01.dbf'  dumpfile=komal.dmp

 Import: Release 11.2.0.1.0 - Production on Sat Aug 1 11:28:42 2015
 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
 Username: system
 Password:

Now login into your Database and check tablespace and table are exist or not.

Read - How to Transport Database from Linux to windows?

Connect with me on:

Instagram: https://www.instagram.com/shripaldba
Linkedin: https://www.linkedin.com/in/shripal-singh
Twitter: https://twitter.com/ocptechnology
Facebook: https://www.facebook.com/ocptechnology
YouTube:https://www.youtube.com/ocptechnology