Loading....

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

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
Please follow and like us:

Last Update: August 31, 2009  

August 31, 2009 150 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

, , ,

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

  1. Mike Liu

    Hello Gavin:
    In part “Use RMAN to convert the tablespace to the target AIX 64 bit format ” you mentioned:
    Note that the converted datafile will reside in the directory we created in an earlier step – +DATA/genua1/xtransport
    you do the convertion just under source_dir(+DATA/genua1/xtransport)? or other parameter can control the converted file’s location?
    2nd: you just use format ‘+DATA’ in Rman script ,not format ‘+DATA/genua1/xtransport/test_asm.266.696338369’ how does rman put the converted file into +DATA/genua1/xtransport?

    Regards
    Mike

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?