1 / 59

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

Extending Oracle 10g Grid Control New Metrics, Targets and Plug - ins. Alex Gorbachev The Pythian Group 2006 November. http://www.pythian.com/documents/UKOUG06-Extending10gGC.ppt http://www.pythian.com/documents/UKOUG06-Extending10gGC.zip. Who am I?. Senior Oracle DBA at The Pythian Group

livana
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 Alex Gorbachev The Pythian Group 2006 November http://www.pythian.com/documents/UKOUG06-Extending10gGC.ppt http://www.pythian.com/documents/UKOUG06-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 software • Software and hardware vendors • Cut costs – avoid expensive packs • Write once – use many times

  4. Pythian Real Life Example • Pythian is providing full remote DBA service and has monitoring tool “Avail” • State of art monitoring tool built by DBA’s for themselves • Years of development and contributions of many DBA’s • More than just Oracle monitoring • Two old customers using Avail • They don’t want to loose proven monitoring • Another customer has invested a lot into OEM but want to leverage our tool • Solution – integrate Avail into OEM

  5. Agenda • User Defined Metrics (UDM) • New target types • Example – MySQL • Management Plug-ins • Advanced Topics • Reports

  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. 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

  9. 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

  10. 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

  11. 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

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

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

  14. 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 • Add instances of new target type

  15. 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

  16. 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>

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

  18. 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

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

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

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

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

  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>

  24. <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

  25. <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>

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

  27. <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>

  28. <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/pythisn_mysql/ pythian_mysql_load.sh %myhost% %myport% %myuser% %mypwd% </Property> </QueryDescriptor> </Metric>

  29. 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>

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

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

  32. 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>

  33. 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

  34. 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 | +-------------+-------------------------------+

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

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

  37. 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

  38. 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>

  39. 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)

  40. 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

  41. 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>

  42. 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

  43. Why Plug-ins? • Central management and deployment • Distribute all required files to agents • Packaging tool for vendors • System reports • Customized home page • Setup and cleanup procedures • Custom storage tables

  44. Configure EM CLI Client • EM Command-Line Interface • Download $OMS_HOME/sysman/jlib/emclikit.jar • https://gc.oracloid.com:1159/em/console/emcli/download • Set environment for JDK 1.4.1 or above $ export JAVA_HOME=… $ export PATH=$JAVA_HOME/bin:$PATH • Install client java -jar emclikit.jar client \ -install_dir=/opt/oracle/emcli

  45. Management Plug-in Archive $ export ADM=$AGENT_HOME/sysman/admin $ export EMX=$ADM/scripts/emx $ emcli add_mp_to_mpa \ -mpa="`pwd`/pythian_mysql.jar" \ -mp_version="0.1" \ -ttd="$ADM/metadata/pythian_mysql.xml" \ -dc="$ADM/default_collection/pythian_mysql.xml" \ -file="MONITORING_SCRIPT:$EMX/pythian_mysql_response.sh" \ -file="MONITORING_SCRIPT:$EMX/pythian_mysql_load.sh" \ -file="MONITORING_SCRIPT:$EMX/pythian_mysql_procaggr.sh" \ -func_desc="MySQL Plug-in (The Pythian Group)" \ -req_desc="mysql executable in PATH" \ -oms_version="10.2.0.1"

  46. Deployment - Import • Import MPA using EM web-based GUI • Set Agent’s preferred credentials • Setup->Management Plug-ins->Import • Enter path • Click “List Archive” • Mark plug-in • OK

  47. Reports • MPA allows adding SYSTEM reports • Can’t be deleted • Can be placed on target’s home page

  48. How to Add Reports to MP • Design and develop using GUI • SQL Statements • Appearance • Using Information Publisher API • Report definition file with PL/SQL block • MGMT_IP and MGMT_MP_HOMEPAGE packages • So far no wizard to convert GUI => IP API

  49. Example Report

  50. Report Definition in GUI

More Related