Scheduler Job Start Time Changed After DST Change

Hi all,
Some time ago a client asked me to check why a Job started running in a different time. This was a week after the DST change. Easy guess, right?

The job was scheduled using timezone offset (PM-04:00) instead of timezone region. Considering the DST change, the offset changed as well (-5:00), but not the job schedule.
To fix the job time definitely, I changed schedule to use timezone region that database is in. Check below the investigation and solution:

SQL> select owner,job_name, repeat_interval, last_start_date, next_run_date from dba_scheduler_jobs where job_name='MYJOB';

OWNER			 JOB_NAME    REPEAT_INTERVAL	LAST_START_DATE                      NEXT_RUN_DATE
---------- ---------- ----------------- -----------------------------------  --------------------
GREPORA		 MYJOB      FREQ=DAILY;			   08-NOV-17 09.00.00.330739 PM -04:00 08-NOV-17 09.00.00.300000 PM -04:00
							  
												  
SQL> SELECT * FROM   dba_scheduler_global_attribute  WHERE  attribute_name = 'DEFAULT_TIMEZONE';

ATTRIBUTE_NAME	  VALUE
----------------- ---
DEFAULT_TIMEZONE  EST5EDT


SQL> SELECT tzabbrev, TZ_OFFSET(tzname), tzname FROM V$TIMEZONE_NAMES 
WHERE tzname IN ('EST', 'EDT') OR tzabbrev IN ('EST', 'EDT') ORDER BY 1,2;

TZABBREV	TZ_OFFS TZNAME
--------- ------- ---------------------------
EDT       -04:00  America/Santo_Domingo
EDT       -05:00  America/Fort_Wayne
EDT       -05:00  America/Grand_Turk
EDT       -05:00  America/Indiana/Indianapolis
EDT       -05:00  America/Indiana/Marengo
EDT       -05:00  America/Indiana/Petersburg
EDT       -05:00  US/Michigan
EDT       -05:00  America/Detroit
EDT       -05:00  US/Eastern
EDT       -05:00  US/East-Indiana
EDT       -05:00  Jamaica
EDT       -05:00  EST5EDT
EDT       -05:00  Canada/Eastern
EDT       -05:00  America/Toronto
EDT       -05:00  America/Thunder_Bay
EDT       -05:00  America/Port-au-Prince
EDT       -05:00  America/Pangnirtung
EDT       -05:00  America/Nipigon
EDT       -05:00  America/New_York
EDT       -05:00  America/Nassau
EDT       -05:00  America/Montreal
EDT       -05:00  America/Louisville
EDT       -05:00  America/Kentucky/Monticello
EDT       -05:00  America/Kentucky/Louisville
EDT       -05:00  America/Jamaica
EDT       -05:00  America/Iqaluit
EDT       -05:00  America/Indiana/Vevay
EDT       -05:00  America/Indiana/Vincennes
EDT       -05:00  America/Indiana/Winamac
EDT       -05:00  America/Indianapolis
EDT       -06:00  America/Indiana/Tell_City
EDT       -06:00  America/Cancun
EST       +10:00  Australia/Queensland
EST       +10:00  Australia/Lindeman
EST       +10:00  Australia/Brisbane
EST       +10:30  Australia/Broken_Hill
EST       +10:30  Australia/Yancowinna
EST       +11:00  Australia/Currie
EST       +11:00  Australia/Canberra
EST       +11:00  Australia/ACT
EST       +11:00  Antarctica/Macquarie
EST       +11:00  Australia/Hobart
EST       +11:00  Australia/LHI
EST       +11:00  Australia/Lord_Howe
EST       +11:00  Australia/Melbourne
EST       +11:00  Australia/NSW
EST       +11:00  Australia/Sydney
EST       +11:00  Australia/Tasmania
EST       +11:00  Australia/Victoria
EST       -04:00  America/Antigua
EST       -04:00  America/Moncton
EST       -04:00  America/Santo_Domingo
EST       -05:00  US/Eastern
EST       -05:00  America/Grand_Turk
EST       -05:00  America/Atikokan
EST       -05:00  Jamaica
EST       -05:00  EST5EDT
EST       -05:00  EST
EST       -05:00  America/Indiana/Indianapolis
EST       -05:00  Canada/Eastern
EST       -05:00  America/Toronto
EST       -05:00  America/Thunder_Bay
EST       -05:00  America/Resolute
EST       -05:00  America/Indiana/Marengo
EST       -05:00  America/Indiana/Petersburg
EST       -05:00  America/Cayman
EST       -05:00  America/Indiana/Vevay
EST       -05:00  America/Indiana/Vincennes
EST       -05:00  America/Indiana/Winamac
EST       -05:00  America/Indianapolis
EST       -05:00  America/Iqaluit
EST       -05:00  America/Jamaica
EST       -05:00  America/Kentucky/Louisville
EST       -05:00  America/Kentucky/Monticello
EST       -05:00  US/Michigan
EST       -05:00  America/Louisville
EST       -05:00  America/Coral_Harbour
EST       -05:00  America/Detroit
EST       -05:00  America/Fort_Wayne
EST       -05:00  America/Montreal
EST       -05:00  America/Nassau
EST       -05:00  America/New_York
EST       -05:00  America/Nipigon
EST       -05:00  America/Panama
EST       -05:00  America/Pangnirtung
EST       -05:00  America/Port-au-Prince
EST       -05:00  US/East-Indiana
EST       -06:00  US/Central
EST       -06:00  CST
EST       -06:00  America/Rankin_Inlet
EST       -06:00  America/Merida
EST       -06:00  America/Menominee
EST       -06:00  America/Managua
EST       -06:00  America/Knox_IN
EST       -06:00  America/Indiana/Tell_City
EST       -06:00  America/Indiana/Knox
EST       -06:00  America/Chicago
EST       -06:00  America/Cancun
EST       -06:00  US/Indiana-Starke
EST       -07:00  America/Cambridge_Bay

100 rows selected.


SQL> BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
   name                 => 'GREPORA.MYJOB',
   attribute            => 'start_date',
   value                => TO_TIMESTAMP_TZ('09-11-2017 21:00:00 EST5EDT' ,'DD-MM-YYYY HH24:MI:SS TZR'));
END;
/

PL/SQL procedure successfully completed.

SQL> select owner,job_name, repeat_interval, last_start_date, next_run_date from dba_scheduler_jobs where job_name='MYJOB';

OWNER			 JOB_NAME    REPEAT_INTERVAL	LAST_START_DATE                  NEXT_RUN_DATE
---------- ---------- ----------------- -------------------------------  --------------------
GREPORA		 MYJOB      FREQ=DAILY;			  08-NOV-17 09.00.00.3 09-NOV-17   09-NOV-17 09.00.00.0

Hope this helps you!
Cheers!

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!

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.