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。
    今天,安装完了衡阳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之多,里边有大表,加上客户方对时间要求比较紧,所以使用如下命令并行导入:
impdp zhul/******** 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文件。以后再做类似工作的时候,要考虑并行是否适用。














目录
相关文章
|
存储 SQL Oracle
Oracle使用expdp/impdp实现全库导入导出的整体流程
Oracle的全库导入,首先一点必须先创建数据库,创建了数据库,才能往该数据库导入所有数据。相对来说,使用Oracle进行数据导入导出还很有些“麻烦”的,大多数资料上来就是......
6731 0
Oracle使用expdp/impdp实现全库导入导出的整体流程
|
10月前
|
Oracle 关系型数据库 数据库
通过数据泵expdp、impdp方式备份与还原(导出与导入)Oracle数据库
通过数据泵expdp、impdp方式备份与还原(导出与导入)Oracle数据库
|
SQL Oracle 关系型数据库
Oracle-数据泵expdp/impdp实操
Oracle-数据泵expdp/impdp实操
286 0
|
Oracle 关系型数据库 数据库
Oracle expdp impdp导出导入命令及数据库备份
Oracle expdp impdp导出导入命令及数据库备份
254 0
|
Oracle 关系型数据库
oracle表数据量太大,expdp只导出部分
oracle表数据量太大,expdp只导出部分
164 0
|
Oracle 关系型数据库 OLAP
Oracle 11.2.0.4数据泵expdp导出报DBMS_AW_EXP等信息
Oracle 11.2.0.4数据泵expdp导出报DBMS_AW_EXP等信息
285 0
|
Oracle 关系型数据库 数据库
ORACLE的EXPDP与ORA-31626、ORA-31637、ORA-06512、ORA-31635
    2015年4月24日,上地的增值业务综合网管系统ORACLE的EXPDP又出错了,系统负责人反映从4月20开始到现在该系统的expdp没有备份文件,相关的处理过程如下。
3639 0
|
14天前
|
Oracle 关系型数据库 数据库
已解决:idea 连接 oracle 数据库 避雷
已解决:idea 连接 oracle 数据库 避雷
|
3天前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用问题之在进行Oracle数据库的全量同步时,只同步了一条数据而源表实际上包含多条数据,是什么原因
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。