Oracle job is not running

[Arsalan]

Hello Arsalan,

I made the below oracle job which are not running automatically

if i run manually it working well, kindly guide what will be problem with this script

windows 2003
oracle 11g R2


BEGIN
       DBMS_SCHEDULER.CREATE_JOB
       (
       job_name            => 'merg_customs_data',
       job_type            => 'STORED_PROCEDURE', --'PLSQL_BLOCK',
       job_action          => 'merg_data',
       number_of_arguments => 0,
       start_date          => SYSTIMESTAMP,
       repeat_interval     =>'FREQ=DAILY; BYDAY=SAT,SUN,MON,TUE,WED,THU; BYHOUR=17',
       end_date            => NULL,
       enabled             => TRUE,
       auto_drop           => FALSE,
       comments            => 'This job merg data from d_customs_load to customs_import_data every night at 5:00PM'
       );
 END;
/

Thanks.

[ErmanArslansOracleBlog]

Why don't you use FREQ=DAILY; BYHOUR=17;

What is the state of the job?
SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS;

[Arsalan]

Arsalan bro,

Why don't you use FREQ=DAILY; BYHOUR=17;

because we exclude the Friday here is friday is off ,

the result of your query is below,

SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS;

[ErmanArslansOracleBlog]

Hi Arsala,

Please check the following document and update me:

Oracle Scheduler doesn't execute any job in a cloned database (Doc ID 1267379.1)

[Arsalan]

Hi Arsalan


1- I changed the job_queue_processes from 0 to  1000 values

2-I change the job_priority from 3 to 1 value.



Now it working fine,




Thanks you Arsalan for your supports

[ErmanArslansOracleBlog]

Aha.. Your job_queue_processes parameter was set to 0!!

Starting with Oracle Database release 11.2.0.1, setting JOB_QUEUE_PROCESSES to 0 causes both DBMS_SCHEDULER and DBMS_JOB jobs to not run.
Previosly, it was not like that.. (setting JOB_QUEUE_PROCESSES to 0 caused DBMS_JOB jobs to not run, but DBMS_SCHEDULER jobs were unaffected and would still run)

If you would mention that, no jobs could be running , we could go to the solution faster.. :)

But good for you Arsala.!
thanks for sharing.

[Roshan]

Hi Erman,

on my production database, my JOB_QUEUE_PROCESSES is set to 1000. However, the job which I scheduled is still in scheduled state. Can you please advise what can cause it not to run?

script.txt

Do you think the date is the issue? My timezone is Abu Dhabi,Muscat

Regards,

Roshan

[ErmanArslansOracleBlog]

Your timezone? What do you mean?

The timezone of data is important here, as the job is running by the database.

Are you setting the job date properly? ( I mean considering the time difference, timezone)

[Roshan]

If the actual date on the linux server is:

Sun Sep 24 14:58:04 MUT 2017

 

should I remove the timezone?

 from
 start_date => TIMESTAMP '2017-09-12 14:20:00.000000 US/EASTERN',

to

start_date => TIMESTAMP '09/24/17 15:20:00',

[ErmanArslansOracleBlog]

connect to the database.
use "select sysdate from dual" and "SELECT SYSTIMESTAMP FROM DUAL;" to check the date of the database (default date), then set your job time accordingly.
You can also use the following to check:

select systimestamp,current_timestamp from dual;

SYSTIMESTAMP -> current timestamp on database serve
current_timestamp ->timestamp on client machine