Re: dbms_scheduler_job_run_details Question

PS.

If you use SQL Developer to create/edit the scheduler jobs, they have made the time zone a field to be chosen - that way you can't miss it ;-)

[image: image.png]

On Wed, Jan 26, 2022 at 2:53 PM Kim Berg Hansen <kibeha_at_gmail.com> wrote:

> Hmm... That output would mean that LOG_DATE also is not in DBTIMEZONE?
> Okay, that might then be the SESSIONTIMEZONE of the session in which the
> job runs, which might be taken from the OS time zone.
> Not quite sure about that.
>
> What is output of select dbtimezone from dual?
>
> You could do AT TIME ZONE DBTIMEZONE on *all three* columns (instead of
> just the *_START_DATE columns).
> Or if you prefer you could use AT TIME ZONE SESSIONTIMEZONE to get the
> output in the time zone of your session where you execute the query.
> Or use AT TIME ZONE 'EST' to have it always fixed in Eastern Standard.
> Or use AT TIME ZONE 'America/New_York' if you want an output that follows
> DST on/off.
>
>
>
> But at least the output shows that it started 05:30:01 in GMT-4, which is
> 04:30:01 in GMT-5.
> Then it stopped a minute later at 04:31:04 in GMT-5, which is the same as
> 05:31:04 in GMT-5.
>
>
> Users say it is supposed to run at 05:30.
> That *is *also what it does - it *does* run at 05:30, which the
> repeat_interval is stating.
> But it is running at 05:30 in *GMT-4*, which is 04:30 in GMT-5.
> So if the users are expecting it to run at 05:30 in GMT-5, then it is not
> running at the time the users are expecting it.
>
> Try the:
>
> select
> to_char(start_date, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR') as start_date
> from dba_scheduler_jobs
> where job_name = 'RIT_K0704_BL_GOALS_DBMS_JOB';
>
> The repeat interval will be used to create starting times in the TZR of
> the start_date of the job.
>
>
> My guess would be that the TZR of the job is -04:00.
> This could happen if DST was active (EDT=GMT-4 instead of EST=GMT-5) at
> the time when the job was created, and a DATE datatype was used in the
> CREATE_JOB call instead of a TIMESTAMP WITH TIME ZONE.
>
> If you can see that the job has -04:00 as TZR, then I suggest dropping the
> job and re-creating it with something like:
>
> begin
> dbms_scheduler.create_job (
> job_name => 'RIT_K0704_BL_GOALS_DBMS_JOB'
> , job_type => {your values}
> , job_action => {your values}
> , start_date => TIMESTAMP '2022-01-27 05:30:00
> America/New_York'
> , repeat_interval => 'Freq=Daily;ByHour=05;ByMinute=30'
> , job_class => {your values}
> , enabled => {your values}
> , auto_drop => {your values}
> , comments => {your values}
> );
> end;
> /
>
> By explicitly stating America/New_York you will get that the job executes
> at 05:30 EST = GMT-5 currently, but 05:30 EDT = GMT-4 when DST is in
> effect.
>
> Of course, that's only if this is what the users wish. If they want 05:30
> GMT-5 all year round, then that's what you specify instead of
> America/New_York.
>
>
> Is that more clear?
>
> Cheerio
> /Kim
>
>
> On Wed, Jan 26, 2022 at 1:36 PM Scott Canaan <srcdco_at_rit.edu> wrote:
>
>> Kim,
>>
>> Interesting. When I run the query near the end that includes the “at
>> time zone dbtimezone”, I get the following output:
>>
>>
>>
>> LOG_DATE
>> REQ_START_DATE_DB_TZ ACTUAL_START_DATE_DB_TZ
>>
>> 2022-01-25 04:31:04.577 -05:00 2022-01-25 05:30:00.129
>> -04:00 2022-01-25 05:30:01.753 -04:00
>>
>>
>>
>> The reason this came up is that the customer has multiple jobs, running
>> under different schedulers, that depend on one another. Yesterday they
>> noticed that the data from another job that supposedly completed at 5:23am
>> was not actually in the database before this job ran. So they are saying
>> that the log_date is telling them that the job didn’t run at its scheduled
>> time of 5:30am, but actually ran at 4:30am.
>>
>>
>>
>> I don’t see how that could happen, since the job definition has this as
>> the repeat_interval:
>>
>>
>>
>> Freq=Daily;ByHour=05;ByMinute=30
>>
>>
>>
>> *Scott Canaan ‘88*
>>
>> *Sr Database Administrator *Information & Technology Services
>> Finance & Administration
>>
>>
>> *Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
>>
>> *srcdco_at_rit.edu <srcdco_at_rit.edu>* | c: (585) 339-8659
>>
>> *CONFIDENTIALITY NOTE*: The information transmitted, including
>> attachments, is intended only for the person(s) or entity to which it is
>> addressed and may contain confidential and/or privileged material. Any
>> review, retransmission, dissemination or other use of, or taking of any
>> action in reliance upon this information by persons or entities other than
>> the intended recipient is prohibited. If you received this in error, please
>> contact the sender and destroy any copies of this information.
>>
>>
>>
>> *From:* Kim Berg Hansen <kibeha_at_gmail.com>
>> *Sent:* Wednesday, January 26, 2022 6:17 AM
>> *To:* Scott Canaan <srcdco_at_rit.edu>
>> *Cc:* oracle-l_at_freelists.org
>> *Subject:* Re: dbms_scheduler_job_run_details Question
>>
>>
>>
>> Hi, Scott
>>
>>
>>
>> As mentioned by Andy, the columns (although named *_DATE) are not DATE
>> datatype, they are timestamps with time zone.
>>
>>
>>
>> Logging is in the database time zone, start dates are in the time zone
>> specified for each specific job.
>>
>>
>>
>> Example:
>>
>>
>>
>> Database is running GMT:
>>
>>
>>
>> select dbtimezone from dual;
>>
>>
>>
>> DBTIME
>>
>> ------
>>
>> +00:00
>>
>>
>> The job was created using an original start date in Europe/Amsterdam:
>>
>>
>>
>> select
>>
>> to_char(start_date, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR') as start_date
>>
>> from dba_scheduler_jobs
>>
>> where job_name = 'MYJOB';
>>
>>
>>
>> START_DATE
>>
>> --------------------------------------------------------
>>
>> 2021-11-25 15:15:00.000 EUROPE/AMSTERDAM
>>
>>
>> The time zones of the job run details show database time zone for
>> LOG_DATE, job time zone for *_START_DATE:
>>
>>
>>
>> select
>>
>> to_char(log_date, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR') as log_date
>>
>> , to_char(req_start_date, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR') as
>> req_start_date
>>
>> , to_char(actual_start_date, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR') as
>> actual_start_date
>>
>> from dba_scheduler_job_run_details
>>
>> where job_name = 'MYJOB'
>>
>> order by log_date desc fetch first 1 row only;
>>
>>
>>
>> LOG_DATE REQ_START_DATE
>> ACTUAL_START_DATE
>>
>>
>> --------------------------------------------------------
>> --------------------------------------------------------
>> --------------------------------------------------------
>>
>> 2022-01-26 10:30:07.863 +00:00 2022-01-26
>> 11:30:00.673 EUROPE/AMSTERDAM 2022-01-26 11:30:00.800
>> EUROPE/AMSTERDAM
>>
>>
>>
>> Amsterdam is currently GMT+1, so we see that the job actually started at
>> 10:30:00.800 GMT and logged when it ended at 10:30:07.863 GMT.
>>
>>
>>
>> If you want to display output of job run details in DB time zone alone
>> (to make it easier for humans to compare), you can use the AT TIME ZONE
>> syntax, like for example this:
>>
>>
>>
>> select
>>
>> to_char(log_date, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR') as log_date
>>
>> , to_char(req_start_date AT TIME ZONE DBTIMEZONE, 'YYYY-MM-DD
>> HH24:MI:SS.FF3 TZR') as req_start_date_db_tz
>>
>> , to_char(actual_start_date AT TIME ZONE DBTIMEZONE, 'YYYY-MM-DD
>> HH24:MI:SS.FF3 TZR') as actual_start_date_db_tz
>>
>> from dba_scheduler_job_run_details
>>
>> where job_name = 'MYJOB'
>>
>> order by log_date desc fetch first 1 row only;
>>
>>
>>
>> LOG_DATE
>> REQ_START_DATE_DB_TZ
>> ACTUAL_START_DATE_DB_TZ
>>
>> --------------------------------------------------------
>> --------------------------------------------------------
>> --------------------------------------------------------
>>
>> 2022-01-26 10:30:07.863 +00:00 2022-01-26
>> 10:30:00.673 +00:00 2022-01-26 10:30:00.800
>> +00:00
>>
>>
>>
>>
>>
>> Notice also, that in a case like this, where DB is running GMT, the time
>> difference for Amsterdam is currently 1 hour - when Daylight Savings Time
>> kick in, the time difference will be 2 hours.
>>
>> That means also, that if you want jobs to follow DST, you must not use a
>> DATE when you create jobs if your DB time zone is not a DST aware time zone.
>>
>> The safest way to create jobs is to use a timestamp with explicit time
>> zone, like for example:
>>
>>
>>
>> begin
>>
>> dbms_scheduler.create_job (
>>
>> job_name => 'MYJOB'
>>
>> , job_type => 'PLSQL_BLOCK'
>>
>> , job_action => 'mypackage.myproc(42);'
>>
>> , start_date => TIMESTAMP '2020-11-06 01:30:00
>> EUROPE/AMSTERDAM'
>>
>> , repeat_interval => 'freq=daily; byhour=1; byminute=30;'
>>
>> , job_class => 'MY_JOB_CLASS'
>>
>> , enabled => false
>>
>> , auto_drop => false
>>
>> , comments => 'Job to do something every night at 01:30'
>>
>> );
>>
>> end;
>>
>> /
>>
>>
>>
>> This will ensure the job runs at 01:30 Amsterdam time, which will be
>> GMT+1 in the winter and GMT+2 in the summer.
>>
>> If I had used a DATE for the START_DATE parameter, it would have been
>> converted to a timestamp in DB timezone, which might be a non-DST aware
>> time zone.
>>
>>
>>
>> On the other hand if I always want a job to start a given time GMT no
>> matter what time of year it is and ignore DST, then I could just use:
>>
>>
>>
>> , start_date => TIMESTAMP '2020-11-06 01:30:00 GMT'
>>
>>
>>
>> Again specifying exactly what I want a job creation, rather than relying
>> on DB time zone.
>>
>>
>>
>>
>>
>> I hope that helps ;-)
>>
>>
>>
>> Cheerio
>>
>> /Kim
>>
>>
>>
>>
>>
>> Regards
>>
>>
>>
>>
>>
>> Kim Berg Hansen
>>
>> Senior Consultant at Trivadis - Part of Accenture
>>
>> Oracle ACE Director
>>
>>
>>
>> Author of Practical Oracle SQL
>> <https://www.apress.com/gp/book/9781484256169>
>>
>> http://www.kibeha.dk
>>
>> kibeha_at_kibeha.dk
>>
>> _at_kibeha <http://twitter.com/kibeha>
>>
>>
>>
>>
>>
>> On Tue, Jan 25, 2022 at 9:44 PM Scott Canaan <srcdco_at_rit.edu> wrote:
>>
>> I got an inquiry from a customer about the dates in the
>> dbms_scheduler_job_run_details view. He is confused as to why the log_date
>> is an hour earlier than the req_start_date and actual_start_date. For
>> example:
>>
>>
>>
>> Log_id log_date
>> Job_name
>> requested_start_date actual_start_date
>>
>> 8610130 1/25/2022 4:31:05 AM
>> RIT_K0704_BL_GOALS_DBMS_JOB 1/25/2022 5:30:00 AM
>> 1/25/2022 5:30:02 AM
>>
>>
>>
>> The requested start time is set in the job definition to be 5:30am. I’m
>> having trouble explaining why there’s a difference. He has asked me to
>> “fix the GMT differential”.
>>
>>
>>
>> This is an Oracle 12.1.0.2 database running on Red Hat 7.
>>
>>
>>
>>
>> *Scott Canaan ‘88 *
>> *Sr Database Administrator *Information & Technology Services
>> Finance & Administration
>>
>>
>> *Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
>>
>> *srcdco_at_rit.edu <srcdco_at_rit.edu>* | c: (585) 339-8659
>>
>> *CONFIDENTIALITY NOTE*: The information transmitted, including
>> attachments, is intended only for the person(s) or entity to which it is
>> addressed and may contain confidential and/or privileged material. Any
>> review, retransmission, dissemination or other use of, or taking of any
>> action in reliance upon this information by persons or entities other than
>> the intended recipient is prohibited. If you received this in error, please
>> contact the sender and destroy any copies of this information.
>>
>>
>>
>>

--
http://www.freelists.org/webpage/oracle-l




image/png attachment: image.png