当前位置: 首页 > ORA > 正文

ORA-39000 ORA-31640 ORA-27037

转到底部转到底部

2013-10-22HOWTO
为此文档评级 通过电子邮件发送此文档的链接 在新窗口中打开文档 可打印页

In this Document

  Goal
  Solution
  References

APPLIES TO:

Oracle Database – Enterprise Edition – Version 10.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 22-Oct-2013***

GOAL

This article documents a resolution for errors ORA-39000, ORA-31640 and ORA-27037 when performing DataPump export/import.

DataPump Import can fail with the following errors:

Import: Release 10.2.0.1.0 – Production on Friday, 30 January, 2009 15:10:33 

Copyright (c) 2003, 2005, Oracle. All rights reserved. 
;;; 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production 
With the Partitioning, OLAP and Data Mining options 
ORA-39000: bad dump file specification 
ORA-31640: unable to open dump file "/oracle/u01/app/oracle/oracle/product/10.2.0/db_2/admin/dpdump/expdat.dmp" for read 
ORA-27037: unable to obtain file status 
Linux Error: 2: No such file or directory 
Additional information: 3

SOLUTION

The parameter DIRECTORY specifies the location to which the DataPump Export or Import  is not properly created or having permission issues.

Drop  and re-create a new oracle directory object and change the expdp/impdp command to point to the new directory to resolve this issue.  You must have the DBA Privilege to create a Directory.

For example to create a directory object named expdp_dir located at /u01/backup/exports enter the following sql statement:

SQL> drop directory expdp_dir;
SQL> create directory expdp_dir as ‘/u01/backup/exports’;

Then grant read and write permissions to the users who will be performing the data pump export and import.

SQL> grant read, write on directory expdp_dir to system, user1, user2, user3;

REFERENCES

NOTE:266875.1 – Export/Import DataPump Parameter DIRECTORY – How to Specify a Directory

转到底部转到底部

2014-6-10PROBLEM
为此文档评级 通过电子邮件发送此文档的链接 在新窗口中打开文档 可打印页

In this Document

  Symptoms
  Cause
  Solution

APPLIES TO:

Oracle Database – Enterprise Edition – Version 10.2.0.1 to 11.2.0.4 [Release 10.2 to 11.2]
IBM AIX on POWER Systems (64-bit)
***Checked for relevance on 10-Jun-2014***

SYMPTOMS

The following errors are reported while trying to import from an NFS mount point, even if the file system is mounted properly.

ORA-31640: Unable to open dump file
ORA-27054: NFS file system where the file is created or resides is not mounted

CAUSE

From IBM AIX documentation, it is clear that all mount point information is supposed to be present in the ‘/etc/filesystems’ file located on the system. When mount information is not present in ‘/etc/filesystems’ file it can lead to abnormalities.

SOLUTION

The following steps helps in eliminating the above error during import:

1. Ensure the filesystem is mounted with the required mount options. I.e. rsize=32k, wsize=32k, hard, actimeo=0

2. Set the following event in the init.ora, so that disable the mount point parameter checking:

event="10298 trace name context forever, level 32"

转到底部转到底部

2012-9-26HOWTO
为此文档评级 通过电子邮件发送此文档的链接 在新窗口中打开文档 可打印页

In this Document

  Goal
  Fix

APPLIES TO:

Oracle Server – Enterprise Edition – Version 10.2.0.4 to 11.2.0.3 [Release 10.2 to 11.2]
Information in this document applies to any platform.

GOAL

DataPump import (IMPDP) from multiple directories using the substitution variable (%U) fails with:

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/opt/oracle/product/temp/test_01.dmp" for read
ORA-27037: unable to obtain file status

How to import from multiple directories using the substitution variable (%U) ?

FIX

This is an expected behavior when using multiple dump files with %U. Follow the simplified example below to understand the DataPump behavior for this scenario:

1. Run the export using test_%U.dmp for dumpfile:

#> expdp system/password dumpfile=testdp1:test_%U.dmp filesize=20M schemas=test_dir logfile=testdp1:logfile.log

 

Export: Release 10.2.0.5.0 – 64bit Production on Tuesday, 17 July, 2012 17:19:06

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_02":  system/******** dumpfile=testdp1:test_%U.dmp filesize=20M schemas=test_dir logfile=testdp1:logfile.log
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 176.2 MB
…….
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:
/oracle/users/testdp1/test_01.dmp
/oracle/users/testdp1/test_02.dmp
/oracle/users/testdp1/test_03.dmp                    –> The dump files are located in directory TESTDP1
/oracle/users/testdp1/test_04.dmp                    –> which points to /oracle/users/testdp1
/oracle/users/testdp1/test_05.dmp
/oracle/users/testdp1/test_06.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully completed at 17:19:16

2. Move the dump files test_04.dmp, test_05.dmp and test_06.dmp to directory TESTDP2 (which points to /oracle/users/testdp2):

#> mv /oracle/users/testdp1/test_04.dmp /oracle/users/testdp2/test_04.dmp
#> mv /oracle/users/testdp1/test_05.dmp /oracle/users/testdp2/test_05.dmp
#> mv /oracle/users/testdp1/test_06.dmp /oracle/users/testdp2/test_06.dmp

3. After transferring some of the dump files, the directories TESTDP1 and TESTDP2 contain:

#> ls -l /oracle/users/testdp1/*
total 61516
-rw-r—– 1 oracle oinstall 20971520 Jul 17 17:19 test_01.dmp
-rw-r—– 1 oracle oinstall 20971520 Jul 17 17:19 test_02.dmp
-rw-r—– 1 oracle oinstall 20971520 Jul 17 17:19 test_03.dmp

#> ls -l /oracle/users/testdp2/*
total 60192
-rw-r—– 1 oracle oinstall 20971520 Jul 17 17:19 test_05.dmp
-rw-r—– 1 oracle oinstall 20971520 Jul 17 17:19 test_04.dmp
-rw-r—– 1 oracle oinstall 19619840 Jul 17 17:19 test_06.dmp

3. Run the import using the substitution variable %U for dump files, e.g:

#> impdp system/password dumpfile=testdp1:test_%U.dmp,testdp2:test_%U.dmp schemas=testdir logfile=testdp1:test.log

 

Import: Release 10.2.0.5.0 – 64bit Production on Tuesday, 17 July, 2012 17:24:31
Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/oracle/users/testdp2/test_01.dmp" for read
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

The DataPump utility expects the dump file test_01.dmp in directory TESTDP2 directory, too (expected behavior). When %U is used, the DataPump Utility starts to search for the first dump file (in this case test_01.dmp) in all the directories specified in the DataPump command line. In this particular case, the dump files: test_01.dmp, test_02.dmp,….test_0<n>.dmp must exist in both directories TESTDP1 and TESTDP2 when running the import.

To avoid the errors ORA-31640 and ORA-27037 errors when multiple directories with the substitution variable %U are used, the following options are available:

A. In the source database, create the directories TESTDP1, TESTDP2, TESTDP3, etc and run expdp with:

DUMPFILE=TESTDP1:test_%U.dmp,TESTDP2:test_%U.dmp,TESTDP3:test_%U.dmp

Then move the generated dump files exactly as they are generated to directories TESTDP1, TESTDP2, TESTDP3, etc in the target environment.

Or:

B. Instead of running impdp with substitution variable %U and multiple directories, explicitly specify the dump files:

dumpfile=TESTDP1:test_01.dmp,TESTDP2:test_02.dmp,TESTDP3:test_03.dmp

本文固定链接: http://www.htz.pw/2014/06/16/ora-39000-ora-31640-ora-27037.html | 认真就输

该日志由 huangtingzhong 于2014年06月16日发表在 ORA 分类下, 通告目前不可用,你可以至底部留下评论。
原创文章转载请注明: ORA-39000 ORA-31640 ORA-27037 | 认真就输
关键字: , ,