Another way to monitor Oracle

[dalle]

Hi to everyone i've see that this project involve some people and so i've made a sourceforge project if someone want collaborate or has ideas or something please send me a feedback
https://sourceforge.net/projects/checkora

[rde-actinux]

I'm currently testing your programs with Zabbix 1.8 configured on RedHat cluster.

In my case I'm focused on tablespace values and that does not work for me

As you said I just need to configure my zabbix user on Oracle, configure my SQL+ client and check_ora.

I put in crontab check_ora_cron.sh
*/5 * * * * /opt/zabbix/check_ora/check_ora_cron.sh > /opt/zabbix/tmp/zabbix_sender.log

I can find in my output log:
/opt/zabbix/bin/zabbix_sender -z 10.1.128.216 -p 10051 -s EMAUL -k tbl_space -o

But when I try to read value in zabbix last values:
Waits Controlfile I/O 16 Dec 2009 15:03:51 3 ms waits/s - Graph
Tablespaces 16 Dec 2009 15:03:36 none - History
SGA shared pool 16 Dec 2009 15:03:31 109.12 M - Graph

Have you got specials tips to give me to get tablespaces values ?

Thanks a lot again for your excellent job !

[dalle]

Originally Posted by rde-actinux

I'm currently testing your programs with Zabbix 1.8 configured on RedHat cluster.

In my case I'm focused on tablespace values and that does not work for me

As you said I just need to configure my zabbix user on Oracle, configure my SQL+ client and check_ora.

I put in crontab check_ora_cron.sh
*/5 * * * * /opt/zabbix/check_ora/check_ora_cron.sh > /opt/zabbix/tmp/zabbix_sender.log

I can find in my output log:
/opt/zabbix/bin/zabbix_sender -z 10.1.128.216 -p 10051 -s EMAUL -k tbl_space -o

But when I try to read value in zabbix last values:
Waits Controlfile I/O 16 Dec 2009 15:03:51 3 ms waits/s - Graph
Tablespaces 16 Dec 2009 15:03:36 none - History
SGA shared pool 16 Dec 2009 15:03:31 109.12 M - Graph

Have you got specials tips to give me to get tablespaces values ?

Thanks a lot again for your excellent job !

i've got a looong looong time relationship with tablespace... and the resume is this: with autoextend =yes and when tablespace is composed from multiple datafile is completely an hell write a query that return a sorta of real value, i never finded a way.. sorry

[rde-actinux]

Ok, I worked a bit on my tablespace monitoring.

I wrote a request for getting my table space percent usage

ALTER SESSION SET NLS_NUMERIC_CHARACTERS=".,";

SELECT 
  a.tablespace_name AS name,
  ROUND(100* b.bytes / a.bytes,1) AS percent_free
FROM 
 (SELECT tablespace_name, sum(bytes) bytes from SYS.DBA_DATA_FILES 
   GROUP BY tablespace_name) a, 
 (SELECT tablespace_name, sum(bytes) bytes from SYS.DBA_FREE_SPACE 
   GROUP BY tablespace_name) b 
WHERE a.tablespace_name = b.tablespace_name;

On zabbix I can get that values (for example)

2009.Dec.17 11:57:20 	

SYSTEM                                 20.1
INDEX                                  66.7
USERS                                  19.1
SYSAUX                                 23.4
UNDOTBS1                               97.6

What I need is to set triggers on this results when value while raise 80. I can't do it actually.

I think I can do it passing tablespace name for each database but it will take to long to do (about 50 databases, with about 5 tablespace for each).

My question is easy; is it possible to parse this results in zabbix to extract and insert in Zabbix db only the name of the tablespace and % of free space ?

Thank you for your answer !

[dalle]

Originally Posted by rde-actinux

Ok, I worked a bit on my tablespace monitoring.

I wrote a request for getting my table space percent usage

ALTER SESSION SET NLS_NUMERIC_CHARACTERS=".,";

SELECT 
  a.tablespace_name AS name,
  ROUND(100* b.bytes / a.bytes,1) AS percent_free
FROM 
 (SELECT tablespace_name, sum(bytes) bytes from SYS.DBA_DATA_FILES 
   GROUP BY tablespace_name) a, 
 (SELECT tablespace_name, sum(bytes) bytes from SYS.DBA_FREE_SPACE 
   GROUP BY tablespace_name) b 
WHERE a.tablespace_name = b.tablespace_name;

For what i've seen is impossible parse this kind of output inside zabbix, is more easy do something like this:
select * from (
SELECT
a.tablespace_name AS name,
ROUND(100* b.bytes / a.bytes,1) AS percent_free
FROM
(SELECT tablespace_name, sum(bytes) bytes from SYS.DBA_DATA_FILES
GROUP BY tablespace_name) a,
(SELECT tablespace_name, sum(bytes) bytes from SYS.DBA_FREE_SPACE
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
)
where percent_free > 80


and simply do a trigger that become red when there is a sorta of output,
so you are going to see always green when you don't have nothing that has more than 80 % space used.
Consider that "system" tablespace is often fully used.
I hope this will help you.

Consider also this:
if USERS tblspace is made by 2 datafiles and ONE of this has autoextend this query return a wrong value, because don't consider autoextend and maxsize of datafiles. (this is what i mean for trouble with tablespaces query).

[dalle]

Originally Posted by gypeWaple

Hope your studies help you do better - for the Job you state you will get.

Whos paying your way Howie ?

Just adding up your comments in your posts - thats all, not really interested - but some things dont add up.

ok no more something of funny...
btw i really don't know how monitor tablespace inside zabbix a graphic visualization of used space etc.. should be useful but i don't know how do this, if you have any kind of idea let me know.

[rde-actinux]

I partially solved my problem.

First of all, I created a request which admit one argument to get % free space on tablespace :

SELECT 
  /* for debug purpose : 
      a.tablespace_name AS name, */
  ROUND(100* b.bytes / a.bytes,1) AS percent_free
FROM 
 (SELECT tablespace_name, sum(bytes) bytes from SYS.DBA_DATA_FILES 
   GROUP BY tablespace_name) a, 
 (SELECT tablespace_name, sum(bytes) bytes from SYS.DBA_FREE_SPACE 
   GROUP BY tablespace_name) b 
WHERE a.tablespace_name = b.tablespace_name
/* name of tablespace in argument */
AND a.tablespace_name = '&1';

exit;

Then I wrote a small script to launch sql query with args.

#!/bin/sh
# Oracle environment variables
export PATH=$PATH:/home/oracle/bin:/home/oracle/oracle/product/10.2.0/db_1/bin
export ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1

SQLPLUS=/home/oracle/oracle/product/10.2.0/db_1/bin/sqlplus

IP=
INSTANCE=
QUERY=
ARGS=

# Display help
printhelp() {
	echo "query_oracle.sh - an Oracle query for zabbix"
	echo "Usage: query_oracle.sh hostname instance query [parameters] [help]"
	echo "       ip          ip adress of oracle server"
	echo "       instance    to retrieve credentials from credentials file (must be the same with tnsnames.ora"
	echo "       query       execute a query and return just only the output"
	echo "       parameters  for extrat arguments"
}

execquery() {
	# `echo "Running $SQLPLUS -S $INSTANCE $QUERY $ARGS query " >> /opt/zabbix1/var/log/zabbix/zabbix_server.log`
	RES=$($SQLPLUS -S $INSTANCE $QUERY $ARGS)
	RET=$?
	if [ $RET -ne 0 ]
	then
		echo "Error while querying $INSTANCE"
	fi
	echo $RES
}

# testing args
if [ $# -lt 2 ]
then
	printhelp
	exit 1
fi

# better way to "shift"
IP=$1
INSTANCE=zabbix/zabbix@$2
QUERY=@/opt/zabbix1/etc/zabbix/externalscripts/$3
ARGS=$4 $5 $6 $7 $8 $9 # need improvment

execquery

I add group oracle to my zabbix account and add right read on my tnsnames.ora.

In zabbix, I add an Externel check using this script so I can get values for each Oracle instances.

What's wrong with it :

• I have to enter manually instance name (tnsnames.ora)
• I have to enter manually tablespaces name

An upgrade could be to monitor each database as zabbix host:

• Create each database in zabbix
• Modify tnsnames.ora to set instance name with database name in zabbix.

I'm quiet busy so in next step I'll try all that improvments.

I have no good solutions for tablespaces names

(Sorry for my english...)

[dalle]

i'm replying in late, sorry, i'm looking on your request.
If you don't want use tnsnames.ora there is a note wrote by asktom on his forum: http://asktom.oracle.com/pls/asktom/...45033135081903
i'm trying to find also a client that don't use tnsnames.ora this can be another way to solve your problem.

Sorry but i did not undestand the other two upgrade, actually i'm using already dbname as "host" in side zabbix.

[dalle]

relase a minor version 1.1 more info here:
http://www.smartmarmot.com/

downloadable from here:
https://sourceforge.net/projects/checkora/files/
pratically i've just added an external configuration file, some comment anc cleaned a few the code.
This release is not jet tested on zabbix server 1.8.x because i didn't yet upgraded my servers.
For each trouble or question please ask to me.
see ya

[dalle]

relase a minor version 1.1 more info here:
http://www.smartmarmot.com/
downloadable from here:
https://sourceforge.net/projects/checkora/files/
pratically i've just added an external configuration file, some comment anc cleaned a few the code.
This release is not jet tested on zabbix server 1.8.x because i didn't yet upgraded my servers.
For each trouble or question please ask to me.
see ya