Limited Metrics from Oracle Connection

mgress12

Has anyone successfully connected monitor 2023.2 to an Oracle geodatabase? We registered a connection but are unable to log the geodatabase metrics. 

We only see 4 metrics related to database sessions being observed. 

• Oracle v19
• Confirmed access from the Monitor server to the Oracle instance over the allowed port.
• Confirmed the oracle client is installed and we can authenticate with the database.
• Confirmed the oracle account has read permissions to appropriate tables.

Thanks

mgress12

A closer inspection of Monitor debug logs helped us figure out what was going on. We found we had to connect using the SDE account. The three errors below occurred while we were trying to connect our Oracle instance with an account we created that we thought had the proper permissions based on reviewing the Monitor documentation.

1. ” An Enterprise Geodatabase schema could not be found. Some metrics will not be returned.”
1. Likely an error that we need to use the SDE account which is our enterprise geodatabase schema account
2. “SELECT major || '.' || minor || '.' || bugfix "gdb_version" FROM VERSION WHERE EXISTS (SELECT * FROM all_tables WHERE table_name = 'VERSION') - ORA-00942: table or view does not exist”
1. This query is likely looking to query the table, SDE.VERSION. Being the table in the query isn’t fully qualified as SDE.VERSION, the Oracle account used wouldn’t be able to query it as it is not the VERSION table owner.
3. “SELECT SUM(a.value) "open_cursors" FROM v$sesstat a INNER JOIN v$statname b ON a.statistic# = b.statistic# AND a.con_id = b.con_id WHERE b.name = 'opened cursors current' - ORA-00942: table or view does not exist”
1. This was because the Oracle account was not granted read access to the view used in the INNER JOIN, v$statname.
2. Our SDE account could read the v$statname view in the database

So something from the Monitor documentation (https://doc.arcgis.com/en/monitor/2023.2/get-started/windows/register-oracle-databases.htm) that tripped us up:

1. The Oracle user account that you use to register the database for monitoring must have the following read permissions to collect attribute and metric data:
1. SDE system tables (if applicable)
2. v$instance
3. v$session
4. v$sesstat
5. v$sga
6. v$sysmetric
7. DUAL

*What we found is that we needed to be connected as SDE due to errors 1 and 2 above. That wasn’t clear in this step as we read it as any user account with proper privileges to those listed objects provided*

*With error 3 above, perhaps one more grant to add to this list is v$statname