数据泵导入报错impdp ora-31693 ora-31640 ora-19505 ora-27037

最新推荐文章于 2025-02-26 17:44:25 发布

cqq1799

最新推荐文章于 2025-02-26 17:44:25 发布

阅读量1.2k

点赞数

文章标签：数据库运维

在Oracle11.2.0.3 RAC上做数据导入时报错如下：

Starting "SYS"."SYS_IMPORT_FULL_03": "/******** AS SYSDBA" directory=dump dumpfile=full.dmp logfile=impdp_full.log exclude=statistics table_exists_action=truncate parallel=8 full=y
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"SYSAUX" already exists
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists

..............................
..............................
. . imported "DEV_OIM"."OIM_BLKLD_TMP_USER21" 897.7 KB 4997 rows
. . imported "DEV_OIM"."OIM_BLKLD_TMP_USER31" 827.7 KB 4498 rows
ORA-31693: Table data object "APEX_030200"."WWV_FLOW_STEP_BRANCHES" failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file "/exp/backup/full.dmp" for read
ORA-19505: failed to identify file "/exp/backup/full.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-31693: Table data object "APEX_030200"."WWV_FLOW_STEP_BUTTONS" failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file "/exp/backup/full.dmp" for read
ORA-19505: failed to identify file "/exp/backup/full.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-31693: Table data object "APEX_030200"."WWV_FLOW_TEMPLATES" failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file "/exp/backup/full.dmp" for read
ORA-19505: failed to identify file "/exp/backup/full.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-31693: Table data object "DEV_OIM"."UD_CR_USER" failed to load/unload and is being skipped due to error:
在mos上看了下，大意是说存在bug，需要补丁：

CAUSE

The cause of this problem has been identified in unpublished bug 9378256. It is caused by a RAC instance not having physical access to the dump file (as indicated by the DIRECTORY parameter) and using a parallel degree > 1. The problem with this setup is that the PQ/PX slaves are not constrained to the RAC instance from which the IMPDP operation has been started, and when such a PQ/PX slave doesn't have access to the dump file, the errors are raised.

The behavior is also reported in unpublished Bug 8415620 - DATA PUMP DOES NOT HONOR THE BOUNDARIES OF THE CONNECTED SERVICE ON RAC

DataPump RAC support is provided in 11.2. Prior to 11.2, once you use a service to make the initial connection to the database, an instance is selected and the master process and all worker processes run on that instance.

In 11.2, the new DataPump parameter CLUSTER is introduced:

针对该问题的解决方法是：

The issue has been fixed in the 11.2.0.2 patchset.

Possible workarounds for the 11.2.0.1 release are:

use the CLUSTER or SERVICE_NAME parameters to constrain the IMPDP job never to run on the instance not having access to the dump file
configure the database parameters in such a way that PQ/PX slaves are never started on the instance not having access to the dump file
use the PARALLEL=1 IMPDP command line parameter to disable parallel functionality during import
store all dump files on a local filesystem of one of the RAC nodes and use a parallel degree lower than the number of dump files and import with the CLUSTER=N command line option
mount the cluster filesystem on all nodes and set the parallel degree to be equal to the number of dump files and import with the CLUSTER=N command line option

但是按照这样的操作在做到如时仍然存在问题：
在： BUGRA CANBAKAL'S ORACLE BLOG上看到如下解决方案，操作处理ok。

RAC Impdp ORA-31693, ORA-29913, ORA-31640, ORA-19505, ORA-27037 fixing problems

06:42 AM By Bugra Canbakal 4 Comments

1.PURPOSE AND SCOPE

If the dumpfile located on non-shared area and dba try to start impdp operation with parallel onRAC servers (version 11gR2),dba would receive ORA-31693, ORA-29913, ORA-31640, ORA-19505, ORA-27037errors. The reason behind it oracle try to import using all RAC nodes for this operation and if the dumpfile is located on non-shared place , it could no reach it.

2.PRACTICE

2.1. Starting impdp operation.

 
           1 
         
           2 
         
           3 
         
           4 
         
           5 
         
           6 
         
           7 
         
           8 
         
           $ 
           impdp 
           bcanbakal 
          /bcanbakal 
            
          directory 
          = 
          exp 
            
          parallel 
          =8 
           dumpfile 
          =schmedump_%U.dmp 
            
          logfile 
          = 
          log:impdp. 
          log 
            
          full 
          =y 
            
           ORA 
          -31693: 
            
          Table 
           data 
            
          object 
            
          "ESK". 
          "FPP_WALT_MATCH" 
           failed 
            
          to 
           load 
          /unload 
            
          and 
            
          is 
           being 
           skipped 
           due 
            
          to 
           error: 
         
           ORA 
          -31640: 
           unable 
            
          to 
            
          open 
            
          dump 
            
          file 
            
          "/u01/app/exp/schmedump_08.dmp" 
            
          for 
            
          read 
         
           ORA 
          -19505: 
           failed 
            
          to 
           identify 
            
          file 
            
          "/u01/app/exp/schmedump_08.dmp" 
         
           ORA 
          -27037: 
           unable 
            
          to 
           obtain 
            
          file 
           status 
         
           Linux 
          -x86_64 
           Error: 
           2: 
           No 
           such 
            
          file 
            
          or 
            
          directory 
         
           Additional 
           information: 
           3

2.2. We can solve this problem with 3 ways.

2.2.1. We can move dump file to shared location like ASM.

2.2.2. We can add CLUSTER=N parameter so only one node would execute this import operation.

 
           1 
         
           $ 
           impdp 
           bcanbakal 
          /bcanbakal 
            
          directory 
          = 
          exp 
            
          parallel 
          =8 
           dumpfile 
          =schmedump_%U.dmp 
            
          logfile 
          = 
          log:impdp. 
          log 
            
          full 
          =y 
            
          CLUSTER 
          =N

2.2.3. When we parallel parameter oracle automaticly use CLUSTER=YES parameter at import operation so if you remove parallel parameter on impdp operation there wont be any issue..

 
           1 
         
           $ 
           impdp 
           bcanbakal 
          /bcanbakal 
            
          directory 
          = 
          exp 
            dumpfile 
          =schmedump_%U.dmp 
            
          logfile 
          = 
          log:impdp. 
          log 
            
          full 
          =y