Monitoring Oracle 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 future containers:

With the standard login you can use regular or possibly even all instances for 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:

These variables additionally allow even more options apart from username 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 TNS alias (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:

A few explanations for the above example:

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:

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:

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.

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 requirements are diverse, and it is especially practical when it is undesirable to have certain long-running sections to be included in everything, or when only basic information from test instances is required, for example. 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:

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:

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 INST2 instance 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.

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

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

The TNS alias is a user-friendly name for a database connection. TNS stands for the Oracle network technology Transparent Network Substrate. A TNS alias makes it possible to establish a connection to a database instance without having to enter the full connection details (such as host name, port number or service name) each time. TNS aliases are defined in the file tnsnames.ora. The section on Oracle Wallet contains an example of how to define a TNS alias.

TNS_ADMIN is an environment variable that points to the directory in which Oracle configuration files such as sqlnet.ora and tnsnames.ora are located. By default, TNS_ADMIN is set by Oracle to $ORACLE_HOME/network/admin. In the configuration file, you can assign a different path to TNS_ADMIN, as in the following example for a specific Oracle installation:

Only if the variable is not set at all, it is set by the agent plug-in to /etc/check_mk/.

For security reasons, the agent plug-in mk_oracle no longer executes Oracle binaries under the root user. This affects the programs sqlplus, tnsping and — if available — crsctl. Instead, mk_oracle, for example, changes to the owner of the file $ORACLE_HOME/bin/sqlplus before executing sqlplus. This ensures that Oracle programs are only executed by a non-privileged user and thus prevents a malicious Oracle user from replacing a binary such as sqlplus and running it as root user.

The execution of Oracle programs by a non-privileged user can lead to problems when using an Oracle wallet, as this user may not be able to access the wallet-specific files. The non-privileged user needs the permissions to read the files $TNS_ADMIN/sqlnet.ora and $TNS_ADMIN/tnsnames.ora, to execute the wallet folder and to read the files in the wallet folder. You should not have any problems with the access rights as long as you have changed the group of the files and directories as described at the end of the section on Oracle Wallet.

The agent plug-in helps you with the diagnosis and checks whether there are problems accessing the files mentioned and displays them in the connection test. The exact procedure for diagnosing and correcting access rights can be found in the Checkmk Knowledge Base.

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:

In addition, more options other than username 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:

A few explanations regarding the above example:

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:

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:

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

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:

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

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

For security reasons, the agent plug-in mk_oracle.ps1 only executes Oracle binaries as administrator if these programs can only be changed by administrators. Administrators under Windows are the LocalSystem account and members of the built-in Administrators group. This applies to the programs sqlplus.exe, tnsping.exe and — if available — crsctl.exe. The agent plug-in mk_oracle.ps1 does not execute any of these programs if non-administrative users have one of the permissions Write, Modify or Full control for the file. This prevents the security risk of non-privileged users executing programs as administrator.

If necessary, change the access rights by removing the above-mentioned permissions of non-administrative users for the programs. The agent plug-in helps you with the diagnosis and checks whether non-administrative users have access to the files mentioned and displays them in the connection test. The exact procedure for diagnosing and correcting access rights can be found in the Checkmk Knowledge Base.

If it is not possible for you to adjust the permissions for the Oracle programs securely, you can allow individual users and groups to run the programs:

Only if there is no other way to ensure the Oracle monitoring you can switch off the access rights check as a final option.

However, there is also another way to execute the agent plug-in — without administrator rights. You can customize the execution of the agent plug-in and run it under the local Windows group Users, for example. To do this, edit the configuration file check_mk.user.yml of the Windows agent, for example like this:

In the commercial editions, you can have these entries created by the Agent Bakery with the agent rule Run plug-ins and local checks using non-system account.

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.

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:

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:

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.

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:

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.

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:

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:

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:

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:

With the first option (SQLS_SECTIONS) you determine which individual sections you want to have executed. Note that section here means a part of the agent plug-in output — and not a part of a database instance. 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:

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. If you start the agent plug-in with the -t option, it displays the details of a connection. Note that the agent plug-in must be provided with the paths to its configuration file and to the plugin’s cached data beforehand. In the output the dummy sections have been omitted for readability.

The following example is for a Linux server with systemd, on which the agent plug-in is executed asynchronously every 60 seconds:

On a Linux server with xinetd, call mk_oracle for the connection test as follows instead:

Since this call is more likely to be made in the event of an error, you will 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.

The agent 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:

Then run the agent 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:

If the error cannot be found by checking a simple connection, the next step is to create a a log file, which logs all of the agent plug-in’s steps. Do not forget the necessary environment variables here either. In the following example, the output of the sections has also been omitted to improve readability.

Here is the call for a Linux server with systemd, on which the agent plug-in is executed asynchronously every 60 seconds:

Here is the call of mk_oracle for logging on a Linux server with xinetd:

You can use the generated log file to identify very precisely on which line of the script the problem has occurred.

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

If you cannot get to the problem, even with the help of the log, as a last option the agent plug-in provides the complete output of all steps for 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.

The following is an example of debugging on a Linux server with systemd, on which the agent plug-in is executed asynchronously every 60 seconds:

Here is the call of mk_oracle for debugging on a Linux server with xinetd:

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

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