misterbizarro
Contributor
Contributor

Connecting to Oracle Database to Collect Metrics

I am trying to configure my Hyperic installation to pick up metrics for my Oracle database, and I am pretty sure that I am doing something wrong on the oracle end.

Hyperic picks up the Oracle DB installation, but it requires some configuration to gather metrics from the database.

I can telnet to the port the database is listening on from a remote server, but I cannot tnsping it. So I am thinking something may be wrong with my Oracle net configuration (tnsnames.ora)
0 Kudos
6 Replies
misterbizarro
Contributor
Contributor

I fixed my tnsnames.ora on the remote client and now I can connect to the database from a remote location, but using the same connect string I cannot configure my Hyperic server to gather metrics from the database still:

The configuration has not been set for this resource due to : Invalid configuration: Error retrieving value: Query failed for UserCommits, while attempting to issue query SELECT value FROM V$SYSSTAT WHERE name = 'user commits':Io exception: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=169869568)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))

What kind of privileges need to be granted to the jdbc user?
Will a simple session grant work?
0 Kudos
David_hyperic
Contributor
Contributor

We use this script to create the hyperic user:

CREATE USER hyperic IDENTIFIED BY *******
TEMPORARY TABLESPACE TEMP
;

GRANT CONNECT, RESOURCE TO hyperic;
GRANT CREATE SESSION TO hyperic;
GRANT ALTER SESSION TO hyperic;
GRANT SELECT ANY DICTIONARY TO HYPERIC;
0 Kudos
misterbizarro
Contributor
Contributor

Is a hyperic user required for the login / stats to work?
0 Kudos

Yes, you should check the HyperFORGE Documentation here: http://support.hyperic.com/display/hypcomm/Oracle

At the bottom of the specific instance you are trying to monitor you will see info like this from Oracle 10g:
Configure Oracle 10g for Monitoring

Oracle monitoring is done through reading the system catalog. This is done using standard SQL queries which the plugin passes into Oracle via JDBC. In order to access the catalog for reading, an account needs to be created, or designated with the following privileges:

* SELECT ANY DICTIONARY

The following SQL statements illustrate the creation of such an account.

CREATE USER ${jdbcUser} IDENTIFIED BY MYPASSWORD;
GRANT CONNECT TO ${jdbcUser};
GRANT SELECT ANY DICTIONARY TO ${jdbcUser};
misterbizarro
Contributor
Contributor

I created the user as suggested, and I set the environment variables for the hyperic agent's user as suggested, but I still get the following error:

The configuration has not been set for this resource due to : Invalid configuration: Error retrieving value: Query failed for UserCommits, while attempting to issue query SELECT value FROM V$SYSSTAT WHERE name = 'user commits':ORA-00942: table or view does not exist

I am running 10.2.0.1.0 Database as a single-instance

Message was edited by: cronos4d
0 Kudos
misterbizarro
Contributor
Contributor

I solved the problem.
I executed the SQL on the sqlplus command line directly and quickly realized the hyperic user I created with your suggested script did not have sufficient privileges. I granted the user the dba role and everything works fine now.

Thanks for your help everyone.
0 Kudos