The Self-Managing Database : Automatic Health Monitoring and Alerting

The Self-Managing Database : Automatic Health Monitoring and Alerting

Agenda Oracle 10g Enterpirse Manager Oracle 10g 3 rd Party PL/SQL API Summary

(Self-Managing Database)

? 6% 6% 12% 55% 6% Source: IOUG 2001 DBA Survey

(Database Management Challenges)?? SQL???

: (Self Managing Database) Enterprise Manager Database Console Monitor SQL Alert Fix Advise Common Manageability Infrastructure

-1 ( I/O) (1% ) Pull-based SGA CPU

-2

(Automatic Database Health Monitoring) Storage Management Advisory Infrastructure Application & SQL Management Server-generated Alert Infrastructure Automatic Workload Repository Intelligent Infrastructure System Resource Management Automatic Maintenance Task Infrastructure Backup & Recovery Space Database Management Management Management (Advisory Infrastructure) (Server-generated Alerts) (Automatic Maintenance Tasks) (Automatic Workload Repository)

: - Alert Polling DB (Pager / E-Mail) V$ Views Poll Metrics / SGA Structures

: - Alert Pushing DB (Pager / E-Mail) / Server Pushes Alerts MMON (SGA) (SGA) ALERT_QUE Advanced Queue (Automatic Persistence) (AWR)

10g No Pinging (Data Push, Proactive) (0.1% ) MMON (SGA) (SGA)

(Type of Alerts) (Threshold-based Alerts) : : (Non-threshold-based Alerts) : : ORA-1555 Snapshot too old

(Alert Types Examples) (Threshold based) I/O CPU I/O (Non-Threshold based) Snapshot too old

(Threshold Configuration) (Metric) 2 (Warning Threshold) (Critical Threshold)

Out-of-Box Alerts Tablespace Space Usage (warning 85%, critical 97%) Snapshot Too Old Recovery Area Low On Free Space Resumable Session Suspended Enterprise Manager

Server-Generated Alerts Metric-Based 97% Critical Cleared Threshold Alerts Alert 85% Warning Cleared Alert MMON Non- Threshold Alerts Snapshot Too Old Resumable Session Suspended Alert Alert Alert Event-Based Recovery Area Low On Free Space

(Monitoring Architecture)

Recent Metrics Metric History Server-generated Alerts DBA_HIST_SYSMETRIC_ HISTORY V$SYSMETRIC_HISTORY V$SYSMETRIC DBA_THRESHOLDS V$ALERT_TYPES DBA_OUTSTANDING_ALERTS DBA_ALERT_HISTORY

10g Automatic Database Diagnostic Monitor SQL Tuning Memory Access PGA SGA Buffer Cache Shared Pool (ADDM) Space Segment Advisor Undo Common data source AWR Seamless integration

ADDM (Automatic Database Diagnostic Monitor) MMON (SGA) (SGA) ADDM SQL- (SQL ) 10g Advisors AWR Snapshot ( Wait )

EM(Enterprise Manager) Oracle 10g

EM(Enterprise Manager) (Pager, E-Mail)

EM - Target Database

EM - ADDM Finding Details

EM - SQL Tuning Advisor

EM - Database Performance

EM - User I/O Wait Class Drilldown

(Manage Metrics) - (Edit Thresholds) Manage Metrics

3 rd Party PL/SQL APIs

DBMS_SERVER_ALERTS SET_THRESHOLD GET_THRESHOLD SQL> exec DBMS_SERVER_ALERT.SET_THRESHOLD (9000, Metric Identifier, METRIC_ID in V$METRICNAME DBMS_SERVER_ALERT.OPERATOR_GE, Operator for Warning Threshold 60, Warning Threshold DBMS_SERVER_ALERT.OPERATOR_GE, Operator for Critical Threshold 80, Critical Threshold 1, Observation Period in Minutes 1, Occurrences NULL, Instance Name (NULL implies ORACLE_SID value) DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE, Object Type 'KITCHEN Name of the Object);

: 3 rd Party 1. Subscribe to ALERT_QUE using dbmsaqadm.add_subscriber() 2. Create the agent for the subscribing user of the alerts using the dbmsaqadm.create_aq_agent() 3. Associate the db user with the AQ agent using the dbmsaqadm.enable_db_access() 4. Grant the dequeue privilege using the dbms_aqadm.grant_queue_privilege() 5. Register for alert enqueue notification (optional) using aq$reg_info() and dbms_aq.register()

: 3 rd Party 6. Configure e-mail and http proxy using various procedures in dbms_aqelm.set*() 7. Dequeue the alert using dbms_aq.dequeue() 8. Reveal the entire alert message using dbms_server_alert.expand_message() EM(Enterprise Manager)!

Oracle Database 10g? Proactive and Strategic Planning 50+% 3% 6% 3% 3% 25%

Summary Oracle 10g (Intelligent Infrastructure) Oracle 10g, Oracle 10g,