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

alert 告警日志：

Errors in file /u01/app/oracle/diag/rdbms/q9jsdg/q9jsdg/trace/q9jsdg_j000_2988.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
Sun Feb 14 15:01:38 2016
Thread 1 advanced to log sequence 136350 (LGWR switch)

官方信息：

CAUSE

The same is addressed in 
Bug 13637859 - "BSLN_MAINTAIN_STATS_JOB FAILS WITH ORA-06502 
which is marked as a duplicate of
Bug 10110625 - DBSNMP.BSLN_INTERNAL RECEIVES ORA-06502


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.

SOLUTION

This issue is fixed in 11.2.0.4.

For versions below 11.1.0.7 apply the patch 6282324.Bug 6282324 is fixed in 11.1.0.7 .

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

另一种方法：

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