OMS Database Important queries

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

Below table to pull all the alerts in individual OEM host DB.

select * from MGMT$ALERT_CURRENT

--where target_name like '%OBI%'

--order by collection_timestamp desc

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

Please find the below query which will help you in identifying the OEM incident state

select a.INCIDENT_NUM,b.CATEGORY_NAME,a.SUMMARY_MSG,a.RESOLUTION_STATE,a.OWNER,a.IS_ACKNOWLEDGED,a.CLOSED_DATE,a.CREATION_DATE from MGMT$INCIDENTS a,MGMT$INCIDENT_CATEGORY b

where

a.RESOLUTION_STATE not in ('Closed','Resolved')

 --and b.CATEGORY_NAME not in ('Diagnostics','Fault')

and a.incident_id=b.incident_id  order by CREATION_DATE desc;

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

Most frequent alert current metric value and status

select to_char(min(begin_time),'hh24:mi:ss')||' /'||round(avg(intsize_csec/100),0)||'s' "Time+Delta",

      metric_name||' - '||metric_unit "Metric",

      sum(value_inst1) inst1, sum(value_inst2) inst2, sum(value_inst3) inst3, sum(value_inst4) inst4,

      sum(value_inst5) inst5, sum(value_inst6) inst6

from

 ( select begin_time,intsize_csec,metric_name,metric_unit,metric_id,group_id,

      case inst_id when 1 then round(value,1) end value_inst1,

      case inst_id when 2 then round(value,1) end value_inst2,

      case inst_id when 3 then round(value,1) end value_inst3,

      case inst_id when 4 then round(value,1) end value_inst4,

      case inst_id when 5 then round(value,1) end value_inst5,

      case inst_id when 6 then round(value,1) end value_inst6

 from gv$sysmetric

 where metric_name in ('Host CPU Utilization (%)','Current OS Load', 'Physical Write Total IO Requests Per Sec',

       'Physical Write Total Bytes Per Sec', 'Global Cache Average Current Get Time', 'Global Cache Average CR Get Time',

       'Physical Read Total Bytes Per Sec', 'Physical Read Total IO Requests Per Sec',

       'CPU Usage Per Sec','Network Traffic Volume Per Sec','Logons Per Sec','Redo Generated Per Sec',

       'User Transaction Per Sec','Database CPU Time Ratio','Database Wait Time Ratio','Database Time Per Sec')

 )

group by metric_id,group_id,metric_name,metric_unit

order by metric_name;