Build precise queries to find exactly what you need
Press ESC to close
Your review has been submitted and is pending approval.
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
Current Version
1.0
Last Release Date
2014-04-04
Owner
Krzysztof Lewandowski
License
GPL
Compatible With
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
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)
You must be logged in to submit a review.
To:
From: