Oracle数据泵(Data Dump)使用过程当中经常会遇到一些奇奇怪怪的错误案例

oracle数据泵(data dump)使用过程当中经常会遇到一些奇奇怪怪的错误案例，下面总结一些自己使用数据泵(data dump)过程当中遇到的问题以及解决方法。都是在使用过程中遇到的问题，以后陆续遇到数据泵(data dump)的错误案例，都会补充在此篇。

错误案例1：

ora-39065： dispatch 中出现意外的主进程异常错误；ora-44002: 对象名无效

windows 平台错误提示：

linux平台错误提示

解决方法：

[oracle@db-server admin]$ oerr ora 39065
39065, 00000, "unexpected master process exception in %s"
// *cause: an unhandled exception was detected internally within the master
// control process for the data pump job. this is an internal error.
// messages will detail the problems.
// *action: if problem persists, contact oracle customer support.
[oracle@db-server admin]$ oerr ora 39097
39097, 00000, "data pump job encountered unexpected error %s"
// *cause: an unexpected, potentially non-fatal error occurred while
// processing a data pump job.
// *action: contact oracle customer support.

执行$oracle_home/rdbms/admin目录下面的catmet2.sql，utlrp.sql 两个sql文件即可解决这个问题。

[oracle@db-server ~]$ cd $oracle_home/rdbms/admin
 
[oracle@db-server admin]$ sqlplus / as sysdba
 
sql*plus: release 10.2.0.1.0 - production on sun sep 7 08:24:23 2014
 
copyright (c) 1982, 2005, oracle. all rights reserved.
 
connected to an idle instance.
 
sql> @catmet2.sql 
 
sql> @utlrp.sql 

错误案例2：

使用expdp/impdp时遭遇ora-39006: internal error;ora-39213: metadata processing is notavailable错误

[oracle@db-server backup]$ impdp frnt/frnt directory=dum_dir dumpfile=20120420.frnt_02.dmp tablespaces=tbs_tr_data;

import: release 10.2.0.1.0 - 64bit production on wednesday, 25 april, 2012 14:41:48

copyright (c) 2003, 2005, oracle. all rights reserved.

connected to: oracle database 10g enterprise edition release 10.2.0.1.0 - 64bit production
with the partitioning, olap and data mining options
ora-39006: internal error
ora-39213: metadata processing is not available

解决方法：

错误原因如下所示，因为data pump不能使用metadata api， 这个是因为xsl stylesheets 没有正确设置缘故。需要以sysdba执行dbms_metadata_util.load_stylesheets

[oracle@db-server admin]$ oerr ora 39213
 
39213, 00000, "metadata processing is not available"
 
// *cause: the data pump could not use the metadata api. typically,
 
// this is caused by the xsl stylesheets not being set up properly.
 
// *action: connect as sysdba and execute dbms_metadata_util.load_stylesheets
 
// to reload the stylesheets.

sql>exec dbms_metadata_util.load_stylesheets

错误案例3：

错误如下所示：

etl@nms_ods_ndb1[/jkfile/klbtmp]#expdp userid=username/password@tnsname dumpfile=ref.dmp directory=dump_test schemas=ref content=all logfile=ref.log 

export: release 10.2.0.3.0 - 64bit production on saturday, 02 march, 2013 10:28:25 

copyright (c) 2003, 2005, oracle. all rights reserved. 

connected to: oracle database 10g enterprise edition release 10.2.0.3.0 - 64bit production 

with the partitioning, real application clusters, olap and data mining options 

ora-39002: invalid operation 

ora-39070: unable to open the log file. 

ora-29283: invalid file operation 

ora-06512: at "sys.utl_file", line 475 

ora-29283: invalid file operation 

解决方法：

出现这个错误，需要检查directory目录,创建directory时，oracle并不检查操作系统目录是否已经存在

首先检查directory目录dump_test对应的directory_path，然后检查操作系统下，验证该目录是否存在。

sql> select * from dba_directories where directory_name='dump_test';
 
owner              directory_name      directory_path
 
------------------------ ------------------------------ -----------------
 
sys                dump_test       /jkfile/klbtmp/
 
sql> 

结果检查发现该目录被删除了，创建该目录即可解决问题。

另外一种情况，如果创建directory目录时，出现了换行，也会出现上面错误信息,这种错误玩玩很难发现，非常折腾人。所以需要非常细心。

sql> create directory dump_test as '/jkfile/klbtmp/

';

错误案例4：

如下所示：

etl@nms_ods_ndb1[/jkfile/work/klb]#expdp userid=etl/sunrise@wgods1 dumpfile=ref.dmp directory=dump_dir schemas=ref content=all logfile=ref.log 

export: release 10.2.0.3.0 - 64bit production on saturday, 02 march, 2013 10:50:25 

copyright (c) 2003, 2005, oracle. all rights reserved. 

connected to: oracle database 10g enterprise edition release 10.2.0.3.0 - 64bit production 

with the partitioning, real application clusters, olap and data mining options 

ora-31631: privileges are required 

ora-39109: unprivileged users may not operate upon other users' schemas 

解决方法：

主要是etl账号缺少exp_full_database 权限，给etl账号授予exp_full_database 权限后，上述问题解决。

# su - oracle 

$ sqlplus / as sysdba 

sql*plus: release 10.2.0.3.0 - production on sat mar 2 10:58:37 2013 

copyright (c) 1982, 2006, oracle. all rights reserved. 

connected to: 

oracle database 10g enterprise edition release 10.2.0.3.0 - 64bit production 

with the partitioning, real application clusters, olap and data mining options 

sql> grant exp_full_database to etl; 

grant succeeded. 

错误案例5：

etl@nms_ods_ndb1[/etl1_cx/etl]expdp etl/sunrise@wgods1 dumpfile=dm.tm_alarm_log.dmp directory=dump_dir tables=dm.tm_alarm_log query='date_cd >=20121201 and date_cd <=20130131'; 

lrm-00101: unknown parameter name '>' 

解决方法：

etl@nms_ods_ndb1[/etl1_cx/etl]expdp etl/sunrise@wgods1 dumpfile=dm.tm_alarm_log.dmp directory=dump_dir tables=dm.tm_alarm_log query="date_cd \>\=20121201 and date_cd \<\=20130131"; 

错误案例6：

lrm-00121: 'data_only' is not an allowable value for 'compression'。如下所示：

etl@nms_ods_ndb1[/etl1_cx/etl]#expdp etl/sunrise@wgods1 dumpfile=ref.dmp directory=dump_dir dumpfile=dm.tm_alarm_log201212.dmp tables=dm.tm_alarm_log compression=data_only query='date_cd >=20121201 and date_cd <=20130131'; 

lrm-00121: 'data_only' is not an allowable value for 'compression' 

解决方法：

首先查看expdp工具的版本，如下所示：

etl@nms_ods_ndb1[/etl1_cx/etl]#expdp version 

export: release 10.2.0.3.0 - 64bit production on monday, 04 march, 2013 14:46:47 

copyright (c) 2003, 2005, oracle. all rights reserved. 

password: 

请注意，在oracle 10g下 compression只有metadata_only和none两个选项，oracle 11g下才有data_only选项。所以报如上错误。所以在使用前，请注意一下expdp工具的版本。

错误案例7：

[oracle@db-server]$ expdp system/***** tables=invenrty.inv_stk_hd dumpfile=inv_stk_hd.dmp logfile=1.log directory=cur_dump_dir 

export: release 10.2.0.4.0 - production on sunday, 14 july, 2013 8:27:16 

copyright (c) 2003, 2007, oracle. all rights reserved. 

connected to: oracle database 10g release 10.2.0.4.0 - production 

ora-31626: job does not exist 

ora-31637: cannot create job sys_export_table_01 for user system 

ora-06512: at "sys.dbms_sys_error", line 95 

ora-06512: at "sys.kupv$ft_int", line 600 

ora-39080: failed to create queues "kupc$c_1_20130714082716" and "kupc$s_1_20130714082716" for data pump job 

ora-06512: at "sys.dbms_sys_error", line 95 

ora-06512: at "sys.kupc$que_int", line 1606 

ora-00832: no streams pool created and cannot automatically create one 

解决方法：

这个案例我以前已经介绍过，具体参考我的博客 expdp 导数错误 ora-00832

错误案例8：

[oracle@testlnx01 u03]$ cd tmp/ 

[oracle@testlnx01 tmp]$ expdp system/***** directory=dumpdir dumpfile=escmuser.dmp schemas=escmuser logfile=escmuser.log 

export: release 10.2.0.4.0 - 64bit production on wednesday, 27 august, 2014 16:30:46 

copyright (c) 2003, 2007, oracle. all rights reserved. 

connected to: oracle database 10g release 10.2.0.4.0 - 64bit production 

ora-31626: job does not exist 

ora-31633: unable to create master table "system.sys_export_schema_05" 

ora-06512: at "sys.dbms_sys_error", line 95 

ora-06512: at "sys.kupv$ft", line 871 

ora-00959: tablespace 'tools' does not exist 

解决方法：

这个案例有点特别，刚遇到的时候，确实有点莫名其妙，经过苦苦求索、求证后才发现，本来有一个tools的表空间，不知道是哪位头脑发热的人，居然指定system用户的默认表空间为tools，但是这个表空间又被人删除了。于是便有了这样一个案例。

sql> alter user system default tablespace system; 

user altered. 

修改用户system的默认表空间后，问题解决。