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–

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐