Monitoring Oracle databases

You can also configure the login for multitenant databases. This is usually performed in the config 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 container and for all in the future:

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:

Important: For an ASM only one login at a time can be specified.

These variables additionally allow even more options, except for 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 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 options are processed determines the result. The entries are in fact processed per instance exactly in sequence as specified. Therefore if the variable ONLY_SIDS is set, SKIP_SIDS is no longer evaluated, and it is no longer possible to determine whether a variable EXCLUDE_ has been specified as 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 accordingly 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 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:

The mechanism therefore allows a value specified in the configuration file to predefine a default and, if necessary, to adjust this value again 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. INST2 in turn queries only three of the four asynchronous sections, 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:

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

In the Agent Bakery you also have the option of creating standard users and creating exceptions for specific instances. The options — separated by a colon — that you find in the configuration file, you will find here as individual options which you can then fill out as needed. In the simplest configuration it will look something like this:

Of course you can also use the wallet here by selecting Authentication method in the dropdown menu, and simply changing to Use manually created ORACLE password wallet.

The configuration for an ASM is done analogously via the Login for ASM option, and the exceptions for specific instances can be found at Login for selected databases.

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

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

Important: Here, too, only one login can be specified for an ASM.

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:

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 alternatives that you know from Linux can also be used here:

The processing is done for each instance in the order given 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_ 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 configuration:

Important: If you specify in the configuration file which sections are to be fetched in which way — you can also convert an asynchronous section into a synchronous one — you must specify all sections that are to be executed in the respective field. For example, if you want to exclude only locks from the synchronous query, specify the entire synchronous list and omit only locks:

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

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 authorization 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 authorization to the user who is authorised to retrieve the data from the instance(s). Important: How this works may differ from the following example. Here the role is assigned to the user as created in the example above:

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. Fianlly, 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 as always. In addition, the agent plug-in must of course be installed on both the primary Oracle host and the standby hosts.

On the monitored host you need to perform certain actions, and on the Checkmk side it is also necessary that you customize the services and assign them to a cluster host — regardless of whether you use ADG or DG. The corresponding check plug-ins have already been prepared to the extent that they can also be configured as Clustered Services. Create a cluster host in Checkmk, and to this cluster host assign the individual Oracle hosts to which the primary and the standalone instances are assigned. 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.

Even with an RAC, it makes sense to combine specified services in a cluster host. 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 switchovers:

The easiest way to connect such a SQL is to use the Custom SQLs for Oracle DBs check plug-in. To do this, first create the file mySQL.sql in the agent configuration directory of the host where 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 performance values. In detail, this syntax is much more powerful here, because you can generate multiline output, and then process it 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:

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

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

With the first option — SQLS_SECTIONS — you determine which individual sections you want to have executed. In the example we have specified only one, and then described it in more detail directly afterwards. Each section is actually a small function called by the mk_oracle 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. 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:

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.

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:

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:

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:

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

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.

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:

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 plug-in, you have to switch on tracing manually: