Monitoring invalid objects using check_oracle_health

[RIDS_I2MP]

Hi Team,

We are monitoring the Oracle DB parameter 'invalid objects' using the plugin check_oracle_health. When we run the plugin it is comparing the sum of Invalid objects, Invalid index partitions and invalid registry components and comparing it against the threshold value. Could you please let us know how we can set this to check only the invalid objects components value against the thresholds.

[jolson]

Could you show us the check as it's configured now?

The help page is as follows:

Code: Select all

$ check_oracle_health --help


Copyright (c) Gerhard Lausser

Check various parameters of Oracle databases 

Usage:

check_oracle_health [-v] [-t ] --connect=


    --username= --password= --mode=


    --tablespace=


check_oracle_health [-h | --help]


check_oracle_health [-V | --version]


Options:


  --connect


     the connect string


  --username


     the oracle user


  --password


     the oracle user's password


  --warning


     the warning range


  --critical


     the critical range


  --mode


     the mode of the plugin. select one of the following keywords:


     tnsping                          (Check the reachability of the server)


     connection-time                  (Time to connect to the server)


     connected-users                  (Number of currently connected users)


     sga-data-buffer-hit-ratio        (Data Buffer Cache Hit Ratio)


     sga-library-cache-hit-ratio      (Library Cache Hit Ratio)


     sga-dictionary-cache-hit-ratio   (Dictionary Cache Hit Ratio)


     sga-latches-hit-ratio            (Latches Hit Ratio)


     sga-shared-pool-reload-ratio     (Shared Pool Reloads vs. Pins)


     sga-shared-pool-free             (Shared Pool Free Memory)


     pga-in-memory-sort-ratio         (PGA in-memory sort ratio)


     invalid-objects                  (Number of invalid objects in database)


     stale-statistics                 (Find objects with stale optimizer statistics)


     tablespace-usage                 (Used space in tablespaces)


     tablespace-free                  (Free space in tablespaces)


     tablespace-remaining-time        (Remaining time until a tablespace is full)


     tablespace-fragmentation         (Free space fragmentation index)


     tablespace-io-balance            (balanced io of all datafiles)


     tablespace-can-allocate-next     (Segments (of a tablespace) can allocate next extent)


     datafile-io-traffic              (io operations/per sec of a datafile)


     soft-parse-ratio                 (Percentage of soft parses)


     switch-interval                  (Time between redo log file switches)


     retry-ratio                      (Redo buffer allocation retries)


     redo-io-traffic                  (Redo log io bytes per second)


     roll-header-contention           (Rollback segment header contention)


     roll-block-contention            (Rollback segment block contention)


     roll-hit-ratio                   (Rollback segment hit ratio (gets/waits))


     roll-wraps                       (Rollback segment wraps (per sec))


     roll-extends                     (Rollback segment extends (per sec))


     roll-avgactivesize               (Rollback segment average active size)


     seg-top10-logical-reads          (user objects among top 10 logical reads)


     seg-top10-physical-reads         (user objects among top 10 physical reads)


     seg-top10-buffer-busy-waits      (user objects among top 10 buffer busy waits)


     seg-top10-row-lock-waits         (user objects among top 10 row lock waits)


     event-waits                      (processes wait events)


     event-waiting                    (time spent by processes waiting for an event)


     enqueue-contention               (percentage of enqueue requests which must wait)


     enqueue-waiting                  (percentage of time spent waiting for the enqueue)


     latch-contention                 (percentage of latch get requests which must wait)


     latch-waiting                    (percentage of time a latch spends sleeping)


     sysstat                          (change of sysstat values over time)


     flash-recovery-area-usage        (Used space in flash recovery area)


     flash-recovery-area-free         (Free space in flash recovery area)


     sql                              (any sql command returning a single number)


     list-tablespaces                 (convenience function which lists all tablespaces)


     list-datafiles                   (convenience function which lists all datafiles)


     list-enqueues                    (convenience function which lists all enqueues)


     list-latches                     (convenience function which lists all latches)


     list-events                      (convenience function which lists all events)


     list-background-events           (convenience function which lists all background events)


     list-sysstats                    (convenience function which lists all statistics from v$sysstat)


  --name


     the name of the tablespace, datafile, wait event, 


     latch, enqueue, or sql statement depending on the mode.


  --name2


     if name is a sql statement, this statement would appear in


     the output and the performance data. This can be ugly, so 


     name2 can be used to appear instead.


  --regexp


     if this parameter is used, name will be interpreted as a 


     regular expression.


  --units


     one of %, KB, MB, GB. This is used for a better output of mode=sql


     and for specifying thresholds for mode=tablespace-free


  --ident


     outputs instance and database names


Tablespace-related modes check all tablespaces in one run by default.


If only a single tablespace should be checked, use the --name parameter.


The same applies to datafile-related modes.


tablespace-remaining-time will take historical data into account. The number


of days in the past can be given with the --lookback parameter. (Default: 30)


In mode sql you can url-encode the statement so you will not have to mess


around with special characters in your Nagios service definitions.


Instead of 


--name="select count(*) from v$session where status = 'ACTIVE'"


you can say 


--name=select%20count%28%2A%29%20from%20v%24session%20where%20status%20%3D%20%27ACTIVE%27


For your convenience you can call check_oracle_health with the --encode


option and it will encode the standard input.

To me it looks like you can use the mode 'invalid-objects' to achieve what you're trying to do. i.e. ./check_oracle_health --username=X --password=X --mode invalid-objects

I don't have an in-house box that I can use to check this plugin against - does the above command return the results that you would expect?

[RIDS_I2MP]

The above command i.e --mode invalid-objects gives me an out which lists invalid-objects and other components.

[ssax]

Just so we are testing with the same version, what version of Oracle DB and instant client are you running?

[BanditBBS]

You can't do what you are wanting to do. You'd need to modify the code and find that portion and modify the SQL statement in the plugin so that it only counts the invalid objects for you and not all the other crap it does(trust me, I fully understand your desire ).

Also, make sure and update to the latest version if you haven't, the version installed with XI is quite old and many bug fixes have come out for check_oracle_health.

[lmiltchev]

BanditBBS is correct - you will need to modify the "check_oracle_health" plugin. In my plugin, I can see that "invalid indexes", "invalid index partitions", and "invalid registry components" are referenced around lines 3769-3775.

Code: Select all

3764     } elsif ($params{mode} =~ /server::database::invalidobjects/) {
3765       my @message = ();
3766       push(@message, sprintf "%d invalid objects",
3767           $self->{invalidobjects}->{invalid_objects}) if
3768           $self->{invalidobjects}->{invalid_objects};
3769       push(@message, sprintf "%d invalid indexes",
3770           $self->{invalidobjects}->{invalid_indexes}) if
3771           $self->{invalidobjects}->{invalid_indexes};
3772       push(@message, sprintf "%d invalid index partitions",
3773           $self->{invalidobjects}->{invalid_ind_partitions}) if
3774           $self->{invalidobjects}->{invalid_ind_partitions};
3775       push(@message, sprintf "%d invalid registry components",
3776           $self->{invalidobjects}->{invalid_registry_components}) if
3777           $self->{invalidobjects}->{invalid_registry_components};