一 exp 导出案例及参数详解
1.1 案例
(1)优势: 1. 方便 2. 数据迁移, alert 日志 3.imp 速度比 impdp 慢
(2)四种模式: 1. 完全 2. 表空间 3. 用户 4. 表
(3)导出位置: 在哪操作保存到哪里
(4)导出用户下所有数据
[1]服务端: exp lzh/lzh file=lzh.dmp log=lzh.log // 导出数据和日志到当前目录
[2]客户端: exp lzh/[email protected]:1521/prod file=lzh.dmp log=lzh.log
(5)字符集问题
1.2 exp 参数
(1)exp help=y // 查看
| buffer：快 | full=y：整库 | indexes：索引 |
|---|---|---|
| compress：压缩 | feedback=10 反馈一点 | owner：用户 |
| contraints：约束 | grants：权限 | row y,n:n 只导出结构 |
| table：只导表 | trigger：触发器 | direct：直接路径 |
| ignore：忽略错误 | statistics：统计信息 | consistent：一致性 |
(2)案例(选择部分表)
exp lzh/lzh file=lzh.dmp log=lzh.log feedback=10 compress=y // 导出某个用户
exp sys/PCWLWLKQ file=lzh.dmp log=lzh.log owner=lzh //A 用户导出 B 用户
exp lzh/lzh file=lzh.dmp log=lzh.log rows=n // 导出结构
exp lzh/lzh file=lzh.dmp log=lzh.log tables=test02 // 导出 test 表
exp lzh/lzh file=lzh01.dmp log=lzh01.log tables=test02,test02_new // 导出多表
exp lzh/lzh file=lzh.dmp log=lzh.log tables=f% // 导出 f 开头的表
exp lzh/lzh file=lzh.dmp log=lzh.log tables=% // 导出所有表
1.3 段延迟特性
(1)特性
show parameter deferred_segment_creation
deferred_segment_creation=true; // 段延时默认为 true, 对于新表, 不导出, 不报错
select * from dba_segments t where t.segment_name='TEST02'; // 查询是否分配段
创建新表, 不插入数据时, 不会分配段
表空间 ->段 ->区 ->块
(2)alter table test03 allocate extent; // 手工分配段
(3)exp \"/ as sysdba \" file=full.dmp full=y // 导出全库
1.4 exp 导出 consistent 参数的重要性
(1)导出一致性
exp lzh/lzh file=lzh.dmp log=lzh.log consistent=y
(2)通过 scn 来识别
table1 10000 scn
table2 10000 scn
(3)此参数会占用 undo, 数据太大, 会造成 undo 空间不足问题
生产环境根据实际情况指定这个参数
1.5 配置生产环境 exp 备份及定时自动导出策略
(1)exp.sh
#!/bin/sh
export ORACLE_HOME=/u01/app/Pluto/product/12.2.0/dbhome_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/app/expbackup
find . -ctime +7 -exec rm -rf {} \;
if [ -f $EXP_FILE ]; then
rm -f $EXP_FILE*
fi
exp lzh/[email protected]
:1521/prod file=$EXP_FILE log=$LOG_FILE CONSISTENT=y
gzip -9 /u01/expbackup/$EXP_FILE
(2)0 3 * * */u01/App/exp.sh> /dev/null 2> &1
1.6 oracle 和 txt-Excel 等数据相互导入导出
二 imp 导入案例及参数详解
(1)imp lzh/lzh file=lzh.dmp log=fx.log //
SELECT * FROM dba_users where username='LZH'; // 查看用户表空间
imp help=y; // 查看参数
(2)imp lzh/lzh row=n file=lzh.dmp log=lzh.log // 只导入表结构
imp lzh/lzh data_only=y file=lzh.dmp log=lzh.log // 只导入数据
(3)导入用户相同, 表空间不同, 会还原到用户默认的表空间
(4)查看 dmp 文件是由哪个用户导出的? 导入后查看报错信息
(5)导入到不同用户
imp sys/sys file=lzh.dmp fromuser=lzh touser=lzh // 导出 lzh 导入: lzh
imp sys/sys file=lzh.dmp fromuser=lzh touser=fx // 导出 lzh 导入 fx
(6)buffer
imp lzh/lzh file=lzh.dmp buffer=1000 data_only=y
imp lzh/lzh file=lzh.dmp buffer=1000 data_only=y feedback=5000
//1000 字节, 5000 分段
(7)建议按用户导入
(8)imp 跨版本导入问题
不能由高版本向低版本导入
[1]网络中转
[2]软件修改版本
(9)注意
[1]数据被删除表存在的时候, 需要参数只导入表数据
imp lzh/lzh data_only=y file=fx.dmp full=y
三 expdp 导出参数详解
3.1 expdp 导出参数讲解及 Directory 创建
(1)参数
content: 指定数据库
directory: 用于转储文件和日志文件的目录对象
dumpfile: 转储文件名
logfile: 日志文件名
full=y: 全库导出
schemas: 要导出的表空间的列表
version: 要导出对象的版本
table_exists_action:
skip 是如果已存在表, 则跳过并处理下一个对象;
append 是为表增加数据;
truncate 是截断表, 然后为其增加新数据;
replace 是删除已存在表, 重新建表并追加数据;
remap_schema: 导出表空间名: 导入表空间名
remap_tablespace: 导出用户名: 导入用户名
(2)段延时
flashback_segment_creation: 段延时设为 false
alter system set deferred_segment_craetion=false scope=both;
(3)建立目录并授权
create directory mydump as '/u01/app/mydump';
grant read,write on directory mydump to lzh;
相关视图: dba_directories
3.2 expdp 导出案例演示
(1)expdp lzh/lzh directory=mydump dumpfile=lzh.dmp // 导出某用户
expdp qdcwgl/[email protected]:1521/orcl dumpfile=qdcwgl.dmp
expdp sys/sys directory=mydump dumpfile=lzh01.dmp schemas=lzh // 指定用户
(2)查看 job:select * from dba_datapump_jobs
(3)expdp "/ as sysdba " director=mydump dumpfile=full.dmp full=y // 导出全库
(4)普通用户导出全库
[1]grant datapump_exp_full_database to lzh // 赋权
[2]expdp lzh/lzh directory=mydump dumpfile=full.dmp full=y
(5)权限
datapump_exp_full_database // 导出全库权限
datapump_imp_full_database // 导入全库权限
(6)expdp lzh/lzh directory=mydump dumpfile=lzh.dmp tables=test03; // 按表导出
expdp lzh/lzh directory=mydump dumpfile=lzh.dmp tables=lzh.test%; //test 开头
(7)expdp lzh/lzh directory=mydump dumpfile=lzh.dmp exclude=table:"in('TEST03')"
// 排除 test03 表, 注意要大写
(8)expdp lzh/lzh directory=mydump dumpfile=lzh.dmp content=metadata_only;
// 仅导出表结构
(9)expdp lzh/lzh directory=mydump dumpfile=lzh.dmp version=10.2 // 指定版本导出
3.3 expdp 数据一致性导出
exp 用 consistent=Y 参数
expdp 用 flashback_time=sysdate 参数
expdp lzh/lzh directory=mydump dumpfile=lzh.dmp logfile=lzh.log flashback_time=sysdate
例:
触发器
create or replace trigger test.tg_test
before
insert on test.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..50000 loop
insert into test01 values(‘test’);
commit;
end loop;
end;
/
3.4 配置自动 expdp 导出备份
(1)expdp.sh
#!/bin/sh
export ORACLE_SID=prod
export ORACLE_HOME=/u01/app/Pluto/product/12.2.0/dbhome_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="prod_begin_`date
+%Y%m%d%H%M%S`.dmp"
LOG_FILE="${EXP_FILE}.log"
cd /u01/app/dump
find . -ctime +7 -exec rm -rf {} \;
if [ -f $EXP_FILE ]; then
rm -f $EXP_FILE*
fi
expdp test/test directory=mydump
dumpfile=$EXP_FILE logfile=$LOG_FILE flashback_time=sysdate
gzip -9 /u01/app/dump/$EXP_FILE
(2)crontab
0 3 * * */u01/App/mydump/expdp.sh> /dev/null 2>&1
四 impdp 导入
4.1 impdp 导入案例
(1)impdp lzh/lzh directory=mydump dumpfile=lzh.dmp tables=test01
// 指定导入表
(2)impdp lzh/lzh directory=mydump dumpfile=lzh.dmp content=metadata_only
// 只导入表结构
(3)impdp lzh/lzh directory=mydump dumpfile=lzh.dmp content=data_only
// 只导入数据
(4)impdp lzh/lzh directory=mydump dumpfile=lzh.dmp // 导入 lzh 用户数据
(5)impdp lzh/lzh directory=mydump dumpfile=lzh.dmp remap_schema=lzh:system
//lzh 导出, 导入到 system
(6)impdp QDZYCWXT/QDZYCWXT dumpfile=qdcwgl.dmp remap_tablespace=
qdcwgl:QDZYCWXT remap_schema=qdcwgl:QDZYCWXT // 不同用户, 不同表空间
五 处理数据泵 job 后台进程
(1)ctrl+c 组合键: 在执行过程中, ctrl+c 退出当前模式, 导出操作不会停止
(2)export>status -- 查看当前 job 的状态及相关信息
(3)export>stop_job -- 暂停 job(会退出 export 模式)
(4)重新进入 export 模式
expdp lzh/lzh attach=lzh.SYS_EXPORT_SCHEMA_01
(5)export>start_job -- 打开暂停的 job
(6)export>kill_job -- 取消当前的 job 并释放相关客户会话(将 job 删除同时删除 dmp 文件)
(7)export>exit -- 退出 export 模式
Oracle 逻辑备份参数实例讲解(exp,expdp)
