Checkmk
to checkmk.com

1. Introduction

Checkmk provides comprehensive options for the monitoring of Oracle databases. With the agent plug-in you can not only retrieve a database’s tablespaces or its active sessions, but also many other types of metrics. A complete list of monitoring options with our check plug-ins can be found in the Catalog of Check Plug-ins. We extend these plug-ins regularly, so it is always worth looking in the catalog. Among others, Checkmk can monitor the following values:

To be able to monitor the databases only the agent plug-in is required in addition to the agent on the database server. The Linux, Solaris, AIX and Windows operating systems are currently supported. The agent plug-in for Linux, Solaris, AIX is called mk_oracle and for Windows mk_oracle.ps1. No further additional software will be required for a monitoring, either on the Checkmk server or on the database server.

Many of the steps to set up monitoring are the same for both Linux and Windows. For this reason, we will first describe the general steps, then the specific steps for the respective operating system group and finally the Agent Bakery of the commercial editions.

2. First steps for initial setup

The configuration files with sample content presented in this and the following chapters can be found on the Checkmk server — either via command line or via the Checkmk web interface. In the Raw Edition select Setup > Agents and in the commercial editions Setup > Agents > Windows, Linux, Solaris, AIX > Related. In all editions you will find menu entries for the different operating systems. The configuration files can be found in the Example Configurations box.

2.1. Creating a user

In principle, the first setup is quick and requires only three steps. The first step, of course, is to have a user who is also allowed to query the databases. Provided that you are not using Real Application Cluster (RAC), create a user for each database to be monitored. How to access an instance differs depending on the installed operating system. For Linux, you can do this for example, by always first setting the relevant instance as an environment variable for which a user is to be created. Normally you first switch to the oracle user, but this can differ depending on the setup:

root@linux# su - oracle
oracle@linux$ export ORACLE_SID=MYINST1

Then log on to the instance and create a user for the monitoring. To get all of the data, the following permissions are required. In the following example, the newly-created user is named checkmk. You can also specify any other desired name:

sqlplus> create user checkmk identified by myPassword;
sqlplus> grant select_catalog_role to checkmk;
sqlplus> grant create session to checkmk;
sqlplus> connect checkmk/myPassword
sqlplus> exit

You can find out exactly how the login to a specific instance works in the Oracle documentation.

Multi-tenant databases

You can also configure the login for multi-tenant databases. This is usually performed in the configuration using a special user and with the prefix C##. The assignment of permissions is a bit different as for regular users as you need to specify them for all current containers and for all in the future:

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

2.2. Creating the configuration

After you have created a user, the next step is to enable the agent plug-in to later receive this information. The simplest way is for you to define the same login data for all instances, and set a default in the configuration. Now create on the Oracle host a new configuration file mk_oracle.cfg for Linux, AIX, Solaris or mk_oracle_cfg.ps1 for Windows. In the following example the file for unix-type systems can be seen:

/etc/check_mk/mk_oracle.cfg
# Syntax:
# DBUSER='USERNAME:PASSWORD'
DBUSER='checkmk:myPassword'

For Windows this procedure looks very similar. There you set the variable in a PowerShell script:

C:\ProgramData\checkmk\agent\config\mk_oracle_cfg.ps1
# Syntax:
# $DBUSER = @("USERNAME","PASSWORD")
$DBUSER = @("checkmk","myPassword")

The standard user is all that the agent plug-in really requires. All other options that you can set in unix-type systems or under Windows are optional.

2.3. Using the Oracle Wallet

As an alternative to specify the user directly and 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 access data in an unencrypted form on the Checkmk server and on the Oracle host. So even if you have defined the configuration file’s access rights on the Oracle host to suit, the access data has nevertheless left the server and is located on the Checkmk server as long as you use the Agent Bakery.

The Oracle Wallet in turn stores the encrypted access data on the host to be monitored so that they can only be used, but no login data has to be made known explicitly. Checkmk can thus use this wallet so that the access data needs only be known to the database administrator (DBA). Create — or the DBA can create — a wallet on the appropriate server:

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

The agent plug-in will access this file later whenever a connection to an instance is to be established. So that the necessary user data can also be found, you must enter this once in the wallet. In the following example you thus add the user checkmk for the instance MYINST1:

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

For the agent plug-in to know where to look for the wallet, it must find two files. The first file is sqlnet.ora in which the info for the wallet’s location can be found. The second file — tnsnames.ora — defines the address of the instance so that it can also be addressed via its alias. So that the agent plug-in can access these files you can specify the path under Linux, Solaris and AIX using the TNSALIAS extended variable. This is especially useful if the files already exist. Alternatively, you can create them explicitly. Windows even requires that you specify them manually.

First create the sqlnet.ora file. The agent plug-in alternatively searches in this file for the login data, so the correct file path to the wallet file you just created must be entered here. Make sure that you 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))
 )

Now the plug-in knows which credentials should be used. So that it also accesses the correct address, create tnsnames.ora as the second file. The exact syntax can be found in the Oracle documentation, but as an example the file could look like this:

/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)
    )
  )

With this last step you have created the prerequisites for removing the access data from the agent plug-in’s configuration file. Instead of the access data, you simply enter a / (slash):

/etc/check_mk/mk_oracle.cfg
DBUSER='/:'

You can of course add further access data to the wallet at a later date. The tnsnames.ora file must then simply be amended as necessary.

3. Linux, Solaris, AIX

3.1. Plug-in and configuration paths

Under unix-type systems Checkmk uses a uniform agent plug-in. On the one hand, this reduces maintenance effort, since SQL queries are not duplicated, and on the other hand you only need to pay attention to a single agent plug-in. On all supported systems the file paths for the agents are the same or very similar. Specifically, you need the following directories:

Operating system Plug-in path Configuration path

Linux, Solaris, AIX

/usr/lib/check_mk_agent/plugins/

/etc/check_mk/

Linux with systemd

/usr/lib/check_mk_agent/plugins/<Number>

/etc/check_mk/

AIX

/usr/check_mk/lib/plugins/

/usr/check_mk/conf/

3.2. Installing the agent plug-in

After you have created a user in the initial setup and stored this in the configuration file, install the agent plug-in. Copy the mk_oracle file from the Checkmk server’s directory ~/share/check_mk/agents/plugins/ to the Oracle host’s plug-in directory described above.

Important

The agent plug-in for unix-type systems mk_oracle does not work well with systemd (see Werk #13732). On systems with systemd, you must therefore run the agent plug-in asynchronously. This means that you do not install the agent plug-in directly under /usr/lib/check_mk_agent/plugins/, but in a subfolder /usr/lib/check_mk_agent/plugins/<Number>/. <Number> means the execution interval in seconds. We recommend execution every minute, i.e. /usr/lib/check_mk_agent/plugins/60/. When setting up via the Agent Bakery, you can do this using the Oracle rule’s option Host uses xinetd or systemd, which is set to xinetd by default.

Be sure to make the agent plug-in executable, and correct it if necessary:

root@linux# cd /usr/lib/check_mk_agent/plugins/
root@linux# ls -lA
-rw-r--r-- 1 root root 120808 Jan 25 11:29 mk_oracle
root@linux# chmod +x mk_oracle
root@linux# ls -lA
-rwxr-xr-x 1 root root 120808 Jan 25 11:29 mk_oracle

3.3. Advanced options

In the initial setup you have already learned about the first variables for getting monitoring data from their Oracle instances. However, depending on the application scenario, you will quickly need further possibilities for better, individual control of the monitoring per instance. You will find these options in the following sections.

Advanced user configuration

With the standard login you can use regular or maybe even all instances of a database. However, there are special cases in which you require individual access data for specific instances. In the configuration file you therefore have the following three options for specifying users:

Parameter Description

DBUSER

The default if no individual access data has been defined for the database instance.

DBUSER_MYINST1

Access data for a specific database instance — in this case for the instance MYINST1. The access data is only used for this instance.

ASMUSER

Special access data for Automatic Storage Management (ASM).
Important: For an ASM only one login at a time can be specified.

These variables additionally allow even more options apart from user name and password. You can also determine whether the user is a SYSDBA or SYSASM, on which combination of address and port the instance listens to, and even which TNSALIAS should be used. However, these specifications are always — unlike user and password — optional. In addition to the above example, a configuration can look like this:

/etc/check_mk/mk_oracle.cfg
# 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'

A few explanations for the above example:

  • You can define any number of individual access data. These are always preferred to the standard.

  • Each option is separated from the others by a : (colon).

  • If an optional field is omitted in the middle of the string, a colon must be coded, as with the DBUSER_MYINST2 entry, where no special role is specified.

  • If after a certain point, no more optional fields are needed, you can omit the colons, as with the ASMUSER entry, where neither the host nor the port is specified.

Including or excluding instances

In some cases you may not want to include particular instances in the monitoring. This may be because it is only a playground for developers, or for other reasons. To make the configuration as simple as possible in individual situations, you have various options to entirely or partially exclude one or more instances:

Parameter Description

ONLY_SIDS

Here you can determine which instances are to be monitored. An instance is named by its system identifier (SID). This is a positive list, ignoring all instances that are not explicitly listed. This parameter is very useful if the number of instances to be monitored is smaller than the number of instances to be ignored.

SKIP_SIDS

Unlike ONLY_SIDS, this is a negative list where all instances are monitored except those explicitly listed here. This parameter is very suitable if the number of instances to be ignored is smaller than the number to be monitored.

EXCLUDE_<SID>

With this parameter you can partially exclude an instance by excluding certain sections of the instance from monitoring. In this way, you define a negative list of the sections of an instance. You can also exclude all sections with the value ALL and and thus do the same as if you were to add the instance to SKIP_SIDS.
Important: For ASM SIDs you cannot use this concept but you can use SKIP_SIDS="+ASM1 …​" instead.

You will have already guessed: The order in which these parameters are processed determines the result. The entries are in fact processed per instance exactly in sequence as shown in the table above. Therefore if the variable ONLY_SIDS is set, SKIP_SIDS is no longer evaluated nor is it checked whether the EXCLUDE_<SID> variable is set to ALL for this instance. If ONLY_SIDS is not set, the system proceeds according to the sequence. In case of doubt — that is, as the default behavior — the instance will be monitored accordingly.

Below is an example in which all variables are set and what the behavior is like:

/etc/check_mk/mk_oracle.cfg
ONLY_SIDS='INST1 INST2 INST5'
SKIP_SIDS='INST7 INST3 INST2'
EXCLUDE_INST1='ALL'
EXCLUDE_INST2='tablespaces rman'

Since the positive list from the first line always has priority, the second and third lines are no longer evaluated. Only the fourth (last) line will be considered at a later date, since the instance is only to be partially evaluated here.

In practice, it only makes sense to use one of the variables to determine the number of instances to be monitored.

Determining the data to be fetched

As you learned in the previous section, it is not only possible to disable instances completely, but also to only partially monitor them. The operational purposes are diverse, and it is especially practical when it is undesirable to have certain long-running sections to be included in everything, or only basic information from test instances is required. To restrict sections globally, set the corresponding variables directly — to restrict only certain instances you can slot in the EXCLUDE_<SID> variable which you have already learned about in the previous section. The global variables are:

Parameter Description

SYNC_SECTIONS

Sections that are to be queried synchronously, i.e. every time the agent runs. Since the query interval is 60 seconds by default, the SQL queries used must be run through correspondingly fast. If the variable is not specified, all sections are queried.

ASYNC_SECTIONS

Sections that are to be queried asynchronously, i.e. only every x minutes. How long the data remains valid is determined by the CACHE_MAXAGE variable, below in this table.

SYNC_ASM_SECTIONS

Here the same mechanism for ASM sections applies that is generally described for the SYNC_SECTIONS variable.

ASYNC_ASM_SECTIONS

Here the same mechanism for ASM sections applies that is generally described for the ASYNC_SECTIONS variable.

CACHE_MAXAGE

This variable is used to determine how long asynchronously retrieved data remains valid. If the variable’s value is not specified, a default of 600 seconds (10 minutes) is used. Make sure that the time range is not shorter than the interval at which the Checkmk agent delivers the data (60 seconds by default). Otherwise, the data may be considered outdated and not delivered by the agent.

The mechanism therefore allows you to set a default in the configuration file and overwrite it for individual instances as required. A configuration could then look like this, for example:

/etc/check_mk/mk_oracle.cfg
# DEFAULTS:
# SYNC_SECTIONS="instance sessions logswitches undostat recovery_area processes recovery_status longactivesessions dataguard_stats performance locks"
# ASYNC_SECTIONS="tablespaces rman jobs ts_quotas resumable"
# SYNC_ASM_SECTIONS="instance processes"
# ASYNC_ASM_SECTIONS="asm_diskgroup"
# CACHE_MAXAGE=600

SYNC_ASM_SECTIONS='instance'
ASYNC_SECTIONS='tablespaces jobs rman resumable'

CACHE_MAXAGE=300

EXCLUDE_INST1='undostat locks'
EXCLUDE_INST2='jobs'

As you can see in the example, only the instance section is queried for the ASM instances and a minimum set for the asynchronous sections is specified on all regular instances. Additionally, on the INST1 instance the synchronous sections undostat and locks will be omitted. Since the synchronous sections are not explicitly specified, all synchronous sections are retrieved from all other instances. In the instance INST2 in turn only three of the four asynchronous sections are queried, since jobs was additionally excluded. And finally, the cache of 10 minutes is reduced to 5 minutes (300 seconds), as this is sufficient time to get all data.

Important

If you define in the configuration file which sections you would like to retrieve, and by which method — you can also alter a asynchronous section to a synchronous section — you must specify all sections which should be executed in the respective area.

For example, if you only want locks from the synchronous query, specify the entire synchronous list and simply omit locks:

/etc/check_mk/mk_oracle.cfg
# Just exclude 'locks' from sync sections:
SYNC_SECTIONS='instance sessions logswitches undostat recovery_area processes recovery_status longactivesessions dataguard_stats performance'

The same applies for the other three variables in which the sections can be determined.

Configuring the TNS alias and TNS_ADMIN

Will be delivered later

3.4. Monitoring remote databases

Under unix-type systems, you can not only retrieve locally running instances, but also log on to remote ones and retrieve the databases running there. This, for example, is advantageous if you do not have access to the underlying system, but still want to monitor the database. It is also possible to monitor remote databases from a host on which the agent plug-in is running but no Oracle database.

For monitoring remote databases, there are the following requirements:

  • The Linux AIO access library is installed. Under RHEL and derivatives the package is called libaio.

  • The Oracle Instant Client is installed.

  • The sqlplus program already exists in the installation, or may have been installed as an extension package to the client.

As a rule, the conditions are already fulfilled when an Oracle installation is present on the host on which the agent plug-in is to be executed. Otherwise install the appropriate packages to do so.

In order for the agent plug-in to connect to the remote database, first store the access data in the configuration file. These are similar to the details for DBUSER, you already seen in the advanced user configuration. However there are a number of additional mandatory specifications:

/etc/check_mk/mk_oracle.cfg
# Syntax:
# REMOTE_INSTANCE_[ID]='USER:PASSWORD:ROLE:HOST:PORT:PIGGYBACKHOST:SID:VERSION:TNSALIAS'

REMOTE_INSTANCE_1='check_mk:mypassword::myRemoteHost:1521:myOracleHost:MYINST3:11.2'
REMOTE_INSTANCE_myinst1='/:::myRemoteHost:1521::MYINST1:11.2:INST1'

REMOTE_ORACLE_HOME='/usr/lib/oracle/11.2/client64'

In the example, two remote instances are being configured in two lines. So that each text line is unique, each variable is given an ID. These can be freely-chosen — they just have to be unique for each configuration file. As you have probably already noticed, the port specification is now followed by further values. These are partly optional, and partly necessary to establish a connection.

The first new value PIGGYBACKHOST is set to myOracleHost for the instance MYINST3. This specification assigns the results from the query by the piggyback mechanism to the specified host. If this is present as a host in Checkmk, the new services will appear there accordingly instead of on the host where the agent plug-in is running or from which the data was fetched. You do not see this specification on the second instance MYINST1 — the assignment to another host is optional.

The second new value SID is the instance name. Since the agent plug-in cannot see what instances are running on the remote host , a configuration line must be specified for each remote instance — this value is therefore required and thus not optional.

The third value VERSION is required and is also due to the fact that much metadata is only available if you are directly on the host. The version specification therefore also cannot be omitted, and it determines which SQL queries can be passed to the instance. In the example, both remote instances use version 11.2.

The fourth and last value TNSALIAS is again optional and can be used if you access the remote instance via the Oracle Wallet or the LDAP/Active Directory. In the event that the instance responds only to a specific TNS alias, you can specify this alias here. For the second remote instance, TNSALIAS has the value INST1.

To ensure that the sqlplus program is also found, use the REMOTE_ORACLE_HOME variable to specify where the Oracle client is located on the host that is runs the agent plug-in. Only then are all resources available that are required for the queries.

When querying remote instances, there are some restrictions and special features:

  • You cannot — understandably — exclude remote instances with SKIP_SIDS, and in return you do not need to include them using ONLY_SIDS.

  • Instances with the same name (SID) must not be assigned to the same host. This is especially relevant if you are fetching instances from multiple remote and/or local hosts where identical names are used.

4. Windows

4.1. Plug-in and configuration paths

On Windows, PowerShell is used as a scripting language to monitor Oracle databases. The functionality is similar to the agent plug-in under unix-type systems, but contains only a part of this. To use the agent plug-in under Windows you need the following paths, if you have not changed them.

Operating system Plug-in path Configuration path

Windows

C:\ProgramData\checkmk\agent\plugins

C:\ProgramData\checkmk\agent\config

4.2. Installing the agent plug-in

After you have created a user in the initial setup and stored this in the configuration file, install the agent plug-in. The agent plug-ins for Windows are stored on the host during the installation of the Checkmk agent for Windows. On the Oracle host, copy the file mk_oracle.ps1 from the directory C:\Program Files (x86)\checkmk\service\plugins\ into the plug-in directory described above. Alternatively, you can refer to the file in the installation path by updating the configuration file of the Checkmk agent.

4.3. Special features under Windows

Windows normally prevents the execution of PowerShell scripts if they have not been signed. You can now work around this problem very easily by modifying the policies for running PowerShell scripts for the user who is running the Checkmk agent:

PS C:\ProgramData\checkmk\agent\> Set-ExecutionPolicy -ExecutionPolicy Bypass -Scope LocalMachine
PS C:\ProgramData\checkmk\agent\> Get-ExecutionPolicy -Scope LocalMachine
Bypass

This option is useful if for a short time you want to test a script or the general functionality of the Checkmk agent. To avoid compromising the security of your system, revert this setting on production servers after testing has been completed:

PS C:\Program\checkmk\agent\> Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope LocalMachine
PS C:\Program\checkmk\agent\> Get-ExecutionPolicy -Scope LocalMachine
RemoteSigned

Understandably, you probably don’t want to change the guidelines every 60 seconds. You therefore set a permanent exception only for the relevant scripts. The configuration file of the agent plug-in must also be added to the exceptions. For easy readability, in this example the output has been completely omitted:

PS C:\ProgramData\checkmk\agent\> Unblock-File -Path .\plugins\mk_oracle.ps1
PS C:\ProgramData\checkmk\agent\> Unblock-File -Path .\config\mk_oracle_cfg.ps1

4.4. Advanced options

In the initial setup you have already learned about the first variables to get monitoring data from your Oracle instances. Depending on the application scenario, however, you will quickly need further options to be able to control the monitoring better and individually for each instance. Options that are also available to you under Windows are described in the following sections.

Advanced user configuration

As under Linux, you can also define not only a standard login for the Windows agent plug-in, but also individual access data for individual instances. You therefore have the same three options for specifying users:

Parameter Description

DBUSER

The default if no individual access data has been defined for the database instance.

DBUSER_MYINST1

Access data for a specific database instance — in this case for the instance MYINST1. The access data is only used for this instance.

ASMUSER

Special access data for Automatic Storage Management (ASM).
Important: For an ASM only one login at a time can be specified.

In addition, more options other than user name or password can also be specified here — these additional entries are also optional, however each entry must be filled in if it is used. A configuration may then look like this, for example:

C:\ProgramData\checkmk\agent\config\mk_oracle_cfg.ps1
# Syntax
# DBUSER = @("USERNAME", "PASSWORD", "ROLE", "HOST", "PORT")
# Default
# DBUSER = @("", "", "", "localhost", "1521")
$DBUSER = @("checkmk", "myPassword", "SYSDBA", "localhost", "1521")

@DBUSER_MYINST1 = @("cmk_specific1", "myPassword1", "", "10.0.0.73")
@DBUSER_MYINST2 = @("cmk_specific2", "myPassword2", "SYSDBA", "localhost", "1531")

@ASMUSER = @("cmk_asm", "myASMPassword", "SYSASM")

A few explanations regarding the above example:

  • You can define any number of individual access data. These are always preferred to the standard.

  • Each option is defined in a list. The order of the entries is not arbitrary, so therefore the order may not be rearranged.

  • When an optional field remains unchanged but a field following it is to be edited, both fields must be specified correctly, as with the DBUSER_MYINST2 entry, where the HOST is still set to localhost even though only the PORT is to be changed.

  • If optional fields are no longer needed after a certain point, they can be omitted, as with the ASMUSER entry, in which only the user’s role was specified.

  • If no special role is to be assigned to the user, but HOST or PORT is to be customized, simply enter a pair of inverted commas/double quotes ("") in this position.

Enabling and disabling instances

Even under Windows, one does not always want to include certain instances. The reasons for this have already been described in the section covering Linux. Two of the three parameters that you know from Linux can also be used with Windows:

Parameter Description

ONLY_SIDS

Here you can determine which instances are to be monitored. An instance is named by its system identifier (SID). This is a positive list, ignoring all instances that are not explicitly listed. This parameter is very useful if the number of instances to be monitored is smaller than the number of instances to be ignored.

EXCLUDE_<sid>

Since the SKIP_SIDS parameter is not available under Windows, you can only use EXCLUDE_<SID> to exclude instances and thus define a negative list. You do this by setting the value of the variable to ALL. You can also use this parameter to partially exclude an instance by excluding certain sections of the instance from monitoring. In this way, you define a negative list of the sections of an instance.
Important: An (+ASM) cannot be completely deactivated with this option.

The processing is done for each instance in the order shown in the table above. So first it is checked whether the instance is in ONLY_SIDS, and only then whether certain sections are to be excluded. If the variable EXCLUDE_<SID> is set to ALL, no section will be executed.

Below is an example where each variable is shown once:

C:\ProgramData\checkmk\agent\config\mk_oracle_cfg.ps1
$ONLY_SIDS = @("MYINST1", "MYINST3")
$EXCLUDE_INST1 = "tablespaces rman"
$EXCLUDE_INST3 = "ALL"

Note that ONLY_SIDS is a list, whereas EXCLUDE_INST1 is a string containing sections separated by spaces.

Determining the data to be fetched

The specification of which sections are to be actually fetched is done in the same way as with Linux, and the following is only an example for a Windows configuration:

C:\ProgramData\checkmk\agent\config\mk_oracle_cfg.ps1
# DEFAULTS:
# $SYNC_SECTIONS = @("instance", "sessions", "logswitches", "undostat", "recovery_area", "processes", "recovery_status", "longactivesessions", "dataguard_stats", "performance", "locks")
# $ASYNC_SECTIONS = @("tablespaces", "rman", "jobs", "ts_quotas", "resumable")
# $SYNC_ASM_SECTIONS = @("instance", "processes")
# $ASYNC_ASM_SECTIONS = @("asm_diskgroup")
# $CACHE_MAXAGE = 600

$SYNC_ASM_SECTIONS = @("instance")
$ASYNC_SECTIONS = @("tablespaces", "jobs", "rman", "resumable")

$CACHE_MAXAGE = 300

$EXCLUDE_INST1 = "undostat locks"
$EXCLUDE_INST2 = "jobs'
Important

If you define in the configuration file which sections you would like to retrieve, and by which method — you can also alter a asynchronous section to a synchronous section — you must specify all sections which should be executed in the respective area.

For example, if you only want locks from the synchronous query, specify the entire synchronous list and simply omit locks:

C:\ProgramData\checkmk\agent\config\mk_oracle_cfg.ps1
# Just exclude 'locks' from sync sections:
$SYNC_SECTIONS = @("instance", "sessions", "logswitches", "undostat", "recovery_area", "processes", "recovery_status", "longactivesessions", "dataguard_stats", "performance")

The same applies for the other three variables in which the sections are determined.

4.5. Monitoring remote databases

Monitoring remote databases is currently not possible using the Windows agent plug-in. Therefore if you want to monitor remote databases, you need a host with a compatible unix-type operating system.

5. Setting up with the Agent Bakery

CEE The setup can be greatly simplified in the commercial editions with the Agent Bakery, because syntax errors in the configuration files are avoided, and adaptations to changing environments can be implemented more easily. The main difference compared to a manual configuration is that you only need to work on the Oracle host at the command line if you want to make special Oracle-specific configurations. You can perform the setup with the Agent Bakery for Linux, Solaris, AIX and Windows.

Nevertheless, you cannot configure all functions of the agent plug-in with the Agent Bakery. On the one hand, when you are dealing with obsolete configuration options that are only available for compatibility reasons. These can then be replaced with the current options accordingly. On the other hand, if these are functions that require a major intervention and in-depth specialist knowledge. Accordingly, the custom SQL queries cannot be configured directly in the Agent Bakery.

Via Setup > Agents > Windows, Linux, Solaris, AIX and the Agents > Agent rules menu, you will find the page with the Oracle databases (Linux, Solaris, AIX, Windows) rule set. Create a new rule with Add rule. Here you will find all options available to you for configuring the agent plug-in:

Rule for configuring Oracle in the Agent Bakery.

Many options will be familiar to you from the manual setup. As described there, there are options that are not available for all operating systems. The title of these options shows for which operating systems they can be used.

5.1. Configuring users

In the simplest configuration for a unix-type operating system, the rule will look something like this:

Simplest rule for configuring Oracle in the Agent Bakery.

In the Agent Bakery you also have the option of creating standard users and creating exceptions for specific instances. The options, separated in the configuration file with a colon (for Linux & Co.) or as list entries (for Windows), you will find under Login Defaults as individual options which you can then fill out as needed. Of course, you can also use the Oracle Wallet here by simply changing Authentication method to Use manually created Oracle password wallet.

The configuration for Automatic Storage Management (ASM) is done analogously via the Login for ASM option, and the exceptions for specific instances are entered under Login for selected databases, as described in the advanced user configuration for Linux, Solaris, AIX and Windows.

5.2. Advanced options

The following table lists the remaining options in the Oracle databases (Linux, Solaris, AIX, Windows) rule set, along with a reference to where to find a description of the option:

Option Description

Host uses xinetd or systemd (Linux/AIX/Solaris only)

This option must be activated for unix-type systems with xinetd/systemd. With systemd the asynchronous execution of the agent plug-in is mandatory — at the interval you specify. You can find out more about this at the agent plug-in installation.

Instances to monitor

This option summarizes several options of the configuration file with which you can include or exclude instances for Linux, Solaris, AIX or Windows.

Add pre or postfix to TNSALIASes (Linux/AIX/Solaris only)

This option allows you to extend the TNS alias either globally or for a specific instance.

Sections - data to collect

All available sections are listed under this option, and these can be configured individually at global level. They therefore correspond to the variables SYNC_SECTIONS and ASYNC_SECTIONS and for ASM their counterparts SYNC_ASM_SECTIONS and ASYNC_ASM_SECTIONS. More on this in the section on the data to be fetched for Linux, Solaris, AIX or Windows.

Exclude some sections on certain instances

If you do not want to use EXCLUDE_<SID> to exclude the entire instance, but only to exclude a few sections, you can do so with this option, as described for Linux, Solaris, AIX or Windows.

Cache age for background checks

Specify here how long asynchronous sections should remain valid. The default value is 600 seconds (10 minutes).

Sqlnet Send timeout

This option is added to the sqlnet.ora file and sets a timeout for the instance itself.

Remote instances (Linux/AIX/Solaris only)

Configure remote instances with this option. It contains all elements of the configuration that you already know. To specify the variable’s ID, via Unique ID you can select from various options. These only affect the specified ID and must be unique.

ORACLE_HOME to use for remote access (Linux/AIX/Solaris only)

Here you can determine where the agent plugin finds the sqlplus program. You must enter a value here if you want to monitor a remote instance, but sqlplus cannot be found via the environment variables.

TNS_ADMIN to use for sqlnet.ora and tnsnames.ora (Linux/AIX/Solaris only)

If the two files are located in a directory other than /etc/check_mk/, you can use this option to specify the path name via the environment variable TNS_ADMIN.

sqlnet.ora permission group (Linux/AIX/Solaris only)

Oracle binaries permissions check (Windows only)

6. Clustered instances

6.1. Standby databases

Oracle supports so-called standby databases which can perform specified tasks, and which are usually simply copies of production or primary databases. These database concepts also require special monitoring mechanisms. You can find out more about these mechanisms in the following sections.

With Active Data Guard (ADG)

Once you have licensed and activated ADG, you do not need to make any changes to the configuration of the agent plug-in, since you can read from a standby instance at any time without having to interrupt the synchronization with the primary instance.

Without Active Data Guard (DG)

If the instances do not have ADG, the user with which the monitoring data from the standby instances is to be fetched needs the sysdba role. This permission enables the user to fetch at least part of the data, even if the primary instance fails and the instance on the standby server has not yet been changed from MOUNTED to OPEN.

Assign the permission to the user who is authorized to retrieve the data from the instances. Important: How this works may differ from the following example. Here the role is assigned to the user as created in the initial setup example:

sqlplus> grant sysdba to checkmk;

To enable the data to be queried by the agent plug-in on the standby server in case of an error, create the user on the primary instance, and then copy the password file to the standby server. Finally, in the plug-in’s configuration file, set the role of the user to sysdba:

/etc/check_mk/mk_oracle.cfg
# Syntax:
# DBUSER='USER:PASSWORD:ROLE:HOST:PORT:TNSALIAS'
DBUSER='checkmk:myPassword:sysdba'

Note that specifying a host, port, or TNS alias is optional, and can be omitted. In addition, the agent plug-in must of course be installed on the host of the primary instance as well as on the hosts of the standby instances.

Setting up cluster services

On Checkmk side, it is necessary — regardless of whether you are using ADG or DG — to customize the services and assign them to a cluster host. The corresponding check plug-ins have already been prepared to the extent that they can also be configured as cluster services. Create a cluster host in Checkmk and add the individual Oracle hosts on which the primary and standby instances are running to it as nodes. Then assign the following services to this cluster host:

  • ORA .* RMAN Backup

  • ORA .* Job

  • ORA .* Tablespaces

After this you will no longer need to worry about which instance the data comes from, and will have ensured seamless monitoring of the above-mentioned services — even in the event of a switchover of the primary instance.

6.2. Real Application Cluster (RAC)

Since there is a central storage for the data in a RAC, it is sufficient here to create the user for the agent plug-in only once. Only the agent plug-in needs to be installed and configured on each node of the Oracle cluster.

Important: Always set up the cluster’s nodes yourself, and do not query the Oracle SCAN listener. This is the only way to ensure that access via the agent plug-in works correctly.

Setting up cluster services

It also makes sense to set up cluster services for a RAC. In addition to the services that you assign to the cluster host under an (Active) Data Guard, you also assign the following services to the cluster host to ensure seamless monitoring in the event of a switch over:

  • ASM.* Diskgroup

  • ORA .* Recovery Area

7. Custom SQL queries (Custom SQLs)

7.1. Why custom SQL queries?

With its agent plug-in Checkmk already provides a large number of SQL queries with which you can monitor your database instances. To make these suitable for the widest possible range of technical and content requirements, they are of course kept in a very generalized form.

In order to be able to meet the individual requirements of each company for the monitoring of a specific database, Checkmk provides the possibility of creating your own custom SQL queries (custom SQLs for short) and having them retrieved with the agent plug-in. These custom SQL queries are then automatically recognized and monitored as own services in the Web interface.

Tip

It is only possible to use custom SQL queries under Linux, Solaris and AIX. This option is not available under Windows.

7.2. Simple custom SQL queries

Writing SQL queries

The easiest way to connect such a SQL is to use the Oracle Database: Custom SQLs check plug-in. To do this, first create the MyCustomSQL.sql file in the agent’s configuration directory on the host on which the SQL is to be executed.

The following is a dummy that illustrates the syntax:

/etc/check_mk/MyCustomSQL.sql
/*Syntax help in comments. The first word is alwyas the key word and ends with a ":"*/

/*details:Text to display in the service detail output*/
prompt details: Some details for the service output;

/*perfdata:METRIKNAME=CURRENTVALUE;WARN;CRIT;MAX METRIKNAME=CURRENTVALUE2;WARN;CRIT;MAX*/
prompt perfdata:MyMetricName1=10;15;20;30 MyMetricName2=16;15;20;30;
prompt perfdata:MyMetricName3=21;15;20;30 MyMetricName4=15;15;20;30;

/*long:Text to display in the long output of the service*/
prompt long: Here comes some long output for the service;
prompt long: Here comes some more long output for the service;

/*exit:Status of the service as a number*/
prompt exit:2;

The example shows on the one hand that you can define any number of statements in such a file. On the other hand, the syntax is very similar to that of a local check, especially with regard to metrics. In detail, this syntax is much more powerful here, because you can generate multi-line output, and this is then processed on the Checkmk server as a service. In principle, all lines are optional and do not need to be filled.

The possible keywords are in detail:

  • details: Here you can determine what should be output in the generated service’s Summary. This line is introduced with the keyword and a colon. The rest of the line is the output.

  • perfdata: Metrics is passed with this keyword. Within a line, you can create any number of metrics — each separated by a space. You can also distribute the output of the metrics over several lines. Just always start with the keyword perfdata:.

  • long: If the service should have a long output for the Details field, you can specify it here. You can also use this keyword multiple times to create multiple lines in the Details.

  • exit: If the output should have a certain status, you can specify this here. The known assignments 0, 1, 2, 3 are available for the statuses OK, WARN, CRIT, UNKNOWN.

Tip

You do not have to define the keyword elapsed manually. It is generated automatically at runtime to check how long the statements you defined took to process.

Configuring the agent plug-in

Now that you have defined your first, very simple SQL, make it known to the agent plug-in mk_oracle. This is done via the familiar configuration file, which you can expand accordingly:

/etc/check_mk/mk_oracle.cfg
SQLS_SECTIONS="mycustomsection1"

mycustomsection1 () {
    SQLS_SIDS="INST1"
    SQLS_DIR="/etc/check_mk"
    SQLS_SQL="MyCustomSQL.sql"
}

With the first option (SQLS_SECTIONS) you determine which individual sections you want to have executed. In the example, we have only specified one section (mycustomsection1) and then described it in more detail directly afterwards. Each section is actually a small function called by the agent plug-in.

In this function you can then determine further details and specify for which instances (SQLS_SIDS) this section applies. In addition, you also define where the file with the SQL statements is located (SQLS_DIR), and the name of this file (SQLS_SQL).

This simple configuration is sufficient to be able to see the result in Checkmk. To do this, perform a service discovery and activate the new service. Afterwards you will see this new service with the other services in the host overview:

The new service created by custom SQL queries in the service list.

7.3. Advanced options

The possibilities to monitor with custom SQL queries of course go beyond the simple example shown above. In the following you will find an overview of the available variables that you can use in the mk_oracle.cfg configuration file. For a detailed description, you can also call the agent plug-in mk_oracle with the --help option.

Tip

Variables that can only be set outside or only within a section function are marked accordingly. All others can be defined in both sections. If they are set outside of a section, they will apply globally to all sections.

Variable Short description Optional

SQLS_SECTIONS

The self-defined section functions to be executed by the agent plug-in.
This variable can only be set globally (outside of a section function).

No

SQLS_SIDS

The instances that are to execute the section(s).

No

SQLS_DIR

The path name of the directory in which the files with the cutsom SQL queries are stored.

No

SQLS_SQL

The file that contains the SQL statements for a section.

No

SQLS_SECTION_NAME

The name of the section that you evaluate with an own check plug-in for the cutsom SQL queries.

Yes

SQLS_SECTION_SEP

The separator of the individual elements in a line of the output, defined as a decimal ASCII ID. This variable can only be used in conjunction with the variable SQLS_SECTION_NAME. We recommend that you define your own separator for your own sections and use the ASCII ID 124 for the pipe character (|), as the agent plug-in always separates the elements of the output in the event of an error with |, in the format <SID>|FAILURE|<error description>. The following characters may not be used as a separator: ; [ ] =

Yes

SQLS_ITEM_NAME

Specifies a part of the generated service name. By default, the service name is composed of SID and the file name with the custom SQL queries. The value of this variable replaces the file name in the service name.
This variable can only be set locally (within a section function). It cannot be used together with the variable SQLS_SECTION_NAME.

Yes

SQLS_MAX_CACHE_AGE

Performs the same task as CACHE_MAXAGE — but for the custom SQL queries.

Yes

SQLS_DBUSER

Defines an individual user for a section.

Yes

SQLS_DBPASSWORD

Defines the password of the user defined with SQLS_DBUSER.

Yes

SQLS_DBSYSCONNECT

Only if the user defined with SQLS_DBUSER is sysdba or sysoper you have to define the associated role (SYSDBA or SYSOPER) with this variable.

Yes

SQLS_TNSALIAS

Defines an individual TNS alias for a section.

Yes

7.4. Using your own check plug-ins

If the possibilities of the syntax described above are not sufficient, you can also use the SQLS_SECTION_NAME variable to output your own section names for one or more SQL queries and define your own separator with SQLS_SECTION_SEP. However, this requires that you have also written an appropriate check plug-in and included it in your Checkmk site.

If you have written such a check plug-in, you are completely free to evaluate the output of the self-defined sections of the agent plug-in and can go your own way. Since this method is the most comprehensive, and also the most difficult, it is only mentioned here for completeness. It assumes that you know how to write an agent-based check plug-in and integrate it into the site. After that you assign the custom SQL queries with the variables to this check plug-in.

8. Diagnostic options

8.1. Testing connections

Linux, Solaris, AIX

If you have problems connecting to one or more instances on an Oracle server, the first thing you can do is to check basic parameters. With the -t option you can display the details of a connection. In the output the dummy sections have been omitted for readability. Also note that the plug-in must be provided with the file path to its configuration file beforehand, as it relies on this. The following is an example on a Linux server:

root@linux# export MK_CONFDIR="/etc/check_mk/"
root@linux# /usr/lib/check_mk_agent/plugins/mk_oracle -t
---login----------------------------------------------------------------
    Operating System:       Linux
    ORACLE_HOME (oratab):   /u01/app/oracle/product/11.2.0/xe
    Logincheck to Instance: XE
    Version:                11.2
    Login ok User:          checkmk on ORA-SRV01 Instance XE
    SYNC_SECTIONS:          instance dataguard_stats processes longactivesessions sessions recovery_status undostat logswitches recovery_area performance
    ASYNC_SECTIONS:         tablespaces rman jobs ts_quotas resumable
------------------------------------------------------------------------

Since this call is more likely to be made in the event of an error, you will of course then also receive further information: Both the connection string that was used for the connection and the first 100 characters of the error message that was returned during the connection attempt. With the help of this information, you can quickly identify simple configuration problems and then correct them accordingly.

Windows

The plug-in does not accept any parameters under Windows. So to test the connection here, temporarily limit the sections to be retrieved to instance and activate the DEBUG option:

C:\ProgramData\checkmk\agent\config\mk_oracle_cfg.ps1
# Syntax:
# $DBUSER = @("USERNAME", "PASSWORD")
$DBUSER = @("checkmk", "myPassword")

SYNC_SECTIONS = @("instance")
ASYNC_SECTIONS = @("")
DEBUG = 1

Then run the plug-in manually. Again, you will get information about how the plug-in tries to access the instances. An output may then look like this, for example:

C:\ProgramData\checkmk\agent\plugins\> .\mk_oracle.ps1
2020-08-23T12:48:20.3930944+02:00 DEBUG:value of DBVERSION software = xxx112020xxx
<<<oracle_instances>>>
2020-08-23T12:48:20.3930944+02:00 DEBUG:value of inst_name = xxxXExxx
2020-08-23T12:48:20.3930944+02:00 DEBUG:value of DBVERSION database = xxx112020xxx
2020-08-23T12:48:20.3930944+02:00 DEBUG:value of the_section = sql_instance
2020-08-23T12:48:20.3930944+02:00 DEBUG:now calling multiple SQL
2020-08-23T12:48:20.3930944+02:00 DEBUG:value of sql_connect in dbuser = checkmk/myPassword@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=XE))) as sysdba
<<<oracle_instance>>>
XE|FAILURE|...

8.2. Logging

Linux, Solaris, AIX

If the error cannot be found by checking a simple connection, the next step is to create a complete log, which logs all of the plug-in’s steps. Again, you will need the file path to the configuration file, and also the path to the plug-in’s cached data. Again, the output of the sections has been omitted here to improve readability:

root@linux# export MK_CONFDIR="/etc/check_mk/"
root@linux# export MK_VARDIR="/var/lib/check_mk_agent/"
root@linux# /usr/lib/check_mk_agent/plugins/mk_oracle -l
Start logging to file: /var/lib/check_mk_agent/log/mk_oracle.log

When in doubt, you can use this log to identify very precisely on which line of the script the problem has occurred.

Windows

Logging under Windows works similarly to the connection test itself. If the connection itself is stable, you can add the real sections back in the configuration file and then get a complete output of the logging accordingly.

8.3. Debugging

Linux, Solaris, AIX

If you can’t get to the problem, even with the help of the log, as a last resort the plug-in provides the complete output of all steps for an error analysis. This output is therefore the most comprehensive, and certainly the most difficult to read method to get to the cause of a problem, and should therefore only be used as a last resort. You can call the debugging with the -d option. Do not forget the necessary environment variable:

root@linux# export MK_CONFDIR="/etc/check_mk/"
root@linux# /usr/lib/check_mk_agent/plugins/mk_oracle -d

Important: In this output sensitive data such as passwords is not masked. So everything is readable in plain text.

Windows

A similar functionality is available under Windows. However, since you cannot pass any arguments to the plug-in, you have to switch on tracing manually:

C:\ProgramData\checkmk\agent\plugins\> Set-PSDebug -Trace 1
C:\ProgramData\checkmk\agent\plugins\> .\mk_oracle.ps1

9. Files and directories

9.1. On the Oracle host under Linux, Solaris, AIX

File path Description

/usr/bin/check_mk_agent

The Checkmk agent that collects all data about the host.

/usr/lib/check_mk/plugins/mk_oracle/

The Oracle agent plug-in in the usual directory for agent plug-ins. Note that the path name under AIX is slightly different: /usr/check_mk/lib/plugins/mk_oracle

/etc/check_mk/oracle.cfg

The configuration file for the agent plug-in. Again, AIX is different: /usr/check_mk/conf/mk_oracle.cfg

/etc/check_mk/sqlnet.ora

The configuration file required for the Oracle Wallet.

/etc/check_mk/tnsnames.ora

The configuration file which, if you specify it manually, determines an alias for a schema. Sample files are also located in the Oracle installation, but since the path differs from installation to installation, it cannot be specified in a standardized way.

9.2. On the Oracle host under Windows

File path Description

C:\Program Files (x86)\checkmk\service\check_mk_agent.exe

The Checkmk agent that collects all data about the host.

C:\ProgramData\checkmk\agent\plugins\mk_oracle.ps1

The Oracle agent plug-in in the usual directory for agent plug-ins.

C:\ProgramData\checkmk\agent\config\mk_oracle_cfg.ps1

The configuration file for the agent plug-in.

C:\ProgramData\checkmk\agent\config\sqlnet.ora

The configuration file required for the Oracle Wallet.

C:\ProgramData\checkmk\agent\config\tnsnames.ora

The configuration file which, if you specify it manually, determines an alias for a schema. Sample files are also located in the Oracle installation, but since the path differs from installation to installation, it cannot be specified in a standardized way.

9.3. On the Checkmk server

File path Description

~/share/check_mk/agents/plugins/mk_oracle

The agent plug-in for unix-type systems, which fetches the data on the Oracle host.

~/share/check_mk/agents/plugins/mk_oracle_crs

This agent plug-in for unix-type systems provides data to an Oracle Cluster Manager.

~/share/check_mk/agents/windows/plugins/mk_oracle.ps1

The agent plug-in for Windows, which fetches the data on the Oracle host.

~/share/check_mk/agents/cfg_examples/

Here are sample configuration files for unix-type systems in the files mk_oracle.cfg, sqlnet.ora and tnsnames.ora.

~/share/check_mk/agents/windows/cfg_examples/mk_oracle_cfg.ps1

An example configuration file for Windows.

On this page