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