/*
* Copyright (c) 2008 Massimiliano Ziccardi
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package it.jnrpe.plugin;
import it.jnrpe.commands.CCommandLine;
import it.jnrpe.net.IJNRPEConstants;
import it.jnrpe.plugin.util.ThresholdUtil;
import it.jnrpe.plugins.CReturnValue;
import it.jnrpe.plugins.IPluginInterface;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
/**
* Performs standard checks against an oracle database server
*
* @author Massimiliano Ziccardi
*
*/
public class CCheckOracle implements IPluginInterface
{
private Log m_Logger = LogFactory.getLog(CCheckOracle.class);
public CCheckOracle()
{
}
/**
* Connects to the database
* @param cl
* @return
* @throws SQLException
* @throws InstantiationException
* @throws IllegalAccessException
* @throws ClassNotFoundException
*/
private Connection getConnection(CCommandLine cl) throws SQLException,
InstantiationException, IllegalAccessException,
ClassNotFoundException
{
DriverManager.registerDriver // load driver
((Driver) Class.forName("oracle.jdbc.driver.OracleDriver")
.newInstance());
m_Logger.debug("GETTING CONNECTION TO " + cl.getOptionValue("db")
+ "@" + cl.getOptionValue("server"));
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@"
+ cl.getOptionValue("server") + ":"
+ cl.getOptionValue("port", "1521") + ":"
+ cl.getOptionValue("db"), cl.getOptionValue("username"), cl
.getOptionValue("password"));
return conn;
}
/**
* Checks if the database is reacheble
* @param c
* @param cl
* @return
*/
private CReturnValue checkAlive(Connection c, CCommandLine cl)
{
Statement stmt = null;
ResultSet rs = null;
String sMsg = "{0} : {1} - {2} {3}";
Object[] vObjs = new Object[4];
vObjs[0] = "CHECK_ORACLE";
vObjs[1] = cl.getOptionValue("db");
vObjs[2] = "OK";
vObjs[3] = "";
MessageFormat mf = new MessageFormat(sMsg);
try
{
stmt = c.createStatement();
rs = stmt.executeQuery("SELECT SYSDATE FROM DUAL");
return new CReturnValue(IJNRPEConstants.STATE_OK, mf.format(vObjs));
}
catch (SQLException sqle)
{
vObjs[2] = "UNKNOWN";
vObjs[3] = sqle.getMessage();
return new CReturnValue(IJNRPEConstants.STATE_UNKNOWN, mf.format(vObjs));
}
catch (Exception e)
{
vObjs[2] = "CRITICAL";
vObjs[3] = e.getMessage();
return new CReturnValue(IJNRPEConstants.STATE_CRITICAL, mf.format(vObjs));
}
finally
{
try
{
stmt.close();
rs.close();
}
catch (Exception e)
{
}
}
}
/**
* Checks database usage
* @param c
* @param cl
* @return
*/
private CReturnValue checkTablespace(Connection c, CCommandLine cl)
{
// Integer iWarning = new Integer(cl.getOptionValue("warning", "70"));
// Integer iCritical = new Integer(cl.getOptionValue("critical", "80"));
String sWarning = cl.getOptionValue("warning", "70");
String sCritical = cl.getOptionValue("critical", "80");
String sTablespace = cl.getOptionValue("tablespace").toUpperCase();
String sQry = "select NVL(b.free,0.0),a.total,100 - trunc(NVL(b.free,0.0)/a.total * 1000) / 10 prc"
+ " from ("
+ " select tablespace_name,sum(bytes)/1024/1024 total"
+ " from dba_data_files group by tablespace_name) A"
+ " LEFT OUTER JOIN"
+ " ( select tablespace_name,sum(bytes)/1024/1024 free"
+ " from dba_free_space group by tablespace_name) B"
+ " ON a.tablespace_name=b.tablespace_name WHERE a.tablespace_name='" + sTablespace + "'";
Statement stmt = null;
ResultSet rs = null;
try
{
stmt = c.createStatement();
rs = stmt.executeQuery(sQry);
boolean bFound = rs.next();
if (!bFound)
return new CReturnValue(IJNRPEConstants.STATE_UNKNOWN, "CHECK_ORACLE : UNKNOWN - Tablespace " + cl.getOptionValue("tablespace") + " do not exist?");
BigDecimal ts_free = rs.getBigDecimal(1);
BigDecimal ts_total = rs.getBigDecimal(2);
BigDecimal ts_pct = rs.getBigDecimal(3);
String sMsg = "{0} : {1} {2} - {3,number,0.#}% used [ {4,number,0.#} / {5,number,0.#} MB available ]|{1}={3,number,0.#}%;{6};{7};0;100";
Object[] vObjs = new Object[8];
vObjs[0] = cl.getOptionValue("db");
vObjs[1] = cl.getOptionValue("tablespace");
vObjs[2] = "OK";
vObjs[3] = ts_pct;
vObjs[4] = ts_free;
vObjs[5] = ts_total;
vObjs[6] = sWarning;
vObjs[7] = sCritical;
MessageFormat mf = new MessageFormat(sMsg);
//if (ts_pct.compareTo(new BigDecimal(iCritical.intValue())) == 1)
if (ThresholdUtil.isValueInRange(sCritical, ts_pct))
{
vObjs[2] = "CRITICAL";
CReturnValue rv = new CReturnValue(IJNRPEConstants.STATE_CRITICAL, mf.format(vObjs));
return rv;
}
if (ThresholdUtil.isValueInRange(sWarning, ts_pct))
{
vObjs[2] = "WARNING";
CReturnValue rv = new CReturnValue(IJNRPEConstants.STATE_WARNING, mf.format(vObjs));
return rv;
}
CReturnValue rv = new CReturnValue(IJNRPEConstants.STATE_OK,mf.format(vObjs));
return rv;
}
catch (Exception e)
{
return new CReturnValue(IJNRPEConstants.STATE_CRITICAL,
"CHECK_ORACLE : CRITICAL - " + e.getMessage());
}
finally
{
try
{
stmt.close();
rs.close();
}
catch (Exception e)
{
}
}
}
/**
* Checks cache hit rates
* @param c
* @param cl
* @return
*/
private CReturnValue checkCache(Connection c, CCommandLine cl)
{
String sWarning = cl.getOptionValue("warning", "70");
String sCritical = cl.getOptionValue("critical", "80");
String sQry1 = "select (1-(pr.value/(dbg.value+cg.value)))*100"
+ " from v$sysstat pr, v$sysstat dbg, v$sysstat cg"
+ " where pr.name='physical reads'"
+ " and dbg.name='db block gets'"
+ " and cg.name='consistent gets'";
String sQry2 = "select sum(lc.pins)/(sum(lc.pins)+sum(lc.reloads))*100 from v$librarycache lc";
Statement stmt = null;
ResultSet rs = null;
try
{
stmt = c.createStatement();
rs = stmt.executeQuery(sQry1);
rs.next();
BigDecimal buf_hr = rs.getBigDecimal(1);
rs = stmt.executeQuery(sQry2);
rs.next();
BigDecimal lib_hr = rs.getBigDecimal(1);
String sMessage = "{0} {1} - Cache Hit Rates: {2,number,0.#}% Lib -- {3,number,0.#}% Buff|lib={4,number,0.#}%;{5};{6};0;100 buffer={3,number,0.#};{5};{6};0;100";
MessageFormat mf = new MessageFormat(sMessage);
Object[] vValues = new Object[7];
vValues[0] = cl.getOptionValue("db");
vValues[1] = "OK";
vValues[2] = lib_hr;
vValues[3] = buf_hr;
vValues[4] = lib_hr;
vValues[5] = sWarning;
vValues[6] = sCritical;
//if (buf_hr.compareTo(new BigDecimal(iCritical.intValue())) == -1)
if (ThresholdUtil.isValueInRange(sCritical, buf_hr))
{
vValues[1] = "CRITICAL";
CReturnValue rv = new CReturnValue(IJNRPEConstants.STATE_CRITICAL, mf.format(vValues));
return rv;
}
//if (buf_hr.compareTo(new BigDecimal(iWarning.intValue())) == -1)
if (ThresholdUtil.isValueInRange(sWarning, buf_hr))
{
vValues[1] = "WARNING";
CReturnValue rv = new CReturnValue(IJNRPEConstants.STATE_WARNING,mf.format(vValues));
return rv;
}
CReturnValue rv = new CReturnValue(IJNRPEConstants.STATE_WARNING, mf.format(vValues));
return rv;
}
catch (Exception e)
{
return new CReturnValue(IJNRPEConstants.STATE_CRITICAL,
"CHECK_ORACLE : CRITICAL - " + e.getMessage());
}
finally
{
try
{
stmt.close();
rs.close();
}
catch (Exception e)
{
}
}
}
/*
* (non-Javadoc)
* @see it.jnrpe.plugins.IPluginInterface#execute(it.jnrpe.commands.CCommandLine)
*/
public CReturnValue execute(CCommandLine cl)
{
Connection conn = null;
try
{
conn = getConnection(cl);
if (cl.hasOption("alive"))
return checkAlive(conn, cl);
if (cl.hasOption("tablespace"))
return checkTablespace(conn, cl);
if (cl.hasOption("cache"))
return checkCache(conn, cl);
conn.close();
}
catch (ClassNotFoundException cnfe)
{
m_Logger
.error("ORACLE DRIVER LIBRARY IS NOT IN THE CLASSPATH. (PUT IT IN THE SAME DIRECTORY OF THIS PLUGIN)");
return new CReturnValue(IJNRPEConstants.STATE_UNKNOWN,
"Configuration error");
}
catch (SQLException sqle)
{
m_Logger.info("Error communicating with database.", sqle);
return new CReturnValue(IJNRPEConstants.STATE_CRITICAL,
sqle.getMessage());
}
catch (Exception e)
{
m_Logger.fatal("Error communicating with database.", e);
return new CReturnValue(IJNRPEConstants.STATE_UNKNOWN,
"Configuration error");
}
finally
{
if (conn != null)
{
try
{
conn.close();
}
catch (Exception e)
{
m_Logger.warn("ERROR CLOSING DB CONNECTION.", e);
}
}
}
return null;
}
}