Oracle

check_db

Write a Review

Description:

Remote oracle database check using JDBC drivers. Supports custom SQL queries and regular expression match. Provides similar funcationality as sitescope Db monitor.

Current Version

Last Release Date

May 28, 2009

Compatible With

Owner

Nagios Exchange

Copy Page Link Recommend Login to Add to Favorites Login to Claim
Project Files
Project Notes
This plugin can check almost every aspect of oracle database, written in java for portability. Compiled with JDK 1.5.0_06 Uses JSAP command line parser http://www.martiansoftware.com/jsap/ to compile : copy DbCheck.java JSAP-2.0a.jar in a directory, expand JSAP-2.0a.jar in the same directory using command jar xvf JSAP-2.0a.jar Use : javac -cp .:com/martiansoftware/jsap/JSAP.* DbCheck.java this should generate DbCheck.class file Setup Nagios Environment: Copy DbCheck.class, check_db to /usr/local/nagios/libexec create /usr/local/nagios/libexec/lib directory Copy JSAP-2.0a.jar, classes12.jar (Take from $ORACLE_HOME/jdbc/lib), sql files to /usr/local/nagios/libexec/lib directory Nagios plugin to check output of a sql query, matched with a regular expression. Currently supports numeric data checks only. --help Print help message -H Hostname/ip address of database server. -p Listener port, default is 1521. (default: 1521) -s Oracle database SID. -l Oracle database user to connect. -x Oracle database user password. -f Full path of sql file to be executed. -r PERL style Regular Expression to match in SQL output. Check value must be enclosed between (), should match only one field and must be enclosed between single quotes. Example 'SYSTEM.*,.*,(.*)' -w Warning value for matched expression. Normally warning < critical value. If your check want to alert when result < warning , set critical less than warning -c Critical value for matched expression. Should be more than Warning 1. If not check behaviour is reversed, see warning help text above. -L Label for matched regex value i.e FieldName. Usage: DbCheck --help -H -p -s -l -x -f -r -w -c -L
Reviews (3) Add a Review
Need more documentation
by cipher, May 31, 2012
i need to check the sessions used in a oracle DB and alert if it exceeds. i have compiled your plugin successfully.

what i want i am listing below can you tell me how i cud do the follow: with your plugin if possible


My Oracle DB server details

operating System: Solaris
database: oracle
IP: 192.168.1.2 (not real ip)


My Nagios Server details
OS : Ubuntu
i have configured nagios 3.x


i connect to my db server from my nagios as follow:


ssh -Y root@192.168.1.2
Password: xxxxxxxxx
[root@vboxdb /]# su - oracle


Sun Microsystems Inc. SunOS 5.10 Generic January 2005
[oracle@vboxdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 30 04:53:48 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production





what i want to do i will list below ;




SQL> select current_utilization, max_utilization, limit_value from v$resource_limit where resource_name =’sessions’;


CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
——————- ————— ————————- —————

386 404 1528


if the current utilization reaches 1500 alert critical, 1450 warning state. else normal.
can you tell me how i can use your plugin to do this.
Helpful?    
Report
Works with many changes, but no documentation
by Howard, March 31, 2012
Unlike the other reviewer (who apparently didn't read what little documentation there is since he/she tried to run a .java file at the command line which is nowhere suggested in the notes), I got this to work (kinda).

In later relases, you normally connect to an Oracle environment with a service_name, not a SID, so the old host:port:sid format no longer works. You need to pass host:port/service_name. I changed the .java file from the : to a /, recompiled, and I could conenct. However, the JSA file downloaded is a zip, not a jar, so that doesn't work, either. Easily fixed, but confusing if you aren't aware.

I also still don't know what the regular expression is supposed to do.

Also, the .sql file you run shouldn't be terminated with a semicolon, as you may be used to in oracle. The string is executed by JDBC dynamically, so you just pass "select blah from table", not "select blah from table;"

Lastly, the plugin must be protected (they all shoudl be, admittedly), as the SQL is just run "as is". At least it requires a query, so you can't accidentally run "truncate table big_table" :)

It just needs about two hours of work to take it over the goal line.

Below is where I ended up after all the changes I mentioned above...

[root@ADM-SHOWARD ~]# ./check_db -H 172.26.24.6 -p 1521 -s "ecomonline.expdev.local" -l system -x express -f mysql.sql -r % -w 1 -c 2 -L "who knows"
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:458)
at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:546)
at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:236)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
at java.sql.DriverManager.getConnection(DriverManager.java:620)
at java.sql.DriverManager.getConnection(DriverManager.java:200)
at DbCheck.testSS(DbCheck.java:156)
at DbCheck.main(DbCheck.java:140)
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

at oracle.net.ns.NSProtocol.connect(NSProtocol.java:395)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1102)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:320)
... 8 more
[root@ADM-SHOWARD ~]# vi DbCheck.java
[root@ADM-SHOWARD ~]# javac DbCheck.java
[root@ADM-SHOWARD ~]# export CLASSPATH=/root/ojdbc6.jar:/root/JSAP-2.0a.zip:.
[root@ADM-SHOWARD ~]# ./check_db -H 172.26.24.6 -p 1521 -s "ecomonline.expdev.local" -l system -x express -f mysql.sql -r % -w 1 -c 2 -L "who knows"
java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:873)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1491)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406)
at DbCheck.testSS(DbCheck.java:158)
at DbCheck.main(DbCheck.java:140)
[root@ADM-SHOWARD ~]# ./check_db -H 172.26.24.6 -p 1521 -s "ecomonline.expdev.local" -l system -x express -f mysql.sql -r '(%)' -w 1 -c 2 -L "who knows"
java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:873)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1491)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406)
at DbCheck.testSS(DbCheck.java:158)
at DbCheck.main(DbCheck.java:140)
[root@ADM-SHOWARD ~]# ./check_db -H 172.26.24.6 -p 1521 -s "ecomonline.expdev.local" -l system -x express -f mysql.sql -r '(%)' -w 1 -c 2 -L "who knows"
No match found for regular expression:(%)
[root@ADM-SHOWARD ~]# ./check_db -H 172.26.24.6 -p 1521 -s "ecomonline.expdev.local" -l system -x express -f mysql.sql -r 'OPEN%' -w 1 -c 2 -L "STATUS"
OPEN
No match found for regular expression:OPEN%
[root@ADM-SHOWARD ~]# ./check_db -H 172.26.24.6 -p 1521 -s "ecomonline.expdev.local" -l system -x express -f mysql.sql -r 'OPEN' -w 1 -c 2 -L "STATUS"
OPEN
Unsupported data type detected..
[root@ADM-SHOWARD ~]# vi DbCheck.java
[root@ADM-SHOWARD ~]# vi mysql.sql
[root@ADM-SHOWARD ~]# ./check_db -H 172.26.24.6 -p 1521 -s "ecomonline.expdev.local" -l system -x express -f mysql.sql -r 'OPEN' -w 1 -c 2 -L "STATUS"
0
No match found for regular expression:OPEN
[root@ADM-SHOWARD ~]# vi mysql.sql
[root@ADM-SHOWARD ~]# ./check_db -H 172.26.24.6 -p 1521 -s "ecomonline.expdev.local" -l system -x express -f mysql.sql -r 'OPEN' -w 1 -c 2 -L "STATUS"
5
No match found for regular expression:OPEN
[root@ADM-SHOWARD ~]# vi mysql.sql
[root@ADM-SHOWARD ~]# ./check_db -H 172.26.24.6 -p 1521 -s "ecomonline.expdev.local" -l system -x express -f mysql.sql -r 'OPEN' -w 1 -c 2 -L "STATUS"
0
No match found for regular expression:OPEN
[root@ADM-SHOWARD ~]# ./check_db -H 172.26.24.6 -p 1521 -s "ecomonline.expdev.local" -l system -x express -f mysql.sql -r '5' -w 1 -c 2 -L "STATUS"
0
No match found for regular expression:5
[root@ADM-SHOWARD ~]#


mysql.sql file contents are below:

select case status when 'OPEN' then 0 when 'MOUNTED' then 1 when 'NOT MOUNTED' then 2 end case from v$instance
Helpful?    
Report
didn't work
by Majed, July 31, 2011
first i had much trouble getting the jar command to work but after it did i did everything instructed, not sure about the sql files though. i cpied just one file with the .sql extension. anyway on redhat from command prompt here is the output:
[root@orcl libexec]# ./DbCheck.java -H 10.1.1.247 -s orc1
import: unable to open X server `'.
import: unable to open X server `'.
import: unable to open X server `'.
import: unable to open X server `'.
import: unable to open X server `'.
import: unable to open X server `'.
import: unable to open X server `'.
import: unable to open X server `'.
import: unable to open X server `'.
./DbCheck.java: line 12: public: command not found
./DbCheck.java: line 15: syntax error near unexpected token `('
./DbCheck.java: line 15: ` public DbCheck()'
Helpful?    
Report
Add a Review

You must be logged in to submit a review.

Thank you for your review!

Your review has been submitted and is pending approval.

Recommend

To:


From:


Thank you for your recommendation!

Your recommendation has been sent.

Page Sections
Project Overview Project Files Project Notes Reviews
Project Stats
Rating
1.7 (3)
Favorites
0
Views
113,652