Oracle逻辑备份（exp,imp,expdp,impdp)

一、exp 导出案例及参数详解

备份方式：
1.物理方法:块拷贝方式，rman
2.逻辑方式:exp|imp expdp|impdp
exp 优势:
①.方便，开发人员经常数据。
②.imp 导入非常方便
③.数据迁移，alert 日志

imp 速度比 impdp慢

exp 导出 4 种模式:
完全、表空间、用户、表
exp 导出的文件的位置：
服务器，本地？
expdp 存储在服务器上面
NFS

简单案例:
测试用户和数据:
导出用户下所有数据:
exp ouzy/ouzy file=ouzy.dmp log=ouzy.log（服务器导出，ORACLE_SID）
exp ouzy/ouzy@192.168.230.100:1521/prod file=ouzy.dmp log=ouzy.log(客户端导出)

字符集问题:
server uses ZHS16GBK character set (possible charset conversion)
1.操作系统
2.数据库字符集
3.客户字符集

参数说明：
exp help=y
buffer
compress（压缩）
contraints
feedback=10 …
full=y 整库
grants 权限
indexes 索引 y
owner
rows y,n 结构
tables
trigger 触发器
direct 直接路径
ignore 忽略错误
statistics
consistent 一致性

导出某个用户:
exp ouzy/ouzy file=ouzy.dmp log=ouzy.log feedback=10 compress=y

使用 A 用户导出 B 用户的数据:
exp system/Oracle123 file=ouzy.dmp log=ouzy.log owner=ouzy

导出结构，不包含数据：
exp ouzy/ouzy file=ouzy.dmp log=ouzy.log rows=n

只导出数据不导出结构？
导出某个用户下的几个表？
exp ouzy/ouzy file=ouzy.dmp log=ouzy.log tables=ftest1
exp ouzy/ouzy file=ouzy.dmp log=ouzy.log tables=ftest1,ftest2
exp ouzy/ouzy file=ouzy.dmp log=ouzy.log tables=f% 导出f 开头的表
exp ouzy/ouzy file=ouzy.dmp log=ouzy.log tables=% 导出所有表

除了某一个表或者几个表，其他全部导出
expdp
段延迟特性（默认true，建议建库后修改为false）
deferred_segment_creation=true
创建新表,不插入数据,不会分配 segment 段
表空间-》段-》区-》块
查看是否分配段：select * from dba_segments t where t.segment_name=‘TEST2’;
手动分配段：alter table ftest3 allocate extent;

导出全库：
exp “/ as sysdba” file=full.dmp full=y （转义：/ as sysdba）

普通用户去导出全库
grant exp_full_database to ouzy;
exp 按用户，表

二、exp导出consistent参数的重要性

consistent参数:导出一致性
tab1—tab2
insert----insert
触发器

exp ouzy/ouzy file=ouzy.dmp log=ouzy.log consistent=y
tab1 1000 scn
tab2 1000 scn

创建2张表：
create table tab1(id int,name varchar2(20));
create table tab2(id int,name varchar2(20));

创建触发器，使tab1插入数据，同时写入tab2中：

CREATE OR REPLACE TRIGGER fx.TG_TEST
BEFORE INSERT ON fx.TAB1
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
insert into tab2 (id, name) values (:New.id, :New.name);
END tg_test;
/

自动插入数据:

begin
for i in 1 .. 100000 loop
insert into tab1 values(100,'fanxin');
commit;
end loop;
end;
/

缺点：
此参数会占用undo,数据太大,会造成undo空间不足的问题,
生产环境根据实际情况指定这个参数
ouzy 非常多的表 scn

三、如何配置生产环境exp备份及定时自动导出策略

RMAN物理备份（恢复数据文件等）
exp逻辑备份，误删数据，表，备份时间点（便于恢复）

生产环境是全库exp还是用户exp，还是表exp?
日志表

crontab（定时任务）
https://www.cnblogs.com/whatstone/p/10075037.html

*　　*　　*　　*　　*　　command
分　时　日　月　周　命令
第1列表示分钟1～59 每分钟用*或者 */1表示
第2列表示小时1～23（0表示0点）
第3列表示日期1～31
第4列表示月份1～12
第5列标识号星期0～6（0表示星期天）
第6列要运行的命令

每天晚上3点自动运行
0 3 * * * /u01/exp.sh > /dev/null 2>&1
每分钟执行：
*/1 * * * * /u01/exp.sh > /dev/null 2>&1

crontab定时任务查看：
[oracle@ouzy u01]$ crontab -l
crontab编辑：
[oracle@ouzy u01]$ crontab -e

数据库服务器A 部署
服务器B 部署

创建目录：
[oracle@ouzy ~]$ mkdir -p /u01/expbackup
授权可执行：
[oracle@ouzy u01]$ chmod 777 exp.sh

.sh文件：

#!/bin/sh
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1
export PATH=$PATH:$ORACLE_HOME/bin
export NLS_LANG=American_America.zhs16gbk
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

EXP_FILE="ortest_test_`date +%Y%m%d%H%M%S`.dmp"
LOG_FILE="${EXP_FILE}.log"
cd /u01/expbackup
find . -ctime +7 -exec rm -rf {} \;
if [ -f $EXP_FILE ]; then
        rm -f $EXP_FILE*
fi
exp ouzy/ouzy@127.0.0.1:1521/prod file=$EXP_FILE log=$LOG_FILE CONSISTENT=y
gzip -9 /u01/expbackup/$EXP_FILE

四、Oracle和txt-excel等数据的相互导入导出

exp imp
使用toad工具

五、imp导入案例及参数详解

exp导出,imp导入
导入用户:
exp ouzy/ouzy file=ouzy.dmp log=ouzy.log
exp ouzy/ouzy@ip:1521/prod file=ouzy.dmp log=ouzy.log

SQL> create user ouzy identified by ouzy;
SQL> grant connect,resource to ouzy;
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

导入：imp ouzy/ouzy file=ouzy.dmp log=ouzy.log

只导入结构:（rows=n）
imp ouzy/ouzy rows=n file=ouzy.dmp log=ouzy.log
只导入数据:（data_only=y）
imp ouzy/ouzy data_only=y file=ouzy.dmp log=ouzy.log
前提:ouzy存在。

导入的用户相同，表空间不同。
exp:ouzy,users
imp:ouzy,users2
删除用户：drop user ouzy cascade;
创建表空间：create tablespace users2 datafile ‘/u01/app/oracle/oradata/prod/t01.dbf’ size 50m;
创建用户：create user ouzy identified by ouzy default tablespace users2;
授权：grant connect,resource to ouzy;
imp ouzy/ouzy file=ouzy.dmp log=ouzy.log
结论:表还是存储在以前的表空间里面。
默认表空间，users

ouzy exp users1
删除原来的表空间：drop tablespace users1;
ouzy users2
imp ouzy/ouzy file=ouzy.dmp log=ouzy.log
结论:表存储在现有的表空间里面。

总结:
实际生产操作过程:
提前建立好用户，表空间建立，提前分配好空间
undo表空间，temp临时表空间。
buffer
ouzy导出，ouzy导入
ouzy2
imp ouzy2/ouzy2 file=ouzy.dmp
以前dmp文件是用什么用户导出的

imp ouzy2/ouzy2 file=ouzy.dmp fromuser=ouzy touser=ouzy
imp ouzy2/ouzy2 file=ouzy.dmp fromuser=ouzy touser=ouzy2
imp ouzy2/ouzy2 file=ouzy.dmp fromuser=ouzy touser=ouzy2 buffer=1000000 data_only=y
imp ouzy2/ouzy2 file=ouzy.dmp fromuser=ouzy touser=ouzy2 buffer=1000000 data_only=y FEEDB
ACK=5000
sys导入，全库导入，sys,system（数据字典）
full=y
导出full=y
按用户导入。

六、imp 跨版本导入问题

1.10g 导出的 dmp 文件可以用 11g 导入
2.11g 导出的 dmp 文件不能用 10 导入

解决办法:
1.网络中转
2.软件修改版本
3.dd 配合 uedit 工具（UltraEdit），AlxcTools.exe修改

七、expdp 导出参数讲解及 Directory 创建

Oracle 备份方式:
物理方式:rman
逻辑方式:exp-imp/expdp-impdp
参数:
content {ALL | DATA_ONLY | METADATA_ONLY}
directory
dumpfile
logfile
full=y|n
schemas（指定用户）
tables
version
flashback_scn （保证数据导出的一致性）
参数:
deferred_segment_creation（段延迟特性，模式是ture，一般修改为false）
true false

建立物理路径：mkdir dump（Oracle用户创建）
oracle建立目录并授权:
create directory mydump as ‘/u01/dump’;
grant read,write on directory mydump to ouzy;

相关视图:dba_directories

八、expdp导出案例及参数详解

8.1案例

表空间导出

expdp ouzy/ouzy directory=mydump dumpfile=ouzy2.dmp tablespaces=users

导出某用户:

expdp ouzy/ouzy directory=mydump dumpfile=ouzy.dmp logfile=ouzy.log
expdp system/Oracle123 directory=mydump dumpfile=ouzy2.dmp schemas=ouzy logfile=ouzy2.log

查看 job:
dba_datapump_jobs

用 SYS 导出全库:
expdp “/ as sysdba” directory=mydump dumpfile=full.dmp full=y

用普通用户导出全库:
grant datapump_exp_full_database to begin;
expdp begin/begin directory=mydump dumpfile=full.dmp full=y

相关权限:
datapump_exp_full_database
datapump_imp_full_database

导出表:

expdp begin/begin directory=mydump dumpfile=begin.dmp tables= test,test3
expdp system/Oracle123 directory=mydump dumpfile=ouzy2.dmp tables=ouzy.t% logfile=ouzy2.log
expdp ouzy/ouzy directory=mydump dumpfile=ouzy.dmp exclude=table:"in ('TEST','TEST')"（排除某些表导出）

导出结构:

expdp begin/begin directory=mydump dumpfile=begin.dmp content=metadata_only

指定版本导出:

expdp ouzy/ouzy directory=mydump dumpfile=ouzy2.dmp version=10.2

8.2、expdp 数据一致性导出

保证导出数据的一致性:
exp 用 consistent=Y 参数
expdp 用 flashback_scn 或者 flashback_time=sysdate 参数

expdp ouzy/ouzy directory=mydump dumpfile=ouzy.dmp logfile=ouzy.log flashback_time=sysdate

触发器：表1插入数据，将同时向表2中插入数据

CREATE OR REPLACE TRIGGER OUZY.TG_TEST
BEFORE INSERT ON OUZY.T1
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
insert into t2 (id, name) values (:New.id, :New.name);
END tg_test;
/

自动插入数据脚本语句：

begin
for i in 1 .. 500000 loop
insert into t1 values(100,'testtest');
commit;
end loop;
end;
/

8.3 配置自动 expdp 导出备份

每天晚上 3 点自动运行
0 3 * * * /u01/expdp.sh > /dev/null 2>&1
1.编辑任务脚本expdp.sh内容

#!/bin/sh
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1
export ORACLE_SID=prod
export PATH=$PATH:$ORACLE_HOME/bin
export NLS_LANG=American_America.zhs16gbk
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

EXP_FILE="ortest_begin_`date +%Y%m%d%H%M%S`.dmp"
LOG_FILE="${EXP_FILE}.log"
cd /u01/dump
find . -ctime +7 -exec rm -rf {} \;
if [ -f $EXP_FILE ]; then
        rm -f $EXP_FILE*
fi
expdp ouzy/ouzy directory=mydump dumpfile=$EXP_FILE logfile=$LOG_FILE flashback_time=sysdate
gzip -9 /u01/dump/$EXP_FILE

2.拷贝任务脚本expdp.sh到/u01/dump，并添加可执行权限
[oracle@ouzy dump]$ chmod a+x expdp.sh

3.配置定时任务
0 3 * * * /u01/expdp.sh > /dev/null 2>&1
[oracle@ouzy dump]$ crontab -l（查看）
[oracle@ouzy dump]$ crontab -e（配置）

九、impdp 导入案例演示

授权：grant read,write on directory mydump to ouzy;

导入表:

impdp ouzy/ouzy directory=mydump dumpfile=ouzy.dmp tables=t1
impdp ouzy/ouzy directory=mydump dumpfile=ouzy.dmp tables=t1 content=metadata_only
impdp ouzy/ouzy directory=mydump dumpfile=ouzy.dmp tables=t1 content=data_only

导入用户:

impdp ouzy/ouzy directory=mydump dumpfile=ouzy.dmp
impdp ouzy2/ouzy2 directory=mydump dumpfile=ouzy.dmp remap_schema=ouzy:ouzy2
impdp ouzy2/ouzy2 directory=mydump dumpfile=ouzy.dmp tables=ouzy.t1 content=data_only

十、处理数据泵 job 后台进程

1）Ctrl+C 组合键：在执行过程中，可以按 Ctrl+C 组合键退出当前模
式，退出之后，导出操作不会停止
2）Export> status --查看当前 JOB 的状态及相关信息
3）Export> stop_job --暂停 JOB（暂停 job 后会退出 expor 模式）
4）重新进入 export 模式下:
expdp begin/begin attach=begin.SYS_EXPORT_SCHEMA_01
5）Export> start_job --打开暂停的 JOB
6）Export> kill_job --取消当前的 JOB 并释放相关客户会话（将 job 删
除同时删除 dmp 文件）
7）Export> exit --通过此命令退出 export 模式（通过 4）可再进入
export 模式下）

十一、使用 480300 事件对 expdp 进行跟踪

1️⃣ 在 expdp/impdp 命令后加上一个 trace 参数,该参数由一个 7 位的16 进制数据组成。
2️⃣ 前三位指定 Data Pump 组件的代码，后四位一般是 0300。
3️⃣ 在使用 trace 参数时，执行数据泵操作的用户需要具有 DBA 角色或者 EXP_FULL_DATABASE /IMP_FULL_DATABASE 的角色（SQL> grant dba to ouzy2;）

expdp ouzy2/ouzy2 DIRECTORY=mydump DUMPFILE=ouzy.dmp LOGFILE=ouzy.log TABLES=t1 TRACE=480300

路径：[oracle@ouzy trace]$ pwd
/u01/app/oracle/diag/rdbms/prod/prod/trace

查看：[oracle@ouzy trace]$ less prod_dm00_9313.trc

80300 to trace Master Control Process (MCP)
400300 to trace Worker process(es)
Master Process trace file: dm<process_id>.trc
Worker Process trace file: dw<process_id>.trc


对别的 session 做 10046 跟踪,sql_trace 跟踪需要用到 dbms_system 这个包

select a.session_type,b.sid,b.serial# from dba_datapump_sessions a,v$session b where a.saddr=b.SADDR;

exec dbms_system.set_ev(159,15,10046,4,’’);
10046 级别只有 1，4，8，12

在数据泵卡住的时候，有一个很好的方法来判断：
1.观察 dump 文件的变化，只要 dump 文件大小在变化，那就说明expdp 是正常的。
2.在 impdp 的时候，查看表空间的变化，只要表空间在变化，说明impdp 是正常的。