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

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

You must be logged in to submit a review.

Your review has been submitted and is pending approval.

Your recommendation has been sent.

Project Overview Project Files Project Notes Reviews