Re: Duplicate scheduler jobs running

Daniel, I have no information on your 12.1 scheduler related issue but I can offer an idea on how to prevent critical processes from running concurrently. Consider adding a dbms_lock call to take an exclusive lock on the program (application task) name. When the job fires the first call in the stored procedure should be to allocate then request the lock with no wait. If another execution of the job is taking place it will have the exclusive user lock and the second process will terminate. This is not a fix but if you have some processes where two executions running at once will result in corrupt information it is a means to protect the application data.

Hopefully when two jobs run in close succession but the first has finished before the second runs the first job will have done all the work and the second will just run without doing much of anything. If the second run causes damage then in this case if a fix from Oracle is not in sight you might have to consider making a call to a job dependency procedure the first call in all your single run per day routines that verify the job has not run within X time, required preceding jobs have ran within N time, etc ....

Sorry for the long post. Since upgrading our databases to Oracle 12.1.0. 2 we have noticed that occasionally (8 times in the last 1.5 days, total jobs run approx 52000 for primary prod db) we have scheduler jobs run twice which causes problems like double billing. We did not see this problem with our 11.2.0.3 databases.

No RAC involved, all DB's are single instance with data guard physical standby at remote site. See below for specific version information.

job_queue_processes = 15
We don't see any evidence that the job_queue_processes setting is too low.

Here is an example job from yesterday that experienced the duplicate job problem:

Scheduler job definition (extracted using DBMS_METADATA.GET_DDL):

BEGIN
dbms_scheduler.create_job('"GET_FREQ_SCHED_INT_164"',

   job_type=>'PLSQL_BLOCK',
   job_action=>'BEGIN ois.px_int_inadv_log_p.get_freq_sched_int;END;',    number_of_arguments=>0,
   start_date=>TO_TIMESTAMP_TZ('30-SEP-2006 07.33.42.442580000 AM GMT','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'),    repeat_interval=> 'FREQ=MINUTELY',
   end_date=>NULL,
   job_class=>'"DEFAULT_JOB_CLASS"',
   enabled=>FALSE,
   auto_drop=>TRUE,
   comments=>NULL
);
dbms_scheduler.set_attribute('"GET_FREQ_SCHED_INT_164"','logging_level',DBMS_SCHEDULER.LOGGING_RUNS); dbms_scheduler.enable('"GET_FREQ_SCHED_INT_164"'); COMMIT;
END; Run 1:

   req start date: 20-JUL-16 08.23.42.198257000 PM GMT    actual start date: 20-JUL-16 08.23.42.248354000 PM GMT    run duration: +00 00:00:00.000000
   status: SUCCEEDED

Run 2:

   req start date: 20-JUL-16 08.24.42.249782000 PM GMT    actual start date: 20-JUL-16 08.24.42.997972000 PM GMT    run duration: +00 00:00:00.000000
   status: SUCCEEDED

Run 3: This is the duplicate run, note that the start date is very close (.148446 seconds) to the previous run.

   req start date: 20-JUL-16 08.24.42.999756000 PM GMT    actual start date: 20-JUL-16 08.24.43.146418000 PM GMT    run duration: +00 00:00:00.000000
   status: SUCCEEDED

Run 4:

   req start date: 20-JUL-16 08.25.42.147797000 PM GMT    actual start date: 20-JUL-16 08.25.43.627054000 PM GMT    run duration: +00 00:00:00.000000
   status: SUCCEEDED

Run 5:

   req start date: 20-JUL-16 08.26.42.629330000 PM GMT    actual start date: 20-JUL-16 08.26.45.226534000 PM GMT    run duration: +00 00:00:00.000000
   status: SUCCEEDED

The duplicate scheduler job problem appears to affect random jobs (i.e. it's not the same couple of jobs that are affected by this issue).

Oracle support suggested bug and applying patch (Patch 22071479: DBMS_SCHEDULER - REPEAT_INTERVAL => BYTIME CLUASE IS NOT WORKING AS EXPECTED) to fix.

I have applied the patch and it did not fix this duplicate scheduler jobs issue in our development database. So I am wondering where to go next as Oracle support hasn't been of much help to date.

I have two questions:

1. Has anyone else seen this scheduler behaviour? If so how did you resolve?
2. Is it possible to trace the job queue controller process? Oracle support has not been forthcoming with any answer to this question.

---------------------------------------- --------------- -----------

---------- ------------------------- --------------- ---------------------------------------- ----------------------------------------------------------------------------------------------------
  22139226 12.1.0.2     APPLY     24-MAY-16 04.40.49.245228 PM      Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016)
  21948354 12.1.0.2     APPLY     24-MAY-16 04.43.20.827736 PM      Database Patch Set Update : 12.1.0.2.160119 (21948354)
  20528052 12.1.0.2     APPLY     24-MAY-16 04.43.20.841383 PM

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