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

Note: This particular scenario happened on an Oracle 11g Rel.2 (11.2.0.3.0) RAC on OEL6 x64 with Active Dataguard.

The following error shows on a scheduled basis

ORCL1:
Sat Jan 18 23:00:06 2014
Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL1/trace/ORCL1_j008_6097.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

Reason:
The underlying table required as part of one of the schedule maintenance tasks has invalid values.
The related table is DBSNMP.BSLN_BASELINES. 

After taking a look at the job log we see the failed execution attempts:

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

LOG_DATE                              STATUS
------------------------------------- ------------------------------
11-JAN-14 11.00.07.315077 PM -06:00   FAILED
04-JAN-14 11.00.05.595559 PM -06:00   FAILED
18-JAN-14 11.00.06.554385 PM -06:00   FAILED
28-DEC-13 11.01.05.966337 PM -06:00   FAILED
22-JAN-14 03.36.50.995888 PM -06:00   FAILED

5 rows selected.

FIX Procedure:
Look for the values on the table, detect the invalid values and remove them.

SQL> select * from DBSNMP.BSLN_BASELINES;

      DBID INSTANCE_NAME    BASELINE_ID BSLN_GUID                        TI A STATUS           LAST_COMPUT
---------- ---------------- ----------- -------------------------------- -- - ---------------- -----------
3038864366 orcl                       0 0A03A0424F06F7E6B3841C6CC0999F7C NW N ACTIVE           131027:0000
3038864366 ORCL2                      0 A396EEB2AB3A39AF477DC4A1AEE70CC9 ND Y ACTIVE           131228:2300
3038864366 ORCL1                      0 176C36D62D62855075AD0BBD90B2DA30 ND Y ACTIVE           131228:2300

You must know exactly which row you're deleting, in my very particular case I know which rows do not make sense since this database was recently involved in a fail over scenario and the former name is still being held in the table.


The corrupt row is removed with a simple DELETE command:

SQL> DELETE FROM DBSNMP.BSLN_BASELINES WHERE INSTANCE_NAME ='orcl';

1 row deleted.

SQL> commit;

Commit complete.

Manually re-execute the job and check the execution log, it must show the job executed successfully.  It takes a couple of minutes after execution to show the results in the log table.

SQL> exec dbms_scheduler.run_job('BSLN_MAINTAIN_STATS_JOB',false);
PL/SQL procedure successfully completed.


The issue was fixed, validate it by querying he DBA_SCHEDULER_JOB_RUN_DETAILS view.

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

LOG_DATE                              STATUS
------------------------------------- ------------------------------

11-JAN-14 11.00.07.315077 PM -06:00   FAILED

04-JAN-14 11.00.05.595559 PM -06:00   FAILED

18-JAN-14 11.00.06.554385 PM -06:00   FAILED

28-DEC-13 11.01.05.966337 PM -06:00   FAILED

22-JAN-14 03.36.50.995888 PM -06:00   FAILED

22-JAN-14 03.41.20.714453 PM -06:00   SUCCEEDED