epimp 与 epdpimpdp 对比 及使用中的一些优化事项

1、关于exp/imp和expdp/impdp, 之前整理的2篇Blog如下:ORACLE数据库逻辑备份简单EXP/IMP HYPERLINK /tianlesoftware/archive/2009/10/23/4718366.aspx /tianlesoftware/archive/2009/10/23/4718366.aspxOracle 10g EXPDP 和 IMPDP 使用说明 HYPERLINK /tianlesoftware/archive/2009/10/16/4674224.aspx /tianlesoftware/archive/2009/10/16/4674224.aspx

2、exp/imp 与 expdp/impdp 对比expdp/impdp调用Server端的API在执行操作，是数据库内部的job任务。可以远程使用, 但是生成的dump文件存在于服务器上的directory里。exp/imp与expdp/impdp的默认模式和原理不一样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默认会是传统路径，这种模式下，是用SELECT加数据查询出来，

5、 然后写入buffer cache，在将这些记录写入evaluate buffer.最后传到Export客户端，在写入 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直接路径模式下，数据直接从硬盘读取，然后写入PGA，格式就是export的 格式，不需要转换，数据再直接传到export客户端

8、，写入dump文件。这种模式没有经过evaluation buffer。少了一个过程，导出速度提高也是很明显。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默认就是使用直接路径的，所以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.这种模式很方便，但是速度是最慢的，因为它是通过insert, select + dblink来实现的。速 度慢也由此可见了。示例：create directory dump1 as /oradata/dumpfiles; grant read

16、,write on dump1 to xxx;创建 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参数，可以用口指定expdp xxx/xxx schemas=xxx directory=dumpl dumpfile=xxx_%U.dmp filesize=5g这样每个文件 5G , xxx_01.dump,xxx_02.dump 这样。关于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在下文也会讲到这点。expdp xxx/xxx schemas=xxxdirectory=dump1 network_link =dbl_65dumpfile=xxx_01.dump ,xxx_02.dump这样也可以，但不确定xxx_01.dump增到多大才开始写xxx_02.dump文件。ESTIMATE_ONLY=y可以估计文件大小。NETWORK_LINK :这样就可以不必一定在本机expdp，也可以在目标机通过 NETWORK_LINK把从文件抽到目标机上。expdp xxx/xxx schemas=xxxdirectory=du

19、mp1 network_link =tianlesoftwaredumpfile=xxx_%U.dump filesize=10m或者用impdp + network_link实现无文件导入需要注意，LOB字段可以使用NETWORK_LINK，而long类型字段会报错，ORA-31679: Table data object xx.SYS_USER has long columns, and longs can not be loaded/unloaded using a network link1.3网络和磁盘影响expdp/impdp是服务端程序，影响它速度的只有磁盘IO。exp/imp可

20、以在服务端，也可以在客户端。所以，它受限于网络和磁盘。1.4 exp/imp与expdp/impdp功能上的区别（1 ）把用户usera的对象导到用户 userb,用法区别在于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的时候，要想更改表所在的表空间，需要手工去处理一下， 女口 alter table xxx move tablespace_new 之类的操作。用 impdp 只要用 remap_tablespace=tabspace_old:tablespace_new（3）当指定一些表的时候，使用 exp/imp 时，tables 的用法是 tables=（table1,table2,table3）。 expdp/impdp 用法是 tables=table1,table2,table3。(4)是否要导出数据

22、行exp (ROWS=Y导出数据行，ROWS=N不导出数据行)expdp content( ALL:对象+导出数据行，DATA_ONLY：只导出对象,METADATA_ONLY：只导出数据的记录)使用中的优化事项exp通过上面的分析，知道采用direct path可以提高导出速度。所以，在使用exp 时，就可以采用直接路径模式。这种模式有2个相关的参数:DIRECT和RECORDLENGTH 参数。DIRECT参数定义了导出是使用直接路径方式(DIRECT=Y)，还是常规路径方式 (DIRECT=N)。常规路径导出使用SQL SELECT语句从表中抽取数据，直接路径导出则是将 数据直接从磁盘读

23、到PGA再原样写入导出文件，从而避免了 SQL命令处理层的数据转换过 程，大大提高了导出效率。在数据量大的情况下，直接路径导出的效率优势更为明显，可比 常规方法速度提高三倍之多。和DIRECT=Y配合使用的是RECORDLENGTH参数，它定义了 Export I/O缓冲的大小, 作用类似于常规路径导出使用的BUFFER参数。建议设置RECORDLENGTH参数为最大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参数。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导入有lob字段的biao。不过现在很少有有8版 本的数据库了。这点可以忽略掉了。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选项只对conventional path exp有效。对于直接路径没有影响。对于直接路径，应 该设置RECORDLENGTH参数。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-对于直接路径下，RECORDLENGTH参数建议设成64k (65535)。这个值对性能提高比较大。如： 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进程需要花比Export进程数倍的时间将数据导入数据库。某些关键时刻, 导入是为了应对数据库的紧急

31、故障恢复。为了减少宕机时间，加快导入速度显得至关重要。 没有特效办法加速一个大数据量的导入，但我们可以做一些适当的设定以减少整个导入时 间。（1）避免I/O竞争Import是一个I/O密集的操作，避免I/O竞争可以加快导入速度。如果可能， 不要在系统高峰的时间导入数据，不要在导入数据时运行job等可能竞争系统资源的操作。（2）增加排序区Oracle Import进程先导入数据再创建索引，不论INDEXES值设为YES或者NO,主键 的索引是一定会创建的。创建索引的时候需要用到排序区，在内存大小不足的时候，使用临 时表空间进行磁盘排序，由于磁盘排序效率和内存排序效率相差好几个数量级。增加排序区

32、可以大大提高创建索引的效率，从而加快导入速度。（3）调整BUFFER选项Imp参数BUFFER定义了每一次读取导出文件的数据量，设的越大，就越减少Import 进程读取数据的次数，从而提高导入效率。BUFFER的大小取决于系统应用、数据库规模， 通常来说，设为百兆就足够了。其用法如下：imp user/pwd fromuser=userl touser=user2 file=/tmp/imp_db_pipel commit=y feedback=10000 buffer=10240000（4）使用COMMIT=Y选项COMMIT=Y表示每个数据缓冲满了之后提交一次，而不是导完一张表提交一次。这

33、样 会大大减少对系统回滚段等资源的消耗，对顺利完成导入是有益的。（5）使用INDEXES=N选项前面谈到增加排序区时，说明Imp进程会先导入数据再创建索引。导入过程中建立用 户定义的索引，特别是表上有多个索引或者数据表特别庞大时，需要耗费大量时间。某些情 况下，需要以最快的时间导入数据，而索引允许后建，我们就可以使用INDEXES=N只导 入数据不创建索引，从而加快导入速度。我们可以用INDEXFILE选项生成创建索引的DLL脚本，再手工创建索引。我们也可 以用如下的方法导入两次，第一次导入数据，第二次导入索引。其用法如下：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 等参数，

35、tnsnames.ora 中又没有(SERVER=DEDICATED)的配置，那么数据库就使用了共享服务器模式。在 MTS 模式下，Exp/Imp操作会用到LARGE_POOL，建议调整LARGE_POOL_SIZE到150M。检查数据库是否在MTS模式下：SQLselect distinct server from v$session;如果返回值出现none或shared，说明启用了 MTS。Expdp/Impdp据泵与exp/imp来说性能有很大的提高，其中影响最大的就是paralle。可以 这么来看：expdp/impdp=exp/imp+direct moe + paralle.所以，

36、使用数据泵，要想提高速度, 就要设置并行参数。先看2个参数: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