Thanks for the question, Q.

Asked: June 09, 2023 - 12:46 pm UTC

Last updated: July 03, 2023 - 2:25 am UTC

Version: 19.13.0.0.0

Viewed 1000+ times

You Asked

The job in the scheduled task is not executed regularly！
My database job have not ran since 2023/6/1, one of the job's name is "JOB_WORK".
I wanna know why the job stopped suddenly ,Please help me,thanks a lot!

(1) I Checked the parameters:

show parameter job_queue_processes;

NAME TYPE VALUE
------------------- ------- -----
job_queue_processes integer 50

(2) I checked some views:

SELECT t.job_name,t.enabled,t.logging_level,
to_char(t.last_start_date,'yyyy-mm-dd hh24:mi:ss') last_start_date,to_char(t.next_run_date,'yyyy-mm-dd hh24:mi:ss') next_run_date FROM dba_scheduler_jobs t where t.JOB_NAME ='JOB_WORK';
JOB_NAME, ENABLED, LOGGING_LEVEL, LAST_START_DATE,      NEXT_RUN_DATE
JOB_WORK  TRUE    RUNS           2023-06-01 16:08:20 2023-06-08 16:13:40

On 2023/6/9,I run the job 'JOB_WORK' manually for twice,they all SUCCEEDED!

SELECT         T.OWNER, T.JOB_NAME,T.STATUS,
       TO_CHAR(T.ACTUAL_START_DATE, 'YYYY-MM-DD HH24:MI:SS') AS  ACTUAL_START_DATE,
    TO_CHAR(ACTUAL_START_DATE + NUMTODSINTERVAL(SUBSTR(RUN_DURATION,6,2),'HOUR') +
    NUMTODSINTERVAL(SUBSTR(RUN_DURATION,9,2),'MINUTE')+
    NUMTODSINTERVAL(SUBSTR(RUN_DURATION,12,2),'SECOND')
     ,'YYYY-MM-DD HH24:MI:SS') AS ACTUAL_END_DATE,
       T.ADDITIONAL_INFO,
       T.ERROR# 
  FROM dba_SCHEDULER_JOB_RUN_DETAILS T 
  where TO_CHAR(T.LOG_DATE, 'YYYY-MM-DD HH24:MI:SS') >= '2023-05-31 00:00:00' and T.JOB_NAME='JOB_WORK'
 ORDER BY LOG_DATE DESC;
OWNER, JOB_NAME,        STATUS,         ACTUAL_START_DATE,     ACTUAL_END_DATE,   ADDITIONAL_INFO, ERROR#
ZHTEST JOB_WORK SUCCEEDED 2023-06-09 17:13:50 2023-06-09 17:13:50 (null)         0
ZHTEST JOB_WORK SUCCEEDED 2023-06-09 17:12:17 2023-06-09 17:12:18 (null)  0
ZHTEST JOB_WORK SUCCEEDED 2023-06-01 16:08:20 2023-06-01 16:08:20 (null)  0
ZHTEST JOB_WORK SUCCEEDED 2023-06-01 16:08:00 2023-06-01 16:08:01 (null)  0
ZHTEST JOB_WORK SUCCEEDED 2023-06-01 16:07:40 2023-06-01 16:07:41 (null)  0
ZHTEST JOB_WORK SUCCEEDED 2023-06-01 16:07:20 2023-06-01 16:07:20 (null)  0

DDL of the job:

BEGIN 
dbms_scheduler.create_job('"JOB_WORK"',
job_type=>'STORED_PROCEDURE', 
job_action=>
'JOB_WORK_P'
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('29-APR-2022 12.00.00.000000000 AM +08:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), 
repeat_interval=> 'Freq=Secondly;Interval=20'
, end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE, auto_drop=>FALSE,comments=>
NULL
);
sys.dbms_scheduler.set_attribute('"JOB_WORK"','NLS_ENV','NLS_LANGUAGE=''SIMPLIFIED CHINESE'' NLS_TERRITORY=''CHINA'' NLS_CURRENCY=''￥'' NLS_ISO_CURRENCY=''CHINA'' NLS_NUMERIC_CHARACTERS=''.,'' NLS_CALENDAR=''GREGORIAN'' NLS_DATE_FORMAT=''DD-MON-RR'' NLS_DATE_LANGUAGE=''SIMPLIFIED CHINESE'' NLS_SORT=''BINARY'' NLS_TIME_FORMAT=''HH.MI.SSXFF AM'' NLS_TIMESTAMP_FORMAT=''DD-MON-RR HH.MI.SSXFF AM'' NLS_TIME_TZ_FORMAT=''HH.MI.SSXFF AM TZR'' NLS_TIMESTAMP_TZ_FORMAT=''DD-MON-RR HH.MI.SSXFF AM TZR'' NLS_DUAL_CURRENCY=''￥'' NLS_COMP=''BINARY'' NLS_LENGTH_SEMANTICS=''BYTE'' NLS_NCHAR_CONV_EXCP=''FALSE''');
dbms_scheduler.enable('"JOB_WORK"');
COMMIT; 
END;

The procedure of the job:

select owner,object_name,object_type,last_ddl_time,status from dba_objects where object_name = 'JOB_WORK_P';
ZHTEST JOB_WORK_P PROCEDURE 25-5月 -23 VALID

and Connor said...

A few things to look at

a) has anyone been tinkering with the DEFAULT_JOB_CLASS ?
b) Are there windows defined that might be stopping work from occurring
c) Are *other* jobs running? Has anyone turned off the entire scheduler?

Rating

(5 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Scheduler job issues

A reader, June 13, 2023 - 6:51 am UTC

Hello,

if it helps, check the next start date and the status of the job.
That can shed more light.

Also, it can be caused by the TZ issues which may have caused the next start date to might as well have been in the past hence it is not executing.

Hope it helps.

Cheers!

June 13, 2023 - 9:14 am UTC

Good suggestions

The answer maybe is incorrect

Q M, June 15, 2023 - 1:39 am UTC

a) I'm not sure if anyone has modified DEFAULT_JOB_CLASS , is there a way to check it ?

b)

select * from dba_SCHEDULER_WINDOWS;

I checked dba_SCHEDULER_WINDOWS, LAST_START_DATE of window was all in may, so there is no window stopping the job.

c) the entire scheduler jobs have all stopped , but I asked the developers, they said hadn't changed the job.

June 19, 2023 - 4:35 am UTC

Re (b), that in itself is cause for concern, because the standard maintenance windows should open every night.

Not sure what you response to (c) is .... are you saying *no* jobs are running?

TZ issues

Q M, June 15, 2023 - 2:02 am UTC

SELECT t.job_name,t.enabled,t.logging_level,
to_char(t.last_start_date,'yyyy-mm-dd hh24:mi:ss') last_start_date,to_char(t.next_run_date,'yyyy-mm-dd hh24:mi:ss') next_run_date FROM dba_scheduler_jobs t where t.JOB_NAME ='JOB_WORK';
JOB_NAME, ENABLED, LOGGING_LEVEL, LAST_START_DATE,      NEXT_RUN_DATE
JOB_WORK  TRUE    RUNS           2023-06-01 16:08:20 2023-06-08 16:13:40

the job is ENABLED and LAST_START_DATE is 2023-06-01 16:08:20, NEXT_RUN_DATE is 2023-06-08 16:13:40,but 'Freq=Secondly;Interval=20' , the NEXT_RUN_DATE is wrong.

TZ is Asia/Shanghai (CST, +0800),and TZ haven't been changed ,maybe not TZ issues casued the problem.

all the job have stopped

Q M, June 19, 2023 - 9:06 am UTC

to (C),I'm sure all the job have stopped!

select * from DBA_SCHEDULER_RUNNING_JOBS;

SELECT * FROM DBA_JOBS_RUNNING;

there is no result in DBA_SCHEDULER_RUNNING_JOBS and DBA_JOBS_RUNNING.

July 03, 2023 - 2:25 am UTC

Take a look here

https://connor-mcdonald.com/2020/01/20/has-my-scheduler-been-turned-off/

See if someone has disabled the entire tool

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database

Breadcrumb

Question and Answer