Metricbeat can't connect to Oracle 10g database

gurbelunder · February 9, 2024, 12:37pm

Hi community,

I'm trying to configure metricbeat on a Windows Server 2008 R2 server for 3 oracle 10.2.0.5 databases.
I know both is not newest, but customer uses this still and of course these databases are important and not able to upgrade them

Unfortunately metricbeat can't connect to my database. I've two different errors I want to show you.

In first case metricbeat can connect to database but it tries to login into database with wrong password. But password is correct. My oracle.yml in modules.d directory looks like this:

- module: oracle
  metricsets: ["tablespace", "performance"]
  enabled: true
  period: 10s
  hosts: ["HH-T-ORASRV-01:1521/ORACLEHH"]
  username: elastic
  password: password123

For this configuration I get these message in merticbeat logfile. Sorry for geman text, but the databases are running in german language.

2024-02-09T13:23:26.478+0100	ERROR	module/wrapper.go:266	Error fetching data for metricset oracle.performance: error creating connection to Oracle: error doing ping to database: params=oracle://HH-T-ORASRV-01%3A1521:************?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-01017: Benutzername/Kennwort ungültig; Anmeldung abgelehnt
type or paste code here

I just removed my passwords in this message for this forum so please not wonderung :D. But it's classical Oracle error ORA-01017 which mease "hey your login is not right. But it is
Password also have no special letters, only alphanumeric.

Then because I saw this Metricbeat: Oracle module failing to connect while SQL module works · Issue #24118 · elastic/beats · GitHub I tried this configuration in oracle.yml in modules.d directory.

- module: oracle
  metricsets: ["tablespace", "performance"]
  enabled: true
  period: 10s
  hosts: ["oracle://HH-T-ORASRV-01:1521/ORACLEHH"]
  username: elastic
  password: password123

Now metricbeat can't connect to my service because it means the service can't be found.

2024-02-09T13:32:16.427+0100	ERROR	module/wrapper.go:266	Error fetching data for metricset oracle.performance: error creating connection to Oracle: error doing ping to database: params=oracle://elastic:*********@HH-T-ORASRV-01:1521/ORACLEHH?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-12154: TNS: Angegebener Connect Identifier konnte nicht aufgelöst werden

But normal sqlplus connect works fine in this style:

D:\oracle\product\10.2.0\db_1\BIN>sqlplus elastic/********@//HH-T-ORASRV-01:1521/ORACLEHH

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Feb 9 13:36:09 2024

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Verbunden mit:
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production

ELASTIC@//HH-T-ORASRV-01:1521/ORACLEHH>

So something in both configuration seems to be wrong.

All tests I'm trying direct on the database server.

Maybe somebody have good idea?

Thanks and regards,
David

stephenb · February 9, 2024, 2:46pm

What version of metricbeat?

Rios · February 9, 2024, 2:46pm

Check the documentation, params are wrong:

  period: 10s
  hosts: ['user="elastic" password="<pass>" connectString="HH-T-ORASRV-01:1521/ORACLEHH"']

gurbelunder · February 9, 2024, 2:52pm

Sorry i forgot the versions. Because of support for Windows Server 2008 R2 I'm using Metricbeat 7.17.17. See also the support matrix a bit more down this side (sorry I didn't found any anchor).

For this version documentation is here documentation metricbeat 7.17 and from this documentation my configuration should be fine.

gurbelunder · February 9, 2024, 3:34pm

I also tried the parameter settings for the actual version of metricbeat. Just give it a try.

First I set this:

- module: oracle
  metricsets: ["tablespace", "performance"]
  enabled: true
  period: 10s
  hosts: ['user="elastic" password="mysecret" connectString="HH-T-ORASRV-01:1521/ORACLEHH"']

With that I get a

connectString=\"HH-T-ORASRV-01:1521/ORACLEHH\"": invalid character " " in host name; host parsing failed for oracle-performance: error parsing URL: parse "oracle://user=\"elastic\" password=\"mysecret\" connectString=\"HH-T-ORASRV-01:1521/ORACLEHH\"": invalid character " " in host name

in metricbeat logfile. So I tried it for connection string without the double quotes

- module: oracle
  metricsets: ["tablespace", "performance"]
  enabled: true
  period: 10s
  hosts: ["user='elastic' password='mysecret' connectString='HH-T-ORASRV-01:1521/ORACLEHH'"]

But also same error like first try. So I think this parameter format isn't included in this "older" version.

Rios · February 9, 2024, 3:43pm

Good point Stephen.
The version 7.17 syntax:
hosts: ["elastic:pass@HH-T-ORASRV-01:1521/ORACLEHH"]

gurbelunder · February 9, 2024, 6:14pm

I also tried this syntax. Here complete

- module: oracle
  metricsets: ["tablespace", "performance"]
  enabled: true
  period: 10s
  hosts: ["elastic:mysecret@HH-T-ORASRV-01:1521/ORACLEHH"]

But again I'm getting ORA-01017 error, that my password is not correct.

2024-02-09T19:11:30.411+0100	ERROR	module/wrapper.go:266	Error fetching data for metricset oracle.tablespace: error creating connection to Oracle: error doing ping to database: params=oracle://elastic%3Amysecret%40HH-T-ORASRV-01%3A1521:SECRET-7YuAXBX6DXc=@?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-01017: invalid username/password; logon denied

With that passwort I'm able to do a connection over sqlplus to the database.

D:\oracle\product\10.2.0\db_1\BIN>sqlplus elastic/mysecret@//HH-T-ORASRV-01:1521/ORACLEHH

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Feb 9 19:13:38 2024

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Verbunden mit:
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production


GLOBAL_NAME
--------------------------------------------------
elastic@ORACLEHH 09.02.2024 19:13:39
Abgelaufen: 00:00:00.01

ELASTIC@//HH-T-ORASRV-01:1521/ORACLEHH> exit

stephenb · February 9, 2024, 7:33pm

@gurbelunder Curious if you have a bunch of special characters in the password?

Any Chance you can test with a new version of metricbeat? Like 8.12.1 and / or with a simple password...some reason I remember a bug somewhere with # sign or something

I do know it should / does work, as I had a user using it ...

It uses the godor oracle driver under the covers...

Accordering to the code I see here

You should be able to use username and password separately.

	if params.Username == "" {
		params.Username = c.Username
	}

	if params.Password == "" {
		params.Password = c.Password
	}

The other thing I would suggest is try the raw SQL module

See if you can get that to work...

- module: sql
  metricsets:
    - query
  period: 10s
  hosts: ["oracle://sys:Oradoc_db1@172.17.0.3:1521/ORCLPDB1.localdomain?sysdba=1"]

  driver: "oracle"
  sql_query: 'SELECT name, physical_reads, db_block_gets, consistent_gets, 1 - (physical_reads / (db_block_gets + consistent_gets)) "Hit Ratio" FROM V$BUFFER_POOL_STATISTICS'
  sql_response_format: table

gurbelunder · February 10, 2024, 5:11pm

Actually I didn't try it with actual version of metricbeat because of Windows Server 2008 R2 is maximally supported by verison 7.17.x.

I also resetted passwort of the "elastic" database user as simple as I can and set it to "elastic". So I'm logging in with "elastic/elastic" and still get the ORA-01017 error.

I will look into the godror link to see if I can see something because as DBA I also know that password authentication was another in Oracle 10g databases and changed with Oracle 11g databases.

I also will try the sql module, let's see what I can get with these and I'll keep you informed.

system · March 9, 2024, 7:12pm

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.