INTRODUCTON
Some customers execute database jobs using DBMS_SCHEDULER. This alert will notify you of failures in those jobs. Because Ignite custom alerts work with numeric returns, this SQL statement changes the status value to one of 0 (normal), 10 (stopped) or 20 (failed) in increasing severity. The thresholds defined at the bottom will send a Warning message when the status is 10 and send a critical message when it is 20.
ALERT DEFINITION
To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert with values similar to these:
Alert Name: Database Job Failure
Execution Interval: 10 Minutes
Notification Text: The following list of database jobs have failed within the last 10 minutes.
SQL Statement:
select owner||'.'||job_name||'-'||status,
case status
when 'STOPPED' then 10
when 'FAILED' then 20
else 0
end as status_num
from dba_scheduler_job_log
Execute Against: Monitored Instance
Units: Status
High Threshold: MIN 20, MAX empty
Medium Threshold: MIN 10, MAX 20
