超详细的EXPDP、IMPDP规范及常用技巧总结

https://www.toutiao.com/i6727232212850180619/
原创 波波说运维 2019-08-24 00:06:00

概述

Oracle备份方式主要分为数据泵导出备份、热备份与冷备份三种,数据泵导出/导入属于逻辑备份,热备份与冷备份都属于物理备份。oracle10g开始推出了数据泵(expdp /impdp),可以使用并行参数选项,因此,相对于传统的exp命令来说,执行效率更高。

今天主要分享下EXPDP、IMPDP规范和一些技巧。

[转帖]超详细的EXPDP、IMPDP规范及常用技巧总结-LMLPHP


一、并行提升速度

在EXPDP/IMPDP过程中,为了提升速度,而使用并行,有人说不就是加个parallel=n参数嘛,但该如何利用该参数做到真正提升速度呢?

[转帖]超详细的EXPDP、IMPDP规范及常用技巧总结-LMLPHP

1、单表导出导入数据时使用parallel=n参数无效

2、导入(IMPDP)索引过程中是串行的方式,虽然在执行计划中看到建索引时是带并行参数,但建议使用sqlfile参数生成语句的方式建索引,避免因创建了结构后,再导入数据,这样就会变得异常慢。大概步骤如下:

cat >expdp_testdump.par <<EOFuserid='/ as sysdba'directory=datadumpdumpfile=expdp_testdump_%U.dmplogfile=expdp_testdump.logcluster=noparallel=2exclude= index,constraintEOF--排除索引和约束,执行导入nohup impdp parfile=expdp_testdump.par > expdp_testdump.par.out &--通过sqlfile参数生成创建索引语句cat >impdp_testdump_idx.par <<EOFuserid='/ as sysdba'directory=datadumpdumpfile=expdp_testdump_%U.dmpsqlfile=impdp_testdump_idx.sqllogfile=impdp_testdump_idx.logcluster=noparallel=2tables=scott.testdumpinclude=index,constraintEOF--执行生成创建索引语句(实际并不会导入)nohup impdp parfile= impdp_testdump_idx.par > impdp_testdump_idx.par.out &--修改创建索引的并行度,并行度建议不超过CPU核数的1.5倍--LINUX环境使用sed -i 's/PARALLEL 1/PARALLEL 16/g' impdp_testdump_idx.sql--因AIX环境sed没有-i参数,可以使用如下两种方法:perl -pi -e 's/ PARALLEL 1/PARALLEL 16/g' impdp_testdump_idx.sql或者vi impdp_testdump_idx.sql << EOF:%s/ PARALLEL 1/PARALLEL 16/g:wqEOF--等导入完数据之后,执行建索引的SQL:cat create_testdump_index.sh. ~/.profilesqlplus / as sysdba <<EOFset timing onset echo onset verify onspool create_testdump_index.log@impdp_testdump_idx.sqlspool offexitEOF--执行建索引的SQLnohup sh create_testdump_index.sh> create_testdump_index.sh.out &

以上可能看起来简单的事变的复杂,步骤多了,本来一个参数能解决的事(实际也不能解决),这个步骤已经经过多次实践,解决索引不能并行建的问题。

3、在线调整并行度

当导出导入动作已经发起后,发现并行还可以加大,或者需要减少,该怎么办?

expdp \'\/ as sysdba\' attach=SYS_EXPORT_SCHEMA_01

再执行paralele n就可以调整。


二、含LOB大表导出技巧

在导出含LOB字段的大表时,表未分区,并且表大小已过TB,在导出过程中经常遇到因undo表空间大小和undo_retention设置保留时间,导致ORA-01555: snapshot too old的报错。那该怎么避免呢?

[转帖]超详细的EXPDP、IMPDP规范及常用技巧总结-LMLPHP

通过rowid或者主键的方式分批导出(推荐使用rowid)

--创建一个表用于记录要导出表的rowid,并分批,这里分成50create table scott.exp_rowid as select mod(rownum,50) sou_seq,rowid sou_rowid from scott.lobtest;--根据分批次数生成对应的parfile文件cat >expdp_lobtest_seq0.par <<EOFuserid='/ as sysdba'directory=datadumpdumpfile=expdp_lobtest_%U.dmplogfile=expdp_lobtest.logcluster=notables=scott.lobtestquery="where rowid in (select sou_rowid from scott.exp_rowid where sou_seq=0)"EOF--执行导出nohup expdp expdp_lobtest_20181219_seq0.par > expdp_lobtest_seq0.par.out &…..nohup expdp expdp_lobtest_20181219_seq50.par > expdp_lobtest__seq50.par.out &

分成50个批次导出,可通过循环生成导出脚本

或者通过如下脚本:

chunk=10for ((i=0;i<=9;i++));doexpdp /as sysdba TABLES=LOBTEST QUERY=LOBTEST:\"where mod\(dbms_rowid.rowid_block_number\(rowid\)\, ${chunk}\) = ${i}\" directory=DMP dumpfile=lobtest_${i}.dmp logfile= lobtest_${i}.log &echo $idone

三、进度查询和监控

当领导问你导出数据进度时,会不会手忙脚乱的,无从查起?当然,作为一个负责任的DBA,实时的知道导出导入的进度,是必须掌握的技能。

1、 查看数据泵作业的运行情况

select owner_name owr,job_name jbn,operation ope,job_mode jbm,state,degree, attached_sessions atts,datapump_sessions dats from dba_datapump_jobs;select sid, serial#,session_type from v$session s, dba_datapump_sessions d where s.saddr = d.saddr;

2、监控数据泵的逻辑备份程度

SELECT sid, serial#, context, sofar, totalwork, ROUND(sofar/totalwork*100,2) "%_COMPLETE" FROM v$session_longops WHERE totalwork != 0 AND sofar <> totalwork;

SID SERIAL# CONTEXT SOFAR TOTALWORK %_COMPLETE103 89 0 54 7 83.33

3、查看数据泵的详细进度

expdp \'\/ as sysdba\' attach= SYS_IMPORT_TABLE_01

Import> status

Job: SYS_IMPORT_TABLE_01 Operation: IMPORT Mode: TABLE State: EXECUTING Bytes Processed: 1,364,791,288 Percent Done: 99 Current Parallelism: 2 Job Error Count: 0 Dump File: /opt/datadump/expdp_testdump.dmp
05-11 23:03