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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/32980/viewspace-1059249/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/32980/viewspace-1059249/