For some time now, checkmk has been thinking about rewriting the mk_oracle plugin to make it more maintainable and to adapt it to new requirements.
Since Oracle 23c Multitenant is the standard and mk_oracle does not handle CDB/PDBs so well by default, a rework of the Oracle check is a necessary step.
I would be interested to know what your experiences are with the mk_oracle plugin? What does the existing mk_oracle do well and what is missing or needs to be improved from your point of view?
We use oracle-Exadata-Server. So no installation of checkmk-agent on db-server. As we use 2 node RAC an 2 node standby-RAC monitoring has to be done by remote-server.
checkMK-plugin uses v$views instead of gv$views. Due to this we have to configure 5 Remote_instances per database: 4 database-instances with sync_sections, 1 with async_sections.
Otherwise after database switchover most services would be stale or vanished:
example
#database: jobs, tablespaces, …
REMOTE_INSTANCE_pp1disp=‘checkmk:checkmk:::::pp1disp:19.18:pp1disp’
EXCLUDE_REMOTE_INSTANCE_pp1disp=$SYNC_SECTIONS
#primary-DB instances - sessions, processes, logswitch, locks, etc.
EXCLUDE_REMOTE_INSTANCE_pp1disp1=$ASYNC_SECTIONS
EXCLUDE_REMOTE_INSTANCE_pp1disp2=$ASYNC_SECTIONS
REMOTE_INSTANCE_pp1disp1=‘checkmk:checkmk:::::pp1disp:19.18:pp1disp1’
REMOTE_INSTANCE_pp1disp2=‘checkmk:checkmk:::::pp1disp:19.18:pp1disp2’
#Standby-DB instances
EXCLUDE_REMOTE_INSTANCE_pp1dispx1=$ASYNC_SECTIONS
EXCLUDE_REMOTE_INSTANCE_pp1dispx2=$ASYNC_SECTIONS
REMOTE_INSTANCE_pp1dispx1=‘checkmk:checkmk:::::pp1dispx:19.18:pp1dispx1’
REMOTE_INSTANCE_pp1dispx2=‘checkmk:checkmk:::::pp1dispx:19.18:pp1dispx2’
It would be very nice if checkmk would use gv$views for monitoring instead of v$views. So it could be one connect to database instead of 5. This Remotserver monitors 20 databases resulting in 100 connections per intervall to databases.
@Rendanic
Whats your way to monitoring Multitenant? Did you find a way/workaround?
I know at least some years ago you’ve been working on something PDB/CDB wise
@marcel.arentz
maybe something for you to note?
Hi,
it is expected that the plugin is using v$ instead of gv$ views.
Why?
The whole design of mk_oracle depends on the instancecheck which is always done with sqlnet-connections over network.
=> Make sure that connections from client hosts to the instance are possible.
=> The listener is automatically checked with this design.
=> we need to connect to each instance from every host for this.
=> no need to use a gv$view for information
All other checks should ignore connection issues from the instance check but we nned to check them on each node due to the connection via sqlnet.
=> no need to use a gv$view for information
Regards
Thorsten
Hi Thorsten
Can you tell us something about the Multitenant monitoring and if you solved the problems we encounter?
Cheers
Since mk_oracle only autodiscovers the CDBs and makes no logon to the PDBs (if it was not explicitly configured) it would be better if the general metrics were accessed entirely via CDB in one request. And then you would just have to check whether the individual PDBs are accessible and execute the custom queries if any are available.
If I’m not mistaken, all DBs (CDB and PDBs) are listed in the /etc/oratab file. Maybe that would be a better source for determining installed DBs.
@Rendanic
Hi Thorsten
Wouldn’t it be more efficient to connect to the CDB only once and query all available metrics for the CDB and all existing PDBs in one go via gv$views?
If the user wishes, mk_oracle could then use the PDB list and additionally check the connectivity for each PDB and perform custom queries for the desired PDBs.
In the best case, this would reduce the number of connections to 1. The behavior could be controlled via a “multi-tenant” and “check pdb” flag. For singledatabases or single PDBs mk_oracle would use v$views as before, and for CDB/PDB environments it would use the gv$views.
Regards, Lars
Hi Lars,
GV$-Views are RAC related. They represent data for all Nodes in a RAC instead of the data for the current connection. That has nothing to do with multitenant.
The plugin should be connecting to the cluster for each instance against the SID and not a service. That way we can guarantee to get details from the instance and we are sure that connections via listener are possible.
Using services is dangerous, because the listener could do some load balacing or redirction to another instance and we won’t get data from the expected instance.
Make sure that the user in the CDB has rights to read data from all PDBs who should be monitored by mk_oracle. In most cases the grant is missing and you think that the plugin must connect to the PDBs, but that is not needed for the default checks of mk_oracle.
Oracle has a lot data dictionary views and they added a new group with 12c for the multitenant option. Look out for ‘CDB_’-Views. They contain the data for the current PDB or all PDBs when connected to the CDB.
That’s why the plugin is using some CDB_ instead of DBA_-Views. Both types of views could be used in non multitenant as well. That’s why there is no big difference in the SQLs for multitenant.
Greetings
Thorsten