How to transport a tablespace from 10g ASM on Windows to 11g ASM on AIX

 
This example demonstrates how to transport a tablespace from a 10g ASM on Windows environment to 11g ASM on AIX environment using the DBMS_FILE_TRANSFER package.
The source database on Windows is GENUA1 and the target database is a 11g RAC database RACDB1.
Create the tablespace and the test object in the source database

SQL> create tablespace test_asm
  2  datafile size 100m;

Tablespace created.

SQL> select file_name from dba_data_files
  2  where tablespace_name='TEST_ASM';

FILE_NAME
--------------------------------------------------------------------------------

+DATA/genua1/datafile/test_asm.376.696333127

SQL> conn system/xxx
Connected.

SQL> create table myobjects
  2  tablespace test_asm
  3  as select * from dba_objects;

Table created.

SQL> select count(*) from system.myobjects;

  COUNT(*)
----------
     56504

Create a database link from the source database to the target database

SQL> CREATE DATABASE LINK  DBS2 CONNECT TO
  2  system identified by oracle
  3  using 'racdb1';

Database link created.

SQL> select * from dual@dbs2;

D
-
X
Create a directory to hold the converted datafile on the source

SQL> create directory source_dir as '+DATA/genua1/xtransport/';

Directory created.

Create a directory to hold the tablespace meta data dump on the source

SQL> create directory source_dir_2 as '+DATA';

Directory created.

Create an O/S level directory for the data pump export logfile
SQL> create directory test_asm_log as 'd:\oracle\';

Directory created.

Create a directory on the target to hold the transported datafile
SQL> CREATE OR REPLACE DIRECTORY target_dir  AS '+DATA';

Directory created.

Create an O/S level directory for the data pump import logfile
 create directory test_asm_log as '/u01/oracle/';

Directory created.

Make the tablespace which is going to be transported read only
SQL> alter tablespace test_asm read only;

Tablespace altered.

Export the tablespace meta data using Data Pump
C:\Documents and Settings\bwdba_cbduat>expdp directory=source_dir_2 dumpfile=test_asm.dmp transport_tablespaces=TEST_ASM transport_full_check=Ylogfile=test_asm_log:exp.log
Export: Release 10.2.0.4.0 - Production on Thursday, 13 October , 2011 10:41:34

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

Username: system
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Produc
tion
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=sour
ce_dir_2 dumpfile=test_asm.dmp transport_tablespaces=TEST_ASM transport_full_che
ck=Y logfile=test_asm_log:exp.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_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:
  +DATA/test_asm.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:42:16
Use DBMS_FILE_TRANSFER to transfer the export dump file to the target
SQL> begin
  2   dbms_file_transfer.put_file
  3   (source_directory_object => 'source_dir_2',
  4   source_file_name => 'test_asm.dmp',
  5   destination_directory_object => 'target_dir',
  6  destination_file_name => 'test_asm.dmp',
  7   destination_database => 'dbs2');
  8   end;
  9  /

PL/SQL procedure successfully completed.

Use RMAN to convert the tablespace to the target AIX 64 bit format
Note that the converted datafile will reside in the directory we created in an earlier step - +DATA/genua1/xtransport

RMAN> convert tablespace test_asm to
2> platform 'AIX-Based Systems (64-bit)'
3> format '+DATA';

Starting backup at 31/AUG/09
configuration for DISK channel 2 is ignored
configuration for DISK channel 3 is ignored
configuration for DISK channel 4 is ignored
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=470 instance=genua11 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00224 name=+DATA/genua1/datafile/test_asm.376.696333127
converted datafile=+DATA/genua1/xtransport/test_asm.266.696338369
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
Finished backup at 31/AUG/09

Use DBMS_FILE_TRANSFER to copy the converted datafile to the target
SQL> begin
  2    dbms_file_transfer.put_file
  3    (source_directory_object => 'source_dir',
  4     source_file_name => 'test_asm.266.696338369',
  5    destination_directory_object => ' target_dir',
  6    destination_file_name => 'tts1_db1.dbf',
  7     destination_database => 'dbs2');
  8    end;
  9     /

PL/SQL procedure successfully completed.

On the target import the datafile meta data using Data Pump
The imp.par import parameter file has the following contents

directory=target_dir
dumpfile=test_asm.dmp
logfile=test_asm_log:imp.log
TRANSPORT_DATAFILES='+DATA1/tts1_db1.dbf'
keep_master=y

middba1:/u01/oracle> impdp parfile=imp.par

Import: Release 11.1.0.6.0 - 64bit Production on  Thursday, 13 October , 2011  11:39:32

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

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_02" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_02":  system/******** parfile=imp.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_02" successfully completed at 11:39:48

On the target database RACDB1, check that the TEST_ASM tablespace has been plugged in and the MYOBJECTS table is present
middba1:/u01/oracle> sql

SQL*Plus: Release 11.1.0.6.0 - Production on Thu  Oct  13 11:41:29 2009

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters and Real Application Testing options

SQL> select file_name from dba_data_files where tablespace_name='TEST_ASM';

FILE_NAME
--------------------------------------------------------------------------------
+DATA/tts1_db1.dbf

SQL> select count(*) from system.myobjects;

  COUNT(*)
----------
     56504