理解IMPDP ORA-19505、ORA-31640错误

若在Oracle RAC集群数据库中,则可能Data Pump Worker数据泵工作进程在非发起本Expdp的节点上被分配并工作,除非指定了EXPDP参数CLUSTER=NO。 若启动该Workder进程的节点上相应的data pump目录没有合适的权限,则remote node上的Worker将无法正常工作。 可以利用cluster=no参数来Workaround这个由于RAC引起的ORA-19505错误。

[oracle@lab1 ~]$ oerr ora 19505
19505, 00000, “failed to identify file \”%s\””
// *Cause: call to identify the file returned an error
// *Action: check additional messages, and check if the file exists

 

关于EXPDP/IMPDP的CLUSTER参数说明如下:

CLUSTER
Default: YES
Purpose
Determines whether Data Pump can use Oracle Real Application Clusters (Oracle
RAC) resources and start workers on other Oracle RAC instances.
Syntax and Description
CLUSTER=[YES | NO]
To force Data Pump Import to use only the instance where the job is started and to
replicate pre-Oracle Database 11g release 2 (11.2) behavior, specify CLUSTER=NO.
To specify a specific, existing service and constrain worker processes to run only on
instances defined for that service, use the SERVICE_NAME parameter with the
CLUSTER=YES parameter.
Use of the CLUSTER parameter may affect performance because there is some
additional overhead in distributing the import job across Oracle RAC instances.

 

关于该ORA-19505更多信息参考以下Note:

We did expdp on AIX server with mutliple files using filesetsize=10G parameter 
and when we are importing into LInux database we are facing below errors:

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

 

When you are using a cluster database it is possible for datapump workers to start on 
any of the available nodes unless you specify cluster=N. If the nodes that start the 
workers do not have access to the datapump directory they will be unable to process 
the files. This is proved out when you specify cluster=N to remove the other nodes from the equation.

 

Use cluster=N to run the import from a single node of the RAC cluster which has file access.

Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *