Transport tablespace in oracle 11.2.0.1 between windows 32 bit to Linux 64 bit using RMAN

Transport tablespace from Windows 32 bit to linux 64bit database 11g R2 using RMAN.





Reference : 10g : Transportable Tablespaces Across Different Platforms [ID 243304.1]

1. The source and target database must use the same character set and national character set.check as below.

From source

SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET               WE8MSWIN1252
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.1.0

From target

SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET               WE8MSWIN1252
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.1.0



2. You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or the destination tablespace before the transport operation.

3. You cannot transport the SYSTEM tablespace or objects owned by the user SYS.

>> Using RMAN CONVERT

1. Prepare for export of the tablespace.
  
    * Check that the tablespace will be self contained:
 SQL> execute sys.dbms_tts.transport_set_check('USERS', true);

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

no rows selected
  
>>  The tablespaces need to be in READ ONLY mode in order to successfully run a transport tablespace export.

     SQL> alter tablespace users read only;

Tablespace altered.


2. Export the metadata.
    

>> Using the original export utility
   

exp 'sys/database as sysdba' file=D:\transport_tblsp\n1.dmp log=D:\transport_tblsp\tba_exp.log transport_tablespace=y tablespaces=n1
   
>> Using datapump exp
     
  CREATE OR REPLACE DIRECTORY dpump_dir AS '/tmp/subdir' ;

  GRANT READ,WRITE ON DIRECTORY dpump_dir TO system;

  expdp system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir TRANSPORT_TABLESPACES = TBS1

>> If you want to perform a transport tablespace operation with a strict containment check, use the TRANSPORT_FULL_CHECK parameter:
expdp system/password DUMPFILE=expdat.dmp DIRECTORY = dpump_dir TRANSPORT_TABLESPACES= TBS1,TBS2 TRANSPORT_FULL_CHECK=Y

If the tablespace set being transported is not self-contained then the export will fail.
3. Use V$TRANSPORTABLE_PLATFORM to  determine the endianness of each platform.You can execute the following query on each platform instance:   
SELECT tp.platform_id,substr(d.PLATFORM_NAME,1,30), ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

>> then copy the datafiles and dumpfiles with ftp or copy

D:\transport_tblsp>ftp
Connected to 10.417.313.812.
220 (vsFTPd 2.0.5)
User (10.417.313.812:(none)): username
331 Please specify the password.
Password:
230 Login successful.
ftp> cd /u01/app/backup
250 Directory successfully changed.
ftp> pwd
257 "/u01/app/backup"
ftp> put *.dmp
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 4096 bytes sent in 0.00Seconds 4096000.00Kbytes/sec.
ftp>




D:\app\oradata\db11g>ftp 10.417.313.812
Connected to 10.417.313.812.
220 (vsFTPd 2.0.5)
User (10.417.313.812:(none)): oracle
331 Please specify the password.
Password:
230 Login successful.
ftp>
ftp> cd /u01/app/backup
250 Directory successfully changed.
ftp> ls
200 PORT command successful. Consider using PASV.
150 Here comes the directory listing.
n1.dmp
226 Directory send OK.
ftp: 9 bytes received in 0.00Seconds 9000.00Kbytes/sec.
ftp> pwd
257 "/u01/app/backup"
ftp> bin
200 Switching to Binary mode.
ftp> put n1.dbf
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 10493952 bytes sent in 2.36Seconds 4446.59Kbytes/sec.
ftp> ls
200 PORT command successful. Consider using PASV.
150 Here comes the directory listing.
N1.DBF
n1.dmp
226 Directory send OK.
ftp: 18 bytes received in 0.00Seconds 18000.00Kbytes/sec.
ftp> bye
221 Goodbye.

>> Convert using RMAN as below


[oracle@Hostname backup]$ rman target "'sys/sys as sysdba'"

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jun 1 19:34:07 2011

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

connected to target database: ORCL (DBID=1248484971)

RMAN> convert datafile '/u01/app/backup/N1.DBF' to platform="Linux x86 64-bit" db_file_name_convert='/u01/app/backup/N1.DBF','/u01/app/oracle/oradata/orcl/N1.DBF';

Starting conversion at target at 02-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/u01/app/backup/N1.DBF
converted datafile=/u01/app/oracle/oradata/orcl/N1.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:02
Finished conversion at target at 02-JUN-11

Starting Control File and SPFILE Autobackup at 02-JUN-11
piece handle=/u02/RMAN_BACKUP/controlfile_c-1248484971-20110602-02 comment=NONE
Finished Control File and SPFILE Autobackup at 02-JUN-11


4. Import the transportable tablespace
    
>> Using the original import utility

[oracle@HOSTNAME backup]$ imp  "'sys/******* as sysdba'" file=/u01/app/backup/n1.dmp log=/u01/app/backup/tba_imp.log transport_tablespace=y datafiles='datafiles='/u01/app/oracle/oradata/orcl/N1.DBF''

Import: Release 11.2.0.1.0 - Production on Wed Jun 1 17:31:14 2011

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

Export file created by EXPORT:V11.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
export client uses WE8MSWIN1252 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing NAV's objects into NAV
. . importing table                         "TAP1"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
[oracle@kor300949lx1 backup]$

    
>> Using datapump

  CREATE OR REPLACE DIRECTORY dpump_dir AS '/tmp/subdir';

  GRANT READ,WRITE ON DIRECTORY dpump_dir TO system;
     
  impdp system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir
  TRANSPORT_DATAFILES='/tmp/....'REMAP_SCHEMA=(source:target)
  REMAP_SCHEMA=(source_sch2:target_schema_sch2)

You can use REMAP_SCHEMA if you want to change the ownership of the transported database objects.
5. Put the tablespaces in read/write mode:

  SQL> alter tablespace n1 read write;

Tablespace altered.

Regards,

Navaneeth