Monitoring Oracle Database

December 15, 2020

Monitoring Oracle Database with Linux GDMA

GroundWork Monitor makes it simple to monitor the health of Oracle databases, whether the need is simple monitoring of availability or for capacity planning purposes.

Oracle databases may be monitored either directly on the Oracle host or from a different host, using the GroundWork Distributed Monitoring Agent (GDMA). In both scenarios, SQL queries are used to provide the data from the database. This offers flexibility in that any Oracle query you create that returns a numerical result can be monitored as well as measured. As database monitoring needs vary on the organizational level – and even the database level, this flexibility is important.

Local Monitoring vs. Remote Monitoring

When deciding what you’re going to monitor, it’s also important to evaluate from which perspective the monitoring should come from:

• Local method: If you need to know the general health of the database, and gather metrics for capacity planning purposes, then this can be local monitoring, meaning you could install GDMA on the Oracle server itself to gather such data.
• Remote method: If you are unable to install GDMA on the Oracle server for whatever reason, or need to get data about the query from the perspective of an end-user (such as latency), then remote monitoring of the database may make sense. This scenario is the same process as installing GDMA on the Oracle server itself.

Of course, you may monitor using both local and remote methods if you wish to do so, separating the service checks appropriately to suit the situation.

Oracle GDMA Profile

We provide a profile for GDMA allowing you to monitor the basic health of your Oracle database, including:

• Database availability
• Tablespace size
• Max processes

This profile, along with instructions on how to implement it, are available at Monitoring Oracle with Linux GDMA.

Extending Oracle Monitoring

This profile monitors the Oracle database by using queries in a single line without semicolon characters at the end of the line which return a numerical value. These SQL queries are stored on the system which GDMA is installed in /usr/local/groundwork/nagios/libexec/sql/, you may want to review the existing SQL queries provided for an idea of how the query should be formatted.

Once you have a query, you need to determine which column to key in on for the value you want to monitor. The example below shows the tablespace query and its output, and we key in on the PCT_USED column for our threshold values, which is column 1 (starting from 0).

Any query you can think of that fits the proper format can be used for monitoring purposes.

SQL> select u.tablespace_name, round((u.total_mb-nvl(f.free_mb+decode(sign(u.total_mb-current_mb),1,u.total_mb-current_mb,0),0))*100/u.total_mb,0) pct_used from (select tablespace_name, round(sum(bytes)/1024/1024,0) current_mb, round(sum(decode(autoextensible, ‘NO’, bytes, decode(sign(bytes-maxbytes),1, bytes, 0, bytes, maxbytes)))/1024/1024,0) total_mb from dba_data_files group by tablespace_name) u, (select tablespace_name, round(sum(bytes)/1024/1024,0) free_mb from dba_free_space group by tablespace_name ) f, (select tablespace_name, contents from dba_tablespaces) t where u.tablespace_name = f.tablespace_name (+) and t.tablespace_name = u.tablespace_name and t.contents not in (‘UNDO’,’TEMPORARY’) order by pct_used desc;

TABLESPACE_NAME PCT_USED
—————————— ———-
TEST01 70
SYSAUX 4
SYSTEM 3
USERS 0

Once you’ve decided on a query you want to turn into a service, save the query to a file with a .sql extension, and place it on the Oracle server running GDMA in the /usr/local/groundwork/nagios/libexec/sql/ directory.

You can then create a copy of the gdma_check_tablespace service available and the check_oracle_tablespace service external. In the external, you will change the -f parameter from ‘tablespace’ to the name of the SQL query file you have uploaded to the server.

In Summary

By installing GDMA and importing the provided profile, you can be up and running with Oracle Database monitoring in a matter of minutes. Extending the monitoring beyond what is already provided is as easy as placing your desired query in the proper directory, and adding a new service check for it.

See our support portal GroundWork Support at www.support8.gwos.com.

Thanks for reading our Blog.