Oracle7 Server Distributed Systems Volume II: Replicated Data

Troubleshooting Job Execution Problems

Several factors can prevent the successful execution of queued jobs; for example:

not having any SNP background processes to run the job

a network or instance failure

an exception when executing the job

Note: If a job fails, Oracle rolls back the job's current transaction (if any such transaction exists).

Recording Errors

When a job fails, information about the failure is recorded in a trace file and the alert log. Oracle writes message number ORA-12012 and includes the job number of the failed job.

Job Failure and Execution Times

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. When the retry interval exceeds the execution interval, Oracle continues to retry the job at the normal execution interval. However, if the job fails 16 times, Oracle automatically marks the job as broken and no longer tries to execute it.

Thus, if you can correct the problem that is preventing a job from running before the job has failed 16 times, Oracle will eventually run that job again.

Running a Job That Oracle Has Marked as Broken

If a problem has caused a job to fail 16 times, Oracle marks the job as broken. Once you have fixed this problem, you can run the job by either

forcing the job to run by calling DBMS_JOB.RUN, or

marking the job as not broken by calling DBMS_JOB.BROKEN and waiting for Oracle to execute the job.

If you force the job to run by calling the procedure DBMS_JOB.RUN, Oracle runs the job immediately. If the job succeeds, then Oracle labels the job as not broken and resets its count of the number of failed executions for the job.

Once you reset a job's broken flag (by calling either RUN or BROKEN), job execution resumes according to the scheduled execution intervals set for the job.

Killing a Job

You can kill a running job by marking the job as broken, identifying the session running the job, and disconnecting that session. You should mark the job as broken so that Oracle does not attempt to run the job again.

Once you have identified the session running the job, you can disconnect the session using SQL command ALTER SYSTEM.

For examples of viewing information about jobs and sessions, see "Viewing Job Queue Information" .