The Self-Managing Database : Automatic Health Monitoring and Alerting

Size: px

Start display at page:

Download "The Self-Managing Database : Automatic Health Monitoring and Alerting"

주현 강
6 years ago
Views:

2 The Self-Managing Database : Automatic Health Monitoring and Alerting

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

4 (Self-Managing Database)

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

6 (Database Management Challenges)?? SQL???

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

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

9 -2

(Automatic Database Health Monitoring) Storage Management Advisory Infrastructure Application & SQL Management Server-generated Alert Infrastructure Automatic Workload Repository Intelligent

10 (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)

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

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

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

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

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

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

17 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

18 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

19 (Monitoring Architecture)

20 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

21 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

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

23 EM(Enterprise Manager) Oracle 10g

24 EM(Enterprise Manager) (Pager, )

25 EM - Target Database

26 EM - ADDM Finding Details

27 EM - SQL Tuning Advisor

28 EM - Database Performance

29 EM - User I/O Wait Class Drilldown

30 (Manage Metrics) - (Edit Thresholds) Manage Metrics

31 3 rd Party PL/SQL APIs

32 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);

33 : 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()

34 : 3 rd Party 6. Configure 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)!

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

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

Jerry Held

Jerry Held DB / TSC Oracle Database 10g (Self-Managing Database) (Common Infrastructure) (Automatic Workload Repository) (Server-generated Alerts) (Automated Maintenance Tasks) (Advisory Framework) (ADDM) (Self-Managing