Oracle

This document describes the Oracle functionality of PandoraFMS Discovery.

Introduction

This plugin is designed to monitor Oracle databases by executing queries that extract key information related to the performance and status of the databases. This information includes metrics such as the number of connections, query counts, restart status, fragmentation ratio, and cache. These data points will be reflected in PandoraFMS through modules that provide statistical value, within an agent representing each database.

It's important to note that this plugin is specifically developed for use with Pandora FMS Discovery. Unlike other plugins, it doesn't generate agents through XML; instead, all the discovered information is returned in the JSON output of the plugin.

Prerrequisites

This plugin establishes remote connections to the databases it monitors, so it's essential to ensure connectivity between the Pandora FMS server and these databases. Proper network and firewall configurations must be in place to allow communication between the server and the remote databases to ensure the plugin's effective operation.

Parameters and configuration

Parameters

--conf Path to the configuration file.
--target_databases Path to the configuration file containing the database targets.
--target_agents Path to the configuration file containing the agent targets.
--custom_queries Path to the configuration file containing custom queries.


Configuration file (--conf)

agents_group_id: The ID of the group where agents will be created.
interval: The agent monitoring interval in seconds.
user: The connection username.
password: The password for the user.
threads: The number of threads to be used for agent creation.
modules_prefix: The prefix for module names.
execute_custom_queries: Activate with 1 to enable the use of custom queries.
analyze_connections: Activate with 1 to enable connection monitoring.
engine_uptime: Activate with 1 to enable runtime monitoring.
query_stats: Activate with 1 to enable query statistics monitoring.
check_tablespaces: Activate with 1 to enable tablespace statistics monitoring.
fragmentation_ratio: Activate with 1 to enable fragmentation ratio statistics monitoring.
cache_stats: Activate with 1 to enable cache statistics monitoring.
thick_mode = < Activate with 1 to enable thick mode connection >
client_path = < Client path. Only necessary in case of using the thick mode >

Example

agents_group_id = 10
interval = 300
user = sa 
password = HHgD85V@
threads = 1
modules_prefix = 
execute_custom_queries = 1
analyze_connections = 1
engine_uptime = 1
query_stats = 1
check_tablespaces = 1
fragmentation_ratio = 1
cache_stats = 1
thick_mode = 1
client_path = /usr/lib/oracle/21/client64/lib/

The standard installation of instant client 19.8 is done in the /usr/lib/oracle/19.8/client64/lib path.

List of target databases (--target_databases):

The content of the file should be a list of target databases, with each database separated by commas or lines. The format for a database can be any of the following:

ip/sid
ip:puerto/sid 
ip:puerto/service_name

If the port is not specified, the default TCP port 1521 will be used for connecting to the target.

Example:

172.17.0.3:1521/FREEPDB1

Each line in the file should represent a separate target database using one of these formats. This file is used to specify the databases that the plugin should monitor.

List of target agents (--target_agents):

The content of the file should be a list of agent names, with each agent separated by commas or lines. These agent names will be used to associate the information from each target database with the corresponding specified agent name, instead of letting the plugin generate agent names automatically.

The position of each agent name in the list should match the position of the target database in its own list. In other words, the name for the first target database should be the first name in this list, considering that blank lines are ignored.

Example:

agente1,,agente3
agente4
agente5,agente6,agente7,,agente9

Custom queries (--custom_queries)

A module must be introduced for each custom query that is intended to be monitored. The modules must follow a structure, which is as follows:

check_begin --> Module opening label
name --> Module name
description --> Description of the module.
operation --> Operation type
datatype --> Module type
min_warning --> Configuration of the minimum warning threshold
max_warning --> Configuration of the maximum warning threshold
str_warning --> Warning string configuration
warning_inverse --> Activate the inverse interval with 1 for warning threshold
min_critical --> Setting the minimum critical threshold
max_critical --> Setting the maximum critical threshold
str_critical --> Critical string configuration
critical_inverse --> Activate inverse interval with 1 for critical threshold
module_interval --> This interval is calculated as a multiplier of the agent interval.
target --> Custom query
target_databases --> Database agents in which the module will be created
check_end --> Module closing tag

Example:

check_begin
name Select 1
description Number of invalid objects
operation value
datatype generic_data
min_warning 5
target SELECT 1 FROM dual
target_databases all
check_end

check_begin
name NumeroConexiones
description Number of connections
operation value
datatype generic_data
min_warning 5
target SELECT COUNT(*) AS NumeroConexiones FROM v$session;
target_databases pandora
check_end

check_begin
name NumeroRegistros.files_table
description Invalid objects (detail)
operation full
datatype generic_data
target SELECT COUNT(*) AS NumeroRegistros FROM files;
target_databases pandora
check_end

Manual execution

The plugin execution format is as follows:

./pandora_oracle \
--conf <path to the configuration file> \
--target_databases <path to the configuration file containing target databases> \
[ --target_agents <path to the agent configuration file> ] \
[ --custom_queries <path to the custom queries configuration file> ]

For example :

./pandora_oracle \
--conf /usr/share/pandora_server/util/plugin/oracle.conf \
--target_databases /usr/share/pandora_server/util/plugin/targets.conf \
--target_agents /usr/share/pandora_server/util/plugin/target_agents.conf \
--custom_queries /usr/share/pandora_server/util/plugin/custom_queries.conf

 

Discovery

This plugin can be integrated with Pandora FMS Discovery.

To do this, you must load the ".disco" package that you can download from the Pandora FMS library:

https://pandorafms.com/library/ 

image-1687944670019.png

Once loaded, Oracle environments can be monitored by creating Discovery tasks from the Management > Discovery > Applications section.

image-1687944766442.png

For each task, the following minimum data will be requested:

image.png

Task settings can also be adjusted to customize the desired monitoring:

image-1684831045318.png

Tasks that are successfully completed will have an execution summary with the following information:

image-1684831085871.png


The tasks that are not completed successfully will have an execution summary recording the errors produced.

Agents y modules generated by the plugin

The plugin will create an agent for each target database. That agent will contain the following modules

If engine_uptime is activated:

restart_detection It will be 0 if an unexpected restart has been detected, and 1 if this is not the case. When a server restarts unexpectedly, there may be an interruption in access to the database and potentially lost transactions or data not saved correctly.

Si esta activado query_stats:

queries: select Number of SELECT queries. SELECT queries are used to retrieve data from the database. Monitoring SELECT queries allows you to assess query efficiency and index optimization. By identifying slow or inefficient SELECT queries, you can take steps to improve their performance, such as adding appropriate indexes, optimizing the queries, or adjusting server configuration.
queriers: update Number of UPDATE queries. UPDATE queries are used to modify existing data in the database. Monitoring UPDATE queries is important to assess the frequency and efficiency of data updates. You can identify UPDATE queries that affect a large number of rows or have a significant impact on server performance. This allows you to optimize queries, review table structure, or take steps to reduce the load generated by updates.

queries: delete

 Number of DELETE queries. DELETE queries are used to delete data from the database. Monitoring DELETE queries is useful for evaluating the frequency and efficiency of data deletions. You can identify DELETE queries that affect a large number of rows or that have a significant impact on server performance. This allows you to optimize queries, review table structure, or take steps to reduce the load generated by deletes.
queries: insert Number of INSERT queries. INSERT queries are used to insert new data into the database. Monitoring INSERT queries allows you to assess the frequency and efficiency of data inserts. You can identify INSERT queries that are placing a high load on the server or could be causing performance issues. This allows you to optimize queries, review table structure, or consider lazy insert strategies to improve performance in high-concurrency environments.

If analyze_connections is activated:

session usage Number of current connections with respect to the total of maximum connections. Monitoring session usage in SQL Server is important to optimize performance, identify blocking issues, improve security and auditing, and efficiently plan server resources.

If cache_stats is enabled:

cache hit ratio (dictionary) The cache ratio of the dictionary. "Cache Hit Ratio (Dictionary)" refers to the ratio of Oracle dictionary data access requests that are resolved using cached data compared to requests that require disk access. The Oracle dictionary contains information about the database structure, objects, metadata, and other important details. A high hit ratio indicates that most dictionary access requests are resolved using data in the cache, which improves system performance by avoiding disk access.
cache hit ratio (library) The cache ratio of the library. "Cache Hit Ratio (Library)" refers to the ratio of access requests to Oracle procedures, functions, and packages stored in the shared library cache. The shared library cache stores the compiled code of frequently executed programs and SQL queries. A high hit ratio indicates that most requests are resolved using cached code, which avoids the need for recompilation and improves performance by reducing execution time.
cache hit ratio (buffer) The buffer cache ratio. "Cache Hit Ratio (Buffer)" refers to the proportion of requests to access blocks of data stored in Oracle's buffer cache that are resolved using data in the cache compared to requests that require access to disk. The buffer cache stores blocks of data that are frequently read or modified. A high hit ratio indicates that most data access requests are resolved using blocks stored in the buffer cache, which reduces the need for disk access and improves overall system performance.

If check_tablespaces is enabled:

tablespace <table name> free Percentage of usage in GB. It is important to monitor "tablespace free" in Oracle to keep track of the amount of space available in the tablespaces. This allows you to proactively manage storage space growth and avoid insufficient space issues that could impact database functionality. By monitoring tablespace free, you can take actions such as adding more storage space or making adjustments to configurations to ensure there is enough space available.
tablespace <table name> status Status of the table, 1 if it is online and 0 if not. Monitoring tablespace status in Oracle is essential to evaluate the health and status of tablespaces. Provides information about the structural integrity of tablespaces, including the existence of errors or corruption issues. By monitoring tablespace status, you can detect problems early and take action to resolve them, such as restoring from backups or performing recovery tasks. This ensures database continuity and stability, preventing data loss or performance degradation due to tablespace issues.

If fragmentation_ratio is activated:

fragmentation ratio The fragmentation ratio. The Fragmentation Ratio in Oracle Database is a value that indicates the amount of space wasted due to fragmentation in a data structure. A high Fragmentation Ratio indicates greater fragmentation and wasted space, which can impact system performance.

The plugin will also create a module for each custom query defined in the configuration file.