expdp ora-31640 ora-19505，expdpora-31640

expdp ora-31640 ora-19505，expdpora-31640
oracle@db01:/oraapp/oracle/backup>impdp \'/ as sysdba\' directory=DB_BACKUP dumpfile=newdb.20140918.dmp logfile=newdb_0919.log parallel=4 c>
Import: Release 11.2.0.3.0 - Production on Fri Sep 19 11:27:12 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsMaster table "SYS"."SYS_IMPORT_FULL_04" successfully loaded/unloadedStarting "SYS"."SYS_IMPORT_FULL_04":  "/******** AS SYSDBA" directory=DB_BACKUP dumpfile=newdb.20140918.dmp logfile=newdb_0919.log parallel=4 content=all full=y Processing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/TABLESPACE_QUOTAProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCEProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATAORA-31693: Table data object "H1"."TRDX_FEE_TABLE" failed to load/unload and is being skipped due to error:ORA-31640: unable to open dump file "/oraapp/oracle/backup/newdb.20140918.dmp" for readORA-19505: failed to identify file "/oraapp/oracle/backup/newdb.20140918.dmp"ORA-27037: unable to obtain file statusIBM AIX RISC System/6000 Error: 2: No such file or directoryAdditional information: 3ORA-31693: Table data object "H1"."TRDX_Q" failed to load/unload and is being skipped due to error:ORA-31640: unable to open dump file "/oraapp/oracle/backup/newdb.20140918.dmp" for readORA-19505: failed to identify file "/oraapp/oracle/backup/newdb.20140918.dmp"ORA-27037: unable to obtain file statusIBM AIX RISC System/6000 Error: 2: No such file or directoryAdditional information: 3ORA-31693: Table data object "H1"."TRDX_OI" failed to load/unload and is being skipped due to error:ORA-31640: unable to open dump file "/oraapp/oracle/backup/newdb.20140918.dmp" for readORA-19505: failed to identify file "/oraapp/oracle/backup/newdb.20140918.dmp"ORA-27037: unable to obtain file statusIBM AIX RISC System/6000 Error: 2: No such file or directoryAdditional information: 3ORA-31693: Table data object "H1"."TRDX_Q" failed to load/unload and is being skipped due to error:ORA-31640: unable to open dump file "/oraapp/oracle/backup/newdb.20140918.dmp" for readORA-19505: failed to identify file "/oraapp/oracle/backup/newdb.20140918.dmp"ORA-27037: unable to obtain file statusIBM AIX RISC System/6000 Error: 2: No such file or directoryAdditional information: 3
Import> exit

SQL> select * from v$version;
BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE    11.2.0.3.0      ProductionTNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - Production
SQL> 
使用expdp匯入資料到Rac環境，parallel 並行為4，發現匯入報以上錯誤，分析發現Rac環境使用並行匯入時會嘗同時寫資料庫到兩個節點，如果目錄在本地磁碟，則匯入時候會報此錯誤。（匯出時也存在此問題）解決辦法是在匯出匯入的時候加上cluster參數等於N進行單節點匯入操作。即：impdp \'/ as sysdba\'   directory=db_backup dumpfile=newdb.dmp logfile=newdb.log  parallel=4 content=all full=y cluster=n



. . imported "H1"."TRDX_OC"     5.950 MB   60692 rows. . imported "H1"."TRDX_Q"         4.643 MB   15728 rowsORA-31693: Table data object "H1"."TRDX_P" failed to load/unload and is being skipped due to error:ORA-02354: error in exporting/importing dataORA-12081: update operation not allowed on table "H1"."TRDX_P". . imported "H1"."TRDX_QU"         5.687 MB   18639 rowsORA-31693: Table data object "H1"."TRDX_HL" failed to load/unload and is being skipped due to error:ORA-02354: error in exporting/importing dataORA-12081: update operation not allowed on table "H1"."TRDX_HLD". . imported "H1"."TRDX_API_TX"           5.079 MB   48360 rows. . imported "H1"."TRDX_DE"     4.361 MB   91505 rowsORA-31693: Table data object "H1"."TRDX_ASSE" failed to load/unload and is being skipped due to error:ORA-02354: error in exporting/importing dataORA-12081: update operation not allowed on table "H1"."TRDX_ASSET"

匯入的同時出現以上錯誤，根據Mos [Doc 1270847.1]描述是由於這些表上存在物化視圖，建議使用TABLE_EXISTS_ACTION=APPEND參數匯入資料；此參數預設是SKIP。

oracle impdp 問題

你在資料庫裡建立recoverpath這個目錄了嗎？
SQL> Create Or Replace Directory 'recoverpath' as '/data/recover';
SQL>Grant read,write on Directory 'recoverpath' to user;