DBA_Jobs: auto execute of job 26 error (ORA-12012)

[daljitsb]

Environment: Windows, Oracle 10.1

On one of my databases, Job 26 from DBA_JOBS shows invalid Dates & Times and is throwing errors in the alert log every few minutes and sometimes choke the db. The db is up for about 2 years and is 50GB in size.

Tue Apr 17 09:57:47 2007
Errors in file d:\oracle\product\10g\admin\ops10g\bdump\ops10g_j000_3532.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj# 203, file 1, block 1490 (2)
ORA-12012: error on auto execute of job 26
ORA-08102: index key not found, obj# 203, file 1, block 1490 (2)

JOB: 26
LOG_USER: SYSMAN
LAST_DATE: 9/23/2006 10:31:50 AM
LAST_SEC: 10:31:50
THIS_DATE: 4/17/2007 11:13:42 AM
THIS_SEC: 11:13:42
NEXT_DATE: 9/23/2006 10:32:52 AM
NEXT_SEC: 10:32:52
INTERVAL: sysdate + 1 / (24 * 60)
FAILURES: 0
WHAT: EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();

See attached file too.

How can I have this fixed.

-D

[davey23uk]

https://metalink.oracle.com/metalink...p_id=1034886.6

[daljitsb]

An ORA-08102 indicates that there is a mismatch between the key(s) stored in
the index and the values stored in the table.

Table involved is: sys.job$
Index is: I_JOB_NEXT
I_JOB_NEXT on JOB$ (NEXT_DATE)

SQL> ANALYZE TABLE job$ VALIDATE STRUCTURE CASCADE;

ANALYZE TABLE job$ VALIDATE STRUCTURE CASCADE

ORA-00054: resource busy and acquire with NOWAIT specified

What now?
-D

[PAVB]

Kill all jobs hitting your tables/indexes or wait for them to end.

If analyze validate structure does not reports errors, then rebuild offending indexes, gather fresh stats.

[daljitsb]

But it seems the issue is with the Date and Time for the job 26 and index is based on Next_Date

LAST_DATE: 9/23/2006 10:31:50 AM
LAST_SEC: 10:31:50
THIS_DATE: 4/17/2007 11:13:42 AM
THIS_SEC: 11:13:42
NEXT_DATE: 9/23/2006 10:32:52 AM
NEXT_SEC: 10:32:52

How do I get the job parameters fixed.