RE: dbms_job failed to execute

Are you running oracle 9.2.0.4 or 9.2.0.5 on solaris? If so after 497 days of uptime Job Queue processes stop working and you will have to reboot the server. Following is an link to a bug on metalink

https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_ database_id=NOT&p_id=3427424.8      

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Igor Neyman
Sent: 27. október 2005 13:54
To: mag.andersen_at_gmail.com; oracle-l_at_freelists.org Subject: RE: dbms_job failed to execute  

Try to find additional info in the files: <instance_name>_j0000_,,,trc in bdump directory.

Look for the files with the timestamp close to the time when your jobs stopped working.  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Magnus Andersen
Sent: Thursday, October 27, 2005 9:32 AM To: oracle-l_at_freelists.org
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