oracle expdp impdp总结

来源：互联网发布：视频图像分析算法编辑：程序博客网时间：2024/05/21 10:28

经常使用数据泵导数据，一直以来都是零零散散的问题，今天趁周末来个详细的总结

使用EXPDP和IMPDP时应该注意的事项：

EXP和IMP是客户端工具程序，它们既可以在客户端使用，也可以在服务端使用。

EXPDP和IMPDP是服务端的工具程序，他们只能在ORACLE服务端使用，不能在客户端使用。

IMP只适用于EXP导出的文件，不适用于EXPDP导出文件；IMPDP只适用于EXPDP导出的文件，而不适用于EXP导出文件。

范例：

expdp \'/ as sysdba\' directory=DATA_PUMP_DIR schemas=BIPT,KETTLE,EMBD dumpfile=all.dmp logfile=all.log content=data_only flashback_scn=57175227

解析：

这里导入用户我们用到了sysdba 系统管理员所以有权限在DATA_PUMP_DIR 逻辑目录操作，我们也可以使用普通用户

例如：

create directory fafa_dp as '/opt/app/oracle/oradata/dump';

给fafa的导出用户该逻辑目录的权限

Grant read,write on directory fafa_dp to fafa;

这里我可以查看一下数据库的逻辑目录，以上就用到了数据库自带的DATA_PUMP_DIR 逻辑目录

SQL> col OWNER for a10

SQL> col DIRECTORY_NAME for a30

SQL> col DIRECTORY_PATH for a100

SQL> set linesize 500 pagesize 500

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH

---------- ------------------------------ ----------------------------------------------------------------------------------------------------

SYS XMLDIR /opt/app/oracle/product/11.2.0/dbhome_1/rdbms/xml

SYS ORACLE_OCM_CONFIG_DIR /opt/app/oracle/product/11.2.0/dbhome_1/ccr/hosts/bd-dev-oracle1-168/state

SYS DATA_PUMP_DIR /opt/app/oracle/admin/BDCFG/dpdump/

SYS ORACLE_OCM_CONFIG_DIR2 /opt/app/oracle/product/11.2.0/dbhome_1/ccr/state

这里content有三种模式

1) ALL : 导出/导入对象定义和数据，该参数的默认值就是ALL

2) DATA_ONLY : 只导出/导入数据。

3) METADATA_ONLY : 只导出/导入对象定义。即表结构

这里的flashback_scn参数可以指定导出的时间点，这个在ogg初始化用的较多。

闲话不多说，现在重点讲导出会出现问题

问题1：

导出的数据很大，以至于网络有时候中断，不知道导出是否完成的方法

EXPDP/IMPDP相比传统的exp/imp的最本质区别在于服务器端执行，客户端发出指定后，通过API启动服务器的备份job，在执行过程中，可以拿下Ctrl+C组合键，退出当前交互模式，退出之后，导出操作不会停止。此外，退出交互之后，用ps -ef |grep expdp在服务器端是看不到expdp进程。

例如：

expdp \'/ as sysdba\' directory=DATA_PUMP_DIR schemas=KETTLE dumpfile=fafa.dmp logfile=fafa.log job_name=fafajob

然后

^C（即Ctrl+C）

退出之后，要重新查看进程，执行如下命令可以附加job，查看备份状态。

expdp \'/ as sysdba\' attach=fafajob

Export: Release 11.2.0.4.0 - Production on Sat Jul 23 16:32:25 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

Job: FAFAJOB

Owner: SYS

Operation: EXPORT

Creator Privs: TRUE

GUID: 384A61BB834728BDE053A83910ACED87

Start Time: Saturday, 23 July, 2016 16:31:47

Mode: SCHEMA

Instance: BDCFG

Max Parallelism: 1

EXPORT Job Parameters:

Parameter Name Parameter Value:

CLIENT_COMMAND "/******** AS SYSDBA" directory=DATA_PUMP_DIR schemas=KETTLE dumpfile=fafa.dmp logfile=fafa.log job_name=fafajob

State: COMPLETING

Bytes Processed: 44,375,457

Percent Done: 100

Current Parallelism: 1

Job Error Count: 0

Dump File: /opt/app/oracle/admin/BDCFG/dpdump/fafa.dmp

bytes written: 44,867,584

Worker 1 Status:

Process Name: DW00

State: WORK WAITING

此时说明这个导出进程仍然存在，若不存在则会报进程不存在的错误。

上述Ctrl+C组合键以后，我们执行下面操作，就可以重新查看运行状态，但是此时expdp是正在运行的。

Export> start_job

命令说明：

START_JOB 启动/恢复当前作业。

START_JOB=SKIP_CURRENT 在开始作业之前将跳过作业停止时执行的任意操作。

STOP_JOB 顺序关闭执行的作业并退出客户机。选择yes

STOP_JOB=IMMEDIATE 将立即关闭数据泵作业。

首先查看进程状态，当正在进行时

SQL> select * from DBA_DATAPUMP_SESSIONS;

OWNER_NAME JOB_NAME INST_ID SADDR SESSION_TYPE

------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ---------- ---------------- ------------------------------------------

SYS FAFAJOB 1 000000009F7742E0 DBMS_DATAPUMP

SYS FAFAJOB 1 000000009F6C91E0 MASTER

SYS FAFAJOB 1 000000009F06AFE0 WORKER

SQL> select * from dba_datapump_jobs;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS

-----------------------------------------------------------------------------------------------------------------------------------------------------------

SYS FAFAJOB EXPORT SCHEMA EXECUTING

问题2：

导入表空间对应失败问题

以下语句查询DATAPROD用户下的表空间

select username,default_tablespace,temporary_tablespace from dba_users where username = 'DATAPROD';

然后在看看是否需要remap_tablespace，然而实际情况是导入很多情况是，原库为索引专门建立了表空间，而目标库没有而失败。

impdp \'/ as sysdba\' directory=DATA_PUMP_DIR remap_schema=KETTLE:BDMP dumpfile=all.dmp logfile=all.log TABLE_EXISTS_ACTION=truncate

稍微讲解一下TABLE_EXISTS_ACTION，IMPDP完成数据库导入时，如遇到表已存在时，Oracle提供给我们如下四种处理方式：

a.忽略（SKIP，默认行为）；

b.在原有数据基础上继续增加（APPEND）；

c.先DROP表，然后创建表，最后完成数据插入（REPLACE）；

d.先TRUNCATE，再完成数据插入（TRUNCATE）。

当碰到原库索引表空间问题的时候，我们可以在impdp的报错中看出，给原库索引表空间remap_tablespace一个表空间即可。

问题3：

触发器问题

1、从schema1中导入到schema2中，sequence的值会发生变化，但是变化是在导出过程中产生的，在导入时会把变化值导入到schema2中。完成后两个schema的值还是一致的。

2、从schema1中导出，然后导入schema1中（table_exists_action=truncate)，oracle10g在导入（注意只是impdp时不变，expdp会发生变化的）前后不会发生变化，而oracle11g环境中是会发生变化的。

3、触发器禁用与否对sequence没有影响。

4、sequence值具体什么原因产生变化的，本人目前没时间没做细致研究，从操作看貌似10g没有同步trigger而11g有。

0 0