Ep. 53: Monitoring a remote Oracle database with Checkmk
| [0:00:00] | Welcome to the Checkmk channel. Today I will show you how to monitor a remote Oracle database. |
| [0:00:15] | In one of the previous videos, we installed the Checkmk agent and the agent plug-in on the target host which was hosting the Oracle database. Sometimes it is not possible to install the agent on the underlying host because of access issues or it's simply a cloud service which is only accessible remotely. |
| [0:00:36] | Today we will be using the same agent and the agent plug-in but this time we will be doing it on another Host. This another host will be serving as a piggyback host, which can be the monitoring server itself, or it can be any other server which is able to query the remote database and at the same time reachable by the Checkmk agent from the monitoring server. So without further Ado, let's get started. |
| [0:01:00] | For this demo I'm using an Amazon RDS Oracle database which is already created. There's already an endpoint that has been exposed and there's a port the security groups have already been configured so that it is reachable by the Linux server in question. So we can straight away move to our documentation and look at the prerequisites. This is our official documentation chapter number 3.3. |
| [0:01:38] | Here you have the prerequisites that should be available on the Linux server. It's the “libaio” library, the Oracle instant client should be installed, and the SQL program should exist, which is already available when you install the instant client. And once we are sure that these prerequisites are there then we have to create a user. Which is available in the initial article about the user creation. |
| [0:02:10] | So, let's move back to the command line. So now on the command line we will check whether these prerequisites are already met. We can already see that the Oracle instant client is already installed at the same time we can also look at whether the "libaio" package has also been installed. Yeah, it's already there. And we can then test the connectivity between this Linux server and the remote Oracle database, via the simple sqlplus command. Which basically tries to use the user admin, it tries to use the host which I get from the RDS page and the port and the SID. |
| [0:02:54] | I simply need to add my password here, and I'm able to successfully log in. So we can go back to the documentation and start creating the user. That we will be needing to query the remote Oracle database. So let's type in the username and password. Grant the select catalog role to this user. Let's do the other grant. |
| [0:03:36] | And that's it. We are done with the prerequisites of creating a read-only user. We verified all the libraries, and now we can go ahead and do the configuration of the “mk_oracle” agent plugin. For that we can go back to the browser, Checkmk Web UI, "Add" a host. I name this host as "remote-oracle-host", the IP address can be the IP address of my Linux host here which will be 10.201.04.224. |
| [0:04:19] | And I can simply save it and proceed with the Oracle agent plug-in configuration, for that you can go to the setup menu and go to the Oracle databases under "Agent rules". Click on “Add Rule” and then you are presented with the form where you need to go to the Oracle database configuration. Click on "Activation", because we want to deploy the Oracle database agent plug-in. We are running Rocky 8 under the hood. So I'll be using 'systemd' here because that's default of deploying the Checkmk agent, and then I set up the authentication method, the user was "checkmk". |
| [0:05:06] | And then we can proceed directly to the remote instances' configuration. It's available here, you have to choose a “Unique ID” where you have three options use the “remote SID”, use the “following ID” or just simply use the monitoring hostname. I'll choose the "remote SID". |
| [0:05:32] | Here you have to define what should be the name of the piggyback instance where this data pulled from the remote Oracle database will be attached to. So as it says the “Monitoring host this database should be mapped to”. |
| [0:05:49] | I write it here 'mypiggybackinstance' and here it will be the Oracle SID of my remote instance which is "TESTDB". And in this case we already saw when we performed the connectivity test, and then we need the DNS host name or IP address of the database server. For that we can go back to the AWS console and look at the AWS RDS page, copy the hostname from here and go back and paste the DNS name. |
| [0:06:25] | The TCP port number is 1521 in my case. Now we will select the Oracle release of the remote database. Since we don't have the release 19 here, I'm choosing the closest one to 19. We are working on this to extend this list, but this will be announced in the upcoming Werks. |
| [0:06:49] | That's it for the configuration I will assign this now to the remote Oracle host that I created in the initial step. Save this rule. And then we are going to bake this configuration, so that we have it attached to the host, and then we can download this RPM file. And in the meantime we can also activate the changes. |
| [0:07:17] | Let's go back to the command line and copy the downloaded RPM to our Linux server. So this is the RPM file that we have, and we are going to copy this to our Linux server. As soon as this file has been copied, we will SSH back to this host. And now let's try to install the package that we have downloaded there. |
| [0:07:59] | This is our package, we will now install this baked agent. And once this is installed we can go back to the etc/checkmk, where the configuration has been created. If we look at the entry this is how the 'mk_oracle.cfg' will look like. You have the basic “DBUSER” connection string “ASMUSER”, all your basic sections, “SYNC_SECTIONS”, “ASYNC_SECTIONS”, information about the sync and async sections for ASM, the cache age and last but not the least the “REMOTE_INSTANCE_TESTDB”. Where you have the username and password followed by the role. |
| [0:08:49] | Since we didn't have a role we left it blank and after that you have the public hostname or the endpoint of your remote Oracle database, the port, the Piggyback in where you would like to map the data once it is queried from the main remote Oracle database. This will be the real SID and this is the release. We can now try running the Checkmk agent and look at the output. If I pipe it with "less" and try to look for the Oracle section. |
| [0:09:31] | When we search for this word we see some dummy sections and then corresponding piggyback host or the piggyback instance that we created and finally another set of dummy sections. Why these dummy sections are necessary? Because they guarantee the integrated service dependency. When your database is down all the services, but the instance service will be stale. If you go further down we also see other sections here. |
| [0:10:04] | For example the Oracle instance, Oracle performance has all the performance metrics and if we go further down we have Oracle system parameter, processes, oracle_recovery_status. We also have information about oracle_tablespaces, oracle_jobs, and finally the last one is oracle_resumable. |
| [0:10:30] | Now let's move on to the Checkmk Web UI and perform a service discovery of these Oracle checks and have a look at the list of services. So, we can already see that the remote Oracle host that we set up already has it is able to communicate to the agent. And there are around 19 services that were found which were the basic Linux checks like CPU load, Filesystem, Interface, Mount options and the systemd checks. Let's accept it. |
| [0:11:09] | And let's also activate the changes. The name of our piggyback host was “mypiggybackinstance”. Either I can create it manually or I can use Dynamic host management to do that for me. If there are multiple instances coming from this single remote Oracle database. So I can also create them automatically here. Let's create a piggyback host we can restrict to the remote Oracle host and let's create it in the main directory and simply save it. |
| [0:11:54] | After I activate the changes you will already see that it starts creating a new piggyback host if I refresh the activate painting changes the changes have already been activated. Let's refresh the browser and on the 'Tactical Overview', you will see a new host that has been created this is our piggyback instance that we declared in the Oracle Agent Bakery rule. |
| [0:12:23] | And this is the host where the results for the remote article database has been mapped to. It has the standard Checkmk and Checkmk Discovery checks, it has information about the instance, logs and all the key metrics, information about the table spaces the jobs, and yeah that completes the whole list that we were expecting for. |
| [0:12:51] | Of course if there are more instances and more resources that are running on my remote Oracle database then the number of checks will vary. |
| [0:13:04] | So that's it for today thanks for watching please don't forget to like and subscribe, and I'll see you around. |
Interested in learning more? Register for a dedicated Synthetic Monitoring training course.
