ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"

一.版本信息

操作系统版本：

AIX 6100

数据库版本：

11.2.0.3(psu5)

 

二.错误描述

在alert 日志中发现如下报错

Sun Sep 28 15:00:22 2014
Errors in file /oracle/app/oracle/diag/rdbms/xxx/xxx/trace/xxx2_j000_8519814.trc:
ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "DBSNMP.BSLN_INTERNAL", line 2073
ORA-06512: at line 1

trace文件中信息同alert日志文件中报错类似

SQL> select log_date,status from dba_scheduler_job_run_details where job_name='BSLN_MAINTAIN_STATS_JOB' order by log_date desc;

LOG_DATE                                                                    STATUS
--------------------------------------------------------------------------- ------------------------------
28-SEP-14 03.00.22.110836 PM +08:00                                         FAILED
21-SEP-14 03.00.20.124075 PM +08:00                                         FAILED
14-SEP-14 03.00.20.780679 PM +08:00                                         FAILED
07-SEP-14 03.00.24.276810 PM +08:00                                         FAILED
31-AUG-14 03.00.02.057835 PM +08:00                                         FAILED

#通过dba_scheduler_job_run_details视图我们可以看到在alert出现报错的时间有一条失败记录

 

三.问题原因及解决方案

通过MOS查到如下NOTE (Doc ID 1413756.1)

MOS给的解释是：

As in a specific customer's situation, the issue can occur when a Database is created with a template from the old database or created as a clone from another database. The existing records in table "DBSNMP.BSLN_BASELINES" conflict with new baseline information inserted in the cloned database, thereby containing inconsistent information.

#如果你的数据库是通过模板创建，或者是克隆的，那么这个错误就可能好发生。因为原数据库中"DBSNMP.BSLN_BASELINES" 表的记录可能会同新插入数据冲突。

 

MOS给出的解决方案是：

This issue is fixed in 11.2.0.4.

For 11.2.0.2 and 11.2.0.3, please apply Patch 10110625 (if available for your platform and database version).

 

临时解决方案：

As a workaround, the DBSNMP user can be dropped and re-created using the standard scripts.
Ensure you have Backed up your database.

-- Login as sys user.
 
SQL> sqlplus / as sysdba
 
-- From the sqlplus execute the following:
 
-- Drop the DBSNMP user by executing catnsnmp.sql script.
 
SQL> @$ORACLE_HOME/rdbms/admin/catnsnmp.sql
 
-- Create the DBSNMP user by executing catsnmp.sql
 
SQL> @$ORACLE_HOME/rdbms/admin/catsnmp.sql

 ##详细情况请查看 "Doc ID 1413756.1"

 

四. 解决过程

因为暂时没有条件给现网数据库打补丁，所以决定先采用临时解决方案进行处理。

但是在重建执行@$ORACLE_HOME/rdbms/admin/catsnmp.sql命令重建DBSNMP时报如下错误：

Package body created.


Package body created.

declare
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "DBSNMP.BSLN" has been invalidated
ORA-04065: not executed, altered or dropped package "DBSNMP.BSLN"
ORA-06508: PL/SQL: could not find program unit being called: "DBSNMP.BSLN"
ORA-06512: at line 18



PL/SQL procedure successfully completed.	

发生该错误的具体原因，及解决方案将在我的另一篇博客中详细解释。博客连接如下：

http://blog.csdn.net/shaochenshuo/article/details/39669273