Implementing UCP connections with Tomcat and Oracle

Installation Steps

  • Make sure Tomcat server is stopped at this point. If running using shutdown.sh or shutdown.bat script. If you are following this as part of initial installation, most likely server is not yet started.
  • Download the latest Oracle JDBC driver from https://www.oracle.com/database/technologies/appdev/jdbc-ucp-19c-downloads.html. For example, ojdbc8-full.tar.gz.
  • Remove ojdbc jar files like ojdbc*.jar or ojdbc*dms.jar from <FlexDeploy Home>/apache-tomcat-flexdeploy/lib.
  • Copy all jar files (For example ojdbc8.jar, ons.jar, ucp.jar and other jars) from download archive to <FlexDeploy Home>/apache-tomcat-flexdeploy/libext. You will need to create libext folder as it may not exist.
  • Update <FlexDeploy Home>/apache-tomcat-flexdeploy/conf/context.xml as shown below. Make sure to take backup of existing context.xml file.
Oracle
<Resource name="jdbc/flexdbDS" auth="Container"
    type="oracle.ucp.jdbc.PoolDataSource"
    factory="oracle.ucp.jdbc.PoolDataSourceImpl"

    connectionFactoryClassName="oracle.jdbc.pool.OracleDataSource"
    minPoolSize="0"
    maxPoolSize="100"
    initialPoolSize="0"
    autoCommit="false"

    url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=**HOSTNAME**)(PORT=**PORT**)))(CONNECT_DATA=(SERVICE_NAME=**SERVICE_NAME**)))"
    user="fd_admin"
    password="**FD_ADMIN_PASSWORD**"

    fastConnectionFailoverEnabled="true"

    validationQuery="select 1 from dual"
    testOnBorrow="true"

    useLocalSessionState="true"
    defaultAutoCommit="false"/>
  • Replace **HOSTNAME** with database host name.
  • Replace **PORT** with database listener port.
  • Replace **SERVICE_NAME** with database service name.
  • Replace **FD_ADMIN_PASSWORD** with password of fd_admin.

Key Points

  • URL must be in the format listed in the example, the JDBC short format of host:sid:port should not be used.
  • If RAC is used for the database, multiple nodes can be listed in the address list of the URL or it can point to the scan listener for the cluster.
  • Sample URL for a RAC implementation that has a primary and secondary scan listener connecting to to a service name GOLD-CLOUD. This includes connection timeout, retry, and retry delay settings.
Sample RAC URL
url="jdbc:oracle:thin:@(DESCRIPTION = CONNECT_TIMEOUT=120) (RETRY_COUNT=20) (RETRY_DELAY=3) (TRANSPORT_CONNECT_TIMEOUT=3) (ADDRESS_LIST =(LOAD_BALANCE=on) (ADDRESS = (PROTOCOL = TCP) (HOST=primary-scan) (PORT=1521))) (ADDRESS_LIST =(LOAD_BALANCE=on) (ADDRESS = (PROTOCOL = TCP) (HOST=secondary-scan) (PORT=1521))) (CONNECT_DATA=(SERVICE_NAME = gold-cloud)))"
  • Type and factory must reference the UCP driver.
  • Actual timeout values will be environment specific. I would start with the defaults(not setting them) and then increase as needed.
  • Set CONNECT_TIMEOUT to a high value to prevent logon storms.
  • DO NOT use RETRY_COUNT without RETRY_DELAY
  • Set LOAD_BALANCE= ON per ADDRESS_LIST to balance SCANs 
  • Use one DESCRIPTION and more than one causes long delays 

References