Oracle-数据泵expdp/impdp实操

本文涉及的产品
云原生数据仓库AnalyticDB MySQL版,8核32GB 100GB 1个月
简介: Oracle-数据泵expdp/impdp实操

概述


数据泵的作用:


  • 1.实现逻辑备份和逻辑恢复
  • 2.在数据库用户之间移动对象
  • 3.在数据库之间移动对象
  • 4.实现表空间转移


数据泵的特点与传统导出导入的区别


  • 1.EXP和IMP是客户段工具程序, EXPDP和IMPDP是服务端的工具程序
  • 2.EXP和IMP效率比较低. EXPDP和IMPDP效率高
  • 3.数据泵功能强大并行、过滤、转换、压缩、加密、交互等等
  • 4.数据泵不支持9i以前版本, EXP/IMP短期内还是比较适用
  • 5.同exp/imp数据泵导出包括导出表,导出方案,导出表空间,导出数据库4种方式.


Oracle官方指导文档


需求描述


198测试环境的cc用户下的全量数据,需要同步到到准生产环境197一份


方案分析


因数据库版本为11.2(大于Oracle10g)初步确定使用数据泵的方式从198导出后导入197数据库


操作步骤


expdp


SQL 窗口下执行以下操作


1.创建逻辑目录


创建逻辑目录

SQL>  create directory dir_dp as '/oracle/oracle11';


2.查看目录

同时查看操作系统是否存在,如果不存在,mkdir -p 新建目录 否则出错

SELECT privilege, directory_name, DIRECTORY_PATH
  FROM user_tab_privs t, all_directories d
 WHERE t.table_name(+) = d.directory_name
 ORDER BY 2, 1;


20161024222155430.png


或者 可以查看

select * from dba_directories ;


DATA_PUMP_DIR就是直接可以用的DUMP目录,导出文件和导入文件就放在上面的路径下,如果没有,则需要手动创建并赋给读/写权限.

如果没有查询到的话,刷新一下

SQL> host ls /oracle/oracle11


3.给cc用户赋予在指定目录的操作权限

以dba用户等管理员赋予

SQL>  Grant read,write on directory dir_dp to cc;


4. 导出数据

使用主机的Oracle用户

按用户导/导出指定schema:

oracle@entel2:[/oracle]$expdp cc/password@//10.45.7.198:1521/cc schemas=cc directory=dir_dp dumpfile =expdp_test1.dmp logfile=expdp_test1.log;
...........
Dump file set for CC.SYS_EXPORT_SCHEMA_01 is:
  /oracle/oracle11/expdp_test1.dmp
Job "CC"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Aug 20 12:02:13 2016 elapsed 0 00:01:33



参数解析:

  • schemas:数据库用户别名 而非实例名
  • cc/password@//10.45.7.198:1521/cc port后面指定的是实例名 ,最前面的是用户名和密码


scp dmp 到目标主机

[root@entel2 ~]# scp expdp_198.dmp oracle@10.45.7.197:/oracle/oracle11


impdp

删除用户

SQL> DROP USER cc CASCADE;


创建用户

SQL>CREATE USER cc IDENTIFIED BY password
DEFAULT TABLESPACE TAB_CC;


赋予权限

SQL>GRANT CONNECT ,RESOURCE TO cc ;
SQL>GRANT ALL PRIVILEGES TO cc ;
SQL>GRANT DBA TO cc ;


使用Oracle用户导入

相同的schema

oracle@entel1:[/oracle]$impdp cc/password@//10.45.7.197:1521/cc schemas=cc directory=dir_dp_198 dumpfile =expdp_198.dmp logfile=expdp_198.log;


不同的schema

oracle@entel1:[/oracle]$impdp entelcc_st4/password@//10.45.7.197:1521/cc schemas=cc remap_schema=cc:entelcc_st4 directory=dir_dp_198 dumpfile =expdp_198.dmp logfile=expdp_198.log;


说明:

  • 目标主机上 directory也需要建立 ,并赋予读写权限,如果不指定,则使用缺省目录 缺省Directory为DATA_PUMP_DIR
--查看缺省目录
select * from dba_directories where directory_name='DATA_PUMP_DIR';


DATA_PUMP_DIR这个目录,是系统缺省目录,如不单独指定目录,dmp文件会在这里,但默认这个目录其他用户是没有权限的。

  • dmp文件需要存放到对应的directory目录下,否者导入报错
  • 如果目标库的schema和源库的schema一致,按以上脚本即可,不一致的情况需要指定,语法如下:schemas=old_schema remap_schema=old_schema:new_schema
    否者报错:
ORA-39002: invalid operation
ORA-39166: Object ENTELCC_ST4 was not found.


REMAP_SCHEMA、REMAP_TABLESPACE、REMAP_DATAFILE

除了REMAP_SCHEMA,还有REMAP_TABLESPACE、REMAP_DATAFILE 等

1、REMAP_SCHEMA

该选项用于将源方案的所有对象装载到目标方案中:REMAP_SCHEMA=source_schema:target_schema

2、REMAP_TABLESPACE

将源表空间的所有对象导入到目标表空间中:REMAP_TABLESPACE=source_tablespace:target:tablespace

3、REMAP_DATAFILE 该选项用于将源数据文件名转变为目标数据文件名,在不同平台之间搬移表空间时可能需要该选项.

REMAP_DATAFIEL=source_datafie:target_datafile


不同路径下的dump文件同时导入

需要创建多个directory

指定 dumpfile=dir1:1.dmp,dir2:2.dm


查看脚本

查看各个SCHEMA占用空间的大小

select owner,round(sum(bytes/1024/1024),2)||' MB' as schema_size from dba_segments group by owner order by SCHEMA_SIZE desc;  


查询己经导入的对象个数

select owner,count(*) from dba_objects where owner like 'ENTELCC_ST4%' group by owner order by owner;  


这里不同的schema,之前没有注意到,导致一直报错,看到了yfleng2002的博文Oracle schema 级别的数据迁移,赞一个。


schema解释

A schema is a collection of database objects (used by a user.).

Schema objects are the logical structures that directly refer to the database’s data.

A user is a name defined in the database that can connect to and access objects.

Schemas and users help database administrators manage database security.


一个用户一般对应一个schema,该用户的schema名等于用户名,并作为该用户缺省schema。


Oracle数据库中不能新创建一个schema,要想创建一个schema,只能通过创建一个用户的方法解决(Oracle中虽然有create schema语句,但是它并不是用来创建一个schema的),在创建一个用户的同时为这个用户创建一个与用户名同名的schem并作为该用户的缺省shcema。


即schema的个数同user的个数相同,而且schema名字同user名字一一 对应并且相同,所有我们可以称schema为user的别名,虽然这样说并不准确,但是更容易理解一些。


一个用户有一个缺省的schema,其schema名就等于用户名,当然一个用户还可以使用其他的schema。如果我们访问一个表时,没有指明该表属于哪一个schema中的,系统就会自动给我们在表上加上缺省的sheman名。比如我们在访问数据库时,访问scott用户下的emp表,通过select * from emp; 其实,这sql语句的完整写法为select * from scott.emp。在数据库中一个对象的完整名称为schema.object,而不属user.object。类似如果我们在创建对象时不指定该对象的schema,在该对象的schema为用户的缺省schema。这就像一个用户有一个缺省的表空间,但是该用户还可以使用其他的表空间,如果我们在创建对象时不指定表空间,则对象存储在缺省表空间中,要想让对象存储在其他表空间中,我们需要在创建对象时指定该对象的表空间。


以上schema解释参考oracle中schema指的是什么?,感谢分享


注意事项


  1. EXPDP和IMPDP是服务端的工具程序,只能在ORACLE服务端使用,不能在客户端使用
  2. 低版本是不能导入高版本dmp,需要在高版本的EXPDP导出时指定版本号导出。低版本IMPDP无需指定版本。
    例如:11.2.0.4导入到10.2.0.5
expdp username/password directory=dump_dir dumpfile=test.dmp version=10.2.0.5.0


导出导入全库

全库导出

单个实例

oracle@entel2:[/oracle]$expdp \'/ as sysdba\' directory=dir_dp full=y dumpfile=fulldb.dmp parallel=2;
【开始执行........上述命令也可以增加logfile等 自行决定】
Export: Release 11.2.0.4.0 - Production on Mon Oct 24 18:47:01 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
Starting "SYS"."SYS_EXPORT_FULL_01":  "/******** AS SYSDBA" directory=dir_dp full=y dumpfile=fulldb.dmp parallel=2 
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.294 GB
Processing object type DATABASE_EXPORT/TABLESPACE
. . exported "ZMC"."NM_ALARM_EVENT"                      317.5 MB  467108 rows
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
..............


PARALLEL 更改当前作业的活动 worker 的数目。PARALLEL=<worker 的数目>。指定执行导出操作的并行进程个数,默认值为1

FULL 导出整个数据库 默认为N。


多个实例

如果存在多个实例,需要先export ORACLE_SID20180513021125849.png

tbprocsdb1:[/oracle$]export ORACLE_SID=testbed
tbprocsdb1:[/oracle$]expdp \'/ as sysdba\' directory=TMP_DIR full=y dumpfile=fulldb20180512.dmp logfile=full20180512.log parallel=4;
Export: Release 11.2.0.4.0 - Production on Sat May 12 14:06:38 2018
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
Starting "SYS"."SYS_EXPORT_FULL_01":  "/******** AS SYSDBA" directory=TMP_DIR full=y dumpfile=fulldb20180512.dmp logfile=full20180512.log parallel=4 
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

全库导入

$ impdp \'/ as sysdba\' directory=dir_dp full=y dumpfile=fulldb.dmp parallel=2 table_exists_action=replace;

更多详情


单表 以及单表按条件导出导入

artisandb:[/oracle$]  expdp xxx/xxxx@PR_CC   directory=CC_DUMP  include=table:\" =\'TMP_BATCH203\' \"      dumpfile =TMP_BATCH203.dmp logfile=TMP_BATCH203.log;
Export: Release 11.2.0.4.0 - Production on Thu May 31 14:31:41 2018
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
Starting "CRM"."SYS_EXPORT_SCHEMA_02":  crm/********@PR_CC directory=CC_DUMP include=table:" ='TMP_BATCH203' " dumpfile=TMP_BATCH203.dmp logfile=TMP_BATCH203.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported "CRM"."TMP_BATCH203"                         9.75 KB      50 rows
Master table "CRM"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for CRM.SYS_EXPORT_SCHEMA_02 is:
  /ccexp/cc/TMP_BATCH203.dmp
Job "CRM"."SYS_EXPORT_SCHEMA_02" successfully completed at Thu May 31 14:31:49 2018 elapsed 0 00:00:08
artisandb:[/oracle$]


或者

artisandb:[/oracle$]  expdp xxx/xxxxx@PR_CC   directory=CC_DUMP   Tables=TMP_BATCH203      dumpfile =TMP_BATCH204.dmp logfile=TMP_BATCH204.log;
Export: Release 11.2.0.4.0 - Production on Thu May 31 14:36:25 2018
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
Starting "CRM"."SYS_EXPORT_TABLE_01":  crm/********@PR_CC directory=CC_DUMP Tables=TMP_BATCH203 dumpfile=TMP_BATCH204.dmp logfile=TMP_BATCH204.log 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
. . exported "CRM"."TMP_BATCH203"                         9.75 KB      50 rows
Master table "CRM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CRM.SYS_EXPORT_TABLE_01 is:
  /ccexp/cc/TMP_BATCH204.dmp
Job "CRM"."SYS_EXPORT_TABLE_01" successfully completed at Thu May 31 14:36:28 2018 elapsed 0 00:00:02
artisandb:[/oracle$]

按照条件

artisandb:[/ccexp/cc$]  expdp xxx/xxxxx@PR_CC   directory=CC_DUMP   Tables=TMP_BATCH203      dumpfile =TMP_BATCH206.dmp logfile=TMP_BATCH206.log query=TMP_BATCH203:'"where acc_nbr_id=170686"';
Export: Release 11.2.0.4.0 - Production on Thu May 31 14:40:16 2018
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
Starting "CRM"."SYS_EXPORT_TABLE_01":  crm/********@PR_CC directory=CC_DUMP Tables=TMP_BATCH203 dumpfile=TMP_BATCH206.dmp logfile=TMP_BATCH206.log query=TMP_BATCH203:"where acc_nbr_id=170686" 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
. . exported "CRM"."TMP_BATCH203"                        7.601 KB       1 rows
Master table "CRM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CRM.SYS_EXPORT_TABLE_01 is:
  /ccexp/cc/TMP_BATCH206.dmp
Job "CRM"."SYS_EXPORT_TABLE_01" successfully completed at Thu May 31 14:40:27 2018 elapsed 0 00:00:10
artisandb:[/ccexp/cc$]


然后drop掉TMP_BATCH203

artisandb:[/ccexp/cc$]  impdp  xxx/xxxxx@PR_CC   directory=CC_DUMP     dumpfile =TMP_BATCH206.dmp  logfile=TMP_BATCH_1.log ;
Import: Release 11.2.0.4.0 - Production on Thu May 31 14:44:26 2018
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 "CRM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "CRM"."SYS_IMPORT_FULL_01":  crm/********@PR_CC directory=CC_DUMP dumpfile=TMP_BATCH206.dmp logfile=TMP_BATCH_1.log 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "CRM"."TMP_BATCH203"                        7.601 KB       1 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Job "CRM"."SYS_IMPORT_FULL_01" successfully completed at Thu May 31 14:44:27 2018 elapsed 0 00:00:01
artisandb:[/ccexp/cc$]


查看数据:


20180601025449674.png


exclude和include更多用法


相关实践学习
数据库实验室挑战任务-初级任务
本场景介绍如何开通属于你的免费云数据库,在RDS-MySQL中完成对学生成绩的详情查询,执行指定类型SQL。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
相关文章
|
2月前
|
存储 Oracle 关系型数据库
Oracle数据泵:数据迁移的魔法棒
【4月更文挑战第19天】Oracle数据泵是高效的数据迁移工具,提供并行处理、灵活数据过滤、完整数据保留及详细日志记录等功能。相比传统工具,数据泵在性能和易用性上更具优势,能大幅缩短迁移时间,降低业务中断风险。通过掌握其使用技巧,数据管理员可以充分利用数据泵,提升数据管理效率。
|
11月前
|
Oracle 关系型数据库 数据库
通过数据泵expdp、impdp方式备份与还原(导出与导入)Oracle数据库
通过数据泵expdp、impdp方式备份与还原(导出与导入)Oracle数据库
|
Oracle 关系型数据库 数据库
Oracle实例迁移_真实场景实操
Oracle实例迁移_真实场景实操
94 0
|
Oracle 关系型数据库 数据库
Oracle expdp impdp导出导入命令及数据库备份
Oracle expdp impdp导出导入命令及数据库备份
263 0
|
Oracle 关系型数据库 数据库
ORACLE的EXPDP与ORA-31626、ORA-31637、ORA-06512、ORA-31635
    2015年4月24日,上地的增值业务综合网管系统ORACLE的EXPDP又出错了,系统负责人反映从4月20开始到现在该系统的expdp没有备份文件,相关的处理过程如下。
3672 0
|
2天前
|
存储 监控 Oracle
关系型数据库Oracle备份策略建议
【7月更文挑战第21天】
11 6
|
2天前
|
存储 Oracle 关系型数据库
|
3天前
|
监控 Oracle 关系型数据库
关系型数据库Oracle恢复测试
【7月更文挑战第20天】
18 7
|
1天前
|
Oracle 关系型数据库 Unix
关系型数据库Oracle设置环境变量:
【7月更文挑战第22天】
14 4
|
1天前
|
Oracle 关系型数据库 数据库
关系型数据库Oracle执行RMAN脚本
【7月更文挑战第22天】
7 2

推荐镜像

更多