Oracle-数据泵实操

概述

数据泵的作用：

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

或者 可以查看

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.dmpJob "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 passwordDEFAULT 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 operationORA-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 2016Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "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_DATATotal estimation using BLOCKS method: 2.294 GBProcessing object type DATABASE_EXPORT/TABLESPACE. . exported "ZMC"."NM_ALARM_EVENT"                      317.5 MB  467108 rowsProcessing object type DATABASE_EXPORT/PROFILEProcessing object type DATABASE_EXPORT/SYS_USER/USERProcessing object type DATABASE_EXPORT/SCHEMA/USERProcessing object type DATABASE_EXPORT/ROLEProcessing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANTProcessing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANTProcessing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANTProcessing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLEProcessing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA..............

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

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

全库导入

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

更多详情参考