经常使用数据泵导数据,一直以来都是零零散散的问题,今天趁周末来个详细的总结
使用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
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, 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有。