Oracle Rant

Like most Oracle shops, we use Grid Control to monitor and give insight into our DB environments. The Grid Control repository is loaded with lots of useful information which sometimes is not always evident or accessible via the Grid Control GUI. Some of us have even attempted to reverse engineer the GC schema to try and answer some questions that we have. This is a collection of scripts/queries I use against the GC repository to get some answers I am looking for. If you have some scripts/pointers, then please share them .

Run the following query for current metrics. This shows what thresholds Alerts will be triggered for (Alerts not email/pager notification – that is part of notification rules).

SELECT a.target_name, a.target_type, a.metric_name, a.metric_column,
       DECODE(TRIM(a.warning_threshold), '',
       DECODE(TRIM(a.critical_threshold), '', 'DISABLED', 'ENABLED'), 'ENABLED') STATUS ,
       a.warning_operator, a.warning_threshold, a.critical_operator,
       a.critical_threshold,
       REPLACE(REGEXP_SUBSTR(b.schedule_ex, '"[^"]*"'), '"', '') FREQUENCY,
       NVL(REPLACE(REPLACE(REGEXP_SUBSTR(b.schedule_ex, 'T="[^"]*"'), 'T=', ''),'"', ''), 'Minutes') FREQUENCY_UNIT,
       c.frequency_code, a.occurence_count, a.warning_count,
       a.critical_count, c.is_enabled
FROM MGMT$METRIC_COLLECTION a, SYSMAN.MGMT_METRIC_COLLECTIONS b, sysman.MGMT$TARGET_METRIC_COLLECTIONS c
WHERE a.target_guid = b.target_guid
  and a.target_guid = c.target_guid
  and b.metric_guid = c.metric_guid
  and a.metric_name = c.metric_name
ORDER BY target_name, target_type, a.metric_name;

Next, Notification Methods need to be queried to determine which alerts Oracle sends our emails for.

SELECT RULE_NAME, OWNER, TARGET_TYPE, TARGET_NAME, TARGET_GUID,
                 METRIC_NAME, METRIC_COLUMN, KEY_VALUE, KEY_PART_1, KEY_PART_2,
                 KEY_PART_3, KEY_PART_4, KEY_PART_5, WANT_CLEARS, WANT_WARNINGS,
                 WANT_CRITICAL_ALERTS, WANT_TARGET_UP, WANT_TARGET_DOWN,
                 WANT_TARGET_UNREACHABLE_START, WANT_TARGET_UNREACHABLE_END,
                 WANT_TARGET_METRIC_ERR_START, WANT_TARGET_METRIC_ERR_END,
                 WANT_TARGET_BLACKOUT_START, WANT_TARGET_BLACKOUT_END,
                 WANT_POLICY_CLEARS, WANT_POLICY_VIOLATIONS,
                 WANT_WARNING_JOB_SUCCEEDED, WANT_WARNING_JOB_PROBLEMS,
                 WANT_CRITICAL_JOB_SUCCEEDED, WANT_CRITICAL_JOB_PROBLEMS,
                 WANT_POLICY_JOB_SUCCEEDED, WANT_POLICY_JOB_PROBLEMS, IGNORE_RCA
FROM SYSMAN.MGMT_NOTIFY_RULE_CONFIGS;

This final Query will combine the 2 to show alerts and if they will be notified on.

SELECT DISTINCT a.target_name,
           a.target_type,
           a.metric_name,
           a.metric_column,
           DECODE (
              TRIM (a.warning_threshold),
              '',
              DECODE (TRIM (a.critical_threshold), '', 'DISABLED', 'ENABLED'),
              'ENABLED'
           )
              STATUS,
           NVL (d.rule_name, 'NO RULE') rule_name,
           a.warning_operator,
           a.warning_threshold,
           a.critical_operator,
           a.critical_threshold,
           REPLACE (REGEXP_SUBSTR (b.schedule_ex, '"[^"]*"'), '"', '')
              FREQUENCY,
           NVL (
              REPLACE (
                 REPLACE (REGEXP_SUBSTR (b.schedule_ex, 'T="[^"]*"'), 'T=', ''),
                 '"',
                 ''
              ),
              'Minutes'
           )
              FREQUENCY_UNIT,
           c.frequency_code,
           a.occurence_count,
           a.warning_count,
           a.critical_count,
           c.is_enabled
    FROM   MGMT$METRIC_COLLECTION a,
           SYSMAN.MGMT_METRIC_COLLECTIONS b,
           sysman.MGMT$TARGET_METRIC_COLLECTIONS c,
           (SELECT DISTINCT DECODE (TRIM (y.target_name),
                             '%', x.target_name,
                             y.target_name) target_name,
                     y.rule_name,
                     y.metric_name,
                     y.metric_column
              FROM   SYSMAN.MGMT$METRIC_COLLECTION x,
                     SYSMAN.MGMT_NOTIFY_RULE_CONFIGS y
             WHERE   (x.target_name = y.target_name
                      OR TRIM (y.target_name) = '%')
                     AND x.metric_name = y.metric_name
                     AND x.metric_column = y.metric_column) d
   WHERE       a.target_guid = b.target_guid
           AND a.target_guid = c.target_guid
           AND b.metric_guid = c.metric_guid
           AND a.metric_name = c.metric_name
           AND a.target_name = d.target_name(+)
           AND a.metric_name = d.metric_name(+)
           AND a.metric_column = d.metric_column(+)
ORDER BY   target_name, target_type, a.metric_name;

If you see any errors above, then please let me know so I can fix them!

Lastly, we run a script against the Grid Control repository to pull out availability metrics such as database uptime %, Avg Host CPU Util %, Avg Host memory Util %, and ASM Disk Group total space and free space. Hopefully if will give you a pretty good idea of where the information is so you can modify it for your own use with your own metrics of interest.

Basically SQLPLUS spools it out to an Excel file and then the script emails it out.

First, the shell script

#!/bin/bash

export CYM=$(date '+%Y%m')
export MCY=$(date '+%B %Y')
export SQLD=$HOME/scripts
export ORACLE_SID=emrep
export ORACLE_HOME=/u001/app/oracle/product/db10g

$ORACLE_HOME/bin/sqlplus -s "sysman/sysman_password" @$SQLD/metrics.sql

cp /home/oracle/scripts/spool/oracle_metrics.xls /home/oracle/scripts/spool/oracle_metrics_$CYM.xls

echo "Oracle Metrics Collection - ${MCY}" | /usr/bin/mutt -a /home/oracle/scripts/spool/oracle_metrics_${CYM}.xls -s "Oracle Metrics Collection - ${MCY}" email_address@domain.com
exit 0

Really you could email with sendmail or mailx, but you will need uuencode to encode the attachment and then pipe that into your mail program.

Next is the SQL script to use. You will need to identify you ASM targets (Chances are you only want one of them). You will also have to put a filter. If you read the script you will see what you have to put in. I will do some tweaking to see if I can’t make it more universal without editing needed.

SET MARKUP HTML ON HEAD "<title>Production Metrics Report</title> - <meta http-equiv='Content-Type' content='application/vnd.ms-excel;'> <style type='text/css'> <!-- BODY {background: red} --> </style>"
set pagesize 50000
set trimspool on
set feedback off
set echo off
set term off
spool /home/oracle/scripts/spool/oracle_metrics.xls
select
  distinct a.target_name "Target Name",
  nvl(b.uptime,100) "Database Uptime (%)"
from
  sysman.MGMT$AVAILABILITY_HISTORY a,
  (select target_name,
     round((100-(sum(trunc(86400*(end_timestamp-start_timestamp)))/trunc(86400*(add_months(trunc(sysdate,'MM'),-0)-add_months(trunc(sysdate,'MM'),-1))))),5) uptime
   from
     sysman.MGMT$AVAILABILITY_HISTORY
   where
     target_type in ('oracle_database','rac_database')
     and target_name like '%somefilter%'
     and target_name not like 'st%'
     and target_name not like 'dg%'
     and target_name not like 'meta%'
     and target_name not like 'emrep%'
     and start_timestamp between add_months(trunc(sysdate,'MM'),-1) and add_months(trunc(sysdate,'MM'),0)
     and availability_status = 'Target Down'
   group by target_name) b
where
  a.target_name = b.target_name(+)
  and a.target_type in ('oracle_database','rac_database')
  and a.target_name like '%somefilter%'
  and a.target_name not like 'st%'
  and a.target_name not like 'dg%'
  and a.target_name not like 'meta%'
  and a.target_name not like 'emrep%'
  order by a.target_name;

select t.target_name "Target Name", TO_CHAR(round(avg(value_average),2)) "Avg Host CPU Util (%)"
from mgmt_metrics_1hour h, mgmt_targets t where
h.target_guid in (select target_guid from mgmt_targets t where
target_type = 'host' and
target_name in (select target_name from mgmt_targets where target_type='host' and target_name like '%somefilter'
                                 )) and
h.metric_guid = (select m.metric_guid from mgmt_metrics m where
                          m.target_type = 'host' and
                          m.metric_name = 'Load' and
                          m.metric_column = 'cpuUtil'and
                          t.type_meta_ver = m.type_meta_ver and
                         (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') and
                         (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') and
                         (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') and
                         (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') and
                         (t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ')) and
h.target_guid = t.target_guid and
--rollup_timestamp > sysdate-31
rollup_timestamp between add_months(trunc(sysdate,'MM'),-1) and add_months(trunc(sysdate,'MM'),0)
group by t.target_name;

select t.target_name "Target Name", TO_CHAR(round(avg(value_average),2)) "Avg Host Memory Util (%)"
from mgmt_metrics_1hour h, mgmt_targets t where
h.target_guid in (select target_guid from mgmt_targets t where
target_type = 'host' and
target_name in (select target_name from mgmt_targets where target_type='host' and target_name like '%somefilter_such as domain name'
                                 )) and
h.metric_guid = (select m.metric_guid from mgmt_metrics m where
                          m.target_type = 'host' and
                          m.metric_name = 'Load' and
                          m.metric_column = 'memUsedPct'and
                          t.type_meta_ver = m.type_meta_ver and
                         (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') and
                         (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') and
                         (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') and
                         (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') and
                         (t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ')) and
h.target_guid = t.target_guid and
--rollup_timestamp > sysdate-31
rollup_timestamp between add_months(trunc(sysdate,'MM'),-1) and add_months(trunc(sysdate,'MM'),0)
group by t.target_name;

select decode(t.target_name,'YOURASM_TARGET','FRIENDLY NAME','YOURDRASM_TARGET','FRIENDLY NAME') "Target Name", TO_CHAR(round(max(b.value_average)/1024,0)) "Total Space (GB)",
TO_CHAR(round(max(a.value_average)/1024,0)) "Total Free Space (GB)"
from mgmt_metrics_1hour a, mgmt_metrics_1hour b, mgmt_targets t
where a.target_guid in (select target_guid from mgmt_targets t where
target_type = 'osm_instance' and
target_name in (select target_name from mgmt_targets where target_type='osm_instance' and target_name like '+ASM1%'
and target_name not like 'st%'))
and
a.metric_guid = (select m.metric_guid from mgmt_metrics m where
                          m.target_type = 'osm_instance' and
                          m.metric_name = 'DiskGroup_Usage' and
                          m.metric_column = 'free_mb' and
                          t.type_meta_ver = m.type_meta_ver and
                         (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') and
                         (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') and
                         (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') and
                         (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') and
                         (t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' '))and
b.metric_guid = (select m.metric_guid from mgmt_metrics m where
                          m.target_type = 'osm_instance' and
                          m.metric_name = 'DiskGroup_Usage' and
                          m.metric_column = 'total_mb' and
                          t.type_meta_ver = m.type_meta_ver and
                         (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') and
                         (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') and
                         (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') and
                         (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') and
                         (t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ')) and
a.target_guid = t.target_guid and
b.target_guid=t.target_guid and
--a.rollup_timestamp > sysdate-31
a.rollup_timestamp between add_months(trunc(sysdate,'MM'),-1) and add_months(trunc(sysdate,'MM'),0)
group by t.target_name;

spool off
exit

And that’s all I have for now, but please leave feedback on any errors or if there are some scripts you like to run against the repository.