Fake lack of TimeZones in DBMS_JOBs

Reading about others mistakes is great fun, but everyone believes that he will not make such stupid mistakes. My personal attitude is a bit different – If someone has done such mistake, it can be repeated by another man. We are living in the world without unique things either good or bad.
Today I’ll speak about DBMS_JOBs and TIMEZONEs. The issue that I got was looking very odd thus I want to present it hear.
I have discovered it when I run the dbms_jobs. The job successfully posted into DBA_JOBS but launch postponed for one hour in comparison to the time form NEXT_DATE. Moreover this problem appeared to be only on one node.
To reproduce the case I have chosen the very simple job:

declare
    job_id pls_integer;
  begin
    sys.dbms_job.submit(job =>  job_id,
                         what => 'begin null; end;', -- fake job pl/sql block
                         next_date => sysdate, -- start immediately
                         interval => 'sysdate+1', -- next start next day
                         instance => 1); -- RAC instance that I am going to use
    commit;
end;
/

I have run the same queries for the first and for the second nodes. As result of these scripts I got the following output (the query can be launched on any RAC node). To make it more clear I have add “SYSDATE” column to the DBMS_JOBS view.

> select JOB, WHAT, BROKEN, LAST_DATE, NEXT_DATE, INSTANCE, SYSDATE from DBA_JOBS;
JOB  WHAT                 B LAST_DATE            NEXT_DATE              INSTANCE SYSDATE
---- -------------------- - -------------------- ---------------------- -------- --------------------
   1 begin null; end;     N                      26 Jul 10:13:18               1 26 Jul 10:21:36
   2 begin null; end;     N 26 Jul 10:18:14      27 Jul 10:18:14               2 26 Jul 10:21:36

The job on the first node does not run even when sysdate is after next_date. All general checks like job_queue_processes and broken jobs do not explain the situation.
After one hour when I starring on the screen the content of DBA_JOBS changed to

> select JOB, WHAT, BROKEN, LAST_DATE, NEXT_DATE, INSTANCE, SYSDATE from DBA_JOBS;
JOB  WHAT                 B LAST_DATE            NEXT_DATE              INSTANCE SYSDATE
---- -------------------- - -------------------- ---------------------- -------- --------------------
   1 begin null; end;     N 26 Jul 10:13:20      27 Jul 10:13:18               1 26 Jul 11:15:23
   2 begin null; end;     N 26 Jul 10:18:14      27 Jul 10:18:14               2 26 Jul 11:15:23

Aha! The CJQ process run as expected but just believes that the time is different from what I see. According to documentation DBMS_JOB uses the “date” data type to store the start date/time and does not store/use time zone related information. JOBS who are scheduled are always executed when sysdate => next execution time. But this information contradicts with data that I see.
I struggle with this puzzle until find out that in UNIX each process have separate time zone. And can be easily managed by environment variable TZ.

> export TZ=Europe/London
>  echo $TZ
Europe/London
> date
Tue Jul 26 12:12:25 GMT+01:00 2011
> export TZ=GMT-1:30
>  echo $TZ
GMT-1:30
> date
Tue Jul 26 12:42:04 GMT 2011

Back to oracle side of the process. What is sysdate – sysdate is oracle function that simply queries the OS time of the day. The result looks like independent of any oracle time zone information but
the OS TZ environment variable influences the time that the OS will pass on to Oracle. The process time zone is defined at process start time.
Thus my first attempt to sort it out was to restart coordinator CJQ process.

>alter system job_queue_processes=0 scope=memory sid='db1';
System altered.
-- wait for a minute
>alter system job_queue_processes=100 scope=memory sid='db1';
System altered.
> host ps -ef | grep cjq
  oracle 31653974        1   0 12:46:59      -  0:00 ora_cjq0_db1

The CJQ process was restarted without any gains. It looks like the oracle core respawns it with the same TZ as the PMON. But after restarting the instance from UNIX session with the right TZ environment variable the issue has been fixed. The conclusion is common – be careful and life would be easier.

Useful Note: DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained. [ID 467722.1]
Troubleshooting DBMS_SCHEDULER and DBMS_JOB [ID 783357.1]

Advertisements

2 thoughts on “Fake lack of TimeZones in DBMS_JOBs

  1. Pingback: 了解SYSDATE函数 | Oracle Clinic

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s