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之多,裡邊有大表,加上客戶方對時間要求比較緊,所以使用如下命令並行匯入:
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檔案。以後再做類似工作的時候,要考慮並行是否適用。
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檔案。以後再做類似工作的時候,要考慮並行是否適用。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29357786/viewspace-2133575/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle expdp and impdpOracle
- oracle expdp/impdp用法Oracle
- ORACLE expdp/impdp詳解Oracle
- Oracle expdp/impdp 使用示例Oracle
- oracle expdp和impdp使用例子Oracle
- ORACLE 10G expdp/impdpOracle 10g
- Oracle10g expdp & impdpOracle
- oracle資料庫的impdp,expdpOracle資料庫
- oracle EXPDP/IMPDP 常用命令Oracle
- Oracle 邏輯備份 expdp/impdpOracle
- expdp&impdp For Oracle 10GOracle 10g
- exp/imp與expdp/impdp區別
- impdp/expdp 示例
- oracle匯入匯出之expdp/impdpOracle
- Oracle expdp impdp dump引數介紹Oracle
- Oracle 傳輸表空間-EXPDP/IMPDPOracle
- ORACLE EXPDP IMPDP 中停止和啟動Oracle
- oracle 10g imp/exp IMPDP/EXPDPOracle 10g
- oracle實驗記錄 (expdp/impdp使用)Oracle
- exp/expdp與imp/impdp的區別
- 【EXPDP/IMPDP】使用 EXPDP/IMPDP工具“模糊”匯出和匯入
- oracle 10g expdp-impdp解讀Oracle 10g
- oracle實驗記錄 (transport tablespace(EXPDP/IMPDP))Oracle
- Expdp Impdp詳解
- expdp與impdp全庫匯出匯入
- EXPDP/IMPDP任務的檢視與管理
- Oracle資料泵匯出匯入(expdp/impdp)Oracle
- EXPDP/IMPDP工具的使用
- expdp/impdp 用法詳解
- expdp/impdp操作例項
- expdp, impdp characteristic 特性--索引索引
- Expdp,impdp工具的使用
- 將partition expdp後impdp
- expdp/impdp用法詳解
- expdp/impdp 使用總結
- expdp與impdp全庫匯出匯入(二)
- ORACLE EXPDP IMPDP 的停止和啟動及監控Oracle
- Oracle使用資料泵 (expdp/impdp)實施遷移Oracle