eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« Oracle中password file的作用及说明 | Blog首页 | 10g新特性之-expdp与传统exp的速度比较 »

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/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
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 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

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.

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.32none)): 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.


7.使用rman在目标数据库转换文件

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

恢复管理器: 版本10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

连接到目标数据库: EYGLE (DBID=1587222708)


RMAN> CONVERT DATAFILE 'E:\Oracle\oradata\eygle\dpdata\data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg'
2> DB_FILE_NAME_CONVERT
3> 'E:\Oracle\oradata\eygle\dpdata\data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg','E:\Oracle\oradata\eygle\EYGLE\DATAFILE\trans01.dbf';

启动 backup 于 27-4月 -04
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 启动数据文件转换
输出文件名=E:\ORACLE\ORADATA\EYGLE\DPDATA\DATA_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01FK92HG
已转换的数据文件 = E:\ORACLE\ORADATA\EYGLE\EYGLE\DATAFILE\TRANS01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:04
完成 backup 于 27-4月 -04

RMAN>

8.在目标数据库plugin数据文件

注意目标数据库中的目标用户必须存在,否则会报错.

E:\Oracle\oradata\eygle\dpdata>impdp eygle/eygle dumpfile=trans.dmp directory=dpdata transport_datafiles='E:\Oracle\orad
ata\eygle\EYGLE\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\eygle\EYGLE\DATAFILE\TRANS01.DBF'
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: 数据泵可传输的表空间作业中止
ORA-29342: 数据库中不存在用户 TRANS

作业 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01" 因致命错误于 15:02 停止


E:\Oracle\oradata\eygle\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\eygle\dpdata>impdp eygle/eygle dumpfile=trans.dmp directory=dpdata transport_datafiles='E:\Oracle\orad
ata\eygle\EYGLE\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\eygle\EYGLE\DATAFILE\TRANS01.DBF'
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
作业 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01" 已于 15:03 成功完成

9.检查数据

E:\Oracle\oradata\eygle\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\eygle\dpdata>

可以选择把表空间更改为读写

SQL> alter tablespace trans read write;

表空间已更改。

10.总结

10g的表空间跨平台迁移,较9i就是增加了一个使用Rman进行的文件格式转换的过程.
实际上也就是转换了数据文件头的格式信息而已.

然而这一简单改进带来的方便之处是显而易见的.

-The End-


历史上的今天...
    >> 2013-06-26文章:
    >> 2011-06-26文章:
    >> 2009-06-26文章:
    >> 2008-06-26文章:
    >> 2007-06-26文章:
           摄影习作-小小的太阳
    >> 2006-06-26文章:
           Oracle9iR2 NF:压缩表技术
    >> 2005-06-26文章:

无觅

By eygle on 2004-06-26 19:37 | Comments (14) | Oracle12c/11g | 29 |

14 Comments

eygle大师,您好!
如果单个的表空间的跨平台传输在10g上已然可以实现了,我想请教的是在Oracle10g里,我们用rman在某个操作系统,比如说AIX环境下,做的全备份(backup database),能否在别的环境,比如说HP-UNIX环境下,恢复成功?理论上是否可以实现?您是否成功做过?谢谢。

正常情况下是不行的。

其实跨平台的主要问题在于ENDIAN_FORMAT和file header的问题。
对于数据文件,后者可以手工解决,前者需要通过RMAN来解决。

而RMAN的备份集首先就是无法跨平台的。

感谢eygle大师关注,谢谢。ENDIAN_FORMAT和file header我还没有接触到这么深的层面,受教了:)另外rman的备份集恢复无法跨平台的,此结论是您自己的实验结论还是Oracle的官方说法,如果是官方说法,能否方便贴一下相关的链接,再次表示感谢!

RMAN着眼的就是数据文件

你测试一下看看啊!

请问空间数据库可以吗?比如从Oracle9i 里把raster数据转入到Oracle10g里
谢谢

[oracle@test2 ~]$ expdp trans/trans dumpfile=trans.dmp directory=dump transport_tablespaces=trans

Export: Release 10.2.0.3.0 - Production on Wednesday, 17 October, 2007 17:46:45

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31633: unable to create master table "TRANS.SYS_EXPORT_TRANSPORTABLE_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01647: tablespace 'TRANS' is read only, cannot allocate space in it

我查了一下
SQL> show parameter process

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1

TRANS表空间大小是100M
ORACLE 10.2.0.3
这个是什么问题?大师

expdp导出需要创建一张Master Table,你的表空间只读,无法创建。

你可以用别的用户来执行导出即可!

受益中......

大师,请教个问题,有个问题一直没有想通,这种迁移方式和用
expdp eygle/eygle TABLESPACES=trans dumpfile=trans.dump DIRECTORY=trans
导出数据,再到目标服务器做
impdp eygle/eygle dumpfile=trans.dump DIRECTORY=trans
导入,有什么区别吗,感觉这样做还简单一点。
谢谢。

以上的测试是不同的平台,直接导出导入是不支持的。

跨平台是关键!

不好意思,我还是不明白跨平台的意思,exp和expdp 出来的dump文件应该是二进制的平台独立的文件吧,我记得以前跨平台的数据迁移就使用的exp然后再imp。
我做了一个实验
我在hp-unix上建了一个新的tablesapce,用expdp导出之后,把文件传到windows目录下,然后impdp到windows平台下,是可以的阿

注意,我们测试的是传输表空间,涉及到物理文件的传输,是数据文件层面

使用了TRANSPORT_TABLESPACES 参数。

这不仅仅审exp/imp,中间还有数据文件。

大师,我前段时间看完了你的 "深入浅出oracle",收获蛮大的.
这段时间我在看"循序渐进oracle".

windows xp /oracle 10gr2
我在测试10g expdp/impdp的新特性的时候,顺便结合可传输表空间,遇到一个现象:
我把源平台上的数据库中将要导出的tablespace 设置为只读,用expdp导出时报下列错误:
ora-31626:作业不存在
ora-31626:无法创建主表"user.sys_export_tablespace_01 "
ora-06512: 在"sys.dbms_sys_error",line 95
ora-06512: 在"sys.kupv$ft",line 701
ora-01647:表空间"users"是只读,无法在其中创建空间.

我判断为:
1.在创建存储目录和授权时表空间未打开读写功能.
2.在导出时表空间为只读.(在exp时设置为只读是ok的)

按照我的判断,我做了以下工作:
1.在创建存储目录和授权时将表空间设置为可读写.
2.把表空间设置为可读写再进行expdp.

结果显示的信息,作业"user.sys_export_tablespace_01 "完成,但有一个错误.
ora-31655:尚未为作业选择数据或元数据对象.

我该如何处理这种情况?

请给我回一封邮件,告知.
谢谢!

我在使用expdp导出transportable tablespace时也报错,说操作无效;无法打开日志文件;目录名无效。
而且,使用expdp system/system方式也报错,说用户名/密码错误,拒绝访问;后改为expdp 'system/system as sysdba'方式又报了开头的错误。
郁闷!expdp工具也太难用了吧。


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com