The source database on Windows is PROD1 and the target database is a 11g RAC database RACDB1.
1) Create the tablespace and the test object in the source database.
SQL> create tablespace test_asm datafile size 100m; Tablespace created.
SQL> select file_name from dba_data_files where tablespace_name='TEST_ASM';
FILE_NAME ------------------------------------------------------------------------------ +DATA/PROD1/datafile/test_asm.376.696333127
SQL> conn system/xxx Connected.
SQL> create table myobjects tablespace test_asm as select * from dba_objects; Table created.
SQL> select count(*) from system.myobjects;
COUNT(*) ---------- 56504
2) Create a database link from the source database to the target database.
SQL> CREATE DATABASE LINK DBS2 CONNECT TO system identified by oracle using 'racdb1'; Database link created.
SQL> select * from dual@dbs2;
D - X
3) Create a directory to hold the converted datafile on the source.
SQL> create directory source_dir as '+DATA/PROD1/xtransport/';
Directory created.
4) Create a directory to hold the tablespace meta data dump on the source.
SQL> create directory source_dir_2 as ‘+DATA’; Directory created.
5) Create an O/S level directory for the data pump export logfile.
SQL> create directory test_asm_log as 'd:\oracle\';
Directory created.
6) Create a directory on the target to hold the transported datafile.
SQL> CREATE OR REPLACE DIRECTORY target_dir AS '+DATA';
Directory created.
7) Create an O/S level directory for the data pump import logfile.
SQL> create directory test_asm_log as ‘/u01/oracle/’; Directory created.
8) Make the tablespace which is going to be transported read only.
SQL> alter tablespace test_asm read only;
Tablespace altered.
9) Export the tablespace meta data using Data Pump.
C:\Documents and Settings>expdp directory=source_dir_2 dumpfile=test_asm.dmp transport_tablespaces=TEST_ASM transport_full_check=Y logfile=test_asm_log:exp.log
Export: Release 10.2.0.4.0 - Production on Monday, 31 August, 2009 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 - Production 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
10) Use DBMS_FILE_TRANSFER to transfer the export dump file to the target.
SQL> begin dbms_file_transfer.put_file (source_directory_object => 'source_dir_2', source_file_name => 'test_asm.dmp', destination_directory_object => 'target_dir', destination_file_name => 'test_asm.dmp', destination_database => 'dbs2'); end; / PL/SQL procedure successfully completed.
11) Use RMAN to convert the tablespace to the target AIX 64 bit format.
Note: The converted datafile will reside in the directory we created in an earlier step – +DATA/PROD1/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=PROD11 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00224 name=+DATA/PROD1/datafile/test_asm.376.696333127
converted datafile=+DATA/PROD1/xtransport/test_asm.266.696338369
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
Finished backup at 31/AUG/09
12) Use DBMS_FILE_TRANSFER to copy the converted datafile to the target.
SQL> begin dbms_file_transfer.put_file (source_directory_object => 'source_dir', source_file_name => 'test_asm.266.696338369', destination_directory_object => ' target_dir', destination_file_name => 'tts1_db1.dbf', destination_database => 'dbs2'); end; /
PL/SQL procedure successfully completed.
13) 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 Monday, 31 August, 2009 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
14) 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 Mon Aug 31 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
Comments