数据泵联合dblink 使用场景

一 、 expdp可以通过dblink来实现把导出文件直接放在客户端

1、在客户端数据库创建db link和要保存数据文件的目录（在system 用户下操作）

create database link fbfj
  connect to system identified BY ora1Q23$AZ 
  using '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = fbfj)
    )
  )';

create directory dir_test as '/oradata/dmptest';

2、在客户端发布命令

 expdp 'system/"ora1Q23$AZ"' schemas= USR_Q9FJ directory=dir_test network_link=fbfj dumpfile=fbfj%U.dmp logfile=fbfj.log  parallel=4

二、expdp + dblink 与expdp ＋nfs 比较

1、expdp + dblink 

[.com_oracle:/oradata/dmptest$ expdp 'system/"ora1Q23$AZ"' schemas= USR_Q9FJ directory=dir_test network_link=fbfj dumpfile=fbfj%U.dmp logfile=fbfj.log  parallel=2

Export: Release 11.2.0.3.0 - Production on Mon Aug 24 16:17:24 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas= directory=dir_test network_link=fbfj dumpfile=fbfj%U.dmp logfile=fbfj.log parallel=2 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 93.67 GB
Processing object type SCHEMA_EXPORT/USER

<pre name="code" class="html">Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /oradata/dmptest/fbfj01.dmp
  /oradata/dmptest/fbfj02.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:32:02

dmp 文件为 82G ，用时 70分钟

2、 expdp ＋nfs 

nfs 参数：

10.9.:/Data        /tmpdata        nfs     rsize=32768,wsize=32768,hard,nointr,rw,bg,vers=3,tcp,noac,nolock,actimeo=0,timeo=600 0 0

Export: Release 10.2.0.4.0 - 64bit Production on Monday, 17 August, 2015 11:18:21

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;; 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_04":  system/******** schemas= USR_Q9FJ directory=dir_hlb dumpfile=fbfj_%U.dmp logfile=fbfj.log parallel=2 flashback_scn=1463086658 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 84.48 GB

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_04 is:
  /tmpdata/fbfj_01.dmp
  /tmpdata/fbfj_02.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_04" successfully completed at 12:37:44
[oracle@oradb1 tmpdata]$ 

dmp 文件 72G ，用时 80分钟。

从上可以看出当paralle 为 2 的时候，expdp + dblink  的性能优于 expdp + nfs

三、impdp +dblink 实现不落地导入

条件跟（一） 一样

[oracle@qs-oradb1 ~]$   impdp  'system/"ora1Q23$AZ"' schemas= USR_Q9FJ network_link=fbfj  flashback_scn= 6387059556023 parallel=2

Import: Release 11.2.0.4.0 - Production on Mon Aug 24 20:47:35 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** schemas= network_link=fbfj flashback_scn= parallel=2 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 94.50 GB

<pre name="code" class="html">Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Mon Aug 24 22:32:53 2015 elapsed 0 01:45:17

耗时 100分钟 。

单独导入 需要30 分钟，所以 expdp + dblink +impdp 与 impdp +　dblink的 效率差不多，但是优于  expdp + nfs + impdp ，但是 impdp +　dblink 方法最省事。