Oracle DB monitoring with graphs

[lrnnetops]

Hi Team,

We wanted to monitor our Oracle DB Ver.11.2.0 with graphing.

# Nagios Server Details
Machine Type – Virtual Machine
Server Operating System - CentOS release 6.0
Operating System Architecture - i686

# Nagios XI Details
Version - 5.4.3

We started with Configuration by changing "LD_LIBRARY_PATH" & "ORACLE_HOME" as per our environment by editing check commands.

Then started by Configuration Wizards => Oracle Query. Filled the details & submitted the changes. service got added but we unable to see the graph for the added service.

nagios_1.JPG

Later we added some more services by Configuration Wizards => Oracle Tablespace. Filled the details & submitted the changes. this time only few of the services graph got generated.

nagios_tablespace.JPG

Please help us with below queries.

1 - Why graph not getting generated for the service we added

nagios_1.JPG

?

2 - Why not all services got graphs

nagios_tablespace.JPG

?

3 - In Configuration Wizards for oracle we see only three options Oracle Query, Oracle Tablespace & Oracle Serverspace. we wanted to monitor below mentioned oracle DB functions. how can we monitor those?

Extent Size skip USERS RBS TOOLS
Locks
Login
Max Extents
Session Count
Events Queue
Log Rows

4 - If we wanted to monitor our custom oracle query how can we add it as a service & how can we generate its graph?

Regards,
Rohan

[tgriep]

1. Depending on the plugin and how it is configured, it may not output performance data. When that happens, the Nagios XI server cannot gather the performance data and it will not generate a graph.
Not all plugins display performance data. To check to see if there is performance data, go to the Service Details menu, select the service and click on the Advanced Tab. If you see data in the Performance Data: field, then a graph should be generated.

2. Same as above

3. Take a look at the help screen for the check_oracle_health plugin to see if it can monitor what you are looking for.
You may have to create a simple Query and use the --mode sql option below to get some of the data you are looking for.

Code: Select all

 Check various parameters of Oracle databases 

  Usage:
    check_oracle_health [-v] [-t <timeout>] --connect=<connect string>
        --username=<username> --password=<password> --mode=<mode>
        --tablespace=<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)
       session-usage                    (Percentage of sessions used)
       process-usage                    (Percentage of processes used)
       rman-backup-problems             (Number of rman backup errors during the last 3 days)
       sga-data-buffer-hit-ratio        (Data Buffer Cache Hit Ratio)
       sga-library-cache-gethit-ratio   (Library Cache (Get) Hit Ratio)
       sga-library-cache-pinhit-ratio   (Library Cache (Pin) Hit Ratio)
       sga-library-cache-reloads        (Library Cache Reload (and Invalidation) Rate)
       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)
       corrupted-blocks                 (Number of corrupted blocks in database)
       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)
       datafiles-existing               (Percentage of the maximum possible number of datafiles)
       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
    --commit
       turns on autocommit for the dbd::oracle module

  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.

4. For a custom query, you would use the Oracle Query Wizard. It should run and create a graph if the query only returns a number.

[lrnnetops]

Hi Tgriep,

Thank you for the update.

We are using nagios plugins only.

Can you help me with queries.

Service 1 "darlagp Connected Users" able to produce the graph. as in Advanced Tab Performance Data field data is their in numbers.

graph.jpg

but in service 2 "Oracle Query - Test Query" not able to generate the graph but Advanced Tab Performance Data field data is their in numbers.

no-graph.jpg

what causing "Oracle Query - Test Query" unable to produce the graph?

below details of command set & argument set for check for both the services.

commands.jpg

# ARG1 darlagp Connected Users

--connect '10.101.2.231:1526/darlagp' --username 'XXXXXXXXX' --password 'XXXXXXXX' --mode connected-users --warning 50 --critical 100

=====================

# ARG1 Oracle Query - Test Query

--connect '10.101.2.231:1526/darlagp' --username 'XXXXXXXX' --password 'XXXXXXXX' --mode sql --name="select count(*) from v\$session where status = 'ACTIVE'" --warning 50 --critical 200

Regards,
Rohan

[tmcdonald]

Two great resources to look into:

1.) Performance Graph Problems article on our KB - Walk through the troubleshooting in this guide as a starting point.
2.) Install the Performance Data Tool by @Box293 - Will help you see the performance data in tabular form, to verify it is being properly saved in the RRD files.

The KB article might solve your problem, but if not try out the tool in the second link and post screenshots of the performance data tables.

[tgriep]

In the performance data for the query, you see a dollar sign on the end of the critical threshold that is an invalid character and it causing the graph to not be created.
This can be fixed by changing the command from

Code: Select all

--connect '10.101.2.231:1526/darlagp' --username 'XXXXXXXX' --password 'XXXXXXXX' --mode sql --name="select count(*) from v\$session where status = 'ACTIVE'" --warning 50 --critical 200

to

Code: Select all

--connect '10.101.2.231:1526/darlagp' --username 'XXXXXXXX' --password 'XXXXXXXX' --mode sql --name="select count(*) from v$"$"session where status = 'ACTIVE'" --warning 50 --critical 200

Save and Apply the Config and within 20 minutes is should start to graph. If not, you will have to delete the old graph data so it can create new ones.

The problem is that when using the $ in a command, they usually need special escaping like the example I posted.

[lrnnetops]

Hi tgriep,

Thanks for your suggestion it worked....

both by lock & session count queries are working with nagios xi & producing performance graphs.

Please help us with below mentioned issue.

We are trying to execute & monitor output from below query.

"SELECT owner,segment_name,segment_type, EXTENTS, MAX_EXTENTS,round((EXTENTS/MAX_EXTENTS)*100,2) PCT_EXTENT_USED FROM DBA_SEGMENTS WHERE SEGMENT_TYPE != 'CACHE' ORDER BY 6 desc"

we kept check command = check_xi_oracleserverspace

our Command view as below =
---------------
/usr/bin/env LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib ORACLE_HOME=/usr/lib/oracle/11.2/client64 $USER1$/check_oracle_health $ARG1$
---------------

our $ARG1$ as below =
---------------
--connect '10.101.2.231:1526/darlagp' --username 'XXXXXXXX' --password 'XXXXXXXX' --mode sql --name="SELECT owner,segment_name,segment_type, EXTENTS, MAX_EXTENTS,round((EXTENTS/MAX_EXTENTS)*100,2) PCT_EXTENT_USED FROM DBA_SEGMENTS WHERE SEGMENT_TYPE != 'CACHE' ORDER BY 6 desc" --warning 80 --critical 90
---------------

Once we do "Run check command" we are getting below output.
---------------
[nagios@xxxxxxx ~]$ /usr/bin/env LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib ORACLE_HOME=/usr/lib/oracle/11.2/client64 /usr/local/nagios/libexec/check_oracle_health --connect '10.101.2.231:1526/darlagp' --username 'xxxxxxxx' --password 'xxxxxxxx' --mode sql --name="SELECT owner,segment_name,segment_type, EXTENTS, MAX_EXTENTS,round((EXTENTS/MAX_EXTENTS)*100,2) PCT_EXTENT_USED FROM DBA_SEGMENTS WHERE SEGMENT_TYPE != 'CACHE' ORDER BY 6 desc" --warning 80 --critical 90
UNKNOWN - got no valid response for SELECT owner,segment_name,segment_type, EXTENTS, MAX_EXTENTS,round((EXTENTS/MAX_EXTENTS)*100,2) PCT_EXTENT_USED FROM DBA_SEGMENTS WHERE SEGMENT_TYPE != 'CACHE' ORDER BY 6 desc
---------------

please help us to get it resolved.

Regards,
Rohan

[lrnnetops]

HI Team,

This is related to Alert : Tablespace Usage, This check is basically designed based on AUTO EXTEND ON for Datafailes by Nagios.

We need the usage to be calculated on actual usage of tablesapce , we do not want that the alert based on Autoextend extend.

Please suggest if you have related plugin or guide us what needs to change in code for the above request ?

Regards,
Rohan

[tgriep]

Can you run that query from your previous post on the Oracle server and post what the output is?

First place to look for another plugin that functions how you want is to look at the exchange site.
https://exchange.nagios.org/

If you don't find one that suits your needs you may want to contact the Sales department at [email protected] for custom development to get the feature added to the plugin.

[lrnnetops]

Hi tgriep,

Please find attached output of queries we ran on our DB server.

Let me know with your findings.

Regards,
Rohan

[tgriep]

I did a quick search of the Exchange site and didn't find any plugins that are for directly querying for MAX EXTENTS.
The only other option is to use the Oracle Query Wizard / Plugin to get the results you are looking for.