Re: dbms_job failed to execute

We had similar problem on many 9.2.0.4 instances on Solaris. The bug is supposed to be fixed in 9.2.0.6/10.1.0.2 but you can check that note anyway.  

MetaLink Note 3240280.8

        Bug 3240280 - Jobs may suddenly stop although next_date <= sysdate

HTH,
Dimitre Radoulov

• Original Message ----- From: Magnus Andersen To: oracle-l_at_freelists.org Sent: Thursday, October 27, 2005 3:32 PM Subject: dbms_job failed to execute

  Hi All,

  I have several dbms_jobs scheduled and have had them there for a long time. Tuesday they stopped being executed on appointed times. I've looked in Metalink, but have not found anything. I have no errors in my alert log, nor do I have any failures. Being a "newbie" I'm not sure where to go from here. Below is some system information. Please let me know if there is anything else I need to provide for this to be meaningful to you.

  Any help/advice is greatly appreciated.

  Thanks,
  --
  Magnus Andersen
  Systems Administrator / Oracle DBA
  Walker & Associates, Inc.

  System Information:
  Server = HP DL 580
  OS = RHEL 3 AS Update 4
  DB = 9.2.0.6
  Output from dba_jobs =

         JOB    LOG_USER    PRIV_USER    SCHEMA_USER    LAST_DATE    LAST_SEC    THIS_DATE    THIS_SEC    NEXT_DATE    NEXT_SEC    TOTAL_TIME    BROKEN    INTERVAL    FAILURES    WHAT    NLS_ENV    MISC_ENV    INSTANCE
  1    143    SYSADM    SYSADM    SYSADM    27-Oct-05 7:07:51 AM    07:07:51            28-Oct-05 10:03:00 PM    22:03:00    54    N    "trunc(sysdate+1) + 22.05/24


  "    0    wa_service_level;    NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'    0102000000000000    0

  2    142    SYSADM    SYSADM    SYSADM    27-Oct-05 7:07:46 AM    07:07:46            28-Oct-05 10:00:00 PM    22:00:00    86    N    "trunc(sysdate+1) + 22.00/24


  "    0    wa_ship_label_table_cleanup;    NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'    0102000000000000    0

  3    201    SYSADM    SYSADM    SYSADM    27-Oct-05 7:14:59 AM    07:14:59            28-Oct-05 5:24:00 AM    05:24:00    91216    N    trunc(sysdate+1) + 05.40/24    0    wa_forecast_driver('Y',12,wa_get_period(trunc(sysdate)));    NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'    0102000200000000    0
  4    145    SYSADM    SYSADM    SYSADM    27-Oct-05 7:07:56 AM    07:07:56            28-Oct-05 10:30:00 PM    22:30:00    21115    N    trunc(sysdate+1) + 22.50/24    0    wa_run_product_update;    NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'    0102000200000000    0
  5    146    SYSADM    SYSADM    SYSADM    27-Oct-05 7:11:21 AM    07:11:21            28-Oct-05 11:27:00 PM    23:27:00    396    N    trunc(sysdate+1) + 23.45/24    0    wa_wynncomm_updates;    NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'    0102000000000000    0
  6    161    SYSADM    SYSADM    SYSADM    27-Oct-05 7:11:36 AM    07:11:36            28-Oct-05 5:24:00 AM    05:24:00    11219    N    trunc(least(next_day(sysdate, 'MONDAY'), next_day(sysdate, 'TUESDAY'), next_day(sysdate, 'WEDNESDAY'), next_day(sysdate, 'THURSDAY'), next_day(sysdate, 'FRIDAY'))) + 05.40/24    0    wa_customer_profile_generator;    NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'    0102000200000000    0
  7    805    SYSADM    SYSADM    SYSADM    27-Oct-05 7:15:15 AM    07:15:15            28-Oct-05 6:30:00 PM    18:30:00    46549    N    trunc(least(next_day(sysdate, 'MONDAY'), next_day(sysdate, 'TUESDAY'), next_day(sysdate, 'WEDNESDAY'), next_day(sysdate, 'THURSDAY'), next_day(sysdate, 'FRIDAY'))) + 18.5/24    0    wa_acct_control_updates;    NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'    0102000200000000    0
  8    17535    SYSADM    SYSADM    SYSADM    27-Oct-05 7:17:11 AM    07:17:11            27-Oct-05 7:32:00 AM    07:32:00    86    N    trunc(SYSDATE+1/96,'MI')    0    po_notify;    NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'    0102000000000000    0

--
http://www.freelists.org/webpage/oracle-l