1. preparations:
Query source database platform information
SQL> col platform_name for a40
SQL> SELECT d. PLATFORM_NAME, ENDIAN_FORMAT
2 from v $ TRANSPORTABLE_PLATFORM tp, V $ DATABASE d
3 WHERE tp. PLATFORM_NAME = d. PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
------------------------------------------------------
Solaris [tm] OE (64-bit) Big
Query target database platform information
SQL> col platform_name for a40
SQL> SELECT d. PLATFORM_NAME, ENDIAN_FORMAT
2 from v $ TRANSPORTABLE_PLATFORM tp, V $ DATABASE d
3 WHERE tp. PLATFORM_NAME = d. PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
------------------------------------------------------
Microsoft Windows IA (32-bit) Little
Query Platform conversions supported by Oracle10g
SQL> select * from v $ transportable_platform;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
-----------------------------------------------------------------
1 Solaris [tm] OE (32-bit) Big
2 Solaris [tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
-----------------------------------------------------------------
9 IBM zSeries Based Linux Big
13 Linux 64-bit for AMD Little
16 Apple Mac OS Big
12 Microsoft Windows 64-bit for AMD Little
2. Create an independent self-contained tablespace
Used for testing
$ Sqlplus "/as sysdba"
SQL * Plus: Release 10.1.0.2.0-Production on Tue Apr 27 14:04:08 2004
Copyright (c) 1982,200 4, Oracle .? All rights reserved.
Connected:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select name from v $ datafile;
NAME
--------------------------------------------------------------------------------
/Opt/oracle/oradata/wwl/system01.dbf
/Opt/oracle/oradata/wwl/undotbs01.dbf
/Opt/oracle/oradata/wwl/sysaux01.dbf
/Opt/oracle/oradata/wwl/users01.dbf
/Data1/oradata/systemfile/wwl01.dbf
/Opt/oracle/oradata/wwl/datafile/o1_mf_test_03xv34ny _. dbf
/Opt/oracle/oradata/wwl/datafile/ow.mf_itpub_03xv5g66 _. dbf
7 rows selected.
SQL> create tablespace trans
2? Datafile '/data1/oradata/systemfile/trans01.dbf'
3? Size 10 M;
Tablespace created.
SQL> create user trans identified by trans
2? Default tablespace trans;
User created.
SQL> grant connect, resource to trans;
Grant succeeded.
SQL> connect trans/trans
Connected.
SQL> create table test as select * from user_objects;
Table created.
SQL> select count (*) from test;
COUNT (*)
----------
1
SQL> select * from test;
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME? OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
-------------------------------------------------------------------------
CREATED? LAST_DDL_TIM TIMESTAMP? STATUS? T G S
-----------------------------------------------------
TEST
15604? 15604 TABLE
27-APR-04? 27-APR-04? 2004-04-27: 14: 05: 42 VALID? N
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-64bit Production
With the Partitioning, OLAP and Data
3. Export the tablespace to be transmitted
$ Pwd
/Opt/oracle
$ Cd dpdata
$ Ls
$ Expdp wwl/wwl dumpfile = trans. dmp directory = dpdata transport_tablespace = trans
LRM-00101: unknown parameter name 'Transport _ tablespace'
$ Expdp wwl/wwl dumpfile = trans. dmp directory = dpdata TRANSPORT_TABLESPACES = trans
Export: Release 10.1.0.2.0-64bit Production on Tuesday, 27 then l, 2004
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "wwl". "SYS_EXPORT_TRANSPORTABLE_01": wwl/******** dumpfile = trans. dmp directory = dpdata TRANSPORT_TABLESPACES = trans
The ORA-39123: Data Pump transportable tablespace job aborted
ORA-29335: tablespace 'Trans 'is not read only
Job "wwl". "SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error
Note: The tablespace to be transferred must be read-only.
$ Sqlplus "/as sysdba"
SQL * Plus: Release 10.1.0.2.0-Production on Tue Apr 27 14:08:13 2004
Copyright (c) 1982,200 4, Oracle. All rights reserved.
Connected:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> alter tablespace trans read only;
Tablespace altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-64bit Production
With the Partitioning, OLAP and Data Mining options
$ Expdp wwl/wwl dumpfile = trans. dmp directory = dpdata TRANSPORT_TABLESPACES = trans
Export: Release 10.1.0.2.0-64bit Production on Tuesday, 27 then l, 2004
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "wwl". "SYS_EXPORT_TRANSPORTABLE_01": wwl/******** dumpfile = trans. dmp directory = dpdata TRANSPORT_TABLESPACES = trans
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
Master table "wwl". "SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
**************************************** **************************************
Dump file set for wwl. SYS_EXPORT_TRANSPORTABLE_01 is:
/Opt/oracle/dpdata/trans. dmp
Job "wwl". "SYS_EXPORT_TRANSPORTABLE_01" successfully completed
4. Use rman to convert the file format
$ Rman target/
Recovery Manager: Release 10.1.0.2.0-64bit Production
Copyright (c) 1995,200 4, Oracle. All rights reserved.
Connected to target database: wwl (DBID = 1337390772)
RMAN> convert tablespace trans
2> to platform 'Microsoft Windows IA (32-bit )'
3> Format '/tmp/% U ';
Starting backup at 27-APR-04
Using target database controlfile instead of recovery catalog
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: sid = 148 devtype = DISK
Channel ORA_DISK_1: starting datafile conversion
Input datafile fno = 00008 name =/data1/oradata/systemfile/trans01.dbf
Converted datafile =/tmp/data_D-wwl_I-1337390772_TS-TRANS_FNO-8_01fk92hg
Channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 27-APR-04
RMAN> exit
Recovery Manager complete.
5. confirm that the exported file has been generated.
$ Ls/tmp/data *
// Tmp/data_D-wwl_I-1337390772_TS-TRANS_FNO-8_01fk92hg
$ Ls-l/tmp/data *
-Rw-r ----- 1 oracle dba 10493952 Apr 27/tmp/data_D-wwl_I-1337390772_TS-TRANS_FNO-8_01fk92hg
$ Chmod 777/tmp/data *
$ Chmod 777/opt/oracle/dpdata /*
6. transfer files to the target host through ftp
220 billing-center.hurray.com.cn FTP server (SunOS 5.8) ready.
User (192.168.96.10none): gqgai
331 Password required for gqgai.
Password:
230 User gqgai logged in.
Ftp> bin
200 Type set to I.
Ftp> cd/tmp
250 CWD command successful.
Ftp> mget data *
200 Type set to I.
Mget data_D-wwl_I-1337390772_TS-TRANS_FNO-8_01fk92hg? Y
200 PORT command successful.
150 Binary data connection for data_D-wwl_I-1337390772_TS-TRANS_FNO-8_01fk92hg (192.168.96.5, 2885) (10493952 bytes ).
226 Binary Transfer complete.
Ftp: 10493952 bytes encoded ed in 15.90 Seconds 659.87 Kbytes/sec.
Ftp> cd/opt/oracle/dpdata
250 CWD command successful.
Ftp> ls
200 PORT command successful.
150 ASCII data connection for/bin/ls (192.168.96.5, 2889) (0 bytes ).
Export. log
Trans. dmp
226 ASCII Transfer complete.
Ftp: 23 bytes encoded ed in 0.01 Seconds 2.30 Kbytes/sec.
Ftp> bin
200 Type set to I.
Ftp> mget trans. dmp
200 Type set to I.
Mget trans. dmp? Y
200 PORT command successful.
150 Binary data connection for trans. dmp (192.168.96.5, 2893) (73728 bytes ).
226 Binary Transfer complete.
Ftp: 73728 bytes encoded ed in 0.03 Seconds 2457.60 Kbytes/sec.
Ftp> bye
221 Goodbye.
7. Use rman to convert files in the target database
E: \ Oracle \ oradata \ wwl \ dpdata> rman target/
Recovery MANAGER: Version 10.1.0.2.0-Production
Copyright (c) 1995,200 4, Oracle. All rights reserved.
Connect to the target database: wwl (DBID = 1587222708)
RMAN> convert datafile 'e: \ Oracle \ oradata \ wwl \ dpdata \ data_D-wwl_I-1337390772_TS-TRANS_FNO-8_01fk92hg'
2> DB_FILE_NAME_CONVERT
3> 'e: \ Oracle \ oradata \ wwl \ dpdata \ data_D-wwl_I-1337390772_TS-TRANS_FNO-8_01fk92hg ', 'e: \ Oracle \ oradata \ wwl \ DATAFILE \ trans01.dbf ';
Start backup in September 4-04
Use channel ORA_DISK_1
Channel ORA_DISK_1: starts data file conversion
Output file name = E: \ ORACLE \ ORADATA \ wwl \ DPDATA \ DATA_D-wwl_I-1337390772_TS-TRANS_FNO-8_01FK92HG
Converted data files = E: \ ORACLE \ ORADATA \ wwl \ DATAFILE \ TRANS01.DBF
Channel ORA_DISK_1: the data file has been converted, after 00:00:04
Backup completed in September 4-04
RMAN>
8. Plug-in data files in the target database
Note that the target user in the target database must exist; otherwise, an error is returned.
E: \ Oracle \ oradata \ wwl \ dpdata> impdp wwl/wwl dumpfile = trans. dmp directory = dpdata transport_datafiles = 'e: \ Oracle \ orad
Ata \ wwl \ DATAFILE \ TRANS01.DBF'
Import: Release 10.1.0.2.0-Production on Tuesday, 27 July 22, 2004
Copyright (c) 2003, Oracle. All rights reserved.
Connect to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-Production
With the Partitioning, OLAP and Data Mining options
The master table "wwl". "SYS_IMPORT_TRANSPORTABLE_01" is successfully loaded/uninstalled"
Start "wwl". "SYS_IMPORT_TRANSPORTABLE_01": wwl/******** dumpfile = trans. dmp directory = dpdata transport_datafiles = 'e :\
Oracle \ oradata \ wwl \ DATAFILE \ TRANS01.DBF'
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transfer-able tablespace job abort
ORA-29342: User TRANS does not exist in the Database
Job "wwl". "SYS_IMPORT_TRANSPORTABLE_01" stopped at due to a fatal error
E: \ Oracle \ oradata \ wwl \ dpdata> sqlplus "/as sysdba"
SQL * Plus: Release 10.1.0.2.0-Production on Tuesday April 27 15:03:03 2004
Copyright (c) 1982,200 4, Oracle. All rights reserved.
Connect:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-Production
With the Partitioning, OLAP and Data Mining options
SQL> create user trans identified by trans;
The user has been created.
SQL> grant connect, resource to trans;
Authorization successful.
SQL> exit
From Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-Production
With the Partitioning, OLAP and Data Mining options disconnected
E: \ Oracle \ oradata \ wwl \ dpdata> impdp wwl/wwl dumpfile = trans. dmp directory = dpdata transport_datafiles = 'e: \ Oracle \ orad
Ata \ wwl \ DATAFILE \ TRANS01.DBF'
Import: Release 10.1.0.2.0-Production on Tuesday, 27 July 22, 2004
Copyright (c) 2003, Oracle. All rights reserved.
Connect to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-Production
With the Partitioning, OLAP and Data Mining options
The master table "wwl". "SYS_IMPORT_TRANSPORTABLE_01" is successfully loaded/uninstalled"
Start "wwl". "SYS_IMPORT_TRANSPORTABLE_01": wwl/******** dumpfile = trans. dmp directory = dpdata transport_datafiles = 'e :\
Oracle \ oradata \ wwl \ DATAFILE \ TRANS01.DBF'
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
The job "wwl". "SYS_IMPORT_TRANSPORTABLE_01" was successfully completed.
9. Check Data
E: \ Oracle \ oradata \ wwl \ dpdata> sqlplus trans/trans
SQL * Plus: Release 10.1.0.2.0-Production on Tuesday April 27 15:03:50 2004
Copyright (c) 1982,200 4, Oracle. All rights reserved.
Connect:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-Production
With the Partitioning, OLAP and Data Mining options
SQL> select count (*) from test;
COUNT (*)
----------
1
SQL> exit
From Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-Production
With the Partitioning, OLAP and Data Mining options disconnected
E: \ Oracle \ oradata \ wwl \ dpdata>
You can change the tablespace to read/write.
SQL> alter tablespace trans read write;
The tablespace has been changed.
10. Summary
10 Gb tablespace is migrated across platforms. Compared with 9i, a file format conversion process using Rman is added.
In fact, the format information of the data file header is converted.
-The End-
For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12