oracle 10g新特性,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=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 "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

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

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

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

连接到目标数据库: 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

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

已成功加载/卸载了主表 "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

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

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

已成功加载/卸载了主表 "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

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\wwl\dpdata>

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

SQL> alter tablespace trans read write;

表空间已更改。

10.总结

10g的表空间跨平台迁移,较9i就是增加了一个使用Rman进行的文件格式转换的过程.

实际上也就是转换了数据文件头的格式信息而已.

-The End-

0b1331709591d260c1c78e86d0c51c18.png

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值