Monitoring Oracle databases with Checkmk

In order for Checkmk to be able to retrieve data from Oracle, you must create a user account with read rights for each database to be monitored. If you use a Real Application Cluster (RAC), it is sufficient to create the user account only once. However, you will later need to install and configure the Checkmk agent including the agent plug-in on each node of the Oracle cluster.

The procedure differs depending on which Oracle environment you are using. However, you can read more details about this in the Oracle documentation. For my Oracle site on my local server, for example, I log in via the command line with SQL Plus, of course you can also use another tool.

In my example, I switch to the Oracle user for this and log in as sysdba. Such a high-level authorization is not a must, but you do need an account with sufficient rights for the creation of a new user account.​​​​

su - oracle
sqlplus / as sysdba

Here you now create the user account for Checkmk. In my case, I have called it ‘monitoring’.​​​​​​​

create user monitoring identified by myPassword​​​​​​​

After creating the user account, assign access rights and the rights to start a session to this user account.​​​​​​​

grant select_catalog_role to monitoring
grant create session to monitoring

You can now test the connection with connect:​​​​​​​

connect monitoring/myPassword

In my case, access has been successful. Here ‘myPassword’ is only a placeholder. You should always use a strong password with sufficient length and a mixture of upper and lower case letters, numbers and special characters.

Now switch to your Checkmk site to prepare the Oracle agent plug-in, which you will later install on your Oracle server. To do this, use the ‘Oracle databases’ agent rule.

• Search for ‘oracle database’ under Setup. Click on ‘ORACLE databases (Linux, Solaris, AIX, Windows)’.
• Click on Add rule.

You will now see many setting options. Since Checkmk already has good default settings, you will only need to activate a few of the options.

• You can skip Rule Properties, as you do not need to make any changes here. These options are mainly for adding additional information for this specific rule.
• You do the configuration under ORACLE databases (Linux, Solaris, AIX, Windows):
  • Select the checkbox for Activation and leave the default setting as ‘Deploy ORACLE database plugin’.
  • On Linux systems, Checkmk assumes by default that your Oracle server uses xinetd as a super server daemon. You have to edit the Host uses xinetd or systemd (Linux/AIX/Solaris only) option if your database host server is set to systemd, as in my case. You can leave Interval at one minute.
  • Activate Login Defaults. You will then see a number of login options.
    • Activate Authentication method to log in via a normal user account. Retain the default setting ‘Login with the following credentials’.
    • Add the user account, in my case ‘monitoring’, to User, followed by the corresponding password to Password.

In most cases, no further configurations are necessary for access. In my example, the rule is as follows.

Under Conditions you do not need to alter any other settings, as agent rules apply to the configuration of the agent package and not to the monitoring hosts.

• Save the rule with Save.

With this, the agent rule has now been configured. Checkmk comes supplied with practical default settings. In the next step, you will bake and install the agent. Nevertheless, in the next part, I would like to explain the other functions of the Oracle monitoring rule. With these you can customize your Oracle monitoring if needed, but in this tutorial you do not need these options. If you do not require any customization, you can proceed directly to the packaging of the agents.

The first seven configuration points under ORACLE databases (Linux, Solaris, AIX, Windows) deal with Checkmk's access to your database.

The most important item is Login Defaults, as here you decide how Checkmk logs on to the Oracle database to be monitored. Under Authentication method, you can use an Oracle Wallet to log in as an alternative to a user account. For more details on working with an Oracle Wallet, see the Oracle documentation or the Checkmk documentation.

By default, Checkmk uses a normal user account to retrieve data from the Oracle database. With login as role you can customize this and instead define Checkmk as sysdba, sysdg or sysoper as role.

The Hostname or IPv4 address for listener option defines the address of the Oracle instance. By default, the agent listens on localhost, but you can change this here if necessary. If you have changed the default Oracle port, you must also change this under TCP port for listener.

If you want to use a Transparent Network Substrate (TNS) alias for access to Oracle, you must store it at TNS Alias (Linux/AIX/Solaris only) in Checkmk.

If you want to customize the login data for a specific SID, you can use the Login for selected databases option. Then you can add the individual SIDs here.

If you want to monitor Oracle's Automatic Storage Management (ASM), you must create a separate user account for Checkmk. You should then store this account under Login for ASM Instances to monitor, otherwise Checkmk cannot collect any monitoring data from ASM.

If you want to exclude certain SIDs from the monitoring, you will find a number of options under Instances to monitor. This can be useful for developers' test systems, for example.

Once you have configured the agent rule, creating the agent package is easy. Switch to the Agent Bakery and bake the agent.

• Go to Setup -> Windows, Linux, Solaris, AIX.
• The button with the flame symbol and the 'Bake agents' caption should be colored orange and thus ready to bake. Click on it.

Wait briefly until the baked agent appears. Next, click on the agent that matches your database server's operating system. For me, this would be the RPM package.

If you use Oracle databases on multiple servers with differing access data, you will need to configure the Oracle database rule multiple times and bake multiple agents. If you change your Oracle server from a server operating system with xinetd to one with systemd, you will usually have to modify the corresponding option and also bake a new agent.

Otherwise, it is sufficient to create one agent for a number of databases that use different operating systems if you use the same access data and the same monitoring parameters for all Oracle databases.

If the agent runs together with the Oracle plug-in on the Oracle server, you can include it in the monitoring. I recommend using a folder structure in Checkmk, as this simplifies the administration of the hosts in the monitoring. For this reason, you should first create a folder for your Oracle databases.

• Go to Setup -> All hosts, click on Add folder and enter a name of your choice under Title. I have named the folder ‘oracle’.
• Click on Save.
• Then open this folder by clicking on it.
• Click Add Host to create a host in this folder.
• Under Hostname, enter the name of your Oracle server. In this tutorial, my Oracle server is called ‘oracle-sr1-01.lan.domain.net’.
• If the host name is not resolvable by DNS, you must activate the IPv4 address option and enter the server's IP address. This is not required for me.
• Click on Save & go to service configuration.

I recommend the use of fully qualified DNS names (FQDN) as the standard for host names in your entire infrastructure. This instruction for a consistent naming concept will help you with the implementation.

• Wait for the automatic service detection and accept all detected monitoring services with Accept all.
• To see your database server as the host, activate the changes in Checkmk. Click on the yellow icon with the exclamation mark in the top right corner. You should see the number of ‘changes’ there.
• Click Activate on selected sites.
• The first Oracle database is now in the monitoring. You can find the host under Monitor -> All Hosts.

In my particular example, Checkmk has detected numerous services. Thanks to the predefined thresholds, Checkmk also warns me that my main memory could be overused (136% committed) and that this database uses a high proportion of the shared main memory (44.76%). Here this is acceptable, however, as I only use a fairly small amount of working memory for my test system.

You now have the Oracle Application Performance and the operating system in the monitoring. If you use a hardware server with a management board, you should also include this as a separate host in the monitoring. You could theoretically also add the hardware services to the already existing host, but this has a negative effect on performance in the long term, so I would advise against it.

Adding the services is easy since Checkmk again recognizes them automatically. Here I use IPMI to transfer the monitoring data.

• Go to Setup -> All hosts and reopen your Oracle folder.
• Click on Add host.
• Under Hostname, enter a name for your management board. I have named it ‘oracle-iLO-sr1-01.lan.domain.net’.
• Click on Save & go to folder.

If your server management board's host name is not resolvable by DNS, you will need to enter an IP address manually under IPv4 address.

• Under Conditions, activate the Explicit hosts option and apply this rule to your management board host. Here I have used ‘oracle-iLO-sr1-01.lan.domain.net’.

• Go to Setup -> Hosts. You should now see your databases' hosts and the hardware server.
• At your management board host, click on the yellow icon for automatic server detection, the third icon on the right.

• If you once again accept the ‘changes’ with Activate on selected sites, you will have added the Management Board to the monitoring.

For example, here is the host view of my host for the server hardware:

Especially for Oracle databases in the cloud, the installation of monitoring agents is out of the question. Checkmk is however able to log into such databases remotely and thus retrieve the monitoring data. Checkmk then transfers this information to the monitoring via a piggyback host.

The actual physical location of the remote Oracle site is irrelevant, but it must be accessible via IP for the piggyback host. On the piggyback host you must be able to install the Checkmk agent including the Oracle configuration files, and the Oracle client must also be installed. The piggyback host must, of course, be accessible from your Checkmk site.

Here, I use my local Oracle server ‘oracle-sr1-01.lan.domain.net’ from before as a piggyback host to monitor my remote Oracle database ‘DemoDB’. This is a typical scenario in hybrid database environments, moreover, an Oracle server usually already includes all of the requirements for a piggyback host.

You can however store the Oracle configuration file with the access data for your remote Oracle database on any local server as long as it meets the requirements for the monitoring of remote Oracle databases.

First you will need to create the host for your remote database in the monitoring. I do that manually in this tutorial, as I only have one host. If you have a larger number of remote databases, however, I recommend using the Dynamic Host Configuration to have the hosts created automatically based on the piggyback data.

• Go again to Setup -> All hosts and open your Oracle folder.
• Click on Add host.
• ou must use the SID of the remote database as the host name, which in my case is ‘DemoDB’. Checkmk cannot assign the data if this name is incorrect. Under Network address, activate the IP address family option and select ‘No IP’.
• Under Monitoring agents click on ‘show more’ and edit two options:
  • Activate the Checkmk agent / API integrations option and select ‘No API integration, no agent’. Under Piggyback, select the ‘Always use and expect piggyback data’ option.
• Click on Save & go to folder.

Create a new agent package and once again use the Oracle agent rule for this:

• Under Setup, search again for ‘oracle database’ and open ‘ORACLE databases (Linux, Solaris, AIX, Windows)’.
• Click Add rule to create a new rule. Do not edit the existing rule for your local Oracle server.
• The configuration differs from before:
  • Activate the Activation and Login Defaults.
  • For Login Defaults, enter the information for your remote database:
    • In my case, the Authentication method is again a normal user account. Keep the default setting ‘Login with the following credentials’.
    • Add the user account for your remote database at User, here I have also named the access account ‘monitoring’, followed by its corresponding password at Password.

• Scroll down and activate the Remote instances (Linux/AIX/Solaris only) option.
  • Click on ‘Add remote instance’.
  • With the Unique ID you ensure that the configuration of the connection to this external database is unique and that there are no duplicates. In my case, I select ‘Use the remote SID’.
  • Under Monitoring host this database should be mapped to, select the host to which Checkmk should assign the piggyback data. For me, this host is called ‘DemoDB’.
  • For ORACLE SID of the remote database, enter the SID of the database to be monitored. In my case, this is also called ‘DemoDB’.
  • At DNS Hostname or IP-address of the database server, you must enter the IP address or DNS name for the database server through which your remote Oracle instance can be accessed, here ‘10.200.6.6’.
  • Under ORACLE release of the remote database, select your Oracle version. For version 12.1 and newer, select ‘12.1’. In my case, I do exactly that, as I use version 19.0.
• Store the rule with Save.

• Bake the agent as before with the Agent Bakery. To do this, click again on ‘Bake agents’ under Setup -> Agents -> Windows, Linux, Solaris, AIX.
• Download the agent package and install it on the piggyback host that you previously defined in the Oracle rule. For me, this is the Oracle server ‘oracle-sr1-01.lan.domain.net’.