Transport tablespace between platforms with same endian format using EXP/IMP.

Transportable Tablespaces  This can be used by only export/import.

Please try to check the endian format before doing this TTablespace using exp/imp. The endian format should be same between the platforms , otherwise use RMAN convert as shown in the other post.  

RESTRICTIONS
----------------

1. Character set as well as national character set of the source and the target must be the same.
2. Users whose default tablespace is getting exported must exist in the target database before importing.
3. Target database must not have tablespace of the same name.
4. The tablespace must be self contained to ensure that the table is complete in case of partitioning.

We will consider a tablespace by name "N2" for the example.

EXPORT THE TABLESPACE
------------------------
1. Make the tablespace readonly.
sqlplus> connect sys/*******
sqlplus> alter tablespace N2 read only;

2. Export the tablespace specifying the TABLESPACES and TRANSPORT_TABLESPACE clause
D:\app\product\11.2.0\dbhome_1\BIN>exp "'sys/database as sysdba'" file=d:\transport_tblsp\n2.dmp tablespaces=n2 transport_tablespace=y

Export: Release 11.2.0.1.0 - Production on Thu Jun 2 16:49:12 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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace N2 ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                            N21
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.


3. Transfer the meta data export dump file and the copy of the datafile(s) to the target  database. (Could be ftp or copy) as below

D:\transport_tblsp>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> cd /u01/app/backup
250 Directory successfully changed.
ftp> put n2.dbf
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 10493952 bytes sent in 5.59Seconds 1875.93Kbytes/sec.
ftp> ls
200 PORT command successful. Consider using PASV.
150 Here comes the directory listing.
N2.DBF
226 Directory send OK.
ftp: 8 bytes received in 0.00Seconds 8000.00Kbytes/sec.
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> ls
200 PORT command successful. Consider using PASV.
150 Here comes the directory listing.
N2.DBF
n2.dmp
226 Directory send OK.
ftp: 16 bytes received in 0.00Seconds 16000.00Kbytes/sec.
ftp>


4. Import the metadata using TABLESPACES, TRANSPORT_TABLESPACE, DATAFILES clause, If more than one datafiles are available then specify all of them comma seperated.
[oracle@hostname backup]$ imp '"sys/******* as  sysdba"' file=n2.dmp TABLESPACES=N2 TRANSPORT_TABLESPACE=y datafiles=/u01/app/oracle/oradata/orcl/N2.DBF

Import: Release 11.2.0.1.0 - Production on Thu Jun 2 17:33:11 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 N2's objects into N2
. . importing table                          "N21"
. importing SYS's objects into SYS
Import terminated successfully without warnings.


5. Make the tablespace read write, if required, at the target database
SQL> alter tablespace n2 read write;

Tablespace altered.


These below tables contains a new field PLUGGED_IN which is helpful in identifying the plugged in tabelspaces.
The value of PLUGGED_IN would be YES in dba_tablespaces and 1 in v$datafile if the tablespace/datafile was plugged in.
SQL> select tablespace_name, status, plugged_in from dba_tablespaces;

TABLESPACE_NAME                STATUS    PLU
------------------------------ --------- ---
SYSTEM                         ONLINE    NO
SYSAUX                         ONLINE    NO
UNDOTBS1                       ONLINE    NO
TEMP                           ONLINE    NO
USERS                          ONLINE    NO
EXAMPLE                        ONLINE    YES
APEX                           ONLINE    NO
TEST11                         ONLINE    NO
N2                             ONLINE    YES

SQL> select name, status, plugged_in from v$datafile;

NAME                                               STATUS  PLUGGED_IN
-------------------------------------------------- ------- ----------
/u01/app/oracle/oradata/orcl/system01.dbf          SYSTEM           0
/u01/app/oracle/oradata/orcl/sysaux01.dbf          ONLINE           0
/u01/app/oracle/oradata/orcl/undotbs01.dbf         ONLINE           0
/u01/app/oracle/oradata/orcl/users01.dbf           ONLINE           0
/u01/app/oracle/oradata/orcl/example01.dbf         ONLINE           0
/u01/app/oracle/oradata/orcl/apex01.dbf            ONLINE           0
/u02/SCRIPTS/test11_data_01.dbf                    ONLINE           0
/u01/app/oracle/oradata/orcl/N2.DBF                ONLINE           1

8 rows selected.

Regards,

Navaneeth

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