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

Posted: April 17, 2012 in ORA- error

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

Comments
  1. Lawrence says:

    Thanks. This was exactly what I was looking for.

  2. Sam says:

    perfect. I am keen to know what causes this ?

    • tonymu99 says:

      Hi glad it helped you out. I believe the error is down to inconsistent information in the Table DBSNMP.BSLN_BASELINES you get this typically from database cloning, so old database baseline information is in the table which causes the issue. You can also get round this by dropping the dbsnmp users and re-create also there is a patch available for 11.2.0.2 please see metalink note ID 1413756.1

      • John K says:

        We had issue when we renamed the database. Old name entry was still in table. Job worked fine once it was deleted.

  3. Jonu Joy says:

    would you know what would be the side effect if we dont run this job( what would happen if we dont keep the default baseline’s statistics up-to-date )

  4. dwianjali says:

    Thanks,my alert log issue also resolved

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s