check_db

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.

---

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

---

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()'

---