1 / 71

Extending Oracle 10g Grid Control New Metrics, Targets and Plug - ins

Extending Oracle 10g Grid Control New Metrics, Targets and Plug - ins. Session number 325. Alex Gorbachev The Pythian Group COLLABORATE 07, Las Vegas 16 April 2007. http://www.pythian.com/documents/IOUG06-Extending10gGC.ppt http://www.pythian.com/documents/IOUG06-Extending10gGC.zip.

Anita
Download Presentation

Extending Oracle 10g Grid Control New Metrics, Targets and Plug - ins

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Extending Oracle 10g Grid ControlNew Metrics, Targets and Plug-ins Session number 325 Alex Gorbachev The Pythian Group COLLABORATE 07, Las Vegas 16 April 2007 http://www.pythian.com/documents/IOUG06-Extending10gGC.ppt http://www.pythian.com/documents/IOUG06-Extending10gGC.zip

  2. Who am I? • Senior Oracle DBA at The Pythian Group • Working with Oracle since ‘97 • Pythian is a leading DBA outsourcing provider – remote DBA 24x7 • Offices worldwide – Canada, US, UK, India, Australia • www.pythian.com/blogs

  3. Why Extending Grid Control? • Keep your own proven monitoring • Monitor non-Oracle components • In-house or COTS applications • Software and hardware vendors • Cut costs – avoid expensive packs • Write once – use many times

  4. Pythian Real Life Example • Pythian has monitoring tool “Avail” • Built by DBA’s for DBA’s • Years of development and contributions of many DBA’s • More than just Oracle monitoring • Many customers use Avail • They don’t want to loose proven monitoring • Some have invested a lot into OEM but want to leverageyears of experience with other tool

  5. Agenda • User Defined Metrics (UDM) • New target types • Example – MySQL • Management Plug-ins • Advanced Topics • Reports Presentation should be viewed in slide mode to show animation properly

  6. User Defined Metrics • To extend existing targets • Oracle Instance targets • SQL querybased • Host targets • OS scripts based • UDMs - bottom of the target’s home page in the Related Links section

  7. SQL based UDM – 10gR1 • Single column, single row • Numeric value • Thresholds operators – = < <= != etc. • Text value • Threshold operators • CONTAINS, MATCH, = • Avoid NULL values • Use “Test” button • SELECT ROUND (c.open_cursors / p.value * 100) open_cursors • FROM (SELECT sid, COUNT (*) open_cursors • FROM v$open_cursor • GROUP BY sid • ORDER BY 2 DESC) c, • v$parameter p • WHERE p.name = 'open_cursors' AND ROWNUM = 1; • Credentials (username + password) • Thresholds (70,90) • Alert message • Collection interval

  8. Demo open cursors metric • DEMO

  9. SQL based UDM – 10gR2 • Two columns, multiple rows • The key • The value (numeric or text) • Each key-value pair is treated separately SELECT q.owner || '.' || q.NAME queue_name, s.ready FROM v$aq s, dba_queues q WHERE s.qid = q.qid AND q.owner IN ('PYTHIAN') • Key based thresholds “<key1>:<th1>;…” • “QUEUE1:10;QUEUE2:100” • Custom alert message • Queue %Key% contains %value% messages

  10. Demo AQ messages • DEMO

  11. OS script based UDM • Script/binary returning single value • em_result=<value> + return zero • em_error=<message> + return none-zero • Permissions (!) • %scriptsDir% • $AGENT_HOME/sysman/admin/scripts • %perlBin% • Environment variables

  12. OS script based UDM • Script/binary returning single value status=`/usr/bin/mysqladmin ping 2>&1` if [ "$status" = "mysqld is alive" ] ; then echo "em_result=Up" else mysqld=`ps -e | grep mysqld | grep -v grep | wc -l` if [ "$mysqld" = "0" ] ; then echo "em_result=Down“ else echo "em_error=$status“ ;exit 1 fi fi

  13. Demo MySQL Status • DEMO

  14. UMDs Pros and Cons • Very simple • but… • No new targets • Limited collection mechanisms • No collection on cumulative counters (V$SYSSTAT) • Has to be defined and managed for each instance • Separate credentials • No real-time collection • Requires Database Diagnostic Pack

  15. New Targets – Real Extensibity • Develop new target types • Write once and reuse • Variety of collection mechanisms • Separation of development and operations

  16. OEM Repository Management Servers Grid Control Architecture Agent Agent Agent Agent DBA’s Data collection

  17. MySQL monitoring from EM • Monitor • Status, availability • Performance • Alerts • Critical and warning notifications • Reports • Easy to deploy

  18. Demo MySQL sneak preview • DEMO

  19. New Target Type – Main Steps • Create target type metadata • Validate new target type and metric collection • Define metrics default collection parameters • Distribute metadata and required support files to all hosts (Management Plug-ins) • Add instances of new target type

  20. XML DTD’s – Best Reference • $AGENT_HOME/sysman/admin/dtds • Target type metadata • TargetMetadata.dtd • Metrics default collection • TargetCollection.dtd, Schedule.dtd • Target instances (in targets.xml) • TargetInstance.dtd

  21. Target type metadata $AGENT_HOME/sysman/admin/metadata/pythian_mysql.xml <?xml version="1.0" ?> <!DOCTYPE TargetMetadata SYSTEM "../dtds/TargetMetadata.dtd"> <TargetMetadata META_VER="1.0" TYPE="pythian_mysql"> <Display>…</Display> <Metric>…</Metric> <Metric>…</Metric> <InstanceProperties>…</InstanceProperties> </TargetMetadata>

  22. <Display> <Display> <Label NLSID=“pythian_mysql_name">MySQL</Label> </Display>

  23. Target type metadata <?xml version="1.0" ?> <!DOCTYPE TargetMetadata SYSTEM "../dtds/TargetMetadata.dtd"> <TargetMetadata META_VER="1.0" TYPE="pythian_mysql"> <Display>…</Display> <Metric>…</Metric> <Metric>…</Metric> <InstanceProperties>…</InstanceProperties> </TargetMetadata> After (!) Metrics

  24. <InstanceProperties> <InstanceProperties> <InstanceProperty>…</InstanceProperty> <InstanceProperty>…</InstanceProperty> <InstanceProperty>…</InstanceProperty> <DynamicProperties>…</DynamicProperties> <DynamicProperties>…</DynamicProperties> </InstanceProperties>

  25. <InstanceProperties> <InstanceProperties> <InstanceProperty NAME="host"> <Display> <Label NLSID=“pythian_mysql_host">Hostname </Label> </Display> </InstanceProperty> </InstanceProperties>

  26. <InstanceProperties> <InstanceProperties> <InstanceProperty NAME="password“ CREDENTIAL="TRUE“ HIDE_ENTRY="TRUE“ NEED_REENTER="TRUE“ OPTIONAL="TRUE“> <Display>…</Display> </InstanceProperty> </InstanceProperties>

  27. <InstanceProperties> <InstanceProperties> <InstanceProperty NAME="host“/> <InstanceProperty NAME="port“/> <InstanceProperty NAME="user“/> <InstanceProperty NAME=“password“/> </InstanceProperties>

  28. Target type metadata <?xml version="1.0" ?> <!DOCTYPE TargetMetadata SYSTEM "../dtds/TargetMetadata.dtd"> <TargetMetadata META_VER="1.0" TYPE=“pythian_mysql"> <Display>…</Display> <Metric>…</Metric> <Metric>…</Metric> <InstanceProperties>…</InstanceProperties> </TargetMetadata>

  29. <Metric> <Metric NAME="Response" TYPE="TABLE"> <Display>…</Display> <TableDescriptor>…</TableDescriptor> <QueryDescriptor>…</QueryDescriptor> </Metric> • TABLE • STRING • NUMBER Fetchlets • OS Command • SQL • SNMP • URL Timing • HTTP Data • URLXML • JDBC

  30. <Metric> <Metric NAME="Response" TYPE="TABLE"> <TableDescriptor> <ColumnDescriptor NAME="Status" TYPE="STRING“/> <ColumnDescriptor NAME="response_time“ TYPE="NUMBER"> <Display FOR_SUMMARY_UI="TRUE"> <Label NLSID="resp_time">Response time (ms) </Label> <ShortName NLSID="resp_s">Response</ShortName> </Display> </ColumnDescriptor> </TableDescriptor> </Metric>

  31. <Metric> <Metric NAME="Response" TYPE="TABLE"> <Display>…</Display> <TableDescriptor>…</TableDescriptor> <QueryDescriptor>…</QueryDescriptor> </Metric>

  32. <QueryDescriptor> <Metric NAME="Response" TYPE="TABLE"> <QueryDescriptor FETCHLET_ID="OSLineToken"> <Property NAME="myhost“ SCOPE="INSTANCE">host</Property> <Property NAME="myport“ SCOPE="INSTANCE“ OPTIONAL="TRUE">port</Property> <Property NAME="myuser" SCOPE="INSTANCE“ OPTIONAL="TRUE">user</Property> <Property NAME="mypwd" SCOPE="INSTANCE“ OPTIONAL="TRUE">password</Property> …… </QueryDescriptor> </Metric>

  33. <QueryDescriptor> <Metric NAME="Response" TYPE="TABLE"> <QueryDescriptor FETCHLET_ID="OSLineToken"> …… <Property NAME="startsWith“ SCOPE="GLOBAL">em_result=</Property> <Property NAME="errStartsWith“ SCOPE="GLOBAL">em_error=</Property> <Property NAME="delimiter“ SCOPE="GLOBAL">|</Property> <Property NAME="command“ SCOPE="GLOBAL"> %scriptsDir%/emx/pythian_mysql/ pythian_mysql_load.sh %myhost% %myport% %myuser% %mypwd% </Property> </QueryDescriptor> </Metric>

  34. Output– pythian_mysql_load.sh • MySQL server up……………exit 0 em_result=Up|10 • MySQL server down………...exit 0 em_result=Down • Metric collection error………exit 1 em_error=<error message>

  35. Demo MySQL status script • DEMO

  36. Response Metric • Availability monitoring • Must be of type TABLE • Must contain column Status • Must have critical threshold defined in default collection properties

  37. Property Scope • GLOBAL • Target type metadata • SYSTEMGLOBAL • emd.properties • INSTANCE • Instance properties – targets.xml • ENV • Environment variables

  38. Cumulative data collection <TableDescriptor> <ColumnDescriptor NAME="threads_connected“ TYPE="NUMBER“ /> <ColumnDescriptor NAME="threads_running” TYPE="NUMBER“ /> <ColumnDescriptor NAME="connections" TYPE="NUMBER“ TRANSIENT="TRUE" /> <ColumnDescriptor NAME="connections_ps“ TYPE="NUMBER“ COMPUTE_EXPR= “(connections - _connections) / __interval“/> </TableDescriptor>

  39. Cumulative data collection • Two collection points • em_result=9|3|10498 • em_result=6|4|11265 • Interval – 5 minutes • (11265 - 10498) / 300 = 2.6 /sec

  40. Key columns <TableDescriptor> <ColumnDescriptorNAME="action“TYPE="STRING“ IS_KEY="TRUE“ /> <ColumnDescriptorNAME="proccount“TYPE="NUMBER“ /> </TableDescriptor> +=============+===============================+ |action |proccount | |(Action )|(Number of processes in action)| |type = STRING|type = NUMBER | +=============+===============================+ | Query | 1 | +-------------+-------------------------------+ | Sleep | 2 | +-------------+-------------------------------+

  41. Metadata Validation • ILint • Static validation • Data collection • Metric Browser • Verify data collection in Oracle Agent environment

  42. ILint validation • XML syntax • Conformance to DTD • XML file must contain DOCTYPE line • Additional validation based on heuristical rules

  43. ILint syntax • Help – emctl ilint $ emctl ilint \ -m $AGENT_HOME/sysman/admin/metadata/pythian_mysql.xml $ emctl ilint \ –m $AGENT_HOME/sysman/admin/metadata/pythian_mysql.xml \ -i /opt/oracle/pythian_targets.xml \ -t Pythian-MySQL \ –r Response

  44. ILint collection validation • Sample targets.xml with target instance /opt/oracle/pythian_targets.xml <Targets> <Target TYPE=“pythian_mysql“ NAME=“Pythian-MySQL"> <Property NAME="host" VALUE=“gc.pythian.com"/> <Property NAME="user" VALUE="root"/> </Target> </Targets>

  45. ILint collection validation Validating Target = "Pythian-MySQL" of Type = "pythian_mysql" =============================================================================== Target = "Pythian-MySQL" has the following 2 Properties =============================================================================== Property: host --> Value: gc.pythian.com (INSTANCE) Property: user --> Value: root (INSTANCE) =============================================================================== Executing Metric = "Response" =============================================================================== Number of Rows = 1 +=============+====================+ |Status |response_time | | |(Response time (ms))| |type = STRING|type = NUMBER | +=============+====================+ | Up | 60 | +-------------+--------------------+ METRIC=Response, TARGET=(pythian_mysql,Pythian-MySQL)

  46. Demo Ilint validation • DEMO

  47. Metric Browser $AGENT_HOME/sysman/config/emd.properties enableMetricBrowser=true $ emctl stop agent $ emctl start agent Find - EMD_URL=http://<host>:<port>/emd/main/ Use - http://<host>:<port>/emd/browser/main Register: $ emctl config agent addtargets \ /opt/oracle/pythian_targets.xml

  48. Demo Metric Browser • DEMO

  49. Default Collection • Default collection intervals • Default warning and critical thresholds • $AGENT_HOME/sysman/admin/default_collection <?xml version="1.0" ?> <!DOCTYPE TargetCollection SYSTEM "../dtds/TargetCollection.dtd"> <TargetCollection TYPE=“pythian_mysql"> <CollectionItem> … </CollectionItem> <CollectionItem> … </CollectionItem> <CollectionItem> … </CollectionItem> </TargetCollection>

  50. Default Collection <CollectionItem NAME="Response“ UPLOAD="YES“ UPLOAD_ON_FETCH="TRUE"> <Schedule> <IntervalSchedule INTERVAL="5" TIME_UNIT="Min“/> </Schedule> <Condition COLUMN_NAME="Status" CRITICAL="Down“ OPERATOR="CONTAINS“ /> <Condition COLUMN_NAME="response_time“ WARNING="100" CRITICAL="300“ OPERATOR="GT" OCCURRENCES="2“ /> </CollectionItem> $ emctl ilint \ –m $AGENT_HOME/sysman/admin/metadata/pythian_mysql.xml \ -c …/admin/default_collection/pythian_mysql.xml

More Related