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

Search Exchange

Search All Sites

Nagios Live Webinars

Let our experts show you how Nagios can help your organization.

Contact Us

Phone: 1-888-NAGIOS-1
Email: sales@nagios.com

Login

Remember Me

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

Current Version
1.0
Last Release Date
2014-04-04
Compatible With
  • Nagios 3.x
  • Nagios 4.x
Owner
License
GPL
Hits
32367
Files:
FileDescription
setup_nagios_dbuser.sqlsetup_nagios_dbuser.sql
check_ora_dbcheck_ora_db
Network Monitoring Software - Download Nagios XI
Log Management Software - Nagios Log Server - Download
Netflow Analysis Software - Nagios Network Analyzer - Download
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)
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)