HP OpenView Database Management Oracle Users Group Briefing Jan, 2002

1. HP OpenView Database Management Oracle Users Group Briefing Jan, 2002 1 H

2. Agenda • Application Management Overview • SPI for Oracle • Features • Architecture • Customization • Screenshots

3. Build new databases and keep existing ones running Keep track of long-running projects while fighting fires Doing more with less while avoiding burn-out Primary concerns are database performance and availability Act as central integration point for application groups Challenges to DBAs

4. The Enterprise Database SolutionBrief Description • HP OpenView Database Plug-Ins add intelligence to key OpenView availability, performance and reporting tools and OpenView and partner administration tools in order to: • keep your databases up and running • propose solutions to performance problems • enable you to predict future capacity needs • report service achievements back to the business • administer your databases centrally

5. Access Manager OmniBack II IT/Operations Console PerfView/MeasureWare Service Reporter SMART Plug-Ins DB Vendor Admin Tools GlancePlus The Enterprise Solution

6. Value Proposition • HP OpenView Database Plug-Ins enable you to: • Reduce downtime and improve performance --> recover lost revenue, improve productivity • Do more with less --> manage more data by leveraging lower-skilled staff and minimizing training needs • get up and running quickly with out-of-the-box deployment and minimal customization --> Get a fast return on management investment

7. DBSPI’s features

8. DBSPI’s features

9. SPI for Oracle OpenView Integration • 4 Collects/Analyzers obtain data using Embedded SQL, calculate metrics and provide early warnings • Oracle7 and Oracle8 • database status, freespace, object growth, rollback segment contention, lock contention, buffer & sort performance, parameters, redo logs, archives, trace files, OPS awareness, parallel query, MTS • filtering and multi-thresholds for tablespaces & objects • user-definable metrics protect home-grown scripts 82 Metrics Integration of OEM events and applications into ITO Oracle Enterprise Manager Integration Every event has an associated helptext which describes the probable cause and suggested action ~100 alert log conditions Frequently used DB tools and reports as ITO applications 60+ Oracle Applications • Interval can be customized via central console • Metrics available in PV and Service Reporter 77 Performance Metrics

10. Database Objects Rollback Segments Tablespaces SPI for Oracle Monitoring Oracle SGA Redo Log Buffer Database Buffer Cache Sort Area Shared Pool Library Cache Dictionary Cache Oracle Processes SMON PMON DBWR LGWR ARCH CKPT Alert and Trace Files Offline Storage Device Redo Log Files Database Files Control Files

11. - Collect segment information from dba_free_space, dba_segments and v$parameter - Interval: 15 min Operator-Initiated: -list all segments in danger Helptext: -change pctincrease, next_extent, resize or add datafiles Calculation: complex correlation: - autoextend? - dynamic coalescence Message: # of segments that cannot extend Area: Oracle Fault Severity: Critical SPI for Oracle Scenario# of segments unable to extend Collecting # of segments unable to extend Acting Processing Presenting

12. User Definable Metrics Configuring UDMs Centralized configuration, use ITO to distribute, needs ITO monitor templates METRIC defines a metric, PL/SQL is used to define algorithm options are MW, ITO, RATE, MW RATE, ITO RATE, MW ITO RATE multiple REPORT lines per metric ==> auto action Example: ORACLE METRIC 0700 COLLECT MW ITO " declare sql1_val number; sql2_val number; begin :dbspi_error := '<no error>'; select value into sql1_val from v$sysstat where name='summed dirty queue length'; ... REPORT 1 INCLUDE “mhaereport.sql” REPORT 2 “select value from v$sysstat where...

13. Metric Process ArchitectureIT/Operations and/or MeasureWare Collection Scheduler Metric MonitorsTemplates ITO Monitor Agent Initiate Metric Collection Submitting values using ITO Agent API Persistent Store for Delta Calculations RDBMS DB-SPI Metric Collector/Analyzer MeasureWare DSILOG Connect using Embedded SQL

14. Metric Process Example Collect metric 4, 26, 31 every 5 min ITO Monitor Agent RDBMS DB-SPI Metric Collector/Analyzer SQL statements for Metric 4 Metric 26 Metric 31 Calculate Metrics DBSPI-0004 Monitor Template Submit value for DBSPI-0004 DBSPI-0026 Monitor Template Submit value for DBSPI-0026 Submit value for DBSPI-0031 DBSPI-0031 Monitor Template

15. Logfile Monitoring Architecture ITO Logfile Conditions ITO Logfile Encapsulator LogfileMonitoring RDBMS Logfile RDBMS

16. Application Desktop Architecture DB-SPI Application Desktop ITO Action Agent requests Report DB-SPI Metric Collector/Analyzer DB-SPI Application Wrapper Checks for configured instance and other parameters launch RDBMS Application RDBMS DB-SPI Configuration Store

17. Levels of CustomizationChange collection interval in one group globally

18. Levels of CustomizationChange threshold for all instances Empty = matches all instances

19. Levels of CustomizationChange threshold for specific instances • Processing order: Top down • “Match all” rule always at the end • Stricter rules before more generic rules

20. Combining OpenView and Oracle EM Service Level Management Systems Network Storage Databases Applications End-to-end Response Time Management

21. Oracle Enterprise Manager Integration ITO Application Bank ITO Message Browser OEM Console Admin Tool Management Console Management server OMS Java API ITO agent OEM Intelligent Agent Managed Node Oracle DB or Application

22. EM Event Integration

23. Our bottom line depends on the health of our information systems! CFO Financial Benefits • Reduce Downtime/Improve productivity • early warning before outages • detect performance problems before users call • immediate notification of critical situations • Quantify Savings • recovered revenue for manufacturing, accounting, distribution. etc. • DBA time and cost • leverage of using same OV tools

24. We need to manage more databases but I must ensure my guys don’t leave! DBA Manager Benefits for the DBA Manager • Avoid burnout and retain DBA staff • Use tools to reduce pressure • Team up with operations using 2-tier model • DBAs have total control of operators’ environments • Manage more with less • centralize monitoring • Management reports at your fingertips • web-based service reports • report on top problems, availability, workload

25. Typical Oracle Monitoring Needs • Avoid typical database problems • archive stuck • run out of extents • snapshot too old • Notify immediately of critical problems • ORA-600 internal errors • Report on performance bottlenecks • buffer and library cache hit ratios • poor sort performance, table scans • lack of optimizer stats and indexes • database overhead on the system • Track growth over time • tablespace freespace • object growth

28. SPI for Oracle ExampleMessage Groups

29. SPI for Oracle Example# of foreign objects

30. SPI for Oracle Example# of foreign objects

31. SPI for Oracle Example (ITO Application Bank)

32. MeasureWare IntegrationPerfView (1) ID matches the metrics DBSPI-0xxx

33. MeasureWare integrationPerfView (2)

34. Glance IntegrationScreenshots (1)

35. Glance IntegrationScreenshots (2)

36. Levels of CustomizationEnable/Disable and move to other metric groups • Metrics are enabled/disabled in the collection scheduling metric • Not by assigning the External Metrics Monitors to the node

37. Levels of CustomizationCustom grouping Template identifier has to match the “-c” Collector/Analyzer commandline parameter

38. Levels of CustomizationTAG Feature: Examples Screenshot • Commandline parameter “-t SAP” • Collector/Analyzer submits values with the SAP prefixe.g. SAPDBSPI-0020

39. SPS ContactsOpenView (301) 963-4777 x31mark.weinel@spsnet.comMarketing (301) 963-4777 x16nancy.vogel@spsnet.com