Oracle數(shù)據(jù)泵(Data Dump)使用過(guò)程當(dāng)中經(jīng)常會(huì)遇到一些奇奇怪怪的錯(cuò)誤案例，下面總結(jié)一些自己使用數(shù)據(jù)泵(Data Dump)過(guò)程當(dāng)中遇到的問(wèn)題以及解決方法。都是在使用過(guò)程中遇到的問(wèn)題，以后陸續(xù)遇到數(shù)據(jù)泵(Data Dump)的錯(cuò)誤案例，都會(huì)補(bǔ)充在此篇。
錯(cuò)誤案例1：
ORA-39065： DISPATCH 中出現(xiàn)意外的主進(jìn)程異常錯(cuò)誤；ORA-44002: 對(duì)象名無(wú)效
Windows 平臺(tái)錯(cuò)誤提示：
Linux平臺(tái)錯(cuò)誤提示
解決方法：
[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.
執(zhí)行$ORACLE_HOME/rdbms/admin目錄下面的catmet2.sql，utlrp.sql 兩個(gè)SQL文件即可解決這個(gè)問(wèn)題。
[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
錯(cuò)誤案例2：
使用expdp/impdp時(shí)遭遇ORA-39006: internal error;ORA-39213: Metadata processing is notavailable錯(cuò)誤
[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
解決方法：
錯(cuò)誤原因如下所示，因?yàn)镈ata Pump不能使用Metadata API， 這個(gè)是因?yàn)閄SL stylesheets 沒(méi)有正確設(shè)置緣故。需要以SYSDBA執(zhí)行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
錯(cuò)誤案例3：
錯(cuò)誤如下所示：
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
解決方法：
出現(xiàn)這個(gè)錯(cuò)誤，需要檢查DIRECTORY目錄,創(chuàng)建DIRECTORY時(shí)，ORACLE并不檢查操作系統(tǒng)目錄是否已經(jīng)存在
首先檢查DIRECTORY目錄DUMP_TEST對(duì)應(yīng)的DIRECTORY_PATH，然后檢查操作系統(tǒng)下，驗(yàn)證該目錄是否存在。
SQL> SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME='DUMP_TEST';
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------ ------------------------------ -----------------
SYS DUMP_TEST /jkfile/klbtmp/
SQL>
結(jié)果檢查發(fā)現(xiàn)該目錄被刪除了，創(chuàng)建該目錄即可解決問(wèn)題。
另外一種情況，如果創(chuàng)建DIRECTORY目錄時(shí)，出現(xiàn)了換行，也會(huì)出現(xiàn)上面錯(cuò)誤信息,這種錯(cuò)誤玩玩很難發(fā)現(xiàn)，非常折騰人。所以需要非常細(xì)心。
SQL> create directory DUMP_TEST as '/jkfile/klbtmp/
';
錯(cuò)誤案例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賬號(hào)缺少exp_full_database 權(quán)限，給etl賬號(hào)授予exp_full_database 權(quán)限后，上述問(wèn)題解決。
# 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.
錯(cuò)誤案例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";
錯(cuò)誤案例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:
請(qǐng)注意，在ORACLE 10g下 COMPRESSION只有METADATA_ONLY和NONE兩個(gè)選項(xiàng)，ORACLE 11g下才有DATA_ONLY選項(xiàng)。所以報(bào)如上錯(cuò)誤。所以在使用前，請(qǐng)注意一下EXPDP工具的版本。
錯(cuò)誤案例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
解決方法：
這個(gè)案例我以前已經(jīng)介紹過(guò)，具體參考我的博客 Expdp 導(dǎo)數(shù)錯(cuò)誤 ORA-00832
錯(cuò)誤案例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
解決方法：
這個(gè)案例有點(diǎn)特別，剛遇到的時(shí)候，確實(shí)有點(diǎn)莫名其妙，經(jīng)過(guò)苦苦求索、求證后才發(fā)現(xiàn)，本來(lái)有一個(gè)TOOLS的表空間，不知道是哪位頭腦發(fā)熱的人，居然指定SYSTEM用戶的默認(rèn)表空間為T(mén)OOLS，但是這個(gè)表空間又被人刪除了。于是便有了這樣一個(gè)案例。
SQL> ALTER USER SYSTEM DEFAULT TABLESPACE SYSTEM;
User altered.
修改用戶SYSTEM的默認(rèn)表空間后，問(wèn)題解決。
您可能感興趣的文章:- Oracle數(shù)據(jù)泵的導(dǎo)入與導(dǎo)出實(shí)例詳解
