Long running jobs AQ$_PLSQL_NTFN_%

Hello all!

Some time ago I had an issue with some jobs running for 64 days. Cool han?
All jobs names were AQ$_PLSQL_NTFN_%. All activity of jobs were related to SQLID f7zggdz9p7bhk in wait event “Streams AQ: waiting for messages in the queue”.

# SQLID f7zggdz9p7bhk:
begin SYS.SCHEDULER$_JOB_EVENT_HANDLER(context => :1,reginfo => sys.aq$_reg_info(:2, :3, :4, :5, :6, :7),descr => sys.aq$_descriptor(:8, :9, :10, sys.msg_prop_t(:11, :12, :13, :14, :15, :16, :17, :18, sys.aq$_agent(:19, :20, :21), :22, :23), sys.aq$_ntfn_descriptor(:24), :25, :26), payload => :27, payloadl => :28); end;

Reviewing on MOS, found a match to Bug 20528052 – Many AQ$_PLSQL_NTFN jobs executed affecting database performance (Doc ID 20528052.8).

The root cause is procedure SYS.SCHEDULER$_JOB_EVENT_HANDLER keep waiting AQ PL/SQL Notification callbacks associated with scheduler job email notifications for messages in “SYS”.”SCHEDULER$_EVENT_QUEUE” which no longer exist.

From MOS, this can be easily solved with fix for Bug 16623661 – No email notification is sent by scheduler, wich is included in 12.1.0.1 (base) and affect versions 11.2.0.2 and later.
Also from MOS, and which I did, is drop these jobs. The problem is these jobs just restart and have the same problem again after automated recreation.

So, apply the fix or upgrade your database. ūüėČ

Some related references:
Bug 20528052 – Many AQ$_PLSQL_NTFN jobs executed affecting database performance (Doc ID 20528052.8)
AQ$_PLSQL_NTFN Scheduler Jobs Executed in large numbers affecting Database performance (Doc ID 2001165.1)
Bug 21665897: FIX FOR BUG 14712567 CAUSES LARGE NUMBERS OF AQ$_PLSQL_NTFN_% TO BE SPAWNED

Hope it helps!
Cheers!

Advertisements

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

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.

Scheduler Job by Node (RAC Database)

Sometimes you want to run something just in one node of the RAC. Here is an example to do it:

create or replace procedure USER_JOB.PRC_SOMETHING is
begin
-- do something
null;
end;
/
begin
sys.dbms_scheduler.create_job(job_name => 'USER_JOB.JOB_SOMETHING',
job_type            => 'PLSQL_BLOCK',
job_action          => 'USER_JOB.PRC_SOMETHING;',
start_date          => sysdate,
repeat_interval     => 'Freq=Minutely;Interval=30',
end_date            => to_date(null),
job_class           => 'DEFAULT_JOB_CLASS',
enabled             => true,
auto_drop           => false,
comments            => 'Something Job.');
end;
/
begin
dbms_scheduler.set_attribute(name => 'USER_JOB.JOB_SOMETHING',
 attribute=>'INSTANCE_ID', value=> 1);
end;
/

Matheus.