oracle 31640,ORACLE impdp或expdp与ORA-31693&ORA-31640&ORA-19505&ORA-27037

今天，安装完了衡阳ORACLE 11.2.0.4 rac for RHEL6.8集群后，做数据迁移的时候，碰到了expdp报错：

ORA-31693&ORA-31640&ORA-19505&ORA-27037。具体的报错信息如下：

Import: Release 11.2.0.4.0 - Production on Wed Feb 15 11:24:55 2017

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

Master table "ZHUL"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "ZHUL"."SYS_IMPORT_SCHEMA_01":  zhul/******** directory=zhul schemas=BMI dumpfile=ORCL_EXPDP_BMI_2017021501.DMP logfile=impdp_orcl_BMI_20170215.log parallel=4

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

ORA-31693: Table data object "BMI"."DW_BILLDETAIL":"P_DW_BILLDETAIL_201610" failed to load/unload and is being skipped due to error:

ORA-31640: unable to open dump file "/home/oracle/expdp/ORCL_EXPDP_BMI_2017021501.DMP" for read

ORA-19505: failed to identify file "/home/oracle/expdp/ORCL_EXPDP_BMI_2017021501.DMP"

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

由于dmp文件比较大，34G之多，里边有大表，加上客户方对时间要求比较紧，所以使用如下命令并行导入：

impdpzhul/******** directory=zhul schemas=BMI dumpfile=ORCL_EXPDP_BMI_2017021501.DMP logfile=impdp_orcl_BMI_20170215.log parallel=4

然而，执行过程却碰到上述错误，更为蹊跷的是，虽然impdp报错说找不到指定的dmp文件，但是主机上确实存在这个dmp文件，而且报错imp还有数据导入到数据库中，信息如下：

ORA-31693: Table data object "BMI"."DW_OPINION_DETAILS":"P_DW_OPINION_DETAILS_201605" failed to load/unload and is being skipped due to error:

ORA-31640: unable to open dump file "/home/oracle/expdp/ORCL_EXPDP_BMI_2017021501.DMP" for read

ORA-19505: failed to identify file "/home/oracle/expdp/ORCL_EXPDP_BMI_2017021501.DMP"

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

. . imported "BMI"."DW_BILL":"P_DW_BILL_201604"          10.28 MB   26019 rows

. . imported "BMI"."DW_BILL":"P_DW_BILL_201612"          10.27 MB   25808 rows

. . imported "BMI"."DW_BILL":"P_DW_BILL_201701"          10.36 MB   25917 rows

. . imported "BMI"."DW_OPINION_DETAILS":"P_DW_OPINION_DETAILS_201608"  9.801 MB   81796 rows

. . imported "BMI"."DW_OPINIONS":"P_DW_OPINIONS_201603"  9.829 MB   22372 rows

. . imported "BMI"."DW_BILL":"P_DW_BILL_201511"          9.302 MB   23493 rows

. . imported "BMI"."DW_BILL":"P_DW_BILL_201512"          9.404 MB   23770 rows

ORA-31693: Table data object "BMI"."DW_BILL":"P_DW_BILL_201606" failed to load/unload and is being skipped due to error:

ORA-31640: unable to open dump file "/home/oracle/expdp/ORCL_EXPDP_BMI_2017021501.DMP" for read

ORA-19505: failed to identify file "/home/oracle/expdp/ORCL_EXPDP_BMI_2017021501.DMP"

ORA-27037: unable to obtain file status

本次最终导入结果是失败的，在数据同事做数据核对时，表的数目是对的，但是很多表的数据没有导入或导入的数据条目不正确。

实在没有更好的办法，我只能尝试放弃并行，删除bmi用户后，使用原先的dmp文件，做重新impdp导入，意想不到的是竟然成功了，仅仅有几个视图、存储过程编译警告：

Import: Release 11.2.0.4.0 - Production on Wed Feb 15 12:41:55 2017

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

Master table "ZHUL"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "ZHUL"."SYS_IMPORT_SCHEMA_01":  zhul/******** directory=zhul schemas=BMI dumpfile=ORCL_EXPDP_BMI_2017021501.DMP logfile=impdp_orcl_BMI_20170215.log

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "BMI"."DRUG_INSTRUCTION"                    4.409 GB   76521 rows

. . imported "BMI"."DW_BILLDETAIL":"P_DW_BILLDETAIL_201604"  665.6 MB 2872334 rows

. . imported "BMI"."DW_BILLDETAIL":"P_DW_BILLDETAIL_201701"  540.3 MB 2229265 rows

......

. . imported "BMI"."TMP_STA_BILL_PROBLEM_DETAIL"             0 KB       0 rows

. . imported "BMI"."TMP_STA_CLASSIFY_ILL_DETAIL"             0 KB       0 rows

ORA-39082: Object type PACKAGE_BODY:"BMI"."PKG_SUSPICIOUS_AMOUNT" created with compilation warnings

ORA-39082: Object type PACKAGE_BODY:"BMI"."PKG_SYNCDATA_PERMONTH" created with compilation warnings

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "ZHUL"."SYS_IMPORT_SCHEMA_01" completed with 14 error(s) at Wed Feb 15 13:51:00 2017 elapsed 0 01:09:04

总结：可能是底层存储比较差，集群光纤卡性能差，导致oracle的dmp文件在expdp或impdp时(适用dblink远程expdp时，该集群也是报ORA-31693&ORA-31640&ORA-19505&ORA-27037)

出现无法正确写或读取到dmp文件。以后再做类似工作的时候，要考虑并行是否适用。

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