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.
HI,
Thanks for stating the process to resolve this. We are facing the same issue in our Oracle 11.2.0.3 database running on AIX 7.1 server.
Is there a chance this process will corrupt the database? I need to confirm before executing this. Kindly reply.
Hello Debomitra,
The best suggestion I can give is to open a SR and have this confirmation from Oracle.
This is most likely not corrupt your database, but once done the stats will not be gathered for the database ID you deleted. So, make sure your are deleting the proper DBID/instance name.
Anyway, I sguggest you store the information in a backup table before deleting. Something like a CTAS should be enough.
Cheers!
Thanks @matheusdba, I actually executed this in my test instance. It’s all fine now. Thanks for the article! Cheers!
Hello,I check your blog named “ORA-12012: error on auto execute of job “SYS”.”BSLN_MAINTAIN_STATS_JOB” – |GREP ORA” on a regular basis.Your humoristic style is witty, keep it up! And you can look our website about love spells.