1.準備工作:
查詢源資料庫平臺資訊

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

查詢目標資料庫平臺資訊

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

查詢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.建立一個獨立的自包含表空間

用於測試

$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.1.0.2.0 - Production on Tue Apr 27 14:04:08 2004

Connected to:
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/wwl/datafile/o1_mf_test_03xv34ny_.dbf
/opt/oracle/oradata/wwl/wwl/datafile/o1_mf_itpub_03xv5g66_.dbf

7 rows selected.

SQL> create tablespace trans
2?datafile '/data1/oradata/systemfile/trans01.dbf'
3?size 10M;

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 N 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.匯出要傳輸的表空間

$ 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 April, 2004 14:07

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
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 at 14:08

注意:傳輸表空間必須置為只讀狀態

$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.1.0.2.0 - Production on Tue Apr 27 14:08:13 2004

Connected to:
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 April, 2004 14:08

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 at 14:09

4.使用rman轉換檔案格式

$ rman target /

Recovery Manager: Release 10.1.0.2.0 - 64bit Production

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.確認匯出檔案已生成

$ 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 14:12 /tmp/data_D-wwl_I-1337390772_TS-TRANS_FNO-8_01fk92hg
$ chmod 777 /tmp/data*
$ chmod 777 /opt/oracle/dpdata/*

6.透過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 received in 15.90Seconds 659.87Kbytes/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 received in 0.01Seconds 2.30Kbytes/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 received in 0.03Seconds 2457.60Kbytes/sec.
ftp> bye
221 Goodbye.

7.使用rman在目標資料庫轉換檔案

E:\Oracle\oradata\wwl\dpdata>rman target /

恢復管理器: 版本10.1.0.2.0 - Production

連線到目標資料庫: 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\wwl\DATAFILE\trans01.dbf';

啟動 backup 於 27-4月 -04
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 啟動資料檔案轉換
輸出檔名=E:\ORACLE\ORADATA\wwl\DPDATA\DATA_D-wwl_I-1337390772_TS-TRANS_FNO-8_01FK92HG
已轉換的資料檔案 = E:\ORACLE\ORADATA\wwl\wwl\DATAFILE\TRANS01.DBF
通道 ORA_DISK_1: 資料檔案轉換完畢, 經過時間: 00:00:04
完成 backup 於 27-4月 -04

RMAN>

8.在目標資料庫plugin資料檔案

注意目標資料庫中的目標使用者必須存在,否則會報錯.

E:\Oracle\oradata\wwl\dpdata>impdp wwl/wwl dumpfile=trans.dmp directory=dpdata transport_datafiles='E:\Oracle\orad
ata\wwl\wwl\DATAFILE\TRANS01.DBF'

Import: Release 10.1.0.2.0 - Production on 星期二, 27 4月, 2004 15:02

連線到: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
已成功載入/解除安裝了主表 "wwl"."SYS_IMPORT_TRANSPORTABLE_01"
啟動 "wwl"."SYS_IMPORT_TRANSPORTABLE_01": wwl/******** dumpfile=trans.dmp directory=dpdata transport_datafiles='E:\
Oracle\oradata\wwl\wwl\DATAFILE\TRANS01.DBF'
處理物件型別 TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: 資料泵可傳輸的表空間作業中止
ORA-29342: 資料庫中不存在使用者 TRANS

作業 "wwl"."SYS_IMPORT_TRANSPORTABLE_01" 因致命錯誤於 15:02 停止

E:\Oracle\oradata\wwl\dpdata>sqlplus "/ as sysdba"

SQL*Plus: Release 10.1.0.2.0 - Production on 星期二 4月 27 15:03:03 2004

連線到:
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;

使用者已建立。

SQL> grant connect,resource to trans;

授權成功。

SQL> exit
從 Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options 斷開

E:\Oracle\oradata\wwl\dpdata>impdp wwl/wwl dumpfile=trans.dmp directory=dpdata transport_datafiles='E:\Oracle\orad
ata\wwl\wwl\DATAFILE\TRANS01.DBF'

Import: Release 10.1.0.2.0 - Production on 星期二, 27 4月, 2004 15:03

連線到: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
已成功載入/解除安裝了主表 "wwl"."SYS_IMPORT_TRANSPORTABLE_01"
啟動 "wwl"."SYS_IMPORT_TRANSPORTABLE_01": wwl/******** dumpfile=trans.dmp directory=dpdata transport_datafiles='E:\
Oracle\oradata\wwl\wwl\DATAFILE\TRANS01.DBF'
處理物件型別 TRANSPORTABLE_EXPORT/PLUGTS_BLK
處理物件型別 TRANSPORTABLE_EXPORT/TABLE
處理物件型別 TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
作業 "wwl"."SYS_IMPORT_TRANSPORTABLE_01" 已於 15:03 成功完成

9.檢查資料

E:\Oracle\oradata\wwl\dpdata>sqlplus trans/trans

SQL*Plus: Release 10.1.0.2.0 - Production on 星期二 4月 27 15:03:50 2004

連線到:
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
從 Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options 斷開

E:\Oracle\oradata\wwl\dpdata>

可以選擇把表空間更改為讀寫

SQL> alter tablespace trans read write;

表空間已更改。

10.總結

10g的表空間跨平臺遷移，較9i就是增加了一個使用Rman進行的檔案格式轉換的過程.
實際上也就是轉換了資料檔案頭的格式資訊而已.

-The End-

10g新特性之-跨平臺表空間傳輸

相關文章