| |
 |
|
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:
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;
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
for only $9.95.
|
