EM 13C : 'Failed Job Count' Metric for target database shows incorrect value (Doc ID 3039928.1)

Last updated on SEPTEMBER 26, 2024

Applies to:

Symptoms

In EM 13c, 'Failed Job Count' metric for pluggable database shows high count of job failures. Validating internally at DB level, shows no recent failure of any jobs.

Target database home page >> Oracle Database  >> Monitoring >> All Metrics >> Search with 'Failed Job' >> Click on 'Failed job count' metric

Following is the query used by EM agent to get metric value

with last_run as

(
SELECT all_runs.OWNER,
all_runs.JOB_NAME,
all_runs.STATUS
FROM DBA_SCHEDULER_JOB_RUN_DETAILS all_runs,
(SELECT sub.OWNER,
sub.JOB_NAME,
MAX(sub.ACTUAL_START_DATE) AS START_DATE
FROM DBA_SCHEDULER_JOB_RUN_DETAILS sub
WHERE sub.job_name in (SELECT job_name FROM
DBA_SCHEDULER_JOB_RUN_DETAILS where status = 'FAILED')
GROUP BY sub.OWNER,JOB_NAME) latest_runs
WHERE all_runs.status = 'FAILED'
AND all_runs.OWNER= latest_runs.OWNER
AND all_runs.JOB_NAME= latest_runs.JOB_NAME
AND all_runs.ACTUAL_START_DATE=latest_runs.START_DATE
)
SELECT NVL(SUM(broken),0),
NVL(SUM(failed),0)
FROM (SELECT DECODE(broken, 'N', 0, 1) broken,
DECODE(NVL(failures,0), 0, 0, 1) failed
FROM dba_jobs
UNION ALL
SELECT DECODE(STATE,'BROKEN',1,0) broken,
DECODE(STATUS
,'FAILED',DECODE(STATE,'BROKEN',0,'DISABLED',0,1),0) failed
FROM (SELECT all_jobs.OWNER,
all_jobs.JOB_NAME,
last_run.STATUS,
all_jobs.STATE
FROM last_run,
DBA_SCHEDULER_JOBS all_jobs
WHERE last_run.OWNER=all_jobs.OWNER
AND last_run.JOB_NAME=all_jobs.JOB_NAME
AND all_jobs.NEXT_RUN_DATE IS NOT NULL
UNION -- only one-shot jobs get a FAILED status
SELECT last_run.OWNER,
last_run.JOB_NAME,
last_run.STATUS,
NULL STATE -- one-shot job has no STATE
FROM last_run
WHERE STATUS = 'FAILED'));

Changes

 NA

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.