News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Cross Platform Transportable Tablespace using ASM with Oracle XML DB

  • Posted by Gavin Soorma
  • On October 14, 2009
  • 0 Comments

In one of my earlier posts How To Transport a Tablespace from 10g ASM to 11g ASM on AIX we discussed how to transport a tablespace stored on ASM storage from Windows to AIX using the DBMS_FILE_TRANSFER package.

We will now look at how the same thing can be done using FTP with Oracle XML DB Repository and its virtual folders.

All we need to do is to run the following script and provide the port numbers which will be used for the FTP and HTTP protocols for accessing the repository.

@?/rdbms/admin/catxdbdbca 8080 7787

In this case we have assigned the port 8080 for the FTP protocol and the port 7787 is for HTTP access.

The ASM related directories on the Windows Oracle database are accessed via the sys/asm virtual folder and then we use FTP to copy the ASM files over the network and then use RMAN to convert it to AIX 64 bit format

The example below shows us how we are connecting to the Windows server via port 8080 using the FTP protocol. Note we are then connecting with a database user – SYSTEM in this case.

testdb:/u01/oracle > ftp cbdorca261 8080
Connected to cbdorca261.bankwest.com.
220- cbdorca261
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 cbdorca261 FTP Server (Oracle XML DB/Oracle Database) ready.
Name (cbdorca261:oracle): system
331 pass required for SYSTEM
Password:
230 SYSTEM logged in
ftp> i
200  Type set to I.
ftp> cd sys/asm
250 CWD Command successful
ftp> cd DATA/genua1/datafile
250 CWD Command successful
ftp> get test_tts.376.699110247
200 PORT Command successful
150 BIN Data Connection
226 BIN Transfer Complete
104865792 bytes received in 2.25 seconds (4.551e+04 Kbytes/s)
local: test_tts.376.699110247 remote: test_tts.376.699110247
ftp> quit
221 QUIT Goodbye.

 testdb:/u01/oracle > rman target /

Recovery Manager: Release 11.1.0.7.0 - Production on Thu Oct 1 13:50:02 2009

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

connected to target database: TESTDB (DBID=2482257163)

RMAN>  convert datafile2>
 '/u01/oracle/test_tts.376.699110247'
3> FORMAT '+DATA'
4> from platform 'Microsoft Windows IA (32-bit)';

Starting conversion at target at 01-OCT-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=140 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/u01/oracle/test_tts.376.699110247
converted datafile=+DATA/testdb/datafile/test_tts.267.699112231
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished conversion at target at 01-OCT-09


imp file=tts.dmp transport_tablespace=y tablespaces=TEST_TTS datafiles=+DATA/testdb/datafile/test_tts.267.699112231

Import: Release 11.1.0.7.0 - Production on Thu Oct 1 13:57:33 2009

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

Username: sys as sysdba
Password:

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

Export file created by EXPORT:V10.02.01 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 SYSTEM's objects into SYSTEM
. . importing table                    "MYOBJECTS"
. importing SYS's objects into SYS
Import terminated successfully without warnings.

SQL> conn system/oracle
Connected.

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

FILE_NAME
--------------------------------------------------------------------------------
+DATA/testdb/datafile/test_tts.267.699112231


SQL> select tablespace_name from user_tables
  2  where table_name='MYOBJECTS';

TABLESPACE_NAME
------------------------------
TEST_TTS

SQL> select count (*) from myobjects;

  COUNT(*)
----------
     56454
 

0 Comments

Leave Reply

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