check_ora_db - full-featured plug-in for Oracle DB (including alert.log, backups and standby monitoring)

Submit review Recommend Print Contact Owner

Rating

1 vote

Favoured:

Current Version

1.0

Last Release Date

2014-04-04

Compatible With

Nagios 3.x
Nagios 4.x

Owner

krislew

License

GPL

Hits

43424

Files:

File	Description
setup_nagios_dbuser.sql	setup_nagios_dbuser.sql
check_ora_db	check_ora_db

Meet The New Nagios Core Services Platform

Built on over 25 years of monitoring experience, the Nagios Core Services Platform provides insightful monitoring dashboards, time-saving monitoring wizards, and unmatched ease of use. Use it for free indefinitely.

Download Now

Monitoring Made Magically Better

Nagios Core on Overdrive
Powerful Monitoring Dashboards
Time-Saving Configuration Wizards
Open Source Powered Monitoring On Steroids
And So Much More!

Nagios plug-in for Oracle database (tested on 8i/9i/10g/11g). This script can be run remotely from separate monitoring host.
It checks:
a) database status (up/down)
b) listener status
c) session limit count
d) alert.log errors within defined time frame; error list can be customized (number of occurrences and time window)
e) last analyse time (statistics) based on either default auto jobs or list of schemas that should be checked
f) last archivelogs backup time
g) last full database backup time
h) last incremental database backup time
i) last export time - based on nagios export status table (see setup section)
j) last cold backup time - based on nagios cold backup status table (see setup section)
k) logical standby lag in minutes
l) logical standby gap detection
m) physical standby lag in minutes
n) physical standby gap detection

Run 'check_ora_db --help' for full description.

Setup:

1. install 10g (or above) oracle client (script uses EZ connect to establish database connection); this client is to be used by check_ora_db script

2. create required database user (nagios), objects and privileges on target databases (see separate script).

3. if you would like to monitor either exp/expdp backup or cold backup you have to create export/cold backup status tables
in the target database (see separate script) and modify your backup scripts to insert return code to nagios status tables (see check_ora_db header).

4. edit nrpe.cfg and insert required checks with options, for example:

Note: ORACLE_HOME below points to oracle client binaries used by nagios plugin. It's not $ORACLE_HOME of monitored database SID.

command[check_WHATEVER_SID]=/usr/lib64/nagios/plugins/check_ora_db --action=statusOK
command[check_dbstatus_SID]=/usr/lib64/nagios/plugins/check_ora_db --action=dbstatus --service=[SID|SERVICE] --oh=[ORACLE_HOME] --dbuser=nagios --dbpass=[nagiospass] --port=[dbport] --host=[dbhost]
command[check_lsnrstatus_SID]=/usr/lib64/nagios/plugins/check_ora_db --action=lsnrstatus --service=[SID|SERVICE] --oh=[ORACLE_HOME] --lsnrports=[dbhost]:[dbport_1],[dbhost]:[dbport_2]
command[check_sessionlimit_SID]=/usr/lib64/nagios/plugins/check_ora_db --action=sessionlimit --service=[SID|SERVICE] --oh=[ORACLE_HOME] --warning=85 --critical=95 --dbuser=nagios --dbpass=[nagiospass] --port=[dbport] --host=[dbhost]
command[check_alertlogerror_SID]=/usr/lib64/nagios/plugins/check_ora_db --action=alertlogerror --service=[SID|SERVICE] --oh=[ORACLE_HOME] --dbuser=nagios --dbpass=[nagiospass] --port=[dbport] --host=[dbhost]
command[check_alertlogerror_SID]=/usr/lib64/nagios/plugins/check_ora_db --action=alertlogerror --service=[SID|SERVICE] --oh=[ORACLE_HOME] --dbuser=nagios --dbpass=[nagiospass] --port=[dbport] --host=[dbhost] --alertlogerrlist=ORA-04045:1::4,ORA-04098::1:4,ORA-27:::24
command[check_laststats_SID]=/usr/lib64/nagios/plugins/check_ora_db --action=laststats --service=[SID|SERVICE] --oh=[ORACLE_HOME] --warning=7 --critical=14 --dbuser=nagios --dbpass=[nagiospass] --port=[dbport] --host=[dbhost] --statsTarget=auto_job
command[check_laststats_SID]=/usr/lib64/nagios/plugins/check_ora_db --action=laststats --service=[SID|SERVICE] --oh=[ORACLE_HOME] --warning=7 --critical=14 --dbuser=nagios --dbpass=[nagiospass] --port=[dbport] --host=[dbhost] --statsTarget=DBA_TAB_STATS_HISTORY
command[check_laststats_SID]=/usr/lib64/nagios/plugins/check_ora_db --action=laststats --service=[SID|SERVICE] --oh=[ORACLE_HOME] --warning=7 --critical=14 --dbuser=nagios --dbpass=[nagiospass] --port=[dbport] --host=[dbhost] --statsTarget=USER1,USER2
command[check_lastArchBackup_SID]=/usr/lib64/nagios/plugins/check_ora_db --action=lastArchBackup --service=[SID|SERVICE] --oh=[ORACLE_HOME] --warning=8 --critical=24 --dbuser=nagios --dbpass=[nagiospass] --port=[dbport] --host=[dbhost]
command[check_lastFullBackup_SID]=/usr/lib64/nagios/plugins/check_ora_db --action=lastFullBackup --service=[SID|SERVICE] --oh=[ORACLE_HOME] --warning=33 --critical=36 --dbuser=nagios --dbpass=[nagiospass] --port=[dbport] --host=[dbhost]
command[check_lastIncrBackup_SID]=/usr/lib64/nagios/plugins/check_ora_db --action=lastIncrBackup --service=[SID|SERVICE] --oh=[ORACLE_HOME] --warning=1.5 --critical=2 --dbuser=nagios --dbpass=[nagiospass] --port=[dbport] --host=[dbhost]
command[check_lastExpBackup_SID]=/usr/lib64/nagios/plugins/check_ora_db --action=lastExpBackup --service=[SID|SERVICE] --oh=[ORACLE_HOME] --warning=1 --critical=3 --dbuser=nagios --dbpass=[nagiospass] --port=[dbport] --host=[dbhost]
command[check_lastColdBackup_SID]=/usr/lib64/nagios/plugins/check_ora_db --action=lastColdBackup --service=[SID|SERVICE] --oh=[ORACLE_HOME] --warning=1 --critical=3 --dbuser=nagios --dbpass=[nagiospass] --port=[dbport] --host=[dbhost]
command[check_logstbyLag_SID]=/usr/lib64/nagios/plugins/check_ora_db --action=logstbyLag --service=[SID|SERVICE] --oh=[ORACLE_HOME] --warning=60 --critical=180 --dbuser=nagios --dbpass=[nagiospass] --port=[dbport] --host=[dbhost]
command[check_logstbyGap_SID]=/usr/lib64/nagios/plugins/check_ora_db --action=logstbyGap --service=[SID|SERVICE] --oh=[ORACLE_HOME] --dbuser=nagios --dbpass=[nagiospass] --port=[dbport] --host=[dbhost]
command[check_phystbyLag_SID]=/usr/lib64/nagios/plugins/check_ora_db --action=phystbyLag --service=[SID|SERVICE] --oh=[ORACLE_HOME] --warning=60 --critical=180 --dbuser=nagios --dbpass=[nagiospass] --port=[dbport] --host=[dbhost]
command[check_phystbyGap_SID]=/usr/lib64/nagios/plugins/check_ora_db --action=phystbyGap --service=[SID|SERVICE] --oh=[ORACLE_HOME] --dbuser=nagios --dbpass=[nagiospass] --port=[dbport] --host=[dbhost]

5. reload nrpe daemon and configure appropriate checks on nagios server

Examples:

/usr/lib64/nagios/plugins/check_ora_db --action=statusOK
[OK] This is dummy entry

/usr/lib64/nagios/plugins/check_ora_db --action=dbstatus --service=orcl.world --oh=/oracle/orahome --dbuser=nagios --dbpass=nagiospass --port=1521 --host=orahost
[OK] orcl.world-> database is up (version 11.2.0.3.0)

/usr/lib64/nagios/plugins/check_ora_db --action=lsnrstatus --service=orcl.world --oh=/oracle/orahome --lsnrports=orahost:1521,orahost:1542
[CRITICAL] Listener status (Host:Port orahost:1521 - OK) (Host:Port orahost:1542 - CRITICAL)

/usr/lib64/nagios/plugins/check_ora_db --action=sessionlimit --service=orcl.world --oh=/oracle/orahome --warning=85 --critical=95 --dbuser=nagios --dbpass=nagiospass --port=1521 --host=orahost
[OK] orcl.world-> Sessions = 1514 : MaxSessions = 7552 (20.05%) :: Warning/Critical = 85%/95%

Alertlog handling:
/usr/lib64/nagios/plugins/check_ora_db --action=alertlogerror --service=orcl.world --oh=/oracle/orahome --dbuser=nagios --dbpass=nagiospass --port=1521 --host=orahost
a) [OK] orcl.world-> AlertLog Errors: 5 : ORA-28(3)[OK],ORA-3136(2)[OK]
b) [OK] orcl.world-> AlertLog Errors: 0
c) [WARNING] orcl.world-> AlertLog Errors: 6 : ORA-00060(3)[Warning],ORA-01555(1)[OK],ORA-3136(1)[OK],ORA-609(1)[OK last: 18.6h ago]
This output shows that during last 24h hours there where three ORA-00060 that triggers warning; one ORA-01555 and ORA-3136 that are ignored; and one ORA-609 that by default triggers warning during last 3 hours; here it was earlier so status is OK

/usr/lib64/nagios/plugins/check_ora_db --action=alertlogerror --service=orcl.world --oh=/oracle/orahome --dbuser=nagios --dbpass=nagiospass --port=1521 --host=orahost --alertlogerrlist=ORA-04045:1::4,ORA-04098::1:4,ORA-27:::24
[OK] orcl.world-> AlertLog Errors: 2 : ORA-12012(2)[OK]
here the meaning of "ORA-04045:2::4,ORA-04098::1:4,ORA-27:::24" parameter is as follows:
ORA-04045:1::4 - trigger WARNING if at least 2 errors found during last 4 hours
ORA-04098::1:4 - trigger CRITICAL if at least 1 errors found during last 4 hours
ORA-27:::24 - ignore any occurrence
ORA-12012 is ignored by default

Statistics handling:
a) 'auto_job' means default oracle statistics job (AUTOTASK for 11g or GATHER_STATS_JOB job for 10g)
/usr/lib64/nagios/plugins/check_ora_db --action=laststats --service=orcl.world --oh=/oracle/orahome --warning=7 --critical=14 --dbuser=nagios --dbpass=nagiospass --port=1521 --host=orahost --statsTarget=auto_job
[OK] orcl.world-> Statistics: (AUTO_JOB 6/12 OK) :: Warning/Critical = 7/14 days
b) 'DBA_TAB_STATS_HISTORY' means force the script to check last updates (gathered statistics) in DBA_TAB_STATS_HISTORY table - use when default auto_job is disabled and you want to check if any statistics are gathered (10g/11g)
/usr/lib64/nagios/plugins/check_ora_db --action=laststats --service=orcl.world --oh=/oracle/orahome --warning=7 --critical=14 --dbuser=nagios --dbpass=nagiospass --port=1521 --host=orahost --statsTarget=DBA_TAB_STATS_HISTORY
[OK] orcl.world-> Statistics: (DBA_TAB_STATS_HISTORY 312/642 OK) :: Warning/Critical = 7/14 days
c) list of schemas that should be checked against statistics gathering
/usr/lib64/nagios/plugins/check_ora_db --action=laststats --service=orcl.world --oh=/oracle/orahome --warning=7 --critical=14 --dbuser=nagios --dbpass=nagiospass --port=1521 --host=orahost --statsTarget=USER1,USER2
[OK] orcl.world-> Statistics: (USER1 23/30 OK) (USER2 26/37 OK) :: Warning/Critical = 7/14 days

/usr/lib64/nagios/plugins/check_ora_db --action=lastArchBackup --service=orcl.world --oh=/oracle/orahome --warning=8 --critical=24 --dbuser=nagios --dbpass=nagiospass --port=1521 --host=orahost
[OK] orcl.world-> Archivelogs: 20 backups in last 8 hour(s) : 53 backups in last 24 hours

/usr/lib64/nagios/plugins/check_ora_db --action=lastFullBackup --service=orcl.world --oh=/oracle/orahome --warning=33 --critical=36 --dbuser=nagios --dbpass=nagiospass --port=1521 --host=orahost
[OK] orcl.world-> DB FULL backup: 1 backup(s) in last 33 day(s) : 2 backups in last 36 days

/usr/lib64/nagios/plugins/check_ora_db --action=lastIncrBackup --service=orcl.world --oh=/oracle/orahome --warning=1.5 --critical=2 --dbuser=nagios --dbpass=nagiospass --port=1521 --host=orahost
[OK] orcl.world-> DB INCR backup: 1 backup(s) in last 1.5 day(s) : 1 backups in last 2 days

/usr/lib64/nagios/plugins/check_ora_db --action=lastExpBackup --service=orcl.world --oh=/oracle/orahome --warning=1 --critical=3 --dbuser=nagios --dbpass=nagiospass --port=1521 --host=orahost
[OK] orcl.world-> Database export: 1 export(s) in last 1 day(s) : 3 exports in last 3 days

/usr/lib64/nagios/plugins/check_ora_db --action=lastColdBackup --service=orcl.world --oh=/oracle/orahome --warning=1 --critical=3 --dbuser=nagios --dbpass=nagiospass --port=1521 --host=orahost
[OK] orcl.world-> Database cold backup: 1 backup(s) in last 1 day(s) : 3 backups in last 3 days

/usr/lib64/nagios/plugins/check_ora_db --action=logstbyLag --service=orcl.world --oh=/oracle/orahome --warning=60 --critical=180 --dbuser=nagios --dbpass=nagiospass --port=1521 --host=orahost
[OK] orcl.world-> Latest committed transactions time (from primary): 2014-04-04 10:22:31 - Lag: 4 minutes :: Warning/Critical = 60/180 min

/usr/lib64/nagios/plugins/check_ora_db --action=logstbyGap --service=orcl.world --oh=/oracle/orahome --dbuser=nagios --dbpass=nagiospass --port=1521 --host=orahost
[OK] orcl.world-> No GAP detected

/usr/lib64/nagios/plugins/check_ora_db --action=phystbyLag --service=orcl.world --oh=/oracle/orahome --warning=60 --critical=180 --dbuser=nagios --dbpass=nagiospass --port=1521 --host=orahost
[OK] orcl.world-> Lag: 15 minutes :: Warning/Critical = 60/180 min

/usr/lib64/nagios/plugins/check_ora_db --action=phystbyGap --service=orcl.world --oh=/oracle/orahome --dbuser=nagios --dbpass=nagiospass --port=1521 --host=orahost
[OK] orcl.world-> No GAP detected

Reviews (1)

Getting Error while monitoring listener process

bysumit.tyagi, March 6, 2019

Working fine at client server
[nagios@co libexec]$ /usr/local/nagios/libexec/check_ora_db --action=lsnrstatus --service=xe --oh=/u01/app/oracle/product/11.2.0/xe --lsnrports=10.10.2.175:1521
[OK] Listener status (Host:Port 10.10.2.175:1521 - OK)
But giving error at Nagios server
[nagios@nagios ~]$ /usr/bin/env LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib /usr/local/nagios/libexec/check_ora_db ORACLE_HOME=/usr/lib/oracle/12.1/client64 --action=lsnrstatus --service=xe --oh=/usr/lib/oracle/12.1/client64 --lsnrports=10.10.2.175:1521
[CRITICAL] Listener status (Host:Port 10.10.2.175:1521 - CRITICAL)

Nagios, the Nagios logo, and Nagios graphics are the servicemarks, trademarks, or registered trademarks owned by Nagios Enterprises. All other servicemarks and trademarks are the property of their respective owner. The files and information on this site are the property of their respective owner(s). Nagios Enterprises makes no claims or warranties as to the fitness of any file or information on this website, for any purpose whatsoever. In fact, we officially disclaim all liability. We do, however, think these community contributions are pretty damn cool. Website Copyright © 2009-2024 Nagios Enterprises, LLC. All rights reserved.