Guide - Oracle JDBC Universal Connection Pool (UCP)

1 Introduction

The following configurations and deployments are required to support the correct interaction between the Intershop 7 application server and the Oracle Database.

The Intershop 7 application server requires specific Oracle client JAR files. The Intershop 7 setup does not install these Oracle JAR files, as Intershop cannot accept the Oracle OTN License Agreement and restrictions especially for the distribution of the Oracle oci-client, ojdbc*.jar and ucp.jar on behalf of Intershop's customers. The customers must obtain these files themselves.

1.1 Glossary

This glossary describes the terms in this document:

IS_HOME

The environment variable specifying the Intershop 7 home directory on the local host.

IS_SHARE

The environment variable specifying the Intershop 7 shared file system.

ORACLE_HOME

The environment variable specifying the directory path where the Oracle client or server software is installed.

System Identifier (SID)

The Oracle system identifier (SID) identifies a specific instance of the running Oracle software. For a Oracle Real Application Clusters database, each node within the cluster has an instance referencing the database.

Port

Number of the port where the Oracle server listens for requests. A number used by TCP/IP that identifies the end point for a connection to a host that supports multiple simultaneous connections.

Host

A single standalone instance address.

RAC-node, VIP

A virtual IP address for each node.

SERVICE_NAME

To identify the Oracle database database service to access.
A logical representation of a database, which is the way a database is presented to clients. A database can be presented as multiple services and a service can be implemented as multiple database instances. The service name is a string that is the global database name, that is, a name comprising the database name and domain name, entered during installation or database creation. If you are not sure what the global database name is, then you can obtain it from the value of the SERVICE_NAMES parameter in the initialization parameter file.

JDBC

An industry-standard Java interface for connecting to a relational database from a Java program, defined by Sun Microsystems.

Implicit Connection Caching (ICC)

Connection caching, generally implemented in the middle tier, is a means of keeping and using cache of physical database connections. The implicit connection cache is an improved Java Database Connectivity (JDBC) 3.0-compliant connection cache implementation for DataSource. Java and Java2 Platform, Enterprise Edition (J2EE) applications benefit from transparent access to the cache, support for multiple users, and the ability to request connections based on user-defined profiles.

Universal Connection Pool (UCP)

The Universal Connection Pool (UCP) for JDBC provides a connection pool implementation for caching JDBC connections. Java applications that are database-intensive use the connection pool to improve performance and better utilize system resources.

Fast Connection Failover (FCF)

Fast Connection Failover provides high availability to FAN integrated clients, such as clients that use JDBC, OCI, or ODP.NET. If you configure the client to use fast connection failover, then the client automatically subscribes to FAN events and can react to database UP and DOWN events. In response, Oracle gives the client a connection to an active instance that provides the requested database service.

Fast Application Notification (FAN)

Applications can use FAN to enable rapid failure detection, balancing of connection pools after failures, and re-balancing of connection pools when failed components are repaired. The FAN notification process uses system events that Oracle publishes when cluster servers become unreachable or if network interfaces fail.

Oracle Notification Services (ONS)

A publish and subscribe service for communicating information about all FAN events.

Single Client Access Name (SCAN)

Oracle Database 11g database clients (or higher) use SCAN to connect to the database. SCAN can resolve to multiple IP addresses, reflecting multiple listeners in the cluster handling public client connections.

Real Application Clusters (RAC)

Option that allows multiple concurrent database instances to share a single physical database.

2 Deployment in Intershop 7 | Intershop Commerce Management

2.1 The JAR files

Oracle required JAR files:

Intershop
Version

Oracle
JAR File

Oracle Home | Download Source

Intershop Location1

Required for
default
JDBC/UCP
handling

Required for
FCF and RAC
support

ICM 7.6 and later     
 

ojdbc7.jar

ORACLE_HOME/jdbc/lib/ojdbc7.jar
or Oracle JDBC downloads
IS_SHARE/system/cartridges/3rd_oracle/release/libyesyes
 

ucp.jar

ORACLE_HOME/ucp/lib/ucp.jar
or Oracle UCP downloads
IS_SHARE/system/cartridges/3rd_oracle/release/libyesyes
 

ons.jar

ORACLE_HOME/opmn/lib/ons.jar
or Oracle UCP downloads

IS_SHARE/system/cartridges/3rd_oracle/release/libnoyes

Intershop 7.5

     

 

ojdbc7.jar

ORACLE_HOME/jdbc/lib/ojdbc7.jar
or Oracle JDBC downloads

IS_SHARE/system/cartridges/3rd_oracle/release/lib

yes

yes

 

ucp.jar

ORACLE_HOME/ucp/lib/ucp.jar
or Oracle UCP downloads

IS_SHARE/system/cartridges/3rd_oracle/release/lib

yes

yes

 

ons.jar

ORACLE_HOME/opmn/lib/ons.jar
or Oracle UCP downloads

IS_SHARE/system/cartridges/3rd_oracle/release/libnoyes
Intershop 7.4 CI     
 

ojdbc6.jar

ORACLE_HOME/jdbc/lib/ojdbc6.jar
or Oracle JDBC downloads

IS_HOME/lib

yesyes
 

ucp.jar

ORACLE_HOME/ucp/lib/ucp.jar
or Oracle UCP downloads

IS_HOME/lib

yesyes
 

ons.jar

ORACLE_HOME/opmn/lib/ons.jar
or Oracle UCP downloads

IS_SHARE/system/cartridges/3rd_oracle/release/libnoyes

1 Intershop versions prior to the Continuous Integration approach required to manually copy these *.jar files to the according location after the server deployment. Since Intershop 7.4 CI this process is automated by the CI setup. For more detailed information please refer to the according CI Setup Guide:

Oracle JAR Files with Intershop 7 Version Matrix:

Intershop
Version

File
Name

Oracle
Version

JAR File Version

Size in
Bytes

SHA1 checksum

ICM 7.6 and later

     
 ucp.jar12c12.1.0.2.0733,729384b4a763188849bfd68f313701ac11dafd1899b
 ojdbc7.jar12c12.1.0.2.03,698,8577c9b5984b2c1e32e7c8cf3331df77f31e89e24c2
 ons.jar12c12.1.0.2.0106,4961aee60b3f7aa581d90f2f18d5e334a1913da52c9

Intershop 7.5

     
 ucp.jar12c12.1.0.1.0583.371

7f21ba5dc542f8b672b312d50106fe045b89f363

 

ojdbc7.jar

12c

12.1.0.1.0

3.397.734a2348e4944956fac05235f7cd5d30bf872afb157
Intershop 7.4 CI     
 

ucp.jar

11gR211.2.0.4 5520b4e492939b477cc9ced90c03bc72710dcaf3
 

ojdbc6.jar

11gR211.2.0.42,739,670

a483a046eee2f404d864a6ff5b09dc0e1be3fe6c

Tools to compute and check MD5 message digest md5sum and determine the Implementation-Version from META-INF/MANIFEST.MF for required JAR files:

  • ucp/ojdbc*.jar:      unzip -p <jarfile> META-INF/MANIFEST.MF | grep Implementation-Version
  • ons.jar:             unzip -p <jarfile> META-INF/MANIFEST.MF | grep Label
  • ucp/ojdbc*/ons.jar:   md5sum <jarfile>

3 Configuration in Intershop 7 | Intershop Commerce Management 7

3.1 orm.properties

This file (IS_SHARE/system/config/cluster/orm.propeties) defines important intershop.jdbc.* properties for Intershop 7 | Intershop Commerce Management 7.

Properties intershop.jdbc.url and intershop.jdbc.rac. :*

Oracle
Server
Type

Property Key

Valid Values

Notes

Single
Instance

 

 

 

 

intershop.jdbc.url

jdbc:oracle:thin:@(DESCRIPTION= \
(ADDRESS=(PROTOCOL=TCP)(HOST= host )(PORT= port )) \
(CONNECT_DATA=(SID= sid )))

SID based

 

 

jdbc:oracle:thin:@(DESCRIPTION= \
(ADDRESS=(PROTOCOL=TCP)(HOST= host )(PORT= port )) \
(CONNECT_DATA=(SERVICE_NAME= service_name )))

SERVICE_NAME based

 

 

jdbc:oracle:thin:@// host : port / service_name

SERVICE_NAME based with
new Easy Connect naming

RAC

 

 

 

 

intershop.jdbc.url
(Oracle 11.2.0. 1.0
and 11.2.0. 2.0)

jdbc:oracle:thin:@(DESCRIPTION= \
(ADDRESS=(PROTOCOL=TCP)(HOST= cluster_alias )(PORT= port )) \
(CONNECT_DATA=(SERVICE_NAME= service_name )))

SCAN access based

 

intershop.jdbc.url
(Oracle 11.2.0. 2.0
and 11.2.0. 3.0)

jdbc:oracle:thin:@// cluster_alias:port/service_name

SCAN access based with
new Easy Connect naming

 

 

jdbc:oracle:thin:@(DESCRIPTION= \
(ADDRESS=(PROTOCOL=TCP)(HOST= rac-node1 )(PORT= port )) \
(ADDRESS=(PROTOCOL=TCP)(HOST= rac-node2 )(PORT= port )) \
... \
(ADDRESS=(PROTOCOL=TCP)(HOST= rac-nodeN )(PORT= port )) \
(LOAD_BALANCE=YES) \
(CONNECT_DATA=(SERVICE_NAME= service_name )))

All addresses based

 

intershop.jdbc.rac.
fastConnectionFailover

true or false

JDBC Fast Connection Failover
(FCF) via remote Oracle Notification
Service (ONS) configuration
via client-side daemon ons.jar

 

intershop.jdbc.rac.
remoteONSConfig

nodes= cluster_alias : ons_port

SCAN access based

 

 

nodes= rac-node1:ons_port,rac-node2:ons_port, ...rac-nodeN:ons_port

All addresses based

Oracle cluster database server tools to determine RAC cluster ONS ports and status required for intershop.jdbc.rac.remoteONSConfig with onsctl commands.

CommandDescription

onsctl help

print help message
onsctl startstart ONS daemon
onsctl stopstop ONS daemon
onsctl pingdetermine status of ONS
onsctl debugdisplay debug information for the ONS daemon
onsctl reconfigreload the ONS configuration without daemon shutdown
onsctl detailedprint a verbose syntax description

Other important intershop.jdbc.* properties:

Property Key
intershop.jdbc.*

Valid
Values

Notes

dataSourceFactory

com.intershop.beehive.core.capi.jdbc.oracle.Oracle Ucp DataSourceFactory

New Oracle UCP with latest
recommended FCF feature.

ucp.ConnectionFactoryClassName

oracle.jdbc.pool.OracleDataSource

Pool data source class.

ucp.ValidateConnectionOnBorrow

true or false

Validation connections, default false.

ucp.MaxStatements

10

Caching SQL statements.

ucp.InactiveConnectionTimeout

30

Controlling stale connections.

serverName

tns_alias , default ISSERVER.world

used for DataSource via JDBC
driver and Oracle Tools support via
ant tasks and SQL*Plus calls from
dbinit and dbmigrate. It maps to
tnsnames.ora, default ISSERVER.world

Variable Expansion

Property values like ${token} are automatically expanded when the configuration file is loaded. Example:
intershop.jdbc.connectionCacheInactivityTimeout = 30
intershop.jdbc.ucp.InactiveConnectionTimeout    = ${intershop.jdbc.connectionCacheInactivityTimeout} 
The result will be:
intershop.jdbc.ucp.InactiveConnectionTimeout    = 30

3.2 tnsname.ora

This file is occated in IS_SHARE/system/config/oracle/tnsname.ora.

Oracle
Server
Type

Property Key

Valid Values

Notes

Single
Instance

 

 

 

 

ISSERVER.world

(DESCRIPTION=
 (ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=port))
 (CONNECT_DATA=(SID=sid))
)

SID based

 

 

(DESCRIPTION=
 (ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=port))
 (CONNECT_DATA=(SERVICE_NAME=service_name))
)

SERVICE_NAME
based

RAC

 

 

 

 

ISSERVER.world

(DESCRIPTION=
 (ADDRESS=(PROTOCOL=TCP)(HOST=cluster_alias)(PORT=port))
 (CONNECT_DATA=(SERVICE_NAME=service_name))
)

SCAN and
SERVICE_NAME
access based

 

 

(DESCRIPTION=
 (ADDRESS=(PROTOCOL=TCP)(HOST=rac-node1)(PORT=port))
 (ADDRESS=(PROTOCOL=TCP)(HOST=rac-node2)(PORT=port))
 ...
 (ADDRESS=(PROTOCOL=TCP)(HOST=rac-nodeN)(PORT=port)
 (LOAD_BALANCE=YES)
 (CONNECT_DATA=(SERVICE_NAME=service_name)
)

All addresses
based

 

 

Disclaimer

The information provided in the Knowledge Base may not be applicable to all systems and situations. Intershop Communications will not be liable to any party for any direct or indirect damages resulting from the use of the Customer Support section of the Intershop Corporate Web site, including, without limitation, any lost profits, business interruption, loss of programs or other data on your information handling system.

Customer Support
Knowledge Base
Product Resources
Support Tickets