Monitoring Oracle databases

The functionality presented here is a preview of a new feature that will be subject to development and expansion until further notice and is therefore referred to as experimental. In this state, it is possible that functionality will not only be added, but also modified in such a way that an existing configuration will become obsolete and you will have to recreate it. We ask for your understanding in this matter.

1. Introduction

In this article, you’ll learn how to monitor Oracle databases using the new agent plug-in mk-oracle.

If you’re looking for information about the old agent plug-in mk_oracle, please see the article of the same name in the 2.4.0 branch of the manual.

With the agent plug-in, you can not only retrieve tablespaces or the active sessions of a database, but also many other metrics. You can find a complete list of monitoring options available with our check plug-ins in the Catalog of check plug-ins.

The output of mk-oracle is identical to that of the old plug-in, so the exact same check plug-ins continue to be used.

The new agent plug-in for monitoring Oracle is an executable binary file. It is available for IBM AIX (64-bit POWER), Linux (x86_64), Oracle Solaris (x86_64), and Windows (x86_64). We will also offer the new agent plug-in for Linux on aarch64 (64-bit ARM) in one of the upcoming versions of Checkmk. Until then, you will need to continue using the old shell script on Linux architecture.

1.1. Overview of This Article

If you don’t want to read (almost) the entire long article, here are a few links with shortcuts. However, if you’re setting up Oracle monitoring with Checkmk for the first time, you should work through the entire article to familiarize yourself with all the options.

To learn how to install the Oracle Instant Client (OIC), see the chapter 'Deploying the Oracle instant client (OIC)'.
Users of the Agent Bakery in the commercial editions of Checkmk will find an explanation of the most important options in the chapter 'Creating a configuration for the agent plug-in'. In the reference section of this article, we’ll go over all configuration options again and also show which entries in the configuration file mk-oracle.yml correspond to the options from the rule Unified Oracle plug-in (experimental).
If the data points provided 'out-of-the-box' by the agent plug-in are not sufficient for you, you can also monitor your databases using custom SQL queries.
Users who wish to switch from mk_oracle to mk-oracle can use our rule and configuration migration scripts to simplify the transition.

1.2. How does Oracle monitoring work with Checkmk?

To monitor Oracle instances and their databases with Checkmk, you must install the Checkmk agent and the mk-oracle agent plug-in on the respective host. The agent plug-in requires a configuration file, which you must save as mk-oracle.yml in the configuration directory. In addition, the agent plug-in relies on libraries from the Oracle Instant Client (OIC). Therefore, the Oracle Instant Client must also be available on the host.

For instructions on how to install the OIC, see the chapter Setting up prerequisites on the oracle host.

2. Setting up the prerequisites on the Oracle host

On each host where you want to monitor Oracle databases or sites, you must satisfy two prerequisites.

The Oracle Instant Client (OIC) client library must be made available to the agent plug-in. For instructions on how to do this, see the relevant Deploying Oracle Instant Client (OIC) chapter.
The mk-oracle agent plug-in must be granted access to your Oracle instances or databases. You have two options for doing this:
- You can create a user and password on the host that is used exclusively for monitoring by Checkmk. We explain this method in the chapter Create a database user.
- Alternatively, you can use the Oracle Wallet.

2.1. Deploying Oracle Instant Client (OIC)

The mk-oracle agent plug-in requires the Oracle Instant Client (OIC) libraries in order to function. You have three options for deploying OIC.

You can install OIC exclusively for the agent plug-in. This is the method we recommend, which we describe here.
Alternatively, you can install OIC globally on the host.
If OIC is already installed on the host and you want to use this installation for monitoring via Checkmk, you can tell the agent plug-in where to find OIC by adding an entry to the configuration. Link to the option in the rule.

First, download the package appropriate for your host from the page Oracle Instant Client downloads in ZIP format.

Linux

Windows

Linux

For a Linux x86-64 system, for example, use this Basic Light Package (ZIP).

Verify the name of the Checkmk agent’s plug-in directory on your host:

root@linux# plugindir=$(cmk-agent-ctl dump | grep "^PluginsDirectory" | head -1 | awk '{print $2}')
root@linux# echo "$plugindir"
/usr/lib/check_mk_agent/plugins

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

Unzip the archive file into that very same plug-in directory:

root@linux# mkdir -p "${plugindir}/mk-oracle"
root@linux# unzip -j instantclient-basiclite-linux.x64-23.26.2.0.0.zip -d "${plugindir}/mk-oracle"

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

Since some ZIP files contain Oracle Linux user IDs, and an existing user with that ID could therefore become the owner of the client library, as a safety precaution you should change the owner and access permissions:

root@linux# chown -R root:root "${plugindir}/mk-oracle"
root@linux# chmod -R go-w "${plugindir}/mk-oracle"

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

Windows

For Windows, for example, use the Basic light package (ZIP).

Extract the archive into the packages\mk-oracle subdirectory of the plug-in directory.

By default, on a Windows host, this is %PROGRAMDATA%\checkmk\agent\plugins\packages\mk-oracle.

Install OIC globally

On a Linux host, you can also install the Oracle Instant Client in /opt/, for example.

OMD[central]:~$ sudo unzip -j instantclient-basiclite-linux.x64-23.26.2.0.0.zip -d /opt/checkmk/oracle-instant-client

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

On a Windows host, you can also install the Oracle Instant Client in any directory. You must then specify this directory in the Windows PATH environment variable.

2.2. Grant access rights for the agent plug-in

The agent plug-in mk-oracle must be granted access to your Oracle instances or databases. You can do this either through a database user or the Oracle Wallet.

Simple database user

Database user (multi-tenant)

Wallet

Simple database user

If you want to set up a regular user for monitoring, we recommend using that user exclusively for Checkmk.

First, connect to your Oracle host and switch to the user under whose credentials the Oracle database is running. This is usually oracle:

root@linux# su - oracle

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

Store the instance that sqlplus should connect to in an environment variable:

oracle@linux$ export ORACLE_SID=MYINST1

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

Start sqlplus. Specify the role with which sqlplus should start.

oracle@linux$ sqlplus / as sysdba

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

Now create a user that will be used exclusively for monitoring Oracle. The permissions will apply to all existing and future databases.

sqlplus> create user checkmk identified by myPassword;
sqlplus> alter user checkmk set container_data=all container=current;
sqlplus> grant select_catalog_role to checkmk container=all;
sqlplus> grant create session to checkmk container=all;
sqlplus> exit

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

Database user (multi-tenant)

If you want to set up a user for monitoring, we recommend using that user exclusively for Checkmk.

First, connect to your Oracle host and switch to the user under whose account the Oracle database is running. This is usually oracle:

root@linux# su - oracle

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

Store the instance that sqlplus should connect to in an environment variable:

oracle@linux$ export ORACLE_SID=MYINST1

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

Start sqlplus. Specify the role with which sqlplus should start.

oracle@linux$ sqlplus / as sysdba

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

Now create a user that will be used exclusively for monitoring Oracle. The prefix C## ensures that the user can view all databases in the instance. The permissions will also apply to all existing and future databases.

sqlplus> create user C##checkmk identified by myPassword;
sqlplus> alter user C##checkmk set container_data=all container=current;
sqlplus> grant select_catalog_role to C##checkmk container=all;
sqlplus> grant create session to C##checkmk container=all;
sqlplus> exit

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

Wallet

As an alternative to specifying the user directly with a password in a configuration file, you can also use the Oracle Wallet. This has the advantage that you no longer need to store the login credentials in plain text on both the Checkmk server and the Oracle host. Even if you have adapted the permissions for the configuration file on the Oracle host accordingly, the login credentials will still have left the server and will also be located on the Checkmk server, provided you are using the Agent Bakery.

The Oracle Wallet, in turn, stores the credentials in an encrypted form on the host being monitored, so that they can be used without having to explicitly disclose any login credentials. Checkmk can use this wallet, meaning that, in principle, only the database administrator (DBA) needs to know the credentials. To do this, you—or the DBA—create a wallet on the relevant server and store it in the configuration directory in a file named oracle_wallet.

root@linux# mkstore -wrl /etc/check_mk/oracle_wallet -create

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

The agent plug-in will later access this file whenever it needs to establish a connection to an instance. To ensure that the necessary user data can be found, you must enter it into the wallet once. In the following example, you add the user checkmk for the site MYINST1:

root@linux# mkstore -wrl /etc/check_mk/oracle_wallet -createCredential MYINST1 checkmk myPassword

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

In order for the agent plug-in to know where to look for the wallet, it must locate two files. The first file is sqlnet.ora, which specifies the location of the wallet. The second file—tnsnames.ora—defines the instance’s address so that it can also be accessed via its alias. To ensure the agent plug-in finds these files, you can set the path on Linux, Solaris, and AIX using the TNS_ADMIN environment variable. This is particularly useful if the files already exist. Alternatively, you can create them explicitly. On Windows, you must specify these paths manually.

First, create the sqlnet.ora file. The agent plug-in searches this file for the connection credentials as a fallback, so you must specify the correct path to the wallet file you just created here. Be sure to set the SQLNET.WALLET_OVERRIDE parameter to TRUE:

/etc/check_mk/sqlnet.ora

LOG_DIRECTORY_CLIENT = /var/log/check_mk/oracle_client
DIAG_ADR_ENABLED = OFF

SQLNET.WALLET_OVERRIDE = TRUE
WALLET_LOCATION =
 (SOURCE=
   (METHOD = FILE)
   (METHOD_DATA = (DIRECTORY=/etc/check_mk/oracle_wallet))
 )

The plug-in will now know which credentials to use. To ensure it connects to the correct address, create a file named tnsnames.ora. You can find the exact syntax in the Oracle documentation, but here is an example of what the file might look like:

/etc/check_mk/tnsnames.ora

MYINST1
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MYINST1_ALIAS)
    )
  )

You can, of course, add additional credentials to the wallet at a later time. In that case, you may need to update the tnsnames.ora file.

Finally, change the permissions of the files and directories created manually in this section so that the access rights are set correctly when they are executed. The agent plug-in, which runs as root, switches to the owner of the Oracle binaries (e.g., $ORACLE_HOME/bin/sqlplus) before executing them. Therefore, at a minimum, the group of the Oracle binary owner requires read access to the manually created files in /etc/check_mk/. In the following example, we assume that the group is oinstall.

The following commands change the group to oinstall:

root@linux# chgrp oinstall /etc/check_mk/sqlnet.ora /etc/check_mk/tnsnames.ora
root@linux# chgrp -R oinstall /etc/check_mk/oracle_wallet

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

These commands ensure that the group can read the oracle_wallet directory and its contents:

root@linux# chmod g+x /etc/check_mk/oracle_wallet
root@linux# chmod -R g+r /etc/check_mk/oracle_wallet

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

Afterward, the permissions should look something like this:

root@linux# tree -ugpR /etc/check_mk
[drwxr-xr-x root     root    ]  /etc/check_mk
├── [drwxr-x--- root   oinstall ]  oracle_wallet
│   └── [-rw-r----- root   oinstall ]  cwallet.sso
│   └── [-rw-r----- root   oinstall ]  cwallet.sso.lck
│   └── [-rw-r----- root   oinstall ]  ewallet.p12
│   └── [-rw-r----- root   oinstall ]  ewallet.p12.lck
├── [-rw-r--r-- root   oinstall ]  sqlnet.ora
├── [-rw-r--r-- root   oinstall ]  tnsnames.ora

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

The command output shows only those files and directories that are relevant to the configuration of the Oracle Wallet.

3. Configuring the agent plug-in

Once you have set up the prerequisites for monitoring with Checkmk on the Oracle host, you must now configure the agent plug-in.

Users of our commercial editions and the Agent Bakery can find instructions in the chapter Creating a configuration for the agent plug-in using a rule.
If you want to monitor your Oracle instances using Checkmk Community or prefer to configure them manually for other reasons, please read the chapter Setting up Oracle monitoring manually.

3.1. Using agent rules and the Agent Bakery

Users of a commercial edition of Checkmk can conveniently configure the agent plug-in using the Unified Oracle plug-in (experimental) agent rule. In this chapter, we will focus on the sections authentication, connection options, and database-specific credentials. All other options in the Unified Oracle plug-in (Experimental) rule set are either already sufficiently explained in the inline help or described in the reference section of this article.

Now open the Setup menu of your Checkmk site, search for Unified Oracle plug-in (Experimental), and open the rule set. Click Add rule and begin configuring the authentication method.

Information about the additional options is part of the reference section.

Defining the authentication type

Depending on which method you chose in the chapter Setting up prerequisites on the Oracle host, select the appropriate option under Authentication type and enter the corresponding data.

You can optionally use Role to specify which role the agent plug-in should assume during authentication. And if you want to use a different username for ASM Authentication, you can enter that here as well.

Configuring connection options

In the next block, you can configure the details for the connection options. In addition to your Oracle instance’s host name and port, you can also specify the TNS_ADMIN directory path. If you do not enter anything here, mk-oracle assumes that this is the agent’s configuration directory. With the Oracle Local Registry path option, you can tell the agent plug-in where the local registry files for your Oracle cluster are located. The agent plug-in’s instance detection will then use this information to monitor the instances described therein.

Database-specific credentials

Especially in larger environments, it may sometimes be necessary for different instances in your Oracle environment to have different credentials and connection options. The Databases to monitor section is provided for this purpose. Here, you can specify instance-specific login credentials and connection options. Your settings under Default settings will then apply only to the instances that you have not explicitly specified under Databases to monitor.

Additional configuration options

All other options for the Unified Oracle Plugin (Experimental) rule are described in the chapter References.

Setting conditions

Finally, remember to restrict your new rule Unified Oracle plug-in to your Oracle hosts using the Conditions.

Now save the rule, bake the new agent package, and install it on the Oracle host. Transfer the new agent package to your Oracle host and install it. Afterward, you can perform a service discovery on your Oracle host in Checkmk.

3.2. Manual Configuration

This chapter is intended for anyone who uses CRE Checkmk Community or wishes to avoid using the Agent Bakery.

Once you have met the prerequisites, you must install the agent plug-in on the Oracle host. At this point, it’s worth noting that this works a little differently than you might be used to with other agent plug-ins.

Last but not least, you’ll need to provide the agent plug-in with a configuration.

First, let’s go over the prerequisites again.

Prerequisites for a manual setup

Before you can begin the manual setup, verify that the following two prerequisites have been met:

The Checkmk agent must already be installed and registered on the Oracle host. We explain how to do this in the articles Monitoring Linux and Monitoring Windows.
You must also have already ensured that the Oracle Instant Client is available on the host.

Once all prerequisites have been met, you can proceed to install the agent plug-in.

Installing the agent plug-in

The mk-oracle agent plug-in comes with two helper programs, which are ultimately only there to launch mk-oracle itself in either synchronous or asynchronous mode. You must also install these two helper programs on the host, which we’ll demonstrate first.

Linux

Windows

Linux

In addition to the mk-oracle agent plug-in, you will also need the oracle_unified_sync and oracle_unified_async files. You can find all three files in CRE Checkmk Community under Setup > Agents > Other operating systems > Plug-ins. In the commercial editions, go to the Setup menu and select Agents > Windows, Linux, Solaris, AIX to first access the Agent Bakery, where you’ll find the ready-made packages. From there, you can access the list of agent files via the menu item Related > Other operating systems.

If you have file system access, you can find all the necessary files in the ~/version/lib/python3/cmk/plugins/oracle/agents/ directory of your Checkmk site.

Transfer the three files to the Oracle host using any method of your choice. If wget is available on the host, you could proceed as follows:

root@linux# wget -P /tmp http://mycmkserver/mysite/check_mk/agents/mk-oracle
root@linux# wget -P /tmp http://mycmkserver/mysite/check_mk/agents/oracle_unified_sync
root@linux# wget -P /tmp http://mycmkserver/mysite/check_mk/agents/oracle_unified_async

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

Here, the three files get downloaded to the /tmp directory on the Oracle host. The following steps will assume this storage location.

Install the plug-in on the Oracle host in the agent’s plug-in directory (by default, /usr/lib/check_mk_agent/plugins). You can find the name of the plug-in directory on your host in the output from the Checkmk agent. The following command sets the plug-in directory in the MK_LIBDIR variable. The subsequent commands will then also use $MK_LIBDIR. If the next command does not produce meaningful output for you—for example, because awk is not available—simply enter the actual plug-in directory into MK_LIBDIR yourself so that you can continue working with the specified commands.

# Determine the plugin path and store it to a variable
root@linux# export MK_LIBDIR=$(cmk-agent-ctl dump | awk '/^PluginsDirectory:/ {print $2; exit}')
# Test the result
root@linux# echo $MK_LIBDIR
/usr/lib/check_mk_agent/plugins

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

Now the necessary files are on the Oracle host, and the variable $MK_LIBDIR specifies the location of the plug-in directory. Finally install mk-oracle on the host:

root@linux# install -D -m 755 /tmp/mk-oracle "$MK_LIBDIR/packages/mk-oracle/mk-oracle"
root@linux# install -m 755 /tmp/oracle_unified_sync "$MK_LIBDIR/oracle_unified_sync"
root@linux# install -D -m 755 /tmp/oracle_unified_sync "$MK_LIBDIR/600/oracle_unified_async"

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

In the above example, the oracle_unified_async file was installed in the 600 subdirectory. This means that all sections you set up later for asynchronous retrieval will have a maximum cache age of 600 seconds. The number 600 can be customized to your needs if required.

Windows

In addition to the mk-oracle agent plug-in, you will also need the files oracle_unified_sync.ps1 and oracle_unified_async.ps1. In the commercial editions, go to the Setup menu and select Agents > Windows, Linux, Solaris, AIX to first access the Agent Bakery, where you will find the baked packages. From there, use the menu item Related > Other operating systems to access the list of agent files.

The actual installation of the agent plug-in works a little differently than you might be used to with other plug-ins.

All of the included plug-ins for Windows can be found on the monitored host in the agent’s installation directory at C:\Program Files (x86)\checkmk\service\plugins, where they are stored so that they are immediately available. Alternatively, the plug-ins are also located on the Checkmk server at ~/share/check_mk/agents/windows/plugins, and the two utility programs are located at ~/version/lib/python3/cmk/plugins/oracle/agents/.

Copy the three files from there to the following directories:

File name Directory

File name	Directory
`mk-oracle`	`%ProgramData%\checkmk\agent\plugins\packages\mk-oracle\`
`oracle_unified_sync.ps1`	`%ProgramData%\checkmk\agent\plugins\`
`oracle_unified_async.ps1`	`%ProgramData%\checkmk\agent\plugins\`

mk-oracle

%ProgramData%\checkmk\agent\plugins\packages\mk-oracle\

oracle_unified_sync.ps1

%ProgramData%\checkmk\agent\plugins\

oracle_unified_async.ps1

%ProgramData%\checkmk\agent\plugins\

Once the files are in place, you must provide the appropriate configuration for the file responsible for executing the asynchronous sections—that is, oracle_unified_async.ps1.

We explain how to do this in the section Customizing the execution of a specific plug-in in the manual article on Windows monitoring.

The installation of the entire agent plug-in is now complete. To ensure that the agent plug-in now understands what it is supposed to do, you must still run Create the configuration for the plug-in.

Creating the configuration for the agent plug-in

In the configuration directory in the Checkmk agent, you must now create a file named mk-oracle.yml.

Linux

Windows

Linux

You can easily find out the name of the configuration directory on your host using the following command:

OMD[central]:~$ sudo sudo cmk-agent-ctl dump | grep "^AgentDirectory" | head -1
AgentDirectory: /etc/check_mk

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

Using your preferred editor, create the file mk-oracle.yml in this directory.

Windows

In the %PROGRAMDATA%\checkmk\agent\config directory, create the file mk-oracle.yml using your preferred editor.

To avoid having to start from scratch, the chapter Sample Configurations provides a starting point.

A very simple configuration for a freshly installed Oracle AI database free—which you may have set up for testing purposes—could look something like this:

mk-oracle.yml

---
oracle:
  main:
    authentication:
      password: mypassword
      type: standard
      username: mymonitoringuser
    cache_age: 600
    connection:
      hostname: localhost
    custom_metrics_cache_age: 600
    instances: []

Copy file content to clipboard

Successfully copied file content to clipboard!

Write access to clipboard has been denied!

So you could start with this example for now. In this article’s reference section, we explain all the other configuration options that you can use.

Once the agent plug-in has been configured, you can already perform service discovery on the respective host from the Checkmk server. This completes the basic setup of the monitoring. If the services provided by the agent plug-in are not sufficient for your needs, you can proceed directly to the Custom SQL Queries.

4. Setting Up Custom SQL Queries

Custom SQL queries cannot be defined using the Unified Oracle Plugin (Beta) rule. You must perform the necessary configuration directly on the host from which you monitor your Oracle instances and databases.

4.1. Why Custom SQL Queries?

Checkmk already provides a large number of SQL queries through the agent plug-in, which you can use to monitor your database instances. To ensure that these queries are suitable for as wide a range of technical and content-related requirements as possible, they are kept general.

To meet the individual requirements of each organization for monitoring a specific database, Checkmk offers the option to create your own custom SQL queries and have them executed by the agent plug-in. These are then automatically recognized and monitored as services in Checkmk.

You have two options for providing your queries to the agent plug-in:

Either write the queries directly into the configuration file mk-oracle.yml.
Or, instead, specify in the configuration file where the agent plug-in can find the files containing your custom SQL queries.

The latter approach simplifies maintenance and also makes version control easier, since content is not mixed into the plug-in’s configuration file. However, if you’re only dealing with a small number of queries anyway, you may want to skip the extra effort.

We will start by showing a simple example that is written directly into the plug-in’s configuration file.

4.2. Setting up simple user-defined SQL queries

We’ll start by showing a query that already returns real data from an Oracle instance. This example is intended only to illustrate how easy it can be to incorporate user-defined SQL queries. Exactly how useful this query is in day-to-day work remains to be seen.

Get a feel for a query that can later be used in production by first executing your ideas for SQL queries as sysdba. To prevent potentially long-running queries from slowing down a production system, we recommend using a test instance.

oracle@linux$ sqlplus / as sysdba

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

sqlplus> SELECT 'details:Free memory: ' || ROUND(SUM(bytes)/1024/1024, 2) || ' MB' FROM v$sgastat WHERE name = 'free memory';
'DETAILS:FREEMEMORY:'||ROUND(SUM(BYTES)/1024/1024,2)||'MB'
----------------------------------------------------------------
details:Free memory: 230,86 MB

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

You can ignore this header’s output—just as the agent plug-in does. The important part is the keyword details used in the query. The check plug-in on the Checkmk server will extract the line that begins this way and use it to generate the Summary for the service. But how does Checkmk know what the service is actually supposed to be called? You specify the name of the service when you transfer the SQL query to the configuration for the agent plug-in:

mk-oracle.yml

---
oracle:
  main:
    connection:
      hostname: localhost
    authentication:
      username: mymonitoringuser
      password: mypassword
      type: standard
    custom_metrics:
      - My service:
          sql: "SELECT 'details:Free memory: ' || ROUND(SUM(bytes)/1024/1024, 2) || ' MB' FROM v$sgastat WHERE name = 'free memory'"

Copy file content to clipboard

Successfully copied file content to clipboard!

Write access to clipboard has been denied!

Our example illustrates three things:

To include custom SQL queries, you always need a key named custom_metrics.
This is followed by a list (here: My service). In YAML, such a list begins with a -. The name of this list becomes part of the service name in Checkmk and can be chosen freely.
Next comes the line containing the query. It must begin with sql:, and the query itself must be enclosed in double quotes. Also make sure that the query does not end with a semicolon.

After you have inserted this line into the agent plug-in’s configuration file on your Oracle host, you can return to your Checkmk server. There, perform a service discovery for the Oracle host. You will then see this new service:

The service name consists of several components:

The name always begins with ORA.
This is followed by the SID (here: FREE).
The subsequent SQL indicates that this service was generated by a user-defined SQL query.
This is followed by the string you were able to choose freely in the configuration file, in uppercase letters.

In the Summary, you’ll already see data derived from the results of the SQL query.

There will certainly be cases where such a simple query is exactly what you need. In that case, however, the service is probably still of little use. That’s why, in the chapter Advanced Custom SQL Queries, we’ll show you how to create a more comprehensive service in just a few simple steps.

4.3. Setting up advanced custom SQL queries

Building on the example shown in the chapter Simple custom SQL queries, we’ll now show you how to retrieve a service with a metric in just a few steps.

To do this, it’s a good idea to move the SQL query out of the agent plug-in’s configuration file. Instead, after the custom_metrics key, specify a file where the agent plug-in can find your SQL queries.

mk-oracle.yml

---
oracle:
  main:
    connection:
      hostname: localhost
    authentication:
      username: mymonitoringuser
      password: mypassword
      type: standard
    custom_metrics:
      - My advanced service:
          path: 'myadvancedservice.sql'

Copy file content to clipboard

Successfully copied file content to clipboard!

Write access to clipboard has been denied!

If you enter the file without specifying an absolute path, the agent plug-in will search through several directories in a specific order. We explain which directories these are and exactly what happens during this process in the Reference Section of this article.

Now, in the Checkmk agent’s Configuration Directory, create a subdirectory named orasql and create a file within it with the .sql extension, as specified after path:.

In this example, we assume that the configuration directory on a Linux host is named /etc/check_mk/. First, create the absolutely necessary orasql subdirectory.

root@linux# mkdir /etc/check_mk/orasql

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

Next, create the file myadvancedservice.sql in that directory. You can now much more easily include multi-line SQL queries in that file. It is important to ensure that the keywords for the check plug-in are included.

/etc/check_mk/orasql/myadvancedservice.sql

SELECT 'details:Free memory: ' || REPLACE(ROUND(SUM(bytes) / 1024 / 1024, 2), ',', '.') || ' MB' FROM v$sgastat WHERE name = 'free memory'
UNION ALL
SELECT 'perfdata:free_ram_mb=' || REPLACE(ROUND(SUM(bytes) / 1024 / 1024, 2), ',', '.') FROM v$sgastat WHERE name = 'free memory'
UNION ALL
SELECT 'exit:0' FROM dual

Copy file content to clipboard

Successfully copied file content to clipboard!

Write access to clipboard has been denied!

In addition to the first line, which you already know from the simple example, the SQL script now outputs a line that begins with perfdata. Checkmk will use this to generate a metric and automatically create a graph. You can also use the exit keyword to control the service’s status. In this example, it is set to 0, which stands for the OK status. The check plug-in on the Checkmk server requires these keywords to generate services and metrics from the provided data. We explain the four keywords details, perfdata, long, and exit in detail in the chapter Output Formatting and Keywords.

Once you have set all of this up and run a service discovery again, the old service will disappear and a new one—whose name ends with MY ADVANCED SERVICE—will be found instead.

5. Migration from `mk_oracle` to `mk-oracle`

5.1. Migrating the agent plug-in’s configuration

In Checkmk 2.5.0, the new agent plug-in mk-oracle includes a feature that helps users of the existing mk_oracle migrate to the new agent plug-in. To do this, deploy mk-oracle to your Oracle host and run the agent plug-in with the -M option, followed by the name of a configuration file in the old format. The agent plug-in will then read the configuration for the old mk_oracle and output it in the format expected by mk-oracle.

This might look something like this, for example:

root@linux# /usr/lib/check_mk_agent/plugins/packages/mk-oracle/mk-oracle -M /etc/check_mk/mk_oracle.cfg
# --- Converted from /etc/check_mk/mk_oracle.cfg at 2026-06-29 19:00:00 UTC ---
# # Syntax
# # DBUSER='USERNAME:PASSWORD:ROLE:HOST:PORT:TNSALIAS'
# DBUSER='checkmk:myPassword'

 DBUSER_MYINST1='cmk_specific1:myPassword1:SYSDBA:localhost:1521'
# DBUSER_MYINST2='cmk_specific2:myPassword2::localhost::INST2'

 ASMUSER='cmk_asm:myASMPassword:SYSASM'
# --- Known environment variables defined in legacy config ---
# DBUSER_MYINST2 cmk_specific2:myPassword2::localhost::INST2
# ASMUSER cmk_asm:myASMPassword:SYSASM
# DBUSER checkmk:myPassword
# DBUSER_MYINST1 cmk_specific1:myPassword1:SYSDBA:localhost:1521
# --- Unified Config ---
---
oracle:
  main:
    connection:
      hostname: localhost
    authentication:
      username: "checkmk"
      password: "myPassword"
      type: standard
      asm_username: "cmk_asm"
      asm_password: "myASMPassword"
      asm_role: sysasm
    instances:
      - sid: $ORACLE_SID
        alias: $ORACLE_SID
      - sid: MYINST2
        alias: INST2
        connection:
          hostname: localhost
        authentication:
          username: "cmk_specific2"
          password: "myPassword2"
          type: standard
      - sid: MYINST1
        connection:
          hostname: localhost
          port: 1521
        authentication:
          username: "cmk_specific1"
          password: "myPassword1"
          type: standard
          role: sysdba

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

By default, mk-oracle writes the migrated configuration to standard output. We strongly recommend doing this as your first step so you can review the output first. The output begins with your old configuration as a comment. For reference purposes, it’s a good idea to keep this comment for a while.

If everything looks good, you can use the --migrate-output option with mk-oracle to have it write the output directly to a file.

root@linux# /usr/lib/check_mk_agent/plugins/packages/mk-oracle/mk-oracle \
    -M /etc/check_mk/mk_oracle.cfg \
    --migrate-output /etc/check_mk/mk-oracle.yml

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

If the migration command is unable to migrate parts of your old configuration, this will be clearly indicated in the output as #WARNING.

The following table shows which configuration variables are translated by mk_oracle and how:

Old variable… …is migrated to:

Old variable…	…is migrated to:
`DBUSER` (required)	Top-level `connection:` (host name, port) and `authentication:`, plus the first entry under `instances:`
`DBUSER_<SID>`	One `instances:` entry for each `SID`, with `connection:` and `authentication:` entries per instance
`ASMUSER`	`asm_username`, `asm_password`, `asm_role` under `authentication:`
`REMOTE_INSTANCE_<ID>`	One `instances:` entry per ID, including `piggyback_host:` (Linux/AIX only)
`SYNC_SECTIONS` / `ASYNC_SECTIONS`	`sections:` with the attribute `is_async: false` / `true`
`SYNC_ASM_SECTIONS` / `ASYNC_ASM_SECTIONS`	`sections:` with the `affinity: "asm"` (`"all"`) attribute
`CACHE_MAXAGE`	`cache_age:`
`SQLS_MAX_CACHE_AGE`	`custom_metrics_cache_age:`
`MAX_TASKS`	`options.threads:` (Only for values ≥ 2, up to a maximum of 8)
`ONLY_SIDS`	`discovery.include:` (with `detect: true`)
`SKIP_SIDS`, `EXCLUDE_<SID>="ALL"`	`discovery.exclude:` (with `detect: true`)
`TNS_ADMIN`	`connection.tns_admin:`
`OLRLOC`	`connection.oracle_local_registry:`
`SQLS_SECTIONS` + `SQLS_*` per section	`custom_metrics:`

DBUSER (required)

Top-level connection: (host name, port) and authentication:, plus the first entry under instances:

DBUSER_<SID>

One instances: entry for each SID, with connection: and authentication: entries per instance

ASMUSER

asm_username, asm_password, asm_role under authentication:

REMOTE_INSTANCE_<ID>

One instances: entry per ID, including piggyback_host: (Linux/AIX only)

SYNC_SECTIONS / ASYNC_SECTIONS

sections: with the attribute is_async: false / true

SYNC_ASM_SECTIONS / ASYNC_ASM_SECTIONS

sections: with the affinity: "asm" ("all") attribute

CACHE_MAXAGE

cache_age:

SQLS_MAX_CACHE_AGE

custom_metrics_cache_age:

MAX_TASKS

options.threads: (Only for values ≥ 2, up to a maximum of 8)

ONLY_SIDS

discovery.include: (with detect: true)

SKIP_SIDS, EXCLUDE_<SID>="ALL"

discovery.exclude: (with detect: true)

TNS_ADMIN

connection.tns_admin:

OLRLOC

connection.oracle_local_registry:

SQLS_SECTIONS + SQLS_* per section

custom_metrics:

The entries in the following table are not migrated automatically and must be manually updated to work with the new plug-in:

Old variable Note on manual migration

Old variable	Note on manual migration
`SQLS_DBUSER`, `SQLS_DBPASSWORD`, `SQLS_DBSYSCONNECT`	Login credentials per user-defined SQL; use instance-specific overrides under the `authentication:` key instead
`SQLS_PARAMETERS`	Passing SQL*Plus parameters is not supported.
`EXCLUDE_<SID>="<section> …"`	Exclusion of individual sections per SID; only `EXCLUDE_<SID>="ALL"` is converted
`ORACLE_HOME`, `REMOTE_ORACLE_HOME`	The OIC runtime environment—specify `use_host_client` if necessary.
`ID_BY`	Selects `SID=` over `SERVICE_NAME=` in the old connection configuration; use the instance fields `sid:` / `service_name:` instead

SQLS_DBUSER, SQLS_DBPASSWORD, SQLS_DBSYSCONNECT

SQLS_PARAMETERS

Passing SQL*Plus parameters is not supported.

EXCLUDE_<SID>="<section> …"

Exclusion of individual sections per SID; only EXCLUDE_<SID>="ALL" is converted

ORACLE_HOME, REMOTE_ORACLE_HOME

The OIC runtime environment—specify use_host_client if necessary.

ID_BY

Selects SID= over SERVICE_NAME= in the old connection configuration; use the instance fields sid: / service_name: instead

5.2. Migration of user-defined SQL queries

The old agent plug-in mk_oracle forwards user-defined SQL queries via the command-line tool sqlplus. The new mk-oracle plug-in, on the other hand, executes SQL queries directly using the Oracle Instant Client driver. This change in the execution model has two major implications for existing SQL queries.

Basic restrictions and execution model

Non-SQL*Plus commands

Commands such as PROMPT, SET, COLUMN, SPOOL, EXEC/EXECUTE, or VAR/VARIABLE are functions of the sqlplus client tool and are not part of the SQL database language. They do not work under the OIC driver.
No anonymous PL/SQL blocks: Anonymous blocks in the format DECLARE/BEGIN {…} END; cannot be executed directly as a script. Only pure SQL statements are supported.

The agent plug-in in migration mode checks every referenced SQL file and, if discrepancies are found, issues a warning in the terminal as well as in the generated configuration file. Although the affected sections are migrated, you must manually edit them; otherwise, the queries will fail at runtime.

Features of the new execution model

Multiple statements: An SQL file can contain multiple top-level SQL statements separated by semicolons. These are executed in sequence, and their results are concatenated.
Each returned result must comply with the specifications for Output Formatting and Keywords.

Removing SQL*Plus Commands

Formatting commands and interactive commands from sqlplus have no equivalent in the Oracle Instant Client driver. You must therefore delete them without replacement. The agent plug-in outputs each returned data row directly.

Next, we’ll first show an SQL file that still worked for mk_oracle:

SET PAGESIZE 0
SET FEEDBACK OFF
COLUMN details FORMAT A80
PROMPT collecting session count ...
SELECT 'details:' || COUNT(*) || ' sessions' FROM v$session;

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

You must modify such a file for mk-oracle as follows so that it works with OIC:

SELECT 'details:' || COUNT(*) || ' sessions' FROM v$session

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

Converting PL/SQL blocks into pure SELECT queries

In most cases, anonymous PL/SQL blocks can be replaced with standard SQL constructions: * PL/SQL variables become WITH clauses (Common Table Expressions). * IF/ELSIF conditions are replaced by CASE statements. * DBMS_OUTPUT.PUT_LINE outputs are represented using UNION ALL, or separate statements separated by semicolons.

The following example shows an SQL file that contains a PL/SQL block:

SET SERVEROUTPUT ON
DECLARE
    invalid_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO invalid_count
      FROM dba_objects
     WHERE status = 'INVALID';
    IF invalid_count > 10 THEN
        DBMS_OUTPUT.PUT_LINE('exit:2');
    ELSE
        DBMS_OUTPUT.PUT_LINE('exit:0');
    END IF;
    DBMS_OUTPUT.PUT_LINE('details:' || invalid_count || ' invalid objects');
END;

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

You must rewrite such a file into pure SQL for the new mk-oracle. For example, it might look like this:

WITH invalid AS (
    SELECT COUNT(*) AS cnt
      FROM dba_objects
     WHERE status = 'INVALID'
)
SELECT 'exit:' || CASE WHEN cnt > 10 THEN '2' ELSE '0' END FROM invalid
UNION ALL
SELECT 'details:' || cnt || ' invalid objects' FROM invalid

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

Moving complex PL/SQL logic to stored functions

If the logic absolutely requires PL/SQL (e.g., for loops, complex error handling, or temporary states), it must itself be moved into the database as a pipelined function.

To do this, you must set up the pipelined function once in your database:

CREATE OR REPLACE FUNCTION checkmk_invalid_objects
    RETURN sys.odcivarchar2list PIPELINED
AS
    invalid_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO invalid_count
      FROM dba_objects
     WHERE status = 'INVALID';

    -- Beliebige PL/SQL-Logik ist hier erlaubt
    PIPE ROW ('details:' || invalid_count || ' invalid objects');
    PIPE ROW ('perfdata:invalid_objects=' || invalid_count || ';10;100');
    PIPE ROW (CASE WHEN invalid_count > 10 THEN 'exit:2' ELSE 'exit:0' END);
    RETURN;
END;
/

GRANT EXECUTE ON checkmk_invalid_objects TO checkmk;

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

Once you’ve set up this pipelined function, you can have the agent plug-in call it:

SELECT column_value FROM TABLE(checkmk_invalid_objects)

Copy command(s) to clipboard

Successfully copied command(s) to clipboard!

Write access to clipboard has been denied!

If the function is in a different schema, the schema name must be prefixed: TABLE(owner.checkmk_invalid_objects).

6. Dashboards and Views

6.1. Oracle Dashboards

The commercial editions of Checkmk are shipped with a built-in dashboard for Oracle. The number of dashboards for Oracle will continue to grow in the coming weeks. Feel free to keep an eye on our Werks resource for any references to Oracle.

You can always access this via the Oracle databases dashboard, which you can reach via Monitor > Applications > Oracle databases.

7. References

7.1. Custom SQL Queries

Using the custom_metrics configuration key, you can define ad hoc SQL queries whose output is evaluated on the Checkmk server by the Check plug-in link: oracle_sql. Each entry is identified by an item name, which is displayed in Checkmk as part of the service name.

Basics and Configuration

You can either pass the SQL queries directly as a string or load them from a file. In the following example configuration, you will find an SQL query in the global section that is identified by the key oracle.main. Under the key oracle.main.instances, you can specify a key custom_metrics, which is then, of course, executed only for those instances.

mk-oracle.yml

---
oracle:
  main:
    connection:
      hostname: localhost
    authentication:
      username: mymonitoringuser
      password: mypassword
      type: standard
    custom_metrics:
      - product_price: # item name -> will generate service "<SID> SQL PRODUCT_PRICE"
          sql: "SELECT 'details:Price OK' FROM dual"
    instances:
      - service_name: FREE
        custom_metrics:
          - last_sessions: # will only be run for this specific instance
              sql: "SELECT 'details:per-instance' FROM dual"

Copy file content to clipboard

Successfully copied file content to clipboard!

Write access to clipboard has been denied!

An instance always executes both globally defined SQL queries and its own instance-specific SQL queries.
If a global entry and an instance-specific entry share the same item name, the instance-specific entry always takes precedence.

External SQL Files (path:)

Instead of writing SQL queries directly into the agent plugin’s configuration file using sql:, you can use path: to reference external .sql files. This works for both custom_metrics and predefined sections.

Variations in Path Specification

mk-oracle.yml

custom_metrics:
  # 1. Absolute path for a file
  - heavy_query:
      path: '/opt/checkmk/sql/heavy_query.sql'

  # 2. Relative path
  # First searched in MK_LIBDIR/... and then in MK_CONFDIR/...
  - product_price:
      path: 'queries/product_price.sql'

  # 3. Path to a directory
  # The file name is automatically derived from the item name (here: "sessions_stats.sql")
  - sessions_stats:
      path: 'queries/'

  # 4. File with inline fallback
  # If the file cannot be read, the inline fallback is used
  - last_resort:
      path: 'queries/last_resort.sql'
      sql: "SELECT 'details:fallback' FROM dual"

Copy file content to clipboard

Successfully copied file content to clipboard!

Write access to clipboard has been denied!

Resolution Rules

Absolute vs. relative: Absolute paths are used exactly as they are specified. Relative paths are first searched for under MK_LIBDIR/plugins/packages/mk-oracle/orasql/ and then under MK_CONFDIR/orasql/. If the file exists in both directories, the file in MK_LIBDIR takes precedence.
File vs. directory: A path can refer to a specific file (with or without a .sql extension) or to a directory. If it refers to a directory, the filename is derived from the item name for custom_metrics and from the section name for predefined sections.
Version-dependent variants: In addition to the base file (<name>.sql), you can provide Oracle-specific variants in the format <name>@<min_version>.sql (e.g., sessions@12010000.sql). The plug-in automatically selects the file with the highest min_version that is less than or equal to the version of the connected Oracle instance. An 8-digit numeric format is used for versioning (MMmmRRSSSS for Major / Minor / Release / Patch). Example: 12.1.0.2 → 12010002.
Fallback chain: The order for determining the SQL query is: path: > (inline) sql: > included default code (only for predefined sections). If path: fails and no sql: is defined, the section will generate no output.

SQL parameters

You can define named parameters that are inserted into the SQL code before execution. Each placeholder in the format ${<name>} is replaced, on a text-by-text basis, with the configured value. This works for both inline SQL and .sql files.

mk-oracle.yml

---
custom_metrics:
  - test:
      sql: 'SELECT ${parameter_1} FROM dual; SELECT ${parameter_2} FROM dual'
      sql_params:
        parameter_1: 'value_1'
        parameter_2: '${ENV_VAR_1}'

Copy file content to clipboard

Successfully copied file content to clipboard!

Write access to clipboard has been denied!

${ENV_VAR_1} is resolved from the environment variables.
Textual substitution (no bind variables): All values are inserted into the statement unchanged. This means you can also pass column or table names.
Environment variables: A value can reference environment variables (format $VAR or ${VAR}). These are resolved when the configuration is read. If a referenced variable is not specified, the parameter is skipped with a warning. The placeholder remains in the SQL code (which usually results in an SQL error rather than executing a query with empty values).
Unused parameters: Placeholders without a matching parameter value are ignored and remain unchanged in the code.

Output formatting and keywords

The mk-oracle agent plug-in automatically inserts the section header <<<<<oracle_sql:sep(58)>>> , as well as the subsection header [[[<SID>|<item>]]]. The SQL to be executed is responsible solely for the body of the output. Each SQL query must return rows with a single string column. The value must begin with one of the following prefixes so that the Checkmk plug-in can interpret it correctly:

details: Here you can specify what should be displayed in the Summary of the generated service. The line begins with the keyword followed by a colon. The rest of the line constitutes the output.
perfdata: Metrics are passed using this keyword. Within a single line, you can specify any number of metrics—separated by a space. You can also spread the metric output across multiple lines. Simply start each line with the keyword perfdata:.
long: If you want the service to have a long output for the Details field, you can specify it here. You can also use this keyword multiple times to generate several lines in the Details.
exit: If the output should result in a specific status, you can specify it here. You can use the standard mappings 0, 1, 2, 3 for the statuses OK, WARN, CRIT, UNKNOWN.

7.2. Configuration options

In this chapter, we explain all the options available for configuring the agent plug-in. These options apply whether you configure the agent plug-in using the Unified Oracle Plug-in (Beta) rule set or configure it manually.

The explanations in this manual sometimes go beyond the content of the inline help—for example, when there is interesting background information about an option or when a more detailed explanation is appropriate for specific use cases. We also specify which entry is created in the mk-oracle.yml configuration file as a result of a selected option.

In addition, anyone interested can find a description of all options in mk-oracle.yml in our description on GitHub.

Additional options > Maximum connections

The maximum number of database connections to open.

Configuration file (example)

mk-oracle.yml

---
oracle:
  main:
    options:
      max_connections: <int>

Copy file content to clipboard

Successfully copied file content to clipboard!

Write access to clipboard has been denied!

Additional options > Maximum queries

The maximum number of queries that may be executed per connection.

Configuration file (example)

mk-oracle.yml

---
oracle:
  main:
    options:
      max_queries: <int>

Copy file content to clipboard

Successfully copied file content to clipboard!

Write access to clipboard has been denied!

Additional options > Ignore database name

Configuration file (example)

mk-oracle.yml

---
oracle:
  main:
    options:
      IGNORE_DB_NAME: "0|1"

Copy file content to clipboard

Successfully copied file content to clipboard!

Write access to clipboard has been denied!

Additional Options > Oracle Instant Client options

Configuration file (example)

mk-oracle.yml

---
oracle:
  main:
    options:
      use_host_client: "auto|never"

Copy file content to clipboard

Successfully copied file content to clipboard!

Write access to clipboard has been denied!

Authentication > Authentication type > Oracle Wallet

The Oracle Wallet provides a secure way to authenticate with Oracle databases without storing plaintext passwords in configuration files.

Configuration file (example)

mk-oracle.yml

---
oracle:
  main:
    authentication:
      type: wallet

Copy file content to clipboard

Successfully copied file content to clipboard!

Write access to clipboard has been denied!

Authentication > Type

Here you can choose between standard and wallet. If you select wallet, no further settings are required at this point. standard refers to authentication via username and password. In this case, you must specify username and password.

Configuration file (example)

mk-oracle.yml

---
oracle:
  main:
    authentication:
      type: "standard|wallet"

Copy file content to clipboard

Successfully copied file content to clipboard!

Write access to clipboard has been denied!

Authentication > Authentication type > Username and password

Configuration file (example)

mk-oracle.yml

---
oracle:
  main:
    authentication:
      username: 'myuser'
      password: 'mypassword'
      type: 'standard'

Copy file content to clipboard

Successfully copied file content to clipboard!

Write access to clipboard has been denied!

Authentication > Role

If you have chosen to authenticate using a username and password, you can optionally specify the user’s role.

Configuration file (example)

mk-oracle.yml

---
oracle:
  main:
    authentication:
      username: 'myuser'
      password: 'mypassword'
      type: 'standard'
      role: "sysdba|sysoper|sysasm|szsbackup|sysdg|syskm"

Copy file content to clipboard

Successfully copied file content to clipboard!

Write access to clipboard has been denied!

Connection options

In this option, you define the network-level connection parameters that apply to all instances, unless they are overridden in the database-specific settings.

Configuration file (example)

mk-oracle.yml

---
oracle:
  main:
    connection:
       hostname: 'myhostname' # optional, default: 'localhost'
       port: 1521 # optional, default: 1521
       timeout: 5 # optional, default: 5 (seconds)
       tns_admin: '/path/to/oracle/config/files/' # optional, default: MK_CONFDIR
       oracle_local_registry: '/etc/oracle/olr.loc' # optional, path to the Oracle Local Registry

Copy file content to clipboard

Successfully copied file content to clipboard!

Write access to clipboard has been denied!

Cache age

The Cache age option allows you to specify the cache validity period for the section you are monitoring asynchronously.

Configuration file (example)

mk-oracle.yml

---
oracle:
  main:
    cache_age: <int> # Default: 600 (seconds)

Copy file content to clipboard

Successfully copied file content to clipboard!

Write access to clipboard has been denied!

Custom Metrics cache age

Configuration file (example)

mk-oracle.yml

---
oracle:
  main:
    custom_metrics_cache_age: <int> # Default: 600 (seconds)

Copy file content to clipboard

Successfully copied file content to clipboard!

Write access to clipboard has been denied!

Instance discovery

Using the options under Instance discovery, you can precisely control which instances you want to monitor with Checkmk and which ones you may want to exclude from monitoring. This option only works if the agent plug-in is located on the same host as the instances.

Configuration file (example)

mk-oracle.yml

---
oracle:
  main:
    discovery:
      detect: yes # Enable automatic instance detection
      include: ['PROD', 'DEV'] # optional; only the instances listed here are monitored
      exclude: ['TEST'] # optional; the instances listed here are ignored

Copy file content to clipboard

Successfully copied file content to clipboard!

Write access to clipboard has been denied!

Sections—data to collect

This option allows you to specify precisely which data the agent plug-in should retrieve from your instances and databases. In addition, you can specify for each section whether the data should be retrieved synchronously or asynchronously. The is_async settings in the following example show our default configurations.

Configuration file (example)

mk-oracle.yml

---
oracle:
  main:
    sections:
    - instance:
        is_async: false
    - asm_instance:
        is_async: false
    - asm_diskgroup:
        is_async: true
    - dataguard_stats:
        is_async: false
    - locks:
        is_async: false
    - logswitches:
        is_async: false
    - longactivesessions:
        is_async: false
    - performance:
        is_async: false
    - processes:
        is_async: false
    - recovery_area:
        is_async: false
    - recovery_status:
        is_async: false
    - sessions:
        is_async: false
    - systemparameter:
        is_async: false
    - undostat:
        is_async: false
    - iostats:
        is_async: true
    - jobs:
        is_async: true
    - resumable:
        is_async: true
    - rman:
        is_async: true
    - tablespaces:
        is_async: true

Copy file content to clipboard

Successfully copied file content to clipboard!

Write access to clipboard has been denied!

Databases to monitor

If individual or even all Oracle instances on a host have different login credentials or connection settings, you must configure them individually in the Databases to monitor section. To identify the individual databases, you can choose from SID, Alias, or Service Name. At this point, you should generally consult the inline help to learn more about the three options. You can also find some additional configuration examples in our description on GitHub.

Configuration file (example)

mk-oracle.yml

---
oracle:
  main:
    instances:
      - service_name: MYSERVICE
      - sid: MYSID
      - alias: MYALIAS

Copy file content to clipboard

Successfully copied file content to clipboard!

Write access to clipboard has been denied!

7.3. Sample configurations

You can use the following sample configurations as starting points for the manual setup of your Oracle monitoring.

A minimal configuration

mk-oracle.yml

---
oracle:
  main:
    authentication:
      password: mypassword
      type: standard
      username: mymonitoringuser
    cache_age: 600
    connection:
      hostname: localhost
    custom_metrics_cache_age: 600
    instances: []

Copy file content to clipboard

Successfully copied file content to clipboard!

Write access to clipboard has been denied!

An (almost) complete example

Additional options have been added since this example was created. The example will be updated shortly.

mk-oracle.yml

system:
  logging:
    level: 'warn'
    max_size: 1000000
    max_count: 5

oracle:
  main:
    options:
      max_connections: 6
      use_host_client: never
      IGNORE_DB_NAME: 0
    connection:
      hostname: 'localhost'
      port: 1521
      timeout: 5
      tns_admin: '/etc/check_mk'
    authentication:
      username: 'mymonitoringuser'
      password: 'mypassword'
      role: 'sysdba'
      type: 'standard'
    discovery:
      detect: yes
      include: ['PROD', 'DEV']
      exclude: ['TEST']
    instances:
      - service_name: 'ORCL'
        sid: 'ORCL'
      - alias: 'REMOTE_DB'
    sections:
      - instance:
          affinity: 'db'
      - tablespaces:
          is_async: yes
      - performance:
      - sessions:
    cache_age: 600
    custom_metrics_cache_age: 600
    piggyback_host: 'mypiggybackhost'

Copy file content to clipboard

Successfully copied file content to clipboard!

Write access to clipboard has been denied!

Last modified: Fri, 14 Aug 2026 08:56:08 GMT via commit d6dd0579f

On this page

1. Introduction
- 1.1. Overview of This Article
- 1.2. How does Oracle monitoring work with Checkmk?
2. Setting up the prerequisites on the Oracle host
- 2.1. Deploying Oracle Instant Client (OIC)
- 2.2. Grant access rights for the agent plug-in
3. Configuring the agent plug-in
- 3.1. Using agent rules and the Agent Bakery
- 3.2. Manual Configuration
4. Setting Up Custom SQL Queries
5. Migration from mk_oracle to mk-oracle
- 5.1. Migrating the agent plug-in’s configuration
- 5.2. Migration of user-defined SQL queries
6. Dashboards and Views
- 6.1. Oracle Dashboards
7. References

Monitoring Oracle databases

1. Introduction

1.1. Overview of This Article

1.2. How does Oracle monitoring work with Checkmk?

2. Setting up the prerequisites on the Oracle host

2.1. Deploying Oracle Instant Client (OIC)

2.2. Grant access rights for the agent plug-in

3. Configuring the agent plug-in

3.1. Using agent rules and the Agent Bakery

Defining the authentication type

Configuring connection options

Database-specific credentials

Additional configuration options

Setting conditions

3.2. Manual Configuration

Prerequisites for a manual setup

Installing the agent plug-in

Creating the configuration for the agent plug-in

4. Setting Up Custom SQL Queries

4.1. Why Custom SQL Queries?

4.2. Setting up simple user-defined SQL queries

4.3. Setting up advanced custom SQL queries

5. Migration from mk_oracle to mk-oracle

5.1. Migrating the agent plug-in’s configuration

5.2. Migration of user-defined SQL queries

Basic restrictions and execution model

Non-SQL*Plus commands

Features of the new execution model

Removing SQL*Plus Commands

Converting PL/SQL blocks into pure SELECT queries

Moving complex PL/SQL logic to stored functions

6. Dashboards and Views

6.1. Oracle Dashboards

7. References

7.1. Custom SQL Queries

Basics and Configuration

External SQL Files (path:)

Variations in Path Specification

Resolution Rules

SQL parameters

Output formatting and keywords

7.2. Configuration options

Additional options > Maximum connections

Additional options > Maximum queries

Additional options > Ignore database name

Additional Options > Oracle Instant Client options

Authentication > Authentication type > Oracle Wallet

Authentication > Type

Authentication > Authentication type > Username and password

Authentication > Role

Connection options

Cache age

Custom Metrics cache age

Instance discovery

Sections—​data to collect

Databases to monitor

7.3. Sample configurations

A minimal configuration

An (almost) complete example

5. Migration from `mk_oracle` to `mk-oracle`

Sections—data to collect