Tuesday, August 9, 2016

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

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

ORA-06550: line 1, column 807:

PLS-00201: identifier 'DBSNMP.BSLN_INTERNAL' must be declared


Cause:

You might get this error when database is created using template from old database or by cloning from another database.  Therefore, table DBSNMP.BSLN_BASELINES contains inconsistent information that is causing the job to fail.



Solution:

If you delete the inconsistent rows from table DBSNMP.BSLN_BASELINES, it will fix the problem but you can fix this easiest way by following the below guidelines.

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

LOG_DATE                                                                    STATUS

--------------------------------------------------------------------------- ------------------------------

13-AUG-14 09.45.36.831010 AM +02:00                                         FAILED

17-AUG-14 09.00.00.980275 AM +02:00                                         FAILED


SQL> sqlplus / as sysdba

-- 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

-- Re-execute the job

SQL> exec dbms_scheduler.run_job('BSLN_MAINTAIN_STATS_JOB',false);

PL/SQL procedure successfully completed.

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

LOG_DATE                                                                    STATUS

--------------------------------------------------------------------------- ------------------------------

13-AUG-14 09.45.36.831010 AM +02:00                                         FAILED

17-AUG-14 09.00.00.980275 AM +02:00                                         FAILED

20-AUG-14 02.50.36.799841 PM +02:00                                         SUCCEEDED

Reference metalink Doc ID 1413756.1