Hi
Oracle 12.1.0.2.0
Primary site 2 node rac
Secondary site single instance database.
I have following problem. Since yesterday we switchovered production database to second site, I noticed that one scheduler job delays its start. Now it has happened 4 times in a very small time period.
When I started investigating, I noticed that this same problems occurs also in our primary site, but the delay is much less. Compared to when running from secondary site 22 minutes.
I have checked job_queue_processes parameter and it was set to value that would have to be enough i.e. 1000. In our primary site it was set to 20, but this still seems enough in my opinion.
There is very little load on secondary site database, since it's weekend.
Job is created like this:
BEGIN
SYS.DBMS_SCHEDULER.DROP_JOB
(job\_name => 'ETOIMIK.OIGUSED\_MVIEW\_REFRESH');
END;
/
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job\_name => 'ETOIMIK.OIGUSED\_MVIEW\_REFRESH'
,start\_date => TO\_TIMESTAMP\_TZ('2017/06/08 20:36:38.000000 +03:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
,repeat\_interval => 'FREQ=SECONDLY; INTERVAL=10;'
,end\_date => NULL
,job\_class => 'DEFAULT\_JOB\_CLASS'
,job\_type => 'PLSQL\_BLOCK'
,job\_action => 'begin materialiseeritudoigused.AUTOMATIC\_MVIEW\_REFRESH; end;'
,comments => NULL
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'ETOIMIK.OIGUSED\_MVIEW\_REFRESH'
,attribute => 'RESTARTABLE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'ETOIMIK.OIGUSED\_MVIEW\_REFRESH'
,attribute => 'LOGGING\_LEVEL'
,value => SYS.DBMS\_SCHEDULER.LOGGING\_OFF);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'ETOIMIK.OIGUSED\_MVIEW\_REFRESH'
,attribute => 'MAX\_FAILURES');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'ETOIMIK.OIGUSED\_MVIEW\_REFRESH'
,attribute => 'MAX\_RUNS');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'ETOIMIK.OIGUSED\_MVIEW\_REFRESH'
,attribute => 'STOP\_ON\_WINDOW\_CLOSE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'ETOIMIK.OIGUSED\_MVIEW\_REFRESH'
,attribute => 'JOB\_PRIORITY'
,value => 3);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'ETOIMIK.OIGUSED\_MVIEW\_REFRESH'
,attribute => 'SCHEDULE\_LIMIT');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'ETOIMIK.OIGUSED\_MVIEW\_REFRESH'
,attribute => 'AUTO\_DROP'
,value => FALSE);
SYS.DBMS_SCHEDULER.ENABLE
(name => 'ETOIMIK.OIGUSED\_MVIEW\_REFRESH');
END;
/
I know that 12.1 had some problem with job delay, but I have applied this patch also, and after that everything seemed normal. Until I found out this delay this morning.
19417094 19417094 Mon Jan 29 20:14:56 EET 2018 CHANGE IN EXECUTION DELAY FOR DBMS_JOBS SUBMITTED UNDER 12.1.0.2
**And enabled event:
**SQL> alter system set event="27401 trace name context forever,level 1048576" scope=spfile;
Regards
Raul