Hi @ll,
do you know if it possible to execute oracle custom queries to remote instances with the mk_oracle plugin ?
We need to execute custom sql queries against windows oracle servers and as the windows plugin does not support custom queries we would like to execute these from the checkmk server itself.
Thank you 
if checkMK-server is linux, apply oracle Client, oracle Pluggin and connet remote via ora-net to Ora-DB on Windows.
Hi,
Is there any specific config to do when doing this. I’ve not been able to make it work.
The instructions in documentation works just fine from linux to linux hosts. But when I try from linux to windows, it doesn’t work.
I run it from the checkmk server where I’ve installed the prerequisites (libaio and instant client incl sqlplus).
my settings in mk_oracle.cfg are
REMOTE_INSTANCE_myname='checkmk:******::the-remote-db-host:1521::<SID>:12.1:'
REMOTE_ORACLE_HOME='/usr/lib/oracle/19.21/client64'
I set the MK_CONFDIR och MK_VARDIR and run
/usr/lib/check_mk_agent/plugins/mk_oracle -l
I get the following plugin output
<<<oracle_instance:sep(124)>>>
REMOTE_INSTANCE_MYNAME|FAILURE|ERROR: ORA-12545: Connect failed because target host or object does not exist SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus
and the log output is like this
2023-12-18 16:17:16 [0] [REMOTE_INSTANCE_myname] [do_sync_checks] [mk_ora_sqlplus] Piggyback host:
2023-12-18 16:17:16 [1] [REMOTE_INSTANCE_myname] [do_sync_checks] [mk_ora_sqlplus] Found '^ERROR at line'
I don’t understand what the errors are. It complains that the host does not exist even if I put the correct hostname or that there is an error.
I’m I missing something in the config?
BTW, I’m just testing this. The actual host has the checkmk agent and oracle plugin so I’m putting the same connection string on the linux host as it is on the actual windows host.
Any help is appreciated. Thanks
/r
did you test connection?
/usr/lib/check_mk_agent/plugins/mk_oracle -t
any output her?
you need oracle-Client at same machine
and better use tns-alias
Hi,
This is the output from mk_oracle -t
(I’ve changed the name of oracle db and hostnames.)
---login without TNS alias ----------------------------------------------------------------
Operating System: Linux
ORACLE_HOME (remote): /usr/lib/oracle/19.21/client64
Logincheck to Instance: REMOTE_INSTANCE_myname
Version:
Logindetails: checkmk/******@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxx.xx.xx)(PORT=1521))(CONNECT_DATA=(SID=ORACLE)(SERVER=DEDICATED)(UR=A)))
Error Message: REMOTE_INSTANCE_MYNAME|FAILURE|ERROR: ORA-12545: Connect failed because target host or object does n
SYNC_SECTIONS: instance sessions logswitches undostat recovery_area processes recovery_status longactivesessions dataguard_stats performance locks systemparameter
ASYNC_SECTIONS: tablespaces rman jobs resumable iostats
------------------------------------------------------
---login with TNS alias ----------------------------------------------------------------
Operating System: Linux
ORACLE_HOME (remote): /usr/lib/oracle/19.21/client64
Logincheck to Instance: REMOTE_INSTANCE_myname
Version:
Logindetails: checkmk/******@ORACLE
Error Message: REMOTE_INSTANCE_MYNAME|FAILURE|ERROR: ORA-12545: Connect failed because target host or object does n
SYNC_SECTIONS: instance sessions logswitches undostat recovery_area processes recovery_status longactivesessions dataguard_stats performance locks systemparameter
ASYNC_SECTIONS: tablespaces rman jobs resumable iostats
------------------------------------------------------------------------
I have the oracle client on the machine that has the checkmk agent. It throws an error when I don’t have it.
I don’t know how to interpret the errors. It seems that it tries to put the remote_instance_id as the instance name.
I have the same settings when doing it from linux to linux. But it fails from linux to windows. Is there any syntax differences in the 2 cases?
/r
Yes this can be done and we use this successfully to monitor Oracle on Windows.
A couple of things:
This requires that you exclude the async Oracle checks from running on the target hos and the sync checks from running via the Oracle remote config.
Work out if you need to run the monitoring for Oracle either fully remote or a hybrid as we do.
Next - it looks like your remote config is missing the piggyback hostname from the database config. This needs to be added in addition to the tnsnames alias.
Let me know if you need an example of the config and I can post one up.
Hi,
I didn’t put the piggyback host since to my understanding it is not mandatory. If you omit the results end up on the host where the checks are running from. In my case it’s the checkmk server. Also I’m just trying to get it to work.
One question I have is what port is the mk_oracle plugin using when doing the remote check? Is it still 1521 directly from the remote checking host to the db server as a normal agent does when running locally?
That means that the port 1521 on the db server needs to be open to be accessed from the checkmk server.
We are running the checkmk server in our environment with a ipsec tunnel to the customer’s db server and only the checkmk agent port 6556 is open.
That might explain why it’s not working.
@burgeau, could you post an example config on how you’ve set it up on both sides?
Thanks
/r
You will need to have access to port 1521 from the CMK server to connect to the DB listener for remote checks.
This is an example from the /etc/check_mk/mk_oracle.cfg file on the CMK server:
REMOTE_INSTANCE_TST_ORACLE1_PRIM='check_mk:<<check_mk_password>>::oracle.db.local:1521:<<piggyback_hostname>>:TST_ORACLE1_PRIM:19.17:TST_ORACLE1_PRIM_CMK'
hostname: oracle.db.local
ORACLE_SID: TST_ORACLE1_PRIM
TNS_NAME: TST_ORACLE1_PRIM_CMK
Exclude all the checks that we want to run via the CMK agent:
EXCLUDE='instance processes sessions longactivesessions logswitches undostat recovery_area recovery_status dataguard_stats systemparameter resumable locks jobs'
Specify what remote checks we want to run sync or async:
ASYNC_SECTIONS="rman tablespaces asm_diskgroup"
SYNC_SECTIONS="performance iostats"
On the target db server config we exclude the checks that are run remotely from executing.
Using piggyback lets us run local checks via the CMK agent - these are mostly the sync checks and the remote checks from the CMK server assigning the output to the db server.
This is for a Oracle RAC environment so it has the ASM config setup this way as well - making it a bit more complicated. But it works ok.
Thanks @burgeau, for sharing your config.
It makes sense, of course, to connect to 1521 from the remote host.
The reason I cannot make it work is that we have the checkmk server in our environment and then have ipsec tunnels set to our customers environment. And the only port open in the tunnel is 6556 for checkmk agent. It might be aproblem for our customers to open up also the db port to an external server even if it goes thorugh a ipsec tunnel.
Also I see that your hostname and remote instance id is the same. Does that matter?
Another question in relation to remote_instance.
If the login to the db server is done with ssh keys instead of username + pw, how can that be set up? Now I’m refering to connections in the customer’s network from one server to another (linux to linux) but with the same idea as above running async from the remote server
Is it even possible?
The servers can reach each other on port 1521.
/r
Understand and it can be difficult to work around access restrictions.
I had a typo in the db hostname - this has been corrected now.
Using SSH keys you can setup port forwarding (so long as the DB server allows that) - but you are then reliant on another connection for your monitoring.
Keeping the customers security in mind - what you can ask for is another Oracle listener be configured on the database server on a different port to 1521. The listener is configured to only allow connections from your CMK server IP address (this is done via configuration in the sqlnet.ora file). That way they can lock down the source/dest and port rules as required.
Great tips and suggestions. I take them with me.
So the ORACLE_SID and the remote_instance id is the same. Is that your choice or is it mandatory to be so?
/r
I’m not sure if the sid and the remote instance had to be the same - we kept it that way to make it easy to identify which config line was applicable to which instnace. This setup is being used for multiple Oracle RAC environments being monitored so each instance had its own config line as required.
What actual host and instance that gets connected to is defined in the TNS alias.
ok, thanks a lot for the thorough explanation.
I will try this out.
/r
This topic was automatically closed 365 days after the last reply. New replies are no longer allowed. Contact an admin if you think this should be re-opened.