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

Error in Alert log

Errors in file /wload/test/app/oracle/diag/rdbms/TEST/TEST/trace/TEST_j000_7733378.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

Sat Mar 24 23:01:18 2012

Error from trace file

*** 2012-03-24 23:00:13.943

*** SESSION ID:(23.6871) 2012-03-24 23:00:13.943

*** CLIENT ID:() 2012-03-24 23:00:13.943

*** SERVICE NAME:(SYS$USERS) 2012-03-24 23:00:13.943

*** MODULE NAME:(DBMS_SCHEDULER) 2012-03-24 23:00:13.943

*** ACTION NAME:(BSLN_MAINTAIN_STATS_JOB) 2012-03-24 23:00:13.943

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

Cause

Table DBSNMP.BSLN_BASELINES contains inconsistent information that is causing the job to fail.

Check the job status

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

LOG_DATE STATUS

————————————————————————— ——————————

10-MAR-12 11.00.13.501250 PM +00:00 FAILED

03-MAR-12 11.00.10.603238 PM +00:00 FAILED

17-MAR-12 11.00.14.793987 PM +00:00 FAILED

24-MAR-12 11.00.13.980545 PM +00:00 FAILED

Role of BSLN_MAINTAIN_STATS_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

Check the DBSNMP.BSLN_BASELINES table

SQL> select * from DBSNMP.BSLN_BASELINES;

DBID INSTANCE_NAME BASELINE_ID BSLN_GUID TI A STATUS LAST_COMP

———- —————- ———– ——————————– — – —————- ———

1166314350 FTEST 0 4AC774574F6C7D60D4ADF390356098C1 NX Y ACTIVE 27-NOV-10

1166314350 TEST 0 CEF9782DC11EE02FCB9E1D821B49FB3F HX Y ACTIVE 27-NOV-10

So on our database we should just have the entry for TEST so we need to delete the entry for FTEST.

Delete the inconsistent entry

SQL> delete from DBSNMP.BSLN_BASELINES where INSTANCE_NAME=’FTEST’;

1 row deleted.

SQL> select * from DBSNMP.BSLN_BASELINES;

DBID INSTANCE_NAME BASELINE_ID BSLN_GUID TI A STATUS LAST_COMP

———- —————- ———– ——————————– — – —————- ———

1166314350 TEST 0 CEF9782DC11EE02FCB9E1D821B49FB3F HX Y ACTIVE 27-NOV-10

Now re-run the job.

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

PL/SQL procedure successfully completed.

The job has now successfully run.

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

LOG_DATE STATUS

————————————————————————— ——————————

10-MAR-12 11.00.13.501250 PM +00:00 FAILED

29-MAR-12 01.11.43.054124 PM +01:00 SUCCEEDED

03-MAR-12 11.00.10.603238 PM +00:00 FAILED

17-MAR-12 11.00.14.793987 PM +00:00 FAILED

24-MAR-12 11.00.13.980545 PM +00:00 FAILED