Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

 

Fixing Oracle broken jobs

Oracle Tips by Burleson Consulting

Scheduled jobs appears in two places in Oracle 10g, in the dba_scheduler_jobs (for automatic jobs and jobs scheduled via dbms_scheduler) as well as in the dba_jobs views for jobs that were scheduled with the dbms_job package.

The O'Reilly book on PL/SQL Built-in Packages: notes an automatic procedure for "fixing" special types of broken jobs:

Fixing Broken Jobs Automatically

Charles Dye recommended the next example, probably based on his experiences with replication. When jobs have relatively complex execution requirements in terms of the database objects on which they depend, they can easily become broken by incurring multiple execution failures. Perhaps the DBA has modified some database links or recreated tables or views, and the job's definition has been temporarily compromised.

Well, it's a pain to manually reset the broken flag for these "not really broken" jobs, so why not have a job that regularly tries to unbreak jobs? Sounds good to me; here is a procedure called job_fixer to do just that:

/* Filename on companion disk: job5.sql */*
CREATE OR REPLACE PROCEDURE job_fixer
AS
   /*
   || calls DBMS_JOB.BROKEN to try and set
   || any broken jobs to unbroken
   */
   
   /* cursor selects user's broken jobs */
   CURSOR broken_jobs_cur
   IS
   SELECT job
     FROM user_jobs
    WHERE broken = 'Y';
    
BEGIN
   FOR job_rec IN broken_jobs_cur
   LOOP
      DBMS_JOB.BROKEN(job_rec.job,FALSE);
   END LOOP;
END job_fixer;

The Oracle documentation suggests that fixing "disabled" jobs in 10g dbms_scheduler is different from the "broken" in dbms_job (emphasis added) and adds many "state" column values for jobs in dba_scheduler_jobs:

select state from dba_scheduler_jobs;

Job State Description
disabled The job is disabled.
scheduled The job is scheduled to be executed.
running The job is currently running.
completed The job has completed, and is not scheduled to run again.
broken The job is broken.
failed The job was scheduled to run once and failed.
retry scheduled The job has failed at least once and a retry has been scheduled to be executed.
succeeded The job was scheduled to run once and completed successfully.

 

Enabling Jobs

You enable jobs by using the ENABLE procedure. The effect of using this procedure is that the job will now be picked up by the job coordinator for processing. Jobs are created disabled by default, so you need to enable them before they can run. When a job is enabled, a validity check is performed. If the check fails, the job is not enabled.

You can enable several jobs in one call by providing a comma-delimited list of job names or job class names to the ENABLE procedure call. For example, the following statement combines jobs with job classes:

BEGIN
DBMS_SCHEDULER.ENABLE ('job1, job2, job3, 
   sys.jobclass1, sys.jobclass2, sys.jobclass3');
END;
/

Also, note this on dbms_scheduler

Finally, Dr. Hall notes about the "state" column of dba_scheduler_jobs:

A regular failure doesn't alter the state column. We can prove this by creating a procedure that fails and scheduling it:

CREATE OR REPLACE PROCEDURE proc_fail AS
BEGIN
  RAISE_APPLICATION_ERROR(-20000, 'I forced this error!');
END;
/

BEGIN
  DBMS_SCHEDULER.create_job(
    job_name        => 'test1',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN proc_fail; END;',
    repeat_interval => 'freq=minutely;',
    enabled         => TRUE,
    start_date      => SYSTIMESTAMP);
END;
/


If we let it run a few times we can see that the state column stays as
SCHEDULED, even after failures. You can check this using the following
query:

SELECT job_name, state FROM dba_scheduler_jobs WHERE job_name = 'TEST1';

As you say, you can check the details of the failures by querying the
DBA_SCHEDULER_JOB_RUN_DETAILS view.

The possible values for the STATE column vary a little, depending on the database version, but here is the state list for 10gR2.

Here is a query shows which dbms_scheduler jobs failed and why they failed:

-- What scheduled tasks failed during execution, and why?
COL log_id              FORMAT 9999   HEADING 'Log#'
COL log_date            FORMAT A32    HEADING 'Log Date'
COL owner               FORMAT A06    HEADING 'Owner'
COL job_name            FORMAT A20    HEADING 'Job'
COL status              FORMAT A10    HEADING 'Status'
COL actual_start_date   FORMAT A32    HEADING 'Actual|Start|Date'
COL error#              FORMAT 999999 HEADING 'Error|Nbr'

TTITLE 'Scheduled Tasks That Failed:'

select
   log_date,
   job_name,
   status,
   req_start_date,
   actual_start_date,
   run_duration
from
   dba_scheduler_job_run_details
where
--   job_name = 'MYJOB'

   status <> 'SUCCEEDED'
order by
   actual_start_date;
 

Need  more details on Oracle Job Scheduling?  Get the Oracle job scheduling book.


 

 

��  
 
 

 
 
 
 
oracle dba poster
 

 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright 1996 -  2014

All rights reserved by Burleson

Oracle is the registered trademark of Oracle Corporation.