Another way to monitor Oracle

[vjunior1981]

Don't worry...

No problem man. I can wait. Sorry if I'm boring you.

[Mich]

When I run the file:
canes2:/etc/zabbix/externalscripts # ./check_ora_cron.sh
Sending failed. Use option -vv for more detailed output.
/usr/local/sbin/zabbix_sender -z 10.10.100.139 -p 10051 -s comrtst -k audit -o

If I don't put the -r parameter it works fine for me, but I don't get any information in the zabbix screen

Hi, I have exactly the same problem.

Zabbix sender help says:
-o --value <Key value> Specify value of the key

Your sendtrap script sends $MYMSG as key value:
$ZBX_SENDER -z $ZBX_SERVER -p $ZBX_PORT -s $MYHOST -k $MYKEY -o "$MYMSG";

And it is definied as third parameter given to sendtrap:
MYMSG="$3"

Correct me if I'm wrong, but I can't see third parameter in check_ora_cron.sh:
./check_ora_sendtrap.sh -i $host -q ${query%.sql} &

Am I mistaken or you just forgot about sending query output?

[dalle]

Hi, I have exactly the same problem.

Zabbix sender help says:
-o --value <Key value> Specify value of the key

Your sendtrap script sends $MYMSG as key value:
$ZBX_SENDER -z $ZBX_SERVER -p $ZBX_PORT -s $MYHOST -k $MYKEY -o "$MYMSG";

And it is definied as third parameter given to sendtrap:
MYMSG="$3"

Correct me if I'm wrong, but I can't see third parameter in check_ora_cron.sh:
./check_ora_sendtrap.sh -i $host -q ${query%.sql} &

Am I mistaken or you just forgot about sending query output?

the parameter $host is provided by the function :
gethosts () {
cd /etc/zabbix/externalscripts/check_ora
cat credentials | grep -v '^#' | cut -d';' -f 1
}

and in particular
HOSTS=$(gethosts)

so HOSTS contain a list of values, and inside for.. do cicle $host contain every values of $HOSTS

for host in $HOSTS; do

for query the method is the same:

getqueries () {
cd /etc/zabbix/externalscripts/check_ora
ls *.sql
}

QUERIES=$(getqueries)
in the begin of for.. do for every values of $query and of $host execute:

./check_ora_sendtrap.sh -r -i $host -q ${query%.sql} &

if you run
sh -x check_ora_cron.sh
you will see the list of values contained inside HOSTS and the same for the values inside QUERIES

for me is :
HOSTS='DGDB0101
....
ZBH02'

QUERIES='audit.sql
...
waits_sqlnet.sql'
+ cd /etc/zabbix/externalscripts
+ for host in '$HOSTS'
+ for query in '$QUERIES'
+ ./check_ora_sendtrap.sh -r -i DGDB0101 -q audit
+ sleep 1

and in the end run:
/check_ora_sendtrap.sh -r -i DGDB0101 -q audit

so if you want debug check_ora_sendtrap.sh :
sh -x ./check_ora_sendtrap.sh -r -i DGDB0101 -q audit

you will see the output of the script step by step.

the last lines for me are:

+ /usr/local/sbin/zabbix_sender -z 10.11.77.93 -p 10051 -s DGDB0101 -k audit -o none
Info from server: "Processed 1 Failed 0 Total 1 Seconds spent 0.001992"
sent: 1; skipped: 0; total: 1


this mean that the trap was sent correctly
"Processed 1 Failed 0 Total 1 Seconds spent 0.001992"

[Mich]

So, I was definitely mistaken

Thank you for detailed explanation.

Well, in my and vjunior1981's case it is not working. May be I will give a closer look to it later.

Now I'm focusing on your first solution - via external scripts from server.

Let me know if anyone will find something about this "Sending failed."

[dalle]

So, I was definitely mistaken

Thank you for detailed explanation.

Well, in my and vjunior1981's case it is not working. May be I will give a closer look to it later.

Now I'm focusing on your first solution - via external scripts from server.

Let me know if anyone will find something about this "Sending failed."

no problem i'm happy that my explanation was clear
if i can help you let me know

[dalle]

released on sourceforge

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]

Thank you !

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]

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]

Tablespace monitoring

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]

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]

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]

Monitoring tablespaces

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]

version 1.1 as been released

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]

version 1.1 as been released

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