Oracle Exporter gives "unsupported value type" error for some Oracle internal views like v$lock.

[serdargurdal]

I'm submitting a ...

• [ ] bug report regarding "Unsupported value type" error.

We use Oracle exporter 2.45 on our environment and I would like to create metrics via using Oracle internal views.

For example I would like to create a metric to check blocking locks and other dml locks on the system but oracle exporter cannot count number of locks and gives "Unsupported value type" error for the following basic query.

Query: "SELECT COUNT(*) as VALUE FROM v$lock WHERE block=1 "

When i change the table name same query works. So it's not a query syntax issue but I couldn't find any detail information about the error although I ran the exporter in debug mode for extra logging.

Another query which gets same error is the following query which checks the error "ORA-600" in the alert.log of the PDB database.

SELECT COUNT(*) as value FROM v$diag_alert_ext WHERE message_text LIKE '%ORA-600%' AND originating_timestamp > sysdate -5(1440)

I need to select the data of the view "v$diag_alert_ext" to be able to check the alert.log of database and capture ORA errors that occur on the database.

v$diag_alert_ext view does contain alert.log data in multitenant architecture. It's very critical view to be monitor the database properly.

Prometheus oracle user does have select access on all Oracle internal views and tables. And these queries are tested via sqldeveloper and sqlplus and they worked well. However Oracle exporter gives "Unsupported value type" error.

Strange part is that same query works for another internal view like v$session. If you replace v$lock with v$session it works properly without any error.

What is the current behavior?

Oracle exporter gives "Unsupported value type" error for some oracle views.

If the current behavior is a bug, please provide the steps to reproduce and if possible a minimal demo of the problem

What is the expected behavior?

It shouldn't return "Unsupported value type" error.

What is the motivation / use case for changing the behavior?

When I prepare all required metrics we will use these custom metrics on more than 1000 databases in banking environment. Can you please help me on it ?

Please tell us about your environment:

Prometheus Version: 2.45.0 GoVersion : go1.20.5

[serdargurdal]

Maybe some Oracle internal tables and views are already defined in the exporter but some of them are not. Can it be related this ?

[hoepfnerj]

I had a similar problem. Try to set a higher query timeout with the following environment variable: QUERY_TIMEOUT: 10

[serdargurdal]

May i set it like below ?

export DATA_SOURCE_NAME=user/password@//primaryhost:1521,standbyhost:1521/service?connect_timeout=5&transport_connect_timeout=3&retry_count=3&query_timeout=10

[hoepfnerj]

May i set it like below ?

export DATA_SOURCE_NAME=user/password@//primaryhost:1521,standbyhost:1521/service?connect_timeout=5&transport_connect_timeout=3&retry_count=3&query_timeout=10

You can set it the same way as DATA_SOURCE_NAME as an extra environment variable. Or parameter "query.timeout" should work. See: https://github.com/iamseth/oracledb_exporter/blob/145b4ee86cbf5cea66d3d296a3254b30341bebeb/main.go#L32

[serdargurdal]

Thanks a lot for your quick response, I will try it out. However I checked the elapsed time of the query which gets this error. It takes max 3 seconds, if i am not wrong default timeout value is 15 seconds in Prometheus.

[serdargurdal]

I set it by 2 different options in the oracle_exporter service config file but it didn't help to solve the issue. Can you please check and confirm that I set it correctly ?

Environment=QUERY_TIMEOUT=10 ExecStart=/opt/oracle_exporter/oracle_exporter --web.config=tls.yml --default.metrics /opt/oracle_exporter/config.toml --log.level debug --query.timeout=10s

[hoepfnerj]

The "query.timeout" Parameter is an "int":

https://github.com/iamseth/oracledb_exporter/blob/145b4ee86cbf5cea66d3d296a3254b30341bebeb/collector/collector.go#L46

Maybe try to set it higher than 10. But this is just a guess of mine (I am not the developer). Btw, this parameter is not the prometheus timeout, but the timeout for the SQL query.

[serdargurdal]

I also set it to 60 seconds but it didn't help to solve the issue. The another thing I noticed that Oracle exporter gives same error if you have "LIKE" predicate in where condition of the query. It works for exact match searching but if you use LIKE it fails.

Can it be a bug ?

This one works

---

SELECT COUNT(*) as value FROM v$diag_alert_ext WHERE message_text= 'ORA-600' AND originating_timestamp > sysdate -5(1440)

This one fails

---

SELECT COUNT(*) as value FROM v$diag_alert_ext WHERE message_text LIKE '%ORA-600%' AND originating_timestamp > sysdate -5(1440)

[miguelmolgar]

Hi @serdargurdal, I had the same error this morning and it was caused by missing permissions. The user the exporter was using to connect to the oracle database didn't have SELECT permission on the table of the query. But this shouldn't be the case based your previous comment.