Get more from Oracle EM Grid Control- part 1 – reporting
Here, I’d like to share some tricks of tailoring your own reports in OEM Grid Control (GC).
1) If you schedule DB backup jobs or patch jobs from GC, it takes time and is easily forgettable to log on GC and click click click for job status every day. I like a quick way such as reading a report of all of them in an email, preferably through PDA – iPhone etc.
It can be achieved by creating a GC report querying the repository database objects. For example, GC can schedule a routine to run the query below and email the job status, execution time, etc to the inbox automatically.
select job_name, target_name, status, start_time, end_time
from sysman.MGMT$JOB_EXECUTION_HISTORY
where start_time > SYS_EXTRACT_UTC(systimestamp) -1
and start_time < SYS_EXTRACT_UTC(systimestamp)
and lower(job_type) not in (‘deletetargetjob’,'discardstatejob’,'pafdaemonjob’,'publishreport’)
and job_name <> ‘LISTENER CHECK’
Be careful if jobs have more than 1 step and they do not return status codes. Some time if one step failed or cancelled, GC still thinks the job was successful(, with Warnings some times).
To mitigate, the report needs to include the details of each job steps which are stored in mgmt_job_history.step_status_code.
select distinct mj.job_name, mj.job_description, to_char(mjh.step_status_code) status, mjh.start_time, mjh.end_time
from sysman.mgmt_job_history mjh join sysman.mgmt_job mj
on mjh.job_id = mj.job_id
where mjh.step_status_code <> 0
If step_status_code is not zero, you might need to double check detailed job log to find out the root cause even the entire job was reported successul.
2) Centralized report for databases in the entire oragnization.
OEM repository stores collected information about all targets under the radar. There are lots of stuffs to mine in MGMT_<> tables. But in many case, DBAs need more. For example, the job scheduled locally on each database instances are not logged in GC repository.
For example, here are a database PRODDB1 and GC repository database GCREPO. The default statistics maintenance job GATHER_STATS_JOB on PRODDB1 will be only logged locally. The information is accessable through DBA_SCHEDULER_JOB_RUN_DETAILS and DBA_SCHEDULER_JOBS. As security policy permits, DBA can create a DB link from GCREPO to PRODDB1 and then GC can report on GATHER_STATS_JOB job logs automatically.
Applied the same setup to all databases, a centralized report can be nicely presented and easily accessable to DBA. If extra coding or configuration is feasible, GC can be setup reporting jobs on hosting server (eg. crontab job by parsing logs presented as External tables etc.) and more.
Please note, as of now, Oracle newest Enterprise Manager Grid Control is version 10.2.0.5. I hope more funcations will be available in the later version.
Thank you for the informative article. I was trying to get this report for non-DBA type operation folks. But instead of sending the notification for each database, I wanted to send a consolidated report in an email. I was able to get this query after digging around int he MGMT views. But what I’m having a hard time is that the report time stamp does not match what the job scheduler/execution time stamp says. Do I need to do some sort of time conversion?
I would appreciate any inside to this.
Thanks.
Barry Ghotra
The time stamps in MGMT view are the job logs. If you observe a difference, it may be due to the Grid Control Server were in a different timezone as GMT. Please try moving SYS_EXTRACT_UTC in the query.