sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle报错ORA-39000 ORA-31640 ORA-27037的解决办法

时间:2016-10-25 21:27   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心案例分析:运维DBA反映在巡检Oracle数据库时发现alert日志文件中出现ORA-39000 ORA-31640 ORA-27037报错,结合MOS官方文档总结解决办法。
How to Prevent ORA-39000 ORA-31640 ORA-27037 Errors When Performing DataPump Export/Import (文档 ID 784566.1) 

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


Errors ORA-31640 ORA-27054 Encountered With DataPump Import (IMPDP) Involving NFS (文档 ID 462652.1) 转到底部转到底部
修改时间:2014-6-10类型:PROBLEM
为此文档评级 通过电子邮件发送此文档的链接 在新窗口中打开文档 可打印页

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"
How To Import From Multiple Directories Using IMPDP With The Substitution Variable (%U) And Avoid The Error ORA-31640 (文档 ID 1491656.1) 转到底部转到底部
修改时间:2012-9-26类型:HOWTO
为此文档评级 通过电子邮件发送此文档的链接 在新窗口中打开文档 可打印页

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.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 | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-39000 ORA-31640 ORA-27037的解决办法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1067.html

Oracle研究中心

关键词:

ORA-27037

ORA-39000

ORA-31640

ORA-39000 ORA-31640 ORA-27037产生原因和官方解决方法