Monitoring ORACLE databases


1. Introduction

Check_MK provides comprehensive options for the monitoring of Oracle databases. So with the plug-in you can not only retrieve a database's Tablespaces or its active sessions, but also many other types of performance data. A complete list of the monitoring options can be found in our Catalogue of Check plug-ins. We extend this plug-in regularly, so it is always worth looking in the catalogue. Among others, Check_MK can monitor the following values:

  • Oracle Database Instance
  • ORACLE Database Processes
  • ORACLE Database Recovery Area
  • ORACLE tablespaces
  • ORACLE tablespaces: perfomance data
  • ORACLE Database Locks
  • ORACLE Database Jobs
  • ORACLE Database Long Active Sessions
  • ORACLE RMAN Backup Status
  • ORACLE ASM Disk Groups
  • Oracle Clusterware: Cluster Resources
  • Oracle Clusterware: Voting Check
  • Check Undo Retention of Oracle Instances
  • Check apply and transport lag of Oracle Data-Guard
  • Log switch activity of ORACLE database
  • Number of active sessions in ORACLE database

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

The following description is for an installation in Linux. The procedure as described is also applicable to the other Unix-like operating systems. The fundamentals for an installation under Windows will be covered below. Likewise for an installation using the Agent Bakery.

2. Initial configuration

The monitoring of a simple local instance can be configured in just three steps. Only the following prerequisites need to be satisfied:

  • The permission to create users in the database
  • You have system permissions for the server to be monitored

Creating users

The users are generated individually for each database – thus the respective instance must be set as an environment-variable before any login – for example, for the SID MYINST1:

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

Next, log in to the instance, create a user for the monitoring and give it the following permissions. Our example user is called check_mk – in principle the name is unimportant:

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

Creating the configuration file

Next create the configuration file mk_oracle.cfg in /etc/check_mk:

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

Please ensure that the file can only be read by root, and cannot be accessed by anyone else:

root@linux# chmod 600 /etc/check_mk/mk_oracle.cfg

Moving a script into the plug-in directory

The mk_oracle or mk_oracle.aix plug-ins are found in share/check_mk/agents/plugins/. If no direct access to the monitoring server is possible, the file can also be alternatively accessed via the web browser. With this the directory can be reached directly using the address line myserver/mysite/check_mk/agents/plugins/. Select the correct version of the plug-in for your database server there. The mk_oracle.ps1 plug-in for a Windows server can be found in myHost/mySite/check_mk/agents/windows/plugins/. Please note that the mk_oracle.bat plug-in is obsolete and should no longer be used.

Save the plug-in to the database server in the /usr/lib/check_mk_agent/plugins/ directory, and ensure that the plug-in is executable:

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

In older versions of the Check_MK agent the plug-in directory can be in another location. If case of uncertainty, the directory can be identified with the following:

root@linux# grep MK_LIBDIR= /usr/bin/check_mk_agent
export MK_LIBDIR="/usr/lib/check_mk_agent"

Once these steps have been performed the installation will be complete, a Service Discovery can be run in Check_MK, and the newly- identified services activated. In this example the screenshot shows only a selection of services:

3. Advanced configurations

Depending on the application scenario, a number of further options are available for configuring a monitoring of ORACLE databases. All of these options are available in the Agent Bakery. The following options for configuring the users are available:

Parameter Description
DBUSER The access data for the database instance if the same login data has been configured for all, or respectively, as standard for not explicitly defined SIDs.
DBUSER_MYINST1 Access data for the database instance MYINST1. The login data will only be used for the specified SID.
ASMUSER The access data for the Automatic Storage Management (ASM).

You are free to specify whether to define the same user data for every database, or to separately define individual logins. Only one user can be nominated for the ASM. Roles, host and ports are optional and and can be omitted. An mk_oracle.cfg can look like this:

/etc/check_mk/mk_oracle.cfg
# Syntax:
# DBUSER='myUser:mypassword:role:host:port'
DBUSER='check_mk:myPassword'
DBUSER_MYINST1='this_user:this_password:sysdba:localhost:1521'
DBUSER_MYINST2='that_user:that_password::localhost'

The following options are additionally available – with their help, among other functions it can be specified which data and the sequence in which the data is to be retrieved:

Parameter Description
ONLY_SIDS Monitoring of only the SIDs specified here. All other instances will be ignored.
EXCLUDE_MYINST1 The instance MYINST1 will not be be included or only partially included. This can make sense if only a few SIDs should be excluded and the number of instances to be monitored is large, or if single sections in particular SIDs should not be queried. Possible values are ALL or the names of sections.
SYNC_SECTIONS Sections are all SQL-statements, or respectively, functions in the plug-in. This parameter determines which sections should be synchronously queried. If this parameter is not used the standard values will be used, as can be seen in the configuration example below. Important: if the sections have been manually set, all sections must be present in either SYNC_SECTIONS or ASYNC_SECTIONS – otherwise they will not not be queried!
ASYNC_SECTIONS Sections to be asynchronously queried are specified here. The value will then be retained for a specified period. See CACHE_MAXAGE
SYNC_ASM_SECTIONS See SYNC_SECTIONS; Applies for ASM sections
ASYNC_ASM_SECTIONS See ASYNC_SECTIONS; Applies for ASM sections
CACHE_MAXAGE Overwrites the standard value for the retention time for the asynchrously-queried sections.

Here is an example of how this can look in the mk_oracle.cfg:

/etc/check_mk/mk_oracle.cfg
# Syntax:
# Variable='Wert'


# Only exclude the two specified sections in the MYINST1 schema:
EXCLUDE_MYINST1='longactivesessions logswitches'


# Exclude all sections in the MYINST2 instance:


# Sections not specified here will not be retrieved:
SYNC_SECTIONS='instance performance processes sessions undostat'
ASYNC_SECTIONS='jobs resumable rman tablespaces ts_quotas'

4. Remote databases

With the mk_oracle plug-in, under Linux you can also access databases running on another host. These can in fact be assigned to another host in Check_MK (Piggyback). For the remote acces to function either a local Oracle installation must be present, or the following prerequisites must be met:

  • The Linux AIO access library is installed. This packet is called libaio under RHEL/CentOS.
  • The Instant Client for Oracle Database is installed.
  • SQLPlus is already present in the installation, or must be installed as an extension packet on the client where applicable.

Likewise the plug-in can be installed on the host as described above. So that the plug-in can connect to a remote database the access data must be saved in the configuration file. This can be combined with the other configuration options so that local and remote databases can be simultaneously queried without problem. The extended configuration can look like this example:

/etc/check_mk/mk_oracle.cfg
# Syntax:
# Variable='Wert'
# REMOTE_INSTANCE_[ID]='myUser:myPassword:role:host:port:piggybackhost:SID:version'

REMOTE_INSTANCE_1='check_mk:mypassword::myRemoteHost:1521:myOracleHost:MYINST3:11.2'
REMOTE_ORACLE_HOME='/usr/lib/oracle/11.2/client64'

REMOTE_INSTANCE_this='check_mk:mypassword::myRemoteHost:1521::MYINST1:11.2'
REMOTE_ORACLE_HOME='/usr/lib/oracle/11.2/client64'

In the above example queries for two remote instances have been configured. The instance MYINST3 will then be allocated to the host myOracleHost in Check_MK using a piggyback process. For this to function the name of the host in Check_MK must be identical to that given in the configuration. Be aware of upper and lower case here. By omitting this configuration component the instance on the remote host will be queried, but its data will be assigned to the host on which the plug-in is running. This can be advantageous if you have access to the data, but due to deficiencies in the general access possibilities the host is not actually installed in Check_MK.

As you can see, the configuration is otherwise very similar to a normal query. You provide a user name and password, configure the port and the user's role if necessary, and specify the host name on which the database is running. Additionally, here you only need to enter the instance's SID and the version of the database on which the instance is located.

The information REMOTE_ORACLE_HOME will then be specified if the querying server does not have a local Oracle-installation and only has the use of the client. In this case there is unfortunately no other possibility for accessing this path. In the example the regular path for the client has been specified.

Important: The SIDs are only permitted to appear once if they simultaneously query local and remote instances and allocate them to the same host!

5. Distinctive features of cluster-instances

5.1. Standby-databases without Data Guard

To monitor Standby-Instances which do not use Active Data Guard, the user which retrieves the monitoring data requires the SYSDBA-role. Through this permission the user is then in the position of being able to retrieve at least a part of the data should the primary instance fail and the database has not yet been switched from MOUNTED to OPEN on the standby-server. You can assign this permission, among others, when creating the users as described above, using the following command:

sqlplus> grant sysdba to check_mk;

So that in the case of a failure situation the data can be retrieved from the standby-server, the user will be created on the primary instance and the password file then copied to the standby-server. Then, in the mk_oracle.cfg configuration file set the user's role likewise to SYSDBA:

/etc/check_mk/mk_oracle.cfg
DBUSER='check_mk:myPassword:sysdba:localhost:1521'

As always, the host name and port can be omitted if a local instance with the standard port is being configured. Note that the plug-in with its configuration file must also be configured on the standby-server so that the data can also be retrieved from there if necessary.

The following services require a configuration as Clustered Services:

  • ORA .* RMAN Backup
  • ORA .* Job
  • ORA .* Tablespace

Important: The SYSDBA-role is equivalent to root-access. Therefore, a sufficiently secure password is highly recommended!

5.2. Real Application Cluster (RAC)

In an RAC it is enough to create the user only once since it will be saved to the shared database. The plug-in with its configuration file must however be installed on every node.

For the monitoring, the SCAN Listeners should not be used as Hosts in Check_MK, rather the nodes themselves should be used. Only then can it be ensured that an access over the plug-in will function.

Here as well there are services which must be configured as Clustered Services:

  • ASM Diskgroup .*
  • ORA .* Recovery Area
  • ORA .* RMAN Backup
  • ORA .* Job
  • ORA .* Tablespace

6. Using the ORACLE Wallet

The user data was formerly always stored in the plug-in's configuration file. Not least this had the disadvantage that the data was stored unencrypted – not only in Check_MK, but also on the database server. Even if the permissions for the configuration file on the database server had been appropriately defined, the access data had nevertheless left the server and could be found on the Check_MK-Server.

In order to address this problem, Oracle provides the ‘Wallet’, in which the access data can be stored in an encrypted format. Check_MK can use this wallet, so that this access data no longer needs to be made known in the configuration file, and generally only needs to made known to the database administrator. For this first wallet you or the nominated database administrator will need to the create a wallet on the database server:

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

The plug-in will later always access this file if a connection to an instance needs to be created. So that the required user data can also be found, as a one-off action this data must be entered into the wallet. In the following example we are adding a user for the instance MYINST1:

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

To finish, the sqlnet.ora file must be created. Be certain 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))
 )

So that the connections can also be resolved the SIDs must be stored as an alias in tnsnames.ora. Examples for a configuration can be found in Check_MK and in your Oracle installation. The configuration can look like this, for example:

/etc/check_mk/tnsnames.ora
MYINST1
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MYINST1)
    )
  )

There will be no further need to save access data in mk_oracle.cfg and you can simply enter a leading slash (‘/’), and possibly the user role:

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

The monitoring user's access data will now fully administered from the database server and no longer from the monitoring server. Further access data can also be added to the wallet at a later date.

7. Using the Agent Bakery for a configuration

7.1. Initial setup

Under Linux, AIX and Solaris a configuration with the Agent Bakery will be greatly simplified, since syntax errors in the configuration files can be avoided and adaptations to changing environmental conditions can be done simply. The basic difference from a manual installation is that it is then only necessary to work in the ORACLE host's command line if a special, ORACLE-specific configuration is reqired. The Bakery currently has no facility for configuring a monitoring of ORACLE under Windows.

For the first installation it is sufficient if the Database user is created on the ORACLE host, and an appropriate rule in the Bakery. The rule set can be found in WATO ➳ Monitoring Agents ➳ Rules. By entering ‘oracle’ the rule set can also be searched-for in the search field:

If the same user has been created for all of the instances the Login Defaults can be used. Otherwise use the Login for selected databases option, and enter the instance's SID in addition to the login data:

For the Authentication Method there is a choice between the standard entry of a username/password and the ORACLE Wallet. With the latter no futher configuration is needed since everything has already been stored in the wallet.

The other fields are optional for the login. Here the role can be set as sysdba, and a specific hostname and port defined.

The entry mask for logins always has basically the same structure, or at least one very similar, so that one only needs to use the entry procedure once for it to become familar.

Once the rule has been saved and the the agents actualised on the ORACLE server, the initial setup as described at the beginning of this section has been completed.

7.2. Further options

In the Agent Bakery there is also the possibility of fine-tuning the ORACLE instances’ monitoring. The options in the extended configuration are also available for this purpose. Note that the sections to be retrieved must be defined fundamentally with a one-off action using the Sections - data to collect option. If this option is not activated Check_MK will use the plug-in's default.

You will then be able to exclude individual sections for specific instances with the Exclude some sections on certain instances option.

A remote instances monitoring can also be configured with the Agent Bakery. Here again is the first example from above. In the example we have used ‘1’ as the parameter ID. So that we can illustrate it here, the Unique ID must be appropriately adapted:

Every removed instance must receive an explicit ID. The host on which the data will be displayed in Check_MK can also be specified. In this case the Unique ID will be altered to Use monitoring host name and the host name entered into the Monitoring host this database should be mapped to option. Otherwise this field can remain blank.

8. Configuration in Windows

File Windows path
mk_oracle.ps1 %programfiles(x86)%\check_mk\plugins\
mk_oracle.cfg.ps1 %programfiles(x86)%\check_mk\

Important: It is currently not possible to perform a configuration for Windows using the Agent Bakery.

9. Diagnostic possibilities

To test whether a configuration on an ORACLE host is correct the plug-in can be invoked with the -t option. First therefore, the path to the console session's configuration files must be known:

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

Note that the path to the Plug-in can differ under some circumstances. How the path for the plug-ins can be identified is explained earlier in monitoring_oracle#install_plugin|Installing plug-ins]. The output from a successful connection will look something like this:

<<<oracle_instance>>>
<<<oracle_sessions>>>
<<<oracle_logswitches>>>
<<<oracle_undostat>>>
<<<oracle_recovery_area>>>
<<<oracle_processes>>>
<<<oracle_recovery_status>>>
<<<oracle_longactivesessions>>>
<<<oracle_dataguard_stats>>>
<<<oracle_performance>>>
<<<oracle_tablespaces>>>
<<<oracle_rman>>>
<<<oracle_jobs>>>
<<<oracle_ts_quotas>>>
<<<oracle_resumable>>>
<<<oracle_locks>>>
<<<oracle_instance>>>
<<<oracle_asm_diskgroup>>>
-----------------------------------------------
Logincheck to Instance: +ASM  Version: 12.1
Login ok User: SYS on ora12c.local
SYNC_SECTIONS= instance
ASYNC_SECTIONS= asm_diskgroup
-----------------------------------------------
Logincheck to Instance: MYINST1  Version: 12.1
Login ok User: CHECK_MK on ora12c.local
SYNC_SECTIONS= instance sessions logswitches undostat recovery_area processes recovery_status longactivesessions dataguard_stats performance
ASYNC_SECTIONS= tablespaces rman jobs ts_quotas resumable locks

If the connection has not been successful, its output will show the problem. An unsuccessful login could be due to an incorrect syntax in mk_oracle.cfg, for example. Here it is especially important to code a colon ( : ) between the individual parameters.

The login can also be tested by logging in to the Host in ORACLE with the configured user. If this is successful, also check that the appropriate permissions have been defined. Note that the user name is coded with capitals in the SQL query:

root@linux# export ORACLE_SID=MYINST1
root@linux# sqlplus check_mk
sqlplus> select * from user_role_privs where username='CHECK_MK'

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
CHECK_MK                       SELECT_CATALOG_ROLE            NO  YES NO

sqlplus select * from user_sys_privs where username='CHECK_MK'

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CHECK_MK                       CREATE SESSION                           NO

For debugging it is generally very useful to start first with the simplest possible configuration for the ORACLE monitoring, and then to increase the complexity step by step. In this way it can be quickly identified with which change the setup fails. Also make careful use of the configuration examples for this purpose. The path can be found in the following chapter.

10. Files and directories

10.1. On the ORACLE host

Path Function
/usr/bin/check_mk_agent The agent that collects all data for the host.
/usr/lib/check_mk/plugins/ The standard directory in which the plug-ins are stored.
/etc/check_mk/oracle.cfg The configuration file for the plug-in.
/etc/check_mk/sqlnet.ora The configuration file required for the ORACLE wallet.
tnsnames.ora The configuration file which defines an alias for a schema. Example files are also found in the ORACLE installation, but since the actual path depends on the installation there is no simple standard version.

10.2. On the Check_MK Server

Path Function
share/check_mk/agents/plugins/cfg_examples/ Here can be found examples of the configuration files required on the ORACLE host.
share/check_mk/agents/plugins/mk_oracle The plug-in that retrieves the data on the ORACLE host.
share/check_mk/agents/plugins/mk_oracle_asm The ASM instance can be monitored with this plug-in.
share/check_mk/agents/plugins/mk_oracle_crs This plug-in provides data for an ORACLE Cluster Manager.