如何提高oracle expd impdp效率
你可以一無所有，但絕不能一無是處。
oracle幾種方案的對比
rman
物理級備份，整個實例包括sys等無用信息，還原時會完全按照原資料庫實例還原，所以也需要準備相同容量的磁碟空間，由於包含了很多系統數據，備份文件大小往往會超過預期。
exp/imp
oracle最早的邏輯備份，可以導出全庫級，用戶級，表級備份，對於GB級數據量，推薦使用，數據量更大一些，存在導出導入時長過大，exp導出時如果是空表不會被導出，出錯後需要重試的情況。
expdp/impdp
從10G開始oraclet提供了數據泵工具，相比exp/imp，數據泵提供了更高級的特性，官方也推薦使用：
可以中斷導出/導入作業，然後恢復它們；
可以重新啟動已失敗的導出和導入作業；
可以重映射對象屬性以修改對象；
可以容易地從另一個會話中監控數據泵的作業，甚至可以在作業過程中修改其屬性；
使用並行技術很容易快速移動大量的數據；
可以使用更強大的可移植表空間特性來快速移植大量的數據，甚至可在不同操作系統平台之間移動。
數據泵
數據泵常用參數
1
文件目錄有關的參數
DIRECTORY
Directory參數指向為轉儲文件和日誌文件使用的目錄對象
DUMPFILE
導出導入時使用的文件名稱，通過指定%U替換變數，可創建多個轉儲文件，替換變數將從01到99，例如exp%U.dmp可以轉儲成exp01.dmp, exp02.dmp, exp03.dmp等文件名。默認的文件參數名expdat.dmp。
需要注意的是如果使用%U想並行創建多個文件，需要制定filesize參數確定文件大小；
FILESIZE
可用位元組，KB,MB,GB為單位指定FILESIZE參數。若不指定，則無限制；
PARFILE
PARFILE參數文件，如同前面介紹的內容。
LOGFILE
指定導出作業的日誌文件
COMPRESSION
默認時，所有被導出的元數據均被壓縮。可以指定參數：
all:允許對整個操作壓縮
data_only：只壓縮數據
metadata_only：只壓縮原數據，這是默認值
none:不壓縮
2
與導出方式有關的參數
full
是否全庫導出
schema
導出哪些schema，逗號隔開
tables
導出哪些表
與導入相關的參數
table_exists_action
skip：默認操作
replace：先drop表，然後創建表，最後插入數據
append：在原來數據的基礎上增加數據
truncate：先truncate，然後再插入數據
remap_schema
映射schema
remap_talbespace
映射新的表空間
3
過濾參數
CONTENT
使用CONTENT參數，可以過濾要寫入導出轉儲文件中的內容。
CONTENT參數可取以下三個值：
ALL:同時導出數據和元數據
DATA_ONLY：只導出數據
METADATA:只導出元數據
EXCLUDE和INCLUDE
EXCLUDE和INCLUDE是兩個對立的參數，可用來過濾元數據,前者是包含哪些範圍，後者表示排除哪些範圍
注意：
使用CONTENT=DATA_ONLY時，不能再使用EXCLUDE和INCLUDE。
EXCLUDE和INCLUDE是相對的兩個參數，不可同時使用
簡單設置EXCLUDE參數，可在導出作業中排除特定的資料庫對象。另外INCLUDE允許只包括一組特定的對象，格式
EXCLUDE=object_type[:name_clause]
INCLUDE=object_type[:name_clause]
如：
EXCLUDE=TABLE:"LIKE "EMP%"" 它排除所有以EMP開頭的表。
EXCLUDE=index排除所有索引
EXCLUDE=SCHEMA:"="HR"" 排除HR整個模式
EXCLUDE =TABLE:"IN (select tablename from excludetable)" 也可以將需要排除的表存起來
EXCLUDE =constraint,index,STATISTICS 排除約束，索引，統計信息
INCLUDE正好和EXCLUDE相反，包含特定的對象，如：
INCLUDE=TABLE:"IN("tableA", "tableB")" 只導出tableA和tableB兩個表
INCLUDE=PROCEDURE 只導出模式中所有的存儲過程
INCLUDE=INDEX:"LIKE "EMP%""只導出以EMP開頭的索引
INCLUDE =constraint,index,STATISTICS 包含約束，索引，統計信息
QUERY
QUERY參數與它在傳統的導出實用程序中作用相同：藉助於SQL語句選擇導出的錶行數據。
QUERY參數允許用一個表名限定SQL語句，如：QUERY=hr.orders:"where order_id > 1000000"
4
加密
ENCRYPTION
ENCRYPTION參數指定在將數據寫到轉儲文件時是否對其加密。
可給ENCRYTION參數指定以下值
ALL:加密所有數據和元數據
DATA_ONLY：只加密數據
ENCRYPTION_COLUMN_ONLY：只加密使用TDE特性的加密列
METADATA_ONLY：只加密元數據
NONE：不進行加密（默認）
注意：
可通過指定ENCRYPTION或ENCRYPTION_PASSWORD參數，或者兩者都指定，實施加密。
如果指定了ENCRYPTION_PASSWORD參數，則ENCRYPTION參數默認ALL。
下面的例子只加密數據
$ expdp hr/hr DIRECROTY=dump_dir DUMPFILE=expdat1.dmp ENCRYPTION=data_only ENCRYPTION_PASSWORD=123456
ENCRYPTION_ALGORIHM
加密演算法，默認為AES128，你也可以指定AES192,AES256
ENCRYPTION_MODE
生成加密密鑰的方法，默認即可，可以不指定
dual:
password:
transparent:
ENCRYPTION_PASSWORD
加密密碼
估算參數
ESTIMATE
將指出新的導出作業將要消耗多少空間，可以指定資料庫使用正在被導出的對象中的資料庫塊（BLOCK）的數量，或者表的優化程序統計數據（STATISTICS）進行空間估算
ESTIMATE=(BLOCKS|STATISTICS), BLOCKS是默認選項
ESTIMATE_ONLY
雖然ESTIMATE參數只在導出作業中生效，但是可以使用ESTIMATE_ONLY參數而不啟動導出作業。
5
與作業有關的參數
JOB_NAME：
可選參數，指出導出作業的名字，若未指定，oracle將默認。此名字與作業主表的名字相同。
STATUS：
長時間作業的時候非常有用。此參數以秒為單位取整數，默認是0，將在必要時顯示新的狀態。如果想每分鐘地確保有關正在運行中數據泵作業的更新狀態，指定STATUS=60
PARALLEL：
PARALLEL是功能強大的參數，允許作業指定一個以上的活動執行線程，工作進程，使用PARALLEL參數意味著作業將使用多線程執行；注意配合FILESIZE使用，1個線程對應1個文件，否則無法多線程並行；
ATTACH:
ATTACH參數將使你的數據泵客戶機會話加入到一個運行的作業中，並使你進入交互的方式，此參數只能與用戶名密碼組合使用。
$ expdp hr/hr ATTACH=job_name
可以使用ATTACH命令或者CTRL+C命令交互地加入作業中，不會停止正在允許的作業。交互命令只停止在屏幕上顯示該作業消息並顯示提示符（export>） 常見的互動式命令
ADD_FILE: 給轉儲文件集合增加一個轉儲文件
CONTINUE_CLIENT：返回登陸方式， 作業將重新啟動
EXIT_CLIENT：退出客戶機會話，並保持作業允許
HELP：提供交換命令的幫助 KILL_JOB: 釋放並刪除作業
PARALLEL: 更改當前作業的活動工作進程數量
START_JOB：啟動並重新恢復當前作業 S
TATUS: 設置作業的監控頻率
STOP_JOB: 順序關閉作業的執行，並退出客戶機
6
參數文件
除了在命令行直接指定導出參數外，還可以將他們放入參數文件中，從而在實際的導出作業中調用該參數文件，如：mytext.txt 這個參數文件
SCHEMA=HR
DIRECTORY=dump_dir
DUMPFILE=expda1.dmp HR
創建參數文件後，為了導出HR模式，所需要做的就是用PARFILE參數調用：
$ expdp PARFILE=mytext.txt
數據泵與exp/imp的區別
用戶映射不同
imp system/passwd fromuser=usera touser=userb ...
impdp system/passwd remap_schema="usera":"userb" ...
表空間映射不同
用exp/imp的時候，要想更改表所在的表空間，需要手工去處理一下，如:
alter table xxx move tablespace_new
用impdp只要用remap_tablespace="tabspace_old":"tablespace_new"
表指定方式不同
當指定一些表的時候，使用exp/imp 時，tables的用法是 tables=("table1","table2","table3")。expdp/impdp的用法是tables="table1","table2","table3"
是否要導出數據行
exp （ROWS=Y 導出數據行，ROWS=N 不導出數據行）expdp content（ALL:對象＋導出數據行，DATA_ONLY：只導出對象，METADATA_ONLY：只導出數據的記錄）
為什麼選擇數據泵？
1.expdp是服務端程序，exp是客戶端程序，exp還需要網路傳輸，這個很影響速度;
2.expdp讀的就是數據塊，exp是要轉換成SQL;
3.expdp可以並行導出數據、元數據和建索引、包，exp不行;
4.expdp採用的是直接路徑讀，exp是要通過SGA;
常用的數據泵操作
1)導出用戶及其對象
expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp directory=dump_dir;
2)導出指定表
expdp scott/tiger@orcl tables=emp,dept dumpfile=expdp.dmp directory=dump_dir;
3)按查詢條件導
expdp scott/tiger@orcl directory=dump_dir dumpfile=expdp.dmp tables=empquery="where deptno=20";
4)按表空間導
expdp system/manager@orcl directory=dump_dir dumpfile=tablespace.dmptablespaces=temp;
5)導整個資料庫
expdp system/manager@orcl directory=dump_dir dumpfile=full.dmp full=y;
impdp導入數據
在正式導入數據前，要先確保要導入的用戶已存在，如果沒有存在，需要創建用戶
1)導入用戶（從用戶scott導入到用戶scott）
impdp scott/tiger@orcl directory=dump_dir dumpfile=expdp.dmp schemas=scott;
2)導入表（從scott用戶中把表dept和emp導入到system用戶中）
impdp system/manager@orcl directory=dump_dir dumpfile=expdp.dmp tables=scott.dept remap_schema=scott:newuser;
3)導入表空間
impdp system/manager@orcl directory=dump_dir dumpfile=tablespace.dmp tablespaces=example;
4)導入資料庫
impdb system/manager@orcl directory=dump_dir dumpfile=full.dmp full=y;
5)追加數據
impdp system/manager@orcl directory=dump_dir dumpfile=expdp.dmp schemas=system table_exists_action=replace
如何提高數據泵效率
資料庫IO優化
非同步IO
如果是同步I/O，當一個I/O操作執行時，應用程序必須等待，直到此I/O執行完。相反，非同步I/O操作在後台運行，I/O操作和應用程序可以同時運行，提高了系統性能；使用非同步I/O會提高I/O流量，像資料庫，文件伺服器等應用往往會利用非同步I/O，使得多個I/O操作同時執行. 而且從官方文檔來看，ORACLE也是推薦ORACLE資料庫啟用非同步IO的這個功能的。
配置非同步IO
a) aio-max-nr
aio-nr是所有當前活動的非同步IO進程上下文的總和，其值最多不能超過aio-max-nr的設定值
echo "fs.aio-max-nr = 3145728" >> /etc/sysctl.conf
b)檢查OS是否已經使用非同步IO
[oracle@localhost ~]$ cat /proc/slabinfo | grep kio
kioctx 37 140 384 10 1 : tunables 54 27 8 : slabdata 14 14 0
kiocb 0 0 256 15 1 : tunables 120 60 8 : slabdata 0 0 0
返回結果中kiocp對應的前兩項為0，說明系統中沒有使用非同步io
c)檢查顯示oracle已經鏈接了aio的包：
d)檢查資料庫參數配置
SQL> show parameter disk_asynch_io
disk_asynch_io boolean TRUE
SQL> show parameter filesystemio_options
filesystemio_options string none
當前使用的是文件系統，因此需要將filesystemio_options設置為asynch，才能開啟非同步io：
SQL> ALTER SYSTEM SET FILESYSTEMIO_OPTIONS = ASYNCH SCOPE = SPFILE;
DB_WRITER_PROCESSES, DBWR_IO_SLAVES建議配置
DB_WRITER_PROCESSES是oracle 數據寫進程數量，每個DBW進程可以對應多個DBWR_IO_SLAVES子進程，DBWR_IO_SLAVES設置成多個可以讓DBW在系統不支持非同步io的情況下模擬非同步io，對這兩個參數的配置建議如下：
如果系統不支持非同步IO，或系統只有1個cpu，建議設置DB_WRITER_PROCESSE為1，同時配置多個DBWR_IO_SLAVES；
如果系統支持非同步IO且有多個cpu，建議設置DB_WRITER_PROCESSE數為cpu/8, 同時關閉DBWR_IO_SLAVES；
下圖為db遇到io瓶頸時對這兩個參數的建議配置；
歸檔
在數據量很大時，如果所屬業務系統支持停機關歸檔，強烈建議關閉歸檔，這樣能避免歸檔引起的IO問題。
redo
配置更多的redo日誌組，同時增加redo日誌容量，已減少redo切換造成的等待；
表空間分離
導入時可以通過exclude，include來分多次導入數據，第一次導入數據，第二次導入索引，統計信息，約束，每次導入使用remap_tablespace的方式轉換表空間，這樣能極大提高導入效率；
數據泵參數
parallel
對於導出來說，由於dump文件只能由一個線程進行操作（包括I/O處理），因此如果輸出的DUMP文件只有一個，即使你指定再多的並行，實際工作仍然是一個，而且還會觸發ORA-39095錯誤。因此，建議設置該參數小於或等於生成的DUMP文件數量。那麼，如何控制生成的DUMP文件數量呢？
EXPDP 命令提供了一個FILESIZE參數，用來指定單個DUMP文件的最大容量，要有效的利用parallel參數，filesize參數必不可少。
舉 例：某用戶對象佔用了4G左右的空間，實際導出後的DUMP文件約為3G，我們嘗試在導出該用戶時指定並行度為4，設置單個文件不超過500M，則語法如下：
$ expdp user/pwd directory=dump_file dumpfile=expdp_20100820_%U.dmp logfile=expdp_20100820.log filesize=500M parallel=4
對於導入的parallel
對於導入來說，使用parallel參數則要簡單的多，我認為導入更能體現parallel參數的優勢。 參數設置為幾，則認為同時將幾張表的內容導入到庫中。
舉例：某dmp文件中包含了200張表，我們嘗試在導入該DMP文件時指定並行度為10，則語法如下：
$ impdp user/pwd directory=dump_file dumpfile=expdp_20100820.dmp logfile=impdp_20100820.log parallel=10
filesize
此參數需要配合parallel使用，算好總導出多少個文件，想利用至少多少個並發算一算。比如
分批次
這裡的分批次即是導出的時候按單個用戶導出，也指導入的時候按數據、索引、約束、統計信息的形式分批次導入
導出的時候按單個用戶導出， 避免所有用戶都導出到一個文件中
導入的時候先導入數據，再導入索引、約束、統計信息
導入數據的時候切換到數據專用的表空間，導入索引的時候使用專門的索引表空間有利於提高系統io
業務相關
業務上需要去著重檢查一下那些無用的表，特別是大表，臨時備份表（如_bak,_20180101類似的），通常來講一個比較舊的系統都逃不過這些無用數據，如果導出的時候能排除掉，將大大加快導出導入時間，同時減少存儲空間佔用。
TAG:DB那些事 |