{"id":4948,"date":"2015-06-12T12:22:23","date_gmt":"2015-06-12T10:22:23","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution\/"},"modified":"2015-06-12T12:22:23","modified_gmt":"2015-06-12T10:22:23","slug":"using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution\/","title":{"rendered":"using dbms_server_alert in combination with a custom monitoring solution"},"content":{"rendered":"<p>Lot&#8217;s of companies do not use Grid- or Cloud Control for monitoring their Oracle databases for various reasons but rather use open source tools like <a href=\"http:\/\/www.nagios.org\/\" target=\"_blank\" rel=\"noopener\">nagios<\/a>. And lot of those either implemented custom script frameworks or rely on plug-ins available for the monitoring solution of choice. This post shall show on how you can let Oracle check a lot of its own metrics and you only need one script for alerting various metrics. Oracle provides a plsql package called <a href=\"http:\/\/docs.oracle.com\/database\/121\/ARPLS\/d_server_alert.htm#ARPLS016\" target=\"_blank\" rel=\"noopener\">DBMS_SERVER_ALERT<\/a>. This package defines plenty of metrics which might be used for alerting. Let&#8217;s take the famous tablespace percent full metric as an example and do a little demo:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; create tablespace tbs1 datafile size 1m;\n\nTablespace created.\n\nSQL&gt; create table t1 ( a varchar2(2000) ) tablespace tbs1;\n\nTable created.\n<\/pre>\n<p>So you want to get a warning if the tbs1 tablespace grows up to 80% and you want to get a critical message if the tablespace exceeds 90%? Easy:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; !cat a.sql\nbegin\n      dbms_server_alert.set_threshold\n        ( metrics_id =&gt; dbms_server_alert.tablespace_pct_full\n        , warning_operator =&gt; dbms_server_alert.operator_ge\n        , warning_value =&gt; 80\n        , critical_operator =&gt; dbms_server_alert.operator_ge\n        , critical_value =&gt; 90\n        , observation_period =&gt; 1\n        , consecutive_occurrences =&gt; 1\n        , instance_name =&gt; null\n        , object_type =&gt; dbms_server_alert.object_type_tablespace\n        , object_name =&gt; 'TBS1'\n        );\nend;\n\/\n\nSQL&gt; @a\n\nPL\/SQL procedure successfully completed.\n\nSQL&gt; \n<\/pre>\n<p>Let&#8217;s generate some data:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; insert into t1 select lpad('0',2000,'0') from dual;\n\n1 row created.\n\nSQL&gt; insert into t1 select * from t1;\n\n1 row created.\n\nSQL&gt; insert into t1 select * from t1;\n\n2 rows created.\n\nSQL&gt; insert into t1 select * from t1;\n\n4 rows created.\n\ninsert into t1 select * from t1;\n\n8 rows created.\n\nSQL&gt; insert into t1 select * from t1;\n\n16 rows created.\n\ninsert into t1 select * from t1;\n\n32 rows created.\n\nSQL&gt; insert into t1 select * from t1;\n\n64 rows created.\n\nSQL&gt; commit;\n\nCommit complete.\n\n<\/pre>\n<p>Did this already generate an alert?<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; set lines 264\nSQL&gt; col OWNER for a10\nSQL&gt; col OBJECT_NAME for a10\nSQL&gt; col REASON for a50\nSQL&gt; select OWNER,OBJECT_NAME,OBJECT_TYPE,MESSAGE_LEVEL from dba_outstanding_alerts;\n\nOWNER\t   OBJECT_NAM OBJECT_TYPE\t\t\t\t\t\t       MESSAGE_LEVEL\n---------- ---------- ---------------------------------------------------------------- -------------\n\t   TBS1       TABLESPACE\t\t\t\t\t\t\t\t   1\nSQL&gt; \n<\/pre>\n<p>Yes it did. So all you need is a script that periodically checks the dba_outstanding_alerts view for any alerts and depending on the message_level send a warning or critical message to wherever you want. As soon as the issue is resolved the message in dba_outstanding_alerts will disappear after the next observation_period:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; alter tablespace tbs1 add datafile size 10m;\n\nTablespace altered.<\/pre>\n<p class=\"brush: sql; gutter: true; first-line: 1\">(after one minute):<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select OWNER,OBJECT_NAME,OBJECT_TYPE,MESSAGE_LEVEL from dba_outstanding_alerts;\n\nno rows selected\n\nSQL&gt;<\/pre>\n<p>The good thing with this is: You only need one script for alerting all the metrics defined in the package. But be careful, not all metrics are really useful and you might end up in getting hundreds of notifications. Another goody: As far as I know you do not need the diagnostic pack for this.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Lot&#8217;s of companies do not use Grid- or Cloud Control for monitoring their Oracle databases for various reasons but rather use open source tools like nagios. And lot of those either implemented custom script frameworks or rely on plug-ins available for the monitoring solution of choice. This post shall show on how you can let [&hellip;]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198],"tags":[143,96],"type_dbi":[],"class_list":["post-4948","post","type-post","status-publish","format-standard","hentry","category-database-management","tag-monitoring","tag-oracle"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>using dbms_server_alert in combination with a custom monitoring solution - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"using dbms_server_alert in combination with a custom monitoring solution\" \/>\n<meta property=\"og:description\" content=\"Lot&#8217;s of companies do not use Grid- or Cloud Control for monitoring their Oracle databases for various reasons but rather use open source tools like nagios. And lot of those either implemented custom script frameworks or rely on plug-ins available for the monitoring solution of choice. This post shall show on how you can let [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-06-12T10:22:23+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"using dbms_server_alert in combination with a custom monitoring solution\",\"datePublished\":\"2015-06-12T10:22:23+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution\/\"},\"wordCount\":276,\"commentCount\":1,\"keywords\":[\"Monitoring\",\"Oracle\"],\"articleSection\":[\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution\/\",\"name\":\"using dbms_server_alert in combination with a custom monitoring solution - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2015-06-12T10:22:23+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"using dbms_server_alert in combination with a custom monitoring solution\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"using dbms_server_alert in combination with a custom monitoring solution - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution\/","og_locale":"en_US","og_type":"article","og_title":"using dbms_server_alert in combination with a custom monitoring solution","og_description":"Lot&#8217;s of companies do not use Grid- or Cloud Control for monitoring their Oracle databases for various reasons but rather use open source tools like nagios. And lot of those either implemented custom script frameworks or rely on plug-ins available for the monitoring solution of choice. This post shall show on how you can let [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution\/","og_site_name":"dbi Blog","article_published_time":"2015-06-12T10:22:23+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"using dbms_server_alert in combination with a custom monitoring solution","datePublished":"2015-06-12T10:22:23+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution\/"},"wordCount":276,"commentCount":1,"keywords":["Monitoring","Oracle"],"articleSection":["Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution\/","url":"https:\/\/www.dbi-services.com\/blog\/using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution\/","name":"using dbms_server_alert in combination with a custom monitoring solution - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-06-12T10:22:23+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/using-dbms_server_alert-in-combination-with-a-custom-monitoring-solution\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"using dbms_server_alert in combination with a custom monitoring solution"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4948","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=4948"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4948\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=4948"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=4948"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=4948"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=4948"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}