oracle跨平台迁移表空间步骤操作教程

操作平台版本

主host1：microsoft windows x86 64-bit

副host2：linux x86 64-bit

sql> select * from v$version;

banner

-------------------------------------------------------------------------------

oracle database 11g enterprise edition release 11.2.0.1.0 - 64bit production

pl/sql release 11.2.0.1.0 - production

core 11.2.0.1.0 production

tns for 64-bit windows: version 11.2.0.1.0 - production

nlsrtl version 11.2.0.1.0 - production

sys@prod1>select * from v$version;

banner

--------------------------------------------------------------------------------

oracle database 11g enterprise edition release 11.2.0.4.0 - 64bit production

pl/sql release 11.2.0.4.0 - production

core 11.2.0.4.0 production

tns for linux: version 11.2.0.4.0 - production

nlsrtl version 11.2.0.4.0 - production

创建测试表空间 表 用户

sql> create tablespace test datafile 'c:\app\administrator\oradata\prod1\test.dbf' size 10m autoextend on;

表空间已创建。

sql> create user enmo identified by oracle default tablespace test;

用户已创建。

sql> create directory cheng as 'c:\dump\';

目录已创建。

sql> grant dba to enmo;

授权成功。

sql> conn enmo/oracle

已连接。

sql> create table t1 as select * from dba_objects;

表已创建。

sql> select table_name,tablespace_name from user_tables;

tabl tablespace_name

---- ------------------------------------------------------------

t1 test

确定源端字节序

sql> select d.platform_name,p.endian_format from v$transportable_platform p,v$da

tabase d where p.platform_name=d.platform_name;

platform_name endian_format

---------------------------- ----------------------------

microsoft windows x86 64-bit little

确定目标端字节序

sys@prod1>select d.platform_name,p.endian_format from v$transportable_platform p,v$database d where p.platform_name=d.platform_name;

platform_name endian_format

---------------------------- --------------

linux x86 64-bit little

表空间自包含检查

sql> exec dbms_tts.transport_set_check('test',true,true);

pl/sql 过程已成功完成。

sql> select * from transport_set_violations;

未选定行

sql> alter tablespace test read only;

表空间已更改。

sql> ho rman target /

恢复管理器: release 11.2.0.1.0 - production on 星期五 3月 11 21:08:20 2016

copyright (c) 1982, 2009, oracle and/or its affiliates. all rights reserved.

连接到目标: prod1 (dbid=2126711254)

转换字节序

rman> convert tablespace 'test' to platform 'linux x86 64-bit' format 'c:\dump\test.dbf';

启动 conversion at source 于 11-3月 -16

使用通道 ora_disk_1

通道 ora_disk_1: 启动数据文件转换

输入数据文件: 文件号=00006 名称=c:\app\administrator\oradata\prod1\test.dbf

已转换的数据文件 = c:\dump\test.dbf

通道 ora_disk_1: 数据文件转换完毕, 经过时间: 00:00:01

完成 conversion at source 于 11-3月 -16

导出表空间元数据

c:\>expdp system/oracle directory=cheng transport_tablespaces='test' transport_full_check=y

export: release 11.2.0.1.0 - production on 星期五 3月 11 21:18:38 2016

copyright (c) 1982, 2009, oracle and/or its affiliates. all rights reserved.

连接到: oracle database 11g enterprise edition release 11.2.0.1.0 - 64bit production

with the partitioning, olap, data mining and real application testing options

启动 "system"."sys_export_transportable_01": system/******** directory=cheng transport_tablespaces='test' transport_full_check=y

处理对象类型 transportable_export/plugts_blk

处理对象类型 transportable_export/table

处理对象类型 transportable_export/post_instance/plugts_blk

已成功加载/卸载了主表 "system"."sys_export_transportable_01"

******************************************************************************

system.sys_export_transportable_01 的转储文件集为:

c:\dump\expdat.dmp

******************************************************************************

可传输表空间 test 所需的数据文件:

c:\app\administrator\oradata\prod1\test.dbf

作业 "system"."sys_export_transportable_01" 已于 21:19:12 成功完成

将导出的文件expdat.dmp及test.dbf传输到目标端

[oracle@enmo app]$ ll expdat.dmp test.dbf

-rwxrwx---. 1 oracle oinstall 98304 mar 11 21:19 expdat.dmp

-rwxrwx---. 1 oracle oinstall 11542528 mar 11 21:12 test.dbf

[oracle@enmo app]$ sqlplus / as sysdba

sql*plus: release 11.2.0.4.0 production on fri mar 11 21:24:26 2016

copyright (c) 1982, 2013, oracle. all rights reserved.

connected to:

oracle database 11g enterprise edition release 11.2.0.4.0 - 64bit production

with the partitioning, olap, data mining and real application testing options

sys@prod1>create directory cheng as '/u01/app';

directory created.

sys@prod1>create user enmo identified by oracle;

user created.

导入表空间到目标库

[oracle@enmo app]$ impdp system/oracle directory=cheng dumpfile=expdat.dmp transport_datafiles=/u01/app/test.dbf

import: release 11.2.0.4.0 - production on fri mar 11 21:28:02 2016

copyright (c) 1982, 2011, oracle and/or its affiliates. all rights reserved.

connected to: oracle database 11g enterprise edition release 11.2.0.4.0 - 64bit production

with the partitioning, olap, data mining and real application testing options

master table "system"."sys_import_transportable_01" successfully loaded/unloaded

source time zone version is 11 and target time zone version is 14.

starting "system"."sys_import_transportable_01": system/******** directory=cheng dumpfile=expdat.dmp transport_datafiles=/u01/app/test.dbf

processing object type transportable_export/plugts_blk

processing object type transportable_export/table

processing object type transportable_export/post_instance/plugts_blk

job "system"."sys_import_transportable_01" successfully completed at fri mar 11 21:28:06 2016 elapsed 0 00:00:03

[oracle@enmo app]$ sqlplus / as sysdba

sql*plus: release 11.2.0.4.0 production on fri mar 11 21:30:25 2016

copyright (c) 1982, 2013, oracle. all rights reserved.

connected to:

oracle database 11g enterprise edition release 11.2.0.4.0 - 64bit production

with the partitioning, olap, data mining and real application testing options

sys@prod1>select tablespace_name,status,plugged_in from dba_tablespaces;

tablespace_name status plu

------------------------------ ------------

system online no

sysaux online no

undotbs1 online no

temp online no

users online no

example online yes

test read onlyyes

7 rows selected.

sys@prod1>alter tablespace test read write;

tablespace altered.

sys@prod1>select owner,table_name,tablespace_name from dba_tables where owner='enmo';

owner table_name tablespace_name

---------- ------------ ------------------------------

enmo t1 test

sys@prod1>select count(*) from enmo.t1;

count(*)

----------

72536

-- end--