Fix dbms_scheduler jobs after upgrade
After applying 10.2.0.5.6 patch to 10.2.0.4 on 2 node RAC database, dbms_scheduler jobs were effected and do not run at scheduled start time.
MOS Doc ID 727363.1 and 731678.1 helped me understand what caused this and how to fix it, however i was not comfortable with the fixing part from MOS doc per se as the steps involved disabling dbms_scheduler and flushing shared_pool, that i can’t do it in production, then how to do i fix it ? ummm not complicated 
though
Before
Scheduler attribute ‘CURRENT_OPEN_WINDOW’ was set to ‘WEEKEND_WINDOW’. As seen below
SQL> SELECT a.attribute_name, VALUE FROM dba_scheduler_global_attribute a; ATTRIBUTE_NAME VALUE ------------------------------ ---------------------------------------- MAX_JOB_SLAVE_PROCESSES LOG_HISTORY 30 DEFAULT_TIMEZONE Etc/GMT LAST_OBSERVED_EVENT EVENT_EXPIRY_TIME CURRENT_OPEN_WINDOW WEEKEND_WINDOW 6 rows selected.
SQL> select WINDOW_NAME,NEXT_START_DATE,LAST_START_DATE from dba_scheduler_windows; WINDOW_NAME ------------------------------ NEXT_START_DATE --------------------------------------------------------------------------- LAST_START_DATE --------------------------------------------------------------------------- WEEKNIGHT_WINDOW 09-JUL-12 10.00.00.600000 PM ETC/GMT 06-JUL-12 10.00.00.566780 PM ETC/GMT WEEKEND_WINDOW 14-JUL-12 12.00.00.700000 AM ETC/GMT 07-JUL-12 06.00.01.127324 AM ETC/GMT
Fix
SQL> exec dbms_scheduler.disable('WEEKEND_WINDOW');
SQL> exec dbms_scheduler.disable('WEEKNIGHT_WINDOW');
SQL> exec DBMS_SCHEDULER.close_window ('WEEKEND_WINDOW');
Execute after 30 mts
SQL> exec dbms_scheduler.enable('WEEKEND_WINDOW');
SQL> exec dbms_scheduler.enable('WEEKNIGHT_WINDOW');
After
SQL> SELECT a.attribute_name, VALUE FROM dba_scheduler_global_attribute a; ATTRIBUTE_NAME VALUE ------------------------------ ---------------------------------------- MAX_JOB_SLAVE_PROCESSES LOG_HISTORY 30 DEFAULT_TIMEZONE Etc/GMT LAST_OBSERVED_EVENT EVENT_EXPIRY_TIME CURRENT_OPEN_WINDOW 6 rows selected. SQL> select WINDOW_NAME,NEXT_START_DATE,LAST_START_DATE from dba_scheduler_windows; WINDOW_NAME ------------------------------ NEXT_START_DATE --------------------------------------------------------------------------- LAST_START_DATE --------------------------------------------------------------------------- WEEKNIGHT_WINDOW 17-JUL-12 10.00.00.000000 PM ETC/GMT 06-JUL-12 10.00.00.566780 PM ETC/GMT WEEKEND_WINDOW 21-JUL-12 12.00.00.500000 AM ETC/GMT 07-JUL-12 06.00.01.127324 AM ETC/GMT
That’s it ! Now monitor dbms_scheduler jobs. If you are using TOAD, select any scheduler job and then go to ‘Run Log’ tab, compare date & time from ‘required start date’ with ‘actual start date’
Cheers !!
Raheel Syed
-
July 21, 2012 at 6:34 am | #110205 patchset apply on 2-node RAC « Raheel's Blog
