NEXT_DATE in dba_jobs is stuck

[nir_s]

Hi all,

I was faced with something really strange:
The jobs are not run,as i can see in dba_jobs.
Also,there are no rows selected in dba_running_jobs.
As you can see from the query from dba_jobs,all the NEXT_DATE values arec stuck on 29/1/04.
All of them are not broken and their FAILURES column shows 0 failures.
I've checked the init*.ora parameters:
SQL> show parameter job

What could be the problem?

Thanks in advance,
Nir

NAME TYPE VALUE
------------------------------------ ------- ------------------------------
job_queue_interval integer 60
job_queue_processes integer 6


SQL> select job,LOG_USER "log_usr",PRIV_USER "priv_usr",SCHEMA_USER "schema",LAST_DATE,
2 NEXT_DATE,INTERVAL,FAILURES "fails" ,broken,what
3 from dba_jobs;

JOB log_usr priv_usr schema LAST_DATE NEXT_DATE INTERVAL fails BR WHAT
----- ---------- ---------- ---------- ----------------- ----------------- --------------- ----- --
1 TRP TRP TRP 30/01/04 00:00:00 to_date( to_cha N TRP.REACTIVATE_FILTERS;
r(sysdate+2,'DD
/MM/YYYY'),'DD/
MM/YYYY HH24:MI
:SS')

2 TRP TRP TRP 29/01/04 00:01:15 sysdate + 1 N TRP.DELETE_OLD_LOGGING;
3 TRP TRP TRP 29/01/04 00:01:15 sysdate + 1/24 N TRP.CASE_SEVERITY_CALCULA
4 TRP TRP TRP 29/01/04 00:01:15 sysdate + 1/24 N TRP.FIX_ALERTS_SUMMARY;
5 TRP TRP TRP 29/01/04 00:01:51 SYSDATE + (15/1 N Cdrsummary('TBL_CDR_SUM_1
440)

JOB log_usr priv_usr schema LAST_DATE NEXT_DATE INTERVAL fails BR WHAT
----- ---------- ---------- ---------- ----------------- ----------------- --------------- ----- --

6 TRP TRP TRP 29/01/04 00:15:19 SYSDATE+1/24/12 N CREATE_SUM_TABLES.CREATE_
7 TRP TRP TRP 29/01/04 00:15:19 SYSDATE+1/24/12 N CREATE_SUM_TABLES.CREATE_
8 TRP TRP TRP 29/01/04 00:15:19 SYSDATE+1/24/12 N CREATE_SUM_TABLES.CREATE_
9 TRP TRP TRP 29/01/04 00:15:19 SYSDATE+1/24/12 N CREATE_SUM_TABLES.CREATE_
10 TRP TRP TRP 29/01/04 00:15:19 SYSDATE+1/24/12 N CREATE_SUM_TABLES.CREATE_
11 TRP TRP TRP 29/01/04 00:15:19 SYSDATE+1/24/12 N CREATE_SUM_TABLES.CREATE_
12 TRP TRP TRP 29/01/04 00:02:14 sysdate+1/288 N dbms_refresh.refresh('"TR
13 TRP TRP TRP 29/01/04 00:02:15 sysdate+1/288 N dbms_refresh.refresh('"TR
14 TRP TRP TRP 29/01/04 00:02:15 sysdate+1/288 N dbms_refresh.refresh('"TR
15 TRP TRP TRP 29/01/04 00:02:15 sysdate+1/288 N dbms_refresh.refresh('"TR

JOB log_usr priv_usr schema LAST_DATE NEXT_DATE INTERVAL fails BR WHAT
----- ---------- ---------- ---------- ----------------- ----------------- --------------- ----- --
16 TRP TRP TRP 29/01/04 00:02:16 sysdate+1/288 N dbms_refresh.refresh('"TR
17 TRP TRP TRP 29/01/04 00:04:13 SYSDATE + 1/288 N delete TBL_PS_A_LOG where
TBL_PS_A_LOG);

18 TRP TRP TRP 29/01/04 00:04:16 SYSDATE + 1 N PS_Alerts_Cleaning();
19 TRP TRP TRP 29/01/04 00:15:28 SYSDATE + 1 N PS_Alerts_Cleaning();
20 TRP TRP TRP 29/01/04 00:19:01 sysdate + 1/24 N dbms_refresh.refresh('"TR
21 TRP TRP TRP 29/01/04 00:19:01 sysdate + 1/24 N dbms_refresh.refresh('"TR
22 TRP TRP TRP 29/01/04 00:19:02 sysdate + 1/24 N dbms_refresh.refresh('"TR
23 TRP TRP TRP 29/01/04 00:19:03 sysdate + 1/24 N dbms_refresh.refresh('"TR
24 TRP TRP TRP 29/01/04 00:19:04 sysdate + 1/24 N dbms_refresh.refresh('"TR

JOB log_usr priv_usr schema LAST_DATE NEXT_DATE INTERVAL fails BR WHAT
----- ---------- ---------- ---------- ----------------- ----------------- --------------- ----- --
25 TRP TRP TRP 29/01/04 00:19:05 sysdate + 1/24 N dbms_refresh.refresh('"TR

[slimdave]

Platform and version?

[nir_s]

Sorry,i've not mentioned it:
Platform - HP-UX B.11.11 U 9000/800 539760581 unlimited-user license
version - 8.1.7.4

[slimdave]

Do you see SNP0, SNP1 etc. processes at the o/s level?

[marist89]

errors in alert.log, bdump?

[marist89]

also I would create a simple job that fires immediately to verify it was working.

[nir_s]

Hi guys,

There are SNP processes at O/S level,the alert.log is clean,no trace files in udump.
I'll try to create a new job and trace for it.
Now i'm at home and tomorrow i'm working at different place,so i'll try it on Tuesday and i'll let you know.
Meanwhile,i'll be glad to have more ideas.

Thanks in advance.
Nir

[slimdave]

You might try "resetting" the job queue by ...

alter system set job_queue_processes = 0;

... then wait a few minutes and ...

alter system set job_queue_processes = 8; -- or whatever

[pando]

what is OS date?

[stmontgo]

can you execute the jobs manually as the owner of the jobs and are you sure there are no snmp type trace files in the udump or bdump?

steve