[转载]关于Oracle10g跨平台传输表空间(页 1) - 常用数据库类{ Database Security } - 巨人脚下[ Oracle ] - 邪恶八进制信息安全团队技术讨论组努力为祖国的信息安全撑起一片蓝天

pub!1c 2006-2-26 19:59

[转载]关于Oracle10g跨平台传输表空间

信息来源:邪恶八进制信息安全团队
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支持的平台转换 
<BLOCKQUOTE><PRE>代码:<HR><CODE>
 SQL> select * fromv$transportable_platform;
 
 PLATFORM_ID PLATFORM_NAMEENDIAN_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 UNIXLittle
 4 HP-UX IA (64-bit)Big
 11 Linux IA (64-bit)Little
 15 HP Open VMSLittle
 8 Microsoft Windows IA (64-bit)Little
 
 PLATFORM_ID PLATFORM_NAMEENDIAN_FORMAT
 ----------- ---------------------------------------- --------------
 9 IBM zSeries Based LinuxBig
 13 Linux 64-bit for AMDLittle
 16 Apple Mac OSBig
 12 Microsoft Windows 64-bit for AMDLittle
 
<B</CODE><HR></PRE></BLOCKQUOTE>

pub!1c 2006-2-26 19:59

2.创建一个独立的自包含表空间 用于测试. 
<BLOCKQUOTE><PRE>代码:<HR><CODE>
 $ 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/eygle/system01.dbf
 /opt/oracle/oradata/eygle/undotbs01.dbf
 /opt/oracle/oradata/eygle/sysaux01.dbf
 /opt/oracle/oradata/eygle/users01.dbf
 /data1/oradata/systemfile/eygle01.dbf
 /opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_test_03xv34ny_.dbf
 /opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_itpub_03xv5g66_.dbf
 
 7 rows selected.
 
 SQL> create tablespace trans
 2datafile '/data1/oradata/systemfile/trans01.dbf'
 3size 10M;
 
 Tablespace created.
 
 SQL> create user trans identified by trans
 2default 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> selectcount(*) from test;
 
 COUNT(*)
 ----------
 1
 
 SQL> select * from test;
 
 OBJECT_NAME
 --------------------------------------------------------------------------------
 SUBOBJECT_NAMEOBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
 ------------------------------ ---------- -------------- -------------------
 CREATEDLAST_DDL_TIM TIMESTAMPSTATUST G S
 ------------ ------------ ------------------- ------- - - -
 TEST
 1560415604 TABLE
 27-APR-0427-APR-042004-04-27:14:05:42 VALIDN N N
 
 
 SQL> exit
 Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
 With the Partitioning, OLAP and Dat</CODE><HR></PRE></BLOCKQUOTE>

pub!1c 2006-2-26 19:59

3.导出要传输的表空间 $ pwd /opt/oracle $ cd dpdata $ ls $ expdp eygle/eygle dumpfile=trans.dmp directory=dpdata transport_tablespace=trans LRM-00101: unknown parameter name 'transport_tablespace' $ expdp eygle/eygle dumpfile=trans.dmp directory=dpdata TRANSPORT_TABLESPACES=trans Export: Release 10.1.0.2.0 - 64bit Production on Tuesday, 27 April, 2004 14:07 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 "EYGLE"."SYS_EXPORT_TRANSPORTABLE_01": eygle/******** 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 "EYGLE"."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 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> 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 eygle/eygle dumpfile=trans.dmp directory=dpdata TRANSPORT_TABLESPACES=trans Export: Release 10.1.0.2.0 - 64bit Production on Tuesday, 27 April, 2004 14:08 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 "EYGLE"."SYS_EXPORT_TRANSPORTABLE_01": eygle/******** 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 "EYGLE"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for EYGLE.SYS_EXPORT_TRANSPORTABLE_01 is: /opt/oracle/dpdata/trans.dmp Job "EYGLE"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 14:09

pub!1c 2006-2-26 20:00

4.使用rman转换文件格式 $ rman target / Recovery Manager: Release 10.1.0.2.0 - 64bit Production Copyright (c) 1995, 2004, Oracle. All rights reserved. connected to target database: EYGLE (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-EYGLE_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.

pub!1c 2006-2-26 20:02

5.确认导出文件已生成. $ ls /tmp/data* /tmp/data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg $ ls -l /tmp/data* -rw-r----- 1 oracle dba 10493952 Apr 27 14:12 /tmp/data_D-EYGLE_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 (172.16.33.32<IMG alt=皱眉 src="http://www.itpub.net/images/smilies/2.gif" border=0>none)): 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-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg? y 200 PORT command successful. 150 Binary data connection for data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg (172.16.32.65,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 (172.16.32.65,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 (172.16.32.65,2893) (73728 bytes). 226 Binary Transfer complete. ftp: 73728 bytes received in 0.03Seconds 2457.60Kbytes/sec. ftp> bye 221 Goodbye.

pub!1c 2006-2-26 20:02

7.使用rman在目标数据库转换文件 E:\Oracle\oradata\vilen\dpdata>rman target / 恢复管理器: 版本10.1.0.2.0 - Production Copyright (c) 1995, 2004, Oracle. All rights reserved. 连接到目标数据库: VILEN (DBID=1587222708) RMAN> CONVERT DATAFILE 'E:\Oracle\oradata\vilen\dpdata\data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg' 2> DB_FILE_NAME_CONVERT 3> 'E:\Oracle\oradata\vilen\dpdata\data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg','E:\Oracle\oradata\vilen\VILEN\DATAFILE\trans01.dbf'; 启动 backup 于 27-4月 -04 使用通道 ORA_DISK_1 通道 ORA_DISK_1: 启动数据文件转换 输出文件名=E:\ORACLE\ORADATA\VILEN\DPDATA\DATA_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01FK92HG 已转换的数据文件 = E:\ORACLE\ORADATA\VILEN\VILEN\DATAFILE\TRANS01.DBF 通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:04 完成 backup 于 27-4月 -04 RMAN>

pub!1c 2006-2-26 20:04

8.在目标数据库plugin数据文件 注意目标数据库中的目标用户必须存在 否则会报错. E:\Oracle\oradata\vilen\dpdata>impdp eygle/eygle dumpfile=trans.dmp directory=dpdata transport_datafiles='E:\Oracle\orad ata\vilen\VILEN\DATAFILE\TRANS01.DBF' Import: Release 10.1.0.2.0 - Production on 星期二, 27 4月, 2004 15:02 Copyright (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 已成功加载/卸载了主表 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01" 启动 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01": eygle/******** dumpfile=trans.dmp directory=dpdata transport_datafiles='E:\ Oracle\oradata\vilen\VILEN\DATAFILE\TRANS01.DBF' 处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK ORA-39123: 数据泵可传输的表空间作业中止 ORA-29342: 数据库中不存在用户 TRANS 作业 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01" 因致命错误于 15:02 停止 E:\Oracle\oradata\vilen\dpdata>sqlplus "/ as sysdba" SQL*Plus: Release 10.1.0.2.0 - Production on 星期二 4月 27 15:03:03 2004 Copyright (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 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\vilen\dpdata>impdp eygle/eygle dumpfile=trans.dmp directory=dpdata transport_datafiles='E:\Oracle\orad ata\vilen\VILEN\DATAFILE\TRANS01.DBF' Import: Release 10.1.0.2.0 - Production on 星期二, 27 4月, 2004 15:03 Copyright (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 已成功加载/卸载了主表 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01" 启动 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01": eygle/******** dumpfile=trans.dmp directory=dpdata transport_datafiles='E:\ Oracle\oradata\vilen\VILEN\DATAFILE\TRANS01.DBF' 处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK 处理对象类型 TRANSPORTABLE_EXPORT/TABLE 处理对象类型 TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK 作业 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01" 已于 15:03 成功完成

pub!1c 2006-2-26 20:05

9.检查数据 E:\Oracle\oradata\vilen\dpdata>sqlplus trans/trans SQL*Plus: Release 10.1.0.2.0 - Production on 星期二 4月 27 15:03:50 2004 Copyright (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 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\vilen\dpdata> 可以选择把表空间更改为读写 SQL> alter tablespace trans read write; 表空间已更改。

pub!1c 2006-2-26 20:05

10.总结 10g的表空间跨平台迁移，较9i就是增加了一个使用Rman进行的文件格式转换的过程. 实际上也就是转换了数据文件头的格式信息而已. 然而这一简单改进带来的方便之处是显而易见的.

页: [1]

邪恶八进制信息安全团队技术讨论组's Archiver

[转载]关于Oracle10g跨平台传输表空间