epimp 與 epdpimpdp 對(duì)比 及使用中的一些優(yōu)化事項(xiàng)

1、關(guān)于exp/imp和expdp/impdp, 之前整理的2篇Blog如下:ORACLE數(shù)據(jù)庫(kù)邏輯備份簡(jiǎn)單EXP/IMP HYPERLINK /tianlesoftware/archive/2009/10/23/4718366.aspx /tianlesoftware/archive/2009/10/23/4718366.aspxOracle 10g EXPDP 和 IMPDP 使用說(shuō)明 HYPERLINK /tianlesoftware/archive/2009/10/16/4674224.aspx /tianlesoftware/archive/2009/10/16/4674224.aspx

2、exp/imp 與 expdp/impdp 對(duì)比expdp/impdp調(diào)用Server端的API在執(zhí)行操作，是數(shù)據(jù)庫(kù)內(nèi)部的job任務(wù)?？梢赃h(yuǎn)程使用, 但是生成的dump文件存在于服務(wù)器上的directory里。exp/imp與expdp/impdp的默認(rèn)模式和原理不一樣1.2.1 exp/imp不同模式原理在metalink的這邊文章中，提到了 exp/imp的不同模式下的工作原理：Parameter DIRECT: Conventional Path Export Versus Direct Path Export ID 155477.1 HYPERLINK /tianlesoftware/

3、archive/2010/12/22/6090759.aspx /tianlesoftware/archive/2010/12/22/6090759.aspxStarting with Oracle7 release 7.3, the Export utility provides two methods for exporting table data: -Conventional Path Export-Direct Path ExportConventional path Export.Conventional path Export uses the SQL SELECT statem

4、ent to extract data from tables. Data is read from disk into the buffer cache, and rows are transferred to the evaluating buffer. The data, after passing expression evaluation, is transferred to the Export client, which then writes the data into the export file.exp/imp默認(rèn)會(huì)是傳統(tǒng)路徑，這種模式下，是用SELECT加數(shù)據(jù)查詢(xún)出來(lái)，

5、 然后寫(xiě)入buffer cache，在將這些記錄寫(xiě)入evaluate buffer.最后傳到Export客戶(hù)端，在寫(xiě)入 dump文件。Direct path Export.When using a Direct path Export, the data is read from disk directly into the export sessions program global area (PGA): the rows are transferred directly to the Export sessions private buffer. This also means that

6、 the SQL command-processing layer (evaluation buffer) can be bypassed, because the data is already in the format that Export expects. As a result, unnecessary data conversion is avoided. The data is transferred to the Export client, which then writes the data into the export file.The default is DIRE

7、CT=N, which extracts the table data using the conventional path.This parameter is only applicable to the original export client. Export DataPump (expdp) uses a Direct Path unload by default and switches to External Table mode if required直接路徑模式下，數(shù)據(jù)直接從硬盤(pán)讀取，然后寫(xiě)入PGA，格式就是export的 格式，不需要轉(zhuǎn)換，數(shù)據(jù)再直接傳到export客戶(hù)端

8、，寫(xiě)入dump文件。這種模式?jīng)]有經(jīng)過(guò)evaluation buffer。少了一個(gè)過(guò)程，導(dǎo)出速度提高也是很明顯。1.2.2 expdp/impdp 不同模式Export/Import DataPump Parameter ACCESS_METHOD - How to Enforce aMethod of Loading and Unloading Data ? ID 552424.1 HYPERLINK /tianlesoftware/archive/2010/12/22/6090757.aspx /tianlesoftware/archive/2010/12/22/6090757.aspxTh

9、e two most commonly used methods to move data in and out of databases with Data Pump are the Direct Path method and the External Tables method.（1）Direct Path mode.After data file copying, direct path is the fastest method of moving data. In this method, the SQL layer of the database is bypassed and

10、rows are moved to and from the dump file with only minimal interpretation. Data Pump automatically uses the direct path method for loading and unloading data when the structure of a table allows it.expdp/impdp默認(rèn)就是使用直接路徑的，所以expdp要比exp塊。（2）External Tables mode.If data cannot be moved in direct path mo

11、de, or if there is a situation where parallel SQL can be used to speed up the data move even more, then the external tables mode is used. The external table mechanism creates an external table that maps the dump file data for the database table. The SQL engine is then used to move the data. If possi

12、ble, the APPEND hint is used on import to speed the copying of the data into the database.Note: When the Export NETWORK_LINK parameter is used to specify a network link for an export operation, a variant of the external tables method is used. In this case, data is selected from across the specified

13、network link and inserted into the dump file using an external table.Data File Copying mode.This mode is used when a transport tablespace job is started, i.e.: the TRANSPORT_TABLESPACES parameter is specified for an Export Data Pump job. This is the fastest method of moving data because the data is

14、not interpreted nor altered during the job, and Export Data Pump is used to unload only structural information (metadata) into the dump file.Network Link Import mode.This mode is used when the NETWORK_LINK parameter is specified during an Import Data Pump job. This is the slowest of the four access

15、methods because this method makes use of an INSERT SELECT statement to move the data over a database link, and reading over a network is generally slower than reading from a disk.這種模式很方便，但是速度是最慢的，因?yàn)樗峭ㄟ^(guò)insert, select + dblink來(lái)實(shí)現(xiàn)的。速 度慢也由此可見(jiàn)了。示例：create directory dump1 as /oradata/dumpfiles; grant read

16、,write on dump1 to xxx;創(chuàng)建 DBLINK:/* Formatted on 2010/12/23 11:28:22 (QP5 v5.115.810.9015) */CREATE DATABASE LINK TIANLESOFTWARECONNECT TO BUSINESSIDENTIFIED BYvPWDUSING(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = IP ADDRESS)(PORT = 1521)(CONNECT_DATA = (SID = ORCL)(SERVER = DEDIC

17、ATED)；Dumpfile參數(shù)，可以用口指定expdp xxx/xxx schemas=xxx directory=dumpl dumpfile=xxx_%U.dmp filesize=5g這樣每個(gè)文件 5G , xxx_01.dump,xxx_02.dump 這樣。關(guān)于U參考： HYPERLINK http:/download.oracle.Com/docs/cd/B19306_01/server.102/b14215/dp_overview.htm%23sthref44 http:/download.oracle.Com/docs/cd/B19306_01/server.102/b142

18、15/dp_overview.htm#sthref44在下文也會(huì)講到這點(diǎn)。expdp xxx/xxx schemas=xxxdirectory=dump1 network_link =dbl_65dumpfile=xxx_01.dump ,xxx_02.dump這樣也可以，但不確定xxx_01.dump增到多大才開(kāi)始寫(xiě)xxx_02.dump文件。ESTIMATE_ONLY=y可以估計(jì)文件大小。NETWORK_LINK :這樣就可以不必一定在本機(jī)expdp，也可以在目標(biāo)機(jī)通過(guò) NETWORK_LINK把從文件抽到目標(biāo)機(jī)上。expdp xxx/xxx schemas=xxxdirectory=du

19、mp1 network_link =tianlesoftwaredumpfile=xxx_%U.dump filesize=10m或者用impdp + network_link實(shí)現(xiàn)無(wú)文件導(dǎo)入需要注意，LOB字段可以使用NETWORK_LINK，而long類(lèi)型字段會(huì)報(bào)錯(cuò)，ORA-31679: Table data object xx.SYS_USER has long columns, and longs can not be loaded/unloaded using a network link1.3網(wǎng)絡(luò)和磁盤(pán)影響expdp/impdp是服務(wù)端程序，影響它速度的只有磁盤(pán)IO。exp/imp可

20、以在服務(wù)端，也可以在客戶(hù)端。所以，它受限于網(wǎng)絡(luò)和磁盤(pán)。1.4 exp/imp與expdp/impdp功能上的區(qū)別（1 ）把用戶(hù)usera的對(duì)象導(dǎo)到用戶(hù) userb,用法區(qū)別在于fromuser=usera touser=userb ,remap_schema=usera:userb。例女口imp system/passwd fromuser=usera touser=userb file=/oracle/exp.dmp log=/oracle/exp.log;impdpsystem/passwddirectory=expdpdumpfile=expdp.dmpremap_schema=user

21、a:userb logfile=/oracle/exp.log;(2)更換表空間，用exp/imp的時(shí)候，要想更改表所在的表空間，需要手工去處理一下， 女口 alter table xxx move tablespace_new 之類(lèi)的操作。用 impdp 只要用 remap_tablespace=tabspace_old:tablespace_new（3）當(dāng)指定一些表的時(shí)候，使用 exp/imp 時(shí)，tables 的用法是 tables=（table1,table2,table3）。 expdp/impdp 用法是 tables=table1,table2,table3。(4)是否要導(dǎo)出數(shù)據(jù)

22、行exp (ROWS=Y導(dǎo)出數(shù)據(jù)行，ROWS=N不導(dǎo)出數(shù)據(jù)行)expdp content( ALL:對(duì)象+導(dǎo)出數(shù)據(jù)行，DATA_ONLY：只導(dǎo)出對(duì)象,METADATA_ONLY：只導(dǎo)出數(shù)據(jù)的記錄)使用中的優(yōu)化事項(xiàng)exp通過(guò)上面的分析，知道采用direct path可以提高導(dǎo)出速度。所以，在使用exp 時(shí)，就可以采用直接路徑模式。這種模式有2個(gè)相關(guān)的參數(shù):DIRECT和RECORDLENGTH 參數(shù)。DIRECT參數(shù)定義了導(dǎo)出是使用直接路徑方式(DIRECT=Y)，還是常規(guī)路徑方式 (DIRECT=N)。常規(guī)路徑導(dǎo)出使用SQL SELECT語(yǔ)句從表中抽取數(shù)據(jù)，直接路徑導(dǎo)出則是將 數(shù)據(jù)直接從磁盤(pán)讀

23、到PGA再原樣寫(xiě)入導(dǎo)出文件，從而避免了 SQL命令處理層的數(shù)據(jù)轉(zhuǎn)換過(guò) 程，大大提高了導(dǎo)出效率。在數(shù)據(jù)量大的情況下，直接路徑導(dǎo)出的效率優(yōu)勢(shì)更為明顯，可比 常規(guī)方法速度提高三倍之多。和DIRECT=Y配合使用的是RECORDLENGTH參數(shù)，它定義了 Export I/O緩沖的大小, 作用類(lèi)似于常規(guī)路徑導(dǎo)出使用的BUFFER參數(shù)。建議設(shè)置RECORDLENGTH參數(shù)為最大I/O 緩沖，即65535(64kb)。其用法如下：女口 : exp userid=system/manager full=y direct=y recordlength=65535file=exp_full.dmp log=ex

24、p_full.log一些限制如下：You cannot use the DIRECT=Y parameter when exporting in tablespace-mode (i.e. when specifying the parameter TRANSPORT_TABLESPACES=Y). You can use the DIRECT=Y parameter when exporting in full, user or table mode (i.e.: when specifying FULL=Y or OWNER=scott or TABLES=scott.emp).-直接路徑

25、不能使用在tablespace-modeThe parameter QUERY applies ONLY to conventional path Export. It cannot be specified in a direct path export (DIRECT=Y).-直接路徑不支持query參數(shù)。query只能在conventional path模式下使用。In versions of Export prior to 8.1.5, you could not use direct path Export for tables containing objects and LOBs

26、.-如果exp版本小于8.1.5，不能使用exp導(dǎo)入有l(wèi)ob字段的biao。不過(guò)現(xiàn)在很少有有8版 本的數(shù)據(jù)庫(kù)了。這點(diǎn)可以忽略掉了。The BUFFER parameter applies ONLY to conventional path Export. It has no effect on a direct path Export. This BUFFER parameter specifies the size (in bytes) of the buffer used to fetch rows. It determines the maximum number of rows in

27、an array, fetched by Export. For direct path Export, use the RECORDLENGTH parameter to specify the size of the buffer that Export uses for writing to the export file.-buffer選項(xiàng)只對(duì)conventional path exp有效。對(duì)于直接路徑?jīng)]有影響。對(duì)于直接路徑，應(yīng) 該設(shè)置RECORDLENGTH參數(shù)。The RECORDLENGTH parameter specifies the length (in bytes) of

28、 the file record. Y ou can use this parameter to specify the size of the Export I/O buffer (highest value is 64 kb). Changing the RECORDLENGTH parameter affects only the size of data that accumulates before writing to disk. It does not affect the operating system file block size. If you do not defin

29、e this parameter, it defaults to your platform-dependent value for BUFSIZ (1024 bytes in most cases).invoking a Direct path Export with a maximum I/O buffer of 64kb can improve the performance of the Export with almost 50%. This can be achieved by specifying the additional Export parameters DIRECT a

30、nd RECORDLENGTH-對(duì)于直接路徑下，RECORDLENGTH參數(shù)建議設(shè)成64k (65535)。這個(gè)值對(duì)性能提高比較大。如： exp system/manager FILE=exp_full.dmp LOG=exp_full.logFULL=y DIRECT=y RECORDLENGTH=65535 imp system/manager FILE=exp_full.dmp LOG=imp_full.logFULL=y RECORDLENGTH=65535IMPOracle Import進(jìn)程需要花比Export進(jìn)程數(shù)倍的時(shí)間將數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫(kù)。某些關(guān)鍵時(shí)刻, 導(dǎo)入是為了應(yīng)對(duì)數(shù)據(jù)庫(kù)的緊急

31、故障恢復(fù)。為了減少宕機(jī)時(shí)間，加快導(dǎo)入速度顯得至關(guān)重要。 沒(méi)有特效辦法加速一個(gè)大數(shù)據(jù)量的導(dǎo)入，但我們可以做一些適當(dāng)?shù)脑O(shè)定以減少整個(gè)導(dǎo)入時(shí) 間。（1）避免I/O競(jìng)爭(zhēng)Import是一個(gè)I/O密集的操作，避免I/O競(jìng)爭(zhēng)可以加快導(dǎo)入速度。如果可能， 不要在系統(tǒng)高峰的時(shí)間導(dǎo)入數(shù)據(jù)，不要在導(dǎo)入數(shù)據(jù)時(shí)運(yùn)行job等可能競(jìng)爭(zhēng)系統(tǒng)資源的操作。（2）增加排序區(qū)Oracle Import進(jìn)程先導(dǎo)入數(shù)據(jù)再創(chuàng)建索引，不論INDEXES值設(shè)為YES或者NO,主鍵 的索引是一定會(huì)創(chuàng)建的。創(chuàng)建索引的時(shí)候需要用到排序區(qū)，在內(nèi)存大小不足的時(shí)候，使用臨 時(shí)表空間進(jìn)行磁盤(pán)排序，由于磁盤(pán)排序效率和內(nèi)存排序效率相差好幾個(gè)數(shù)量級(jí)。增加排序區(qū)

32、可以大大提高創(chuàng)建索引的效率，從而加快導(dǎo)入速度。（3）調(diào)整BUFFER選項(xiàng)Imp參數(shù)BUFFER定義了每一次讀取導(dǎo)出文件的數(shù)據(jù)量，設(shè)的越大，就越減少I(mǎi)mport 進(jìn)程讀取數(shù)據(jù)的次數(shù)，從而提高導(dǎo)入效率。BUFFER的大小取決于系統(tǒng)應(yīng)用、數(shù)據(jù)庫(kù)規(guī)模， 通常來(lái)說(shuō)，設(shè)為百兆就足夠了。其用法如下：imp user/pwd fromuser=userl touser=user2 file=/tmp/imp_db_pipel commit=y feedback=10000 buffer=10240000（4）使用COMMIT=Y選項(xiàng)COMMIT=Y表示每個(gè)數(shù)據(jù)緩沖滿(mǎn)了之后提交一次，而不是導(dǎo)完一張表提交一次。這

33、樣 會(huì)大大減少對(duì)系統(tǒng)回滾段等資源的消耗，對(duì)順利完成導(dǎo)入是有益的。（5）使用INDEXES=N選項(xiàng)前面談到增加排序區(qū)時(shí)，說(shuō)明Imp進(jìn)程會(huì)先導(dǎo)入數(shù)據(jù)再創(chuàng)建索引。導(dǎo)入過(guò)程中建立用 戶(hù)定義的索引，特別是表上有多個(gè)索引或者數(shù)據(jù)表特別龐大時(shí)，需要耗費(fèi)大量時(shí)間。某些情 況下，需要以最快的時(shí)間導(dǎo)入數(shù)據(jù)，而索引允許后建，我們就可以使用INDEXES=N只導(dǎo) 入數(shù)據(jù)不創(chuàng)建索引，從而加快導(dǎo)入速度。我們可以用INDEXFILE選項(xiàng)生成創(chuàng)建索引的DLL腳本，再手工創(chuàng)建索引。我們也可 以用如下的方法導(dǎo)入兩次，第一次導(dǎo)入數(shù)據(jù)，第二次導(dǎo)入索引。其用法如下：imp user/pwd fromuser=userl touser=

34、user2 file=/tmp/imp_db_pipel commit=y feedback=10000 buffer=10240000 ignore=y rows=y indexes=n imp user/pwd fromuser=user1 touser=user2 file=/tmp/imp_index_pipe1 commit=y feedback=10000 buffer=10240000 ignore=y rows=n indexes=y(6)增力口 LARGE_POOL_SIZE如果在 init.ora 中配置了 MTS_SERVICE, MTS_DISPATCHERS 等參數(shù)，

35、tnsnames.ora 中又沒(méi)有(SERVER=DEDICATED)的配置，那么數(shù)據(jù)庫(kù)就使用了共享服務(wù)器模式。在 MTS 模式下，Exp/Imp操作會(huì)用到LARGE_POOL，建議調(diào)整LARGE_POOL_SIZE到150M。檢查數(shù)據(jù)庫(kù)是否在MTS模式下：SQLselect distinct server from v$session;如果返回值出現(xiàn)none或shared，說(shuō)明啟用了 MTS。Expdp/Impdp據(jù)泵與exp/imp來(lái)說(shuō)性能有很大的提高，其中影響最大的就是paralle。可以 這么來(lái)看：expdp/impdp=exp/imp+direct moe + paralle.所以，

36、使用數(shù)據(jù)泵，要想提高速度, 就要設(shè)置并行參數(shù)。先看2個(gè)參數(shù):Setting ParallelismFor export and import operations, the parallelism setting (specified with the PARALLEL parameter) should be less than or equal to the number of dump files in the dump file set. If there are not enough dump files, the performance will not be optimal be

37、cause multiple threads of execution will be trying to access the same dump file.The PARALLEL parameter is valid only in the Enterprise Edition of Oracle Database 10g.Using Substitution VariablesInstead of, or in addition to, listing specific filenames, you can use the DUMPFILE parameter during expor

38、t operations to specify multiple dump files, by using a substitution variable (%U) in the filename. This is called a dump file template. The new dump files are created as they are needed, beginning with 01 for %U, then using 02, 03, and so on. Enough dump files are created to allow all processes spe

39、cified by the current setting of the PARALLEL parameter to be active. If one of the dump files becomes full because its size has reached the maximum size specified by the FILESIZE parameter, it is closed, and a new dump file (with a new generated name) is created to take its place.From: HYPERLINK http:/download.oracle.eom/docs/cd/B19306_01/server.102/b14215/dp_overview.htm%23sthref44 http:/download.oracle.eom/docs/cd/B19306_01/server.102/b1421