ORA-31640: unable to open dump file 問題解決

在一次將ORACLE 10G 資料庫資料遷移到ORACLE 11G RAC環境時,遇到了"ORA-31640: unable to open dump file "的錯誤.
匯入的命令如下:
$nohup idmpdp system/******** directory=imp_dir dumpfile=full_db_20120823.dmp schemas=daanl,daanl_data logfile=imp_full_db_20120824.log parallel=4 &
。。。。。。

ORA-31693: Table data object "DNL_SM"."AM_EXCEPTION_INFO" failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file "/oracle/full_db_20120823.dmp" for read
ORA-19505: failed to identify file "/oracle/full_db_20120823.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

[oracle@dnlgl1 ~]$ oerr ora 31640
31640, 00000, "unable to open dump file "%s" for read"
// *Cause: Import was unable to open the export file for reading. This
// message is usually followed by device messages from the operating
// system.
// *Action: Take appropriate action to restore the device.

而且發現大的表才會報這個錯，小的表可以順利地匯入進去。

[@more@]

二. 問題分析

2.1 因為報的錯誤是不可以讀dump檔案,仔細檢查了檔案及其目錄的訪問許可權,發現沒有任何問題.

2.2 在metalink上搜尋,找到了ID=1071373.1的文章,知道了真正的原因.
原來11g R2的IMPDP 增加了一個引數設定:CLUSTER,在設定了parallel引數>1的情況下, 可以支援多個節點的同時匯入工作.
這邊實際的情況是,另外一個節點無法訪問到dump檔案,我也是隻想從一個節點匯入資料,所以需要關閉這個選項.

三. 問題解決
修改匯入的命令,設定cluster=N,再次進行匯入
$nohup idmpdp system/******** directory=imp_dir dumpfile=full_db_20120823.dmp schemas=daanl,daanl_data logfile=imp_full_db_20120824.log parallel=4 cluster=N;

資料庫資料可以正常匯入到11g rac的資料庫中問題解決.

附件: metalink doc id=1071373.1的文章的主要內容:

Symptoms
DataPump on 11.2 RAC with PARALLEL > 1 still hits the following errors though the unpublished Bug 8415620 is fixed into 11.2:

ORA-31693: Table data object "TITAN"."TN_TICKET":"TN01_2006_02" 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 "/oracle/xfertest/titan_01.dmp" for read
ORA-19505: failed to identify file "/oracle/xfertest/titan_01.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
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.

Cause
From 11.2, DataPump new 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
To force DataPump to use only the instance where the job is started and to replicate pre-Oracle Database 11g release 2 (11.2) behavior, specify CLUSTER=N.

Example:
#> expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr_clus%U.dmp CLUSTER=N PARALLEL=3