Hi all, I am attempting to calculate the availability of an Oracle database using the alert log for a given month, last month to be exact. How can I query the downtime percentage from the following results? I have used the DB Connect to give me the number of days the database has been running since it last shutdown down, but that does not help with calculating for the entire month.
Startup (last startup message)
10/18/15
11:50:55.666 AM
msg time='2015-10-18T11:50:55.666-04:00' org_id='oracle' comp_id='rdbms'
msg_id='ksu_setup_oracle_base:28994:2787919602' type='NOTIFICATION' group='startup'
level='16' host_id='xxx' host_addr='xxx'
pid='3108'>
txt>ORACLE_BASE from environment = d:\app\orainstall
/txt>
/msg>
Shutdown
10/18/15
8:12:06.113 AM
msg time='2015-10-18T08:12:06.113-04:00' org_id='oracle' comp_id='rdbms'
msg_id='opistp_real:1938:503144415' type='NOTIFICATION' group='shutdown'
level='16' host_id='xxx' host_addr='xxx'
pid='2520'>
txt>Instance shutdown complete
Thanks
Like this (might need adjust for field host
😞
index=oracle host=* sourcetype="oracle:alert:xml" group="'startup'" OR group="'shutdown'" | reverse | streamstats current=t count(eval(group="'startup'")) AS sessionID by host | eventstats earliest(_time) AS startTime latest(_time) AS endTime count by sessionID host | where group="'startup'" | eval uptimeSeconds= if((count>1), endTime, now()) - startTime | stats sum(uptimeSeconds) AS totalUptimeSeconds earliest(startTime) AS firstTime by host | eval totalSeconds = now() - firstTime | eval pctUp = 100 * totalUptimeSeconds / totalSeconds | table host pctUp
is this for checking the availibility of oracle ? like if the OEM shuts down or if OEM restarts ?
Please help me in this i am making a dashboard in which whenever OEM shuts down we get the red glass table .
thanks a lot in advance 🙂
Like this (might need adjust for field host
😞
index=oracle host=* sourcetype="oracle:alert:xml" group="'startup'" OR group="'shutdown'" | reverse | streamstats current=t count(eval(group="'startup'")) AS sessionID by host | eventstats earliest(_time) AS startTime latest(_time) AS endTime count by sessionID host | where group="'startup'" | eval uptimeSeconds= if((count>1), endTime, now()) - startTime | stats sum(uptimeSeconds) AS totalUptimeSeconds earliest(startTime) AS firstTime by host | eval totalSeconds = now() - firstTime | eval pctUp = 100 * totalUptimeSeconds / totalSeconds | table host pctUp
It works! Thanks so much!
I forgot to add the search that produces the results above.
index=oracle host=* sourcetype="oracle:alert:xml" group="'startup'" OR group="'shutdown'" | dedup group