Error creating connection to Oracle

[jenssc (Jens Søe Christiansen)]

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

[weltenwort (Felix Stürmer)]

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?

[jenssc (Jens Søe Christiansen)]

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.

[weltenwort (Felix Stürmer)]

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

[jenssc (Jens Søe Christiansen)]

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

[weltenwort (Felix Stürmer)]

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?

[jenssc (Jens Søe Christiansen)]

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=

[weltenwort (Felix Stürmer)]

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?

[weltenwort (Felix Stürmer)]

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

[jenssc (Jens Søe Christiansen)]

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

[weltenwort (Felix Stürmer)]

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

[jenssc (Jens Søe Christiansen)]

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

[jenssc (Jens Søe Christiansen)]

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

[jenssc (Jens Søe Christiansen)]

but now I get OK on table and performance!

[weltenwort (Felix Stürmer)]

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)))

?

[jenssc (Jens Søe Christiansen)]

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!

[weltenwort (Felix Stürmer)]

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

[Mario_Castro]

Hi @jenssc

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

[jenssc (Jens Søe Christiansen)]

let me try

[jenssc (Jens Søe Christiansen)]

wauv you are good @weltenwort

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.