程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle數據泵(Data Dump)錯誤匯集，oracledump

Oracle數據泵(Data Dump)錯誤匯集，oracledump

編輯：Oracle教程

Oracle數據泵(Data Dump)錯誤匯集，oracledump

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

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

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

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

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

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

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

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的默認表空間後，問題解決。

對於ORACLE數據庫數據泵的使用問題

exp hr/hr@ORCL owner=hr file=hr_db_20141017.dmp log=hr_db_20141017.log
imp hr/hr file=hr_db_20141017.dmp log=hr_db_20141017.log full=y

樓上的正解，你不需要導出一個庫中所有帳戶的數據，只需要導出某一個你需要的帳戶即可。