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.
Download 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 ojdbc8.jar, ons.jar and ucp.jar from download archive to <FlexDeploy Home>/apache-tomcat-flexdeploy/lib.
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

FlexDeploy 5.3

Implementing UCP connections with Tomcat and Oracle

Installation Steps

Key Points

References