ITPub博客

首页 > 数据库 > Oracle > Oracle 低版本导入高版本按用户expdp/impdp

Oracle 低版本导入高版本按用户expdp/impdp

Oracle 作者:urgel_babay 时间:2016-02-29 17:28:44 0 删除 编辑

  因开发的需要,要分别把2个生产库中各自相同的一个用户下的的数据导入到测试库中
一、Oracle环境 (这里分别用iP最后的数字代称)
9 库 Oracle :11.2.0.3
5 库 Oracle :11.1.0.6
13库 Oracle :11.2.0.4
     5库和9库下的u_pd_dw用户,导入13库之后的对应情况:

   用户  默认表空间  对应库  用户  默认表空间
 5  u_pd_dw  TBS_PD_PW  13  u_sd_ca  TBS_SD_CA
 9  u_pd_dw  TBS_PD_PW  13  u_sd_sh  TBS_SD_SH

二、导出方案
1、创建目录。
2、执行导出命令。
5库:expdp system/oracle directory=dir dumpfile=u_pd_dw.dmp  logfile=u_pd_dw.log  schemas=u_pd_dw COMPRESSION=all  parallel=8
9库:expdp system/oracle directory=dir dumpfile=u_pd_dw.dmp  logfile=u_pd_dw.log  schemas=u_pd_dw COMPRESSION=all  parallel=16

具体实施导出:
5库:

点击(此处)折叠或打开

  1. [root@zhang5 dmp55]# expdp system/oracle directory=dir dumpfile=u_pd_dw5.dmp logfile=u_pd_dw5.log schemas=u_pd_dw COMPRESSION=all parallel=8;
  2. Export: Release 11.1.0.6.0 - 64bit Production on Saturday, 01 November, 2014 17:49:45
  3. Copyright (c) 2003, 2007, Oracle. All rights reserved.
  4. ;;;
  5. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
  6. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  7. Starting \"SYSTEM\".\"SYS_EXPORT_SCHEMA_02\": system/******** directory=dir dumpfile=u_pd_dw5.dmp logfile=u_pd_dw5.log schemas=u_pd_dw COMPRESSION=all para
  8. llel=8
  9. Estimate in progress using BLOCKS method...
  10. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
  11. Total estimation using BLOCKS method: 38.90 GB
  12. Processing object type SCHEMA_EXPORT/USER

  13. ---------------------
  14. . . exported \"U_PD_DW\".\"T99_COD_STATE\" 0 KB 0 rows
  15. Master table \"SYSTEM\".\"SYS_EXPORT_SCHEMA_02\" successfully loaded/unloaded
  16. ******************************************************************************
  17. Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:
  18.   /orabak/20141101/u_pd_dw5.dmp
  19. Job \"SYSTEM\".\"SYS_EXPORT_SCHEMA_02\" successfully completed at 18:31:05
  20. [root@zhang5 dmp55]#
说明一下:由于两个库的物理机器配置不同,所以并行度也有所差异
9库:

点击(此处)折叠或打开

  1. [oracle@zhang9 20141101]$ expdp system/oracle directory=dir dumpfile=u_pd_dw9.dmp logfile=u_pd_dw9.log schemas=u_pd_dw COMPRESSION=all parallel=16
  2. Export: Release 11.2.0.3.0 - Production on Sat Nov 1 11:16:25 2014
  3. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
  4. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  5. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  6. Starting \"SYSTEM\".\"SYS_EXPORT_SCHEMA_01\": system/******** directory=dir dumpfile=u_pd_dw9.dmp logfile=u_pd_dw9.log schemas=u_pd_dw COMPRESSION=all parallel=16
  7. Estimate in progress using BLOCKS method...
  8. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
  9. Total estimation using BLOCKS method: 246.9 GB
  10. Processing object type SCHEMA_EXPORT/USER
  11. . . exported \"U_PD_DW\".\"T99_COD_STATE\" 0 KB 0 rows
  12. Master table \"SYSTEM\".\"SYS_EXPORT_SCHEMA_01\" successfully loaded/unloaded
  13. *********************** 略 *******************************************************
  14. Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  15.   /orabak/20141101/u_pd_dw9.dmp
  16. Job \"SYSTEM\".\"SYS_EXPORT_SCHEMA_01\" successfully completed at 13:55:27
ok! 成功导出

三、导入方案
核心命令
impdp system/oracle directory=dir89  dumpfile=u_pd_dw.dmp  parallel=3 schemas=u_pd_dw remap_schema=u_pd_dw:u_sd_sh remap_tablespace=TBS_PD_PW:TBS_SD_SH
impdp system/oracle directory=dir55  dumpfile=u_pd_dw.dmp  parallel=3 schemas=u_pd_dw remap_schema=u_pd_dw:u_sd_ca remap_tablespace=TBS_PD_PW:TBS_SD_CA
1、检查导出的用户已经相应的表空间
col object_name  format a26
col tablespace_name  format a26
select o.object_name,o.NAMESPACE,t.tablespace_name,i.TABLESPACE_NAME
from dba_objects o
,dba_tables t
,dba_indexes i
where t.owner='U_PD_DW'
and t.OWNER=o.OWNER
and i.OWNER=o.OWNER;
----- 这个是我写的sql,我在导入的时候,报错了。然后根据报错的信息,找到需要创建哪些表空间和其他相关联的的用户。
----- 相关联的的用户只需要创建,导入成功后,也可drop 掉。
----- 对于相关联的的表空间,要根据原来数据库里面的实际情况,来设置大小和增长量。只要足够用就可以了。

2、先创建相应的表空间。
3、创建相关的用户,权限,默认表空间。
4、创建目录。

具体导入实施:

1、根据查询到的结果,创建表空间----- 主要是根据自己的实际情况,如果你熟悉每个数据库,就更方便了
        TBS_SD_CA
create tablespace TBS_SD_CA datafile '/u01/oracle/oradata/DEVBIREPORT/TBS_SD_CA01.dbf' size 10G autoextend on next 100M maxsize 20G;
alter tablespace TBS_SD_CA add datafile '/u02/oradata/TBS_SD_CA02.dbf' size 5G autoextend on next 10M maxsize 25G;
create user u_sd_ca identified by belle default tablespace TBS_SD_CA temporary tablespace TEMP; 
grant dba,connect,resource,CREATE TABLE,create view,create procedure TO u_sd_ca;
        TBS_SD_SH
create tablespace TBS_SD_SH datafile '/u01/oracle/oradata/DEVBIREPORT/TBS_SD_SH01.dbf' size 1G autoextend on next 20M maxsize 30G;
create user u_sd_sh identified by belle default tablespace TBS_SD_SH temporary tablespace TEMP; 
grant dba,connect,resource,CREATE TABLE,create view,create procedure TO u_sd_sh;
提醒:这里只给出部分的,根据实际情况来预算导入的数据量大小,预先的设置表空间的大小,增长量,总大小和数据文件的个数。
         以免在导入的过程中,由于表空间不足而使导入失败。自动增长量,先设置大一些,导入完成后再调小。
2、目录directory
 create directory dir9 as '/u01/oracle/imp/dmp9';    ---- 一般情况下是sys用户创建,然后授权给其他用户。当然也可以授权其他用户创建directory的权限。总之能用就行
 grant read,write on directory dir89 to public;         ---- 我是为了好区分,才这样
 create directory dir5 as '/u01/oracle/imp/dmp5';
 grant read,write on directory dir89 to public;

9库:

点击(此处)折叠或打开

  1. [oracle@bidevelop4 dmp89]$ impdp system/oracle directory=dir89 dumpfile=u_pd_dw.dmp parallel=3 schemas=u_pd_dw remap_schema=u_pd_dw:u_sd_sh remap_tablespace=TBS_PD_DW:TBS_SD_SH
  2. Import: Release 11.2.0.4.0 - Production on Mon Nov 3 21:06:45 2014
  3. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
  4. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  5. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  6. Master table \"SYSTEM\".\"SYS_IMPORT_SCHEMA_01\" successfully loaded/unloaded
  7. Starting \"SYSTEM\".\"SYS_IMPORT_SCHEMA_01\": system/******** directory=dir89 dumpfile=u_pd_dw.dmp parallel=3 schemas=u_pd_dw remap_schema=u_pd_dw:u_sd_sh remap_tablespace=TBS_PD_DW:TBS_SD_SH
  8. Processing object type SCHEMA_EXPORT/USER
  9. ORA-31684: Object type USER:\"U_SD_SH\" already exists
  10. Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
  11. Processing object type SCHEMA_EXPORT/ROLE_GRANT
  12. Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
  13. Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
  14. Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
  15. Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
  16. ORA-31684: Object type TYPE:\"U_SD_SH\".\"QUEST_SOO_ALERTTRACE_LINE_TYP\" already exists
  17. ORA-31684: Object type TYPE:\"U_SD_SH\".\"QUEST_SOO_ALERTTRACE_LOG_TYP\" already exists
  18. Processing object type SCHEMA_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
  19. -------略--- 
  20. Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
    Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed  at Wed Nov 5 05:53:59 2014 elapsed 1 08:47:13
现在是2014.11.05.早上9点
好了,9库的数据已经导入到了13库,耗时8小时47分13秒。
由于时间的原因,5库的就不贴出来。

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

上一篇: Oracle ORA-错误表
请登录后发表评论 登录
全部评论

注册时间:2016-02-29

  • 博文量
    203
  • 访问量
    213005