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.