Ora 12012 error on auto execute of job

PROBLEM:

Below errors we have observed in the alert log of oracle 11g database.

2017-11-12 00:00:00.885000 +03:00
Errors in file /oradbtrace/diag/ecnmprod/diag/rdbms/prod1/prod/trace/prod_j000_40300.trc:
ORA-12012: error on auto execute of job “SYS”.”BSLN_MAINTAIN_STATS_JOB”
ORA-04063: package body “DBSNMP.BSLN_INTERNAL” has errors
ORA-06508: PL/SQL: could not find program unit being called: “DBSNMP.BSLN_INTERNAL”
ORA-06512: at line 1

SOLUTION:

Check the status of the object BSLN_INTERNAL:

SQL>  select owner,object_name,object_type,status from dba_objects where object_name='BSLN_INTERNAL';

OWNER                          OBJECT_NAME                        OBJECT_TYPE         STATUS
------------------------------ ---------------------------------- ------------------- -------
DBSNMP                         BSLN_INTERNAL                      PACKAGE BODY        INVALID --- > INVALID 
DBSNMP                         BSLN_INTERNAL                      PACKAGE             VALID

Try to compile the package body:

SQL> alter package DBSNMP.BSLN_INTERNAL compile body;

Warning: Package Body altered with compilation errors.

SQL> show error
Errors for PACKAGE BODY DBSNMP.BSLN_INTERNAL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1910/7   PL/SQL: Statement ignored
1910/7   PLS-00201: identifier 'DBMS_JOB' must be declared

Grant execute privilege on dbms_job to dbsnmp:

SQL> select * from dba_tab_privs where table_name='DBMS_JOB';

GRANTEE                        OWNER                          TABLE_NAME                     GRANTOR                        PRIVILEGE                                GRA HIE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --- ---
SYSMAN                         SYS                            DBMS_JOB                       SYS                            EXECUTE                                  NO  NO
EXFSYS                         SYS                            DBMS_JOB                       SYS                            EXECUTE                                  NO  NO

SQL> GRANT EXECUTE ON sys.dbms_job to DBSNMP;

Grant succeeded.

SQL> select * from dba_tab_privs where table_name='DBMS_JOB';

GRANTEE                        OWNER                          TABLE_NAME                     GRANTOR                        PRIVILEGE                                GRA HIE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --- ---
SYSMAN                         SYS                            DBMS_JOB                       SYS                            EXECUTE                                  NO  NO
EXFSYS                         SYS                            DBMS_JOB                       SYS                            EXECUTE                                  NO  NO
DBSNMP                         SYS                            DBMS_JOB                       SYS                            EXECUTE                                  NO  NO

Now compile again:

SQL> alter package DBSNMP.BSLN_INTERNAL compile body;


Package body altered.

SQL> SQL>  select owner,object_name,object_type,status from dba_objects where object_name='BSLN_INTERNAL';

OWNER                          OBJECT_NAME                        OBJECT_TYPE         STATUS
------------------------------ ---------------------------------- ------------------- -------
DBSNMP                         BSLN_INTERNAL                      PACKAGE BODY        VALID
DBSNMP                         BSLN_INTERNAL                      PACKAGE             VALID

As the package body is valid now, the error won’t be logged in the alert log now.

Hi all,
Evaluating a database I detected it was failing to execute the default scheduler job SYS.BSLN_MAINTAIN_STATS_JOB. This job is an Oracle defined automatic moving window baseline statistics computation job, that runs only in weekends.
Below the last stack error in the alert log:

2016-04-24 00:00:10.064000 +00:00
Errors in file /db/u1001/oracle/diag/rdbms/MYDB/MYDB/trace/MYDB_j000_15675.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
2016-04-26 15:54:07.480000 +00:00

And the full tracefile:

Trace file /db/u1001/oracle/diag/rdbms/MYDB/MYDB/trace/MYDB_j000_15675.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2
System name:    Linux
Node name:      prddb09
Release:        2.6.18-164.el5
Version:        #1 SMP Tue Aug 18 15:51:48 EDT 2009
Machine:        x86_64
Instance name: MYDB
Redo thread mounted by this instance: 1
Oracle process number: 151
Unix process pid: 15675, image: oracle@prddb09 (J000)
*** 2016-04-24 00:00:10.064
*** SESSION ID:(586.10305) 2016-04-24 00:00:10.064
*** CLIENT ID:() 2016-04-24 00:00:10.064
*** SERVICE NAME:(SYS$USERS) 2016-04-24 00:00:10.064
*** MODULE NAME:(DBMS_SCHEDULER) 2016-04-24 00:00:10.064
*** ACTION NAME:(BSLN_MAINTAIN_STATS_JOB) 2016-04-24 00:00:10.064
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

According the notes below, the recommended action is to recreate the DBSNMP component:
Bug 10110625 – DBSNMP.BSLN_INTERNAL reports ORA-6502 running BSLN_MAINTAIN_STATS_JOB (Doc ID 10110625.8)
ORA-12012: Error on Auto Execute of job SYS.BSLN_MAINTAIN_STATS_JOB (Doc ID 1413756.1)
KEWBMBTA: Maintain BSLN Thresholds Failed, Check For Details. (Doc ID 1490391.1)

However, it’s a process that can affect other mechanisms. So, I found the follow note with the same error pointing to a privilege issue:
Ora-06508: Pl/Sql: Could Not Find Program Unit Being Called: “DBSNMP.BSLN_INTERNAL” (Doc ID 1323597.1)

But after granting the privilege as workaround suggested, the fail remais…

MYDB> select * from dba_tab_privs where table_name='DBMS_JOB';
GRANTEE                        OWNER                          TABLE_NAME                     GRANTOR                        PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ------------------------------ -----------
APEX_030200                    SYS                            DBMS_JOB                       SYS                            EXECUTE
SYSMAN                         SYS                            DBMS_JOB                       SYS                            EXECUTE
EXFSYS                         SYS                            DBMS_JOB                       SYS                            EXECUTE
PUBLIC                         SYS                            DBMS_JOB                       SYS                            EXECUTE
SQL> GRANT EXECUTE ON sys.dbms_job to DBSNMP;
Grant succeeded.
MYDB> select * from dba_tab_privs where table_name='DBMS_JOB';
GRANTEE                        OWNER                          TABLE_NAME                     GRANTOR                        PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------
SYSMAN                         SYS                            DBMS_JOB                       SYS                            EXECUTE
APEX_030200                    SYS                            DBMS_JOB                       SYS                            EXECUTE
EXFSYS                         SYS                            DBMS_JOB                       SYS                            EXECUTE
DBSNMP                         SYS                            DBMS_JOB                       SYS                            EXECUTE
PUBLIC                         SYS                            DBMS_JOB                       SYS                            EXECUTE
SQL> EXEC DBMS_SCHEDULER.RUN_JOB('BSLN_MAINTAIN_STATS_JOB');
BEGIN DBMS_SCHEDULER.RUN_JOB('BSLN_MAINTAIN_STATS_JOB'); END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "DBSNMP.BSLN_INTERNAL", line 2073
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1

After that, while I was quering on DBSNMP, I realized another instance name active in DBSNMP.BSLN_BASELINES.
I guess this database was created with another instance name and then renamed without DBNID.

MYDB> select * from DBSNMP.BSLN_BASELINES;
DBID INSTANCE_NAME    BASELINE_ID BSLN_GUID                        TI A STATUS
---------- ---------------- ----------- -------------------------------- -- - ---------
4092499541 MYDB                       0 75B49690F8B4742084990643EEFFB6AA HX Y ACTIVE
4092499541 oldname                    0 415373CD9959B77AAEE1804F06D88B60 NW Y ACTIVE

So, I deleted the row and the job started to run successfully:

MYDB> DELETE FROM DBSNMP.BSLN_BASELINES WHERE INSTANCE_NAME ='oldname';
1 row deleted.
MYDB> commit;
Commit complete.
SQL> EXEC DBMS_SCHEDULER.RUN_JOB('BSLN_MAINTAIN_STATS_JOB');
PL/SQL procedure successfully completed.

Execution logs:

MYDB> select *
2    from (select owner, job_name, log_date, status, run_duration
3            from dba_scheduler_job_run_details a
4           where job_name = 'BSLN_MAINTAIN_STATS_JOB'
5           order by log_date)
6   where rownum < 10;
OWNER                          JOB_NAME                  LOG_DATE                            STATUS          RUN_DURATION
------------------------------ ------------------------- ----------------------------------- --------------- ---------------
SYS                            BSLN_MAINTAIN_STATS_JOB   03/04/16 00:00:08,484972 +00:00     FAILED          +000 00:00:08
SYS                            BSLN_MAINTAIN_STATS_JOB   10/04/16 00:00:07,943598 +00:00     FAILED          +000 00:00:07
SYS                            BSLN_MAINTAIN_STATS_JOB   17/04/16 00:00:08,486526 +00:00     FAILED          +000 00:00:08
SYS                            BSLN_MAINTAIN_STATS_JOB   24/04/16 00:00:10,067848 +00:00     FAILED          +000 00:00:09
SYS                            BSLN_MAINTAIN_STATS_JOB   29/04/16 13:58:10,779201 +00:00     FAILED          +000 00:00:01
SYS                            BSLN_MAINTAIN_STATS_JOB   29/04/16 14:01:04,162900 +00:00     SUCCEEDED       +000 00:00:00

I hope it help you too!

Matheus.

In Our 18.3 RAC database, I noticed that below error in alertlog file.

Errors in file /u01/app/oracle/diag/rdbms/sid/instance1/trace/instance1_j002_11339.trc:
ORA-12012: error on auto execute of job "ORACLE_OCM"."MGMT_CONFIG_JOB_2_1"
ORA-29280: invalid directory path
ORA-06512: at "ORACLE_OCM.MGMT_DB_LL_METRICS", line 2436
ORA-06512: at line 1

Seed database was most likely not created right by package dbms_stats.init_package not being ran.

Dbms_stats.init_package creates statistics advisor. This procedure is executed during database creation. If something went wrong during database creation,(for example, init_package is not called for some reason), this kind of errors may be seen in alert log when auto job tries to execute.

So than what is the solution. Here are the steps

$ sqlplus / as sysdba

EXEC dbms_stats.init_package();

select name, ctime, how_created
from sys.wri$_adv_tasks
where owner_name = 'SYS'
and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

Output of the query will look like this:

NAME CTIME HOW_CREATED
----------------------------------- ---------- ------------------------------
AUTO_STATS_ADVISOR_TASK 14-APR-16 CMD
INDIVIDUAL_STATS_ADVISOR_TASK 14-APR-16 CMD

If the query based on “where owner_name = ‘SYS’” condition bring something like output you just follow below steps.

DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

SQL> DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'INDIVIDUAL_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

If Output of the query will not look like this, please change the query as shown below to

$ sqlplus / as sysdba

select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks
where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

If the OWNER is a non-SYS user, you have to drop the tasks as that user first and then try to solution mention in the Note again.

This was a case for one customer.

For example:

— Connect as SYSTEM, for example, if that user owned the tasks and non SYS for some reason

SQL> conn system/&password


DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

SQL> DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'INDIVIDUAL_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/


connect / as sysdba
EXEC DBMS_STATS.INIT_PACKAGE();

Reference:

ORA-12012 Error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_<NN> in 12.2.0 Database version or higher release (like 18c) (Doc ID 2127675.1)

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