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的默认表空间后,问题解决。