Informatics

monitoring Oracle 10g databases on HP-UX with cacti

Oracle is a big beast, and taming its plentiful characteristics is an almost impossible task.

In fact I think that enterprises like this one use complexity as a mean for controlling and tying their clients – but that’s a theme for another post.

Can Oracle 10g be monitored with cacti?

Fortunately enough, Oracle has provided all along during these years an snmp insight of their databases. So as long as it’s available, the answer is “yes”.

But wait… Why would anybody try to use cacti to monitor an Oracle DB? One can always obtain graphs from the Enterprise Manager of each of the databases. Ok. But, you know, it’s soooo strange and hard to manage, that I won’t say anything more about it.

Obviously, installing this snmp feature isn’t easy at all.

Also, there’s a set of templates for getting this data into cacti. Unfortunately, it has some oddities I couldn’t manage at all, so I’ve modified them so as to be controlled by an inexperienced user once oracle’s snmp is active. Here it is:

I’ve created a “zip” file because with Oracle there’s always an unzip command at “$ORACLE_HOME/bin/unzip”, and a tgz could be difficult to manage for Windows users.

So, what has to be done in order to monitor Oracle 10g with cacti?

First of all, one has to install a Management Agent with a snmp subagent. Then, just import the oracle templates to cacti, and select the graphs.


1. Install a Management Agent with an snmp subagent

You do probably already have a Management Agent installed on your oracle database servers. I’d like to use any of them, but I wasn’t able to deduce what steps to follow to install just the subagent part they were missing: the documentation about this matter states always that a new fresh installation is needed. It’s a pity, as it is… 2 GiB in size! So first of all, make sure you have between 2 and 3 GiB of free space on one of your servers.

One can probably deinstall part of the Management Agent parts after installation (2 GiB!? really!?), but I haven’t investigate this. Any hint on this will be appreciated.

In my case Oracle is using RAC, so it’s a cluster – I made the installation in one of the nodes. Databases are the same on any of the servers, but instances are different on any of them. I’m not very sure if statistics are global or particular just for the instance the agent is installed on. I suppose the latter, as Oracle philosophy orbits around the concept of “database instance”, not just “database”. So maybe you’d like to install the agent on all of your nodes and create graphics for all of them.

Where to find the Management Agent?: here’s the Oracle page:

Select your exact Oracle DB version and OS platform, paying attention to the 32 or 64 bits versions.

The downloaded file will be between 500 MiB and 1 GiB in size.

Now, the Oracle documentation can be of some help (or not):

On the latter doc, there’s a reference to the need of an Oracle Management Service (OMS) to communicate with the Enterprise Manager (EM) Grid Control Console. I’ve tried to avoid this step… Sure you do probably have an EM (maybe shutdown) and also an OMS… but EM is not our goal here: it is just the snmp service.

btw, you can check if you have installed those services with these commands ($ORACLE_HOME must be previously set):

$ $ORACLE_HOME/bin/emctl status oms

$ $ORACLE_HOME/bin/emctl status dbconsole

I’ve also supposed by now, that the database user DBSNMP is correctly configured:

Anyway, this way we shall never proceed with the installation!

That’s what Oracle would like… but here we go: there are some preliminary instructions for the downloaded agent:

[/home/oracle]$ mkdir $ORACLE_HOME/sysman/agent_download
[/home/oracle]$ $ORACLE_HOME/bin/unzip HPIA64_Grid_Control_agent_download_10_1_0_5_0.zip -d $ORACLE_HOME/sysman/agent_download
[/home/oracle]$ cd $ORACLE_HOME/sysman/agent_download/
$ ls -l
total 70
-rwxr-xr-x 1 oracle oinstall 15199 Sep 22 2006 agentDownload.hpia64.bak
-rw-r–r– 1 oracle oinstall 18235 Sep 22 2006 agent_download.rsp.bak
drwxr-xr-x 5 oracle oinstall 96 Sep 22 2006 hpia64
-rwxr-xr-x 1 oracle oinstall 1484 Sep 22 2006 instructions.txt
$ mv agent_download.rsp.bak agent_download.rsp
$ vi agent_download.rsp
$ mv agentDownload.hpia64.bak agentDownload.hpia64
$ vi agentDownload.hpia64

Ok. Now following steps 6 and 8 of the txt instructions, we have to correctly fill “s_OMSHost” and “s_OMSPort” in agent_download.rsp and agentDownload.hpia64. You can just put some values, but they shouldn’t be necessarily correct: we’ll deactivate this later.

Now be sure you are user oracle on your shell and that the ORACLE_HOME is correctly set. Here goes the Manager Agent installation (finally!):

$ hpia64/agent/runInstaller

It is an Oracle Universal Installer (OUI), so follow its guidance. You’ll be prompted for new identifier and installation home: use some descriptive string and a directory you should have previously created: some one sibling of your previous installations: if your database is “/oracle/DB/” for example, this could be “/oracle/agent”, jsut to maintain all the software easy to locate.

In the middle of the install as is usual with OUI, you’ll have to run s script as root. Just follow the instructions.

Now, the “$ORACLE_HOME/sysman/agent_download” where I put the unzipped bundle is no longer needed, as it is not the zip file. Yo can safely remove them.

From now on, the new $ORACLE_HOME will be the one used as home for the Management Agent installation. Be careful! So following the previous suggestion, it’d be:

$ export $ORACLE_HOME=”/oracle/agent”

Now, let’s deactivate the OMS configuration:

$ vi $ORACLE_HOME/sysman/config/emd.properties

Comment the line beginning with “REPOSITORY_URL”.

Now, the targets for monitoring must be configured:

$ vi $ORACLE_HOME/sysman/emd/targets.xml

Substitute the multiple lines containing “UserName” and “password” with your correct values on VALUE (Username must be dbsnmp), and put ENCRYPTED to “FALSE”: on agent run, they’ll be encrypted.

<Property NAME=”UserName” VALUE=”dbsnmp” ENCRYPTED=”FALSE”/>
<Property NAME=”password” VALUE=”yourdbsnmppsw” ENCRYPTED=”FALSE”/>

You will have as many group of these values as databases to monitor.

Now, make this link, as the subagent looks for a sqlnet.ora file, but we have an snmp_ro.ora:

$ ln -s $ORACLE_HOME/network/admin/snmp_ro.ora $ORACLE_HOME/network/admin/sqlnet.ora

Now stop and run the agent and (snmp) subagent:

$ $ORACLE_HOME/bin/emctl stop subagent
$ $ORACLE_HOME/bin/emctl stop agent
$ $ORACLE_HOME/bin/emctl start agent
$ $ORACLE_HOME/bin/emctl start subagent

As in HP-UX there’s by default an snmp server running, we shouldn’t have to configure it…

You can check from the server that hosts cacti that everything is running:

[cacti server/]$ snmpwalk -v 2c -c public -m ALL myoracleserver:161 .1.3.6.1.4.1.111 | head

SNMPv2-SMI::enterprises.111.4.1.1.1.1.1 = Counter32: …

Brilliant.

You can look for errors on all the files under “$ORACLE_HOME/sysman/log”: emsubagent.trc, emsubagent.nohup, emsubagent.log and the corresponding ones for the agent: emagent.trc, emagent.nohup, emagent.log


2. Configure your Oracle server on cacti

Ooooook. A long journey until here, wasn’t it?

As I mentioned at the beginning, I used this oracle bundle for cacti:

Unfortunately, it has some oddities I couldn’t manage at all, so I’ve modified it so as to be controlled by a any inexperienced user. Here it is:

The procedure for the installation of this Oracle bundle in cacti is as usual:

  1. Copy the five xml files under the “snmp_queries” folder to your snmp_queries’ cacti folder: <cacti_path>/resource/snmp_queries/
  2. from the admin web console of cacti, import (Import/Export > Import Templates) all the files under the “Templates” folder: five of them are “data queries” which established which data is retrieved and the rest are “graphic templates” which describe how to paint all the info. They’re 29 files, so have a little patience.

Now, you can create your cacti Device from which snmp data will be extracted and graphed:

  1. Go to “Management > Devices”, and click on “Add”.
  2. Important fields: “Hostname” , “Host Template” = “Generic SNMP-enabled Host”, “SNMP Version” = 2.

Now, on the new screen, under “Associated Data Queries”, select one by one your five “Oracle – *” queries, and click “Add”.

You can click on “Verbose query” and check that the snmp data returned make sense.

Now, click on the “Create Graphs for this Host” link, and select the graphs you want under the “Data Query [Oracle – DB Data File Usage]”, “Data Query [Oracle – DB oraDbConfigTable] “, and “Data Query [Oracle – DBLibraryCache Table]” sections.

Under the “Data Query [Oracle – DB oraDbSysTable]” and “Data Query [Oracle – rdbmsSrvInfoTable]” sections, you’ll see a dropdown control from which you can select your different graphs for these queries, one by one: then click on the checkbox(es) for your db instances, and click the “Create” button down on the page:

oracle-cacti-conf-1

And, that’s all! Here finish this step by step manual: you have your graphics! :

oracle-cacti-2 oracle-cacti-3

The graphs of the section “DB Data File Usage” (like the previous one, for an +ASM data file) tend to have a quite long data file path, which can be cut by cacti… This can be prevented changing the maximum field length for a data query on your cacti configuration, which by default is just “15”. Change it to for example “50” : the text will be centered, so the data file path will be shown complete:

  • Settings > Visual > Data Queries > Maximum Field Length

If you change this cacti configuration “after” creating these graphs, they won’t be automatically updated: you’ll have to go to each Data Source and Graph and just click “Save” so they get this new configuration.

oracle-cacti-5 oracle-cacti-6

Just one note: the “Oracle – DB Configuration Changes” graph is used as a “witness” of the db configuration. So any change happened would be reflected there as a variation on any of the values stored. That’s the reason why the graphic itself should remain totally stable.


Notes:

I made these changes to the original zip bundle:

  1. Now the db instance index selection is automatic, so the user does not have to fill any data at all. This were accomplished modifying the data query templates and associating all the graphs to there corresponding data query “mother”.
  2. That’s the reason of the drop-down controls which didn’t appear with the original bundle, on which these graphs where created as “Associated Graph Templates” added one by one to the Devices, and then filled up with additional data like Index values and others which btw I couldn’t manage to make work at all. Another consequence of this change is that each graph provokes an snmp query, so the server receives and send various udp packets per graph… The original bundle tried to avoid this making just one data query and then associating manually new graphs with it. I couldn’t replicate this configuration. Anyway, if this would be a requirement, it could be done by modifying the cacti db with some sql statements to change the rrd data source of the graph to that of the data query, which I’ve left filled with all the original data, so it can be used by any of its associated graphs.
  3. One template (Finished Transactions) was modified as it had an error which prevented the graphic from being correctly drawn.
  4. Also, template times are now 5 min between queries. But cacti always changes template values to the value of your instalment so no problem here.
Advertisements

2 thoughts on “monitoring Oracle 10g databases on HP-UX with cacti

  1. Pingback: monitoring MariaDB MaxScale with cacti | circulos meos

  2. Pingback: monitoring php-pfm threads with cacti (via snmp) | circulos meos

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s