Mining EM Metadata - Job Health Check

This is the first of (hopefully) a number of posts about how to take advantage of information stored in Enterprise Manager's (EM) metadata.  EM has to capture a large quantity and variety of details related to its functionality, which is stored in a highly normalized format.  A lot of this information includes key details about environments that you, as a DBA, support.  Knowing what's stored and how to access it correctly can actually make your job a bit easier.

As a DBA you more than likely use a variety of monitoring tools and require assurance that those tools are working properly.  Ideally EM works without issue but obviously bugs will always be there.  For the situation discussed in this post, the bug deals with an older EM release (10.2.0.3) and problem where targets for an EM Job will disappear, for whatever reason, while other targets stay defined.

For example, let's say you have EM Job "MONITOR - ORACLE SERVICES" and it's defined to run against all cluster databases.  At some point 1 or more of those intended targets drop from the full list of the scheduled Job, so at a later point in time you may find that the EM Job is now running only against 5 cluster databases when you originally submitted it to run on 8.  That's a pretty scary situation for a DBA to find himself it, especially since this particular bug doesn't appear to give any warning or messages.

The good news is EM metadata includes everything you need to catch this situation when it happens.  The key objects are views MGMT$JOBS, MGMT$JOB_TARGETS, and MGMT$JOB_EXECUTION_HISTORY, with a little help from MGMT$TARGET_FLAT_MEMBERS.

MGMT$JOBS

View MGMT$JOBS holds basic information about every EM Job, for both what resides (if at all) in the library and for what has been submitted.  Differentiation is done using IS_LIBRARY, a flag where a value of "1" means the EM Job for that row of data is saved in the library whereas a value of "0" refers to a submitted EM Job.

COL job_name FORMAT A30

COL target_type FORMAT A15

SELECT job_name, job_type, target_type, is_library

  FROM mgmt$jobs

 WHERE job_name LIKE 'EXAMPLE_JOB%';

JOB_NAME                       JOB_TYPE                         TARGET_TYPE             IS_LIBRARY

------------------------------ -------------------------------- --------------- ------------------

EXAMPLE JOB                    OSCommand                        host                             1

Once you submit the EM Job an additional row is returned from MGMT$JOBS where IS_LIBRARY = 1:

JOB_NAME                       JOB_TYPE                         TARGET_TYPE             IS_LIBRARY

------------------------------ -------------------------------- --------------- ------------------

EXAMPLE JOB                    OSCommand                        host                             1

EXAMPLE JOB.1                  OSCommand                        host                             0

Also note that until you define a schedule for the EM Job (either "One Time" or "Repeating"), the column START_TIME will be NULL.

MGMT$JOB_TARGETS

To see which targets have been assigned to a given EM Job, use the view MGMT$JOB_TARGETS.  Be careful, though.  Like I mentioned before, EM's metadata is highly normalized, along with how you can define your own groupings.  If you create an EM Job to run against 5 specific hosts you'll give 5 rows returned for this EM Job from MGMT$JOB_TARGETS.

SELECT j.job_name, j.job_type, jt.target_name, j.target_type

  FROM mgmt$jobs j, mgmt$job_targets jt

 WHERE j.job_name LIKE 'EXAMPLE_JOB%'

   AND j.is_library = 1

   AND (j.job_id = jt.job_id);

JOB_NAME                       JOB_TYPE                         TARGET_NAME TARGET_TYPE

------------------------------ -------------------------------- ----------- ---------------

EXAMPLE JOB                    OSCommand                        linux1      host

EXAMPLE JOB                    OSCommand                        linux2      host

   ...

EXAMPLE JOB                    OSCommand                        linux5      host

But if you defined the EM Job as type "host" and assign it to an EM Group you'll get 1 row with a target type of "composite".

SELECT j.job_name, j.job_type, jt.target_name, jt.target_type

  FROM mgmt$jobs j, mgmt$job_targets jt

 WHERE j.job_name LIKE 'EXAMPLE_JOB%'

   AND j.is_library = 1

   AND (j.job_id = jt.job_id);

JOB_NAME                       JOB_TYPE                         TARGET_NAME TARGET_TYPE

------------------------------ -------------------------------- ----------- ---------------

EXAMPLE JOB                    OSCommand                        PROD_GROUP  composite

In the example above the "Target Type" defined for the EM Job is still "host" but when I selected individual Targets for the EM Job I picked a Group, as I wanted to execute the EM Job against EVERY "host" within the selected Group.  To break out (or "flatten") type "composite" you'll need to join to view MGMT$TARGET_FLAT_MEMBERS.  More on that in just a bit.

MGMT$JOB_EXECUTION_HISTORY

Now that we've found out how to list all targets defined for an EM Job, we need to find out how to compare that to what's actually scheduled.  In comes the view MGMT$JOB_EXECUTION_HISTORY.  The query I used for this is rather complex so let me first break it into pieces.  First, I union EM Jobs of non-composite target types with those that are of composite target types.  The composite target types are broken out into individual targets via view MGMT$TARGET_FLAT_MEMBERS.  Second, I take this list of defined EM Jobs and join that to MGMT$JOB_EXECUTION_HISTORY, which represents those EM Jobs that are either "Scheduled" or current "Running".  (Note: when an EM Job is "Running" it doesn't have an entry for "Scheduled".  That only happens after the execution completes.)

WITH c_jobs_defined AS (SELECT j.job_name, j.job_id, j.job_owner, j.job_type, jt.target_name

                             , jt.target_type

                          FROM mgmt$jobs j, mgmt$job_targets jt

                         WHERE (j.job_id = jt.job_id)

                           AND j.is_library = 0

                           AND j.start_time IS NOT NULL

                           AND jt.target_type NOT IN ('composite')

                               UNION ALL

                        -- Join in Jobs with a composite target type separately as these need to be

                        -- flattened out to get a full list of all targets for that composite type.

                        SELECT j.job_name, j.job_id, j.job_owner, j.job_type, tfm.member_target_name

                             , tfm.member_target_type

                          FROM mgmt$jobs j, mgmt$job_targets jt, mgmt$target_flat_members tfm

                         WHERE (j.job_id = jt.job_id)

                           AND (    jt.target_name = tfm.aggregate_target_name

                                AND jt.target_type = tfm.aggregate_target_type

                                AND j.target_type = tfm.member_target_type)

                           AND j.is_library = 0

                           AND j.start_time IS NOT NULL

                           AND jt.target_type IN ('composite'))

SELECT c.job_name, c.job_owner, c.job_type, c.target_name missing_on_target, c.target_type

  FROM c_jobs_defined c LEFT OUTER JOIN mgmt$job_execution_history jeh

       ON (c.job_id = jeh.job_id AND c.target_name = jeh.target_name)

                        INNER JOIN (SELECT member_target_name, member_target_type

                                      FROM mgmt$target_flat_members

                                     WHERE aggregate_target_name = 'PROD_GROUP') m

       ON (c.target_name = m.member_target_name AND c.target_type = m.member_target_type)

 WHERE (jeh.status IN ('Scheduled', 'Running') OR jeh.status IS NULL)

   AND jeh.job_name IS NULL

 ORDER BY c.job_name, c.target_name;

The INNER JOIN to MGMT$TARGET_FLAT_MEMBERS with the UNION-ed results is to limit the search to those targets within a particular EM Group, in this case our production Group named "PROD_GROUP".

Although this was rather lengthy to explain, hopefully it's clear now how to differentiate EM Jobs as defined in the Job Library verses those listed in Job Activity within the EM Console.