Excellently presented demo. I appreciate you sharing. But how do we return all rows if I want to see akk sessions that are lasting longer than 15 instead of just the top one? Kindly inform us. Regards
@LearningDBwithMemoji - Thanks for your feedback. Please note that OEM MEs are meant to send alerts in short meaningful way as otherwise it will lose focus. Based on the severity determined by the ME, the OEM ruleset sends an alert to phone/PagerDuty (Critical) or over email (Warning). As there may be hundreds of session/SQLs running over the critical threshold in a database with a bad application. If the alert contains the information about all of them, then think how long and boring the message over phone will be. I believe, the DBA on-call will not have that level of patience to listen to the full list, instead he or she will be more interested to hear a message like "5 sessions running longer than 15 minutes in database xxx" - something like that. Once the DBA gets such a message, he or she can go to the database and use the first SQL that I have shared through my google drive (link in the description of the video) to find out which one to kill or spare. Alternately, if you really want the list of long running sessions over an email, you can setup a cron or batch job using the fist SQL. Condition to trigger the email will be the same as used the 2nd SQL for the ME. So, once the on-call DBA receives a phone alert, at the same time an email with the list of sessions will also arrive. Please let me know if that helps.
Thank you for such a clear and precise video. I implemented this and created my incident rule, the alerts are coming in now. Question please:- we noticed an insert statement that has been running for 7+ hours on one of the nodes but no alert is generated. Please do you know what could be the issue here?
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.
Hello there, Your videos are very helpful, informative and easy to follow. Thank you so much for your clear explanations and exmples. 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? For example, from an incident rule when a metric extention(like a blocking lock on a table) is detected.
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: th-cam.com/play/PLgWj5dy2RoFZLk49TIxMjW5g8C5K0ntjq.html 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 th-cam.com/video/zbPkyqBPKQ8/w-d-xo.html Oracle Locks Simplified [Part-2] - Detecting and Alerting on Blocking Locks using SQL and OEM th-cam.com/video/OlGxt-eCkuM/w-d-xo.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.
Very well-illustrated Demo. Thank you for sharing. However, instead of the top 1 if I want all the session that is running more than 15, how do we return all rows? Please let us know. Thank you
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 Vishweshwar, thanks for your feedback. I have a full series in TH-cam (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: th-cam.com/play/PLgWj5dy2RoFZLk49TIxMjW5g8C5K0ntjq.html
@neerusharma8482 - Thanks for your question. This tutorial is for creating a Metric Extension (ME) for alerting on long running SQLs. An ME can operate on a single value to alert on. It cannot accept multiple values returned by the base SQL. Moreover it does not matter how many SQLs are running longer than the threshold you set. It may be 1 or 10 or even more. All you need is an alert to notify that a DB performance issue is going on. Now coming to your question about how to get a list of all SQLs running longer. For that, you have to create a report in BI Publisher Reports, a shell script or any other tool. You may modify the SQL used in the ME to remove the MAX grouping and select the columns in the SELECT list such as SQL_ID, SQL_Text, Elapsed_Time etc. You can tweak the ME and the SQL used to get a few of the SQL IDs in question etc, but not all the details.
@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: th-cam.com/video/j9bA-xagpsY/w-d-xo.html Below is my TH-cam series on Full OEM 13c course: Mastering OEM 13c th-cam.com/play/PLgWj5dy2RoFZLk49TIxMjW5g8C5K0ntjq.html Hope this will help.
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 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..
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 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.
Very well-illustrated video. Thanks for sharing your knowledge with us.
Thanks for your feedback.
Excellently presented demo. I appreciate you sharing. But how do we return all rows if I want to see akk sessions that are lasting longer than 15 instead of just the top one? Kindly inform us. Regards
@LearningDBwithMemoji - Thanks for your feedback.
Please note that OEM MEs are meant to send alerts in short meaningful way as otherwise it will lose focus. Based on the severity determined by the ME, the OEM ruleset sends an alert to phone/PagerDuty (Critical) or over email (Warning). As there may be hundreds of session/SQLs running over the critical threshold in a database with a bad application. If the alert contains the information about all of them, then think how long and boring the message over phone will be. I believe, the DBA on-call will not have that level of patience to listen to the full list, instead he or she will be more interested to hear a message like "5 sessions running longer than 15 minutes in database xxx" - something like that. Once the DBA gets such a message, he or she can go to the database and use the first SQL that I have shared through my google drive (link in the description of the video) to find out which one to kill or spare.
Alternately, if you really want the list of long running sessions over an email, you can setup a cron or batch job using the fist SQL. Condition to trigger the email will be the same as used the 2nd SQL for the ME. So, once the on-call DBA receives a phone alert, at the same time an email with the list of sessions will also arrive. Please let me know if that helps.
Thank you for such a clear and precise video. I implemented this and created my incident rule, the alerts are coming in now. Question please:- we noticed an insert statement that has been running for 7+ hours on one of the nodes but no alert is generated. Please do you know what could be the issue here?
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.
Hello there,
Your videos are very helpful, informative and easy to follow. Thank you so much for your clear explanations and exmples.
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? For example, from an incident rule when a metric extention(like a blocking lock on a table) is detected.
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:
th-cam.com/play/PLgWj5dy2RoFZLk49TIxMjW5g8C5K0ntjq.html
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
th-cam.com/video/zbPkyqBPKQ8/w-d-xo.html
Oracle Locks Simplified [Part-2] - Detecting and Alerting on Blocking Locks using SQL and OEM
th-cam.com/video/OlGxt-eCkuM/w-d-xo.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.
Very well-illustrated Demo. Thank you for sharing. However, instead of the top 1 if I want all the session that is running more than 15, how do we return all rows? Please let us know. Thank you
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. It's a very informative video. Can you also share the link for the video on how to create an incident rule?
Hi Vishweshwar, thanks for your feedback.
I have a full series in TH-cam (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:
th-cam.com/play/PLgWj5dy2RoFZLk49TIxMjW5g8C5K0ntjq.html
Hi Sir according to you we are getting only one row in result, how can we retrieve all long running sqls according to specified time
@neerusharma8482 - Thanks for your question.
This tutorial is for creating a Metric Extension (ME) for alerting on long running SQLs. An ME can operate on a single value to alert on. It cannot accept multiple values returned by the base SQL. Moreover it does not matter how many SQLs are running longer than the threshold you set. It may be 1 or 10 or even more. All you need is an alert to notify that a DB performance issue is going on.
Now coming to your question about how to get a list of all SQLs running longer.
For that, you have to create a report in BI Publisher Reports, a shell script or any other tool.
You may modify the SQL used in the ME to remove the MAX grouping and select the columns in the SELECT list such as SQL_ID, SQL_Text, Elapsed_Time etc. You can tweak the ME and the SQL used to get a few of the SQL IDs in question etc, but not all the details.
@@YouVolve Thanks for reply, I just started watching you sessions. That is very good
Thanks for your feedback.
Can u pls post event rule creation of this metric extension sir
@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:
th-cam.com/video/j9bA-xagpsY/w-d-xo.html
Below is my TH-cam series on Full OEM 13c course:
Mastering OEM 13c
th-cam.com/play/PLgWj5dy2RoFZLk49TIxMjW5g8C5K0ntjq.html
Hope this will help.
Hi sir , you are really amazing , can we monitor a complete schema long running Sqls for 24 hours?
Thank you
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.
Good video
Thanks Shymon
I Followed the same steps and i had created incident rule also but iam unable to get alert mails
can you please reply me
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 for share , please make video how to create incident rule , and send email notification
Thanks Arsala. It is in my To-Do list...
How to create incident rule for it ?
Please share video
Thanks for your interest. There are many requests for this and I will definitely share one.
please share the video for creating incident rule
This will return only one session information. What if there are multiple sessions running long running sql's
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. 😀
How to remove OEM alerts which came unnecessary
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.