Cacti (home)ForumsDocumentation

Oracle Graphs

type:
user_template_graph
usertemplate:
oracle
author:
jerrison
description:
Oracle - DB Graphs
Cacti:
0.8.7e
homepage:
http://forums.cacti.net/viewtopic.php?p=193684
date:
2010-07-07
includes:
yes
templates:
oracle,
subagent,
database

Download

Installation

Setting up SNMP for Oracle 10g under W2K3

Basically, all we need to do is get the “emsubagent” running. The subagent acts like a proxy between SNMP- and DB-queries and is the only
instance that will let us query the following OID-Tree: .1.3.6.1.4.1.111.4 (this is where the main goodies are located).

01 Software Requirements

Beforehand you´ll need the agent software:

Download the Win32_Grid_Control_agent_download_10_2_0_5_0.zip file from the Oracle Technology Network (OTN):
http://www.oracle.com/technology/software/products/oem/htdocs/agentsoft.html
You need an webiste-account for that, it´s free though!
Once you downloaded the software and installed it, you can just copy and paste the folder contents to any Oracle-DB server
and adjust one file (targets.xml), more on that under point 02. All we really need is the emsubagent.exe.

It doesn´t matter if your Oracle-DB is 10.1! I could not get the “Win32_Grid_Control_agent_only_10_1_0_6_0.” to work,
Oracle messed the whole subagent-business seriously up.

02 Oracle-SNMP - Setting it up

The following is a mixture of Oracle-guides, forum searches and simple try and error of how i got the emsubagent working. There is almost
nothing out there concerning the subagent and problems started as early as “where the heck is emsubagent.exe” (which was left out in some agent-installation packs).

Setup-Guides from Oracle (you can check at http://download.oracle.com/docs/cd/E11857_01/em.111/b16244/chap2.htm)

  • Configure Windows-SNMP-service to listen on port 1161 (in the “c:\%systemroot%\system32\drivers\etc\services”-file)
  • Configure SNMP settings in the SNMP-Service (community, access), choose a community to your liking, i´ll just call it “public1”
  • Configure community (public1) in the master.cfg file (“oracle-folder”/NETWORK/ADMIN)
  • Start Windows-SNMP-Service
  • Start Oracle MasterAgent-SNMP-Service (it´s called sth. like ”…..SNMPPeerMasterAgent” at the end)
  • Start the agent-installation 10.2.05 or the root folder from a previous agent installation, i´ll call the root folder from the agent installation “OracleHomes” from now on
  • Configure the Targets.xml file under \OracleHomes\agent10g\sysman\emd → put in the correct servername, DB-instance-name and username+passwort for the SNMP-user
    For username and password: this is the DB-User that connects to the database to answer an SNMP-Query. The default is:


<Property NAME=“UserName” VALUE=“DBSNMP” ENCRYPTED=“false”/>

  <Property NAME="password" VALUE="DBSNMP" ENCRYPTED="false"/>

You can choose any password you like, but we need to keep in mind to change the password of the user DBSNMP accordingly later on.
To give you an example targets.xml:

<Targets>
<Target TYPE="oracle_database" NAME="ORACLE_someserver.org">
  <Property NAME="OracleHome" VALUE="E:\oracle\product\10.1.0\Db_1"/>
  <Property NAME="UserName" VALUE="DBSNMP" ENCRYPTED="false"/>
  <Property NAME="password" VALUE="DBSNMP" ENCRYPTED="false"/>
  <Property NAME="MachineName" VALUE="someserver.org"/>
  <Property NAME="Port" VALUE="1521"/>
  <Property NAME="SID" VALUE="ORACLE"/>
  <Property NAME="ServiceName" VALUE="oracle"/>
</Target>
</Targets>


In some instances the unencrypted values were encrypted at some point so the password was not clear text. I really do not know what
kind of mechanism works the magic, you guess is as good as mine (oh, and i did not spend a lot of time figuring that one out, to be honest).

  • Now alter the DBSNMP-User password in your DB: SQL> ALTER USER DBSNMP IDENTIFIED BY DBSNMP; (last part is the password, i used the default)
    To get an SQL prompt, be a member of the local group called “ORA_DBA” and execute: “sqlplus / as sysdba” (DB-Admins know that, i didn´t, might save someone some time).
  • To unlock the SNMP-Account: SQL> ALTER USER username ACCOUNT UNLOCK; (if it is locked)
  • Now start the subganet via shell and don´t close your shell-window since the subagent starts not as a service. There´s definetly a way to start the agent smoother, be my guest:
    tell us how, create a service, etc. (means: all input is very welcome!).
    Starting the subagent ”\OracleHomes\agent10g\BIN\emctl.bat start subagent” → if all went well, the subagent echoes back “Running sub abent …” and this is where the shell-window
    is best to be left alone.
  • If the subagent does not start:
    • make sure the “master”-agent is running → it´s the ”…..SNMPPeerMasterAgent”-Service
    • check subagent-log files → \OracleHomes\agent10g\sysman\log
    • sometimes the DBSNMP account is locked or doesnt even exist, there´s a sql-file in your oracle installation folder called “catsnmp.sql” (under \RDBMS\ADMIN), import that query with
      sqlplus
  • If the subagents runs succesfully, congratz, big step forward! Now it´s time to SNMPee the bastard from your cacti-server: snmpwalk -c public1 -v 1 10.23.15.100 .1.3.6.1.4.1.111.4
  • You should get a lot of results from the DB just not the host values anymore
  • You still might want to monitor the DB host and you can: just remember we needed to change the SNMP-port to 1161, adjust your device in cacti accordingly
    • on a side-note: some scripts like the windows-services template hardcoded port 161! So if anyone needs more “flexible” scripts, i changed mine to work with the device settings

Now that it´s time to move to greener pastures: leave Oracle-DB alone and on to cacti…

03 Cacti setup

Before you start to import the templates there are a few points i should mention that are specific to my cacti-setup and polling Oracle-DB:

  • I use a 1 miunte polling time (hence additional RRAs)
  • I use cacti 0.8.7e on a centos 5.x linux server in a VM with a 14 GB RAM-Drive (tmpfs)
  • This is/was my first “major” template project, there are bumps and propably smoother ways to handle things (tell me, pls!)
  • For 2 specific set of values i poll from the Oracle-DB i created two big Datasource with 25 and 9 DataSourceItems:
    • This was done to avoid multiple polls and redundant DS since a lot of graphs have just different CDEFs with the same set of values.
    • This data source is “Oracle - oraDbSysTable All Fields” and the same is done with “Oracle - rdbmsSrvInfoTable”.
    • As a result of this, i have to manually assign the “Supplemental Graph Template Data” in the Graphs i create. We´ll come back to that later on.
  • I only worked with SNMP-queries and left script-server and the likes untouched. Somewhere i read that SNMP-queries perform a lot faster which is key
    to a 1-Minute-polling-intervall-cacti (might be mistaken on that one, then my argument would be: it was easier than getting back into PHP).

03.1 Template Import and host-queries

  • Put the attached SNMP-query-scripts in /resource/snmp_queries
  • Import the templates at your own risk → they work good for me and i imported/exported them several times
  • Create a new device for your Oracle-DB-Server and add the Oracle-Queries under “Associated Data Queries”
  • Notice for “Oracle - rdbmsSrvInfoTable”, “Oracle - DB oraDbSysTable” and “Oracle - DB oraDbConfigTable” there is a weird index. In fact, the MIB-Table doesn´t seem to have any index i could poll from
    so you have to set this index after creation manually:
    • Edit your DataSource for “Oracle - rdbmsSrvInfoTable”, “Oracle - DB oraDbSysTable” and “Oracle - DB oraDbConfigTable” and put in the right instance number for you DB (usually “1”).
    • If you have multiple Instances you might need to create an additional DataSource and put in the right instance-number again.
    • (This would be the part where one can clearly see that this is not a smooth solution and any improvment adivse is very welcome!)
  • Now you have a DataSource where values are being stored in what we need next are the graphs pointing to this Datasource:
    • Go to graph-management, select your host and add the Oracle-Graphs you need then manually assign the “Supplemental Graph Template Data” from the DataSource
      we created before. The main idea is still to use just one poll/query on the Oracel-DB-server for the creation of 23 graphs.
  • In short:
    • “Oracle - DB oraDbConfigTable”: Weird Index, need to be set manually to the index of your Oracle-DB → These are all very static values but if someone “accidently” changes parameters you´ll know
      when and what was changed (if you ever heard “i didn´t change anything” you´ll know what i this is for)
    • “Oracle - rdbmsSrvInfoTable”: Weird Index, need to be set manually to the index of your Oracle-DB
    • “Oracle - DB oraDbSysTable”: Weird Index, need to be set manually to the index of your Oracle-DB
    • “Oracle - DB Data File Usage”: Select the DB-Files you need to monitor, index works here
    • “Oracle - DBLibraryCache Table”: Select the Library-Cache you need to monitor, index works here
      • Note: I got no results for JAVA DATA, JAVA RESOURCE, JAVA SOURCE, OBJECT, PIPE - I am not a DB-Admin, don´t ask me what a Library-Cache means, i just monitor :).
    • The right Index for your instance can be found through your snmpwalk, usually the second-last OID-number shows the index of your instance. Like in:
      enterprises.111.4.1.2.1.2.1.5 my index number for this instance would be “1”.
  • The Oracle-subagent can be “sluggish” to put it nicely, i set my SNMP-Timout for for Oracle-DB-device at 5000ms. Does not hurt the perfomance of the poller since cacti can poll
    with multiple processes.

Screenshots

These are just 3 examples of 23+ graphs that are available!








Personal Tools