Oracle EM Grid Control Mining-part 2-Data aggregation in repository

When we think of mining data in EM Grid Control (GC), we first pay attention to 3 main tables in the repository owned by SYSMAN, MGMT_METRIC_RAW, MGMT_METRIC_1HOUR, MGMT_METRIC_1DAY. These tables hold RAW metric data, one-hour aggregated data, one-day aggregated data respectively.

With default installation, Grid Control agents upload raw data in EM repository which are stored in MGMT_METRIC_RAW table for 7 days. Then, raw data are aggregated by Grid Control based on a predefined policy into 1-hour records, stored in MGMT_METRIC_1HOUR. At this poin, the raw data older than 7 days are eligible for purging.  After 31 days or a month, 1-hour records in MGMT_METRIC_1HOUR are subject for further aggregation that rolls upthem into 1-day records. These 1-day records are stored in MGMT_METRIC_1Day for 365 days or 1 year before purged by Grid Control.

As one way to avoid losing details during the aggregation process, we can change the retention policy. New retention periods for these 3 tables can be defined in MGMT_PARAMETERS tables.  These no-default parameters do not exist in mgmt_parameters tables during the standard installation. We need to insert the right value into the table.

Table Name                                  Retention Parameter                  Retention Days
MGMT_METRICS_RAW         mgmt_raw_keep_window        7
MGMT_METRICS_1HOUR     mgmt_hour_keep_window      31
MGMT_METRICS_1DAY        mgmt_day_keep_window        365

INSERT INTO SYSMAN.MGMT_PARAMETERS (PARAMETER_NAME, PARAMETER_VALUE,PARAMETER_COMMENT)
VALUES (‘mgmt_raw_keep_window’,’31′,’Keep raw data for 31 days’);

Not surprising, these 3 main tables are Index-Oraganized tables, partitioned given that e.g. the partitioning option is enabled during the installation.
MGMT_METRICS_RAW is range-partitioned per hour, whereas MGMT_METRICS_1HOUR and MGMT_METRICS_1DAY are range-partitioned per day.

CREATE TABLE “SYSMAN”.”MGMT_METRICS_1DAY“
(    ”TARGET_GUID” RAW(16),
“METRIC_GUID” RAW(16),
“KEY_VALUE” VARCHAR2(256 BYTE) DEFAULT ‘ ‘,
“ROLLUP_TIMESTAMP” DATE,
“SAMPLE_COUNT” NUMBER DEFAULT 0,
“VALUE_AVERAGE” NUMBER DEFAULT 0,
“VALUE_MINIMUM” NUMBER DEFAULT 0,
“VALUE_MAXIMUM” NUMBER DEFAULT 0,
“VALUE_SDEV” NUMBER DEFAULT 0,
CONSTRAINT “MGMT_METRICS_1DAY_PK_INT” PRIMARY KEY (“TARGET_GUID”, “METRIC_GUID”, “KEY_VALUE”, “ROLLUP_TIMESTAMP”) ENABLE
) ORGANIZATION INDEX COMPRESS 3
PARTITION BY RANGE (“ROLLUP_TIMESTAMP”)
(PARTITION “2008-11-11 00:00″  VALUES LESS THAN (TO_DATE(‘ 2008-11-11 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
PARTITION “2008-11-12 00:00″  VALUES LESS THAN (TO_DATE(‘ 2008-11-12 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)) …
)

During maintenance, GC jobs truncate data in the partitions but leave the partition untouched, because dropping partitions would invalidate stored procedures. To drop old partitions, we can manually run “exec emd_maintenance.partition_maintenance;” after bring down OEM Grid Control processes.

Querying the data in these 3 tables, we need to know and refer TARGET_GUID and METRIC_GUID, instead of target names or metric names. For easier access using names, we can use 3 views MGMT$METRIC_DETAILS, MGMT$METRIC_HOURLY, and MGMT$METRIC_DAILY. These views owned by SYSMAN are based on these 3 main tables but having extra columns like ‘target_name’, ‘metric_name’ for easier referrence.

Oracle EM Grid Control Mining- part 1- GC knows what you missed

Maybe happened to you too, I am sometimes curious about history of my databases , e.g , overall storage space allocated to all databases in the past months. As Grid Control (GC) is deployed, the answer lies in GC repository.

In a simple example here, we try to find space allocated to a database’s tablespaces in the past 365 days. The information is stored by GC under SYSMAN schema.  Let’s query along. (See below)

SYSMAN’s view mgmt$metric_daily contains historical metric data that GC monitors, including tablespace allocation, file system allocation, CPU consumption, memory usage, and much more. Focusing on tablespace allocation, first thing to do is identifying the database in interest. We need its global id “guid”, which can be found in mgmt$target. Then we use this value as target_guid, query for the maximum (daily max) of the space allocation (metric_column=’spaceAllocated’) to the tablespaces (metric_name=’tbspAllocation’) in mgmt$metric_daily.

SELECT m.metric_column AS Space_allocated,  TO_DATE(TO_CHAR(m.rollup_timestamp,’YYYY-MM-DD’), ‘YYYY-MM-DD’) AS Day, sum(m.maximum/1024) AS size_kb
FROM
mgmt$metric_daily m, mgmt$target t
WHERE
t.target_guid=’<target_guid>” AND m.target_guid=t.target_guid AND
m.metric_name=’tbspAllocation’ AND m.metric_column=’spaceAllocated’  AND
m.rollup_timestamp >= SYSDATE -365 AND m.rollup_timestamp <= SYSDATE
GROUP BY m.metric_column, m.rollup_timestamp

Here you get a list of total tablespace allocation everyday in the past 365 days.

Furthermore, we can visualize the trends using various chart provided by GC.  We can create  a GC report using supplying this query that generates a line chart visualizing the historical trend. The report would be scheduled to be executed monthly and emailed to me automatically.

If we are interested in other information in the database, like memory/cpu utilization/response time, all we need to do is to rewrite the query above by changing metric_name/metric_column. These metrics names can be found in mgmt_metrics table. There are easily having more than 1 thousand metrics.

The next part of GC mining, I will describe how GC aggregates the data and how to change the schedule. Along the way, we will view some SYSMAN tables/views.

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.