Configuring 10G OEM Grid Control's Automatic Alert Notification System

Protecting Core Data Assets
Database administrators, by the very essence of their job descriptions, are the protectors of their organization's core data assets. They are tasked with ensuring key data stores are continuously available. However, ensuring that data is available on a 24 x 7 basis is a wonderfully complex task. Hardware failures, software failures, user errors and performance problems all combine to make many administrators lie awake nights thinking about whether their databases will be continuously available (or not).

Proactive vs Reactive DBA Support
Daily support requests and problem solving activities often overwhelm many DBA groups. Understaffing, over-commitment to supporting new and legacy application developments, lack of repeatable processes and lack of budget are the most common causes of DBAs being reactive instead of proactive. Proactive DBAs understand that time must be spent creating a performance and availability monitoring infrastructure.

The reactive DBA can be compared to a firefighter; they resolve problems but never prevent them. The biggest problems garner the most attention. The reactive DBA's time is dominated by these firefighting activities, reducing the amount of time they are able to spend implementing the process and procedures required to switch their mode from reactive firefighting to proactive problem prevention.

Numerous methods can be used to create this monitoring infrastructure. Although I personally prefer using Enterprise Manager as the foundation for Giant Eagle's early warning system, I have also written my fair share of operating system scripts to monitor both databases and operating systems.

10G EM'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 EM 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 EM 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 EM 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 EM installation.

Notification Methods
Once that is complete, you'll log in to the 10G EM 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 EM!." 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 EM administrative accounts. Don't confuse these with database and host accounts. This is the account that the 10G EM 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 EM, they won't be able to do much without performing some additional configuration activities. Log off with the default 10G EM 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 EM, 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 EM text messages. If text messages aren't being received on your phone, you may want to configure 10G EM to send you the shorter text messages as I had to.

Setting Up Preferred Credentials
Before a 10G EM 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 EM 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.

Notification Schedule
10G EM 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.

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 EM 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 - Oh My!
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 EM 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 EM'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.

Testing
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.

Next Up
Using 10G EM to perform database performance monitoring and tuning.