BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

Oracle Server-Generated Alerts

Oracle Tips by Burleson Consulting

This is an excerpt from "Oracle 10g New Features for Administrators" by Ahmed Baraka.

Introduction to Metrics

MMON collects database metrics continuously and automatically saves them in the SGA for one hour.

The OEM Database Control’s All Metrics page offers an excellent way to view the various metrics.

Oracle Database 10g Metric Groups are (can be obtained from V$METRICGROUP):

o Event Class Metrics
o Event Metrics
o File Metrics
o Service Metrics
   V$SERVICEMETRIC, V$SERVICEMETRIC_HISTORY

o Session Metrics
o System Metrics
   V$SYSMETRIC, V$SYSMETRIC_HISTORY

o Tablespace Metrics

Viewing Saved Metrics

MMON will automatically flush the metric data from the SGA to the DBA_HISTORY_* views on disk. Examples of the history views are DBA_HIST_SUMMARY_HISTORY,

DBA_HIST SYSMETRIC_HISTORY, and DBA_HIST_METRICNAME. Each of these views contains snapshots of the corresponding V$ view.

Database Alerts

There are three situations when a database can send an alert:

• A monitored metric crosses a critical threshold value

• A monitored metric crosses a warning threshold value

• A service or target suddenly becomes unavailable

Default Server-Generated Alerts

Your database comes with a set of the following default alerts already configured. In addition, you can choose to have other alerts.

• Any snapshot too old errors

• Tablespace space usage (warning alert at 85 percent usage; critical alert at 97 percent usage)

• Resumable session suspended

• Recovery session running out of free space

Server-Generated Alert Mechanism

MMON process checks all the configured metrics and if any metric crosses a preset threshold, an alert will be generated.

Using the Database Control to Manage Server Alerts

You can use Database Control to:

• set a warning and critical threshold

• A response action: a SQL script or a OS command line to execute

• set Notification Rules: when notify a DBA

Using the DBMS_SERVER_ALERT Package to Manage Alerts

SET_THRESHOLD - This procedure will set warning and critical thresholds for given metrics.

DBMS_SERVER_ALERT.SET_THRESHOLD(
DBMS_SERVER_ALERT.CPU_TIME_PER_CALL,
DBMS_SERVER_ALERT.OPERATOR_GE, '8000',
DBMS_SERVER_ALERT.OPERATOR_GE, '10000', 1, 2,
'inst1',
DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE,
'dev.oracle.com')

In this example, a warning alert is issued when CPU time exceeds 8000 microseconds for each user call and a critical alert is issued when CPU time exceeds 10,000 microseconds for each user call. The arguments include: 

o CPU_TIME_PER_CALL specifies the metric identifier. For a list of    support metrics, see PL/SQL Packages and Types Reference.

o The observation period is set to 1 minute. This period specifies the number    of minutes that the condition must deviate from the threshold value before    the alert is issued.

o The number of consecutive occurrences is set to 2. This number specifies    how many times the metric value must violate the threshold values before    the alert is generated.

o The name of the instance is set to inst1.

o The constant DBMS_ALERT.OBJECT_TYPE_SERVICE specifies the    object type on which the threshold is set. In this example, the service name    is dev.oracle.com.

Note: If you don’t want Oracle to send any metric based alerts, simply set the warning value and the critical value to NULL.

GET_THRESHOLD - Use this procedure to retrieve threshold values.

DBMS_SERVER_ALERT.GET_THRESHOLD(
metrics_id IN NUMBER,
warning_operator OUT NUMBER,
warning_value OUT VARCHAR2,
critical_operator OUT NUMBER,
critical_value OUT VARCHAR2,
observation_period OUT NUMBER,
consecutive_occurrences OUT NUMBER,
instance_name IN VARCHAR2,
object_type IN NUMBER,
object_name IN VARCHAR2)

See the section "Proactive Tablespace Management" for more examples of using DBMS_SERVER_ALERT package.

Using the Alert Queue

You can use the DBMS_AQ and DBMS_AQADM packages for directly accessing and reading alert messages in the alert queue.

Steps you should follow are:

1. Create an agent and subscribe the agent to the ALERT_QUE using the     CREATE_AQ_AGENT and ADD_SUBSCRIBER procedures of the     DBMS_AQADM package.

2. Associate a database user with the subscribing agent and assign the enqueue privilege     to the user using the ENABLE_DB_ACCESS and GRANT_QUEUE_PRIVILEGE     procedures of the DBMS_AQADM package.

3. Optionally, you can register with the DBMS_AQ.REGISTER procedure to receive an     asynchronous notification when an alert is enqueued to ALERT_QUE.

4. To read an alert message, you can use the DBMS_AQ.DEQUEUE procedure or     OCIAQDeq call. After the message has been dequeued, use the     DBMS_SERVER_ALERT.EXPAND_MESSAGE procedure to expand the text of     the message.

Data Dictionary Views of Metrics and Alerts

DBA_THRESHOLDS - lists the threshold settings defined for the instance.

DBA_OUTSTANDING_ALERTS - describes the outstanding alerts in the database.

DBA_ALERT_HISTORY - lists a history of alerts that have been cleared.

V$ALERT_TYPES - provides information such as group and type for each alert.

V$METRICNAME - contains the names, identifiers, and other information about the system metrics.

V$METRIC and V$METRIC_HISTORY - views contain system-level metric values in memory.

V$ALERT_TYPES

STATE - Holds two possible values: stateful or stateless. The database considers all the non-threshold alerts as stateless alerts. A stateful alert first appears in theDBA_OUTSTANDING_ALERTS view and goes to the DBA_ALERT_HISTORY view when it is cleared. A stateless alert goes straight to DBA_ALERT_HISTORY.

SCOPE - Classifies alerts into database wide and instance wide. The only database-level alert is the one based on the Tablespace Space Usage metric. All the other alerts are at the instance level.

GROUP_NAME - Oracle aggregates the various database alerts into some common groups: Space, Performance, Configuration-related database alerts.

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

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

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter