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

Advertisements

About Raheel Syed

Oracle DBA
This entry was posted in RAC, upgrade and tagged , , , , , , , , , , . Bookmark the permalink.

One Response to Fix dbms_scheduler jobs after upgrade

  1. Pingback: 10205 patchset apply on 2-node RAC « Raheel's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s