Implementing UCP connections with Tomcat and Oracle

In order to use UCP Connection Pool, update <FlexDeploy Home>/apache-tomcat-flexdeploy/conf/context.xml as shown below. Make sure to take backup of existing context.xml file.

<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.

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

• https://www.oracle.com/technetwork/database/application-development/planned-unplanned-rlb-ucp-tomcat-2265175.pdf
• https://www.oracle.com/technetwork/database/enterprise-edition/ucp-transition-guide-129515.pdf
• https://docs.oracle.com/cd/E11882_01/java.112/e12265/connect.htm#CHDEEIJE