使用expdp(非本地)遠程匯出資料

使用expdp(非本地)遠程匯出資料

背景：

前段時間，需要從異地一個測試資料庫中將測試資料(一張表)匯入本地庫，表資料量大約500萬，欄位160多個，開始用了exp/imp方式，速度奇慢，不能忍，於是轉而使用expdp/impdp方式。

expdp/impd介紹：

從10g開始，除了傳統的exp/imp匯入匯出工具外，Oracle提供了expdp/impdp的資料泵匯入匯出工具。

從官方文檔上看，Oracle資料泵由三部分組成：

>The command-line clients, expdp and impdp

>The DBMS_DATAPUMP PL/SQL package (also known as the Data Pump API)
 

>The DBMS_METADATA PL/SQL package (also known as the Metadata API)
 

The Data Pump clients, expdp and impdp, invoke the Data Pump Export utility and Data Pump Import utility, respectively.
資料泵用戶端(expdp/impdp)，分別會調用資料泵Data Pump Export/Import Utility應用工具。

The expdp and impdp clients use the procedures provided in the DBMS_DATAPUMP PL/SQL package to execute export and import commands, using the parameters entered at the command line. These parameters enable the exporting and importing of data and metadata for a complete database or for subsets of a database.
expdp/impdp用戶端使用由DBMS_DATAPUMP PL/SQL包提供的預存程序來執行export/import命令，並且可以在命令列中添加參數，這些參數可以匯入匯出資料庫中的資料和中繼資料或其中的一部分。

When metadata is moved, Data Pump uses functionality provided by the DBMS_METADATA PL/SQL package. The DBMS_METADATA package provides a centralized facility for the extraction, manipulation, and re-creation of dictionary metadata.

如果需要匯入匯出中繼資料，資料泵會使用DBMS_METADATA PL/SQL包提供的函數。DBMS_METADATA包會提供便捷的方法，用於抽取、控制和重建資料字典中繼資料。

The DBMS_DATAPUMP and DBMS_METADATA PL/SQL packages can be used independently of the Data Pump clients.
 

DBMS_DATAPUMP和DBMS_METADATA的PL/SQL包可以獨立於資料泵用戶端使用。

All Data Pump Export and Import processing, including the reading and writing of dump files, is done on the system (server) selected by the specified database connect string. This means that for unprivileged users, the database administrator (DBA) must create directory objects for the Data Pump files that are read and written on that server file system. (For security reasons, DBAs must ensure that only approved users are allowed access to directory objects.) For privileged users, a default directory object is available. See "Default Locations for Dump, Log, and SQL Files" for more information about directory objects.
 

所有的資料泵匯入匯出過程，包括讀取和寫入的dump檔案，都在資料庫伺服器(server)上。這意味著對於非授權使用者，DBA必須為資料泵檔案建立伺服器端可讀寫的目錄對象，處於安全考慮，DBA必須確保只有授權使用者可以訪問這些目錄對象。對於授權使用者，可以使用預設的目錄對象。

總結：

資料泵工具expdp/impdp工具，執行時其實是調用的兩個PL/SQL包(一個對應資料，一個對應中繼資料)，讀取和寫入的dump檔案儲存體於伺服器上directory目錄對應的檔案夾中。

問題來了，如果沒有伺服器的帳號，即使可以匯出，但如何擷取到匯出的dump呢？

現在的需求是，從本機伺服器使用expdp從遠程伺服器匯出一張表的資料，然後使用impdp匯入到本地庫，且沒有遠程伺服器的登入帳號。

一個遠端資料表的匯入匯出問題，當然這裡使用exp/imp完全可以做到，現在看看expdp/impdp如何做。

解決方案：通過dblink實現遠端資料的匯入匯出。

1. 本地庫建立dblink指向遠程庫。

create database link gf_local connect to username identified by password using 'tnsname';

2. 本地庫建立directory目錄對象。

create directory expdp_dir as '/home/oracle11g/expdp_dir/';

3. 本地庫建立資料泵匯出參數檔案。

vi exp.par：

userid=username/password  //本地庫的使用者名稱密碼，可用上面建立的dblink和directory目錄。

directory=expdp_dir  //本地庫建立的directory目錄。

dumpfile=gf_expdp.dump  //dump檔案名稱。

logfile=gf_expdp.log  //記錄檔名。

tables=depkf15.t_cp  //要匯出的表。

query='"where rownum<1000001"' //匯出條件，前100萬行。

network_link=gf_local  //dblink名。

注意，這裡有個小問題值得注意，tables=depkf15.t_cp，如果待匯出表和登入使用者不同，則這裡需要添加匯出的對象schema(使用者)，否則會報錯。

4. 執行匯出命令。

expdp parfile=exp.par

開始報錯了，提示：

ORA-31631:需要許可權

ORA-39149:無法將授權使用者連結到非授權使用者

此時需要授予遠端資料庫使用者exp_full_database許可權：

GRANT exp_full_database TO username;

接下來就是等待了，dump檔案會儲存至本地的expdp_dir指向路徑下。

總結：

1. expdp/impdp是10g以上提供的資料泵工具，運行時會調用兩個PL/SQL包，當然也可以直接運行這兩個PL/SQL包，沒試過，如果大家試過，也可以分享出來。

2. 匯入資料庫賬戶需要有imp_full_database許可權，匯出資料庫賬戶需要有exp_full_database許可權。

3. expdp比exp更優，還有一些地方，比如從help=y可以看到expdp有更多的參數可選，其中expdp有COMPRESSION壓縮參數可選，解釋如下：

Reduce the size of a dump file.

Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.

可參考secooler的這篇文章（)，比對了四種參數的壓縮比。

4. 由於我的實驗中，遠程庫和本地庫之間網路不是很好，因此實際效果上，exp和expdp都比較慢，也是因為用了dblink的方式，就沒再深入了，這裡只是為了說明expdp/impdp如何?遠端資料庫的匯出。

後經同事測試，一個800MB的檔案，使用exp匯出大約用了1小時，expdp只需要不到10分鐘，請注意，這裡未使用COMPRESSION參數。

有人說這是由於exp和expdp匯出機制決定的，exp匯出過程實際是由select執行的載入資料，放到buffer cache，再傳到export用戶端寫入dump檔案。expdp則是採用直接路徑模式，直接從磁碟讀取，寫入PGA，再傳到export用戶端寫入dump檔案。沒有經過buffer cache緩衝就一定程度決定了他的匯出速度。

5. 網上有人說“expdp/impdp是服務端程式，影響他的只有磁碟IO”，從上面的介紹看，是不完全準確的，expdp/impdp是用戶端工具，執行時調用的是資料庫伺服器端的兩個PL/SQL包，上面的實驗也是通過dblink進行的遠程匯入，並未在伺服器端使用。

----------------------------華麗麗的分割線----------------------------

Oracle匯入匯出expdp IMPDP詳解

Oracle 10g expdp匯出報錯ORA-4031的解決方案

Oracle 10gr2 rac expdp 報錯UDE-00008 ORA-31626

Oracle中利用expdp/impdp備份資料庫的使用說明

Oracle備份還原（expdp/impdp）