Step-By-Step Create OEM Metric Extension - Send Alerts on Long Running SQLs Using Metric Extension 

Thanks for your feedback.

Thanks Shymon

Hi, the 5 conditions to trigger an alert are as below: status='ACTIVE' and username is not null and type 'BACKGROUND' and username not in ('SYS') and last_call_et/60 > 10. When you are expecting an alert and you know the insert statement is still running, please execute the first SQL I have given in the shared document in a SQL*Plus session and see if that returns anything. I suspect the session May have gone into an inactive state. Please let me know what you find.

Hi @sendilkumar9184, thanks for watching my video and providing your feedback. You can find out all the sessions running over 15 minutes by simply changing the condition "greater than 10" to "greater than 15" in the query. However the OEM alerts are meant to be short/precise and notify about the situation rather than sending all the details. If you modify the outer query to return a "count(*)" that will tell you how many sessions are running over 15 minutes and based on the returned count you can determine the severity such as "greater than 20" as critical to "greater than 15" as warning etc. If you really wants to see the list of all sessions then better to create a BIP Report (deprecated in OEM 13.5) or a PowerBI dashboard etc. Hope this will help.

Hi @DUMIE63, first of all thanks for watching my video and providing your valuable feedback. I am dividing you question to different parts so I can answer them individually. I have a full series named "Mastering OEM" on OEM 13c covering topics from installation to setup incident rules and alerts, patching, upgrade etc. If you want, you may watch the full series using the link below: ru-vid.com/group/PLgWj5dy2RoFZLk49TIxMjW5g8C5K0ntjq Now the question 1: Do you have a video with a clear example on how to setup and send an SMS notification to a recipient from an incident rule within OEM13c, please? Answer: OEM by default does not send an SMS or place a phone call but it is actually easy to achieve. I am explaining alert/monitoring setup in the OEM series in Part 9 and 10. To send SMS or make a phone call, you have to integrate a third party Paging software like PagerDuty, Opsgenie or other similar Pager solution with OEM. This is actually very simple as these software have the option to create an associated email id like dba_team@your_company.pagerduty.com or dba_team@your_company.opsgenie.com etc and you just need to redirect the alert email generated by OEM to the Pager software. Once the software receives the alert email, it will send the SMS, or place a phone call with machine voice or an email etc as per the configuration you did. To your second question- incident rule when a metric extension (like a blocking lock on a table) is detected: Please watch below two of my videos: Oracle Locks Simplified [Part-I] - Understanding Locks Deadlocks and Blocking Locks ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-zbPkyqBPKQ8.html Oracle Locks Simplified [Part-2] - Detecting and Alerting on Blocking Locks using SQL and OEM ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-OlGxt-eCkuM.html Hope this will help.

@@YouVolve Hi there Manash, Thanks for getting back to me so quickly. I really appreciate it. I have now managed to get past the issue of OEM13c not being able to send text messages. I now invoke an OS script on the AIX DB server from the OEM13c incident rule. The script then executes and sends a cell phone number to a sms server who will send the SMS to the receiver's (standby person). To test this I have hard coded the cell number into the script. The problem now is that I need to pass the variable values (cell number, DB name, server name, rule name etc.) to the OS script that I am invoking from within OEM13c. Can you explain how I do that please? Thanks for your much valued reply in advance.

Hey there Manash. Never mind, I have worked it all out. Thanks again for the reply earlier. Take care.

Hi Andre, thanks for the update and glad that it worked.

Hi Vishweshwar, thanks for your feedback. I have a full series in RU-vid (link below) on OEM starting from downloading, installation to configuring OEM in a virtual machine for learning and practice covering topics from very basic to mid-advance level. Already 8 episodes have been uploaded and in the next 2-3 weeks I will cover the incident rule, notification and alerting setup. Please watch the series and you will get everything you need to know.. Link to the series: ru-vid.com/group/PLgWj5dy2RoFZLk49TIxMjW5g8C5K0ntjq

@muralidharkuthethur9749 - Thanks for watching my video. For the Step-by-Step OEM Incident rule creation please watch my video below. Setting up OEM Notification, Monitoring and Alerting: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-j9bA-xagpsY.html Below is my RU-vid series on Full OEM 13c course: Mastering OEM 13c ru-vid.com/group/PLgWj5dy2RoFZLk49TIxMjW5g8C5K0ntjq Hope this will help.

Thanks Arsala. It is in my To-Do list...

Hi Naveen, thanks for watching my video. I am going to cover the incident rules and alert configuration in the next episode (Part-10) of the series “Mastering OEM”which I am going to uploaded either this week or the next. Please watch that series..

Thanks Salad for your question and watching my video. Can you please elaborate your question and what you meant by "complete schema long running Sqls for 24 hours"?

@@YouVolve thank you sir for your prompt response, I mean, application schema long running SQLs for one complete day . Can we monitor it , if yes how ?

Hi Salad, I am still not very clear on your requirement but trying to answer based on what I understood. 1) Yes you can monitor SQLs running for 24 hours. If you have very long running SQLs and you expect them to take a long time like 24 hours but want to send an alert if the run time crosses 24 hours then all you need to do is to change the time limit in the SQL query used in the Metric Extension from 10 minutes to 1440 minutes. 1440 minutes equal to 24 hours. 2) You can also monitor SQLs run by a particular schema/user. For example, in the tutorial, I am excluding SQLs run by SYS user. You can add another filter expression in the WHERE clause as "username='YOUR SCHEMA NAME' " to monitor long running SQLs only for that schema. Hope you got the answers you wanted.

Hi, you can modify the SQL to return a comma separated list of all or the top-n session IDs. The ME is meant for alerting on the longest running SQL but you can always use your creativity to enhance it. 😀

Thanks for your interest. There are many requests for this and I will definitely share one.

please share the video for creating incident rule

Thanks for your question. OEM sends the alert only when certain event/incident takes place. If you just want to remove one occurrence, you can go to the Incident Manager section for the target and delete the incident. If you want to stop it for all future occurrences, then you have to disable/modify the thresholds for the metric for the alerting conditions or if it is coming from from a Metric Extension, then you have to update the thresholds for the alerting conditions or the SQL query (if used) accordingly.