Broken Jobs and the Job Queue [ID 103349.1]

***Checked forrelevance on 11-Jul-2012***

NOTE:

=====

The discussedfunctionality is still available in Oracle 10g and Oracle 11g.

However, withOracle 10gR1 the DBMS_SCHEDULER package was introduced with many new views.

It is advised touse the new Oracle scheduler instead of old style Oracle jobs.

=====

The job queue isused to schedule the execution of a stored procedure at a

specified timedinterval. This tool is the only way thedatabase can be forced

to perform aaction more than once.

If you arereceiving multiple ORA-12012 errors you should check for any broken jobs.

Error:ORA 12012

Text:error on auto execute of job <num>

-------------------------------------------------------------------------------

Cause:Some kind of error was caught while doing an automatic execute of a job.

Action: Look at the accompanying errors fordetails on why the execute failed.

This can be doneby executing the following SQL statement:

SELECT JOB

FROM DBA_JOBS

WHERE BROKEN = 'Y';

-or-

SELECT FAILURES, JOB

FROM DBA_JOBS;

If this statementreturns a job with the number of failures set to 16 the job

has been broken.

Also, note thefollowing:

a) A job does not have to have failures = 16to 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 begreater than 16. (e.g. if the job

automatically breaks after 16 failures andthe job is manually run and fails

again, the job will remain broken andfailures will be incremented to 17).

Once a job isbroken it no longer will be executed by the database. If a job

returns an errorwhile Oracle is attempting to execute it, Oracle tries to

execute it again. The first attempt is madeafter one minute, the second

attempt after twominutes, the third after four minutes, and so on, with the

interval doublingbetween each attempt. If the job fails 16 times, Oracle

automatically marksthe job as broken and no longer tries to execute it.

However, betweenattempts, you have the opportunity to correct the problem

that is preventingthe job from running. This will not disturb the retry

cycle, and Oraclewill eventually attempt to run the job again.

There are two waysto unbreak a broken job:

1. EXECUTE DBMS_JOB.RUN(JOB NUMBER);

Example:

EXECUTE DBMS_JOB.RUN(10);

This statement will force job 10 toexecute immediately. If this is

successful the job will complete and resetfailures to 0. This would

then flag the job as being unbroken.

2. EXECUTE DBMS_JOB.BROKEN

(JOB=> <job_number>,

BROKEN => <true|false>)

Example:

EXECUTE DBMS_JOB.BROKEN(

JOB=> 10,

BROKEN => FALSE);

The execution of this statement wouldunbreak job 10.

If the executionof either one of these procedures returns an error saying that

the specified jobcan not be found have the client log in as the owner of the

job and thenattempt the statement again. The ownerof the job is the same as

the person whosubmitted the job.

Finally, youshould always check the alert.log for any accompanying error

messages alongwith looking in the background_dump_dest directory for any

generated SNPtrace files.