今天早上檢查資料庫的備份日誌，發現其中一個資料庫的expdp錯誤：

[oracle@bj oracle]$ /home/oracle/backup/sh/backup_expdp.sh

Export: Release 10.1.0.3.0 - Production on Tuesday, 29 March, 2005 9:58

Copyright (c) 2003, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.CASES_EXPORT"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 701
ORA-00955: name is already used by an existing object

local: backup_expdp_Tue.dmp: No such file or directory
local: backup_expdp_Tue.log: No such file or directory

上面提到的錯誤號：
ORA-31626: job does not exist
Cause: An invalid reference to a job which is no longer executing, is not executing on the instance where the operation was attempted, or that does not have a valid Master Table. Refer to any following error messages for clarification.
Action: Start a new job, or attach to an existing job that has a valid Master Table.

ORA-31633: unable to create master table "string.string"
Cause: Job creation failed because a Master Table and its indexes could not be created, most commonly due to the pre-existance of a table with the same name (job name) in the user schema. Refer to any following error messages for clarification.
Action: Select a different job name, DROP the existing table, or eliminate any problems indicated by the following error messages.

ORA-06512: at stringline string
Cause: Backtrace message as the stack is unwound by unhandled exceptions.
Action: Fix the problem causing the exception or write an exception handler for this condition. Or you may need to contact your application administrator or DBA.

既然說任務不存在，那我換一個任務名試試：
[oracle@bj sh]$ /home/oracle/backup/sh/backup_expdp.sh

Export: Release 10.1.0.3.0 - Production on Tuesday, 29 March, 2005 10:10

Copyright (c) 2003, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."CASES_EXPORT1":  system/********@newadm schemas=admapp directory=backup_expdp_dir dumpfile=backup_expdp_Tue.dmp logfile=backup_expdp_Tue.log job_name=cases_export1
Estimate in progress using BLOCKS method...
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling DBMS_METADATA.FETCH_XML_CLOB []
ORA-31642: the following SQL statement fails:
BEGIN DMSYS.DBMS_DM_MODEL_EXP.SCHEMA_CALLOUT('ADMAPP',0,1,'10.01.00.03.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 872
ORA-04063: package body "DMSYS.DBMS_DM_UTIL" has errors
ORA-06508: PL/SQL: could not find program unit being called

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x7ced91c4     13460  package body SYS.KUPW$WORKER
0x7ced91c4      5810  package body SYS.KUPW$WORKER
0x7ced91c4      8264  package body SYS.KUPW$WORKER
0x7ced91c4      1569  package body SYS.KUPW$WORKER
0x7ced91c4      6325  package body SYS.KUPW$WORKER
0x7ced91c4      1208  package body SYS.KUPW$WORKER
0x7d384694         2  anonymous block

Job "SYSTEM"."CASES_EXPORT1" stopped due to fatal error at 10:11

再執行，就和剛才出現的錯誤提示一樣了。
[oracle@bj lisa]$ /home/oracle/backup/sh/backup_expdp.sh

Export: Release 10.1.0.3.0 - Production on Tuesday, 29 March, 2005 10:20

Copyright (c) 2003, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.CASES_EXPORT1"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 701
ORA-00955: name is already used by an existing object

local: backup_expdp_Tue.dmp: No such file or directory
local: backup_expdp_Tue.log: No such file or directory

錯誤出現在3月23號早上3：00的備份，3月22號的備份日誌還是正常的，也就是3月22號的什麼操作導致的，察看3月22號的日誌，發現成功配置了emca的資料庫，啟動了dbconsole，疑心是這個操作導致的。

在metalink查到這個：

書籤 轉到末尾

文件 ID:  註釋:272874.1
主題:  Export DataPump: ORA-39125 Fatal Error in Worker while Calling DMSYS.DBMS_DM_MODEL_EXP.SCHEMA_CALLOUT
型別:  PROBLEM
狀態:  PUBLISHED
 內容型別:  TEXT/X-HTML
建立日期:  17-MAY-2004
上次修訂日期:  02-JUL-2004
 

The information in this article applies to:
Enterprise Manager for RDBMS - Version: 10.1.0.0 to 10.2.0.0
Oracle Server - Enterprise Edition - Version: 10.1.0.0 to 10.2.0.0
Oracle Server - Personal Edition - Version: 10.1.0.0 to 10.2.0.0
Oracle Server - Standard Edition - Version: 10.1.0.0 to 10.2.0.0
This problem can occur on any platform.

Errors
ORA-04063
ORA-06508
ORA-06512
ORA-31642
ORA-39125

Symptoms
You start a schema level export job in Oracle10g with the export DataPump utility:

expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_scott.dmp LOGFILE=expdp_scott.log SCHEMAS=scott

The export jobs fails with the following errors:

ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS
while calling DBMS_METADATA.FETCH_XML_CLOB []
ORA-31642: the following SQL statement fails:
BEGIN DMSYS.DBMS_DM_MODEL_EXP.SCHEMA_CALLOUT('SCOTT',0,1,'10.01.00.02.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 872
ORA-04063: package body "DMSYS.DBMS_DM_UTIL" has errors
ORA-06508: PL/SQL: could not find program unit being called

Cause
You check for invalid objects in the database:

SQL> connect system/manager
SQL> set lines 200
SQL> select status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
from dba_objects where status != 'VALID' order by 4,2;

The output shows that package DMSYS.DBMS_DM_UTIL is invalid.

Package DMSYS.DBMS_DM_UTIL is used by the Oracle Data Mining option.

Fix
1. Run the script dmputil.plb to re-create the invalid package. E.g.:

SQL> CONNECT dmsys/dmsys
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.

SQL> CONNECT / as sysdba
Connected.

SQL> ALTER USER dmsys IDENTIFIED BY dmsys ACCOUNT UNLOCK;
User altered.

SQL> CONNECT dmsys/dmsys
Connected.

SQL> @$ORACLE_HOME/dm/admin/dmutil.plb
Package created.
Package created.

2. Delete the Export DataPump logfile and dumpfile of the failed previous attempt.

3. Re-run the export DataPump job.

以為是個total solution，結果照著做了，還是不對，鬱悶。

通過OEM，檢查發現DMSYS有四個包是失效的：
其中一個的提示：
Line # = 2924 Column # = 17 Error Text = PL/SQL: Item ignored
Line # = 2924 Column # = 17 Error Text = PLS-00201: identifier 'UTL_FILE' must be declared
Line # = 2931 Column # = 5 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 2931 Column # = 5 Error Text = PL/SQL: Statement ignored
Line # = 2951 Column # = 7 Error Text = PL/SQL: Statement ignored
Line # = 2952 Column # = 9 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 2955 Column # = 23 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 2955 Column # = 7 Error Text = PL/SQL: Statement ignored
Line # = 2962 Column # = 21 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 2962 Column # = 5 Error Text = PL/SQL: Statement ignored

再檢查3月22號的日誌，發現當天做了以下操作：
SQL> revoke EXECUTE on UTL_FILE from PUBLIC;

Revoke succeeded.

當時是在emca啟動成功後，實驗ADDM，提示說應該從PUBLIC收回這個許可權的，暈倒。

SQL> grant EXECUTE on UTL_FILE to public;

Grant succeeded.

找到問題，重新編譯這四個過程，再執行expdp就ok了。
不過還有個問題，原來的任務名CASES_EXPORT和CASES_EXPORT1都不能再執行了，換了任務名才可以，我執行備份之前應該已經刪除了原來的備份和日誌檔案，不知道還有什麼需要清除日誌檔案。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/51862/viewspace-180621/，如需轉載，請註明出處，否則將追究法律責任。