"ORA-00942: table or view does not exist" for Oracle Metrics in Elastic Agent

[Wolfram_Haussig]

Hello all,

I am using the Elastic Agent with the Oracle integration to monitor the metrics of our Oracle DB. In the logs I find the error Error fetching data for metricset sql.query: fetch variable mode failed: dpiStmt_execute: ORA-00942: table or view does not exist.

Unfortunately, the integration documentation does not seem to contain any hints which tables are required, so I used the tables outlined in the metricbeat documentation:

SELECT * FROM V$BUFFER_POOL_STATISTICS;
SELECT * FROM v$sesstat;
SELECT * FROM v$statname;
SELECT * FROM v$session;
SELECT * FROM v$sysstat;
SELECT * FROM V$LIBRARYCACHE;
SELECT * FROM V$SYSMETRIC;
SELECT * FROM SYS.DBA_TEMP_FILES;
SELECT * FROM DBA_TEMP_FREE_SPACE;
SELECT * FROM dba_data_files;
SELECT * FROM dba_free_space;

All of them are readable with the user used in the Oracle integration but I still get those errors.

Has anyone an idea which table I am missing?

Best regards
Wolfram

[Agi_K_Thomas]

Please check for V$PGASTAT, v$sgastat, V$LIBRARYCACHE, gv$session s, v$process additionally.

[Wolfram_Haussig]

Thank you for you fast response, I requested those additional select privileges and I am able to query them:

Select * from V$PGASTAT;
Select * from v$sgastat;
Select * from gv$session;
Select * from v$process;
select * from V$LIBRARYCACHE;

It seems the amount of errors in the Metricbeat logs are reduced but they still exist:

At about 2pm (the red line) the privileges were granted.

Unfortunately, even the DEBUG logs do not tell me anything:

07:00:18.117
elastic_agent.metricbeat
[elastic_agent.metricbeat][debug] PublishEvents: 1 events have been published to elasticsearch in 19.993302ms.
07:00:18.117
elastic_agent.metricbeat
[elastic_agent.metricbeat][debug] ackloop: return ack to broker loop:1
07:00:18.117
elastic_agent.metricbeat
[elastic_agent.metricbeat][debug] ackloop:  done send ack
07:00:19.482
elastic_agent.metricbeat
[elastic_agent.metricbeat][error] Error fetching data for metricset sql.query: fetch table mode failed: dpiStmt_execute: ORA-00942: table or view does not exist
07:00:19.501
elastic_agent.metricbeat
[elastic_agent.metricbeat][error] Error fetching data for metricset sql.query: fetch table mode failed: dpiStmt_execute: ORA-00942: table or view does not exist
07:00:20.332
elastic_agent.metricbeat
[elastic_agent.metricbeat][debug] PublishEvents: 39 events have been published to elasticsearch in 33.127564ms.
07:00:20.332
elastic_agent.metricbeat
[elastic_agent.metricbeat][debug] ackloop: return ack to broker loop:39
07:00:20.332
elastic_agent.metricbeat
[elastic_agent.metricbeat][debug] ackloop:  done send ack
07:00:21.126
elastic_agent.metricbeat
[elastic_agent.metricbeat][debug] PublishEvents: 2 events have been published to elasticsearch in 35.40392ms.
07:00:21.127
elastic_agent.metricbeat
[elastic_agent.metricbeat][debug] ackloop: return ack to broker loop:2
07:00:21.127
elastic_agent.metricbeat
[elastic_agent.metricbeat][debug] ackloop:  done send ack
07:00:21.129
elastic_agent.metricbeat
[elastic_agent.metricbeat][debug] PublishEvents: 2 events have been published to elasticsearch in 27.910826ms.
07:00:21.130
elastic_agent.metricbeat
[elastic_agent.metricbeat][debug] ackloop: return ack to broker loop:2
07:00:21.130
elastic_agent.metricbeat
[elastic_agent.metricbeat][debug] ackloop:  done send ack
07:00:22.356
elastic_agent.metricbeat
[elastic_agent.metricbeat][error] Error fetching data for metricset sql.query: fetch table mode failed: dpiStmt_execute: ORA-00942: table or view does not exist
07:00:23.189
elastic_agent.metricbeat
[elastic_agent.metricbeat][debug] PublishEvents: 24 events have been published to elasticsearch in 41.832929ms.

[Agi_K_Thomas]

The logs shared above are associated with queries having response_format: table.

Please verify for the permissions for below tables / views additionally

dba_data_files
dba_temp_files
dba_free_space
dba_temp_free_space
dba_jobs
V$SYSTEM_WAIT_CLASS