Broken Jobs and
the Job Queue [ID 103349.1]
***Checked for
relevance on 11-Jul-2012***
NOTE:
=====
The discussed
functionality is still available in Oracle 10g and Oracle 11g.
However, with
Oracle 10gR1 the DBMS_SCHEDULER package was introduced with many new views.
It is advised to
use the new Oracle scheduler instead of old style Oracle jobs.
=====
The job queue is
used to schedule the execution of a stored procedure at a
specified timed
interval. This tool is the only way the
database can be forced
to perform a
action more than once.
If you are
receiving multiple ORA-12012 errors you should check for any broken jobs.
Error:
ORA 12012
Text:
error on auto execute of job
-------
------------------------------------------------------------------------
Cause:
Some kind of error was caught while doing an automatic execute of a job.
Action: Look at the accompanying errors for
details on why the execute failed.
This can be done
by executing the following SQL statement:
SELECT JOB
FROM DBA_JOBS
WHERE BROKEN = 'Y';
-or-
SELECT FAILURES, JOB
FROM DBA_JOBS;
If this statement
returns a job with the number of failures set to 16 the job
has been broken.
Also, note the
following:
a) A job does not have to have failures = 16
to be broken. (e.g. if you break
the job manually, this does not set the #
of failures to 16)
b) A job can be broken and failures can be
greater than 16. (e.g. if the job
automatically breaks after 16 failures and
the job is manually run and fails
again, the job will remain broken and
failures will be incremented to 17).
Once a job is
broken it no longer will be executed by the database. If a job
returns an error
while Oracle is attempting to execute it, Oracle tries to
execute it again. The first attempt is made
after one minute, the second
attempt after two
minutes, the third after four minutes, and so on, with the
interval doubling
between each attempt. If the job fails 16 times, Oracle
automatically marks
the job as broken and no longer tries to execute it.
However, between
attempts, you have the opportunity to correct the problem
that is preventing
the job from running. This will not disturb the retry
cycle, and Oracle
will eventually attempt to run the job again.
There are two ways
to unbreak a broken job:
1. EXECUTE DBMS_JOB.RUN(JOB NUMBER);
Example:
EXECUTE DBMS_JOB.RUN(10);
This statement will force job 10 to
execute immediately. If this is
successful the job will complete and reset
failures to 0. This would
then flag the job as being unbroken.
2. EXECUTE DBMS_JOB.BROKEN
(JOB
=> ,
BROKEN => )
Example:
EXECUTE DBMS_JOB.BROKEN(
JOB
=> 10,
BROKEN => FALSE);
The execution of this statement would
unbreak job 10.
If the execution
of either one of these procedures returns an error saying that
the specified job
can not be found have the client log in as the owner of the
job and then
attempt the statement again. The owner
of the job is the same as
the person who
submitted the job.
Finally, you
should always check the alert.log for any accompanying error
messages along
with looking in the background_dump_dest directory for any
generated SNP
trace files.