ITPub博客

首页 > 数据库 > Oracle > oracle跨平台迁移表空间

oracle跨平台迁移表空间

原创 Oracle 作者:lllllcheng 时间:2016-03-11 21:32:46 0 删除 编辑
操作平台版本
主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.DMPTEST.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 ONLY  YES

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





来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30606744/viewspace-2055870/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-12-02

  • 博文量
    41
  • 访问量
    119804