| Oracle Scheduler Timing Issue [message #601146] |
Sun, 17 November 2013 07:13  |
 |
VIP2013
Messages: 91
Registered: June 2013
|
Member |
|
|
Hi All, I am using Release 11.2.0.3.0 of oracle. We migrated all of our jobs to scheduler recently.
and i just found that our job has been running in different times. All the database scheduled job are running 5hr(Perhaps EST) gap and application jobs are showing to be executed ~8hrs(perhaps PST) of gap.
So i verified that my DBA_SCHEDULER_GLOBAL_ATTRIBUTE shows EST5EDT and my expectation was GMT. Now below are my issues
1.The JOBS from application side those submitted to scheduler , should also show the same EST5EDT time on ACTUAL_START_DATE, but these are showing PST time.
2.Again even if i set this time to GMT in my local. And rerun the job(from database schedule but not application), it shows same EST5EDT time on ACTUAL_START_DATE column of dba_scheduler_job_run_details.
BEGIN
DBMS_SCHEDULER.get_scheduler_attribute (
attribute => 'default_timezone',
value => 'GMT');
END;
/
|
|
|
|
| Re: Oracle Scheduler Timing Issue [message #601147 is a reply to message #601146] |
Sun, 17 November 2013 07:29  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
From Oracle docs:
Repeating jobs and windows that use the calendaring syntax need to know which time zone to use for their repeat intervals. They normally retrieve the time zone from start_date, but if no start_date is provided (which is not uncommon), they retrieve the time zone from the default_timezone Scheduler attribute.
This means that if you create a DBMS_SCHEDULER job and specify start date, Oracle will not use DBMS_SCHEDULER.DEFAULT_TIMEZONE. It will derive it from start date. And if you didn't specify time zone in start date Oracle will use session timezone. Use SET_ATTRIBUTE to change job START_DATE to start date with desired time zone.
For example:
SQL> BEGIN
2 DBMS_SCHEDULER.CREATE_JOB(
3 job_name => 'job1',
4 job_type => 'PLSQL_BLOCK',
5 job_action => 'BEGIN NULL; END;',
6 start_date => SYSDATE + 1,
7 repeat_interval => 'FREQ=DAILY',
8 enabled => TRUE
9 );
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> SELECT START_DATE
2 FROM USER_SCHEDULER_JOBS
3 WHERE JOB_NAME = 'JOB1'
4 /
START_DATE
---------------------------------------------------------------------------
18-NOV-13 08.45.35.000000 AM -05:00
SQL> ALTER SESSION SET TIME_ZONE = 'PST'
2 /
Session altered.
SQL> BEGIN
2 DBMS_SCHEDULER.DROP_JOB(
3 job_name => 'job1'
4 );
5 END;
6 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_SCHEDULER.CREATE_JOB(
3 job_name => 'job1',
4 job_type => 'PLSQL_BLOCK',
5 job_action => 'BEGIN NULL; END;',
6 start_date => SYSDATE + 1,
7 repeat_interval => 'FREQ=DAILY',
8 enabled => TRUE
9 );
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> SELECT START_DATE
2 FROM USER_SCHEDULER_JOBS
3 WHERE JOB_NAME = 'JOB1'
4 /
START_DATE
---------------------------------------------------------------------------
18-NOV-13 08.45.35.000000 AM PST
SQL>
SY.
[Updated on: Sun, 17 November 2013 07:51] Report message to a moderator |
|
|
|
| Re: Oracle Scheduler Timing Issue [message #601148 is a reply to message #601147] |
Sun, 17 November 2013 07:41 |
|
VIP2013
Messages: 91
Registered: June 2013
|
Member |
|
|
Got it. But even if i have set the default_timezone to 'GMT' and i am triggering the same scheduler manually. its still populating the EST5EDT time in DBA_SCHEDULER_JOB_RUN_DETAILS dictionary. Why its happening.
Note - here i am not providing the start_date to the scheduler signature. Again i am validating the job run time from dba_scheduler_job_run_details dictionary by looking to the field 'REQ_START_DATE' and 'ACTUAL_START_DATE'. So i am expecting respective timezone should reflect in these fields too.
[Updated on: Sun, 17 November 2013 07:57] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Oracle Scheduler Timing Issue [message #601154 is a reply to message #601150] |
Sun, 17 November 2013 09:00 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
VIP2013 wrote on Sun, 17 November 2013 08:58here i am not providing the start_date to the scheduler signature. Again i am validating the job run time from dba_scheduler_job_run_details dictionary by looking to the field 'REQ_START_DATE' and 'ACTUAL_START_DATE'. So i am expecting respective timezone should reflect in these fields too.
I don't know if it is a bug or is documented, but default timezone is not dynamic. You need to restart DBMS_SCHEDULER AQ for default time zone to take effect. Basically, you need to restart DB. For example:
SQL> ALTER SESSION SET TIME_ZONE = 'PST'
2 /
Session altered.
SQL> SELECT SESSIONTIMEZONE FROM DUAL
2 /
SESSIONTIMEZONE
---------------------------------------------------------------------------
PST
SQL> DECLARE
2 v_val VARCHAR2(20);
3 BEGIN
4 DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(
5 attribute => 'default_timezone',
6 value => 'EST'
7 );
8 DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE(
9 attribute => 'default_timezone',
10 value => v_val
11 );
12 DBMS_OUTPUT.PUT_LINE('default_timezone is ' || v_val);
13 END;
14 /
default_timezone is EST
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_SCHEDULER.DROP_JOB(
3 job_name => 'job1'
4 );
5 END;
6 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_SCHEDULER.CREATE_JOB(
3 job_name => 'job1',
4 job_type => 'PLSQL_BLOCK',
5 job_action => 'BEGIN NULL; END;',
6 start_date => NULL,
7 repeat_interval => 'FREQ=DAILY',
8 enabled => TRUE
9 );
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> SELECT NEXT_RUN_DATE
2 FROM USER_SCHEDULER_JOBS
3 WHERE JOB_NAME = 'JOB1'
4 /
NEXT_RUN_DATE
---------------------------------------------------------------------------
17-NOV-13 10.49.05.200000 PM PST <-- And still it is PST
SQL> connect scott as sysdba
Enter password:
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 3423965184 bytes
Fixed Size 2260000 bytes
Variable Size 2751464416 bytes
Database Buffers 654311424 bytes
Redo Buffers 15929344 bytes
Database mounted.
Database opened.
SQL> connect scott
Enter password:
Connected.
SQL> set serveroutput on
SQL> BEGIN
2 DBMS_SCHEDULER.DROP_JOB(
3 job_name => 'job1'
4 );
5 END;
6 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_SCHEDULER.CREATE_JOB(
3 job_name => 'job1',
4 job_type => 'PLSQL_BLOCK',
5 job_action => 'BEGIN NULL; END;',
6 start_date => NULL,
7 repeat_interval => 'FREQ=DAILY',
8 enabled => TRUE
9 );
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> SELECT NEXT_RUN_DATE
2 FROM USER_SCHEDULER_JOBS
3 WHERE JOB_NAME = 'JOB1'
4 /
NEXT_RUN_DATE
---------------------------------------------------------------------------
17-NOV-13 10.50.53.100000 PM EST <-- Now it is EST
SQL>
SY.
[Updated on: Sun, 17 November 2013 09:03] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
