Jdbc connection pooling with Oracle Databases

We currently have a small collection of metrics being collected from our oracle 12c databases via the jdbc data collection with the database connection details defined in packages in collectd-configuration.xml like this:

 <package name="ABBCS-Oracle-artelprd" remote="false">
      <filter>(IPADDR != '0.0.0.0') &amp; (catincDatabases_artelprd)</filter>
      <include-range begin="1.1.1.1" end="254.254.254.254"/>
      <include-range begin="::1" end="ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff"/>
      <service name="ABBCS-Oracle-artelprd" interval="300000" user-defined="false" status="on">
         <parameter key="collection" value="ABBCS-Oracle"/>
         <parameter key="thresholding-enabled" value="true"/>
         <parameter key="driver" value="oracle.jdbc.driver.OracleDriver"/>
         <parameter key="user" value="monuser"/>
         <parameter key="password" value="hiddenpassword"/>
         <parameter key="url" value="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=OPENNMS_JDBC_HOSTNAME)(PORT=9999))(CONNECT_DATA=(SERVICE_NAME=artelprd)))"/>
         <parameter key="port" value="9999"/>
      </service>
   </package>

also in pollerd-configuration, and as well in the foreign source definition.

Then replicated for every database we have to monitor…

This configuration works, however, the DBA’s have complained that monitoring is now throwing a large number of connections at the database every poll interval, and this is only going to get worse as we configure more monitoring.

So I went looking to see if there was any connection pooling options available and found that I should be able to define the database connection details in opennms-datasources.xml, and then refer to the data source in the collectd-configuration.xml, poller-configuration.xml and the foreign source definition.

so the above collectd-configuration.xml entry gets converted to:

   <package name="ABBCS-Oracle-dev" remote="false">
      <filter>(IPADDR != '0.0.0.0') &amp; (catincDatabases_oracle)</filter>
      <include-range begin="1.1.1.1" end="254.254.254.254"/>
      <include-range begin="::1" end="ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff"/>
      <service name="ABBCS-Oracle-dev" interval="300000" user-defined="false" status="on">
         <parameter key="collection" value="ABBCS-Oracle"/>
         <parameter key="thresholding-enabled" value="true"/>
         <parameter key="data-source" value="OracleDev"/>
      </service>
   </package>

with the connection details defined in opennms-datasources.xml as:

<?xml version="1.0" encoding="UTF-8"?>
<datasource-configuration xmlns:this="http://xmlns.opennms.org/xsd/config/opennms-datasources"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://xmlns.opennms.org/xsd/config/opennms-datasources
  http://www.opennms.org/xsd/config/opennms-datasources.xsd ">

  <connection-pool factory="org.opennms.core.db.HikariCPConnectionFactory"
    idleTimeout="600"
    loginTimeout="3"
    minPool="50"
    maxPool="50"
    maxSize="50" />

  <jdbc-data-source name="opennms"
                    database-name="opennms"
                    class-name="org.postgresql.Driver"
                    url="jdbc:postgresql://localhost:5432/opennms"
                    user-name="opennms_user"
                    password="hiddenpassword" />

  <jdbc-data-source name="opennms-admin"
                    database-name="template1"
                    class-name="org.postgresql.Driver"
                    url="jdbc:postgresql://localhost:5432/template1"
                    user-name="opennms_user"
                    password="hiddenpassword" />

  <jdbc-data-source name="OracleDev"
                    database-name="artelprd"
                    class-name="oracle.jdbc.driver.OracleDriver"
                    url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=1.2.3.4)(PORT=9999))(CONNECT_DATA=(SERVICE_NAME=artelprd)))"
                    user-name="monuser"
                    password="hiddenpassword" />

</datasource-configuration>

However, this does not work, and I’m seeing a nodeLostService with the following Descxription:

A ABBCS-Oracle-dev error was identified on vic-evan01-dbs-ora04_viceldev because of the following condition: JDBC service is not responding on: 1.2.3.4, 08001, org.postgresql.util.PSQLException: Connection to1.2.3.4:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections..

which indicates it is trying to use a postgresql connection rather than the oracle connection

Do I need to separate the connection pools into Postgresql specfic and Oracle specific pools to get this to work? or is there some other magic I’m missing?

OpenNMS version: 24.1.3

Regards,
John Blackburn.

Here is the error I get when trying to test the connection pool using Karaf Shell:

admin@opennms> collection:collect org.opennms.netmgt.collectd.JdbcCollector 1.2.3.4 collection=ABBCS-Oracle source=OracleDev
NOTE: Some collectors require a database node and IP interface.
Collect failed with:java.util.concurrent.ExecutionException: org.opennms.netmgt.collectd.jdbc.JdbcCollectorException: Unable to connect to JDBC URL: 'jdbc:postgresql://1.2.3.4/opennms'
	at java.base/java.util.concurrent.CompletableFuture.reportGet(CompletableFuture.java:395)
	at java.base/java.util.concurrent.CompletableFuture.get(CompletableFuture.java:2022)
	at org.opennms.netmgt.collection.commands.CollectCommand.execute(CollectCommand.java:171)
	at org.opennms.netmgt.collection.commands.CollectCommand.execute(CollectCommand.java:73)
	at org.apache.karaf.shell.impl.action.command.ActionCommand.execute(ActionCommand.java:84)
	at org.apache.karaf.shell.impl.console.osgi.secured.SecuredCommand.execute(SecuredCommand.java:68)
	at org.apache.karaf.shell.impl.console.osgi.secured.SecuredCommand.execute(SecuredCommand.java:86)
	at org.apache.felix.gogo.runtime.Closure.executeCmd(Closure.java:599)
	at org.apache.felix.gogo.runtime.Closure.executeStatement(Closure.java:526)
	at org.apache.felix.gogo.runtime.Closure.execute(Closure.java:415)
	at org.apache.felix.gogo.runtime.Pipe.doCall(Pipe.java:416)
	at org.apache.felix.gogo.runtime.Pipe.call(Pipe.java:229)
	at org.apache.felix.gogo.runtime.Pipe.call(Pipe.java:59)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.opennms.netmgt.collectd.jdbc.JdbcCollectorException: Unable to connect to JDBC URL: 'jdbc:postgresql://1.2.3.4/opennms'
	at org.opennms.netmgt.collectd.jdbc.JdbcAgentState.getJdbcConnection(JdbcAgentState.java:126)
	at org.opennms.netmgt.collectd.JdbcCollector.isGroupAvailable(JdbcCollector.java:244)
	at org.opennms.netmgt.collectd.JdbcCollector.collect(JdbcCollector.java:133)
	at org.opennms.netmgt.collection.client.rpc.CollectorClientRpcModule$1.get(CollectorClientRpcModule.java:83)
	at org.opennms.netmgt.collection.client.rpc.CollectorClientRpcModule$1.get(CollectorClientRpcModule.java:77)
	at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1700)
	... 3 more
Caused by: org.postgresql.util.PSQLException: FATAL: password authentication failed for user "postgres"
	at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:514)
	at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:141)
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:192)
	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
	at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195)
	at org.postgresql.Driver.makeConnection(Driver.java:454)
	at org.postgresql.Driver.connect(Driver.java:256)
	at org.opennms.netmgt.collectd.jdbc.JdbcAgentState.getJdbcConnection(JdbcAgentState.java:120)
 	... 8 more

As you can see, it is using the postgresql driver instead of the oracle one.

Any assistance greatly appriciated.

I just found that if I change the karaf shell command parameter “source=OracleDev” to “data-source=OracleDev”, the Karaf Shell command returns successfully. So it appears that the issue is the collectd or poller configuration as I still get this error:

A ABBCS-Oracle-dev error was identified on vic-evan01-dbs-ora04_viceldev because of the following condition: JDBC service is not responding on: 1.2.3.4, 08001, org.postgresql.util.PSQLException: Connection to 1.2.3.4:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections..

I’ve found a workaround for this.

It appears that the failure only occurs in pollerd not collectd, so by changing the pollerd configuration to a tcp monitor on the database listen port, the error does not occur, and the collectd configuration is correctly using the Oracle driver and my DBA has confirmed that using the connection pool, the number of connections to the database has dropped to 1.