Monday, June 02, 2008

something wrong or Bug on SCHEDULER: after Patch 10.2.0.4 on linux x86_64

After I patched Oracle database 10.2.0. linux x86_64, I found something wrong about Scheduler Job.

I have created "TEST" Scheduler Job:

SQL> select job_name, repeat_interval, state from user_scheduler_jobs where job_name='TEST';

JOB_NAME REPEAT_INTERVAL STATE
-------------------- ------------------------------ ----------
TEST FREQ=MINUTELY SCHEDULED

My job should run every minutes, But I found It haven't done like that.

JOB_NAME STATUS REQ_START_DATE ACTUAL_START_DATE
---------- --------------- --------------------------------------------- ---------------------------------------------
RUN_DURATION ERROR#
------------- ----------
+000 00:00:00 0

TEST SUCCEEDED 31-MAY-08 03.15.42.000000 AM ASIA/BANGKOK 31-MAY-08 03.15.42.102738 AM ASIA/BANGKOK
+000 00:00:00 0

TEST SUCCEEDED 31-MAY-08 03.16.42.000000 AM ASIA/BANGKOK 01-JUN-08 12.57.23.500716 PM ASIA/BANGKOK
+000 00:00:00 0

TEST SUCCEEDED 01-JUN-08 12.57.42.000000 PM ASIA/BANGKOK 01-JUN-08 01.07.19.938139 PM ASIA/BANGKOK
+000 00:00:00 0

TEST SUCCEEDED 01-JUN-08 01.07.42.000000 PM ASIA/BANGKOK 01-JUN-08 01.27.57.945628 PM ASIA/BANGKOK
+000 00:00:00 0

TEST SUCCEEDED 01-JUN-08 05.09.42.000000 PM ASIA/BANGKOK 01-JUN-08 05.20.35.476359 PM ASIA/BANGKOK
+000 00:00:01 0

TEST SUCCEEDED 01-JUN-08 05.20.42.000000 PM ASIA/BANGKOK 01-JUN-08 05.21.15.454726 PM ASIA/BANGKOK
+000 00:00:00 0

TEST SUCCEEDED 01-JUN-08 05.21.42.000000 PM ASIA/BANGKOK 01-JUN-08 05.31.04.582346 PM ASIA/BANGKOK
+000 00:00:00 0

TEST SUCCEEDED 01-JUN-08 05.31.42.000000 PM ASIA/BANGKOK 01-JUN-08 11.18.02.179848 PM ASIA/BANGKOK
+000 00:00:00 0

JOB_NAME STATUS REQ_START_DATE ACTUAL_START_DATE
---------- --------------- --------------------------------------------- ---------------------------------------------
RUN_DURATION ERROR#
------------- ----------

TEST SUCCEEDED 01-JUN-08 11.18.42.000000 PM ASIA/BANGKOK 01-JUN-08 11.48.32.325631 PM ASIA/BANGKOK
+000 00:00:00 0

TEST SUCCEEDED 01-JUN-08 11.48.42.000000 PM ASIA/BANGKOK 02-JUN-08 06.10.06.902078 AM ASIA/BANGKOK
+000 00:00:00 0


I'm not sure I patched to resolve Bug or increase more ... Bug on my system......

Solved =>

SQL> select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE where attribute_name='CURRENT_OPEN_WINDOW';

ATTRIBUTE_NAME  VALUE
---------------------   ---------
CURRENT_OPEN_WINDOW WEEKNIGHT_WINDOW

SQL> exec DBMS_SCHEDULER.close_window ('WEEKNIGHT_WINDOW');

PL/SQL procedure successfully completed.

SQL> select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE where attribute_name='CURRENT_OPEN_WINDOW';

ATTRIBUTE_NAME   VALUE
---------------------   ---------
CURRENT_OPEN_WINDOW


Force to close by "DBMS_SCHEDULER.close_window"

2 comments:

madhu said...

I ran into a scheduler issue after upgrade to 10.2.0.4. In my case it, the scheduler was in incorrect scheduler "window".

Run this:
select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE where attribute_name='CURRENT_OPEN_WINDOW';
If it shows the wrong window for the current time, for example, "Weekend_window" during week day you are probably hitting the same issue. Close the window using: :dbms_scheduler.close_window.

HunterX said...

thank you....


SQL> select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE where attribute_name='CURRENT_OPEN_WINDOW';

ATTRIBUTE_NAME
------------------------------
VALUE
--------------------------------------------------------------------------------
CURRENT_OPEN_WINDOW
WEEKNIGHT_WINDOW

SQL> exec DBMS_SCHEDULER.close_window ('WEEKNIGHT_WINDOW');

PL/SQL procedure successfully completed.

SQL> select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE where attribute_name='CURRENT_OPEN_WINDOW';

ATTRIBUTE_NAME
------------------------------
VALUE
--------------------------------------------------------------------------------
CURRENT_OPEN_WINDOW