今天早上检查数据库的备份日志，发现其中一个数据库的expdp错误：

最新推荐文章于 2023-11-03 11:12:22 发布

cinashu1164

最新推荐文章于 2023-11-03 11:12:22 发布

阅读量496

点赞数

文章标签：数据库

[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

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

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

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/51862/viewspace-180621/

cinashu1164

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
今天早上检查数据库的备份日志，发现其中一个数据库的expdp错误：

[oracle@bj oracle]$ /home/oracle/backup/sh/backup_expdp.shExport: Release 10.1.0.3.0 - Production on Tuesday, 29 M...
复制链接

扫一扫