Wednesday, February 8, 2012

Oracle Scheduler does not works as expected

It is possible that the Oracle Scheduler to hang if you change a windows attribute and make a mistake or after upgrades from 10.2.0.3 - > 10.2.0.5.

Usually you will see that shceduled jobs that no executed correct and in the cjq trace files you will find the follwoing:

jsksGetCurWindowId:1 got error 27468
jsksGetCurWindowId:1 got error 27468

So if you fall into this please execute the follwoing as sysdba (Assuming default scheduler windows):

variable v number;

begin
  dbms_scheduler.disable('WEEKEND_WINDOW');
 dbms_scheduler.disable('WEEKNIGHT_WINDOW');
 dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE');
 commit;
end;
/
select value from v$parameter where name='job_queue_processes';
--Please keep this number (assume J)

alter system set job_queue_processes=0;

begin
 dbms_ijob.set_enabled(FALSE);
 commit;
end;
/

-- 2 times is a must
alter system flush shared_pool;
alter system flush shared_pool;


declare
cwo number;
begin
select o.obj# into cwo from sys.obj$ o where
o.name = 'CURRENT_OPEN_WINDOW' and o.namespace = 51;


update sys.scheduler$_global_attribute set value = null, attr_tstamp = null,
attr_intv = null, additional_info = null where obj# = cwo;

end;
/

begin
 dbms_ijob.set_enabled(TRUE);
 commit;
end;
/




begin
 dbms_scheduler.enable('WEEKEND_WINDOW');
 dbms_scheduler.enable('WEEKNIGHT_WINDOW');
 dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE');
 dbms_ijob.set_enabled(TRUE);
 commit;
end;
/

--Set job_queue_processes to its original setting using J
alter system set job_queue_processes=J;

exit;

Please check the the windows group you have created (MAINTENACE_WINDOW_GROUP) is the default in order to see if your windows are members or need to be added again(Use EM).

References 
Oracle Support Note ID: 731678.1