导致ORA-39095的两种原因(Expdp)

今天在群里帮网友处理了一个expdp导出的问题。处理过程如下：

一、搜集报错信息和背景信息

1、报错的截图如下:

2、该网友使用的脚本内容如下：

expdp ceshi/ceshi@odpsceshi directory=expdp_backup dumpfile=ceshi_%u.dmp filesize=1G parallel=5 full=y

创建dir的命令：

 create directory expdp_backup as ’H:/expdp_backup’

h盘还有200多G，而这个库的大小只有10G左右

二、问题分析及解决办法

1、先看下官方的解释：

ORA-39095  ump
file space has been exhausted: Unable to allocate string bytes
Cause:
The Export job ran out of dump file space before the job was completed.
Action:
Reattach to the job and add additional dump files to the job restarting the job.

这段话的意思是说%U的数目与filesize的乘积数值仍然小于导出数据实际大小，也就是空间不足的问题。很明显，99*1G远远大于10G，我们这里不是这个原因导致的。

2、继续往上google，发现很多类似错误。其中fenng的博客上有这么一段话说得较清楚（源出处为Oracle Database Utilities官方文档）：

http://dbanotes.net/database/_oracle_10gr2_ora-39095_.html

Because each active worker process or I/O server process writes exclusively to one file at a
time, an insufficient number of files can have adverse effects. Some of the worker processes will be idle while waiting for files, thereby degrading the overall performance of the job. More
importantly, if any member of a cooperating group of parallel I/O server processes cannot obtain a file for output, then the export operation will be stopped with an ORA-39095 error.
Both situations can be corrected by attaching to the job using the Data Pump Export utility, adding more files using the ADD_FILE command while in interactive mode, and in the case of a stopped job, restarting the job.

翻译过来意思是：

1）负责写数据到dumpfile文件的进程叫parallel io server processes，而安全机制不允许多个写进程同时写一个dumpfile文件。

2）如果只有一个dumpfile(或少于parallel)就会影响性能，实际上大多数时候，对于小数据库而言，一个dumpfile也无所谓。

3）由于1）中提到的规则，如果此时你的parallel数值大于（实际导出数据量/fileszie）的数值，那么就会有一些进程闲置，他们不能去干活，只能等待。   当一个io server process在不能写dumpfile而不断等待的时候就会报ORA-39095。

3、问题搞清楚了，剩下就好办了，解决办法如下：

parallel<=导出数据量/fileszie

为了实现目的，可以减小parallel，也可以减小filesize。

1）最简单的办法是，减小并行数至1，即修改parallel数值，该值可用范围为1~n.发现不再报错了，但是如果数据量太大，再调整嘛。   最好能计算出实际数据量，可以计算出需要的并行数。

2）减小filesize大小，这个也跟数据量有关的。不废话了。

————————————————————————————————————————————————————

附件： %U参数

附上MOS上查到的相关资料，是关于%U参数所指定的99个dumpfile都不够用的情况下在增加数目的解决办法

Cause

You limited the number of files in DUMPFILE parameter with limited FILESIZE , i.e.:

full=Y
directory=dpump_dir
dumpfile=full_1.dmp,full_2.dmp,full_3.dmp,full_4.dmp
filesize=3000000000

This means max. 4 dump files with file size 3000000000 bytes (4×2.79 GB).

Solution
1. Use the dynamic format  (i.e dumpfile=full_%U.dmp) :

The ‘wildcard’ specification for the dump file can expand up to 99 files. If 99 files have been generated before the export has completed, it will again return the ORA-39095 error.

2. If this is yet not enough and more files are needed, a workaround would be to speciyy a bigger FILESIZE parameter.

3. If this is inconvenient, then another option is to use this syntax:

dumpfile=fullexp%U.dmp, fullexp2_%U.dmp, fullexp3_%U.dmp
which can expand up to 3*99 files.

If encountering problems containing the dump in a single directory using this solution, you may prefer this syntax:

dumpfile=dmpdir1:fullexp1_%U.dmp, dmpdir2:fullexp2_%U.dmp, dmpdir3:fullexp3_U.dmp

附件二：

MOS上查到的跟问题相关的文档

Parallel Export Fails With Error ORA-39095 If There Are Less Specified Files Than Slaves [ID 433391.1]
转到底部
修改时间:2012-5-15类型:PROBLEM状态:PUBLISHED优先级:3
注释 (0)

In this Document
Symptoms
Cause
Solution
References
Applies to:

Oracle Server – Enterprise Edition – Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 15-MAY-2012***
Symptoms

When trying to export using PARALLEL clause with one dumpfile, or a number of dumpfile less than the parallelism value.

The export may fail with the following error:

ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes
Cause

When using one dumpfile or a number less than parallelism value, several slave processes wait for the file locked by the other process to write. And so we are not benefiting from the parallelism anyway.Sometimes the slave process locking the file does not release
the lock after finishing as it’s supposed to release the lock when the dump process ends, and it’ll not end cause the other processes are waiting to write to the file. 

Solution

1. Use a number of dump files equal to, or more than the parallelism value.

Or:

2. Don’t use PARALLEL clause.

References

@ BUG:3065578 – PARALLEL EXPORT FAILS IF THERE ARE LESS SPECIFIED FILES THAN SLAVES

 
相关内容
 
 
产品
 
Oracle Database Products > Oracle Database > Oracle Database > Oracle Database – Enterprise Edition > Export
 
关键字
 
DUMP FILE;EXPDP;EXPORT;PARALLELISM
 
错误
 
ORA-39095

原文自：http://www.enkj.com/help/newscontent/95566