Oracle 10g新特性之-跨平台表空间传输

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 Copyright （c） 1982, 2004, 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 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=transExport: Release 10.1.0.2.0 - 64bit Production on Tuesday, 27 April, 2004 14:07Copyright （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
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29335: tablespace "TRANS" is not read onlyJob "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 2004Copyright （c） 1982, 2004, 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 optionsSQL> 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=transExport: Release 10.1.0.2.0 - 64bit Production on Tuesday, 27 April, 2004 14:08Copyright （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 at 14:094.使用rman转换文件格式
$ rman target /Recovery Manager: Release 10.1.0.2.0 - 64bit ProductionCopyright （c） 1995, 2004, 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-04RMAN> 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:Oracleoradatawwldpdata>rman target /恢复管理器: 版本10.1.0.2.0 - ProductionCopyright （c） 1995, 2004, Oracle. All rights reserved.连接到目标数据库: wwl （DBID=1587222708）
RMAN> CONVERT DATAFILE "E:Oracleoradatawwldpdatadata_D-wwl_I-1337390772_TS-TRANS_FNO-8_01fk92hg"
2> DB_FILE_NAME_CONVERT
3> "E:Oracleoradatawwldpdatadata_D-wwl_I-1337390772_TS-TRANS_FNO-8_01fk92hg","E:OracleoradatawwlwwlDATAFILE rans01.dbf";启动 backup 于 27-4月 -04
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 启动数据文件转换
输出文件名=E:ORACLEORADATAwwlDPDATADATA_D-wwl_I-1337390772_TS-TRANS_FNO-8_01FK92HG
已转换的数据文件 = E:ORACLEORADATAwwlwwlDATAFILETRANS01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:04
完成 backup 于 27-4月 -04RMAN>
8.在目标数据库plugin数据文件注意目标数据库中的目标用户必须存在,否则会报错. E:Oracleoradatawwldpdata>impdp wwl/wwl dumpfile=trans.dmp directory=dpdata transport_datafiles="E:Oracleorad
atawwlwwlDATAFILETRANS01.DBF"Import: Release 10.1.0.2.0 - Production on 星期二, 27 4月, 2004 15:02Copyright （c） 2003, Oracle. All rights reserved.连接到: 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:
OracleoradatawwlwwlDATAFILETRANS01.DBF"
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: 数据泵可传输的表空间作业中止
ORA-29342: 数据库中不存在用户 TRANS作业 "wwl"."SYS_IMPORT_TRANSPORTABLE_01" 因致命错误于 15:02 停止
E:Oracleoradatawwldpdata>sqlplus "/ as sysdba"SQL*Plus: Release 10.1.0.2.0 - Production on 星期二 4月 27 15:03:03 2004Copyright （c） 1982, 2004, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining optionsSQL> 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:Oracleoradatawwldpdata>impdp wwl/wwl dumpfile=trans.dmp directory=dpdata transport_datafiles="E:Oracleorad
atawwlwwlDATAFILETRANS01.DBF"Import: Release 10.1.0.2.0 - Production on 星期二, 27 4月, 2004 15:03Copyright （c） 2003, Oracle. All rights reserved.连接到: 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:
OracleoradatawwlwwlDATAFILETRANS01.DBF"
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
作业 "wwl"."SYS_IMPORT_TRANSPORTABLE_01" 已于 15:03 成功完成9.检查数据E:Oracleoradatawwldpdata>sqlplus trans/transSQL*Plus: Release 10.1.0.2.0 - Production on 星期二 4月 27 15:03:50 2004Copyright （c） 1982, 2004, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining optionsSQL> select count（*） from test;COUNT（*）
----------
1SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options 断开E:Oracleoradatawwldpdata>可以选择把表空间更改为读写SQL> alter tablespace trans read write;表空间已更改。
10.总结10g的表空间跨平台迁移，较9i就是增加了一个使用Rman进行的文件格式转换的过程.
实际上也就是转换了数据文件头的格式信息而已.
-The End-更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12Oracle 表空间利用率及对象大小查询Linux 下实现Oracle随系统自动启动和关闭相关资讯 Oracle数据库基础教程

在Oracle数据库中插入含有&符号的（03/06/2013 09:20:14）
Oracle 执行计划更改导致数据加工（02/13/2013 14:45:04）
判断Oracle Sequence是否存在（02/13/2013 14:32:26）

Oracle数据库中无法对数据表进行（02/26/2013 14:24:58）
Oracle 在同一台主机上建立用户管（02/13/2013 14:40:58）
Oracle em 无法启动,报not found错（02/13/2013 14:29:48）

本文评论查看全部评论（0）