check_oracle_health

Posted on July 3rd, 2009 by admin

Description

check_oracle_health is a plugin to check various parameters of an Oracle database.

Documentation

Command line parameters

  • –connect= The database name
  • –user= The database user
  • –password= Password of the database user.
  • –connect= Alternativ to the parameters above.
  • –connect=sysdba@ Login with / as sysdba (if the user that executes the plugin is privileged to do this)
  • –connect=/@token Login with help of the Password Store (assumes –method=sqlplus)
  • –mode= With the mode-parameter you tell the plugin what it should do. See the list of possible values further down.
  • –tablespace= With this you can limit the check of a single tablespace. If this parameter is omitted all tablespaces are checked.
  • –datafile= With this you can limit the check of a single datafile. If this parameter is omitted all datafiles are checked.
  • –name= Here the check can be limited to a single object (Latch, Enqueue, Tablespace, Datafile). If this parameter is omitted all objects are checked. (Instead of –tablespace or –datafile this parameter can and should be used. It servers the purpose to standardize the CLI interface.)
  • –name2= f you use –mode=sql, then the SQL-Statement appears in the output and performance values. With the parameter name2 you’re able to specify a string for this.
  • –regexp Through this switch the value of the –name Parameters will be interpreted as regular expression.
  • –warning= Determined values outside of this range trigger a WARNING.
  • –critical= Determined values outside of this range trigger a CRITICAL.
  • –absolute Without –absolute values that increase in the course of time will show the increase per second or with –absolute show the difference between the current and last run.
  • –runas= With this parameter it is possible to run the script under a different user. (Calls sudo internally: sudo -u .
  • –environment = With this you can pass environment variables to the script. For example: –environment ORACLE_HOME=/u01/oracle. Multiple declarations are possible.
  • –method= With this parameter you tell the plugin how it should connect to the database. (dbi for using DBD::Oracle (default), sqlplus for using the sqlplus-Tool).
  • –units=<%|KB|MB|GB> The declaration from units servers the "beautification" of the output from mode=sql and simplification from threshold values when using mode=tablespace-free
  • –dbthresholds With this parameter thresholds are read from the database table check_oracle_health_thresholds
  • –statefilesdir This parameter tells the plugin not do use the default directory for temporary files, but a user-specified one. It can be important in a clustered environment with shared filesystems.

Use the option –mode with various keywords to tell the Plugin which values it should determine and check.

Keyword Description Range
tnsping Listener  
connection-time Determines how long connection establishment and login take 0..n Seconds (1, 5)
connected-users The sum of logged in users at the database 0..n (50, 100)
session-usage Percentage of max possible sessions 0%..100% (80, 90)
process-usage Percentage of max possible processes 0%..100% (80, 90)
rman-backup-problems Number of RMAN-errors during the last three days 0..n (1, 2)
sga-data-buffer-hit-ratio Hitrate in the Data Buffer Cache 0%..100% (98:, 95:)
sga-library-cache-gethit-ratio Hitrate in the Library Cache (Gets) 0%..100% (98:, 95:)
sga-library-cache-pinhit-ratio Hitrate in the Library Cache (Pins) 0%..100% (98:, 95:)
sga-library-cache-reloads Reload-Rate in the Library Cache n/sec (10,10)
sga-dictionary-cache-hit-ratio Hitrate in the Dictionary Cache 0%..100% (95:, 90:)
sga-latches-hit-ratio Hitrate of the Latches 0%..100% (98:, 95:)
sga-shared-pool-reloads Reload-Rate in the Shared Pool 0%..100% (1, 10)
sga-shared-pool-free Free Memory in the Shared Pool 0%..100% (10:, 5:)
pga-in-memory-sort-ratio Percentage of sorts in the memory. 0%..100% (99:, 90:)
invalid-objects Sum of faulty Objects, Indices, Partitions  
stale-statistics Sum of objects with obsolete optimizer statistics n (10, 100)
tablespace-usage Used diskspace in the tablespace 0%..100% (90, 98)
tablespace-free Free diskspace in the tablespace 0%..100% (5:, 2:)
tablespace-fragmentation Free Space Fragmentation Index 100..1 (30:, 20:)
tablespace-io-balanc IO-Distribution under the datafiles of a tablespace n (1.0, 2.0)
tablespace-remaining-time Sum of remaining days until a tablespace is used by 100%. The rate of increase will be calculated with the values from the last 30 days. (With the parameter –lookback different periods can be specified) Days (90:, 30:)
tablespace-can-allocate-next Checks if there is enough free tablespace for the next Extent.  
flash-recovery-area-usage Used diskspace in the flash recovery area 0%..100% (90, 98)
flash-recovery-area-free Free diskspace in the flash recovery area 0%..100% (5:, 2:)
datafile-io-traffic Sum of IO-Operationes from Datafiles per second n/sec (1000, 5000)
datafiles-existing Percentage of max possible datafiles 0%..100% (80, 90)
soft-parse-ratio Percentage of soft-parse-ratio 0%..100%
switch-interval Interval between RedoLog File Switches 0..n Seconds (600:, 60:)
retry-ratio Retry-Rate in the RedoLog Buffer 0%..100% (1, 10)
redo-io-traffic Redolog IO in MB/sec n/sec (199,200)
roll-header-contention Rollback Segment Header Contention 0%..100% (1, 2)
roll-block-contention Rollback Segment Block Contention 0%..100% (1, 2)
roll-hit-ratio Rollback Segment gets/waits Ratio 0%..100% (99:, 98:)
roll-extends Rollback Segment Extends n, n/sec (1, 100)
roll-wraps Rollback Segment Wraps n, n/sec (1, 100)
seg-top10-logical-reads Sum of the userprocesses under the top 10 logical reads n (1, 9)
seg-top10-physical-reads Sum of the userprocesses under the top 10 physical reads n (1, 9)
seg-top10-buffer-busy-waits Sum of the userprocesses under the top 10 buffer busy waits n (1, 9)
seg-top10-row-lock-waits Sum of the userprocesses under the top 10 row lock waits n (1, 9)
event-waits Waits/sec from system events n/sec (10,100)
event-waiting How many percent of the elapsed time has an event spend with waiting 0%..100% (0.1,0.5)
enqueue-contention Enqueue wait/request-Ratio 0%..100% (1, 10)
enqueue-waiting How many percent of the elapsed time since the last run has an Enqueue spend with waiting 0%..100% (0.00033,0.0033)
latch-contention Latch misses/gets-ratio. With –name a Latchname or Latchnumber can be passed over. (See list-latches) 0%..100% (1,2)
latch-waiting How many percent of the elapsed time since the last run has a Latch spend with waiting 0%..100% (0.1,1)
sysstat Changes/sec for any value from v$sysstat n/sec (10,10)
sql Result of any SQL-Statement that returns a number. The statement itself is passed over with the parameter –name. A Label for the performance data output can be passed over with the parameter –name2. n (1,5)
sql-runtime The time an sql command needs to run Seconds (1, 5)
     
list-tablespaces Prints a list of tablespaces  
list-datafiles Prints a list of datafiles  
list-latches Prints a list with latchnames and latchnumbers  
list-enqueues Prints a list with the Enqueue-Names  
list-events Prints a list with the events from (v$system_event). Besides event_number/event_id a shortened form of the eventname is printed out. This could be use as Nagios service descriptions. Example: lo_fi_sw_co = log file switch completion  
list-background-events Prints a list with the Background-Events  
list-sysstats Prints a list with system-wide statistics  

 

Measurements that are dependent on a time interval can be execute differently. To calculate the end result the following is needed: start value, end value and the passed time between this two values. Without further options the inital value will be the value from the last plugin run. The passed time is normally the time of normal_check_interval of the according service.

If the increase per second shouldn’t be decisive for the check result, but the difference between two measured values, than use the option –absolute. This is useful for Rollback Segment Wraps which happen very rare so that their rate is nearly 0/sec. Nevertheless you want to be alarmed if the number od this events grows.

The threshold values should be choosen in a way that they can be reached during a retry_check_interval. If not the service will change into the OK-State after each SOFT;1.

Pleae note, that the thresholds must be specified according to the Nagios plug-in development Guidelines.

"10" means "Alarm, if > 10" and

"90:" means "Alarm, if < 90"

Preparation of the database

In order to be able to collect the needed information from the database a database user with specific privileges is required:

CREATE USER nagios IDENTIFIED BY oradbmon; 
GRANT CREATE SESSION TO nagios;
GRANT SELECT any dictionary TO nagios;
GRANT SELECT ON V_$SYSSTAT TO nagios;
GRANT SELECT ON V_$INSTANCE TO nagios;
GRANT SELECT ON V_$LOG TO nagios;
GRANT SELECT ON SYS.DBA_DATA_FILES TO nagios;
GRANT SELECT ON SYS.DBA_FREE_SPACE TO nagios;
--
-- if somebody still uses Oracle 8.1.7...
GRANT SELECT ON sys.dba_tablespaces TO nagios;
GRANT SELECT ON dba_temp_files TO nagios;
GRANT SELECT ON sys.v_$Temp_extent_pool TO nagios;
GRANT SELECT ON sys.v_$TEMP_SPACE_HEADER  TO nagios;
GRANT SELECT ON sys.v_$session TO nagios;

 

Examples

 

nagios$ check_oracle_health --connect bba --mode tnsping
OK - connection established to bba.
 
nagios$ check_oracle_health --mode connection-time
OK - 0.17 seconds to connect  |
  connection_time=0.1740;1;5
 
nagios$ check_oracle_health --mode sga-data-buffer-hit-ratio
CRITICAL - SGA data buffer hit ratio 0.99%  |
  sga_data_buffer_hit_ratio=0.99%;98:;95:
 
nagios$ check_oracle_health --mode sga-library-cache-hit-ratio
OK - SGA library cache hit ratio 98.75%  |
  sga_library_cache_hit_ratio=98.75%;98:;95:
 
nagios$ check_oracle_health --mode sga-latches-hit-ratio
OK - SGA latches hit ratio 100.00%  |
  sga_latches_hit_ratio=100.00%;98:;95:
 
nagios$ check_oracle_health --mode sga-shared-pool-reloads
OK - SGA shared pool reloads 0.28%  |
  sga_shared_pool_reloads=0.28%;1;10
 
nagios$ check_oracle_health --mode sga-shared-pool-free
WARNING - SGA shared pool free 8.91%  |
  sga_shared_pool_free=8.91%;10:;5:
 
nagios$ check_oracle_health --mode pga-in-memory-sort-ratio
OK - PGA in-memory sort ratio 100.00%  |
  pga_in_memory_sort_ratio=100.00;99:;90:
 
nagios$ check_oracle_health --mode invalid-objects
OK - no invalid objects found  |
  invalid_ind_partitions=0 invalid_indexes=0
  invalid_objects=0 unrecoverable_datafiles=0
 
nagios$ check_oracle_health --mode switch-interval
OK - Last redo log file switch interval was 18 minutes |
    redo_log_file_switch_interval=1090s;600:;60:
 
nagios$ check_oracle_health --mode switch-interval --connect rac1
OK - Last redo log file switch interval was 32 minutes (thread 1)|
    redo_log_file_switch_interval=1938s;600:;60:
 
nagios$ check_oracle_health --mode tablespace-usage
CRITICAL - tbs SYSTEM usage is 99.33%
tbs SYSAUX usage is 93.73%
tbs USERS usage is 8.75%
tbs UNDOTBS1 usage is 6.65% | 'tbs_users_usage_pct'=8%;90;98
'tbs_users_usage'=0MB;4;4;0;5
'tbs_undotbs1_usage_pct'=6%;90;98
'tbs_undotbs1_usage'=11MB;153;166;0;170
'tbs_system_usage_pct'=99%;90;98
'tbs_system_usage'=695MB;630;686;0;700
'tbs_sysaux_usage_pct'=93%;90;98
'tbs_sysaux_usage'=802MB;770;839;0;856
 
nagios$ check_oracle_health --mode tablespace-usage 
    --tablespace USERS
OK - tbs USERS usage is 8.75% |
  'tbs_users_usage_pct'=8%;90;98
  'tbs_users_usage'=0MB;4;4;0;5
 
nagios$ check_oracle_health --mode tablespace-usage 
    --name USERS
OK - tbs USERS usage is 8.75% |
  'tbs_users_usage_pct'=8%;90;98
  'tbs_users_usage'=0MB;4;4;0;5
 
nagios$ check_oracle_health --mode tablespace-free 
    --name TEST
OK - tbs TEST has 97.91% free space left |
    'tbs_test_free_pct'=97.91%;5:;2:
    'tbs_test_free'=32083MB;1638.40:;655.36:;0.00;32767.98
 
nagios$ check_oracle_health --mode tablespace-free 
    --name TEST --units MB --warning 100: --critical 50:
OK - tbs TEST has 32083.61MB free space left |
    'tbs_test_free_pct'=97.91%;0.31:;0.15:
    'tbs_test_free'=32083.61MB;100.00:;50.00:;0;32767.98
 
nagios$ check_oracle_health --mode tablespace-free 
    --name TEST --warning 10: --critical 5:
OK - tbs TEST has 97.91% free space left |
    'tbs_test_free_pct'=97.91%;10:;5:
    'tbs_test_free'=32083MB;3276.80:;1638.40:;0.00;32767.98
 
nagios$ check_oracle_health --mode tablespace-remaining-time 
    --tablespace ARUSERS --lookback 7
WARNING - tablespace ARUSERS will be full in 78 days |
  'tbs_arusers_days_until_full'=78;90:;30:
 
nagios$ check_oracle_health --mode flash-recovery-area-free
OK - flra /u00/app/oracle/flash_recovery_area has 100.00% free space left |
    'flra_free_pct'=100.00%;5:;2:
    'flra_free'=2048MB;102.40:;40.96:;0;2048.00
 
nagios$ check_oracle_health --mode flash-recovery-area-free 
    --units KB --warning 1000: --critical 500:
OK - flra /u00/app/oracle/flash_recovery_area has 2097152.00KB free space left |     'flra_free_pct'=100.00%;0.05:;0.02: 
    'flra_free'=2097152.00KB;1000.00:;500.00:;0;2097152.00
 
nagios$ check_oracle_health --mode datafile-io-traffic 
  --datafile users01.dbf
WARNING - users01.dbf: 1049.83 IO Operations per Second |
  'dbf_users01.dbf_io_total_per_sec'=1049.83;1000;5000
 
nagios$ check_oracle_health --mode latch-contention 
  --name 214
OK - SGA latch library cache (214) contention 0.08% |
 'latch_214_contention'=0.08%;1;2
 'latch_214_sleep_share'=0.00% 'latch_214_gets'=49995
 
nagios$ check_oracle_health --mode latch-contention 
  --name 'library cache'
OK - SGA latch library cache (214) contention 0.08% |
 'latch_214_contention'=0.08%;1;2
 'latch_214_sleep_share'=0.00% 'latch_214_gets'=49937
 
nagios$ check_oracle_health --mode enqueue-contention --name TC
CRITICAL - enqueue TC: 19.90% of the requests must wait |
 'TC_contention'=19.90%;1;10
 'TC_requests'=2015 'TC_waits'=401
 
nagios$ check_oracle_health --mode latch-contention 
  --name 'messages'
OK - SGA latch messages (17) contention 0.02% |
 'latch_17_contention'=0.02%;1;2 'latch_17_gets'=4867
 
nagios$ check_oracle_health --mode latch-waiting 
  --name 'user lock'
OK - SGA latch user lock (205) sleeping 0.000841% of the time |
 'latch_205_sleep_share'=0.000841%
 
nagios$ check_oracle_health --mode event-waits 
  --name 'log file sync'
OK - log file sync : 1.839511 waits/sec |
 'log file sync_waits_per_sec'=1.839511;10;100
 
nagios$ check_oracle_health --mode event-waiting 
  --name 'Log file parallel write'
OK - log file parallel write waits 0.045843% of the time |
rarr 'log file parallel write_percent_waited'=0.045843%;0.1;0.5
 
nagios$ check_oracle_health --mode sysstat 
  --name 'transaction rollbacks'
OK - 0.000003 transaction rollbacks/sec |
 'transaction rollbacks_per_sec'=0.000003;10;100
 'transaction rollbacks'=4
 
nagios$ check_oracle_health --mode sql 
  --name 'select count(*) from v$session' --name2 sessions
CRITICAL - sessions: 21 | 'sessions'=21;1;5
 
nagios$ check_oracle_health --mode sql 
  --name 'select 12 from dual' --name2 twelve --units MB
CRITICAL - twelfe: 12MB | 'twelfe'=12MB;1;5
 
nagios$ check_oracle_health --mode sql 
  --name 'select 200,300,1000 from dual' 
  --name2 'kaspar melchior balthasar' 
  --warning 180 --critical 500
WARNING - kaspar melchior balthasar: 200 300 1000 |
'kaspar'=200;180;500 'melchior'=300;; 'balthasar'=1000;;
 
nagios$ check_oracle_health --mode sql 
  --name &quot;select 'abc123' from dual&quot; --name2 \\d 
  --regexp
OK - output abc123 matches pattern \d

 

Authentication

Example with –runas and an "external user"

There are to users in the database:

  • OPS$DBNAGIO IDENTIFIED EXTERNALLY
  • NAGIOS IDENTIFIED BY ‘DBMONI’

There are two unix users:

  • qqnagio with normal access.
  • dbnagio with /bin/false as login shell.

 

qqnagio$ check_oracle_health --mode=connection-time 
    --connect=nagios/dbmoni@BBA 
OK - 0.21 seconds to connect as NAGIOS 
 
dbnagio$ check_oracle_health --mode=connection-time 
    --connect=BBA --runas=dbnagio 
    --environment ORACLE_HOME=$ORACLE_HOME
OK - 0.17 seconds to connect as OPS$DBNAGIO

The background for this example is the following scenario with a SAP-Server:

Only local connections to the database are allowed. The database isn’t reachable over the network. Logging in with username and password is not possible.

Only database-users that are authenticated through the operating system (OPS$-User) are allowed to connect.

These users are not allowed to connect via SSH. (Therefore /bin/false).

Because the Nagios user qqnagio is allowed to connect via SSH, he can’t be used as database user. But the NRPE which executes the plugin will run under the qqnagios-account.

 

Use of environment variables

It is possible to omit –connect (and if not needed –user and –password) completely, if you provide the corresponding values in environment variables. Since Version 3.x it is possible to extend service definitions in Nagios through own attributes (custom object variables). These will appear during the exectution of the check command in the environment.

The environment variables are:

  • NAGIOS__SERVICEORACLE_SID (_oracle_sid in the service definition)
  • NAGIOS__SERVICEORACLE_USER (_oracle_user in the service definition)
  • NAGIOS__SERVICEORACLE_PASS (_oracle_pass in the service definition)

 

Installation

The installation of the perl-modules DBI and DBD::Oracle is required.

After unpacking the archive ./configure is called. With ./configure –help some options can be printed which show some default values for compiling the plugin.

  • –prefix=BASEDIRECTORY Specify a directory in which check_oracle_health should be stored. (default: /usr/local/nagios)
  • –with-nagios-user=SOMEUSER This User will be the owner of the check_oracle_health file. (default: nagios)
  • –with-nagios-group=SOMEGROUP The group of the check_oracle_health plugin. (default: nagios)
  • –with-perl=PATHTOPERL Specify the path to the perl interpreter you wish to use. (default: perl in PATH)

 

Download

check_oracle_health-1.9.3.4.tar.gz

Some versions of tar are having problems with the long filesnames. In this case please unpack the shar-Paket with cat check_oracle_health-xxx.shar.gz | gzip -d | sh

 

Changelog

  • 1.9.3.4 – 2014-03-18
    - allow floating point numbers in thresholds
  • 1.9.3.3 – 2014-03-17
    - bugfix in diskgroups
    - bugfix in ora-error-handling
  • 1.9.3.2 – 2014-01-27
    - bugfix in a tmp-file-cleanup-routine (only –method sqlplus and many concurrent checks)
  • 1.9.3.1 – 2014-01-14
    - show ORA-errors in stderr (results in warning) coming from sqlplus
  • 1.9.3 – 2014-01-13
    - added subpartitions to invalid-objects
  • 1.9.2.1 – 2014-01-09
    - bugfix in sga-library-cache-pinhit-ratio (Thanks Michel van der Voort)
  • 1.9.2 – 2013-12-18
    - show detailed output for mode corrupted-blocks and report html
    - protect sga-data-buffer-hit-ratio against non-plausible values
    - add modes list-asm-diskgroups and asm-diskgroup-usage/free (Thanks Oliver Skibbe!)
  • 1.9.1.1 – 2013-12-17
    - bugfix in connection destructor
  • 1.9.1 – 2013-12-13
    - bugfix in tablespace-free (datafile in status recovery leads to undef error)
  • 1.9 – 2013-12-09
    - rewrite of the timeout handling
    - bugfix for the windows version
  • 1.8.4.3 – 2013-10-16
    - bugfix in sqlplus fetchrow_array, handles empty result set
  • 1.8.4.2 – 2013-10-16
    - bugfix in invalid objects for oracle < 10.x
  • 1.8.4.1 – 2013-10-09
    - show a subset of perfata with –name2 and invalid-objects
  • 1.8.4 – 2013-10-04
    - invalid-objects can be selected with –name/name2
  • 1.8.3 – 2013-09-30
    - show detailed output for mode invalid-objects and report html
  • 1.8.2.1 – 2013-
    - bugfix in –name :…
  • 1.8.2 – 2013-08-20
    - enable easy connect syntax with –method sqlplus
  • 1.8.1.2 – 2013-07-26
    - –mode sql –name2 “:
  • 1.8.1.1 – 2013-07-04
    - invalid-objects takes refresh delay (default: 2 days) into account. Thanks @chtyo)
  • 1.8.1 – 2013-07-02
    - mode sqlplus can execute stored procedures
  • 1.8.0.1 – 2013-06-10
    - bugfix in sysdba-connect
  • 1.8 – 2013-06-04
    - Don Seiler implemented modes dataguard-lag and dataguard-mrp-status
    A big thank to Don! Monitoring of Oracle Data Guard is now possible
  • 1.7.8.2 – 2013-04-26
    - fix a bug in the online help
  • 1.7.8.1 – 2013-04-09
    - cleanup leftover temp-files (written by method sqlplus)
  • 1.7.8 – 2013-03-27
    - added mode decode
  • 1.7.7.3 – 2013-02-21
    - bugfix in add_nagios, raising a deprecated-message in perl. (Thanks Philip Griesbacher)
  • 1.7.7.2 – 2013-01-22
    - optimized tablespace-can-allocate-next (Thanks Thomas Koerblein)
  • 1.7.7.1 – 2013-01-14
    - bugfix in sqlplus connect with a sys user
  • 1.7.7 – 2012-11-29
    - add parameter –mitigation and –notemp and –noreadonly
  • 1.7.6.1 – 2012-11-19
    - fix a bug with –extra-opts and –environ
  • 1.7.6 – 2012-11-10
    - implemented all sorts of thresholds (Thanks Simon Meggle)
  • 1.7.5.1
    - fix a bug in sga-library-cache-reloads (thresholds compared against pinhits) (Thanks claney)
    - fix a bug in calcmeth which only is visible with –environ (Thanks Pullistricker)
  • 1.7.5
    - restrict rman problems to backup-operations (not list, report..) (Thanks marekel)
  • 1.7.4 – 2012-03-15
    - bugfix in timeout-alarm handling under windows (Thanks Marian Jamrich)
    - bugfix in invalid-objects. No longer counts subpartitions (Thanks Teijo Lallukka)
    - bugfix in session-usage (Thanks Bauchi)
    - add mode sql-runtime
  • 1.7.3 – 2011 09-29
    - mode sql now correctly handles dml sql errors like missing tables etc.
    - single ticks around the –name argument under Windows CMD will be removed automatically
  • 1.7.2 – 2011-09-21
    - add mode sga-library-cache-pinhit-ratio
    - sga-library-cache-hit-ratio becomes sga-library-cache-gethit-ratio
    - add mode sga-library-cache-reloads
  • 1.7.1 – 2001-08-17
    - add option –commit (Thanks Ovidiu)
  • 1.7.0 – 2011-08-16
    - add error handling for unwritable status files
    - fix a bug with statefilesdir and capital letters
    - enhance stale statistics
    - enhance invalid objects (Thanks Yannick Charton)
    - fix a bug in open cmdcmd (only affects method sqlplus)
  • 1.6.9 – 2011-06-16
    - sites in an OMD (http://omdistro.org) environment have now private statefile directories
    - add mode session-usage, process-usage, rman-backup-problems, corrupted-blocks (Thanks Ovidiu Marcu)
    -add mode datafiles-created (Thanks Ovidiu Marcu)
  • 1.6.8.1 – 2011-01-08
    - Workaround for the windows version which cleans up leftover spool files which cannot be deleted.
  • 1.6.8 – 2010-01.03
    - massive speedup in modes seg-top10-* (Thanks Michael Nieberg http://kenntwas.de)
    - bugfix in –mode sql (numeric vs. regexp result) (Thanks Michel Meelker)
  • 1.6.7 – 2010-12-18
    - mode sql can now have a non-numerical output which is compared to a string/regexp
    - new mode report can be used to output only the bad news (short,long,html)
  • 1.6.6.2 2010-11-11
    - better error message with method sqlplus when db is down
  • 1.6.6.1 2010-10-01
    - –dbthresholds can have an argument
    - workaround for an oracle-bug in shared-pool-free (Thanks Yannik)
  • 1.6.6 2010-08-12
    - new parameter –dbthresholds. thresholds can now also be deposited in the table check_oracle_health_thresholds
    - bugfix in connection-time. dbuser was uninitialized in rare cases
  • 1.6.5 2010-08-09
    - plugin can now run on windows
    - –with-mymodules-dyn-dir on the commandline overrides the configure-option of the same name
    - added mode flash-recovery-area-[usage|free]
  • 1.6.4
    - added checking of dba_registry to mode invalid-objects. Thanks Ovidiu Marcu
    - speedup of tablespace-remaining-time. Thanks Steffen Poulsen
    - switch-interval detects redo log timestamps in the future and reports critical- method sqlplus now works with “(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP”-like connectstrings
    - new parameter –ident to show instance and database names in the output
    - bugfix in tablespace-usage (temp tbs with multiple datafiles). Thanks Philipp Lemke
  • 1.6.3 2009-09-09
    - optimized tablespace-can-allocate-next
    - added more tracing.
    - fixed a bug which caused invalid statefile names. Thanks Franky van Liedekerke
    - fixed a bug in tablespace-usage for Oracle 8.1.x
    - switch-interval now tries to predict the next interval to avoid false alerts. Thanks Naquada.
    - passwords do no longer show up in error messages. Thanks Jens Seiffert.
    - fixed a bug in mode sql (numbers of the form .5 were rejected). Thanks Shane Jordan.
    - fixed a bug in sga-latches-hitratio (thresholds were ignored). Thanks Yannik Charton.
    - login parameter –user is now –username (–user still works)
  • 1.6.2 2009-04-04
    - fixed a bug in tablespace-usage and german localization
    - fixed a bug with –method sqlplus and tablespacenames. Thanks “contact_name”
    - fixed a bug in tablespace-usage/free with non-autoextensible TEMP-tablespaces. Thanks Daniel Graef.
  • 1.6.1 2009-03-27
    - NAGIOS__HOSTMYSQL_HOST is now possible
    - added detection for offline/damaged tablespaces to –mode=tablespace-usage|free. Thanks Daniel Graef.
  • 1.6 2009-03-12
    - support for DBD::SQLRelay (use it. it spares your database
    the extra load caused by permanent logins)
    - added support for mode=sql and an array of results. Thanks Juergen Lesny from Matrix.
    - added support for login as “sys”. Thanks Joerg Horchler.
    - fixed a bug where warning/critical=0 was ignored. Thanks Danijel Tasov.
  • 1.5.1 2008-12-10
    - the plugin can be used with the embedded perl interpreter.
    - fixed some typos. Thanks Oliver Riesen.
  • 1.5.0.1 2008-10-16
    - fixed a bug with , instead of . in decimal numbers. Thanks Birk Bohne.
    - mode=sql numerical results are round up to two decimal places.
    - fixed a bug in sga-shared-pool-free. You’ll see more free space now. Thanks Birk Bohne.
  • 1.5.0 2008-10-15
    - added authentication with password store
    - added authentication as sysdba
    - new parameter –units for mode=sql
    - new parameter tablespace-free which allows thresholds in combination with –units
    - mode=switch-interval now separates redologs of rac nodes. Thanks Harald Zahn.
    - it is now possible to integrate self-written code
  • 1.4.2.1 2008-09-19
    - bugfix in tablespace-usage. resized datafiles caused usage 100%
  • 1.4.2 2008-09-16
    - new mode –regexp which extends –name
    - bugfix in datafile-io-balance and case sensitive tbs (Thanks Wiltmut Gerdes)
  • 1.4.1 2008-09-07
    - new mode tablespace-can-allocate-next
    - bugfix to handle locked accounts
    - rewrote seg-top10… sql to avoid overloading
    - bugfix in timeout
    - bugfix in mode sql and zero return value. (Thanks Viktor Kaefer)
    - new mode encode
    - undo tablespace usage takes into account expired extents
  • 1.4.0.1 2008-07-07
    - bugfix when –name=0
    - bugfix in –method=sqlplus
    - bugfix in thresholds of invalid-objects (Thanks Konrad Barck)
  • 1.4 2008-07-03
    - statesdir is now /var/tmp/check_oracle_health by default (autom. migration if it was /tmp)
    - bugfix in latch-contention
    - bugfix in sysstats (thresholds were ignored)
    - bugfix in roll-extends and roll-wraps
    - performance enhancements
    - tablespace-usage can be output as a bargraph (Thanks Allan Peda)
  • 1.3.1.2 2008-07-02
    - fixed a bug in disconnect. there were leftover sessions.
  • 1.3.1.1 2008-07-01
    - fixed a bug in method=sqlplus and os$user
    - objects in the recyclebin are no longer treated as invalid
    - better performance data for pga-in-memory-sort-ratio
    - fixed a bug in tablespace-usage and temp tbs (Thanks Franky van Liedekerke)
  • 1.3.1 2008-06-26
    - typos removed, code cleanup
    - fixed a bug in connected-users thresholds (Thanks Frank Brehm)
  • 1.3 2008-06-23
    - –method=sqlplus using sqlplus instead of DBD::Oracle is possible but NOT supported
    - !! NOT !! supported. If you use it and it don’t work, then don’t whine about it
    - tablespace-usage now takes autoextent into account (Thanks Wiltmut Gerdes)
    - data-buffer/library/dictionary-cache-hitratio is now more accurate
    - –method=sqlplus does not work for you? I’m not listening, lalalalala
  • 1.2.7.1 2008-06-20
    - fixed a bug in windows datafile handling
  • 1.2.7 2008-06-20
    - removed unrecoverable datafiles from invalid-objects
    - added mode sql
    - bugfixes in top10-x and pga-in-memory-sort-ratio
  • 1.2.6.1 2008-06-16
    - added sysstat-rate and list-sysstats
  • 1.2.6 2008-06-14
    - added event-waiting
    - added event-waits
    - added list-events
  • 1.2.5.1 2008-06-11
    - added an abstraction layer so that perl-dbi may be replaced
  • 1.2.5 2008-06-03
    - added latch-contention
    - added enqueue-contention
    - added enqueue-waiting
    - added connected-users
    - added roll-avgactivesize (forget it)
    - added –list-latches –list-enqueues
  • 1.2.4.2 2008-05-27
    - windows pathnames of datafiles are now handled correctly
  • 1.2.4.1 2008-05-27
    - added –list-tablespaces –list-datafiles
  • 1.2.4 2008-05-27
    - added datafile-io-traffic
    - added redo-io-traffic
    - better handling of temp tablespaces
  • 1.2.3.1 2008-05-25
    - stale-statistics now works for < 10.x
  • 1.2.3 2008-05-25
    - added roll-block-contention
    - added roll-hit-ratio
    - fixed a bug in switch-interval
  • 1.2.2.1 – 2008-05-23
    - disabled modes which require minimum 10.x
  • 1.2.2 – 2008-05-21
    - fixed a bug in –environment
  • 1.2.1 – 2008-05-19
    - support for externally authenticated users
    - new parameters –runas and –environment
    - sga-buffer-cache-hit-ratio now shows percent (Thanks Maik Ihde)
    - fixed a bug in tablespace-remaining-time
  • 1.2 – 2008-05-06
    - stale-statistics
    - connection timeout handling
  • 1.1 – 2008-05-02
    —————-
    - tablespace-remaining-time predicts when a tablespace will be full
    - tablespace-io-balance uses standard deviation
  • 1.0 – 2008-04-16
    - Initial release

 

Copyright

2008 Gerhard Laußer

Check_oracle_health is published under the GNU General Public License. GPL

 

Author

Gerhard Laußer (gerhard.lausser@consol.de) gladly answers questions to this plugin.

 

Translation

Thanks to Christian Lauf there is finally an english translation of this page :-)

297 Responses to “check_oracle_health”

  1. Marco Says:
    October 7th, 2009 at 12:58

    Hallo,

    ich teste gerade Ihr Tool. Ich bin sehr begeistert davon, denn es nimmt mir viel Abreit ab. Leider habe ich ein kleines Problem mit dem mode sql ./check_oracle_health –connect ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.102.103)(PORT=1521))(CONNECT_DATA=(SID=XYZ)))’ –user nagios –password oradbmon09 –mode sql –name ‘select count(*) from v$session’ –name2 sessions –warning 100 –critical 150 ERgebnis: WARNING – sessions: 21 | ‘sessions’=21;20;30

    In Nagios eingebunden bekomme ich als Status Information nur OK – sessions: Hier müßte eigentlich ja auch die Warning kommen.

    Skript:

    define command{ command_name check_oracle_per_sql command_line $USER1$/check_oracle_health –connect ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$HOSTADDRESS$)(PORT=1521))(CONNECT_DATA=(SID=$ARG3$)))’ –user $ARG1$ –password $ARG2$ –mode sql –name ‘select count(*) from v\$session’ –name2 sessions –warning 5 –critical 10 }

    define service{ use local-service host_name test2_ch service_description Count Open Sessions2 check_command check_oracle_per_sql!nagios!abc!xyz }

    Performance Daten werden auch nicht geschrieben.

    Der Mode Tablespace funktioniert bei mir.

    Gruß Marco

  2. lausser Says:
    October 8th, 2009 at 10:24

    Hallo, das liegt daran, dass Nagios empfindlich auf das Dollarzeichen in seinen Konfigdateien reagiert. Mit –name ‘select count(*) from v$$session’ in der Command-Definition sollte es funktionieren. Eine Alternative wäre, SQL-Statements, die Sonderzeichen beinhalten, vorher zu encodieren. Das geht zwar auf Kosten der Lesbarkeit, dafür muss man sich aber keine Gedanken mehr bzgl. einfache/doppelte Hochkommata, Escapen etc. machen.

    echo 'select count(*) from v$session' | check_oracle_health --mode encode select%20count%28%2A%29%20from%20v%24session

    command_line $USER1$/check_oracle_health ..... --name select%20count%28%2A%29%20from%20v%24session ....

    Gerhard

  3. Marco Says:
    October 9th, 2009 at 8:50

    Wunderbar es funktionert.

    Eine Frage habe ich noch. Kann es sein, dass für einige Abfragen keine Performancedaten geschrieben werden? z.B. für sga-shared-pool-reloads ?

    Marco

    lausser Reply:

    Das kann ich nicht nachvollziehen. $ check_oracle_health --user nagios --password $ORAPW --connect NAPRAX --mode sga-shared-pool-reloads OK - SGA shared pool reload ratio 0.85% | sga_shared_pool_reload_ratio=0.85%;1;10 $ check_oracle_health -V check_oracle_health (1.6.3) Verwendest du die neueste Version? Gerhard

    Chris Reply:

    I have a few questions:

    Does ORACLE_CONNECTED-USERS measure number of connected sessions, or processes? Is there any warning or critical threshold, or no?

    Does ORACLE_DATAFILE-IO-TRAFFIC have a warning or critical threshold associated with it? Or does it just report the traffic?

    Does ORACLE_STALE_STATISTICS have a warning or critical threshold associated with it? If so, what is it?

    Thanks in advance for your answers.

  4. Marco Says:
    October 9th, 2009 at 9:29

    Wenn ich keine Schwellwerte mitgebe, dann schreibt er nichts. Gebe ich welche an, dann werden auch Performancedaten geschrieben.

  5. Marco Says:
    October 9th, 2009 at 12:25

    Ich habe die gleiche Version. Bei mir war es so, dass er keine Performance-Daten nach “/usr/local/nagios/share/perfdata” geschrieben hat. Nachdem ich die Schwellwerte angegeben habe, ging es. Lag vielleich an mir.

    Eine Frage habe ich nun doch schon wieder: Ich möchte mir “sga-data-buffer-hit-ratio” ausgeben lassen. Ergebnis: CRITICAL – SGA data buffer hit ratio 43.73% | sga_data_buffer_hit_ratio=43.73%;98:;95: Unter SQLPLUS kommt folgendes raus: SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) “Cache Hit Ratio” FROM v$sysstat cur, v$sysstat con, v$sysstat phy WHERE cur.name = ‘db block gets’ AND con.name = ‘consistent gets’ AND phy.name = ‘physical reads’;

    Cache Hit Ratio

          86.36
    

    Interpretier ich etwas falsch?

    lausser Reply:

    “db block gets” und die anderen Werte werden stur hochgezählt. Mit deinem SQL-Statement errechnest du die Hitratio über die gesamte Laufzeit der Instanz. Der Wert wird irgendwann sehr ungenau bzw. ändert sich nur sehr langsam. Bei check_oracle_health werden die Deltas dieser Zähler (zwischen dem aktuellen und dem letzten Lauf des Plugins) zur Berechnung verwendet. So bekommst du immer einen aktuellen Wert. (Den Mittelwert im check_interval).

  6. Marco Says:
    October 15th, 2009 at 15:04

    Ich habe schon wieder eine Frage: Kann ich mit “tablespace-usage” auch einige Tablespace excluden? Ich möchte alle TBS’s außer z.B. sysaux, system überwachen. Geht das?

    lausser Reply:

    Der Parameter –regexp sorgt dafür, dass der Parameter –name (mit dem man normalerweise einen Tablespace gezielt abfragt) als regulärer Ausdruck interpretiert wird. Wenn du also –name so formulierst, dass der Pattern alle Namen matcht ausser SYSTEM und SYSAUX, dann werden die beiden nicht angezeigt.

    … –name=’^(?!(SYSTEM|SYSAUX))’ –regexp

    Gerhard

  7. Manuel Says:
    October 18th, 2009 at 3:22

    HAllo aus Spanien , Wo kann ich check_oracle_health download ?’

    DAnke

    lausser Reply:

    Ungefähr 80cm nach oben scrollen bis zur Überschrift “Download”.

  8. guzik Says:
    October 28th, 2009 at 11:35

    Hi, I’ve got a problem with check_oracle_health plugin. Status information in my Nagios: **ePN /usr/lib64/nagios/plugins/check_oracle_health: “Can’t exec “/usr/sbin/p1.pl”: Permission denied at (eval 1) line 5254,”. Few of check working fine, rest has got a problem. From console there is no problem to execute script. What can I do to correct check services?

    lausser Reply:

    Hi, please add one line in the first 10 lines of the plugin:

    # nagios: -epn
    This prevents Nagios from executing check_oracle_health with the embedded Perl interpreter. I will add this to the next release of the plugin as the default. Gerhard

    Peter Reply:

    @lausser, Hallo, ich hab den ersten Test mit diesem Skript gemacht. Der Aufruf aus der Shell funktioniert, aber wenn ich es in der Nagios-Config eintrage und Nagios den check ausführt, dann bekommen ich folgende Fehlermeldung: CRITICAL – cannot connect to Instanz.WORLD. install_driver(Oracle) failed: Can’t load ‘/usr/lib/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/Oracle/Oracle.so’ for module DBD::Oracle: libnnz10.so: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.8.8/x86_64-linux-thread-multi/DynaLoader.pm line 230. at (eval 16) line 3 Compilation failed in require at (eval 16) line 3. Perhaps a required shared library or dll isn’t installed where expected at /usr/local/nagios/libexec/check_oracle_health line 3977

    Ich interpretiere die Fehlermeldung so, das das Problem beim embedded Perl Interpreter liegt, da der nichts von den Perlmodulen weiß.

    Habe dann den Eintrage # nagios: -epn in der 3. Zeile des Skripts eingefügt, aber der Fehler kommt immer noch.

    Muss ich da Nagios neu starten oder wo könnten das Problem liegen?

    Schöne Grüße Peter

    lausser Reply:

    In der Shell hast du das Oracle-Environment (ORACLE_HOME, LD_LIBRARY_PATH etc.), aber wenn Nagios per Initscript gestartet wird, weiss der nagios-Prozess davon nichts. Am Besten ist es, wenn man

    export ORACLE_HOME=...
    export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
    ...
    in /etc/init.d/nagios einträgt. Gruß nach Zandt! (ich stamme aus Cham)

    Peter Reply:

    @lausser, Besten Dank für die schnelle Antwort.

    Des is ja guad, jetzt stammt der aus Cham … hihi Und jetzt hängst in Nürnberg rum?

    lausser Reply:

    Naa, z’Micha. Is aa ned vül schejna.

    Peter Reply:

    @Peter, nochmal Danke, hat super geklappt sorry, ich meinte natürlich München :-)

    Schöne Grüße Peter

  9. Steffen Poulsen Says:
    November 9th, 2009 at 17:29

    I tried to run check_oracle_health at an install using perl, version 5.005_03 – and it barked at me:

    Use of reserved word “our” is deprecated at check_oracle_health.pl line 9. Bareword “our” not allowed while “strict subs” in use at check_oracle_health.pl line 9. Unquoted string “our” may clash with future reserved word at check_oracle_health.pl line 9. Array found where operator expected at check_oracle_health.pl line 9, at end of line (Do you need to predeclare our?) syntax error at check_oracle_health.pl line 9, near “our @ISA ” Global symbol “@ISA” requires explicit package name at check_oracle_health.pl line 9. BEGIN not safe after errors–compilation aborted at check_oracle_health.pl line 80.

    We are very fond of your plugin and would like to use it at this install also – is there per incidence a drag-and-drop replacement for the “our @ISA”-construct that would allow the check to run at this old install also?

    Best regards, Steffen Poulsen

    lausser Reply:

    I think, this would require a major rewrite of the plugin. Can’t you run it on the Nagios server and check the database with a remote connection? Gerhard

  10. SweetBiene91 Says:
    November 11th, 2009 at 0:57

    hey ho bin so einsam jemand lust zu chattn oder so

    lausser Reply:

    Versuch’s mal hiermit: irc server : irc.irclink.net port : 6667 channel : #nagios

  11. Manfred Says:
    November 12th, 2009 at 18:17

    Gibt es eine Option (z.B. quiet) welche nur die Werte ausgeben läßt, welche ein warning oder critical ausgeben? Bei über 30 Tablespaces (bei –mode=tablespace-free) ist es fast unmöglich den zu finden, welcher das Warning ausgelöst hat. Ausserdem wird die Ausgabe in Nagios sehr unübersichtlich und viel zu lange. Ich habe schon im Source versucht, ein “if” einzubauen um die Ausgabe zu unterdrücken, bin damit aber gescheitert – da dann die Warnings selbst ausbleiben. Z.B. der orignal Nagios check der Filesysteme gibt auch nur die aus, welche warning oder critical sind.

    lausser Reply:

    Ich würde in dem Fall empfehlen, check_multi zu verwenden. Das hat ausserdem den Vorteil, dass Schwellwerte im check_multi-Konfigfile geändert werden können, ohne dass man Nagios neu starten muss. Wenn man die Tablespacenamen als Label angibt, so erhält man eine knappe Ausgabe 30 plugins checked, 1 critical (TBS_1), 1 warning (TBS_25), 0 unknown, 28 ok

  12. fsom Says:
    November 20th, 2009 at 15:54

    Tolles Script! Funktioniert soweit alles, nur bei mode=sql komme ich nicht weiter (v1.6.3): ./check_oracle_health –connect=DB –user=xxxxxx –password=yyyyyy –mode=sql –name=”select count(*) from v$session where status = ‘ACTIVE’”

    Use of uninitialized value in numeric gt (>) at /usr/lib/nagios/plugins/check_oracle_health line 3615. Use of uninitialized value in numeric gt (>) at /usr/lib/nagios/plugins/check_oracle_health line 3616. OK – select count(*) from v where status = ‘active’:

    ich bekomme nichts von dem SQL Befehl zurück. Mache ich etwas falsch ? danke, fsom

    lausser Reply:

    Du musst das Dollarzeichen entwerten. Dein Statement: from v$session where… Ausgabe: from v where… Für die Shell sieht $session wie eine Variable aus und da diese nicht existiert, macht sie einen Leerstring draus. Schreib stattdessen …from v\$session…. Wenn das SQL-Statement komplizierter ist und viele solcher Sonderzeichen enthält, kann man es auch encodieren. Dazu rufst du check_oracle_health mit dem Parameter “–mode encode” auf. Es liest dann von der Standardeingabe. Du tippst dein Statement (ohne auf Entwertung von Sonderzeichen achten zu müssen und schliesst es mit RETURN ab.

    $ check_oracle_health --mode encode
    select count(*) from v$session where status = 'ACTIVE'
    select%20count%28%2A%29%20from%20v%24session%20where%20status%20%3D%20%27ACTIVE%27
    
    Als Ausgabe erhältst du das Statement in encodierter Form, das du nun bei –name angeben kannst, ohne auf Dollar- oder irgendwelche Anführungszeichen achten zu müssen.

  13. Bas de Klerk Says:
    November 28th, 2009 at 17:55

    Hi,

    thx for your greate plugin. Saves me a lot of time!

    One small problem I’m having in version 1.6.3 is that the sga-data-bufer-hit-ratio sometimes drops to 0%… no clue why but sometimes it does. If I calculate it by hand using statement below the values are fine. If you need any add. info please let me know. For now I’ve made a workaround using mod=sql

    Regards Bas

    SELECT ((P1.value + P2.value – P3.value) / (P1.value + P2.value))*100 ratio FROM v$sysstat P1, v$sysstat P2, v$sysstat P3 WHERE P1.name = ‘db block gets’ AND P2.name = ‘consistent gets’ AND P3.name = ‘physical reads’;

  14. lausser Says:
    November 28th, 2009 at 20:48

    I use the deltas (the difference to the counter value when check_oracle_health was run last time) for the calculation. E.g. the “physical reads” i use for the calculation is “value of physical reads now – value of physical reads approx. 5 minutes ago.” This way the hitrate reflects the current state of the buffer cache. In your formula you use the counters which increased since the database was started, so it’s an average hitrate over the whole lifetime. But isn’t it more interesting to get the current hitrate? When you get 0% sometimes, it actually means a hitrate of 0% (at least during the last check_interval).This is some kind of a “negative spike”. But i understand the problem. I will introduce a parameter “–lookback” which takes a number of minutes as argument. This way, you can for example measure the hitrate during the last 30 minutes, which is pretty up to date, but gives you much smoother results.

  15. Andreas Says:
    December 12th, 2009 at 8:44

    Hallo, bei mir funktionieren nur run 50% der Abfragen: z.B. TNSPING, CON.-TIME, CON.-USERS, invalid-objects . Aber bei einigen Abfragen z.B. sga-data-buffer-hit-ratio erhalte ich in Nagios folgende Fehlermeldung: **ePN /usr/lib/nagios/plugins/check_oracle_health: printf() on closed filehandle STATE at (eval 1) line 3841,. “-epn” habe ich schon eingebaut. auf der Kommandozeile funktioniert die Abfrage aber. Danke!

    lausser Reply:

    Kann es sein, dass du check_oracle_health auf der Kommandozeile als root ausgeführt hast? Das Plugin merkt sich nämlich Zwischenergebnisse im Verzeichnis /var/tmp/check_oracle_health, welches automatisch angelegt wird. Falls das Verzeichnis root gehört, kann ein check_oracle_health-Prozess, der unter der Nagios-Kennung läuft, da nicht mehr hineinschreiben. Die Fehlermeldung weist darauf hin. Ein “chown -R nagios:nagios /var/tmp/check_oracle_health” sollte das Problem lösen.

    Andreas Reply:

    @lausser, Super! Genau daran hat’s gelegen. Vielen Dank!

  16. Erlon Says:
    December 14th, 2009 at 14:41

    Where I find the download link?

    lausser Reply:

    Scroll up until you see the topic “Download”

  17. Erlon Says:
    December 14th, 2009 at 16:54

    But does not exist the Topic Download!

  18. Erlon Says:
    December 14th, 2009 at 20:41

    Ok, I can see now. I didn’t see before because, i was seeing the page in english, and in english this link dont exists.

  19. Don Seiler Says:
    January 8th, 2010 at 1:14

    Are there plans for ASM checks, such as disk group free space (v$asm_diskgroup.usable_file_mb)?

    lausser Reply:

    No, i actually have no plans (mostly because i’m too occupied with other things). But if you look in the contribs subdirectory, you’ll find a description how you can extend check_oracle_health with your own custom modes. You simply put the code (mostly the sql stements) in a separate file which is sourced at runtime. Perhaps you want to play around with this and post the result. If it works, i will gladly add it to the core plugin.

    Don Seiler Reply:

    @lausser, I’d love to do this if I have some time later. Thanks.

  20. Millet JC Says:
    January 13th, 2010 at 11:29

    Hello All

    I’ve a small compilation error on a Solaris system. I’m not expert but think that it’s linked to my environment :

    ./configure work with success.

    make give me this error :

    Making all in plugins-scripts make: Fatal error: Don’t know how to make target Nagios/DBD/Oracle/Server/Instance/SGA/SharedPool/DictionaryCache.pm' Current working directory /tmp/check_oracle_health-1.6.3/plugins-scripts *** Error code 1 The following command caused the error: failcom='exit 1'; \ for f in x $MAKEFLAGS; do \ case $f in \ *=* | --[!k]*);; \ *k*) failcom='fail=yes';; \ esac; \ done; \ dot_seen=no; \ target=echo all-recursive | sed s/-recursive//; \ list='plugins-scripts t'; for subdir in $list; do \ echo "Making $target in $subdir"; \ if test "$subdir" = "."; then \ dot_seen=yes; \ local_target="$target-am"; \ else \ local_target="$target"; \ fi; \ (cd $subdir && make $local_target) \ || eval $failcom; \ done; \ if test "$dot_seen" = "no"; then \ make "$target-am" || exit 1; \ fi; test -z "$fail" make: Fatal error: Command failed for targetall-recursive’

    Millet JC Reply:

    @Millet JC, Making all in plugins-scripts make: Fatal error: Don’t know how to make target Nagios/DBD/Oracle/Server/Instance/SGA/SharedPool/DictionaryCache.pm' Current working directory /tmp/check_oracle_health-1.6.3/plugins-scripts *** Error code 1 The following command caused the error: failcom='exit 1'; \ for f in x $MAKEFLAGS; do \ case $f in \ *=* | --[!k]*);; \ *k*) failcom='fail=yes';; \ esac; \ done; \ dot_seen=no; \ target=echo all-recursive | sed s/-recursive//; \ list='plugins-scripts t'; for subdir in $list; do \ echo "Making $target in $subdir"; \ if test "$subdir" = "."; then \ dot_seen=yes; \ local_target="$target-am"; \ else \ local_target="$target"; \ fi; \ (cd $subdir && make $local_target) \ || eval $failcom; \ done; \ if test "$dot_seen" = "no"; then \ make "$target-am" || exit 1; \ fi; test -z "$fail" make: Fatal error: Command failed for targetall-recursive’

    lausser Reply:

    Looks like your tar-command does not support filenames which exceed 100 characters (i think SuSE has such a tar). Instead of the tar.gz please download the shar.gz and unpack it with [sourcecode]cat check_oracle_health-xxx.shar.gz | gzip -d | sh[/sourcecode]

  21. Rascal Says:
    January 25th, 2010 at 20:44

    Hallo, ich bin kein Datenbänker, sondern nur “Überwacher”, daher meine Frage: Gibt es eine Möglichkeit den Datenbank-Connect durch das Plugin zu erhalten? Durch den ständigen Auf- und Abbau der Verbindung, schwellen die Logdateien auf der DB an? Oder muss da was an der Datenbank-Config gemacht werden?

  22. lausser Says:
    January 26th, 2010 at 13:13

    Mit http://sqlrelay.sourceforge.net/ kann man einen Proxy laufen lassen, der die Verbindung aufrecht hält. Dadurch entfallen dann die Login-Meldungen in der Logdatei.

    check_oracle_health --method sqlrelay --connect <proxy-ip>:<proxy-port> --username <proxy-user> --password <proxy-password> ...

  23. Frank Says:
    February 15th, 2010 at 18:15

    Hallo, auf Kommandozeile funktioniert die Abfrage als User nagios. Im Nagios selber kommt die Fehlermeldung: ePN failed to compile /usr/lib/nagios/plugins/check_oracle_health “Missing right curly or square bracket at (eval 18) line 4193, at end of line syntax error at (eval 18) line 4200, at EOF at /usr/lib/nagios/p1.pl line 155″

    Die Zeile “# nagios: -epn” steht im Skript bereits drin.

    Kann es daran liegen dass Nagios noch v.2.9 ist? Gibt es einen Weg das unter dieser Version zum laufen zu bringen?

    lausser Reply:

    Die selektive Abschaltung mit -epn gibt es erst ab der Version 3. Leider, bleibt also nur ein Upgrade auf 3.x oder der komplette Verzicht auf ePN.

  24. John Tomawski Says:
    February 16th, 2010 at 23:30

    Be sure to set the –environment flag when required. The flag can be used to set things such as TNS_ADMIN, etc.

    Hopefully this comment saves someone 2 hours… sigh

    ex. –environment TNS_ADMIN=’/usr/lib/oracle/bleh’

  25. Aldo Says:
    February 19th, 2010 at 12:52

    when running the following command:

    ./check_oracle_health –connect REMOTE –username $ORAUSER –password $ORAPWD –mode tablespace-usage –tablespace USERS

    I get the following error message.

    Use of uninitialized value in split at /usr/lib/nagios/plugins/check_oracle_health line 3924. bumm Can’t call method “execute” on an undefined value at /usr/lib/nagios/plugins/check_oracle_health line 4230.

    Can’t use an undefined value as an ARRAY reference at /usr/lib/nagios/plugins/check_oracle_health line 4242.

    and now I’m clue less what todo? can you assist me on this one

    thanks in advance

    lausser Reply:

    Did you give the necessary privileges to your ORAUSER?

    CREATE USER nagios IDENTIFIED BY oradbmon; 
    GRANT CREATE SESSION TO nagios;
    GRANT SELECT any dictionary TO nagios;
    GRANT SELECT ON V_$SYSSTAT TO nagios;
    GRANT SELECT ON V_$INSTANCE TO nagios;
    GRANT SELECT ON V_$LOG TO nagios;
    GRANT SELECT ON SYS.DBA_DATA_FILES TO nagios;
    GRANT SELECT ON SYS.DBA_FREE_SPACE TO nagios;
    You also can create an empty file /tmp/check_oracle_health.trace with the touch-command. As long as this file exists, check_oracle_health will write debugging messages into it. You should see the sql statements sent to the database server and the responses. Maybe this gives you an idea what’s wrong.

    Aldo Reply:

    @lausser, Hi Lausser, it works fantastic now! This is a great plugin.

  26. Hans-Jürgen Says:
    February 22nd, 2010 at 11:03

    Hallo,

    wir benutzen check_oracle_health seit längerem und sind sehr zufrieden damit. Vielen Dank dafür. Für die Tablespaces, bei denen auto-extent eingeschaltet ist, möchten wir die Überwachung von tablespace-usage auf tablespace-can-allocate-next ändern. Wird dabei sowohl überprüft, ob noch genügend Platz ist als auch ob MAX_EXTENT bereits erreicht ist?

    lausser Reply:

    Hallo, max_extent wird meines Wissens nach nicht angeschaut. Wenn man mit touch /tmp/check_oracle_health.trace eine leere Datei anlegt (beschreibbar vom Nagios-User), dann werden dort die angesetzten SQL-Statements und deren Resultate reinprotokolliert.

    Günter Reply:

    @lausser, wird es in Zukunft eine Möglichkeit geben bei tablespace-usage autoextent Tablespaces auszuschließen?

    Günter Reply:

    @Günter, hat sich erledigt. Hab gerade im Trace File gesehen, dass Autoextent Tablespaces berücksichtigt werden, d.h. es wir die max. Größe verwendet.

    lausser Reply:

    Du kannst auch bestimmte Tablespaces per regulärem Ausdruck ausschliessen:

    --name='^(?!(TABLESPACE1$)|(TABLESPACE2$)|(TABLESPACE3$))' --regexp
    bedeutet: alles, ausser TABLESPACE1,TABLESPACE2,TABLESPACE3
  27. angry_admin Says:
    February 24th, 2010 at 13:49

    http://ideas.nagios.org/a/dtd/22035-3955

  28. Rik Says:
    February 26th, 2010 at 16:21

    Thanks you Gerhard for an excellent plugin. Here is a tiny correction on the documentation on this page. –method accepts two arguments: dbi (not tns) or sqlplus. Or am I misinterpreting things?

    lausser Reply:

    Thanks! “tns” was how i named it in a very early phase. Later it was replaced by the less misleading “dbi”.

  29. Thomas Says:
    March 4th, 2010 at 11:57

    Hallo,

    hört sich ja alles sehr schön an. Ich würde es ja auch gerne mal ausprobieren, aber ich finde leider nirgends einen Download Link (auch nicht mittlerweile 1,20 m weiter oben). Habe ich etwas übersehen?

    Danke, Thomas

    lausser Reply:

    du bist vermutlich auf der englischen Seite gelandet, die es nicht gibt (bei der allerdings die Kommentare angezeigt werden) Der Download-Link ist auf dieser Seite: http://labs.consol.de/lang/de/nagios/check_oracle_health/

  30. Thomas Says:
    March 4th, 2010 at 17:27

    Hallo,

    habe leider Schwierigkeiten, den Oracle-Instant-Client zu installieren. Weiß vielleicht jemand eine Seite, die sich mit dem Thema beschäftigt?

    Vielen Dank, Thomas

    Max Reply:

    @Thomas, Hallo Thomas, schaue mal hier, http://samushka.blogspot.com/2009/04/installing-oracle-sqlplus-in-ubuntu.html

  31. Steffen Poulsen Says:
    March 25th, 2010 at 18:09

    When using –mode=tablespace-remaining-time we have the experience, that on some machines it is somewhat slow. I.e. on the machine below it takes more than 60 seconds to process 34 tablespaces.

    Apparantly the processing of each status-file takes two seconds to process at this particular machine (some trace output pasted below) – and as this machine has a new tablespace automaticaly added each week, this is not going to get any better by itself any time soon :-)

    We are aware that we could split the tablespace checking into separate checks and do each tablespace individually – but if you would happen to have an idea for making this mode run a bit faster, so that all tablespaces could be checked inside a timeframe of say 60 seconds, that would be a clear number 1? :-)

    Best regards, Steffen Poulsen

    $ uname -a SunOS 5.10 Generic_141414-07 sun4v sparc SUNW,SPARC-Enterprise-T5220

    ./check_oracle_health –mode=tablespace-remaining-time –lookback=15 –warning=10: –critical=2: …

    Thu Mar 25 15:10:52 2010: loaded 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:10:52 2010 Thu Mar 25 15:10:52 2010: trimmed to 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:10:52 2010 Thu Mar 25 15:10:54 2010: loaded 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:10:54 2010 Thu Mar 25 15:10:54 2010: trimmed to 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:10:54 2010 Thu Mar 25 15:10:56 2010: loaded 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:10:56 2010 Thu Mar 25 15:10:56 2010: trimmed to 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:10:56 2010 Thu Mar 25 15:10:58 2010: loaded 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:10:58 2010 Thu Mar 25 15:10:58 2010: trimmed to 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:10:58 2010 Thu Mar 25 15:11:00 2010: loaded 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:00 2010 Thu Mar 25 15:11:00 2010: trimmed to 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:00 2010 Thu Mar 25 15:11:02 2010: loaded 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:02 2010 Thu Mar 25 15:11:02 2010: trimmed to 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:02 2010 Thu Mar 25 15:11:04 2010: loaded 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:04 2010 Thu Mar 25 15:11:04 2010: trimmed to 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:04 2010 Thu Mar 25 15:11:06 2010: loaded 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:06 2010 Thu Mar 25 15:11:06 2010: trimmed to 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:06 2010 Thu Mar 25 15:11:08 2010: loaded 5822 data sets from Thu Feb 25 11:00:46 2010 – Thu Mar 25 15:11:08 2010 Thu Mar 25 15:11:08 2010: trimmed to 5822 data sets from Thu Feb 25 11:00:46 2010 – Thu Mar 25 15:11:08 2010 Thu Mar 25 15:11:09 2010: loaded 3806 data sets from Thu Mar 4 11:00:53 2010 – Thu Mar 25 15:11:09 2010 Thu Mar 25 15:11:09 2010: trimmed to 3806 data sets from Thu Mar 4 11:00:53 2010 – Thu Mar 25 15:11:09 2010 Thu Mar 25 15:11:10 2010: loaded 1790 data sets from Thu Mar 11 11:00:59 2010 – Thu Mar 25 15:11:10 2010 Thu Mar 25 15:11:10 2010: trimmed to 1790 data sets from Thu Mar 11 11:00:59 2010 – Thu Mar 25 15:11:10 2010 Thu Mar 25 15:11:10 2010: loaded 5 data sets from Mon Mar 22 14:41:08 2010 – Thu Mar 25 15:11:10 2010 Thu Mar 25 15:11:10 2010: trimmed to 5 data sets from Mon Mar 22 14:41:08 2010 – Thu Mar 25 15:11:10 2010 Thu Mar 25 15:11:10 2010: no historical data found Thu Mar 25 15:11:11 2010: loaded 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:11 2010 Thu Mar 25 15:11:11 2010: trimmed to 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:11 2010 Thu Mar 25 15:11:13 2010: loaded 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:13 2010 Thu Mar 25 15:11:13 2010: trimmed to 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:13 2010 Thu Mar 25 15:11:15 2010: loaded 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:15 2010 Thu Mar 25 15:11:15 2010: trimmed to 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:15 2010 Thu Mar 25 15:11:17 2010: loaded 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:17 2010 Thu Mar 25 15:11:17 2010: trimmed to 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:17 2010 Thu Mar 25 15:11:19 2010: loaded 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:19 2010 Thu Mar 25 15:11:19 2010: trimmed to 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:19 2010 Thu Mar 25 15:11:21 2010: loaded 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:21 2010 Thu Mar 25 15:11:21 2010: trimmed to 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:21 2010 Thu Mar 25 15:11:23 2010: loaded 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:23 2010 Thu Mar 25 15:11:23 2010: trimmed to 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:23 2010 Thu Mar 25 15:11:25 2010: loaded 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:25 2010 Thu Mar 25 15:11:25 2010: trimmed to 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:25 2010 Thu Mar 25 15:11:27 2010: loaded 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:27 2010 Thu Mar 25 15:11:27 2010: trimmed to 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:27 2010 Thu Mar 25 15:11:29 2010: loaded 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:29 2010 Thu Mar 25 15:11:29 2010: trimmed to 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:29 2010 Thu Mar 25 15:11:31 2010: loaded 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:31 2010 Thu Mar 25 15:11:31 2010: trimmed to 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:31 2010 Thu Mar 25 15:11:33 2010: loaded 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:33 2010 Thu Mar 25 15:11:33 2010: trimmed to 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:33 2010 Thu Mar 25 15:11:35 2010: loaded 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:35 2010 Thu Mar 25 15:11:35 2010: trimmed to 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:35 2010 Thu Mar 25 15:11:37 2010: loaded 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:37 2010 Thu Mar 25 15:11:37 2010: trimmed to 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:37 2010 Thu Mar 25 15:11:39 2010: loaded 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:39 2010 Thu Mar 25 15:11:39 2010: trimmed to 6419 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:39 2010 Thu Mar 25 15:11:41 2010: loaded 6418 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:41 2010 Thu Mar 25 15:11:41 2010: trimmed to 6418 data sets from Tue Feb 23 09:15:44 2010 – Thu Mar 25 15:11:41 2010 Thu Mar 25 15:11:42 2010: found 2027 usable data sets since Wed Mar 10 15:11:42 2010 Thu Mar 25 15:11:42 2010: found 2028 usable data sets since Wed Mar 10 15:11:42 2010 Thu Mar 25 15:11:42 2010: found 2028 usable data sets since Wed Mar 10 15:11:42 2010 Thu Mar 25 15:11:42 2010: found 2028 usable data sets since Wed Mar 10 15:11:42 2010 Thu Mar 25 15:11:42 2010: found 2028 usable data sets since Wed Mar 10 15:11:42 2010 Thu Mar 25 15:11:42 2010: found 2028 usable data sets since Wed Mar 10 15:11:42 2010 Thu Mar 25 15:11:42 2010: found 2028 usable data sets since Wed Mar 10 15:11:42 2010 Thu Mar 25 15:11:42 2010: found 2028 usable data sets since Wed Mar 10 15:11:42 2010 Thu Mar 25 15:11:42 2010: found 2028 usable data sets since Wed Mar 10 15:11:42 2010 Thu Mar 25 15:11:42 2010: found 2028 usable data sets since Wed Mar 10 15:11:42 2010 Thu Mar 25 15:11:42 2010: found 2028 usable data sets since Wed Mar 10 15:11:42 2010 Thu Mar 25 15:11:43 2010: found 2028 usable data sets since Wed Mar 10 15:11:43 2010 Thu Mar 25 15:11:43 2010: found 2028 usable data sets since Wed Mar 10 15:11:43 2010 Thu Mar 25 15:11:43 2010: found 2028 usable data sets since Wed Mar 10 15:11:43 2010 Thu Mar 25 15:11:43 2010: found 2028 usable data sets since Wed Mar 10 15:11:43 2010 Thu Mar 25 15:11:43 2010: found 2028 usable data sets since Wed Mar 10 15:11:43 2010 Thu Mar 25 15:11:43 2010: found 1 usable data sets since Wed Mar 10 15:11:43 2010 Thu Mar 25 15:11:43 2010: found 6 usable data sets since Wed Mar 10 15:11:43 2010 Thu Mar 25 15:11:43 2010: found 1791 usable data sets since Wed Mar 10 15:11:43 2010 Thu Mar 25 15:11:43 2010: found 2028 usable data sets since Wed Mar 10 15:11:43 2010 Thu Mar 25 15:11:43 2010: found 2028 usable data sets since Wed Mar 10 15:11:43 2010 Thu Mar 25 15:11:43 2010: found 2028 usable data sets since Wed Mar 10 15:11:43 2010 Thu Mar 25 15:11:43 2010: found 2028 usable data sets since Wed Mar 10 15:11:43 2010 Thu Mar 25 15:11:43 2010: found 2028 usable data sets since Wed Mar 10 15:11:43 2010 Thu Mar 25 15:11:43 2010: found 2028 usable data sets since Wed Mar 10 15:11:43 2010 Thu Mar 25 15:11:43 2010: found 2028 usable data sets since Wed Mar 10 15:11:43 2010 Thu Mar 25 15:11:43 2010: found 2028 usable data sets since Wed Mar 10 15:11:43 2010 Thu Mar 25 15:11:43 2010: found 2028 usable data sets since Wed Mar 10 15:11:43 2010 Thu Mar 25 15:11:43 2010: found 2028 usable data sets since Wed Mar 10 15:11:43 2010 Thu Mar 25 15:11:44 2010: DESTROY DBD::Oracle::Server::Database::Tablespace with handle null null

    lausser Reply:

    You’re right. 2 seconds is quite long. In /var/tmp/check_oracle_health you should find several files named tablespace-remaining-time_*

    Please mail me one of these files. I’ll have a look at it.

    Steffen Poulsen Reply:

    Thank you very much for the patch you sent us, run time is down from 65 to 11 seconds at this particular host now :-)

    lausser Reply:

    You’re welcome. If anybody stumbled upon the same problem….i’ll release a version with this patch soon.

    Khadija Reply:

    @Steffen Poulsen, Can you plz let me know the patch that you suggest Steffen?

    Regards, Khadija

    lausser Reply:

    I forgot to release this update. New version of check_oracle_health is coming asap.

  32. Frank Says:
    April 14th, 2010 at 16:03

    Hello,

    We used this plugin (1.5) for some months now and everything worked fine, but since yesterday we receive the message “CRITICAL – connection could not be established within 60 seconds”. Nothing has changed on the plugin, nothing has changed on the network, nothing has changed on the machines nor on nagios/centreon?

    I don’t have a clue where to look to resolve this problem. Does it sound familiar to somebody?

    Regards, Frank

    lausser Reply:

    can you connect with the sqlplus command? (executed on the Nagios server)

  33. Steffen B Says:
    April 15th, 2010 at 9:41

    Hallo,

    erstmal großes Lob an euch, ein super Plugin was ihr dort kreiert habt. Wir nutzen es komplett zur Oracle Überwachung unserer Kundensysteme.

    Seit heute hab ich aber ein Problem wo ich nicht mehr weiter weiß. Situation ist folgende:

    1DB Server – darauf zwei Datenbanken mit jeweils einem Schema – beide mit dem gleichen DB Stand 10.2.0.4 und dem gleichen Schemanamen.

    Ich möchte mit dem Plugin die “Usage” des Tablespaces ermitteln. –mode=tablespace-usage Die Syntax auf der Kommandozeile ist die gleiche, es ändert sich nur der TNSAlias für die Datenbank. Und bei der einen DB funkioniert es ohne Probleme und bei der anderen DB zeigt er mir folgenden Fehler:

    Use of uninitialized value in split at /usr/local/nagios/libexec/check_oracle_health line 3924. bumm Can’t call method “execute” on an undefined value at /usr/local/nagios/libexec/check_oracle_health line 4230.

    Can’t use an undefined value as an ARRAY reference at /usr/local/nagios/libexec/check_oracle_health line 4242.

    Wie schon vorher hier empfohlen, hab ich das check_oracle_health.trace file angelegt und mir ist als einziges aufgefallen, dass ein anderes SQL Statement abgesetzt wird. Aber warum? Die Datenbanken sind gleich isntalliert und auf dem Selben Server, also kann es nicht mit der DB zu tun ahben oder mit dem Betriebssystem, oder?

    Wäre für jede Hilfe Dankbar.

    lausser Reply:

    “..dass ein anderes SQL Statement abgesetzt wird.” Es wäre natürlich hilfreich, diese beiden unterschiedlichen Statements sehen zu können.

  34. Frank Says:
    April 15th, 2010 at 10:10

    I think you’re right, it seems to be a problem with sqlplus.

    As root user I can connect with sqlplus, as Nagios user I cannot connect. I think we have to find out what is changed there…

  35. Frank Says:
    April 15th, 2010 at 13:20

    We had to relocate the nagios server today, so we had to restart the server. Problem is now solved.

  36. Hans Wolters Says:
    April 23rd, 2010 at 14:40

    Dear all,

    Great Plugin. Started to configure it this week and currently for some databases I already have nearly all of the checks possible with the default options.

    One question remains for me. If I have overlooked this in the documentation (yes, I can read German) the please let me know.

    Situation:

    We have several machines with more then one database per service id. Would it be possible to return the SID and database name with the return string of nagios (given by the plugin written in perl). This will enable me to use short service descriptions on those machines and setup service entries with multiple databases/sids on one machine. Maybe even with a parameter so people using only one database can skip the options.

    I could hack it into the source my self but I can imagine I am not the only one who would like that feature.

    Freundliche Grusse,

    Hans Wolters

    lausser Reply:

    Hi, i’ll have a look at it.

  37. Geoff Sears Says:
    April 30th, 2010 at 23:53

    Hi. I’m having trouble making a connection as sysdba, though I understand this should be possible.

    Would you post an example of how to make it work?

    Thanks,

    -geoff

    lausser Reply:

    check_oracle_health –connect sysdba@ …

    Geoff Sears Reply:

    @lausser,

    That’s what I can not get to work. Works fine with connect=host:port//service or connect=//host:port/service

    But, If I use:

    connect=sysdba@host:port/service or connect=sysdba@//host:port/service

    results in ORA-12154: TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach)

    I believe that getting a sysdba connection with DBI/DBD::Oracle requires setting a connection attribute ora_session_mode => ORA_SYSDBA ; just passing that string “sysdba@host:port/service” as the data source won’t do it.

    Geoff Sears Reply:

    @Geoff Sears,

    ok, I finally sat down and read through the code: sysdba@… is only supported for sqlplus connections. I hacked it so tns connections are possible.

    lausser Reply:

    Which version did you use? I looked into the source (in my git repository) and found (in the tns section)

          my $connecthash = { RaiseError => 0, AutoCommit => 0, PrintError => 0 };
          if ($self->{username} eq "sys" || $self->{username} eq "sysdba") {
            $connecthash = { RaiseError => 0, AutoCommit => 0, PrintError => 0,
                  #ora_session_mode => DBD::Oracle::ORA_SYSDBA
                  ora_session_mode => 0x0002  };
            $dsn = sprintf "DBI:Oracle:";
          }
    Isn’t that correct? How does your changes look like?
  38. Thomas Says:
    May 6th, 2010 at 9:09

    I am running nagios 1.2 and the service i have created with check_oracle_health won’t start. The service still remains on pending. When i run the check on the servers shell it works perfectly. Might that be a problem with nagios 1.x? Should I update to nagios 2 or 3?

    lausser Reply:

    I don’t think this has to do with the Nagios version. Can’t you force scheduling of the service through the service detail page? Upgrading to 3.x is a good idea anyway.

  39. Björn Says:
    May 17th, 2010 at 11:54

    Hello,

    for some databases we are using your health check. One of the installations is using a Dataguard Environment. When we configure checks for the standby, we get a critical error, as no connection is allowed (“ORA-01033: ORACLE initialization or shutdown in progress”).

    For our other oracle monitors we excluded the ORA-01033 and give an OK-State with a comment (“OK – Login Denied, the DB is in Standby Mode – this Check only works for Primary DB’s “).

    Could you implement an exeption handling for the ORA-01033 to allow the same Nagios config for Primary and Standby Database?

    lausser Reply:

    ORA-01033 can be a sign of serious problems, for example when a corrupted database was restartet (ORA-10567 et al can be found in the alertlog), hence ignoring this error message is not an option.

    Michael Reply:

    @lausser, Oracle Dataguard tnsping is not working anymore. getting the same error initialization or shutdown in progress. In Version 1.6.2 teh mode tnsping was working for closed Databases.

    lausser Reply:

    How do you call the plugin and what’s the error message (in 1.6.2 and 1.6.4)?

  40. Antonio Romero Says:
    May 25th, 2010 at 17:07

    Hi,

    I have installed the check_oracle_health on my nagios system in order to monitor several Oracle DB’s. All works fine, except one thing. When I ask to the DB for the space used by the tablespace the info that the plugin returns is diferent from the info that I can get by a Oracle query in the Oracle Manager. Can you give me some help about this issue?

    Thank you in advance for your help!

    Toni.

    lausser Reply:

    Oracle tools usually set two values into relation: used space and allocated space. Now if you use autoallocation, the latter value may grow. When used:allocated is near 100%, autoallocation happens, allocated space suddenly grows and used:alloc percentage drops. This means, you could get an alert from nagios because the crit.threshold has been reached. Then, after the autoallocation, the usage drops below the threshold again. False alert. That’s why check_oracle_health calculates the usage percentage from used:max_allocatable

  41. Thomas Says:
    May 26th, 2010 at 11:32

    Hallo,

    zunächst mal danke für das hilfreiche Plugin. Ich habe allerdings noch Probleme es zur Zusammenarbeit mit Nagios (3.2.0) zu überreden. Ich betreibe den Nagiosserver auf Ubuntu und habe den Oracle Instantclient installiert. Das Plugin funktioniert von der Konsole, als User nagios gestartet, ohne Probleme. Als service in Nagios mit folgendem command:

    command_line $USER1$/check_oracle_health –connect rebmasc.world –user dbo –password xxx –mode tnsping

    bekomme ich immer die Fehlermeldung:

    cannot connect to rebmasc.world. ORA-12154: TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach)

    Die Variablen ORACLE_HOME, TNS_ADMIN usw. habe ich in der bash.bashrc für alle korrekt gesetzt und die DB kommt auch in der dort vorhandenen tnsnames.ora vor. Wie gesagt in der Konsole der Maschine ohne Probleme.

    Ich habe schon diverse alternative command probiert (–environment; –method), allerdings ohne Erfolg. Ich kann keinen Fehler finden. Was mache ich falsch?

    lausser Reply:

    Die Environmentvariablen müssen im init-Script von Nagios gesetzt werden. Dateien wie .bashrc werden beim Systemstart nicht gelesen.

    Thomas Reply:

    @lausser, Diesen Hinweis habe ich dann gestern auch im Nagiosforum gefunden. Wenn ich die Variablen in /etc/init.d/nagios setze klappt alles. Danke für den Hinweis.

  42. Antonio Romero Says:
    May 28th, 2010 at 21:56

    Please Lausser, Can you answer my post above?, number 40.

    Thanks!

  43. Dennis Says:
    June 8th, 2010 at 16:07

    Hallo, gibt es eine Möglichkeit die Flash Recovery Area zu überwachen? Bzgl. Füllstand.

    Gruß, Dennis

    lausser Reply:

    Nein, das ist nicht eingebaut. Aber vielleicht wäre sowas hilfreich:

    --mode sql --name 'select max(percent_space_used) from v$flash_recovery_area_usage' --warning 80 --critical 90
  44. Hamza Says:
    June 17th, 2010 at 12:15

    Hi there

    I love your check oracle plugin, it does everything I want.

    Is there any way to specify multiple DB names using some sort of delimiter.

    I currently have a setup as such.

    • In .profile I have export NAGIOS__SERVICEORACLE_SID=/usr/lib/oracle/11.2/client/network/admin/tnsnames.sh

    which basically does a cat of the tnsnames.ora and pulls out all the sids for me.

    What I would like to do is be able to run the check_oracle_health in this way

    check_oracle_health –connect $NAGIOS__SERVICEORACLE_SID:$NAGIOS__SERVICEORACLE_SID –username nagios –password nagios –mode tnsping

    where the : is any delimiter to which can specify multiple DB names.

    Please help.

    Thank you Hamza Maal

    lausser Reply:

    That’s not possible. You can only check databases one at a time. If you want multiple checks inside one single service, you might want to give check_multi a try. http://www.my-plugin.de/wiki/projects/check_multi/discussion

  45. Tim Says:
    June 17th, 2010 at 20:27

    I have an odd problem with this plugin. It works fine, but Nagios reports any response as a warning. From the command line, I’ll get:

    OK – 0.22 seconds to connect as MONITOR | connection_time=0.2193;3;8

    But Nagios shows the service in yellow and the log has:

    SERVICE ALERT: myhost;Oracle mySID Connect;WARNING;HARD;3;OK – 0.14 seconds to connect as MONITOR

    Why is it showing as an alert when the connect time is within the correct range?

    lausser Reply:

    Strange… What about the thresholds? From your command line example i see you set –warning 3 –critical 8 (without these extra parameters it would be 1 and 5 by default) Did you set thresholds also in the service/command definition? When you get such a WARNING, please click on “Service Details” and look at the performance data. Which thresholds do you see there?

    Tim Reply:

    @lausser,

    I think I added the warning/critical params just in case that might affect the display. The performance data looks like this:

    Current Status: WARNING (for 3d 22h 57m 46s) Status Information: OK – 0.23 seconds to connect as MONITOR Performance Data: connection_time=0.2339;3;8 Current Attempt: 3/3 (HARD state) Last Check Time: 06-21-2010 13:06:33 Check Type: ACTIVE

    Interestingly, I also set this up in Icinga and it does the same thing.

    lausser Reply:

    Very strange…the last lines of the plugin are:

    printf "%s - %s", $ERRORCODES{$nagios_level}, $nagios_message;
    printf " | %s", $perfdata if $perfdata;
    printf "&#92;n";
    exit $nagios_level;
    so if $ERRORCODES{$nagios_level} is “OK” (which is in the output), then the exit code $nagios_level must be 0. Can you reset the service to OK with “submit passive checkresult”? Did you see a warning from the first moment when you configured this service? Or has it been OK before?

    Tim Reply:

    @lausser,

    I can send it an OK passive result and it will switch to “OK”, but usually changes right back to a yellow warning.

    I’ve tried enabling and disabling passive checks, event handling, but to no effect.

    One thing I did notice is that it almost always shows:

    Current Attempt: 3/3 (HARD state)

    As if maybe it didn’t pass the first 2 checks. Running from the command line I can submit it repeatedly and I get OK results each time. It’s an odd thing. After all of this testing, I think the script works fine, it appears to be more of a Nagios problem.

    lausser Reply:

    Just to be absolute sure, you can add an extra line at the end of the plugin:

    printf "%s - %s", $ERRORCODES{$nagios_level}, $nagios_message;
    printf " | %s", $perfdata if $perfdata;
    printf "\n";
    printf "i will definitively exit with %d\n", $nagios_level;
    exit $nagios_level;

    The level surely won’t change between the printf and the exit.

  46. IT-COW | Icinga: Oracle-Datenbanken abfragen Says:
    June 19th, 2010 at 8:53

    [...] Es gibt ein PlugIn für Icinga/Nagios, das es erlaubt den Status von Oracle-Datenbanken übers Netzwerk abzufragen. Das Tool nennt sich oracle_check_health und ist wie check_logfiles von Herrn Lausser von der Firma ConSol entwickelt worden – dies ist die Homepage des Projekts: Link. [...]

  47. Hamza Says:
    June 24th, 2010 at 18:00

    Hi

    I seem to be having some trouble setting the warning and critical thresholds for checking tablespace free.

    Could you please advise on the correct syntax for

    check_oracle_health -t 480 –connect db1 –username nagios –password nagios –mode tablespace-free –warning 85 –critical 90

    Please help.

    lausser Reply:

    I assume you want a warning if less than 15% are free and a critical if less than 10% are free. Please use ‘:’ which is the correct syntax for ‘less than’-thresholds.

    --mode tablespace-free --warning 15: --critical 10:
  48. Rija Says:
    July 2nd, 2010 at 15:55

    Hello, I have problem when I execute line command using tablespace-io-balance to check datafiles under all tablespaces. The output is CRITICAL – unable to aquire tablespace info. Can You help me please?

  49. Rija Says:
    July 2nd, 2010 at 15:56

    Hello, I have problem when I execute line command using tablespace-io-balance to check datafiles under all tablespaces. The output is CRITICAL – unable to aquire tablespace info. Could You help me please?

    lausser Reply:

    Do you see this message only with mode tablespace-io-balance? What about –mode list-tablespaces ?

    Maybe you forgot to set the right privileges?

    CREATE USER nagios IDENTIFIED BY oradbmon; 
    GRANT CREATE SESSION TO nagios;
    GRANT SELECT any dictionary TO nagios;
    GRANT SELECT ON V_$SYSSTAT TO nagios;
    GRANT SELECT ON V_$INSTANCE TO nagios;
    GRANT SELECT ON V_$LOG TO nagios;
    GRANT SELECT ON SYS.DBA_DATA_FILES TO nagios;
    GRANT SELECT ON SYS.DBA_FREE_SPACE TO nagios;
  50. Rija Says:
    July 2nd, 2010 at 16:38

    I see this message with tablespace-io-balance only. I’ve executed: check_oracle_health –connect SID –user nagios –password oradbmon –mode tablespace-io-balance. list-tablespaces works, the output gives list and message “OK – have fun” in the end. All privileges are OK for user nagios. Thank You for your help!

    lausser Reply:

    Edit the plugin and search for “sub init_datafiles”, then search for “iobalance” and finally search for “datafileresults”. Now you found the line

    my @datafileresults = $params{handle}-&gt;fetchall_array($sql, $params{selectname}, $params{selectname});
    Please change the $params{selectname} to $params{tablespace} (2 times) and try again.

    Rija Reply:

    @lausser,

    I followed your tips and now everything works. Thank you very much for your help.

    Rija Reply:

    @Rija, Oups! Sorry, it doesn’t work for oracle installed on windows machine, the same error message appear . Have you got another solution for that? Thank you.

    Rija Reply:

    @lausser, Oups! Sorry, it doesn’t work for oracle installed on windows machine, the same error message appear . Have you got another solution for that? Thank you.

    lausser Reply:

    Strange…unfortunately i don’t have a windows db-server. Please execute the following statement with sqlplus:

    SELECT file_name,  SUM(phyrds), SUM(phywrts)
    FROM dba_data_files, v$filestat
    WHERE tablespace_name = UPPER('USERS')
      AND file_id=file# GROUP BY tablespace_name, file_name

    Rija Reply:

    @lausser, Hello! I ran “GRANT SELECT ON V_$filestat TO nagios;” and it works. Thank you… I have another problem, I’d like to modify default values of critical and warning level when execute sga-data-buffer-hit-ratio or sga-library-cache-hit-ratio or sga-dictionary-cache-hit-ratio but I still have the error message that appears critical even value is 100%. I’he executed the following command: check_oracle_health –connect SID –mode sga-data-buffer-hit-ratio –warning 80 –critical 90 CRITICAL – SGA data buffer hit ratio 100.00% | sga_data_buffer_hit_ratio=100.00%;80;90

    Rija Reply:

    @Rija, Sorry! The command is: check_oracle_health –connect SID –mode sga-data-buffer-hit-ratio –warning 90 –critical 80 The same error message appears…

    lausser Reply:

    These are “less than”-thresholds. According to the plugin developer guidelines, you must add a “:”. So –warning <less than 90> is written as –warning 90:

  51. roger Says:
    July 2nd, 2010 at 18:38

    is normal what seg_top_10 metrics is including to PERFSTAT user, also:

    this my top 10 ……….

    PERFSTAT 2154 row lock waits 1 …….. PERFSTAT 1450 row lock waits 2 ……….. PERFSTAT 572 row lock waits 3 ……… PERFSTAT 466 row lock waits 4 ……… PERFSTAT 446 row lock waits 5 ……….. PERFSTAT 382 row lock waits 6 ………… PERFSTAT 350 row lock waits 7 ………… PERFSTAT 288 row lock waits 8 ………….

    PERFSTAT 246 row lock waits 9 ………….. PERFSTAT 191 row lock waits 10

    roger Reply:

    @roger, why distinct value for the same user ?

  52. Hamza Maal Says:
    July 14th, 2010 at 10:06

    Hi

    I am trying to run a sql statement using –mode sql but it does not seem to work. I have tried using the encode but it still comes up with errors

    Original statement /usr/lib/nagios/plugins/check_oracle_health –connect mlc247 –username dbuser –password dbpass –mode sql SELECT TO_CHAR(NEXT_TIME, ‘DD-MON-YYYY HH24:MI:SS’) FROM V$ARCHIVED_LOG where sequence# = (select max(sequence#) from v$archived_log where applied = ‘YES’)

    After encoding

    /usr/lib/nagios/plugins/check_oracle_health –connect mlc247 –username dbuser –password dbpass –mode sql SELECT%20TO%5FCHAR%28NEXT%5FTIME%2C%20%27DD%2DMON%2DYYYY%20HH24%3AMI%3ASS%27%29%20FROM%20V%24ARCHIVED%5FLOG%20where%20sequence%23%20%3D%20%28select%20max%28sequence%23%29%20from%20v%24archived%5Flog%20where%20applied%20%3D%20%27YES%27%29

    This is the error I get using the encode

    Use of uninitialized value $sql in sprintf at /usr/lib/nagios/plugins/check_oracle_health line 4194. Use of uninitialized value in subroutine entry at /usr/local/lib/perl/5.10.0/DBD/Oracle.pm line 284. Use of uninitialized value $value in numeric gt (>) at /usr/lib/nagios/plugins/check_oracle_health line 3615. Use of uninitialized value $value in numeric gt (>) at /usr/lib/nagios/plugins/check_oracle_health line 3616. Use of uninitialized value $params{“name2″} in split at /usr/lib/nagios/plugins/check_oracle_health line 3553. OK – :

    Any help would be much appreciated

    lausser Reply:

    check_oracle_health .... --mode sql --name SELECT%20TO%5...
  53. jhon Says:
    July 15th, 2010 at 22:11

    check_oracle_health –connect SID –mode sga-data-buffer-hit-ratio

    OK – SGA data buffer hit ratio 105.55%

    105.55 !!! why ?

    lausser Reply:

    I don’t know. I need more information. Look into the code. Find the statement which is used to fetch the data used for the calculation of the hit ratio, execute the statement manually, get the values involved in the calculation manually, post the result here.

    jhon Reply:

    SUM(DECODE(NAME,’PHYSICALREADS’,VALUE,0))

    SUM(DECODE(NAME,’PHYSICALREADSDIRECT’,VALUE,0))

    SUM(DECODE(NAME,’PHYSICALREADSDIRECT(LOB)’,VALUE,0))

    SUM(DECODE(NAME,’SESSIONLOGICALREADS’,VALUE,0))

                                    33942
                                            155
                                              319842
                                        5623223
    

    ===== using query of @Marco this result:

    SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) “Cache Hit Ratio” FROM v$sysstat cur, v$sysstat con, v$sysstat phy WHERE cur.name = ‘db block gets’ AND con.name = ‘consistent gets’ AND phy.name = ‘physical reads’ SQL> /

    Cache Hit Ratio

           99.4
    

  54. JamesC Says:
    July 16th, 2010 at 22:23

    I’m having an odd issue, related to running the script as a non-root user. The output is correct, except there’s a printf() error included with the output.

    [nagios@server0224 ~]$ /usr/local/nagios/libexec/check_oracle_health –connect krusta_srv –username USER –password PASS –mode sga-data-buffer-hit-ratio –warning 95: –critical 90: printf() on closed filehandle STATE at /usr/local/nagios/libexec/check_oracle_health line 3828. OK – SGA data buffer hit ratio 99.99% | sga_data_buffer_hit_ratio=99.99%;95:;90:

    lausser Reply:

    You ran the plugin as root. This lead to the creation of /var/tmp/check_oracle_health and probably some files below this directory. (owner: root) These files are necessary to carry state information from one run to the next. Then you ran the plugin as non-root. Overwriting the state file(s) does not work, because they’re owned by root. That’s why you see the error message. Homework:

    • chown -R nagios:nagios /var/tmp/check_oracle_health
    • write 100 times “i must not run plugins as root”
  55. sdouce Says:
    July 20th, 2010 at 12:15

    Hi i receive this kind of message and i dont understand, i have many nagios server using same distrib and working fine , here i have this probleme can oy help ? :

    CRITICAL – cannot connect to ORACLE_TOTO. install_driver(Oracle) failed: Can’t load ‘/usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBD/Oracle/Oracle.so’ for module DBD::Oracle: /usr/lib/oracle/10.2.0.4/client/lib/libocci.so.10.1: ne peut restaurer le segment prot après reloc:

    Permission non accordée at /usr/lib/perl5/5.8.8/i386-linux-thread-multi/DynaLoader.pm line 230. at (eval 14) line 3 Compilation failed in require at (eval 14) line 3. Perhaps a required shared library or dll isn’t installed where expected at /usr/local/nagios/libexec/check_oracle_health line 4193

    lausser Reply:

    Hi, looks like a broken installation of DBD::Oracle.

  56. hha Says:
    August 3rd, 2010 at 11:54

    Hi,

    ich bin gerade am rätseln ob man bei den perfomance-daten, die z.B. auch irgendwie nur die %-Zahlen ausgeben kann, bzw. nur die absoluten Werte. Hintergrund ist: Ich möchte die sachen gerne mit dem nagios-grapher visualisieren. Alternativ: Wenn wer eine passende config dafür hat.. :)

    Danke!

    lausser Reply:

    Die Performancedaten wurden so gestaltet, daß sie den Vorgaben der “Plugin Developer Guidelines” entsprechen und somit von PNP ohne jeglichen Aufwand verarbeitet werden können. Auf Nagios-Grapher habe ich keine Rücksicht genommen, daher gibt es keine Möglichkeit, an der Ausgabe etwas zu drehen. Aber wie du schreibst…irgendwer wird sicher schon eine NG-Config gebaut haben.

  57. Hix Says:
    August 27th, 2010 at 13:42

    Hi lausser,

    Firstly many thanks for your great plugin! I have written MyBind package (based on your documentation thanks again!) for check_oracle_health where I can test PL/SQL execution with bind variables. I have two successful execution: - without bind variables (only simple SQL or PL/SQL codes are executed in my check_oracle_health package) - with bind variables in a single perl script file independently check_oracle_health (using bind_param_inout method) So this two versions of my code is running fine. But when I try to put it into MyBind package I receive the following error message: Can’t locate object method “prepare” via package “DBD::Oracle::Server::Connection::Dbi” at /usr/lib/nagios/plugins/pm/CheckOracleHealthExtMyBind.pm line 36.

    OK, I know there are reasons: In my “without bind” versions I can use your great method: “$self->{handle}->fetchrow_array” and it’s working fine. But in my “with bind” versions I cannot use this because I need call the bind_param_inout method many times between prepare and execute phase. There is a small example: $sql = " begin xy.check_status(:P_PROGRAM_NAME, :P_PROGRAM_ID, :P_RUN_ID, :P_IS_ABORTED, :P_EXCEPTION_CODE, :P_EXCEPTION_NAME); end;";

    $sth = $self->{handle}->prepare($sql); $sth->bind_param_inout(":P_PROGRAM_NAME", \$p_program_name, 100); $sth->bind_param_inout(":P_PROGRAM_ID", \$p_program_id, 100); ... $sth->bind_param_inout(":P_EXCEPTION_NAME", \$p_exception_name, 100); $sth->execute(); I cannot use simple SQL because I have many complex PL/SQL code to monitor applications. So my question is, could you suggest to me how I can resolve this issue? Danke sehr!

    Hix Reply:

    @Hix, Oh… sorry for bad code formating! I have just try the code between “code” tag of WordPress, but it seems that isn’t working properly for me.

  58. Hix Says:
    August 27th, 2010 at 17:26

    Hi Gerhard, I’m sure you are going to find much better solution but I wrote a workaround temporarily: the following prepare method is inserted in the DBD::Oracle::Server::Connection::Dbi package, sub fetchrow_array above:

    sub prepare { my $self = shift; my $sql = shift; my $sth = undef; $sth = $self->{handle}->prepare($sql); if ($@) { $self->debug(sprintf "bumm %s", $@); } return $sth; }

    I hope I was not too difficult to describe the situation and the code formatting will work. :) Best regards

  59. Robson Says:
    September 10th, 2010 at 3:48

    Hi, I am trying to create any check using mode sql, but are dont working, can you helpme? check_oracle_health –connect=orcl2 –user=nagios –password=dbacomp2011 –mode=sql –name=select%20FNC%5FLOCK%20from%20dual –warning=1 –critical=2 return: WARNING – select fnc_lock from dual: 2 | ‘select’=2;1;2 so I create a command: define command { command_name check_check_orah_sql command_line $USER1$/check_oracle_health –connect $ARG1$ –username $ARG2$ –password $ARG3$ –mode $ARG4$ –name=$ARG5$ –warning=1 –critical=3 } and I create a service: define service{ use generic-service ; host_name host_home_orcl2 service_description Oracle Table Lock check_command check_check_orah_sql!orcl2!system!redhat!sql!select%20FNC%5FLOCK%20from%20dual } On command line the warning is detect sucessfull but on nagios the status is only OK. My sql return 2 if there is a lock, and return 0 if there is not a lock. Do you know what is wrong? thanks

    Robson Reply:

    @Robson, Another doubt is, on status information appears: OK – select fnc_lock from dual: how can I change that?, on parameter name2, I can change to a fixed value. Can I implement to show on this columns users thath are locking? the command to extract thath I know, the problems is how to put thath information there. thanks for your help.

    lausser Reply:

    Thats not possible. If you want this kind of customized output you need to implement it yourself. In the contrib-directory of the check_oracle_health-package you find some help´on how to write own extensions.

    lausser Reply:

    I have no idea, why it shows a different result undr nagios. You might create a trace-file with ‘touch /tmp/check_oracle_health.trace’ As long as this file exists, the plugin will write some debugging information to it. Then find the differences between a commandline run and a nagios-triggered run in the logs.

  60. Robson Says:
    September 10th, 2010 at 16:55

    I create file /tmp/check_oracle_health.trace, but the file are writen only by command line, when I force run comando by nagios, the file dont receive anything. There are another thing I can do? thanks

    lausser Reply:

    I bet you ran the plugin on the command line as root, didn’t you? The root is the owner of the tracefile. Now when the plugin runs in a nagios context (and under the nagios user account) it has not the permission to write to the file. Please do never test plugins as root on the command line. (You also have to delete /var/tmp/check_oracle_health, because you’ll have the same problem here. This directory is important for correct results)

  61. Robson Says:
    September 10th, 2010 at 17:36

    Thanks a lot for your help. The error is thath I was using different user on nagios. Thank you so much

  62. rkelly Says:
    September 13th, 2010 at 21:47

    Hi lausser,

    I was hoping to use this plugin but Im having some basic install/config issues. I fairly new to BSD type OS and nagios. Im have installed the latest Groundworks VM (CentOS based) for testing. I setup the NSClient++ for monitoring remote Windows servers (which is working) and was hoping to monitor some basic parameters of Oracle. Are there any issues with the plugin monitoring remote windows machines? I have created an account in Oracle using the defaults and created the plugin in Nagios based on the instructions which seem to install fine and the plugin shows up in Nagios but i am getting the “Error executing command (Permission denied)” when I test a new command. Ive checked access to oracle and i can ping it and log in remotely so i dont think it is oracle access but access to the plugin. I checked the permissions on the plugin and they are nagios/nagios. I created the check_oracle_health.trace and did a touch and then tailed the file but I dont see any debug being created – Im sure it is just me being a noob but any insite would be appreciated.

    thanks

    lausser Reply:

    How did you unpack/install the plugin? Can you run it from the command line?

    rkelly Reply:

    @lausser,

    found out that the groundwork perl is actually in /usr/local/groundwork/perl/bin/perl – i deleted the old script and created a new one using the new path but kept getting the same error – looked at the script and it still says it is pointing to the old location so i just modded the script and now when testing perl is now recognized – still have a few more issues but I should be able to get it now thanks

  63. rkelly Says:
    September 13th, 2010 at 23:50

    Hi -

    I unpacked to the desktop and ran the installer from terminal as the grounwork user. If i run from command line i get:

    /usr/local/groundwork/nagios/libexec/check_oracle_health: /usr/local/groundwork/perl: bad interpreter : permission denied

    i wasnt sure during the config whether to point the perl directory to /perl or /perl/bin – should i reinstall perl – the one i am pointing to is built into the groundwork install (v 5.8.8 for i386 linux thread multi).

    thanks again

  64. Tontonitch Says:
    September 28th, 2010 at 10:12

    Hi Lausser,

    I faced a problem using the mode shared-pool-free reporting 0% event if the shared pool free memory was about 80%. I saw that problem only on Oracle 9i at the moment. Looking at that, it seems that a bug with some statistics reported for exemple 3.6E19 for the kzull component in the shared pool, which is completely false/impossible.

    I’ve adapted the check related query in check_oracle_health (in init_shared_pool_free) to not take into account the values in v$sgastat which are greater than the free memory.

    sub init_shared_pool_free { my $self = shift; my %params = @_; #$self->{free_percent} = $self->{handle}->fetchrow_array(q{ # SELECT ROUND((SUM(DECODE(name, ‘free memory’, bytes, 0)) / # SUM(bytes)) * 100,2) FROM v$sgastat where pool = ‘shared pool’ #}); # Fix for problems with abnormal values. Example: kzull = 3.9E19! $self->{free_percent} = $self->{handle}->fetchrow_array(q{ select ROUND(a.bytes / b.sm * 100,2) from (select bytes from v$sgastat where name=’free memory’ AND pool=’shared pool’) a, (select sum(bytes) sm from v$sgastat where pool = ‘shared pool’ and bytes <= (select bytes from v$sgastat where name=’free memory’ AND pool=’shared pool’)) b }); # scheint nur bis ora9 sinnvoll zu sein. >10.x liefert 0 #$self->{alloc} = $self->{handle}->fetchrow_array(q{ # SELECT value FROM v$parameter WHERE name = ‘shared_pool_size’ #}); if (! defined $self->{free_percent}) { $self->add_nagios_critical(“unable to get sga free”); return undef; } }

    May that change help some people still using Oracle 9i to have correct shared pool usage statistics.

    Cheers,

    Yannick

    Tontonitch Reply:

    @Tontonitch, I will send you that little change via email as it’s a bit crap on that web page.

    Cheers,

    Yannick

    lausser Reply:

    Thanks Yannik!

  65. lozair Says:
    November 4th, 2010 at 18:30

    Hi Lausser,

    I faced a problem using check_oracle_health. I run this command on an oracle server and all is good using a local user.

    I want to launch the command remotely, using nrpe, from my nagios server.

    however i encountered always the same error on the nagios server :

    +++++++++++ CRITICAL – cannot connect to TOTO. install_driver(Oracle) failed: Can’t load ‘/usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/Oracle/Oracle.so’ for module DBD::Oracle: libclntsh.so.10.1: Ne peut ouvrir le fichier d’objet partagé: Aucun fichier ou répertoire de ce type at /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/DynaLoader.pm line 230. at (eval 13) line 3 Compilation failed in require at (eval 13) line 3. Perhaps a required shared library or dll isn’t installed where expected at /usr/local/nagios/libexec/check_oracle_health line 4512 +++++++++++++++

    Reading the forum and googling for a while i view a post saying that we must set the ORACLE_HOME and LD_LIBRARY_PATH variable in nrpe init script in order to have a correct environment to launch the check_oracle_health.

    I attempt this solution but that not solve the problem

    I also attempt to use the –environment check_oracle_health option to set these variables but i obtain always the same error

    Any help/idea would be great

    regards

    lausser Reply:

    check_oracle_health needs (like every other oracle client software) a valid environment with ORACLE_HOME, LD_LIBRARY_PATH etc. So you need to start your nrpe with an oracle environment.

    lozair Reply:

    @lausser, ok thanks for your response i have found the problem, the env variables were correctly set in nrpe but there was bad file permissions on the ORACLE_HOME directory for the nrpe user

  66. OMD Version 0.44 erschienen » klimmbimm Says:
    November 15th, 2010 at 17:56

    [...] dienen der Abfrage von Parametern der bekannten Datenbanksysteme. Mehr Infos auf http://labs.consol.de/lang/de/nagios/check_oracle_health/ und [...]

  67. michauko Says:
    November 15th, 2010 at 20:15

    Hi,

    Thanks for this amazing nagios plug-in. It rocks !! I’m setting it up, just a configuration problem to fix, if you have any idea, that’d be great: - My NRPE process owner is “nagios” on a Debian system, so the script are started with his environment - I can run all the tests from the command line, as “nagios”. It works just fine. - I already have some other NRPE tests running with my Nagios/NRPE configuration.

    For this plugin, from Nagios, I got a ORA-24327 “need explicit attach before authenticating a user” error. I might have something to define in the environment, but what? I saw some remarks about sqlnet.ora, but not sure what I have to do.

    I’ve defined my NRPE commands like this one: command[check_oracle_health_connection-time]=/usr/local/nagios/libexec/check_oracle_health –connect user/pass@MYORA –mode connection-time

    As I told, running this command directly as “nagios” just works.

    Thanks, Regards

    Jacques M

    lausser Reply:

    In order to sucessfully execute Oracle client software (like check_oracle_health) your NRPE needs to run with some special environment variables. Have a look at ORACLE_HOME, TNSADMIN and LD_LIBRARY_PATH. You need to set them in the nrpe-init-script.

    michauko Reply:

    @lausser, MMM, I’ll try tomorrow. The thing is that I could run another Oracle-based plug-in using sqlplus from Nagios without any problem. I’ll check this tomorrow, anyway

    Thank you

    michauko Reply:

    @lausser, You’re right, thank you.

    On Debian, I added in /etc/init.d/nagios-nrpe-server the following: export ORACLE_HOME=/usr/lib/oracle/11.1/client export PATH=$PATH:$ORACLE_HOME/bin export LD_LIBRARY_PATH=$ORACLE_HOME/lib

    After the initial PATH, DAEMON… initialisation

    Thank you

    I wonder if there is a better place to put these. I saw I can give env vars in your check_oracle_health script, but didn’t try it correctly I guess.

    regards

  68. Nagios : supervision d’une base Oracle - Le blog de Michauko Says:
    November 16th, 2010 at 16:52

    [...] j’ai trouvé ça : http://labs.consol.de/nagios/check_oracle_health/ repéré sur exchange.nagios.org. Y’a aussi le pendant pour MySQL, MSSQL etc. A voir à [...]

  69. Sylvain Says:
    November 18th, 2010 at 16:56

    Hello,

    I have a problem when i “make’. I ‘m in Solaris 10 and DBI and DBD Oracle are installed:

    Making all in plugins-scripts make[1]: Entering directory /downloads/Nagios/check_oracle_health-1.6.6.1/plugins-scripts' make[1]: *** No rule to make targetNagios/DBD/Oracle/Server/Instance/SGA/SharedPool/DictionaryCache.pm’, needed by check_oracle_health'. Stop. make[1]: Leaving directory/downloads/Nagios/check_oracle_health-1.6.6.1/plugins-scripts’ make: *** [all-recursive] Error 1

    Please help me

    Many thanks in advance.

    lausser Reply:

    Looks like your tar cannot unpack files with very long names. Take the shar.gz-package instaed.

  70. Thomas Wollner Says:
    November 23rd, 2010 at 17:57

    Hi, thanks for the great plugin. Currently it seems to me that the percent calculation does something strange. I have a tablespace which is 500MB size and ~ 245MB used. the plugin output tells me that there is 0.75 % of the TS used. the performance data seems to have the right values.

    Plugin output: OK – tbs TS_MYDB usage is 0.75% | ‘tbs_ts_mydb_usage_pct’=0.75%;90;98 ‘tbs_ts_mydb_usage’=245MB;29491;32112;0;32767 ‘tbs_ts_mydb_alloc’=500MB;;;0;32767

    The Database is an oracle 10g XE version.

    Any thoughts on this? thanks in advance,

    Tom

    lausser Reply:

    The 500MB are allocated size. But this tablespace seems to be auto-allocatable, so it will grow as soon as the used size becomes nearly as much as the allocated size. If the calculation were based on used/allocated, the percentage would ‘jump’, approaching 100%, then (after the database automativally allocates more space) drop again. That’s why calculation is based on used size/maximum allocatable size.

  71. Prezes Klubu Says:
    November 24th, 2010 at 3:17

    Hello!

    check_oracle_health is awesome :) But… It works fine only for some time… hours, days. After some time service stay in one state forever. And all new added services which using check_oracle_health plugin hangs on “pending” status for all the time. When I run check_command from command line everything works fine, results are correct. Any ideas?

    lausser Reply:

    If Nagios doesn’t schedule a service any more, then it’s not the fault of the underlying plugin. The nagios-users mailing list would be the best place to address this kind of problems.

  72. Prezes Klubu Says:
    November 24th, 2010 at 18:34

    Hello,

    When something should be created in system_vartmpdir? After the first checks, or when? I don’t have anything in this directory… That might be a reason why all nagios checks hangs on one status…?

    lausser Reply:

    Files are creates in the temp-dir as soon as the plugin is executed. No temp-files = plugin never has been run. Plugin never has been run + pending status = Nagios problem. Again: if Nagios doesn’t run a plugin because the service is pending, this has nothing to do with the plugin.

  73. Prezes Klubu Says:
    November 24th, 2010 at 18:48

    If I start check_oracle_plugin from command line (the same command and parameter which are defined in command in nagios), some files should be created in temp-dir ?

    lausser Reply:

    It depends on the mode. For example connection-time does not write a temp-file.

    Prezes Klubu Reply:

    @lausser, could you tell me which mode should created temp-file?

    miwu Reply:

    Hi,

    erstmal vielen Dank für das Top-plugin. Leider habe auch ich Probleme mit dem Plugin in Verbindung mit nrpe. Wenn ich das Plugin lokal auf dem DB-Server aufrufe klappt alles prima. Wenn ich es per nrpe vom Nagios-Host aus versuche leider nicht. ich bekomme folgenden Fehler:

    CRITICAL – cannot connect to testdb. install_driver(Oracle) failed: Can’t load ‘/usr/lib/perl5/site_perl/5.10.0/x86_64-linux-thread-multi/auto/DBD/Oracle/Oracle.so’ for module DBD::Oracle: libclntsh.so.11.1: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.10.0/x86_64-linux-thread-multi/DynaLoader.pm line 203. at (eval 14) line 3 Compilation failed in require at (eval 14) line 3. Perhaps a required shared library or dll isn’t installed where expected at /usr/local/nagios/libexec/check_oracle_health line 4512

    In der /etc/init.d/nrpe habe ich bereits folgendes gesetzt (und den nrpe auch neu gestartet)

    PATH=/opt/oracle/app/oracle/product/11.2.0/dbhome_1/bin:/bin:/sbin:/usr/sbin:/usr/local/sbin:/root/bin:/usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/X11R6/bin:/usr/games:/usr/lib/mit/bin:/usr/lib/mit/sbin ORACLE_HOME=/opt/oracle/app/oracle/product/11.2.0/dbhome_1 LD_LIBRARY_PATH=$ORACLE_HOME/lib export PATH export ORACLE_HOME export LD_LIBRARY_PATH

    Ich habe hier auch noch etwas davon gelesen, daß man im ORACLE_HOME noch Dateirechte ändern sollte, ich bin aber nicht dahinter gekommen, welche geändert werden sollten.

    Kannst Du mir hier weiterhelfen?

    Vielen Dank und Grüße

    Miwu

    lausser Reply:

    Oracle-Environment-Variablen im nrpe-Startscript eintragen. Siehe weiter oben…

    miwu Reply:

    Hallo,

    die Oracle-Environment-Variablen hatte ich schon in die /etc/init.d/nrpe eingetragen (ORACLE_HOME,LD_LIBRARY_PATH und den aktualisierten PATH), fehlt da noch was?

    Vielen Dank!

    Miwu

    miwu Reply:

    habe die Ursache gefunden:

    Die Datei libclntsh.so.11.1 im ORACLE_HOME war für den nrpe-User nicht lesbar.

    Danke!

    Miwu

  74. Prezes Klubu Says:
    November 24th, 2010 at 18:54

    I think that nagios scheduled check service proper… I runs nagios in debug mode and I can found: [1290617402.177408] [016.0] [pid=17995] Checking service ‘IMP Connected Users’ on host ‘hostname’… [1290617402.177431] [001.0] [pid=17995] get_raw_command_line() [1290617402.177438] [2320.2] [pid=17995] Raw Command Input: /usr/lib/nagios/plugins/check_oracle_health –environment ORACLE_HOME=/usr/lib/oracle/10.2.0.4/client –connect IMP –username SYSTEM –password imp –mode connected-users [1290617402.177447] [2320.2] [pid=17995] Expanded Command Output: /usr/lib/nagios/plugins/check_oracle_health –environment ORACLE_HOME=/usr/lib/oracle/10.2.0.4/client –connect IMP –username SYSTEM –password imp –mode connected-users [1290617402.177453] [001.0] [pid=17995] process_macros() [1290617402.177459] [2048.1] [pid=17995] * BEGIN MACRO PROCESSING ******** [1290617402.177465] [2048.1] [pid=17995] Processing: ‘/usr/lib/nagios/plugins/check_oracle_health –environment ORACLE_HOME=/usr/lib/oracle/10.2.0.4/client –connect IMP –username SYSTEM –password imp –mode connected-users’ [1290617402.177482] [2048.2] [pid=17995] Processing part: ‘/usr/lib/nagios/plugins/check_oracle_health –environment ORACLE_HOME=/usr/lib/oracle/10.2.0.4/client –connect IMP –username SYSTEM –password imp –mode connected-users’ [1290617402.177490] [2048.2] [pid=17995] Not currently in macro. Running output (175): ‘/usr/lib/nagios/plugins/check_oracle_health –environment ORACLE_HOME=/usr/lib/oracle/10.2.0.4/client –connect IMP –username SYSTEM –password imp –mode connected-users’ [1290617402.177496] [2048.1] [pid=17995] Done. Final output: ‘/usr/lib/nagios/plugins/check_oracle_health –environment ORACLE_HOME=/usr/lib/oracle/10.2.0.4/client –connect IMP –username SYSTEM –password imp –mode connected-users’ [1290617402.177502] [2048.1] [pid=17995] * END MACRO PROCESSING ********** [1290617402.177510] [064.1] [pid=17995] Making callbacks (type 13)… [1290617402.177555] [016.1] [pid=17995] Check result output will be written to ‘/var/lib/nagios3/spool/checkresults/checkQ53yV6′ (fd=7) [1290617402.177630] [016.1] [pid=17995] ** Using Embedded Perl interpreter to run service check… [1290617402.256972] [016.2] [pid=18501] Moving temp check result file ‘/var/lib/nagios3/spool/checkresults/checkwsH433′ to queue file ‘/var/lib/nagios3/spool/checkresults/cWbnK76′… [1290617402.324166] [016.1] [pid=17995] Embedded Perl successfully compiled /usr/lib/nagios/plugins/check_oracle_health and returned code ref to plugin handler [1290617402.324363] [016.2] [pid=17995] Service check is executing in child process (pid=18503)

  75. Prezes Klubu Says:
    November 26th, 2010 at 16:22

    After changing use_embedded_perl_implicitly options in nagios.cfg everything start working… for now :)

    Thanks!

  76. miwu Says:
    December 3rd, 2010 at 13:16

    Hallo,

    ich bin schwer beigeistert von dem Plugin. Bis auf folgendes Problem klappt alles auch super, nur beim Prüfen der Tablespace_usage kommen bei mir falsche Werte.

    Mein system-Tbs ist 700M groß, davon sind 273M belegt. Mein Check ergibt jedoch Folgendes:

    ./check_oracle_health –connect testdb –username=user –password=pw –mode tablespace-usage –tablespace system -units % OK – tbs SYSTEM usage is 0.84% | ‘tbs_system_usage_pct’=0.84%;90;98 ‘tbs_system_usage’=273MB;29491;32112;0;32767 ‘tbs_system_alloc’=700MB;;;0;32767

    Die 0,84% Tablespace-Belegung stimmen leider nicht, obwohl die ermittelten Werte richtig sind. Das Problem tritt bei allen Tablespaces auf. Woran könnte das liegen?

    Vielen Dank!

    Miwu

    lausser Reply:

    Der Tablespace ist nicht 700MB groß. 700MB sind allocated space. Wenn der used space die 700MB erreicht, wird auch der allocated space wachsen. Das Plugin geht bei der Berechnung vom max. erreichbaren allocated space aus.

  77. i611287 Says:
    December 10th, 2010 at 17:43

    hello Lauser I need your help I have a problem with the query ‘tablespace-usage’

    First: The tablespace to consult the following features: Used%: 92.42 Size: 134 Mb Used: 124 Mb Free: 10.2 Mb

    Second: Consultation with check_oracle_health is:

    /Usr/local/nagios/libexec/check_oracle_health –connect ‘(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = X.Y.Z.R) (PORT = 1521)) (CONNECT_DATA = (SID = sidname))) ‘ –user nagios –password nagios –mode tablespace-usage –tablespace tablespacename

    OK – tbs tablespacename usage is 0.38% | ‘tbs_tablespacename_usage_pct’ = 0.38%, 90, 98 ‘tbs_tablespacename_usage’ = 124MB, 29491, 32112, 0, 32767 ‘tbs_tablespacename_alloc’ = 134MB;;; 0; 32767

    you see, there are data that do not match:

    tbs tablespacename tbs usage is 0.38% (is 92.42%) failed tbs_tablespacename_usage_pct = 0.38% (is 92.42%) failed tbs_tablespacename_usage = 124MB – ok tbs_tablespacename_alloc = 134MB – ok

    The percentages do not match.

    Can you help me with this? If I’m wrong about something, please let me note

    Thanks.

    Regards.

    lausser Reply:

    Used%: 92.42

    92% of the currently allocated space are used. Now write to the database and see what will happen. Oracle will auto-allocate more space and your Used: will drop. Not good. check_oracle_health calculates the usage on the maximum allocatable space.

  78. svbr Says:
    December 13th, 2010 at 9:20

    Hallo, bei der Anwendung des Plugins check_oracle_health halten wir aus Sicherheitsgründen das setzten des Rechtes “GRANT SELECT any dictionary TO nagios; ” für bedenklich, wenn der Nagios User in der DB “nur” die größe der tabelspaces abfragen soll. Nach entzug des any dictionary Rechtes kommen aber keine Werte mehr zu Nagios herüber. Welche Rechte sind explizit für die Tablespaceüberwachung erforderlich, die einen weiteren Zugriff auf andere DB-Informationen nicht zulassen?

    lausser Reply:

    Das herauszufinden kostet Zeit. Meine Freizeit, von der ich momentan keine habe. Wenn sie dringend eine Antwort benötigen, können sie gerne unter support@consol.de ein formales Angebot anfordern. Mein Arbeitgeber wird mir dann erlauben, die Informationen während meiner Arbeitszeit zu beschaffen.

  79. svbr Says:
    December 14th, 2010 at 9:48

    Danke für das Angebot, inzwischen hab ich –nach nächtelanger Suche im Netz — die Infos bekommen:

    GRANT CREATE SESSION TO “NAGIOS” GRANT SELECT ON “SYS”.”DBA_UNDO_EXTENTS” TO “NAGIOS” GRANT SELECT ON “SYS”.”DBA_TABLESPACES” TO “NAGIOS” GRANT SELECT ON “SYS”.”DBA_TEMP_FILES” TO “NAGIOS” GRANT SELECT ON “SYS”.”V_$TEMP_EXTENT_POOL” TO “NAGIOS” GRANT SELECT ON “SYS”.”V_$TEMP_SPACE_HEADER” TO “NAGIOS” GRANT SELECT ON “SYS”.”DBA_DATA_FILES” TO “NAGIOS” GRANT SELECT ON “SYS”.”DBA_FREE_SPACE” TO “NAGIOS” GRANT “CONNECT” TO “NAGIOS”

    Damit funktioniert´s Danke

  80. John Alberts Says:
    December 16th, 2010 at 23:29

    Hi. We’ve been using this plugin for about a year now and I thought I was working great. It turns out that it wasn’t monitoring the tablespace usage properly. We are using this in a rac environment, so I wonder if that might have something to do with it. Does this plugin check tablespace usage properly for Oracle 10g RAC? The numbers it returns almost seem random. For instance, the plugin says my USERS tablespace is at 0.8%, while the actual usage determined using an sql statement is around 91%. Other tables are just the opposite. Real usage is at almost 99%, while the plugin returns usage as being something around 20 or 30%. I know I didn’t give much detail here, but I figured I would ask here real quick and give more details if needed. Thanks

    lausser Reply:

    99% of what? Of the currently allocated space or of the maximal allocatable space? In contrary to other tools, check_oracle_health compares used space to maximum allocatable space. Other tools compare used space to allocated space. Which means, that you have 99% now and (after the db auto-allocates more space) in the next moment less than 99%.

    John Alberts Reply:

    @lausser, I’m actually a complete idiot when it comes to Oracle, but the dba gave me some sql to run to check space which he says is accurate. Can you tell me what the difference is? Here is the output of the sql query, and then the output of the plugin.

    SQL> select a.TABLESPACE_NAME, a.BYTES bytes_used, b.BYTES bytes_free, b.largest, round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used from (select TABLESPACE_NAME, sum(BYTES) BYTES from dba_data_files group by TABLESPACE_NAME) a, (select TABLESPACE_NAME, sum(BYTES) BYTES, max(BYTES) largest from dba_free_space group by TABLESPACE_NAME) b where a.TABLESPACE_NAME=b.TABLESPACE_NAME order by ((a.BYTES-b.BYTES)/a.BYTES) desc; 2 3 4 5 6 TABLESPACE_NAME BYTES_USED BYTES_FREE LARGEST PERCENT_USED

    USERS 292225024 13959168 13303808 95.22 REGISTRATIONDB 674430976 33161216 31653888 95.08

    /usr/local/nagios/libexec/check_oracle_health –connect=’(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=secret)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=secret)))’ –user=secret –password=secret –mode=tablespace-usage –warning=90 –critical=95 –regexp –name “REGISTRATIONDB|USERS” OK – tbs USERS usage is 0.81%, tbs REGISTRATIONDB usage is 1.87% | ‘tbs_users_usage_pct’=0.81%;90;95 ‘tbs_users_usage’=265MB;29491;31129;0;32767 ‘tbs_users_alloc’=278MB;;;0;32767 ‘tbs_registrationdb_usage_pct’=1.87%;90;95 ‘tbs_registrationdb_usage’=611MB;29491;31129;0;32767 ‘tbs_registrationdb_alloc’=643MB;;;0;32767

    Thanks

    John Alberts Reply:

    @John Alberts, Sorry, that didn’t format very well at all. Here’s the output from the sql query formatted a bit better. Hopefully…

    TABLESPACE_NAME BYTES_USED BYTES_FREE LARGEST PERCENT_USED

    USERS 292225024 13959168 13303808 95.22 REGISTRATIONDB 674430976 33161216 31653888 95.08

    lausser Reply:

    Please ask your dba if he knows the difference of allocated size and max size of a tablespace (with auto-allocation configured) As i already wrote, 95.22% usage means: 95.22% of the allocated space are used. If you continue writing to the tablespace, oracle will automatically allocate more space. This means, that your percentage will drop. You’ll get an alarm at 3:00 in the morning because your sql-script reports 99%, but at 3:01 oracle auto-allocates more space. So when you haved turned on your pc at 3:05 you’ll see for example 80%.

    John Alberts Reply:

    @lausser, That’s what I suspected was the problem; however, our disk actually ran out of space and we were never alerted because the plugin still showed we were below our critical threshold of 95%. I wonder… is it possible for the dba to allocate a larger max size for the tablespace than is actually available on the disk? Maybe that’s what happened? The plugin showed tablespace x is at 80% because it still has 20% to go before it hits the max size, but unfortunately, the disk space was 100% used.

    In any case, I’ll need to do some more playing around and talking to the dba some more.

  81. John Alberts Says:
    December 17th, 2010 at 4:43

    Can you explain the performance data information please? I couldn’t find any documentation about it. For instance, for the users tablespace, it shows this:

    ‘tbs_users_usage_pct’=0.81%;90;95 ‘tbs_users_usage’=265MB;29491;31129;0;32767 ‘tbs_users_alloc’=278MB;;;0;32767

    tbs_user_usage_pct makes sense. 0.81% used, warning threshold is 90% and critical is 95%.

    I’m really not sure aobut the actual usage data.

    lausser Reply:

    used = used data, alloc = allocated data

    John Alberts Reply:

    @lausser, :) Yeah, I figured that much. I actually meant the semi-colon separated values. So, ‘tbs_users_usage’=265MB;29491;31129;0;32767

    This = 265MB used and I’m not sure what the other numbers are.

    Thanks for spending so much time helping me understand this.

    Regards, john

    lausser Reply:

    You can find the meaning of the values here: http://nagiosplug.sourceforge.net/developer-guidelines.html#AEN201

    John Alberts Reply:

    @lausser, Thank you. I didn’t realize it was standard.

  82. 17 Nagios-Fliegen mit einer Klappe: OMD 0.44 | KenntWas.de - Technische Tips Says:
    December 22nd, 2010 at 0:08

    [...] check_oracle_health aus den Labs der Fa. Consol überprüft Oracle Datenbanken. Auf die Benutzung werde in ein einem anderen Artikel noch weiter eingehen. check_oracle_health ist ein Perl-Script. Die Datenbanken werden (meistens) remote von Nagiosserver aus abgefragt. Dazu benötigt dieser entweder einen Oracle (Voll-) Client oder den Oracle Instantclient. Die Installation des Oracle Instant Clients unter Ubuntu habe ich in einem früheren Artikel beschrieben. Dort findet sich auch Aufrufbeispiel mit dem Oracle Easy Connectstring. Die Connectstrings sollten direkt im Kommando mit angegeben werden. Eine Verwendung der tnsnames.ora ist zu fehleranfällig (wird schnell vergessen). Ich persönlich ziehe die EZCONNECT-Strings vor (wenn sie erlaubt sind), da sie wesentlich einfacher sind als  “(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP…..”- connectstrings. [...]

  83. Fabien Says:
    December 27th, 2010 at 18:04

    Hi,

    I tried to use the check_oracle_health scriptn but I have some problems with the execution :

    for example, I tried to run :

    “simple” command works : check_oracle_health –connect release –mode tnsping ==> OK

    but when I tried to acces to the database :

    check_oracle_health –mode tablespace-usage

    ==> I have an error : CRITICAL – cannot connect to release. ORA-01017: invalid username/password; logon denied (DBD ERROR: OCISessionBegin)

    If I add -user – password in my commande line, I have another error ;)

    check_oracle_health –mode tablespace-usage -user OR#USER -password toto

    ==> I have an error :

    Use of uninitialized value in split at ./check_oracle_health line 4381. Use of uninitialized value in split at ./check_oracle_health line 4381. bumm Can’t call method “execute” on an undefined value at ./check_oracle_health line 4690.

    Can’t use an undefined value as an ARRAY reference at ./check_oracle_health line 4704.

    Someone can help me on those errors ?

    regards,

    lausser Reply:

    OR#USER is not valid the way you use it. Either chose anothe rname which contains no shell special characters or put it into quotes.

    M.Nieberg Reply:

    @Fabien, ‘#’ ist a special character in /bin/sh shell-commands (comment). To avoid this, you should “quote” the parameter(s) of the command:

    check_oracle_health –mode tablespace-usage -user “OR#USER” -password “toto”

  84. check_oracle_health: seg-top10- Abfragen verbessern | KenntWas.de - Technische Tips Says:
    December 29th, 2010 at 12:27

    [...] Links zu v$segstat Das Nagios-/OMD Plugin check_oracle_health von Gerhard Lausser kann unter anderem auch vier top10-Abfragen auf v$segstat. Diese Abfragen [...]

  85. M.Nieberg Says:
    December 29th, 2010 at 12:51

    After having performance problems with –mode=seg-top10- Queries in check_oracle_health, I use another statement. You cant find it here: http://wp.me/p16yMU-hN (german page) Perhaps it also works for you.

  86. Michel Says:
    January 3rd, 2011 at 18:10

    Hi,

    I am trying your new version (1.6.7) But seems to be running into a small issue. When I am using the sqlmode with –name2=”something” I receive “CRITICAL – output 0 not found” When I am deleting the –name2=”something” it will produce a “OK” status (with the complete script in it ofcourse) Any Ideas what is wrong?

    Michel

  87. Tontonitch Says:
    January 5th, 2011 at 19:06

    Hi Gerhard,

    I’m facing some problems with the new version 1.6.7 of check_oracle_health plugin and the modification made on the sql mode. Hereunder are 2 exemple of use taken from the exemples on this page, with perl error returned:

    Exemple 1: icinga@monitor:/usr/local/icinga/libexec$ ./check_oracle_health –connect XX –username XXXX –password xxxxxxxxxxx –mode sql –name “select ‘abc123′ from dual” –name2 abc123 Use of uninitialized value $_ in pattern match (m//) at ./check_oracle_health line 3878. CRITICAL – output abc123 not found

    Exemple2: icinga@monitor:/usr/local/icinga/libexec$ ./check_oracle_health –connect XX –username XXXX –password xxxxxxxxxxx –mode sql –name ‘select count(*) from v$session’ –name2 sessions Use of uninitialized value $_ in pattern match (m//) at ./check_oracle_health line 3878. CRITICAL – output 26 not found icinga@monitor:/usr/local/icinga/libexec$

    Regards,

    Yannick

    lausser Reply:

    Release 1.6.8 is out which corrects this error.

    Tontonitch Reply:

    @lausser, That was a quick reaction! Great & thanks!

  88. Jens G. Says:
    January 10th, 2011 at 12:44

    Hallo, Ich habe mal eine Frage: Wir haben heute den Check von 1.5.0.1 auf 1.6.8 aktualisiert. Laut Changelog ist mit der Version 1.6.4 die Überprüfung der Objects in der Table “dba_registry” hinzugekommen. Nun habe ich hier ein Projekt, welches Oracle 10 einsetzt mit der Lizenz Standard Edition One. Bei diesen scheint es standardmäßig so zu sein, dass folgende Komponenten Off sind: Oracle Data Mining = OPTION OFF Oracle OLAP API = OPTION OFF Spatial = OPTION OFF OLAP Analytic Workspace = OPTION OFF

    Nun hier die Frage, wie man dieses Problem umgehen kann? Macht es Sinn die Abfrage (status <> ‘VALID’;) zu erweiteren mit status <> ‘OPTION OFF’; ?

    Oder gibt es andere Lösungen?

    Vielen Dank und Viele Grüße Jens G.

  89. Hector Roman Says:
    January 14th, 2011 at 17:52

    your guidance teacher again to install the plugin from Oracle, I am using the following statement from the command line.

    . / check_oracle_health – connect 172.20.10.180 – user monitoring – password bbbb – tablespace-usage mode

    The result is:

    CRITICAL – Can not connect to 172.20.10.180. install_driver (Oracle) failed: Can not locate DBD / Oracle.pm in @ INC (@ INC contains:. / etc / perl / usr/local/lib/perl/5.10.1 / usr/local/share/perl/5.10 .1 / usr/lib/perl5 / usr/share/perl5 / usr/lib/perl/5.10 / usr/share/perl/5.10 / usr / local / lib / site_perl) at (eval 18) line 3. Perhaps the DBD:: Oracle perl module hasn’t Been Fully installed, or Perhaps the capitalization of ‘Oracle’ isn’t right. Available drivers: AnyDATA, CSV, DBM, ExampleP, File, Gofer, ODBC, Proxy, Sponge, Sybase, mysql. at. / check_oracle_health line 4596

    I do not know if the statement that I’m using is correct because I’ve seen some tsnames specify in the connection. I installed the Oracle Client without any problems and I was able to connect using SQLPLUS.

    Appreciating your guidance, I say goodbye.

    Hector Roman Reply:

    @Hector Roman,

    I am using Ubuntu Server 10.4

    perl -MCPAN -e shell cpan> install DBI

    lausser Reply:

    DBI is not enough. From the plugin’s error output: ……Perhaps the DBD:: Oracle perl module hasn’t Been Fully installed…..

    Either install DBD::Oracle or use the parameter –method sqlplus

  90. Christoph Greiner Says:
    January 17th, 2011 at 12:43

    Wenn ich nagios im Daemon-Modus starte bekomme ich folgende Meldungen…

    Illegal division by zero at /usr/local/nagios/libexec/check_oracle_health line 3350, <> line 11. Warning: Return code of 25 for check of service ‘ora-tablespace-io-balance’ on host ‘TSAPBWP02_LEN_LAN’ was out of bounds.

    Mein Command sieht so aus:

    check_oracle_health -3 –method sqlplus –mode tablespace-io-balance –warning 900 –critical 950 –environment ORACLE_HOME=/usr/lib/oracle/11.2/client –environment LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client/lib/ –connect SID –username=nagios –password=nagiospass

    lausser Reply:

    Bitte mit touch /tmp/check_oracle_health.trace eine Datei anlegen. Solange diese existiert, schreibt check_oracle_health die abgesetzten SQL-Statements und derern Ergebnisse rein. Die Datafile-relevanten Statements bitte mit sqlplus von Hand ausführen und auf leere Ergebnisse oder Fehler prüfen.

    Weil ichs grad noch sehe….was bedeutet “als daemon aufrufe”. Heisst das, es funktioniert, wenn mans auf der Kommandozeile aufruft? In dem Fall müsste ich kräftig schimpfen, da check_oracle_health vermutlich als root aufgerufen wurde und das ist pfui. PFUI! Nagios-Plugins ruft man niemals als root auf, sondern als Nagios-User. root = PFUI!

    Bei manchen Modi wird nämlich eine Zwischendatei in /var/tmp/check_oracle_health erzeugt. Wenn die zunächst dem root gehört, kann sie später nicht mehr überschrieben werden, wenn check_oracle_health unter der UID von Nagios läuft und dann kracht’s.

  91. Hennie Says:
    January 25th, 2011 at 1:03

    Hi Lausser

    Thanks for a great extension, I would like to exclude undo tablespace from the fragmentation check, where can i change the script to exclude this?

    lausser Reply:

    you don’t have to modify the code. you simply have to read the manual or search google-

  92. Christoph Says:
    January 27th, 2011 at 11:04

    Hallo Hr. Lausser,

    kann man “tablespace-usage” ohne autoextend Option betreiben? So das der Füllgrad des Tablespace dem Wert im SAP (Transaktion DB02) entspricht?

    Ausgabe check_oracle_health:

    ‘tbs_psapp02_usage_pct’=40.89%;90;95 ‘tbs_psapp02_usage’=310752MB;684000;722000;0;760000 ‘tbs_psapp02_alloc’=460309MB;;;0;760000

    Werte in DB02:

    PSAPP02 Size = 460.310,00 Free = 148.345,69

    Used = 68 %

    Ausserdem würde ich gerne den UNDO-Tablespace excluden – das funktioniert auf der Shell – aber nicht wenn ich es über Centreon konfiguriere.

    ./check_oracle_health -3 –method sqlplus –mode tablespace-usage –name=’^(?!(PSAPUNDO))’ –regexp –warning 90 –critical 95 –environment ORACLE_HOME= …usw…

    da kommt Centreon mit der RegExp nicht zurecht. Könntest du nicht einen seperaten Parameter einführen? ;-)

    Merci und Gruß aus dem Schwarzwald Christoph

    lausser Reply:

    Das geht nicht und wurde absichtlich nicht so gemacht. Der Wert *_allocated sollte dem SAP-Füllstand entsprechen.

  93. Marco P Says:
    February 1st, 2011 at 11:53

    Hi, when i start /usr/local/nagios/libexec/check_oracle_health –connect ora_db1 –mode tablespace-usage

    i get a error: ORA-24327: need explicit attach before authenticating a user (DBD ERROR: OCISessionBegin)

    sqlnet.ora exists: cat /usr/lib/oracle/11.2/client64/network/admin/sqlnet.ora names.directory_path = (TNSNAMES,EZCONNECT)

    env | grep TNS TNS_ADMIN=/usr/lib/oracle/11.2/client64/network/admin

    env | grep ORA ORACLE_HOME=/usr/lib/oracle/11.2/client64

    env | grep LD LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib

    But the following script works:

    ! /usr/bin/perl -w

    use DBI; my $dbh = DBI->connect( ‘dbi:Oracle:192.168.1.1:1521/prod’,’scott’,’tiger’,) || die “Connect error: $DBI::errstr”;

    ($name,$sal) = $dbh->selectrow_array( “SELECT ename,sal FROM scott.emp WHERE empno = 7369″); print “Name Gehalt\n”; print “—- ——\n”; print “$name $sal”; $dbh->disconnect;

    Any Ideas? Thanks Marco

    Marco P Reply:

    Found the solution: username and password was missing Thanks Marco

  94. Hector Roman Says:
    February 3rd, 2011 at 17:08

    Friends of your help, achieved after much stress perl install oracle driver, create the corresponding tsnames the database that I want to monitor and perform a tnsping this he responds.

    tnsping MPROD1

    TNS Ping Utility for Linux: Version 10.2.0.1.0 – Production on 03-FEB-2011 11:50:07 Copyright (c) 1997, 2005, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.10.180)(PORT = 1521))) (CONNECT_DATA = (SID = mprod1) (SERVER = DEDICATED))) OK (10 msec)

    By using the plugins this throws me the following error.

    ./check_oracle_health –method sqlplus –connect 172.20.10.180 –user monitoreo –password monitoreo2011 –mode tablespace-usage

    CRITICAL – cannot connect to 172.20.10.180. ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA .

    Any suggestions teachers

    lausser Reply:

    If there are fifty oracle instances running on 172.20.10.180, how should the listener know which one you mean? You already setup a tnsnames.ora, so why do you connect to the db server’s ip address instead of the oracle instance’s sid?

    ... --connect MPROD1 ...
    

    Hector Roman Reply:

    @lausser, Sorry for my stupidity, I had not noticed, it worked perfectly friend, when you want to come to Chile tells me it has won a stay thanks to the constant support

  95. M.-M. Suchanek Says:
    February 4th, 2011 at 10:50

    Hallo Herr Lausser,

    wenn ich “tablespace-can-allocate-next” abfrage, braucht meine DB länger als 60 sec für die Antwort, Ihr Plugin läuft dann auf “timed out”. Gibt es eine Möglichkeit den “time out” höher zu setzen?

    lausser Reply:

    http://www.nagios-wiki.de/nagios/doku3/configmain#service_check_timeout

    M.-M. Suchanek Reply:

    Hallo Herr Lausser,

    der Parameter “service_check_out” hat nix gebracht (:-(: /usr/lib/nagios/plugins/check_oracle_health –connect=db –user=nagios –password=”xxx” –mode=tablespace-can-allocate-next “UNKNOWN – check_oracle_health timed out after 60 seconds”

    lausser Reply:

    Was ist mit

    check_oracle_health --timeout 300 .....

    M.-M. Suchanek Reply:

    Danke! Jetzt funktioniert’s.

  96. Jens Says:
    February 7th, 2011 at 11:55

    Ich habe auch ein kleines Problem, welches aber nicht immer auftritt, sondern in unregelmässigen Abständen. Weiss leider keine Lösung, bisher hatte Neuinstallation des Plugins immer geholfen, aber diesmal nicht.

    Führt Nagios das Plugin aus, erhalte ich die MEldung: CRITICAL – cannot connect to CPLANDB. ERROR OCIEnvNlsCreate. Check ORACLE_HOME (Linux) env var or PATH (Windows) and or NLS settings, permissions, etc.

    Führe ich den gleichen Befehl von Kommandozeile aus, erhalte ich die Rückmeldung OK.

    lausser Reply:

    Der nagios-Prozess braucht das Oracle-Environment (export ORACLE_HOME=…). Am Besten ins init-Script eintragen.

    Jens Reply:

    Ich nochmal. Nachdem ich den Nagios Server neu gestartet hatte trat das Problem auf. Habe eben nur Nagios neu gestartet, seitdem funktioniert es wieder…

    lausser Reply:

    Wie ich es mir dachte. In deiner Shell hast du die Oracle-Environmentvariablen gesetzt. Wenn du Nagios aus der Shell heraus startest, erbt der Nagios-Prozess das Environment und alles funktioniert. Startet Nagios beim Booten per init-Script, dann hat er dieses Environment nicht und das Plugin funktioniert nicht. Daher muss man im init-Script ORACLE_HOME,LD_LIBRARY_PATH,TNSNAMES oder was auch immer nötig ist, extra eintragen.

  97. Jens Says:
    February 7th, 2011 at 12:25

    Alles klar, dann schau ich mal wie ich das mache :) Vielen Dank

  98. Jens Says:
    February 7th, 2011 at 12:31

    Und nochmal… ich habe eben nachgeschaut, im rc2.d Startup Script von Nagios habe ich die entsprechenden Variablen:

    export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/client_1 export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

    lausser Reply:

    Gibts da auch ein
    export ORACLE_BASE=….?
    Ohne das bekommt ja ORACLE_HOME keinen gültigen Wert.

  99. Jens Says:
    February 7th, 2011 at 12:55

    Danke -.- doch nur blind… scheints wohl gewesen zu sein. Zumindest sieht es nach dem Reboot noch gut aus.

  100. Joe Says:
    February 7th, 2011 at 16:53

    Hallo Herr Lausser,

    ich habe soeben ein wenig Zeit gehabt mich mit den Invalid Registry Components zu beschäftigen, die ein Check seit dem Update unserer Scriptversion bringt.

    In der Registry sind auf dem betroffenen Server einige Module mit ‘OPTION OFF’ vermerkt, sie sind also nicht aktiv. Der Check hat dafür aber leider keine Ausnahme im Code.

    lausser Reply:

    Das heisst, wenn man statt

              SELECT COUNT(DISTINCT STATUS) 
              FROM dba_registry
              WHERE status &lt;&gt; 'VALID'
    künftig
              SELECT COUNT(DISTINCT STATUS) 
              FROM dba_registry
              WHERE NOT (status = 'VALID' OR status = 'OPTION OFF')
    schreiben würde, dann wär’s perfekt?
  101. Conny Seifert Says:
    February 7th, 2011 at 19:49

    Hallo,

    beim mode tablespace-remaining-time (Version 1.6.8)scheints einen Bug zu geben. Nach Anpassung von Zeile 473 in tablespace.pm funktioniert dieser Mode bei mir.

    my $lookback = (($params{lookback} || 30) + 1) * 24 * 3600;

    Ausserdem würde es aus meiner Sicht Sinn machen die Ausgaben welche in den Zeilen 781, 792 und 795 zum “Ausgabepuffer” hinzugefügt werden um den Namen des Tablespace zu ergänzen.

  102. Mendes Says:
    February 8th, 2011 at 15:22

    Hello,

    I’m trying to use check_oracle_health. It works with a few modes (tnsping, connection-time, sga-data-buffer-hit-ratio, tablespace-fragmentation).

    With some other modes (tablespace-usage, tablespace-free, and others), I get the followin error:

    bumm Can’t call method “execute” on an undefined value at /opt/nagios/libexec/check_oracle_health line 4646.

    Can’t use an undefined value as an ARRAY reference at /opt/nagios/libexec/check_oracle_health line 4660.

    With other modes such as sga-library-cache-hit-ratio, sga-dictionary-cache-hit-ratio and some other modes, I get the following type of error :

    CRITICAL – unable to get sga lc

    Would you please help me ? Any advice would be appreciated…

    Regards, S. Mendes

    lausser Reply:

    Looks like your user doesn’t have the necessary privileges.

  103. Hector Roman Says:
    February 8th, 2011 at 15:47

    Lausser

    When I run the plugins from the console I works perfect, but when it is integrated with Nagios I get the following error;

    CRITICAL – Can not connect to mprod1. ORA-12154: TNS: Could not resolve the connect identifier Specified

    If I run tnsping the correct answer me

    tnsping mprod1 TNS Ping Utility for Linux: Version 10.2.0.1.0 – Production on 08-FEB-2011 10:43:17 Copyright (c) 1997, 2005, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 172.20.10.180) (PORT = 1521))) (CONNECT_DATA = (SID = mprod1) (SERVER = DEDICATED) (SERVICE_NAME = mprod1)) ) OK (0 msec)

    I think the problem is that the nagios user can not access the file tnsnames.ora, you think that is the problem?

  104. Mendes Says:
    February 9th, 2011 at 11:41

    You are right ! I just checked it and figured out that the user priviliges were not set correctly. Thank you very much !

    Hector Roman Reply:

    @Mendes,

    first edit the file just granting privileges tsnames.ora 777, and did not work, then chown nagios:nagios tsnames.ora and neither worked, is the same error message, any idea where to look?

    CRITICAL – cannot connect to mprod1. ORA-12154: TNS:could not resolve the connect identifier specified

    Hector Roman Reply:

    @Mendes,

    worth noting that when I use the plugins from a console as root is working properly

    Hector Roman Reply:

    @Hector Roman, solution

    chown nagios:dba tnsnames.ora

    -rwxrwxrwx 1 nagios dba 661 2011-02-03 12:39 tnsnames.ora*

  105. Chris Says:
    February 16th, 2011 at 14:00

    FROM Nagios: define command { command_name check_oracle_health command_line /usr/lib/nagios/plugins/check_oracle_health –connect=nagios/passwd@$ARG1$ –mode=$ARG2$

    }

    define service{ use generic-service host_name some_hostname service_description sga-shared-pool-reloads check_command check_oracle_health!db_name!sga-shared-pool-reloads }

    I receive message **ePN /usr/lib/nagios/plugins/check_oracle_health: “printf() on closed filehandle STATE at (eval 1) line 4332,”. same with keywords: sga-library-cache-hit-ratio sga-latches-hit-ratio pga-in-memory-sort-ratio

    from shell as root: ./check_oracle_health –connect=nagios/passwd@db_name–mode=sga-shared-pool-reloads OK – SGA shared pool reload ratio 0.25% | sga_shared_pool_reload_ratio=0.25%;1;10

    Looks good

    with those keywors it works fine from Nagios: sga-shared-pool-free OK OK – SGA shared pool free 25.07%. Tablespace usage also ok OK – tbs SYSTEM has 32220.80MB free space left

    I googled but I can’t get find the answer. Thanks for helping

  106. Chris Says:
    February 16th, 2011 at 14:21

    The answer is: chown -R nagios:nagios /var/tmp/check_oracle_health as mentioned above

  107. @Christina Says:
    February 23rd, 2011 at 16:24

    Hallo, habe check_oracle_health tablespace-free eingebunden. Funktioniert super bis auf folgende Fehlermeldungen die ich ab und zu kriege wie: **ePN /usr/local/nagios/libexec/check_oracle_health:’Can’t open /tmp/server::database::tablespace::freeVu1kJ.out:No such file or directory at (eval18)line 4631,<>chunk2.”. Nach einiger Zeit verschwindet diese Meldung wieder und der Tablespace wird richtig angezeigt. Hat jemand eine Idee für die Lösung dieses Problems? BR, Christina

    lausser Reply:

    Welche Version von check_oracle_health?

  108. @Christina Says:
    February 23rd, 2011 at 17:26

    Hi, die Version ist 1.6.4.

    lausser Reply:

    Dann bitte auf 1.6.8.1 updaten. Gerade an der Stelle mit den out-Files hat sich seitdem was getan.

  109. miwu Says:
    February 28th, 2011 at 11:10

    Hallo,

    nach dem Restart meines Servers bekomme ich bei meinem Check mit –mode=tablespace-remaining-time überall den Fehler “Can’t use an undefined value as an ARRAY reference at ./check_oracle_health line 3145″. Vorher bekam oich auch schon immer den Fehler “No data avaiable”. Unter /var/tmp/check_oracle_health liegen verschiedene Files zu den Tablespaces. Woran könnte das liegen? Vielen Dank! Miwu

  110. @Christina Says:
    February 28th, 2011 at 11:53

    Hi, danke für die Info. Wir werden auf die neue Version 1.6.8.1 updaten. Eine Frage noch um sicherzugehen, dass wir nicht noch zusätzlich permissions Probleme haben. Wir haben auch noch folgende Fehlermeldung ab und zu:CRITICAL – cannot connect to nagios/***@GIS. Argument x86_64/Linux 2.4.xx isnt numeric in numeric eq (==) at (eval 18) line 4594, line 1. ab und zu? Das PlugIn gehört den nagios user nur ist mir aufgefallen das der Oracle Client dem root gehört (tnsnames.ora). LG, Christina

  111. Sven Says:
    March 4th, 2011 at 16:52

    Hallo Gerhard,

    Ich habe einen ziemlich mysteriösen Fehler mit deinem ansonsten echt genialen Plugin.

    System: Red Hat Enterprise Linux v5.5 DBI v1.52 DBD v1.27 Nagios v3.2.3

    Der erste meiner Checks im command.cfg war

    define command{ command_name oracle_health_tnsping command_line /usr/local/nagios/libexec/check_oracle_health \ –connect=$ARG1$ \ –mode=$ARG2$ }

    Mein zweiter Eintrag war dann mit dem Befehl connection-time. Ich habe schon gelesen, dass connection-time einen funktionierenden TNS Listener voraussetzt und daher der tnsping eigentlich überflüssig wird. Interessant finde ich trotzdem:

    Wenn man zum Testen BEIDE Befehle ausführen möchte, funktioniert keiner, beide kommen mit ziemlich kryptischen Fehlermeldungen daher.

    cannot connect to svenf. install_driver(Oracle) failed: Can’t load ‘/usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/Oracle/Oracle.so’ for module DBD::Oracle: libclntsh.so.11.1: cannot open shared object file: No such file or directory at /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/DynaLoader.pm line 230.

    Wenn ich dann den tnsping auskommentiere, Nagios neu starte und auf ein funktionieren von connection-time hoffe, werde ich enttäuscht.

    connection-time funktioniert erst, wenn ich die service description verändere. Also etwa von “Connection Time” zu “01. Connection Time”

    Ich kann hier den Grund nicht ganz nachvollziehen, ist ja aber auch keine grosse Sache. Erwähnen möchte ich dies einfach nur, falls jemand der nach mir mit dem Plugin zu tun hat, diesen Fehler auch bekommt und dann nicht den ganzen Freitag damit verbrät ;)

    Danke und Gruss Sven

    lausser Reply:

    ‘libclntsh.so.11.1: cannot open shared object file’ besagt, daß LD_LIBRARY_PATH nicht richtig gesetzt ist. Vermutlich gilt das auch für PATH und ORACLE_HOME. Die müssen auch im Init-Script von Nagios stehen.

  112. Sven Says:
    March 4th, 2011 at 16:54

    Vergessen habe ich noch: check_oracle_health v.1.6.8.1

  113. Sven Says:
    March 7th, 2011 at 9:34

    Danke Gerhard, da war der Hund begraben ;)

  114. Thorsten (Panik) Says:
    March 10th, 2011 at 15:40

    Ver 1.6.8.1 und ich bin kein Oracle-Datenbänker Ist alls noch in der Testphase, deswegen auch als Ruth.

    wenn ich

    root@lxthorsten:/usr/local/nagios/libexec# ./check_oracle_health –connect dexicon –mode connected-users –user –password=

    absetze, erhalte ich folgendes

    Invalid conversion in printf: “%S” at /usr/lib/perl/5.10/IO/Handle.pm line 154. Invalid conversion in printf: “%’” at /usr/lib/perl/5.10/IO/Handle.pm line 154. Use of uninitialized value $value in numeric gt (>) at ./check_oracle_health line 4002. Use of uninitialized value $value in numeric gt (>) at ./check_oracle_health line 4003. Use of uninitialized value in sprintf at ./check_oracle_health line 1987. Use of uninitialized value in sprintf at ./check_oracle_health line 1991. OK – 0 connected users | connected_users=0;50;100

    Ein cat /tmp/check_oracle_health.trace zeigt: Thu Mar 10 14:33:40 2011: RESULT: $VAR1 = [];

    Thu Mar 10 14:33:40 2011: fetchrow_array: SELECT version FROM product_component_version WHERE product LIKE ‘%Server%’ Thu Mar 10 14:33:40 2011: args: $VAR1 = [];

    Thu Mar 10 14:33:40 2011: RESULT: $VAR1 = [];

    Thu Mar 10 14:33:40 2011: fetchrow_array: SELECT COUNT(*) FROM v$session WHERE type = ‘USER’

    Thu Mar 10 14:33:40 2011: args: $VAR1 = [];

    Thu Mar 10 14:33:40 2011: RESULT: $VAR1 = [];

    Thu Mar 10 14:33:40 2011: disconnecting DBD with handle

    Bin ich das Problem in meiner Unwissenheit, weil ich den Befehl falsch absetze oder ist das ein ‘echtes’ Problem?

    lausser Reply:

    Eigentlich sollte ich hier nicht antworten, solange ich ‘root’ sehe. Das führt nämlich spätestens dann zu Problemen, wenn das Plugin unter Regie des Nagios-Prozesses läuft. Allerdings ist das dann nicht mein Problem. Vermutlich hat der hier verwendete DB-User nicht die erforderlichen Berechtigungen. Näheres siehe Dokumentation.

    Thorsten (Panik) Reply:

    @lausser, [...]Ist alls noch in der Testphase, deswegen auch als Ruth.[...] habe ich bewusst erwähnt um etwaigen Kommentaren zu diesem Thema entgegen zu treten. Das es Rechteprobleme geben kann, wenn es von der Rutzh zum nagios User wechselt ist mir klar und ich habe das in den späteren Schritten im Hinterkopf.

    Der verwendete Oracle User sollte (theoretisch) lt. der Dokumentation angelegt worden sein – so die Aussage meines Oracle-DBAs.

  115. Theofanis Katsiaounis Says:
    March 14th, 2011 at 11:06

    Hello. I would like to ask you a question. When i run the check_oracle_health –connect AAA –user someuser –password somepassword –mode latch-waiting –name ‘user lock’ command from shell it works fine but when i try to use it through centreon it gives me the following errror. CRITICAL – cannot connect to asp. ORA-24327: need explicit attach before authenticating a user (DBD ERROR: OCISessionBegin)

    I have oracle instant client installed and DBI & DBD::Oracle. Do you have any possible idea this might be happening?? Thanks a lot in advance.

    lausser Reply:

    The centreon process probably needs a correct oracle environment (ORACLE_HOME, LD_LIBRARY_PATH etc..) too. You have this environment in your shell, thats why it works there. Put the export-statements in the centreon initscript.

  116. konrad Says:
    March 14th, 2011 at 16:06

    Hallo,

    benutze check_oracle_health (1.6.8.1) und kriege folgende Fehlermeldung:

    check_oracle_health –connect orcl11 –user nagios –password ****** –mode list-sysstats bumm Can’t call method “execute” on an undefined value at /usr/local/nagios3/libexec/check_oracle_health line 4730.

    Can’t use an undefined value as an ARRAY reference at /usr/local/nagios3/libexec/check_oracle_health line 4744.

    Woran liegt das? Vielen dank

    lausser Reply:

    Hat der Benutzer nagios die erforderlichen Privilegien?

  117. konrad Says:
    March 16th, 2011 at 11:52

    War ein Problem mit der Oracle DB. Jetzt klappt alles.

    vielen dank

  118. Niggo Says:
    March 16th, 2011 at 13:39

    Hi,

    hab ein Problem mit der tablespace-remaining-time Funktion. Beim Aufruf kommt folgender Fehler: Can’t use an undefined value as an ARRAY reference at /usr/local/nagios/libexec/dist/check_oracle_health line 3145.

    Alle anderen Funktionen gehen einwandfrei, die Berechtigungen in der DB stimmen und die Perl Module sind, soweit ich das sehe, auch alle korrekt installiert.

  119. Ashish Kumar Says:
    March 17th, 2011 at 9:47

    Thank you for donating this plugin to the community, it saves a lot of time for system administrators with no background of Oracle.

    I was wondering if there is a way to check multiple (more than one but not all) tablespaces.

    One more quick question, is there a way to display only those tablespaces which have some problem and not all. It seems to annoy DBAs few times when they have to read whole lot of message and figure out which tablespace has a problem.

    I am using version 1.6.3.

    Thank you

    lausser Reply:

    Tablespace monitoring for a selected subset of tablespaces: with the parameter –name you select a specific tablespace, like –name USERS. If you also add the parameter –regexp, the string in name is interpreted as a regular expression.

    --name='^((TABLESPACE1$)|(TABLESPACE2$)|(TABLESPACE3$))' --regexp
    Ot the other way round, every tyblespace except tablespace1/2/3:
    --name='^(?!(TABLESPACE1$)|(TABLESPACE2$)|(TABLESPACE3$))' --regexp
    How to shorten the output can be found in a blog article: http://labs.consol.de/lang/de/blog/nagios/verkrzen-der-ausgabe-von-check_oracle_health-und-konsorten It’s german, but you should understand it, there are the command line parameters and the corresponding screenshots.

    Ashish Kumar Reply:

    @lausser, cool, the regexp part really worked!

    Perhaps, I need to upgrade my plugin to latest one to get the short output part working.

    I could not thank you enough for the comprehensive documentation you write and for the active support you are providing.

    Cheers

  120. Gerard Schurink Says:
    March 24th, 2011 at 11:08

    I have to install the check_oracle_health check on a Centos 5.5 box but i don’t know what i have to install for extra tools from oracle to get this check working.

    Can anybody point me to the right direction please.

    Greetings,

    Gerard

  121. Alex Says:
    March 31st, 2011 at 9:10

    Hello

    Thanks for this plugin, I am using it in my project, but I have a big question to ask that I cannot solve.

    Can this plugin be used, to execute an external oracle sql file, and show the result of the query?

    I need to execute some sql files, which check database and return a result with the status of the oracle database.

    lausser Reply:

    have a look in the contrib subdirectory of the plugin’s tar-package. there you’ll find instructions how to extend the plugin with your self-written sql code.

  122. T. Katsiaounis Says:
    April 7th, 2011 at 17:31

    Hello. I would like to ask if there is a way to check if there are locks/blocks on the database. Thanks a lot in advance.

  123. Alex Says:
    April 8th, 2011 at 8:23

    Hi!

    Thanks for the answer, but I am not able to find extend documentacios at that folder (nor in any place), to can execute an sql file in the oracle database.

    Should not be as easy as in check_mssql_health?

  124. Peter Says:
    April 15th, 2011 at 7:38

    Hallo Gerhard, bin wieder mal mit deinem check_oracle_health beschäftigt. Wir werden in kürze Oracle-Streams umsetzen und da hätt ich ein paar SQL-Abfragen mit check_oracle_health umgesetzt. Jetzt hab ich nur wieder das Problem das es in der Shell funktioniert, aber ich in der Web-Umgebung eine Fehler bekomme.

    Hier mal die Daten: Nagios 3.2.0 check_oracle_health 1.6.8.1

    der Select den ich ausführen möchte: select count(*) from dba_capture where status <> ‘ENABLED’;

    Ergebnis in SQLPLUS:

    COUNT(*)

         0
    

    Den Select hab ich dann mit –encode umgewandelt.

    Ausführung mit check_oracle_health: check_oracle_health –connect zztr01.strmtest –username nagios –password passwort –mode sql –name select%20count%28%2A%29%20from%20dba%5Fcapture%20where%20status%20%3C%3E%20%27ENABLED%27 –warning 1 –critical 1 OK – select count(*) from dba_capture where status <> ‘enabled’: 0 | ‘select’=0;1;1

    Fehler in Weboberfläche: **ePN /usr/local/nagios/libexec/check_oracle_health: “Use of uninitialized value in numeric gt (>) at (eval 28) line 4016,”.

    Wenn ich “# nagios: -epn” im Script eintrage, dann hab ich zwar keinen Fehler mehr, aber ich bekomme auch keine Performancedaten zurück.

    Was meinst du da dazu?

    Viele Grüße Peter

  125. Markus Says:
    April 15th, 2011 at 13:05

    Hallo,

    erstmal vielen Dank für das tolle Plugin, habe bisher noch keinerlei Probleme gehabt, allerdings hänge ich momentan bei dem -mode sql. Ich erhalte immer die gleiche Fehlermeldung, egal ob ich die Abfrage encode oder nicht. ./check_oracle_health -connect dba.test -user nagios -password nagios -mode sql name=select%20count%28%2A%29%20from%20TSDWMASTER%2EJOB%5FEMAIL -warning 1 -critical 2 Use of uninitialized value in numeric gt (>) at ./check_oracle_health line 4002. Use of uninitialized value in numeric gt (>) at ./check_oracle_health line 4003. OK – select count(*) from tsdwmaster.job_email:

    ich habe jetzt in dieser Abfrage den entsprechenden tablespace mit eingebaut, aber auch wen ich diesen weg lasse und seperat mit -tablespace übergebe bekomme ich diese Fehlermeldung. Was mache ich falsch?

    Vielen Dank vorab

    Grüße Markus

  126. OMD: State Retention der nagios-Plugins in /var/tmp !??? | KenntWas.de - Technische Tipps Says:
    May 28th, 2011 at 12:34

    [...] absoluten Pfad (/var/tmp/) zum Cachen von Dateien oder als statesdir. Ein Beispiel dafür sind check_oracle_health und check_logfiles, die sich gewisse Informationen zwischen zwei Aufrufen persistent [...]

  127. check_oracle_health unter Windows: zusätzliche Tipps | KenntWas.de - Technische Tipps Says:
    August 16th, 2011 at 8:32

    [...] PERL5LIBpl2batperl2exeSIGALRM unter Windows32Abhilfe: –method=sqlplusFazitDas Nagios-Plugin check_oracle_health wurde ursprünglich für Linux geschrieben. Da es sich um ein Perlscript handelt, sollte es doch [...]

  128. Niepoprawna (niepełna) obsługa zakresów przez check_oracle_health « guzik Says:
    September 22nd, 2011 at 10:35

    [...] szerszy artykuł o wtyczce check_oracle_health od ConSol Labs i w czasie testów zauważyłem, że obsługa zakresów jest niepoprawna, a w [...]

  129. Monitoring sinusoidy czyli Nagios i dynamicznie zmieniające się progi « guzik Says:
    September 24th, 2011 at 23:00

    [...] poradzić sobie z tym problemem jeśli monitorujemy bazę (dane w bazie) Oracle za pomocą wtyczki check_oracle_health od ConSol Labs (lub SQL Server wtyczką check_mssql_health; pozostałe dwie – [...]

  130. Happy new year ! – ConSol* Labs Says:
    December 30th, 2011 at 13:49

    [...] Nagios plugins got a major boost, with the rock-stars check_logfiles and check_oracle_health, which you probably can find on any larger Nagios installation [...]

  131. Installer shinken ? c’est facile ! | Communauté Francophone de la Supervision Libre Says:
    January 31st, 2012 at 12:28

    [...] check_oracle_health [...]

  132. Новости » Blog Archive » [Перевод] «Вы, должно быть, сумасшедший, если не используете потенциал Open Source». Герхард Лауссер о системах мониторин Says:
    March 1st, 2013 at 10:10

    [...] check_oracle_health — Oracle SQL Server check_mssql_health — Microsoft SQL Server check_mysql_health — MySQL Server check_db2_health — IBM DB2 SQL Server [...]

  133. Новости » Blog Archive » [Перевод] «Вы, должно быть, сумасшедший, если не используете потенциал Open Source». Герхард Лауссер о системах мониторин Says:
    March 1st, 2013 at 10:11

    [...] check_oracle_health — Oracle SQL Server check_mssql_health — Microsoft SQL Server check_mysql_health — MySQL Server check_db2_health — IBM DB2 SQL Server [...]

  134. Плагины для мониторинга СУБД | DK5 Says:
    March 3rd, 2013 at 15:40

    [...] SQL Server – check_oracle_health Microsoft SQL Server – check_mssql_health MySQL Server – check_mysql_health IBM DB2 SQL [...]

  135. Daniel Esteves Says:
    April 29th, 2013 at 17:09

    I’m using check_oracle_health 1.7.8.1 with Nagios 3.5.0 with embedded perl, but the script return the error ** ePN/usr/local/nagios/libexec/check_oracle_health: “can’t exec”/usr/local/nagios/bin/p1.pl “: Permission denied at (eval 1) line 6489,”. The check is not rescheduled and is frozen. The nagios user has permission (775) in the file/usr/local/nagios/bin/p1.pl

  136. Gert Says:
    May 17th, 2013 at 8:28

    ‘lo all,

    I just upgraded from 1.7.7.3 to 1.7.8.1 and there seems to be an error in a reference to “Nagios/DBD/Oracle/Server/Instance/SGA/Latches.pm”

    This should probably be “Latch.pm”, right ? Or maybe it didn’t make it through to the distribution tar ?

    grtz, gert

  137. meds Says:
    May 21st, 2013 at 12:24

    Hi, I used nagios to monitors thousound of server (AIX, SOLARIS, WINDOWS…) and now i want to monitor some information of oracle database such as tbs, user’s connection … I see that the plugins check_oracle_health is the best one to give me all this informations. no, my question is, should’i install this plugin on the “nagios server” or on the “database server” and what are the requiered thins that shoud’i prepare to do it. and if you have the steps for the installation that worked great, please let me know. The nagios server is on “CENTOS” i use FAN distributed distribution The database server is on “AIX 7.1″ Best regard.

  138. Markus Says:
    June 4th, 2013 at 16:51

    Hallo

    Erstmal möchte ich ein großes Lob an die Entwickler dieses Tools aussprechen.

    Bzgl. Berechtigungen etc. will ich das Skript als user nagios über sudo mit dem Oracle User ausführen lassen. Dazu habe ich die entsprechenden Rechte in der /etc/sudoers gesetzt. Ausführung klappt auch wunderbar. Leider bekomme ich dann die bereits bekannte Meldung:

    Can’t load ‘/usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/Oracle/Oracle.so’ for module DBD::Oracle: libclntsh.so.10.1:…….

    Ich weiß , dass bei der Skriptausführung die Shell Umgebungsvariablen nicht verfügbar sind und die Variable LD_LIBRARY_PATH gesetzt sein muss.

    Jedoch verstehe ich das Skript so, dass es diese Variable aus dem Oracle_Home/lib kombiniert.

    Ich denke ich verstehe ich grundsätzlich etwas falsch.

    Was kann ich evtl. machen um dieses Skript mit den Rechten des Users Oracle laufen zu lassen ??

    Danke für alle Antworten.

    lausser Reply:

    Servus, bereits bei der Ausführung durch den User Nagios muss das Environment stimmen. Das kann entweder durch Setzen der Variablen im Init-Script von Nagios passieren oder man gibt sie einzeln als Kommandozeilenparameter mit. –environment ORACLE_HOME=/opt/oracle… –environment LD_LIBRARY_PATH=/usr/lib:/opt/oracle/lib:…

  139. Vincent Says:
    June 13th, 2013 at 22:49

    Amazing work !

  140. ossmon Says:
    June 18th, 2013 at 15:19

    check_oracle_health tablespace-free: Kombination Prozent und Absolut Ist es möglich eine solche Abfrage durchzuführen ?

    schlag Alarm, wenn weniger als 10% frei und weniger als 300MB frei ist oder schlag nicht alarm, wenn weniger als 10% frei aber mehr als 300MB frei ist

  141. Günter Says:
    June 26th, 2013 at 10:24

    Hallo,

    habe ein Problem mit dem sga-shared-pool-free Parameter. Es kommt oft vor, dass mehr als 100% angezeigt wird. Habe dies bereits im Forum gepostet aber leider noch keine Antwort bekommen http://labs.consol.de/forum/#/discussion/335/sga-shared-pool-free-100

  142. Diego Says:
    July 17th, 2013 at 16:51

    Execution of command: check_oracle_health –mode sql –name ‘select status from v$instance’ produce this error: UNKNOWN – got no valid response for select status from v$instance If I execute the query from sqlplus with same credentials in the server where Oracle DB is installed the query works correctly. What can I do ? How can resolve this issue ?

  143. Facundo Says:
    July 17th, 2013 at 22:20

    Hello, I have an error when I try to connect a Stand By Database. The error is: ORA-01034: ORACLE not available (DBD ERROR: OCISessionBegin). If I connect to another database (non-stand by) with the same command (/usr/lib/nagios/plugins/check_oracle_health –connect=”sys/sysp2@EXTTROP as sysdba” –mode connection-time) all works fine. Do you know this issue? Thanks in advanced.

  144. Tim Says:
    July 18th, 2013 at 15:35

    I am having problems getting the –absolute parameter to work. I want to use this together with the –mode sql parameter to chart the growth over time of certain tables. I thought using the absolute parameter would only show the difference between the currrent and the last run.

    Tim Reply:

    @Tim, The problem I am having is that Iit always shows the the full count and not the delta from the last run

  145. Tim Says:
    July 19th, 2013 at 13:12

    Hi,

    I want to use the –absolute parameter together with the mode sql. Can this be made available in a future version

  146. Ashish Kumar Says:
    August 22nd, 2013 at 11:48

    Hi,

    I’m trying to exclude all tablespaces from monitoring that starts with the pattern “AN_”. The following command gives the desired output:

    $ check_oracle_health –mode tablespace-free –tablespace ‘^(?!(AN_))’ –regexp

    Now, I want to exclude all tablespaces that contains the string “AN_”, e.g. FOO_AN_BAR, USER_AN_QUX. I tried multiple regexes but it did not work. Can you please provide the correct options to achieve this?

    Thank you

    Ashish Kumar Reply:

    @Site moderator, Hi, I was doing some mistake and apologise for the trouble. I have got it working by using:

    –mode tablespace-usage –name ‘^(?!(.*AN_))’ –regexp

    You may want to remove my previous post.

    Thanks

  147. Thilo Says:
    September 9th, 2013 at 11:06

    Hi, the plugin currently calculates tablespace’s max size using … SUM(DECODE(a.autoextensible, ‘YES’, a.maxbytes, ‘NO’, a.bytes)) maxbytes … IMHO it would be more exact to use … SUM(DECODE(a.autoextensible, ‘YES’, greatest(a.maxbytes,a.bytes), ‘NO’, a.bytes)) maxbytes … instead. Although the maxsize of a datafile might be limited for autoextension, it’s valid to exceed it issuing “alter database datafile …resize ..”. Using greatest() as shown above, you’ll keep these “overallocations” in mind. Kind regards, Thilo.

  148. rdx Says:
    September 10th, 2013 at 8:46

    cool!Thank you for your work