Error creating connection to Oracle

Elastic Stack Beats
,
1

Hi
I'm having a lot of problems getting the metricbeat connect to my Oracle database!

What ever I'm trying to put in my YML-file I always end up with this error:

Error fetching data for metricset oracle.tablespace: error creating connection to Oracle

My YML-file is like this:

metricbeat.modules:
- module: oracle
  metricsets: ["tablespace", "performance"]
  enabled: true
  period: 10s
  hosts: ["oracle://10.48.179.59:1521/(service name)?sysdba=1"]  
  username: "system"
  password: "****"

Any suggestions?

Best regards
Jens Christiansen

2

Hi @jenssc,

I assume you made sure the requirements mentioned in https://www.elastic.co/guide/en/beats/metricbeat/current/metricbeat-module-oracle.html#_requirements are fulfilled?

3

Hi @weltenwort
Everything should be installed according to the requirements. However, we currently use version 19.3 of the Oracle client!
I'm running everything on a Windows host so I'm not sure if LD_LIBRARY_PATH is required or only the standard PATH in Windows, but I've tried both without luck.

4

Is there anything else in the log after "error creating connection to Oracle"? Could you maybe share the full log output?

5

Hi @weltenwort

Here is the result of t a test modules (I've changed the username/password):

oracle...
  tablespace...
    error... ERROR error creating connection to Oracle: error doing ping to database: params=oracle://username%3Apassword%40ncps-t-db01.prod.sitad.dk%3A1521:SECRET-nKaKvQ2XRYo=@?connectionClass=GODROR&enableEvents=0&heterogeneousPool=0&poolIncrement=1&poolMaxSessions=1000&poolMinSessions=1&poolSessionMaxLifetime=1h0m0s&poolSessionTimeout=5m0s&poolWaitTimeout=30s&prelim=0&standaloneConnection=0&sysasm=0&sysdba=0&sysoper=0&timezone= extAuth=0: ORA-12560: TNS:protocol adapter error
  performance...
    error... ERROR error creating connection to Oracle: error doing ping to database: params=oracle://username%3Apassword%40ncps-t-db01.prod.sitad.dk%3A1521:SECRET-nKaKvQ2XRYo=@?connectionClass=GODROR&enableEvents=0&heterogeneousPool=0&poolIncrement=1&poolMaxSessions=1000&poolMinSessions=1&poolSessionMaxLifetime=1h0m0s&poolSessionTimeout=5m0s&poolWaitTimeout=30s&prelim=0&standaloneConnection=0&sysasm=0&sysdba=0&sysoper=0&timezone= extAuth=0: ORA-12560: TNS:protocol adapter error
oracle...
  tablespace...
    error... ERROR error creating connection to Oracle: error doing ping to database: params=oracle://user%3Apass%400.0.0.0%3A1521:SECRET-GIRSvgyNems=@?connectionClass=GODROR&enableEvents=0&heterogeneousPool=0&poolIncrement=1&poolMaxSessions=1000&poolMinSessions=1&poolSessionMaxLifetime=1h0m0s&poolSessionTimeout=5m0s&poolWaitTimeout=30s&prelim=0&standaloneConnection=0&sysasm=0&sysdba=0&sysoper=0&timezone= extAuth=0: ORA-12560: TNS:protocol adapter error
  performance...
    error... ERROR error creating connection to Oracle: error doing ping to database: params=oracle://user%3Apass%400.0.0.0%3A1521:SECRET-GIRSvgyNems=@?connectionClass=GODROR&enableEvents=0&heterogeneousPool=0&poolIncrement=1&poolMaxSessions=1000&poolMinSessions=1&poolSessionMaxLifetime=1h0m0s&poolSessionTimeout=5m0s&poolWaitTimeout=30s&prelim=0&standaloneConnection=0&sysasm=0&sysdba=0&sysoper=0&timezone= extAuth=0: ORA-12560: TNS:protocol adapter error

Hope you have a great idea to a solution :slight_smile:

6

I'm not at all knowledgeable about Oracle, so I can only try to make sure it's not a metricbeat problem. The ORA-12560 seems to be a very generic transport error, but at least it seems to be able to pick up the shared library. Can you connect to the DB using a different client from the machine in question using the same credentials?

7

Yes - that is working great.
But it seems that the service name is lost when the metricbeat is trying to connect. We can't use SID and my host configuration is like this (without username/password):

@ncps-t-db01.prod.sitad.dk:1521/ncpswmx.prod.sitad.dk

But that is no where to be found in the output:

@ncps-t-db01.prod.sitad.dk:1521:SECRET-nKaKvQ2XRYo=@?connectionClass=GODROR&enableEvents=0&heterogeneousPool=0&poolIncrement=1&poolMaxSessions=1000&poolMinSessions=1&poolSessionMaxLifetime=1h0m0s&poolSessionTimeout=5m0s&poolWaitTimeout=30s&prelim=0&standaloneConnection=0&sysasm=0&sysdba=0&sysoper=0&timezone=

8

The examples in the metricbeat Oracle module docs show the connection string in hosts as user:pass@host:port/db without the oracle:// prefix and with the username and password being embedded in the string. Did you try that?

9

@Mario_Castro any idea what else we could do to track down this connection problem?

10

I don't have oracle:// in front - that is added by the metricbeat.

11

Ok, just checking, because your yaml config snippet in the original post contains it. :+1:

12

actually... putting the oracle:// in front fixes two of the error

13

but I still gets these two errors:

oracle...
  tablespace...
    error... ERROR error creating connection to Oracle: error doing ping to database: params=oracle://user%3Apass%400.0.0.0%3A1521:SECRET-GIRSvgyNems=@?connectionClass=GODROR&enableEvents=0&heterogeneousPool=0&poolIncrement=1&poolMaxSessions=1000&poolMinSessions=1&poolSessionMaxLifetime=1h0m0s&poolSessionTimeout=5m0s&poolWaitTimeout=30s&prelim=0&standaloneConnection=0&sysasm=0&sysdba=0&sysoper=0&timezone= extAuth=0: ORA-12560: TNS:protocol adapter error
  performance...
    error... ERROR error creating connection to Oracle: error doing ping to database: params=oracle://user%3Apass%400.0.0.0%3A1521:SECRET-GIRSvgyNems=@?connectionClass=GODROR&enableEvents=0&heterogeneousPool=0&poolIncrement=1&poolMaxSessions=1000&poolMinSessions=1&poolSessionMaxLifetime=1h0m0s&poolSessionTimeout=5m0s&poolWaitTimeout=30s&prelim=0&standaloneConnection=0&sysasm=0&sysdba=0&sysoper=0&timezone= extAuth=0: ORA-12560: TNS:protocol adapter error
14

but now I get OK on table and performance!

15

Weird, but at least an improvement. Looking at the metricbeat module code the string is just passed to the ParseConnString() function of the godror package, so maybe one of the other connection string formats works, such as

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orclpdb1)))

?

16

Exactly and that is actually working if I put the oracle:// in front.

However, why is the metricbeat doing this connection (which is the one failing):

oracle://user:pass@0.0.0.0:1521:SECRET-GIRSvgyNems=@?connectionClass=GODROR&enableEvents=0&heterogeneousPool=0&poolIncrement=1&poolMaxSessions=1000&poolMinSessions=1&poolSessionMaxLifetime=1h0m0s&poolSessionTimeout=5m0s&poolWaitTimeout=30s&prelim=0&standaloneConnection=0&sysasm=0&sysdba=0&sysoper=0&timezone=

I Don't have anything in my yml file indicating that the metricbeat should do a connection to 0.0.0.0 and I didn't change the username/password - it says user:pass in the log!

17

Do you possibly have a duplicate config file in modules.d that contains these incorrect params?

18

Hi @jenssc :slightly_smiling_face:

Your error is a bit weird, not sure if related to your specific oracle version or something in the oracle library. While I investigate a bit more, can you try to connect as sysdba but like oracle://10.48.179.59:1521/(service name) AS SYSDBA instead of sysdba=1. It's a long shot but maybe it's related, I think the error comes from Oracle complaining about an invalid SID

19

let me try :slight_smile:

20

wauv you are good @weltenwort :slight_smile:

I've been adding my settings in the metricbeat.yml file but I see that you have the settings in the modules.d files and 0.0.0.0 is the default value of that file.