EXPDP、IMPDP規範及常用技巧總結

一、巧用并行提升速度

在EXPDP/IMPDP過程中，為了提升速度，而使用并行，有人說不就是加個parallel=n參數嘛，但該如何利用該參數做到真正提升速度呢？

1、單表導出導入資料時使用parallel=n參數無效

2、導入（IMPDP）索引過程中是串行的方式，雖然在執行計劃中看到建索引時是帶并行參數，但建議使用sqlfile參數生成語句的方式建索引，避免因建立了結構後，再導入資料，這樣就會變得異常慢。大概步驟如下：

cat >expdp_testdump_20181218.par  <<EOF
userid='/ as sysdba'
directory=datadump
dumpfile=expdp_testdump_20181218_%U.dmp
logfile=expdp_testdump_20181218.log
cluster=no
parallel=2
exclude= index,constraint
EOF
--排除索引和限制，執行導入
nohup impdp parfile=expdp_testdump_20181218.par > expdp_testdump_20181218.par.out &
--通過sqlfile參數生成建立索引語句
cat >impdp_testdump_idx_20181218.par <<EOF
userid='/ as sysdba'
directory=datadump
dumpfile=expdp_testdump_20181218_%U.dmp
sqlfile=impdp_testdump_idx_20181218.sql
logfile=impdp_testdump_idx_20181218.log
cluster=no
parallel=2
tables=scott.testdump
include=index,constraint
EOF
--執行生成建立索引語句（實際并不會導入）
nohup impdp parfile= impdp_testdump_idx_20181218.par > impdp_testdump_idx_20181218.par.out &
--修改建立索引的并行度，并行度建議不超過CPU核數的1.5倍
--LINUX環境使用
sed -i 's/PARALLEL 1/PARALLEL 16/g' impdp_testdump_idx_20181218.sql
--因AIX環境sed沒有-i參數，可以使用如下兩種方法：
perl -pi -e 's/ PARALLEL 1/PARALLEL 16/g' impdp_testdump_idx_20181218.sql
或者
vi impdp_testdump_idx_20181218.sql << EOF
:%s/ PARALLEL 1/PARALLEL 16/g
:wq
EOF
--等導入完資料之後，執行建索引的SQL：
cat create_testdump_index.sh
. ~/.profile
sqlplus / as sysdba <<EOF
set timing on
set echo on
set verify on
spool create_testdump_index.log
@impdp_testdump_idx_20181218.sql
spool off
exit
EOF
--執行建索引的SQL
nohup 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的報錯。那該怎麼避免呢？

通過rowid或者主鍵的方式分批導出（推薦使用rowid）

--建立一個表用于記錄要導出表的rowid，并分批，這裡分成50
create table scott.exp_rowid as select mod(rownum,50) sou_seq,rowid sou_rowid from scott.lobtest;
--根據分批次數生成對應的parfile檔案
cat >expdp_lobtest_20181219_seq0.par <<EOF
userid='/ as sysdba'
directory=datadump
dumpfile=expdp_lobtest_20181219_%U.dmp
logfile=expdp_lobtest_20181219.log
cluster=no
tables=scott.lobtest
query="where rowid in (select sou_rowid from scott.exp_rowid where sou_seq=0)"
EOF
--執行導出
nohup expdp expdp_lobtest_20181219_seq0.par > expdp_lobtest_20181219_seq0.par.out &
…..
nohup expdp expdp_lobtest_20181219_seq50.par > expdp_lobtest_20181219_seq50.par.out &
 
分成50個批次導出,可通過循環生成導出腳本,這裡先不提供循環腳本,後面提供參考      

或者通過如下腳本：

chunk=10
for ((i=0;i<=9;i++));
do
expdp /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 $i
done      

 三、多表串行自動化導出導入

有這樣一種場景，做OGG初始化時，需要導出導入某個業務不同schema下不同的表，如果表數量比較多的情況下，降低導出導入時對庫的壓力，可通過以下方式，進行導出導入。

--提供多表自動循環導入腳本，
cat auto_impdp.sh
while read tab_name
do
   impdp_num=`ps -ef|grep impdp|grep -v grep|grep -v "tail "|wc -l`
   while [ $impdp_num -ge 1 ]
   do
     sleep 1m
     impdp_num=`ps -ef|grep impdp|grep -v grep|grep -v "tail "|wc -l`
   done
   par_file='echo "impdp_"$table_name"_181219.par"'
   log_file='echo "impdp_"$table_name"_181219.log"'
   echo 'userid="/ as sysdba"' >$par_file
   echo "dumpfile=expdp_scott_181219_%U.dmp" >>$par_file
   echo "logfile="$log_file >> $par_file
   echo "exclude=statistics,object_grant,index" >> $par_file
   echo "transform=segment_attributes:n" >> $par_file
   echo "tables=("$tab_name")" >> $par_file
   nohup impdp parfile="$par_file " > " $par_file".out 2>&1 &
done <table_list.txt      

這裡可能應用場景并不多，但對于自動化生成腳本可以提供一個很好的參考。

 四、進度查詢和監控

但上司問你導數進度時，會不會手忙腳亂的，無從查起？當然，作為一個負責任的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   %_COMPLETE
103  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_20181218_01.dmp      

 五、性能診斷技巧

在導出導入過程中，偶爾會遇到一些性能問題，可以會因主機資源，資料庫版本，PSU版本，datapump本身bug等原因導緻。

首先可根據需求判斷是在哪個環節出問題，導出/導入中繼資料還是資料時出現性能問題；是否是導出/導入特定對象遇到性能問題？對于這些問題，MOS上提供了如下3種診斷方法：

方法一：
在expdp/imdp指令中添加參數METRICS=Y TRACE=480300 (或者 480301 捕獲SQL trace) 并重新運作expdp/impdp
方法二：
對DataPump Master (DM) 和 Worker (DW)程序啟用level 12的10046 trace (資料庫版本 >= 11g)
SQL> connect / as sysdba -- 版本>= 11g and < 12c SQL> alter system set events 'sql_trace {process : pname = dw | pname = dm} level=12'; -- 版本= 12c SQL> alter system set events 'sql_trace {process: pname = dw | process: pname = dm} level=12';
然後用參數METRICS = Y加入指令行啟動expdp/impdp
方法三：（推薦）
跟蹤已經開始運作的DataPump 導出程序
SQL> connect / as sysdba  set lines 150 pages 100 numwidth 7  col program for a38  col username for a10  col spid for a7  select to_char (sysdate, 'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid, s.status, s.username, d.job_name, p.spid, s.serial#, p.pid         from   v$session s, v$process p, dba_datapump_sessions d        where  p.addr = s.paddr and s.saddr = d.saddr and             (UPPER (s.program) LIKE '%DM0%' or UPPER (s.program) LIKE '%DW0%');
确認Data Pump Worker SID 和 SERIAL# （例如. 對于 DM 和DW 程序）。  以level 12跟蹤 Master/Worker 程序 (bind和wait資訊)：
SQL> exec sys.dbms_system.set_ev (SID, SERIAL#, 10046, 12, '');
等待一段時間 (至少1 小時) 以捕獲足夠的資訊。
 結束跟蹤：  
SQL> exec sys.dbms_system.set_ev (SID, SERIAL#, 10046, 0, '');      

以上為MOS提供的診斷方法，可靈活的使用，查詢出DataPump程序的SID後可以查詢是否有會話阻塞，異常等待事件是什麼？比如由于“StreamsAQ: enqueue blocked on low memory”等待事件導緻expdp / impdp指令出現嚴重性能問題，是因為Bug 27634991引起的（在版本19.1及更高版本中修複了）。

 六、其它參數技巧

1、巧用括号将參數包含

導入目标庫要求換使用者名和表名，比如：

remap_schema=scott:test

remap_table= t1:t2  --無括号需多個參數

可直接寫成

remap_table=(scott.t1: test.t2)  --正确姿勢

如果沒有括号會怎樣呢？

remap_table= scott.t1: test.t2

. . imported "SCOTT"."TEST.T2"      0 KB       0 rows –可看到導入的還是SCOTT使用者的表

2、  存儲參數的消除

當導入到目标端時，如果表空間不同，需要用remap_tablespace轉換表空間，且源端導出的中繼資料是建立表結構的語句是包含預配置設定段的，此時會占用很多的表空間，可用以下參數消除存儲參數。

transform=segment_attributes:n

使用該參數會将對象段配置設定在導入目标使用者的預設表空間上。

3、預估導數時間

有時想預估導出導入某個對象的時間和消耗，而不想真正導入，可以使用estimate

4、關于壓縮compression

在空間不足的情況下，使用compression=all進行壓縮導入，壓縮比大概是1:6左右，根據導出資料類型的不同會有偏差，使用壓縮，導出/導入時，耗時較長。

5、include/exclude包含選擇性條件時需注意的點

exclude/include參數用法：

exclude=[object_type]:[name_clause],[object_type]:[name_clause]  -->排出特定對象

include=[object_type]:[name_clause],[object_type]:[name_clause]  -->包含特定對象

l  exclude、include參數不能同時使用，這2個參數相斥

l  使用parfile，可以用多個exclude參數，但隻能用一個include參數

l  include、exclude參數中，在escape語句中，不能用\作為轉義符

6、query參數的使用技巧

直接在指令行輸入參數，在linux環境下，所有特殊字元都否要使用\轉義

QUERY=SCOTT.TEST1:\"WHERE create_date \>= TO_DATE\(\'20181219 10:00:00\',\'yyyymmdd hh24:mi:ss\'\)\"      

通過用parfile方式，可以不用加轉義符（推薦）

QUERY=SCOTT.TEST1:"WHERE create_date >= TO_DATE('2018-12-19 10:00:00','yyyy-mm-dd hh24:mi:ss')"      

7、expdp/impdp其它需要注意問題

expdp/impdp是服務端工具，不能導出sys使用者下的對象，隻能通過exp/imp用戶端工具導出導入；對應表空間的建立，表空間大小配置設定，歸檔目錄大小（導入會産生很多歸檔），導入索引時temp表空間的大小，源和目标庫的版本（高進低出），字元集是否一緻，share pool大小等都是需要考慮的點。

七、源和目标導數比對

查詢是否存在報錯:grep –i ora- expdp_testdump_20181219.log

grep ^". . exported " exp_testdump_20181219.log|awk '{print $4,$7}'|sed 's/"//g'|sort –n>1.txt

grep ^". . exported " imp_testdump_20181219.log|awk '{print $4,$7}'|sed 's/"//g'|sort –n>2.txt

comm 1.txt 2.txt  --比對導出導入記錄數是否一緻

源和目标對比導出導入對象數是否一緻

select owner,object_type,count(*) from dba_objects where owner='SCOTT' group by owner,object_type order by 1,2,3;

八、12c的一些新功能

1、 像表一樣導出視圖

views_as_tables參數允許把視圖當成表導出

2、  transform參數的擴充加強

為減少導入期間減少相關日志的産生，可使用transform=disable_archive_logging:y，（包含表和索引級别）導入後再将日志屬性重置為LOGGING。

導入時改變表的LOB存儲：TRANFORM參數的LOB_STORAGE子句使得在進行非可傳輸導入操作時改變表的壓縮特性。TRANSFORM=LOB_STORAGE:[SECUREFILE | BASICFILE| DEFAULT | NO_CHANGE]

導入時改變表壓縮：TRANSFORM參數的TABLE_COMPRESSION_CLAUSE子句允許表導入過程中動态改變表的壓縮特性TRANSFORM=TABLE_COMPRESSION_CLAUSE:[NONE |compression_clause]

3、  導出壓縮可指定壓縮級别

COMPRESSION_ALGORITHM=[BASIC | LOW | MEDIUM |HIGH]

4、  在安全方面，增加了expdp/impdp的審計功能，已經使用加密導出時，加密密碼的增強。

5、資料泵用于PDB和用于非CDB資料庫沒太大差别，要導出/導入某個PDB時，指定對應的就行userid='/ as sysdba@pdb'