Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy



Oracle OEM automatic threshold alerts

Article by Rampant Author Chris Foot

Automatic Alerting
Oracle10G provides early warning mechanisms to alert administrators of possible error conditions. The database itself collects numerous metrics that were previously collected by 9i's Oracle Enterprise Manager. Administrators are able to select from dozens of server-generated alerts. Server generated alerts are activated by a new background process called MMON. MMON is able to access the SGA directly and perform the metrics calculations and threshold monitoring.

10G Grid Control enhances these capabilities by providing administrators with a GUI interface to administer alerts and configure notification mechanisms. Administrators are able to use Grid Control or the standalone version, Database Control to set up e-mail and pager notifications. In addition, all server-generated alerts are displayed on the Database Control/Grid Control home pages.

As readers of this blog know, I'm a huge fan of 10G Grid Control. Although the database alerts can be set up in the database using the archaic command line interface, if I can do it using Grid Control with a few clicks of the mouse, that's the tool I will use. And to think I used to call 9i OEM "a Sissy GUI Tool".

Setting up 10G's "early warning system" places you in the proactive DBA category as opposed to reactive. The term we us to describe a pure reactive DBA is "firefighter". That's the DBA or DBA team that runs from one fire to the next. I will acknowledge that we are ALL firefighters from time to time. At one of my previous jobs, I spent six months working with another DBA trying to beat an absolutely atrocious third-party vendor package into submission.

You all know the type of third-party vendor package I'm talking about. Someone from your management food chain shows up in your cube and states "Hey, even though you rated this vendor an absolute 0 in all technical categories, and flatly stated that we should not consider them as a viable alternative, we chose them anyway. The Senior VP from the business unit liked the color of their screens. I'm sure you'll be able to get it working."

You then get on 72 conference calls with the vendor after the package crushes a 6 CPU box with 10 concurrent users. Most of the conversations are with the vendor's lead technical experts who are as arrogant as they are inexperienced. Their continued mantra is "you don't understand how Oracle works, it's your fault, you don't understand how Oracle works, it's your fault….". This mantra never stops even though you show them poor SQL coding, transaction looping problems, etc…

But I digress... During my career as an Oracle instructor, one of the most common recommendations I used to provide to my Oracle students was the benefits of database monitoring. My catch phrase was "We monitor by day so we don't get called at night." By the end of the DBA I class, all I would have to say is "We monitor by day…" and the class would respond accordingly. I would then award them with a set of scripts that allowed them to do database connect checks and track datafile free space.

10G Grid Control does all that (and more) for you automatically. The set of checks range the spectrum, from agent unable to connect to target (that means either the database, server or agent broke), to tablespace freespace and alert log errors. As I stated previously, 10G provides dozens of alert choices and is your one-stop-shop for proactive database monitoring. That's a good thing. I would rather wade through dozens of alerts I don't want to activate than spend ANY time coding an alert that wasn't provided by the toolset.

10G OEM's Early Warning System at Giant Eagle

As I stated, I am a big fan of using Oracle Enterprise Manager as the foundation for our proactive notification system. I'm glad to report that 10G OEM contains many new features that justify my preference for this tool. We have fully implemented 10G's server generated alert feature here at Giant Eagle. We use the 10G OEM GUI interface, the "SGT - Sissy GUI Tool" as I used to call it when I was an Oracle instructor, to administer these server generated alerts. What we have found is that there are a LOT more canned alerts available in 10G than in previous versions. That is a good thing. I would rather wade through dozens of alerts I don't want to activate than spend ANY time coding an alert that wasn't provided by the toolset. Administrators are able to select from close to two-dozen server-generated and over a hundred database-generated alerts. What more could a proactive DBA ask for!

Setting Up the Environment

Like previous versions of OEM, 10G OEM is a multi-tier architecture consisting of the HTML console, a management service with an integrated information repository and management agents running on all monitored targets. During the management service installation process, the installer will ask several questions on the host's e-mail configuration. If you don't know the answers to these questions, you'll need to confer with your friendly operating system administrator. It would be a good idea to have e-mail configured on the host server before you begin the 10G OEM installation.

Notification Methods

Once that is complete, you'll log in to the 10G OEM console using the default account and password provided during the installation. The first thing you'll want to do is set up the notification methods. Navigate to SETUP and then NOTIFICATION METHODS. You do this by clicking on the SETUP tab at the top right hand side of the screen and then selecting the Notification Methods link on the left side of the panel. The input fields will allow you to enter the host server's outgoing SMTP server name. In addition, you can use the default IDENTIFY SENDER AS value "EnterpriseManager" or tailor it to your own preferences. I personally prefer "Greetings From 10G OEM!." You will also need to fill in the SENDER'S E-MAIL ADDRESS. In our system, the SENDER's E-MAIL ADDRESS needed to be a valid account in Giant Eagle's E-mail system.

Creating Administrator Accounts

You will also need to create 10G OEM administrative accounts. Don't confuse these with database and host accounts. This is the account that the 10G OEM users will specify when they log on to the tool itself. These are NOT the accounts for the targets they will be administrating. We'll configure those accounts in an upcoming step. You navigate to the administrator setup panel by clicking on the SETUP tab at the top of the screen and then selecting the Administrators link on the left side of the panel. You'll notice on the screenprint that I have blocked out our account names for security reasons. Click on the CREATE or CREATE LIKE buttons to create the administrator account. You will be asked to provide the administrator's E-mail account, or accounts, on subsequent screens.

Configuring Administrator Accounts

Although the new administrator is now able to log on to 10G OEM, they won't be able to do much without performing some additional configuration activities. Log off with the default 10G OEM account and log back in to the tool using the account you just created. If you look at the top right side of each panel in 10G OEM, you'll see a PREFERENCES link. Click on that link to navigate to the preferences panel. The first screen that appears will allow you to EDIT or ADD additional E-Mail accounts that will be used to receive notifications. The MESSAGE FORMAT was an important configuration parameter in my case. My cell phone provider did not allow text messages over a certain length to be sent to my phone. I had to choose the SHORT FORMAT in order to receive 10G OEM text messages. If text messages aren't being received on your phone, you may want to configure 10G OEM to send you the shorter text messages as I had to.

OEM Preferred Credentials

Before a 10G OEM user can administer targets, they must provide the appropriate accounts and passwords for each target on the preferred credentials panels. This first configuration panel shows the different types of targets that require accounts and passwords to administer. You'll notice that our environment has 30 database targets, 13 host targets and 14 listener targets.

I clicked on the SETUP HOST ICON on the right side of the panel to navigate to the host configuration panel to set up the preferred credentials for one of our hosts. This panel allows you to set up a default credential for all targets that do not have credentials set up individually. The target credential grid allows you to enter host names and their associated operating system accounts and passwords. This is the account that will be used to run host commands and jobs on the target host server.

The database preferred credential panel provides the same input format. Users are able to set up default, normal and privileged account/password combinations for each target database they want to administer. If preferred credential setup is not performed for a particular target, a pop-up box will appear asking you to provide the required account/password combination when you click on the link to navigate to the target's home page.

You'll notice that there are two different types of accounts and passwords, NORMAL and PRIVILEGED. Normal database credentials are used by Enterprise Manager to perform functions that do not require database administrator privileges. These credentials are also used as the default login credentials when accessing an OEM function that requires a database connection. Privileged accounts are SYSDBA database credentials used by functions that access non-open databases or perform database startup/shutdown operations.

Alert Notification Schedule

10G OEM allows administrators to customize how they want to be notified of events or alerts. For example, I may want to have an E-mail sent to my work account during the hours from 9:00AM to 5:00PM and a text page sent to my cell phone during off hours. Administrators use the notification schedule setup panel to customize their alert notifications. You navigate to this panel by clicking on the SETUP tab at the top of the screen and then selecting the Schedule link on the left side of the panel.

Alert Notification Rules

Now that we know how we want to be notified when an event occurs, we'll need to setup up WHAT we want to be notified of. The notification rules panel is used to create notification rules for all monitored targets. You navigate to this panel by clicking on the SETUP tab at the top of the screen and then selecting the Rules link on the left side of the panel.
10G OEM provides templates to simplify the rule notification creation process. You'll notice that we have created our own notification rules. Although this screen print doesn't show it, we have created different notification rules for test and production targets. In addition, we also have some customized notification rules set up for some of our more mission-critical targets that require more stringent monitoring.

Let's continue our discussion by creating a notification rule for our test databases. We do this by clicking on the CREATE button on the notification rules panel. The first rules configuration screen allows us to enter the notification rule's NAME, DESCRIPTION and TYPE (database, host, listener, application server).
Clicking next takes us to the target selection panel. This screen allows us to select the targets we want this notification rule to pertain to.

We then click next to navigate to the availability panel which allows us to select the availability states for which we would like to receive notifications.
We navigate to the metrics selection panel by clicking next. This panel lists all of the different metrics for that target. We use the drop down to select the metrics that we want to be notified of. We'll set the metric's threshold values later.

The object panel is used to limit the objects that are monitored on the target. Traditional SQL syntax is used to select or restrict the objects. In this case, we have used the NOT LIKE SQL syntax to prevent the monitoring of all tablespaces that have the characters "TEMP" in them. Hey, if an application developer writes a five-table join that blows out our 7 GIG test TEMP tablespace, let them call me.

Metrics and Thresholds and Alerts

Metrics are units of measurement that are set at predefined values to assess the health of the target being monitored. These predefined values are called metric thresholds. Thresholds are one or more values against which current statistical values are compared. Alerts are generated when a metric's threshold is exceeded.
10G OEM divides the threshold settings into warning and critical categories and allows administrators to configure a specific set of actions that will occur when that threshold category is reached. For example, the DBA may want to be e-mailed if a particular host's CPU utilization exceeds the warning threshold of 70% and paged if the host's CPU utilization exceeds the critical threshold of 90%.
Each target (host, database, listener, application server) has its own set of predefined metrics that can be customized to address each application's unique performance monitoring requirements. In addition, administrators are able to create their own set of user-defined metrics to provide a truly customized monitoring strategy for each monitored target.

Let's end our discussion by setting up thresholds for a target database. You navigate to the manage metrics screen by clicking on the TARGETS link on 10G OEM's home page and then clicking on the DATABASE link which will take you to the databases being monitored. You select the database you want to configure which takes you to the target database's home page. Click on the PERFORMANCE tab and then scroll down until you see the Manage Metrics link. Clicking on this link will take you to the manage metrics panel. This panel lists all of the metrics associated with that particular target type. Click the Edit Threshold link to edit the threshold values. You can also use the Copy Threshold link to copy this target's thresholds to other like targets.


To test your configuration, I highly suggest that you create a rule for one of your databases and then set a few of the target's threshold values. We tested our first alert by filling up our USERS tablespace in one of our DBA play databases. Although the initial setup is somewhat tricky, I know that you'll become a 10G notification guru in no time.




 Copyright © 1996 -2016 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks