文章概述

搜MOS文章看到這篇比較有意思的文章，Oracle數據泵導出和導入的時候使用了并行之后，如果沒有加上cluster=n，那么很容易報錯。 這篇文章中MOS提供了另一個解決方法，雖然不太實用，可以了解一下。

數據泵導入報錯ORA-29913 ORA-31640 ORA-19505 ORA-27037

參考
DataPump Import With PARALLEL > 1 In RAC Environment Fails With Errors ORA-29913 ORA-31640 ORA-19505 ORA-27037 (Doc ID 1173765.1)

報錯現象

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.1 [Release 11.2]
When importing a dump file using IMPDP where the dump file is stored on a cluster filesystem, the following errors are raised:

ORA-31693: Table data object "<SCHEMA_NAME>"."<TABLE_NAME>":"<PARTITION_NAME>" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-31640: unable to open dump file "<PATH>/<DUMP_NAME>.dmp" for read
ORA-19505: failed to identify file "<PATH>/<DUMP_NAME>.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
The IMPDP parameters used are:

directory=<DIRECTORY_NAME>
filesize=30G
full=yes
dumpfile=<DUMP_NAME>%U.dmp
logfile=<LOG_NAME>.log
status=200
parallel=7

CAUSE

The cause of this problem has been identified in unpublished bug 9378256. It is caused by a RAC instance not having physical access to the dump file (as indicated by the DIRECTORY parameter) and using a parallel degree > 1. The problem with this setup is that the PQ/PX processes are not constrained to the RAC instance from which the IMPDP operation has been started, and when such a PQ/PX process doesn't have access to the dump file, the errors are raised.

The behavior is also reported in unpublished Bug 8415620 - DATA PUMP DOES NOT HONOR THE BOUNDARIES OF THE CONNECTED SERVICE ON RAC

DataPump RAC support is provided in 11.2. Prior to 11.2, once you use a service to make the initial connection to the database, an instance is selected and the master process and all worker processes run on that instance. 

In 11.2, the new DataPump parameter CLUSTER is introduced:

CLUSTER : Default=Y

Purpose :
Determines whether Data Pump can use Oracle Real Application Clusters (RAC)
resources and start workers on other Oracle RAC instances.

Syntax and Description : CLUSTER=[Y | N]

SOLUTION

The issue has been fixed in the 11.2.0.2 patchset.

Possible workarounds for the 11.2.0.1 release are:

use the CLUSTER or SERVICE_NAME parameters to constrain the IMPDP job never to run on the instance not having access to the dump file

configure the database parameters in such a way that PQ/PX processes are never started on the instance not having access to the dump file

use the PARALLEL=1 IMPDP command line parameter to disable parallel functionality during import

store all dump files on a local filesystem of one of the RAC nodes and use a parallel degree lower than the number of dump files and import with the CLUSTER=N command line option

mount the cluster filesystem on all nodes and set the parallel degree to be equal to the number of dump files and import with the CLUSTER=N command line option

我們來回溯一下整個過程：
1.數據泵導入使用了并行，沒有配置cluster參數;
2.環境是一套RAC,dump文件的目錄呢RAC的節點并不是共享的，那么數據泵cluster默認參數是y,那么使用并行之后oracle后臺為了負載均衡，可能會讓子進程在RAC的不同節點執行導入的動作，那么其它節點的子進程需要讀取dump文件，由于文件系統dump對應的目錄對于RAC的其它節點操作系統并不共享，因此讀取失敗，就報錯了；
3.官方建議解決辦法，也是我們非常熟悉的cluster=n去解決，限定在單個主機上，你的導入的任務的進程就不要跑到其它節點上了；
4.官方有另一個比較有意思的SERVICE_NAME 指向，這個就是本篇文章想分享的內容。同樣把MOS文檔放出來。

How To Limit The Data Pump Slaves To Only A Handful Of RAC Instances?

參考
(Doc ID 1590699.1)

GOAL
In RAC environment, the Data Pump use all the instances of the database rather than just one instance for all the slaves. This is the default behavior because of cluster=y (by default).
What if you have more than two instances and while you want to run Data Pump slaves in cluster mode, you want to limit them to only a handful of instances only; not all?

SOLUTION
Use the DP parameter: service_name. Create a service name that is defined on only those instances and use it in Data Pump.
Here is an example where you have four instances and you want to use only instances 2 and 3 for the Data Pump job. Create a service name – nodes_2_3 – as shown below:

$ srvctl add service -d <DatabaseName> -s nodes_2_3 -a RAC2,RAC3 -r RAC1,RAC4 -P BASIC


Then you should use the parameter in Data Pump:

cluster=y service_name=nodes_2_3
Now the DP job will use RAC2 and RAC3 instances only, i.e. nodes 2 and 3.

Note: If you start the expdp process on nodes 1 or 4, the Data Pump processes can start there as well, in addition to nodes 2 and 3.

小結：本質上和RAC通過service_name配置主備節點，避免GC的原理是一樣的。 有興趣的小伙伴知道有這個技巧就可以了。 之前不知道這個，就寫個文章記錄一下。