Dba Jobs check

Job details

Name:	Dba Jobs check
Platform:	Oracle
Category:	Availability
Description:	Detecting failed scheduled jobs in old style dba_jobs.
Long description:	The BROKEN column in the Oracle dictionary view DBA_JOBS is used to detect jobs that have failed.
Version:	2.3
Default schedule:	5,15,25,35,45,55 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & _priv_read_dba_jobs = ‘1′ ]

Parameters

Name	Default value	Description
ignore job id		Job ID from job column in dba_jobs excluded from being checked (comma separated).
return status on failed job	1	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when a job have status FAILED (and are not in “ignore job names” list).
last run	01.03.2010 00:00	The last date the dbWatch Job scheduling check was run. Only jobs run after this date will be checked (column actual_start_date in the dba_scheduler_job_run_details view). Value format DD.MM.YYYY HH24:MI

Job Summary

Purpose: The main purpose of the “Dba Jobs check” is to monitor and detect scheduled jobs in Oracle databases that have failed, using the Oracle dictionary view DBA_JOBS.
Why: This monitoring is crucial because failed jobs can significantly impact the operational aspects of a database. Timely detection allows for quick intervention to resolve issues, ensuring reliability and availability.
Manual checking: You can manually check the status of DBA jobs in the database with the following SQL command:

select job, log_user, substr(what, 0, 50) as what, broken from dba_jobs where broken = 'Y';

Job Description

The “Dba Jobs check” examines the BROKEN column in the DBA_JOBS dictionary view to identify any jobs flagged as broken. This check filters out any job IDs specified in the “ignore job id” parameter from triggering false positives in the monitoring results.
Jobs that have failed since a specified “last run” date (which takes into account the most recent check performed by dbWatch Job Scheduler) will be flagged according to a designated return status value configured for failed jobs.

Job Configuration and Dependencies

Version: 2.3
Schedule: The default schedule is set to run at five minutes intervals: at 5, 15, 25, 35, 45, and 55 minutes past each hour.
Dependencies: This check has a self-dependency which means it requires previous results from the same check to function optimally.

Job Outputs and Reports

The output includes:
- A report listed under the title “Job statistics” containing details of all currently running jobs.
- Logged information identifying user, job description (truncated at 50 characters), job ID, and broken status.
- An acknowledgment setting that allows alerts to be acknowledged manually.
- Bordered presentation tables outline “Log user”, “What job”, “Job ID”, and “Broken” as major columns.

Implementation Details

Implementation Code: A PL/SQL stored procedure “dba_jobs_check” is used to perform the actual check. This procedure iterates over dba_jobs entries flagged as broken (‘Y’) not included in the “ignore job id” list.
Exception Handling: Any SQL exceptions encountered during the execution are logged into DBW_ERRORS, containing details like the error number, message, and the date-time occurrence.

Tables

Log user	What job	Job ID	Broken
Log user from DB	Partial job description	Numerical ID of the Job	Broken status (Y or N)

Additional Information

Acknowledgements: The job configuration supports acknowledgment settings to confirm user awareness and intervention where needed.
Compatibility: This job is designed specifically for Oracle database instances with the appropriate access privileges.
Performance Impacts: Regularly checking the broken jobs can influence the database’s load, especially with frequent execution schedules. Tables with many records might require thoughtful indexing or reconsideration of the check intervals.

This extensive setup provided by dbWatch Control Center helps maintain the reliability and performance of Oracle database environments by timely detecting and alerting on any anomalies or failures within the scheduled database jobs.