Check if an Oracle table is empty


#1

Hi,

As non Oracle man I’m trying to setup an Oracle check.

I have found quite some info and tested a lot, but I can’t seem to get it right.

Here’s the setup:

2 Oracle servers (Windows), with a database per server.

I want to check if the error table is empty. if it isn’t I want to trigger an alert, so the DB admin can act.

This is the sql statement I need to run: select count(*) from system.def$_aqerror;

I have the connectstring info. On the CentOS (Nagios) server sqlplus is installed. This all works if I connect from the command prompt with sqlplus. I then get a result of the number of records.

Now, I want to do the same with Nagios, hence with a plugin. I found and tested quite a few, but the results are not there. The one that comes most close is check_db_record_count. Only the help file on nagiosexchange and in the script are incomplete. Details about 2 variables (holiday_table and holiday_criteria) are missing, so I don’t know the syntax. if I comment out these variables I get an error “[CRITICAL] unable to locate REMEDIAL_ACTION in
query_file” This REMEDIAL_ACTION isn’t described anywhere.

I want to do something like this:

[root libexec]# ./check_oracle user/password@//server/database “select count(*) from system.def$_aqerror;” c=1

Which would mean that I get an “0” if it’s Ok, or 1 or more means Critical.

Any help would be appreciated!

Cheers,

Michael


#2

Hi,
you can do this with check_oracle_health. Because the SQL-statement contains a $, it’s best to encode it, so you don’t have to care for quoting.
echo ‘select count(*) from system.def$_aqerror’ | check_oracle_health --mode encode
select%20count%28%2A%29%20from%20system%2Edef%24%5Faqerror

check_oracle_health --connect /@sid --mode sql --name select%20count%28%2A%29%20from%20system%2Edef%24%5Faqerror --warning 0 --critical 0 --name2 errors --method sqlplus
should give you for example:
CRITICAL - errors: 15 | errors=15;0;0

–name2 is a beautifier for the output. without it you would see your sql statement in the output and the perfdata.

You can find the plugin here: consol.de/opensource/nagios/ … cle-health
Sorry, it’s in german. I hadn’t the time to make a translation. But the command line parameters and the examples should be self-explaining.

Gerhard