Skip to end of metadata
Go to start of metadata

This page contains information specific to Oracles (generally broken) JDBC driver(s). This is information is a supplement to the general RDBMS data connector documentation. Start there before moving on to here.

Application Managed data connectors which use Oracle JDBC Thin driver seem to be losing connections after an idle period. This results in an exception, reported in the idp-access.log, such as

11:59:29.339 - ERROR [edu.internet2.middleware.shibboleth.....RDBMSDataConnector:...]
- RDBMS data connector [.., SELECT * FROM ...] - Unable to execute SQL query java.sql.SQLException:
An SQLException was provoked by the following failure: com.mchange.v2.resourcepool.ResourcePoolException:
Attempted to use a closed or broken resource pool

Switching to a Container Managed connection seems to solve the problem.

Configure an Oracle DB Data Connector

To configure a Container Managed connection with Tomcat 6, operate as follow:

  • copy the Oracle JDBC Thin driver .jar file into $TOMCAT_HOME/lib
  • if you use a Context Deployment Fragment such as $TOMCAT_HOME/conf/Catalina/localhost/idp.xml, add a Resource section to it:
    <Context docBase="/opt/shibboleth-idp/war/idp.war"
             privileged="true"
             antiResourceLocking="false"
             antiJARLocking="false"
             unpackWAR="false"
             swallowOutput="true" >
      <Resource name="jdbc/ORAIDP"
         type="javax.sql.DataSource"
         driverClassName="oracle.jdbc.OracleDriver"
         url="jdbc:oracle:thin:@127.0.0.1:1521:mysid"
         validationQuery="SELECT 1 FROM dual"
         username="scott" password="tiger"
         maxActive="20" maxIdle="10" maxWait="-1" />
    </Context>
    
    Oracle JNDI Datasource configuration is covered by the Tomcat documentation
  • in the connector use (note that the resource named jdbc/ORAIDP in the Deployment Fragment becomes java:comp/env/jdbc/ORAIDP in the connector definition):
    <resolver:DataConnector xsi:type="dc:RelationalDatabase" id="UNIQUE_ID">
    
         <!-- Dependency and Failover information would go here -->
    
         <dc:ContainerManagedConnection resourceName="java:comp/env/jdbc/ORAIDP" />
         ...
    
  • restart Tomcat

Oracle converts all column names to uppercase names, independent of how the SQL statement looks like. Therefore, it is necessary to use column mappings in any case and even if you think the SQL looks ok. Also see ResolverRDBMSDataConnector

More information on the Apache Commons DBCP wiki.

  • No labels