job failures =16后 job 会broken

最新推荐文章于 2023-06-17 11:21:32 发布

ygdmario

最新推荐文章于 2023-06-17 11:21:32 发布

阅读量1.7k

点赞数

分类专栏： oracle 文章标签： oracle database jobs action server each

oracle 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

Broken Jobs and the Job Queue [ID 103349.1]

		修改时间 11-JUL-2012 类型 PROBLEM 状态 PUBLISHED


***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 <num>
  ------- ------------------------------------------------------------------------
  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    => <job_number>,
     BROKEN => <true|false>)

     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.