BSLN_MAINTAIN_STATS_JOB is getting failed

Issue:
BSLN_MAINTAIN_STATS_JOB is getting failed




Error in Alert Log:
Sun Jan 01 13:30:01 2012
Errors in file /orahome/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_j000_1781806.trc:
ORA-12012: error on auto execute of job 353101
ORA-06550: line 1, column 780:
PLS-00201: identifier 'DBSNMP.BSLN_INTERNAL' must be declared
ORA-06550: line 1, column 780:
PL/SQL: Statement ignored



Trace file:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /orahome/app/oracle/product/11.2.0/dbhome_1
System name:    AIX
Node name:      PROD
Release:        1
Version:        6
Machine:        00C8A8E54C00
Instance name: orcl1
Redo thread mounted by this instance: 1
Oracle process number: 56
Unix process pid: 1781806, image: oracle@PROD (J000)
*** 2012-01-01 13:30:01.195
*** SESSION ID:(308.19730) 2012-01-01 13:30:01.195
*** CLIENT ID:() 2012-01-01 13:30:01.195
*** SERVICE NAME:(SYS$USERS) 2012-01-01 13:30:01.195
*** MODULE NAME:(DBMS_SCHEDULER) 2012-01-01 13:30:01.195
*** ACTION NAME:(BSLN_MAINTAIN_STATS_JOB) 2012-01-01 13:30:01.195
ORA-12012: error on auto execute of job 353101
ORA-06550: line 1, column 780:
PLS-00201: identifier 'DBSNMP.BSLN_INTERNAL' must be declared
ORA-06550: line 1, column 780:
PL/SQL: Statement ignored





Findings:
SQL> select object_name from dba_objects where object_id=353101;

OBJECT_NAME
-----------------------------------
BSLN_MAINTAIN_STATS_JOB


09:58:16 orcl1 > 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
------------------------------------------------------------------------ ------------
01-JAN-12 01.30.01.224309 PM +05:30                                         FAILED
25-DEC-11 01.30.00.855555 PM +05:30                                         FAILED
18-DEC-11 01.30.00.761203 PM +05:30                                         FAILED
11-DEC-11 01.30.00.583605 PM +05:30                                         FAILED
04-DEC-11 01.30.00.450731 PM +05:30                                         FAILED





Role of BSLN_MAINTAIN_STATS_JOB:
This job is a compute statistics job. This job runs the   BSLN_MAINTAIN_STATS_PROG program on the
BSLN_MAINTAIN_STATS_SCHED schedule. The program BSLN_MAINTAIN_STATS_PROG will keep the default
baseline's statistics up-to-date.




Cause:
Table DBSNMP.BSLN_BASELINES contains inconsistent information. After database cloning , existing records in table “DBSNMP.BSLN_BASELINES” conflict with new baseline information inserted in the cloned database. The DBSNMP user needs to be dropped and re-created.


Fixed:
SQL> @?/rdbms/admin/catnsnmp.sql

SQL> @?/rdbms/admin/catsnmp.sql