1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package it.jnrpe.plugin;
17
18 import it.jnrpe.ICommandLine;
19 import it.jnrpe.Status;
20 import it.jnrpe.plugins.Metric;
21 import it.jnrpe.plugins.MetricGatheringException;
22 import it.jnrpe.plugins.PluginBase;
23 import it.jnrpe.utils.BadThresholdException;
24 import it.jnrpe.utils.thresholds.ThresholdsEvaluatorBuilder;
25
26 import java.math.BigDecimal;
27 import java.sql.Connection;
28 import java.sql.Driver;
29 import java.sql.DriverManager;
30 import java.sql.ResultSet;
31 import java.sql.SQLException;
32 import java.sql.Statement;
33 import java.text.MessageFormat;
34 import java.util.ArrayList;
35 import java.util.Collection;
36 import java.util.List;
37
38
39
40
41
42
43
44 public class CCheckOracle extends PluginBase {
45
46
47
48
49 private static final String PLUGIN_NAME = "CHECK_ORACLE";
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66 private Connection getConnection(final ICommandLine cl)
67 throws SQLException, InstantiationException,
68 IllegalAccessException, ClassNotFoundException {
69 DriverManager.registerDriver((Driver) Class.forName(
70 "oracle.jdbc.driver.OracleDriver").newInstance());
71
72 log.debug("Connecting to " + cl.getOptionValue("db") + "@"
73 + cl.getOptionValue("server"));
74
75 Connection conn = DriverManager.getConnection(
76 "jdbc:oracle:thin:@" + cl.getOptionValue("server") + ":"
77 + cl.getOptionValue("port", "1521") + ":"
78 + cl.getOptionValue("db"),
79 cl.getOptionValue("username"), cl.getOptionValue("password"));
80
81 return conn;
82 }
83
84
85
86
87
88
89
90
91
92
93
94
95
96 private List<Metric> checkAlive(final Connection c, final ICommandLine cl)
97 throws BadThresholdException, SQLException {
98
99 List<Metric> metricList = new ArrayList<Metric>();
100 Statement stmt = null;
101
102 long lStart = System.currentTimeMillis();
103
104 try {
105 stmt = c.createStatement();
106 ResultSet rs = stmt.executeQuery("SELECT SYSDATE FROM DUAL");
107
108 if (!rs.next()) {
109
110 throw new SQLException(
111 "Unable to execute a 'SELECT SYSDATE FROM DUAL' query");
112 }
113
114 long elapsed = (System.currentTimeMillis() - lStart) / 1000L;
115
116 metricList.add(new Metric("conn", "Connection time : " + elapsed
117 + "s", new BigDecimal(elapsed), new BigDecimal(0), null));
118
119 return metricList;
120
121 } finally {
122 try {
123 if (stmt != null) {
124 stmt.close();
125 }
126 } catch (Exception e) {
127
128 }
129 }
130 }
131
132
133
134
135
136
137
138
139
140
141
142
143 private List<Metric> checkTablespace(final Connection c,
144 final ICommandLine cl) throws BadThresholdException, SQLException {
145
146
147
148 List<Metric> metricList = new ArrayList<Metric>();
149
150 String sTablespace = cl.getOptionValue("tablespace").toUpperCase();
151
152 final String sQry = "select NVL(b.free,0.0),a.total,100 "
153 + "- trunc(NVL(b.free,0.0)/a.total * 1000) / 10 prc"
154 + " from ("
155 + " select tablespace_name,sum(bytes)/1024/1024 total"
156 + " from dba_data_files group by tablespace_name) A"
157 + " LEFT OUTER JOIN"
158 + " ( select tablespace_name,sum(bytes)/1024/1024 free"
159 + " from dba_free_space group by tablespace_name) B"
160 + " ON a.tablespace_name=b.tablespace_name "
161 + "WHERE a.tablespace_name='" + sTablespace + "'";
162
163 Statement stmt = null;
164 ResultSet rs = null;
165
166 try {
167 stmt = c.createStatement();
168
169 rs = stmt.executeQuery(sQry);
170
171 boolean bFound = rs.next();
172
173 if (!bFound) {
174 throw new SQLException("Tablespace "
175 + cl.getOptionValue("tablespace") + " not found.");
176 }
177
178 BigDecimal tsFree = rs.getBigDecimal(1);
179 BigDecimal tsTotal = rs.getBigDecimal(2);
180 BigDecimal tsPct = rs.getBigDecimal(3);
181
182 metricList.add(new Metric("tblspace_freepct", cl
183 .getOptionValue("tablespace") + " : " + tsPct + "% free",
184 tsPct, new BigDecimal(0), new BigDecimal(100)));
185
186 metricList.add(new Metric("tblspace_free", cl
187 .getOptionValue("tablespace") + " : " + tsFree + "MB free",
188 tsPct, new BigDecimal(0), tsTotal));
189
190 return metricList;
191
192 } finally {
193 try {
194 if (stmt != null) {
195 stmt.close();
196 }
197 } catch (Exception e) {
198
199 }
200 }
201
202 }
203
204
205
206
207
208
209
210
211
212
213
214
215 private List<Metric> checkCache(final Connection c, final ICommandLine cl)
216 throws BadThresholdException, SQLException {
217
218 List<Metric> metricList = new ArrayList<Metric>();
219
220
221 String sQry1 = "select (1-(pr.value/(dbg.value+cg.value)))*100"
222 + " from v$sysstat pr, v$sysstat dbg, v$sysstat cg"
223 + " where pr.name='physical reads'"
224 + " and dbg.name='db block gets'"
225 + " and cg.name='consistent gets'";
226
227 String sQry2 = "select sum(lc.pins)/(sum(lc.pins)"
228 + "+sum(lc.reloads))*100 from v$librarycache lc";
229
230 Statement stmt = null;
231 ResultSet rs = null;
232
233 try {
234 stmt = c.createStatement();
235
236 rs = stmt.executeQuery(sQry1);
237 rs.next();
238
239 BigDecimal buf_hr = rs.getBigDecimal(1);
240
241 rs = stmt.executeQuery(sQry2);
242 rs.next();
243
244 BigDecimal lib_hr = rs.getBigDecimal(1);
245
246 String libHitRate = "Cache Hit Rate {1,number,0.#}% Lib";
247 String buffHitRate = "Cache Hit Rate {1,number,0.#}% Buff";
248
249 metricList.add(new Metric("cache_buf", MessageFormat.format(
250 buffHitRate, buf_hr), buf_hr, new BigDecimal(0),
251 new BigDecimal(100)));
252 metricList.add(new Metric("cache_lib", MessageFormat.format(
253 libHitRate, lib_hr), lib_hr, new BigDecimal(0),
254 new BigDecimal(100)));
255
256 return metricList;
257 } finally {
258 try {
259 if (stmt != null) {
260 stmt.close();
261 }
262 } catch (Exception e) {
263
264 }
265 }
266
267 }
268
269 @Override
270 public void configureThresholdEvaluatorBuilder(
271 final ThresholdsEvaluatorBuilder thrb, final ICommandLine cl)
272 throws BadThresholdException {
273 if (cl.hasOption("th")) {
274 super.configureThresholdEvaluatorBuilder(thrb, cl);
275 } else {
276 if (cl.hasOption("alive")) {
277 thrb.withLegacyThreshold("conn", null,
278 cl.getOptionValue("warning"),
279 cl.getOptionValue("critical"));
280 }
281
282 if (cl.hasOption("tablespace")) {
283 thrb.withLegacyThreshold("tblspace_freepct", null,
284 cl.getOptionValue("warning", "70"),
285 cl.getOptionValue("critical", "80"));
286 }
287
288 if (cl.hasOption("cache")) {
289 thrb.withLegacyThreshold("cache_buf", null,
290 cl.getOptionValue("warning", "70"),
291 cl.getOptionValue("critical", "80"));
292 }
293 }
294 }
295
296 @Override
297 public Collection<Metric> gatherMetrics(final ICommandLine cl)
298 throws MetricGatheringException {
299 Connection conn = null;
300
301 try {
302 conn = getConnection(cl);
303
304 List<Metric> metricList = new ArrayList<Metric>();
305 metricList.addAll(checkAlive(conn, cl));
306
307 if (cl.hasOption("tablespace")) {
308 metricList.addAll(checkTablespace(conn, cl));
309 }
310 metricList.addAll(checkCache(conn, cl));
311
312 return metricList;
313 } catch (ClassNotFoundException cnfe) {
314 log.error("Oracle driver library not found into the classpath: "
315 + "download and put it in the same directory "
316 + "of this plugin");
317
318 throw new MetricGatheringException(cnfe.getMessage(),
319 Status.UNKNOWN, cnfe);
320 } catch (SQLException sqle) {
321 log.error("Error communicating with database.", sqle);
322
323 throw new MetricGatheringException(sqle.getMessage(),
324 Status.CRITICAL, sqle);
325
326 } catch (Exception e) {
327 log.fatal("Error communicating with database.", e);
328
329 throw new MetricGatheringException(e.getMessage(), Status.UNKNOWN,
330 e);
331 } finally {
332 if (conn != null) {
333 try {
334 conn.close();
335 } catch (Exception e) {
336 log.warn("Error closing the DB connection.", e);
337 }
338 }
339 }
340 }
341
342 @Override
343 protected String getPluginName() {
344 return PLUGIN_NAME;
345 }
346 }