X

Learn Tips and Best Practices from the Oracle JDBC Development

Recent Posts

Create, Deploy, and Run Java apps connecting to ATP on Azure (Eclipse)

Java developers are interested in connecting their Java  applications to Oracle Autonomous Transaction Processing (ATP) and Oracle Autonomous Data Warehousing (ADW) and take full advantage of autonomous database capabilities (self-patching, self-tuning, fully managed etc., ). The Java applications can be deployed either on Oracle Cloud or on Microsoft Azure.This blog provides specific instructions to create, deploy, and run the Java applications connecting to ATP with Tomcat on Microsoft Azure using Eclipse as the IDE.  Let us get started with step by step instructions. Pre-requisites (1) Create ATP and Download client credentials: Login to your oracle cloud at cloud.oracle.com and create ATP in a few simple steps. Download the client credentials file (wallet_dbname.zip) from "DB Connection" tab of ATP database that you created. Contents of the zip file are: tnsnames.ora, ojdbc.properties, sqlnet.ora, cwallet.ora, ewallet.p12, truststore.jks, keystore.jks Make sure to check the version of the JDBC driver and JDK and other necessary steps by going through this section.  (2) MS Azure Subscription: In order to deploy the Java web applications on Azure, you need to have signed up for MS Azure (https://azure.microsoft.com/). Note that this blog doesn't discuss or use Oracle's FastConnect.  (3) Eclipse IDE: Make sure you have a working Eclipse IDE with the workspace setup. We have used 'Eclipse Java EE IDE for Web Developers'  in this blog.  (4) Install MS Azure toolkit: With Eclipse IDE, install MS Azure Toolkit that will make it easier to deploy the Java web app on Azure. Follow these steps to Install and sign-in to complete your MS Azure Toolkit setup.  Java webapp  First, create a Java web application using Eclipse that connects to ATP. Deploy the web app on Azure using the MS Azure Toolkit and invoke the servlet and make sure it is working. Follow these three simple steps to get your web application working on Azure.  Create a Java webapp for ATP Deploy a Java webapp on Azure Run a Java webapp  Create a Java webapp for ATP Step 1: Create a webapp:  Click File --> New --> Dynamic Web Project. Choose the name of the webapp (e.g.,ATPWebApp) and click Finish. Step 2: Download JDBC driver (ojdbc8.jar) and ucp.jar:  Download the latest 19.3 ojdbc8-full.tar.gz that includes JDBC driver (ojdbc8.jar) and ucp.jar and add it under <eclipse-workspace>/ATPWebApp/WebContent/WEB-INF/lib. Note to use ojdbc8.jar that is comptible with JDK8.  Note: When using JKS, remove oraclepki.jar, osdt_core.jar, and osdt_cert.jar from the classpath (lib folder).  These additional jars are required only for Oracle Wallets.  Step 3: Unzip client credentials:  Unzip the client credentials (wallet_<dbname>.zip) file to <eclipse-workspace>/ATPWebApp/WebContent/WEB-INF/lib.  Java applications can use either JKS or Oracle Wallets to connect to the ATP database. The ojdbc.properties file is pre-configured for using Oracle Wallets.  When using JKS, add the below properties in ojdbc.properties. Note that the password used for these properties is the one used while downloading the client credentials zip file.  JKS related property in ojdbc.properties: # Connection properties for using JKS.  # Keystore and trustStore passwords are the password used while # downloading the client credentials zip file from console.  oracle.net.ssl_server_dn_match=true javax.net.ssl.trustStore=${TNS_ADMIN}/truststore.jks javax.net.ssl.trustStorePassword=<password_from_console> javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks javax.net.ssl.keyStorePassword=<password_from_console> Step 4: Create a context.xml: Follow these instructions for creating a context.xml with ATP database details. Refer to the sample below.  1. Place the context.xml under <eclipse-workspace>/ATPWebApp/WebContent/META-INF/context.xml 2. Update the database user and password to point to the ATP database that you have created. 3. Make sure the database URL has TNS alias. Check out tnsnames.ora or 'DB Connection' tab for the available services.  4. TNS_ADMIN should be pointed to /home/site/wwwroot/webapps/ROOT/WEB-INF/lib, when you deploy it on the ROOT. If you have chosen a different slot then change the path accordingly. Use KUDU to check the location of the web app after the deployment. (E.g., http://<webappname>.scm.azurewebsites.net)   <Context> <Resource name="tomcat/UCP_atp" auth="Container"    factory="oracle.ucp.jdbc.PoolDataSourceImpl"    type="oracle.ucp.jdbc.PoolDataSource"    description="UCP Pool in Tomcat"    connectionFactoryClassName="oracle.jdbc.pool.OracleDataSource"    minPoolSize="5"    maxPoolSize="50"    initialPoolSize="15"    user="jdbcuser"    password="XXXXXXX"    url="jdbc:oracle:thin:@dbtest_medium?TNS_ADMIN=/home/site/wwwroot/webapps/ROOT/WEB-INF/lib"  /> </Context> Step 5: Create the Servlet: By default, a index.jsp is created. We can use UCPServlet.java that has the code to connect to the database and perform some database operations. Copy the servlet and place it under <eclipse-workspace>/ATPWebApp/src. You must use the datasource name used in context.xml in the UCPServlet. Example., we are using 'UCP_atp'  as the datasource.  Deploy a Java web app on Azure Step 1: Build the Java project Right click on the project and make sure you are using JDK8 as the compiler and 'Build Project'. Make sure there are no compilation errors.  Step 2: Deploy the Java webapp Use Azure Tool Kit to deploy the web app on Azure as shown below. Make sure to login to your Azure account before the deployment.  Create an AppService on Azure as shown below. Choose the name of the app that you want to create. Example., ATPWebApp After it is published on Azure, you can check the 'Azure Activity Log' that says "Published"  Run the web app After the deployment, you can invoke the servlet at http://<webappname>.azurewebsites.net/<servletname> Login to Azure website and under "AppServices" you can your web app. You can start/stop/restart your webapp.  Example., http://atpwebapp.azurewebsites.net/UCPServlet  p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 19.0px Menlo; color: #000000; background-color: #ffffff} span.s1 {font-variant-ligatures: no-common-ligatures} p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 22.0px Menlo; color: #4e9072} p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 22.0px Menlo} p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 22.0px Menlo; min-height: 25.0px} span.s1 {text-decoration: underline} span.s2 {color: #931a68} span.s3 {color: #7e504f} span.s4 {color: #3933ff} span.s5 {color: #000000}

Java developers are interested in connecting their Java  applications to Oracle Autonomous Transaction Processing (ATP) and Oracle Autonomous Data Warehousing (ADW) and take full advantage...

Configuring the Oracle JDBC Security Policy File

Java Security Model Java Security Manager provides additional protection for resources running in a JVM; It uses the Java security policy file to enforce a set of permissions granted to the code base. For example to use the NLS feature, application needs to give java.io.FilePermission "${oracle.jdbc.policy.ORAI18N}", "read"; permission. Please refer to the section below with the header "JDBC driver features and the required permissions" for more details. You must enable the security manager to use the policy file.  One way to enable the security manager using the -Djava.security.manager option. Application servers such as Web Logic Server, Tomcat, JBoss, etc. have different ways to specify the policy file. Please refer to the following link for more details about the Java Security Manager: https://docs.oracle.com/javase/8/docs/technotes/guides/security/spec/security-specTOC.fm.html Policy File Strategy Grant the least permissions as possible. Start with the sample ojbc.policy file, described in the next section. Then run the application. Check the thrown security exception. Add the smallest-grained permission possible in the file that fixes exception. Repeat this until application run without exception. Regularly review security policy files to accommodate any updates in the application. Review all *.policy files. Check the policy files precedence order. Remove unused grants. Add extra permissions to applications or modules that require them, not all applications. Put comments in the file to track  your changes. To debug any issue use –Djava.security.debug=all to troubleshoot security failures.   ojdbc.policy File This is a sample Oracle JDBC Driver Security Policy File. If you enable a SecurityManager, you must grant Oracle JDBC certain permissions. You need to use the Oracle JDBC security policy file in your application such as -Djava.security.policy=ojdbc.policy. This file can be downloaded from the JDBC OTN website. e.g. http://download.oracle.com/otn/utilities_drivers/jdbc/193/ojdbc.policy If you don’t use the policy file, then the JDBC driver won’t work when the security manager is enabled. E.g. you can get an exception like [java] java.security.AccessControlException: access denied (javax.management.MBeanTrustPermission register) When you use the policy file, it protects critical internal resources. No privileged access to external resources. This way one cannot use the Thin driver for a DoS (denial-of-service) attack. The ojdbc.policy file is parameterized. You must define certain system properties to use it.  e.g. -Doracle.jdbc.policy.JDBC_CODE_BASE=$ORACLE_HOME/lib/ojdbc8.jar Here are the few samples of policy file: Sample-1: Application uses a  Thin driver connection and queries a table I have a Select.java which connects to a database and then select from a table (e.g. DUAL) in that database. Here is the code snippet for it: … OracleDataSource ds = new OracleDataSource(); String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myhost)(PORT=myport))(CONNECT_DATA=(SERVICE_NAME=myservicename)))"; ds.setURL(url); … Connection conn = ds.getConnection(); Statement statement = conn.createStatement(); ResultSet rs = statement.executeQuery(“SELECT * FROM dual”); … You can run this sample to use the file using the following command: java -Doracle.jdbc.policy.CLIENT_HOST=myclienthost -Doracle.jdbc.policy.DBMS_HOST=myserverhost -Doracle.jdbc.policy.DBMS_PORT=myport -Doracle.jdbc.policy.JDBC_CODE_BASE=$ORACLE_HOME/lib/ojdbc8.jar -Doracle.jdbc.policy.USER_CODE_BASE=/myhome/application/- -Doracle.jdbc.policy.CONFIG_FILE=$TNS_ADMIN/ojdbc.properties -Djava.security.manager -Djava.security.policy=myojdbc.policy Select Please update above command with your actual host, port, application directory, etc. myojdbc.policy file looks as shown below: grant codeBase "file:${oracle.jdbc.policy.JDBC_CODE_BASE}" { /* Always needed */ permission java.sql.SQLPermission "deregisterDriver", ""; permission java.util.PropertyPermission "user.name", "read"; permission java.util.PropertyPermission "oracle.jdbc.*", "read"; permission java.util.PropertyPermission "database", "read"; permission java.util.PropertyPermission "oracle.net.*", "read"; permission java.util.PropertyPermission "javax.net.ssl.*", "read"; permission java.lang.management.ManagementPermission "control"; permission javax.management.MBeanServerPermission "createMBeanServer"; permission javax.management.MBeanPermission "oracle.jdbc.driver.OracleDiagnosabilityMBean#[com.oracle.jdbc:type=diagnosability,*]", "registerMBean"; permission javax.management.MBeanTrustPermission "register"; permission java.lang.RuntimePermission "getenv.TNS_ADMIN"; permission java.util.PropertyPermission "TNS_ADMIN", "read"; permission java.io.FilePermission "${oracle.jdbc.policy.CONFIG_FILE}", "read"; permission java.lang.RuntimePermission "accessDeclaredMembers"; permission java.util.PropertyPermission "java.util.logging.config.file", "read"; /* Needed only if you use the Thin driver */ permission java.net.SocketPermission "${oracle.jdbc.policy.CLIENT_HOST}", "connect,resolve"; permission java.net.SocketPermission "${oracle.jdbc.policy.DBMS_HOST}:${oracle.jdbc.policy.DBMS_PORT}", "connect,resolve"; /* Allow the JDBC driver to auto-resolve and instantiate the Oracle PKI * Provider (For SSL with Oracle Wallets). Not needed if the Oracle PKI * provider is registered with Java security. */ permission java.util.PropertyPermission "oracle.pki.*", "read"; permission java.security.SecurityPermission "putProviderProperty.OraclePKI"; /* Allow the driver to obtain all key stores from Key Store Service */ permission oracle.security.jps.service.keystore.KeyStoreAccessPermission "stripeName=*,keystoreName=*,alias=*", "read"; }; grant codeBase "file:${oracle.jdbc.policy.USER_CODE_BASE}" { permission java.io.FilePermission "${oracle.jdbc.policy.USER_CODE_BASE}","read,write"; permission java.io.FilePermission "${oracle.jdbc.policy.ORAI18N}", "read"; permission javax.management.MBeanServerPermission "createMBeanServer"; permission java.lang.RuntimePermission "accessDeclaredMembers"; permission java.util.PropertyPermission "oracle.jdbc.*", "read"; /* Needed only if you use the Thin driver */ permission java.net.SocketPermission "${oracle.jdbc.policy.DBMS_HOST}:${oracle.jdbc.policy.DBMS_PORT}", "connect,resolve"; permission java.net.SocketPermission "${oracle.jdbc.policy.CLIENT_HOST}", "connect,resolve"; /* Needed only if you use Key Store Service. Replace the * to limit access. */ permission oracle.security.jps.service.keystore.KeyStoreAccessPermission "stripeName=*,keystoreName=*,alias=*", "read"; }; If you use the Thick driver then, you need to add more permissions in the myojdbc.policy file as shown below: grant codeBase "file:${oracle.jdbc.policy.JDBC_CODE_BASE}" { … /* Needed only if you use the OCI driver */ permission java.lang.RuntimePermission "loadLibrary.ocijdbc19"; } Sample-2: Application uses a Universal Connection Pool and query a table I have a UCPSelect.java which connects to a database and then select from a table (e.g. DUAL) in that database. This time it uses the Universal Connection Pool. Here is the code snippet for it: … PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource(); String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myhost)(PORT=myport))(CONNECT_DATA=(SERVICE_NAME=myservicename)))"; pds.setURL(url); pds.setConnectionFactoryClassName(OracleDataSource.class.getName()); … Connection conn = pds.getConnection(); Statement statement = conn.createStatement(); ResultSet rs = statement.executeQuery(“SELECT * FROM dual”); … You can run this sample to use the file using the following command: java -Doracle.jdbc.policy.CLIENT_HOST=myclienthost -Doracle.jdbc.policy.DBMS_HOST=myserverhost -Doracle.jdbc.policy.DBMS_PORT=myport -Doracle.jdbc.policy.JDBC_CODE_BASE=$ORACLE_HOME/lib/ojdbc8.jar -Doracle.jdbc.policy.CONNECTION_POOL_CODE_BASE=$ORACLE_HOME/lib/ucp.jar -Doracle.jdbc.policy.USER_CODE_BASE=/myhome/application/- -Doracle.jdbc.policy.CONFIG_FILE=$TNS_ADMIN/ojdbc.properties -Djava.security.manager -Djava.security.policy=myojdbc.policy UCPSelect Please update above command with your actual host, port, application directory, etc. You need to add more permissions in the myojdbc.policy file as shown below: /* Needed only if you use connection pool. */ grant codeBase "file:${oracle.jdbc.policy.CONNECTION_POOL_CODE_BASE}" { /* Always needed */ permission java.util.PropertyPermission "oracle.ucp.*", "read"; permission java.util.PropertyPermission "oracle.jdbc.*", "read"; permission java.util.PropertyPermission "oracle.jdbc.fanEnabled", "write"; permission java.util.PropertyPermission "oracle.jdbc.beginRequestAtConnectionCreation", "write"; permission java.util.PropertyPermission "java.util.logging.config.file", "read"; permission java.lang.RuntimePermission "accessDeclaredMembers"; permission java.lang.RuntimePermission "shutdownHooks"; permission java.lang.RuntimePermission "modifyThread"; permission javax.management.MBeanServerPermission "createMBeanServer"; permission javax.management.MBeanPermission "oracle.ucp.admin.UniversalConnectionPoolManagerMBean#-[oracle.ucp.admin:name=UniversalConnectionPoolManagerMBean(*),*]", "registerMBean"; permission javax.management.MBeanPermission "oracle.ucp.admin.JDBCUniversalConnectionPoolMBeanImpl#-[oracle.ucp.admin.UniversalConnectionPoolMBean:name=*]", "registerMBean"; /* Needed only if you use the Thin driver */ permission java.net.SocketPermission "${oracle.jdbc.policy.CLIENT_HOST}", "connect,resolve"; permission java.net.SocketPermission "${oracle.jdbc.policy.DBMS_HOST}:${oracle.jdbc.policy.DBMS_PORT}", "connect,resolve"; }; The JDBC driver features and the required permissions Only define the system properties required by the JDBC features you use. Many permissions based on a feature in use such as DMS, XA, XDB, AQ, NLS, etc. Here is the table showing features and related permissions to grant in the policy file for the ojdbc??.jar codebase: Feature Permissions Remark NLS java.io.FilePermission "${oracle.jdbc.policy.ORAI18N}", "read"; Put orai18n.jar in your classpath AQ or DCN java.net.SocketPermission "${oracle.jdbc.policy.DBMS_HOST}", "accept";   DMS java.util.PropertyPermission "oracle.dms.console.DMSConsole", "read"; java.util.PropertyPermission "oracle.dms.mount", "read"; java.util.PropertyPermission "oracle.dms.property.file", "read"; java.util.PropertyPermission "oracle.dms.clock", "read"; java.util.PropertyPermission "oracle.dms.clock.units", "read"; java.util.PropertyPermission "oracle.dms.publisher.classes", "read";   XA java.util.PropertyPermission "oracle.jserver.version", "read";   XML java.util.PropertyPermission "oracle.xdkjava.compatibility.version", "read";   Fast Connection Failover oracle.ons.CreatePermission "ONSUser"; oracle.ons.SubscribePermission "ONSUser"; java.io.FilePermission "${oracle.jdbc.policy.OPMN_CONFIG}", "read"; java.util.PropertyPermission "oracle.ons.*", "read"; java.net.SocketPermission "${oracle.jdbc.policy.REMOTE_ONS_HOST1}:${oracle.jdbc.policy.REMOTE_ONS_PORT1}", "connect,resolve"; java.net.SocketPermission "localhost", "connect,resolve"; Remote ONS host:port   The sample ojdbc.policy file contains comments for permissions needed for each feature and codebase. Please read it and make the necessary changes for your application requirement. There may be a small performance penalty when you use the policy file. It only applies when an application attempts some activity that requires permission checks. Most operations that require permission checks are expensive operations (IO, Network access, etc.) so the overhead of security checks will be a pretty low percentage of total runtime. Benchmark your application to determine performance overhead. Resources Policy File Syntax: http://docs.oracle.com/javase/8/docs/technotes/guides/security/PolicyFiles.html#FileSyntax Permissions in JDK: http://docs.oracle.com/javase/8/docs/technotes/guides/security/permissions.html JDBC Download: http://www.oracle.com/technetwork/database/features/jdbc/jdbc-ucp-122-3110062.html

Java Security Model Java Security Manager provides additional protection for resources running in a JVM; It uses the Java security policy file to enforce a set of permissions granted to the code base....

What's New in 19c and 18c JDBC and UCP ?

JDBC and UCP New Features in Oracle Database 19c and 18c Oracle Database 19c and 18c JDBC drivers and Java connection pool (UCP) introduce several new features in the performance, high availability, security, and scalability areas. New connection and security features were added to simplify connecting to Autonomous Transaction Processing (ATP) and Autonomous Data Warehousing (ADW). This blog gives you a summary of these features. Refer to the JDBC Developer Guide and UCP Developer Guide for more details. Quick Summary: Connection: Easy Connect Plus for easier TCPS connections and passing connection properties (19c only); new ojdbc.properties file to set connection properties; ways for setting TNS_ADMIN; setting server's domain name (DN) cert as a connection property; and support of new wallet property (my_wallet_directory) Performance: Reactive Streams Ingest (RSI) for streaming data into the Oracle Database (19c only); Oracle connection manager (CMAN) in traffic director mode (CMAN-TDM) Scalability: Oracle RAC data affinity; and shard routing APIs for mid-tiers High Availability: Transparent Application Continuity (TAC); AC support in DRCP; and AC support for legacy Oracle JDBC types implemented as concrete Java classes Security: Automatic Provider Resolution (OraclePKIProvider); support for Key Store Service (KSS); and HTTPS proxy support Data types: Accessing PL/SQL associative arrays; Oracle REF CURSOR as IN bind parameter; and JSON datatype validation Connectivity Enhancements: Easy Connect Plus The traditional Easy Connect string had limited capabilities and allowed only TCP connections. In Oracle Database 19c release, the Easy Connect Plus enhances the connection string to be more powerful and self-sufficient for TCPS connections and multiple hosts etc.,The connection string is extended to accept multiple connection parameters through name-value pairs. Easy Connect Plus can now be used for: specifying multiple hosts and ports TCPS connections and can pass wallets/JKS related connection properties passing connection properties  Easy Connect Plus for specifying the multiple hosts and ports.  Long Form Connection URL jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=salesserver1)(PORT=1521))(ADDRESS=(PROTOCOL=tcp)(HOST=salesserver2)(PORT=1522))(ADDRESS=(PROTOCOL=tcp)(HOST=salesserver3)(PORT=1522)))(CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))) Easy Connect Plus: jdbc:oracle:thin:@tcp://salesserver1:1521, salesserver2, salesserver3:1522/sales.us.example.com Easy Connect Plus for specifying the connection properties as name-value pairs.  jdbc:oracle:thin:@tcp://myorclhostname:1521/myorclservicename? oracle.jdbc.implicitStatementCacheSize=100 New connection properties file (ojdbc.properties) Starting with Oracle Database Release 18c, the JDBC driver supports a new file ojdbc.properties that makes it easier to pass connection properties to Java applications. Oracle Autonomous Data Warehouse (ADW) and Autonomous Transaction Processing (ATP) include ojdbc.properties file as part of the client credentials download that is pre-populated with the required connection property. The JDBC driver handles the property files as follows: The driver attempts to load the default file i.e., ojdbc.properties from the default location: $TNS_ADMIN where the tnsnames.ora resides. TNS_ADMIN can be set as a Java system property, as an environment variable or as part of the connection URL. For other non-default ways of setting ojdbc.properties  file, check out Appendix [1].  Multiple ways to set TNS_ADMIN In the older releases, TNS_ADMIN was supported as a system property (oracle.net.tns_admin). With Oracle Database 18c JDBC drivers, TNS_ADMIN can be set as an environment variable and can also be appended to the connection URL. jdbc:oracle:thin:@//myhost:1521/orcl?TNS_ADMIN=/home/oracle/network/admin/ Setting Server's Domain Name (DN) cert as a connection property In the older releases, the server's domain name (DN) cert has to be set as part of the connection URL and also enabled through a connection property. Starting with Oracle Database 18c, the JDBC driver can use a connection property to set the server's DN cert. When the certificate is set as part of the connection URL or through a connection property then there is no need to set the additional property oracle.net.ssl_server_cert_dn_match=true. oracle.net.ssl_server_cert_dn="CN=test.us1.oracletest.com,OU=ST,O=Oracle,ST=California,C=US" Support of new property (my_wallet_directory) for wallets Starting with Oracle Database Release 18c, the JDBC driver supports a new property my_wallet_directory for specifying the location of the wallets. You can add this property in security section of the connection URL or in the properties file or directly in the code. The wallet location may contain environment variables and ${identifier} is supported for environment variables. A sample connection URL with this property is as shown below. jdbc:oracle:thin:@dbaccess where dbaccess is defined in tnsnames.ora like this: dbaccess = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(Host=hostname)(Port=1522))(CONNECT_DATA=(SERVICE_NAME=myservicename))(Security=(my_wallet_directory=$TNS_ADMIN/jnetadmin_c/))) Performance Features Oracle Connection Manager  (CMAN) in Traffic Director Mode (CMAN-TDM) Starting with Oracle Database 18c, a new capability called Connection Manager in traffic director mode (CMAN-TDM); it is a database proxy which is placed between the database clients (i.e., JDBC, ODP.net, OCI) and the database instances. The database operations  (TTC messages) sent from the clients are intercepted and parsed by CMAN-TDM then routed to the appropriate database.  CMAN-TDM transfers the results from the databases to the clients (TTC responses). Scalability Features Oracle RAC data affinity The Java connection pool (UCP) in Oracle database release 18.3, supports Oracle RAC Data Affinity. When the data affinity is enabled on the Oracle RAC database, the tables are partitioned in such a way that a particular partition or subset of rows for a table is affinitized to a particular Oracle RAC database instance. The affinity leads to higher performance and scalability for the applications due to improved cache locality and reduced inter-node synchronization and block pings among the RAC instances. Shard routing APIs for mid-tiers The Java connection pool (UCP) introduces a mid-tier shard routing feature. Through this capability, applications may couple a mid-tier for each data center or cloud and route client requests directly to the relevant mid-tier based on the shard that contains the relevant data. Java applications use the mid-tier routing API getShardInfoForKey(shardKey, superShardKey) method of the OracleShardRoutingCache class.  For more details refer to the Mid-tier Routing using UCP blog. High Availability Features Oracle Connection Manager  (CMAN) in Traffic Director Mode (CMAN-TDM) CMAN-TDM add transparency to high availability by hiding the planned/unplanned outages as well as the relocation of Pluggable Databases (PDBs) from one root or container database (a.k.a. CDB) to the other.  CMAN-TDM  automatically detects, redirects, and re-establishes the database connections in such scenarios. Transparent Application Continuity (TAC) Application Continuity (AC) is a feature introduced in 12.1 that masks database outages both planned and unplanned to the application and improves the end user's experience. A database request is a unit of work that is demarcated by the transaction boundaries or the check-out and check-in of a connection from a connection pool. AC needs to be enabled on both the server and client sides. The clients record the interactions with the database then replay the in-flight transaction upon unplanned outages.  AC requires the use of JDBC replay datasource (oracle.jdbc.replay.OracleDataSourceImpl). In an effort to minimize the application changes and making AC more transparent, the 19.3 JDBC driver implicitly adds beginRequest on each new connection created using a replay datasource and when FAILOVER_TYPE on the database service is set to AUTO. If you want to turn off this feature explicitly, you can set the value of the Java system property oracle.jdbc.beginRequestAtConnectionCreation to false. The default value of this property is true. As TAC detects and injects implicit request boundaries automatically, this feature should be used with caution for applications that change server session states during a request. The JDBC Thin driver provides the oracle.jdbc.enableImplicitRequests property to turn off implicit requests, if needed. This property can be set at the system level, which applies to all connections, or at the connection level, which applies to a particular connection. By default, the value of this property is true, which means that support for implicit request is enabled. AC support for concrete classes The 18.3 JDBC driver supports the following concrete classes with Application Continuity: oracle.sql.CLOB, oracle.sql.NCLOB, oracle.sql.BLOB, oracle.sql.BFILE, oracle.sql.STRUCT, oracle.sql.REF, oracle.sql.ARRAY AC support for DRCP  The 18.3 JDBC driver supports Application Continuity when Database Resident Connection Pooling (DRCP) is enabled on the server side. For using Application Continuity with DRCP, you must configure an application service to a server that uses DRCP. The following connection string shows how to configure AC with DRCP: String url = "jdbc:oracle:thin:@(DESCRIPTION = (TRANSPORT_CONNECT_TIMEOUT=3000) (RETRY_COUNT=20)(RETRY_DELAY=3)(FAILOVER=ON) (ADDRESS_LIST =(ADDRESS=(PROTOCOL=tcp) (HOST=CLOUD-SCANVIP.example.com)(PORT=5221)) (CONNECT_DATA=(SERVICE_NAME=ac-service(SERVER=POOLED)))"; Security Features Automatic Provider Resolution The 18.3 JDBC driver resolves the provider as long as the provider implementation is on the CLASSPATH eliminating the need to register the provider. For example., If the oraclepki.jar file is on the CLASSPATH, then the JDBC driver automatically loads the OraclePKIProvider and will be able to establish the connection. Similarly, if the oracle.net.wallet_location connection property is set, the JDBC driver attempts to automatically load the Oracle PKI provider. The following key store types map to a known provider: SSO: oracle.security.pki.OraclePKIProvider KSS:oracle.security.jps.internal.keystore.provider.FarmKeyStoreProvider Support for Key Store Service (KSS) The 18.3 JDBC driver supports Key Store Service (KSS). So, if you have configured a Key Store Service in a WebLogic server, then JDBC applications can now integrate with the existing Key Store Service configuration. The driver can load the key stores that are managed by the Key Store Service. If the value of the javax.net.ssl.keyStore property or the javax.net.ssl.trustStore property is a URI with kss:// scheme, then the driver loads the key store from Key Store Service. For permission-based protection, permission needs to be granted to access the keystore. Support for HTTPS Proxy Configuration The 18.3 JDBC driver supports HTTPS proxy configuration for TCPS connections. HTTPS proxy comes in handy in accessing the public cloud database service through internal corporate network without the requirement to open an outbound port on a client side firewall. Refer to the connection string that configures HTTPS proxy. (DESCRIPTION=(ADDRESS=(HTTPS_PROXY=sales-proxy)(HTTPS_PROXY_PORT=8080)(PROTOCOL=TCPS)(HOST=sales2-svr)(PORT=443))(CONNECT_DATA=(SERVICE_NAME=sales.us.example.com)))   Data Types Accessing PL/SQL Associative Arrays The 18.3 JDBC driver supports accessing both the keys (indexes) and values of associative arrays and also supports object types associative arrays. The following methods can be used for the new functionality. Array createOracleArray(String arrayTypeName,Object elements) throws SQLException ARRAY createARRAY(String typeName,Object elements)throws SQLException Oracle REF CURSOR Type The 18.3 JDBC drivers support REF CURSOR as IN bind variables. Stored procedures can accept or return cursor variables of the REF CURSOR type. Refer to RefCursorInSample.java for reference. JSON Datatype verification The 18.3 JDBC driver has a new method isColumnJSON() available in oracle.jdbc.OracleResultSetMetaData to verify whether a column returned in the ResultSet is a JSON column or not. Appendix  [1] There are other variations of the property file supported. These are listed for the reference. (a) ojdbc_<tnsalias>.properties file: If the TNS alias is used in the connection then the TNS alias can be appended to the property file name. Example;., ojdbc_orcl.properties. If both ojdbc.properties and ojdbc_<tnsalias>.properties files are present then ojdbc_<tnsalias>.properties takes the precedence. (b) Custom properties file: The property file can have any other name and can be placed at any location. Use the property oracle.jdbc.config.file or set the connection property OracleConnection.CONNECTION_PROPERTY_TNS_ADMIN. There can be multiple property files. Multiple files can be mentioned as a comma delimited list: "fileOne, fileTwo, fileThree". Higher precedence is given to the file that appear last in the list. Example., fileThree would override fileTwo's definition of the property. Likewise, fileTwo's values can override fileOne's.

JDBC and UCP New Features in Oracle Database 19c and 18c Oracle Database 19c and 18c JDBC drivers and Java connection pool (UCP) introduce several new features in the performance, high availability,...

Boost the Java apps performance through Client Side ResultSet Cache

What is Client Side ResultSet Cache? Java applications can reuse the result sets of frequent/identical queries, cached in the driver's memory on the client side. This feature improves the performance by saving network roundtrips to the database and saving server-side resources as the query results are cached on the client side and not re-executed with every invocation. Also, the cache is shared across multiple connections with the same attributes (same user, same session settings, etc.,). What happens to the cache if the data changes on the server side? Client Side ResultSet Cache feature includes a sophisticated cache invalidation mechanism making sure that results of the query are in sync with the database. A cached result is automatically invalidated whenever the data changes on the server or when the session attributes change or when a local transaction that affects the data is started.    How do you enable Client Side ResultSet Cache? Though, the client side resultset cache is enabled by default in JDBC Thin driver version 18.3, it does require some settings on the server side for it take affect. Set a few parameters related to cache size (memory that you want to allocate for the cache) and cache lag (maximum time in milliseconds before the JDBC driver makes a round trip to get any database changes related to the query) on the server side to enable this feature. Steps 1-2 show the required settings. Refer to server side documention for using Client Result Cache for more details.   If you want to disable this feature then, set oracle.jdbc.enableQueryResultCache to false without the need to remove any SQL hints from the queries.This can be set either as a connection property or as a system property. Refer to JDBC Developer's guide for more details on this feature. Step 1: Make sure that the database server is configured to support resultset caching. Add the following configurations in init.ora and restart the database server. CLIENT_RESULT_CACHE_SIZE =  100MB -  Set to client result set cache size CLIENT_RESULT_CACHE_LAG = 1000 - Specifies the max time in milliseconds that the cache can lag Step 2: Add SQL hints to cache the result set for the required queries. SQL hints can be present at three levels. Query level has the highest precedence, later the table annotations and then the server initialization parameter. Query Level :  Enable or Disable client result cache for a single query using the SQL hints /*+ result_cache */ or /*+ no_result_cache */. This is the recommended way to identify the queries in the applications that are worth being cached. SELECT /*+ result_cache */ first_name, last_name from employees where employee_id < 150 Table Annotation: You can also identify the read-mostly tables on the server using annotations too. You can use the RESULT_CACHE  MODE (FORCE) clause to enable the client side result set cache while creating or altering the table. To disable it, use RESULT_CACHE MODE (DEFAULT) ALTER TABLE dept result_cache (MODE FORCE); Server side:  The session parameter RESULT_CACHE_MODE = (FORCE or MANUAL), enables or disables client side resultset cache for all queries for the database session.  This parameter must be added in init.ora. The recommendation is to use MANUAL. How to check if the Client Side ResultSet Cache is enabled and working ? Use the method below to find if the query results are retrieved from the cache or from the database. try (ResultSet rs = statement.executeQuery("select /*+ result_cache */ empno, ename from emp")) { System.out.println("isFromResultSetCache:" + rs.isFromResultSetCache()); }        

What is Client Side ResultSet Cache? Java applications can reuse the result sets of frequent/identical queries, cached in the driver's memory on the client side. This feature improves the performance...

Dev2Dev

Mid-tier Routing using Oracle Universal Connection Pool (UCP)

Oracle Universal Connection Pool (UCP) adds a new feature called "Mid-tier Routing" in the Oracle Database 18c release. The feature is targeted for customers using Oracle Sharded Database. Currently mid-tier connection pools route database requests to specific shards. This leads to a situation where each mid-tier connection pool establishes connections to each shard. This will create too many connections to the Database. The problem can be solved by affinitizing mid-tiers with shards. In such scenario it is desirable to have dedicated mid-tier (web server/application server) for each data center or cloud, and to have client requests routed directly to the right mid-tier where the shard containing the client data (corresponding to the client shard key) resides. A common term used for this kind of setup is “swim lanes”, where each lane is a dedicated stack, from web server to application server all the way to the Database. UCP solves the above problem by providing mid-tier routing API which can be used to route the client requests to right mid-tier. The mid-tier API is exposed by UCP’s OracleShardRoutingCache class . An instance of this class represents UCP's internal shard routing cache which can be created by providing connection properties such as user, password and url. The routing cache connects to the sharding catalog to retrieve the key to shard mapping topology and stores it in its cache. The routing cache is used by mid-tier API getShardInfoForKey(shardingKey,superShardingKey) which  accepts sharding key as input and returns a set of ShardInfo instances mapped to the input sharding key. The ShardInfo instance encapsulates a unique shard name and priority of the shard. The application using the mid-tier API can map the returned unique shard name value to a mid-tier which has connections to a specific shard. The routing cache is automatically refreshed/updated on chunk move/split by subscribing to respective ONS events. Public APIs/Interfaces/classes for Mid-tier Routing: /** * This class extends the UCP's internal shard routing cache and makes the basic * routing cache feature available to the WLS /Mid tier routers or Load * Balancers. */ public class OracleShardRoutingCache extends ShardRoutingCache { /** * Creates an instance of a Shard Routing cache that can be used by a mid-tier * that needs to do shard-based routing. Once this cache is created, * getShardInfoForKey can be used for every Sharding Key to know which shard * needs to be used. * * @param dataSourceProps * Required datasource properties to create a connection to shard * catalog. * @throws UniversalConnectionPoolException * * @see getShardInfoForKey */ public OracleShardRoutingCache(Properties dataSourceProps) throws UniversalConnectionPoolException { } /** * Gets the information of each sharded database that maps to the sharding * keys. * * @param key * sharding key for which the Sharded Database information is * requested * @param superKey * super sharding key for which the Sharded Database information is * requested * @return set of ShardInfo objects each of which contains information about * the shard name and the priority of the shard for the corresponding * shard keys. */ public Set<ShardInfo> getShardInfoForKey(OracleShardingKey key, OracleShardingKey superKey) { } } /** * When the routing cache is queried for shard information corresponding to * particular sharding keys, a set of objects of this type is returned. Each * such object encapsulates all the required information about one of the shards * that corresponds to the sharding keys. */ public interface ShardInfo { /** * Returns the encapsulated shard name. * * @return shard name */ String getName(); /** * Returns the encapsulated shard's priority. * * @return shard priority */ int getPriority(); } Below is a simple code example which illustrates the usage of UCP’s mid-tier routing API.   import java.sql.SQLException; import java.util.Properties; import java.util.Random; import java.util.Set; import oracle.jdbc.OracleShardingKey; import oracle.jdbc.OracleType; import oracle.ucp.UniversalConnectionPoolException; import oracle.ucp.routing.ShardInfo; import oracle.ucp.routing.oracle.OracleShardRoutingCache; /** * The code example illustrates the usage of UCP's mid-tier routing feature. The * API accepts sharding key as input and returns the set of ShardInfo instances * mapped to the sharding key. The ShardInfo instance encapsulates unique shard * name and priority. The unique shard name then can be mapped to a specific * mid-tier server which connects to a specific shard. * */ public class MidtierShardingExample { private static String user = "testuser1"; private static String password = "testuser1"; // catalog DB URL private static String url = "jdbc:oracle:thin:@//hostName:1521/catalogServiceName"; private static String region = "regionName"; public static void main(String args[]) throws Exception { testMidTierRouting(); } static void testMidTierRouting() throws UniversalConnectionPoolException, SQLException { Properties dbConnectProperties = new Properties(); dbConnectProperties.setProperty(OracleShardRoutingCache.USER, user); dbConnectProperties.setProperty(OracleShardRoutingCache.PASSWORD, password); // Mid-tier routing API accepts catalog DB URL dbConnectProperties.setProperty(OracleShardRoutingCache.URL, url); // Region is required to get the ONS config dbConnectProperties.setProperty(OracleShardRoutingCache.REGION, region); OracleShardRoutingCache routingCache = new OracleShardRoutingCache( dbConnectProperties); final int COUNT = 10; Random random = new Random(); for (int i = 0; i < COUNT; i++) { int key = random.nextInt(); OracleShardingKey shardKey = routingCache.getShardingKeyBuilder() .subkey(key, OracleType.NUMBER).build(); OracleShardingKey superShardKey = null; Set<ShardInfo> shardInfoSet = routingCache.getShardInfoForKey(shardKey, superShardKey); for (ShardInfo shardInfo : shardInfoSet) { System.out.println("Sharding Key=" + key + " Shard Name=" + shardInfo.getName() + " Priority=" + shardInfo.getPriority()); } } } }

Oracle Universal Connection Pool (UCP) adds a new feature called "Mid-tier Routing" in the Oracle Database 18c release. The feature is targeted for customers using Oracle Sharded Database.Currently...

Oracle Sharding with the Universal Connection Pool in Frameworks (Spring, Hibernate, MyBatis, and so on)

This article illustrates the steps to use Oracle Sharding with the Oracle Universal Connection Pool (UCP) in MyBatis-Spring framework. However, the same technique can be used with other frameworks too. Below are two key steps:   1.  Create a custom UCP data source and override getConnection(user,password, labels) method. 2.  Pass the sharding key information to the datasource using a shared thread-local object.   Assume there is a simple sharded table customer with two columns “custid” and “name” where “custid” is the sharding key column.   1. Create a custom UCP datasource by extending oracle.ucp.jdbc.PoolDataSourceImpl class and override the getConnection(user, password, labels) method like below. The overridden getConnection method gets the sharding key value from shared thread-local variable and uses it to get the connection from UCP.   import java.sql.Connection; import java.sql.SQLException; import java.util.Properties; import oracle.jdbc.OracleShardingKey; import oracle.jdbc.OracleType; import oracle.ucp.jdbc.PoolDataSourceImpl; public class ShardingDataSourceImpl extends PoolDataSourceImpl { @Override public Connection getConnection(String username, String password, Properties labels) throws SQLException { String shardingkey = ShardingController.getShardingKeyContext().get(); OracleShardingKey shardKey = this.createShardingKeyBuilder() .subkey(shardingkey, OracleType.VARCHAR2).build(); return this.createConnectionBuilder().user(username).password(password) .labels(labels).shardingKey(shardKey).build(); } } 2. Create a DAO class which sets the value of sharding key on the shared thread-local object before calling a method of sqlSession class which internally calls getConnection() method.   public class CustomerDaoImpl implements CustomerDao { private SqlSession sqlSession; public void setSqlSession(SqlSession sqlSession) { this.sqlSession = sqlSession; } public Customer getCustomerDetails(String customerId) { ShardingController.getShardingKeyContext().set(customerId); return (Customer) sqlSession.selectOne("CustomerMapper.getCustomerDetails", customerId); } } /* * This class uses a thread-local variable to pass sharding key information from * application to UCP. The application code sets the value of sharding key on * the thread-local object, which is being read by UCP’s overridden * getConnection() method. */ public class ShardingController { private static ThreadLocal<String> shardingKeyContext = new ThreadLocal<String>(); public static ThreadLocal<String> getShardingKeyContext() { return shardingKeyContext; } } The spring XML configuration file look like below:     The CustomerDaoImpl class uses sqlSessionTemplate which internally calls the Universal Connection Pool (UCP)  APIs for connection check-outs and check-ins. The main application code looks like the following.    import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; public class TestUCPShardingWithMyBatisSpring { public static void main(String args[]) throws Exception { ApplicationContext ctx = new ClassPathXmlApplicationContext( "spring-config.xml"); String customerId = “cust123”; CustomerDaoImpl dao = (CustomerDaoImpl) ctx.getBean("customerDao"); Customer customer = dao.getCustomerDetails(customerId); System.out.println("Customer ID = " + customer.getCustId()); System.out.println("Customer Name = " + customer.getName()); } }

This article illustrates the steps to use Oracle Sharding with the Oracle Universal Connection Pool (UCP) in MyBatis-Spring framework. However, the same technique can be used with other frameworks...

Java Scalability with Sharded Database

Scalability is very important for Java applications as the number of users, number of transactions, and data are increasing exponentially. Sharding distributes and replicates the data across a pool of databases that do not share hardware or software. Each individual database is known as a shard. Java applications can linearly scale up or scale down by adding databases (shard) to the pool or by removing databases (shards) from the pool. In addition to achieving linear scalability, sharding has many other benefits. It provides extreme data availability by eliminating single point of failure and isolating the faulted shards from other working shards. It makes cloud deployment easier as the size of the shard is small. Sharding also makes data sovereignty and data proximity possible by locating different parts of data in different countries or regions. Sharding uses horizontal partitioning in which shard contains the table with the same columns but a different subset of rows. This partitioning is based on a sharding key. Choosing a good partitioning strategy is very important for sharding. A good sharding key will uniformly distribute the data across all shards so that DMLs and queries have fair distribution without creating any hot shards. Usually the primary key of the table that uniquely identifies the users or objects of the applications qualifies as a sharding key. There can be multiple sharding keys too. Example, Customer_ID could be the sharding key and REGION could be the super sharding key. The diagram below shows how the data is distributed into three shards but together they are represented as a single logical database. How to make your Java applications shard aware? Java applications require sharding key(s) or super sharding key (if it exists) for establishing a connection to a particular shard. Once the session is established to a shard, all SQL queries and DMLs are executed in the scope of the given shard. The JDK9 standard Sharding APIs accept sharding key and super sharding key to develop shard aware Java applications. For example, Oracle JDBC driver and the Universal Connection Pool (UCP) from 12.2.0.1 have been enhanced to accept sharding key and super sharding key while connecting to a sharded database. Looking at Oracle Sharding as an example. Oracle Database v12.2.0.1 supports sharding through Global Data Services (GDS). There are Shard Directors or GSM listeners that route connections to the appropriate shards based on the sharding key passed during a connection request. It maintains an up-to-date shard topology (sharding key range mappings stored in a particular shard). How does UCP improve performance in a shard aware Java applications? The Universal Connection Pool (UCP) caches the shard topology and acts as a shard director. This way, UCP improves the performance of a shard aware Java applications by getting the fast path to shards saving the additional hop to the shard director. To begin with, UCP requires only one successful connection to the shard to pull the shard topology and cache it on the client side. Later, when connections are requested by passing the sharding key, UCP makes a lookup in its cached topology to know which shard the key belongs to and returns a connection to the correct shard. This is known as the "Direct Routing". How to aggregate the data from all shards? There are scenarios when the application needs to aggregate the data across all the shards. In such a scenario, cross shard queries can be executed by connecting to the Shard Coordinator also known as Shard Catalog. The Shard Coordinator or Shard Catalog allows the users to submit SQL statements without a sharding key. The coordinator’s SQL compiler analyzes and rewrites the query into query fragments  that are sent and executed by the multiple shards. After the query processing the data is aggregated by the coordinator. This is known as "Proxy Routing". JDBC APIs for Sharding: The Sharding APIs require the sharding key to be passed for establishing a connection to the database. The following steps are required in getting a connection to a sharded database: (1) Build the sharding key: Make sure to pass the sharding key value and the sharding data type while building the sharding key. You can connect to a sharded database through SQL Developer or SQLPlus and get a list of sharding keys for testing purposes. (2) Build the super sharding key: Super sharding key is optional. If your sharded database is not using super sharding key then you can ignore this step. (3) Getting a connection to the shard: After the sharding key and super sharding key are built, they need to be passed to get a successful connection to the shard that contains the data pertinent to the sharding key. Refer to JDBCShardingSample.java for a quick code sample to test the sharded database. Note: JDBC driver 12.2.0.1 doesn't support JDK9 standard Sharding APIs and the plan is to support it in the future database release. Instead, the Oracle JDBC driver uses oracle.jdbc.OracleShardingKey for supporting sharding APIs for applications using JDK8 and JDK9. Refer to the code snippet that shows how to establish a connection to a sharded database using Oracle JDBC driver. import oracle.jdbc.OracleShardingKey; import oracle.jdbc.OracleType; import oracle.jdbc.pool.OracleDataSource; OracleDataSource ods = new OracleDataSource(); ods.setURL(url); ods.setUser(user); ods.setPassword(pwd); // 1. Build the Sharding Key Date shardingKeyVal = new java.sql.Date(0L); OracleShardingKey shardKey = ods.createShardingKeyBuilder() .subkey(shardingKeyVal, OracleType.DATE) .build(); // 2. Build the Super Sharding Key (Optional) OracleShardingKey superShardKey = ods.createShardingKeyBuilder() .subkey("Customer_Location_US”,oracle.jdbc.OracleType.VARCHAR2) .build(); // 3. Get a connection from the specific shard Connection conn = ods.createConnectionBuilder() .shardingKey(shardKey) .suerShardingKey(superShardKey) .build(); UCP APIs for Sharding: UCP Sharding APIs require the sharding key to be passed for establishing a connection to the sharded database. Refer to UCPShardingSample.java for for a quick code sample to test the sharded database. Refer to the code snippet that shows how to establish a connection to a sharded database using Oracle Universal Connection Pool (UCP). import oracle.jdbc.OracleShardingKey; import oracle.jdbc.OracleType; import oracle.ucp.jdbc.PoolDataSourceFactory; import oracle.ucp.jdbc.PoolDataSource; PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource(); pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource"); pds.setURL(DB_URL); pds.setUser(DB_USER); pds.setPassword(DB_PASSWORD); pds.setConnectionPoolName("UCP_POOL"); pds.setInitialPoolSize(5); //Initial connections when the pool is created pds.setMinPoolSize(5); // Minimum number of connections pds.setMaxPoolSize(20); // Set the maximum number of connections // 1. Build the Sharding Key String email= "test@test.com"; OracleShardingKey shardKey =  pds.createShardingKeyBuilder()                    .subkey(email, OracleType.VARCHAR2)                     .build(); // 2. Build the Super Sharding Key (Optional) OracleShardingKey superShardKey = pds.createShardingKeyBuilder() .subkey("Location_US”,oracle.jdbc.OracleType.VARCHAR2) .build(); // 3. Get a connection to the specific shard Connection conn = pds.createConnectionBuilder() .shardingKey(shardKey) .suerShardingKey(superShardKey) .build(); Appendix: Refer to JDBC Developer's Guide and UCP Developer's Guide for Sharding Glossary, supported data types for sharding key, and more details about the new Sharding APIs.

Scalability is very important for Java applications as the number of users, number of transactions, and data are increasing exponentially. Sharding distributes and replicates the data across a pool of...

SSL Connection to Oracle DB using JDBC, TLSv1.2, JKS or Oracle Wallets (12.2 and lower)

Brief Introduction to SSL The Oracle database product supports SSL/TLS connections in its standard edition (since 12c). The Secure Sockets Layer (SSL) protocol provides network-level authentication, data encryption, and data integrity. When a network connection over SSL is initiated, the client and server perform a handshake that includes:     • Negotiating a cipher suite for encryption, data integrity, and authentication     • Authenticating the client by validating its certificate     • Authenticating the server by verifying that it’s Distinguished Name (DN) is expected     • Client and server exchange key information using public key cryptography. To establish an SSL connection the Oracle database sends its certificate, which is stored in a wallet. Therefore, on the server the configuration requires a wallet and on the client, the JDBC thin driver can use different formats to store the client’s certificate and key: JKS, Wallet or PKCS12. In this blog, we will provide clear steps to establish an SSL connection over TLSv1.2 using the JDBC thin driver with either JKS files or a wallet. Note: One of the Oracle Cloud offerings, Exadata Express Cloud Service (EECS) mandates TLSv1.2. SSL Connection using TLSv1.2 SSL Connection using JKS SSL Connection using Oracle Wallets SSL connection using TLSv1.2 JDK 7 and JDK 8 releases support TLSv1.2 protocol. The other protocols such as TLSv1.1, TLSv1, SSLv3, and SSLv2 have security vulnerabilities and the recommendation is to use the latest standard version TLSv1.2 and use more secure SSL cipher suites. Follow these pre-requisites below to use TLSv1.2.  1: Make sure you have the correct JDBC Thin driver If you are using ojdbc8.jar from 12.2.0.1 version then, you are all set. But, if you are using 12.1.0.2 JDBC driver then you need to either download the 12.1.0.2 patched driver or apply the patch for the bug 19030178 that allows TLSv1.2. Note that the patch allows TLSv1.2 but doesn't enable it by default. So, you must set the property oracle.net.ssl_version=1.2. This property can be set either as a system property (using -D) or through the datasource properties.  2: JDK version and JCE files If you are NOT using JDK9 or JDK8u162 then download the JCE Unlimited Strength Jurisdiction Policy Files. Refer to README for installation notes. Without the Java Cryptography Extension (JCE) files, the strong cipher suites (for example TLS_RSA_WITH_AES_256_CBC_SHA256) won't be enabled. P.S:  If you are using JDK7 and using a strong cipher suite such as TLS_RSA_WITH_AES_256_CBC_SHA256, then you must enable it through the property -Doracle.net.ssl_cipher_suites="(TLS_RSA_WITH_AES_256_CBC_SHA256)"  3: Set the Database Connection String Download DataSourceSample.java or UCPSample.java from Github Modify the Java code to include the correct DB_URL, DB_USER, and DB_PASSWORD of the database that you have access to. Make sure to use TCPS protocol with its corresponding port and configure the server's DN for mutual authentication. Example: DB_USER = "hr", DB_PASSWORD ="hr", and  DB_URL = "jdbc:oracle:thin:@(DESCRIPTION= (ADDRESS=                    (PROTOCOL=TCPS)(PORT=1522)(HOST=myhost))                    (CONNECT_DATA=(SERVICE_NAME=myorcldbservicename))                    (SECURITY=(ssl_server_cert_dn="CN=testcert.oracle.com, O=Oracle Corporation,L=Redwood City,ST=California,C=US")))" Note: If you are using tnsnames.ora then it is sufficient to use TNS alias in the URL E.g., DB_URL="jdbc:oracle:thin:@dbaccess". Set the path of tnsnames.ora with oracle.net.tns_admin system property. SSL connection using TLSv1.2 with JKS Java Key Store (JKS) is used as a container for the client's certificates exchanged between the server and the client. The advantage of using JKS is its native support in the JRE and there is no need of any additional security provider because Sun's default PKI provider supports JKS format. Refer to the definitions of trustStore and keyStore for clarity. trustStore stores certificates from trusted Certificate Authorities (CA) which will be used to verify a certificate presented by the server in the SSL connection. keyStore contains the client certificate which will be used for authentication and it also contains a set of private/public keys that will be used for encryption. Applications should present the keyStore when the client needs to be authenticated on the server. Follow these steps to connect to Oracle DB using JDBC Thin driver and JKS: Step 1: Complete the pre-requisites 1-3 from the "SSL Connection using TLSv1.2" section Step 2: Use the JKS (keyStore.jks and trustStore.jks) files Make sure to have the files keyStore.jks and trustStore.jks at a location accessible to the application and use the connection properties to provide the JKS file location and password. Refer to the sample commands for the properties. Step 3: Enable the server DN matching Server DN matching is used for mutual authentication during the SSL handshake. Set this using oracle.net.ssl_server_dn_match=true system property. Step 4: Sample commands to run a Java program using JKS files  === JDK8 AND THE 12.2.0.1 JDBC THIN DRIVER ==== java -Doracle.net.ssl_server_dn_match="true" -Doracle.net.tns_admin=./lib -Djavax.net.ssl.trustStore="truststore.jks" -Djavax.net.ssl.trustStorePassword="welcome1" -Djavax.net.ssl.trustStoreType="JKS" -Djavax.net.ssl.keyStore="/client_credentials/keystore.jks" -Djavax.net.ssl.keyStoreType="JKS" -Djavax.net.ssl.keyStorePassword="welcome1" DataSourceSample   ==== JDK7 AND THE 12.1.0.2 PATCHED THIN DRIVER ==== java -Doracle.net.ssl_version="1.2" -Doracle.net.tns_admin=./lib -Doracle.net.ssl_server_dn_match="true" -Doracle.net.ssl_cipher_suites="(TLS_RSA_WITH_AES_256_CBC_SHA256)" -Djavax.net.ssl.trustStore="/client_credentials/truststore.jks" -Djavax.net.ssl.trustStorePassword="welcome1" -Djavax.net.ssl.keyStore="/client_credentials/keystore.jks" -Djavax.net.ssl.keyStorePassword="welcome1" DataSourceSample SSL connection using TLSv1.2 with Oracle Wallets Wallets created by Oracle Wallet Manager or "orapki" use the standard PKCS12 format to store X.509 certificates and private keys. The wallet is stored in a file named "ewallet.p12". If you enable auto-login in the wallet, an obfuscated copy of the wallet is created in the file "cwallet.sso" which can then be used without providing the password. Note that you must use Oracle’s PKI provider named “OraclePKI” to access Oracle wallets from Java. Follow these steps to connect to Oracle DB using JDBC Thin driver and Oracle Wallets: Step 1: Complete the pre-requisites 1-3 from the "SSL Connection using TLSv1.2" section Step 2: Add the required dependant jars for using Oracle Wallets Along with JDBC thin driver (e.g., ojdbc8.jar from 12.2.0.1), also download oraclepki.jar, osdt_cert.jar, and osdt_core.jar files (these are available under $ORACLE_HOME/lib) or you can also download these from JDBC Download page on OTN and place them in the CLASSPATH. Step 3: Enable Oracle PKI provider Follow any of the two ways to enable Oracle PKI provider. (a) Enable it statically as follows: If you use SSO wallets (cwallet.sso), Add the OraclePKIProvider at the end of the provider list in the file java.security (this file is part of your JRE install located at $JRE_HOME/jre/lib/security/java.security) which  typically looks like: security.provider.1=sun.security.provider.Sun security.provider.2=sun.security.rsa.SunRsaSign security.provider.3=com.sun.net.ssl.internal.ssl.Provider security.provider.4=com.sun.crypto.provider.SunJCE security.provider.5=sun.security.jgss.SunProvider security.provider.6=com.sun.security.sasl.Provider security.provider.7=oracle.security.pki.OraclePKIProvider Note that if another provider supports the type “SSO”, then it should be inserted after Oracle’s PKI provider. If you use PKCS12 wallets (ewallet.p12), move Oracle’s PKI provider to position #3 or any position ahead of Sun’s provider which also supports PKCS12 but which is not compatible with Oracle’s wallets. So the list of providers in java.security look like: security.provider.1=sun.security.provider.Sun security.provider.2=sun.security.rsa.SunRsaSign security.provider.3=oracle.security.pki.OraclePKIProvider security.provider.4=com.sun.net.ssl.internal.ssl.Provider security.provider.5=com.sun.crypto.provider.SunJCE security.provider.6=sun.security.jgss.SunProvider security.provider.7=com.sun.security.sasl.Provider (b) Enable it dynamically in your Java program as follows: If you use SSO wallets, you simply need to “add” Oracle’s PKI provider because the order does not matter (assuming that there is no other provider for SSO): Security.addProvider(new oracle.security.pki.OraclePKIProvider()); If you use PKCS12 wallets, Oracle PKI’s provider needs to be inserted at position#3: Security.insertProviderAt(new oracle.security.pki.OraclePKIProvider(),3); Step 4: Set the Oracle Wallet location Set the wallet location using oracle.net.wallet_location="(SOURCE=(METHOD=file)(METHOD_DATA=(DIRECTORY=/home/test/cloud))) The wallet location is where the SSO wallets (cwallet.sso and ewallet.p12) are placed. Step 5: Enable the server DN matching Server DN matching is used for mutual authentication during the SSL handshake. Set this using oracle.net.ssl_server_dn_match=true system property. Step 6: Sample commands to run a Java program using Oracle Wallets If you are using a plain Java program then use the command as shown below to connect using Oracle Wallets and JDBC driver. java -classpath ./lib/ojdbc8.jar:./lib/oraclepki.jar:./lib/osdt_cert.jar:./lib/osdt_core.jar:. -Doracle.net.tns_admin=./lib -Doracle.net.wallet_location="(SOURCE=(METHOD=file)(METHOD_DATA=(DIRECTORY=/home/test/cloud/lib)))" -Doracle.net.ssl_server_dn_match=true DataSourceSample Appendix:  If you run into more issues, you can turn on tracing using -Djavax.net.debug=all Refer to the JDBC Troubleshooting Tips Refer to Java Connectivity with ATP/ADW using 19c and 18c for the latest 

Brief Introduction to SSL The Oracle database product supports SSL/TLS connections in its standard edition (since 12c). The Secure Sockets Layer (SSL) protocol provides network-level authentication,...

Create and Deploy a Java Servlet using WebLogic Server (WLS)

Oracle WebLogic Server(WLS) is one of the popular Java EE containers.  In this blog, we want to highlight the steps required to create a Java servlet that connects to Oracle Database using Oracle JDBC and UCP.  The blog contains step-by-step instructions from installing WebLogic Server to deploying a servlet that connects to the Oracle database and retrieves data from the database.  Refer to the table below for more details. Step Number Description Step 1 Download the WebLogic Server v12.2.1.2 and Unzip the contents Step 2 Install the WebLogic Server v12.2.1.2 Step 3 Create a domain Step 4 Start the WebLogic  Server and access the admin console Step 5 Create an Oracle Datasource from the console Step 6 Create a Java Servlet that uses the Datasource created Step 7 Compile the Java Servlet Step 8 Deploy the WAR file and Invoke the Servlet from the browser Step 9 Questions and Answers   Step 1: Download the WebLogic Server v12.2.1.2.  Make sure to download "Quick Installer for Mac OSX, Windows, and Linux".  Unzip the contents of the Installer which includes read me files and fmw_12.2.1.2.0_wls_quick_Disk1_1of1.zip. Step 2: Install the WebLogic Server by using the command "java -jar fmw_12.2.1.2.0_wls_quick.jar".  This takes sometime and wait for the installation to complete. It creates a folder "wls12212". Refer to this location as WLS_HOME. Step 3: Create a domain in WLS1.  A domain is required to start the adminstration server. Invoke the "Configuration Wizard" by running the config.sh from "WLS_HOME/oracle_common/common/bin/config.sh".  Follow the configuration wizard to (a) Choose "Basic Weblogic Server Domain -12.2.1.2.0[wlsserver]*" which is the default option (b) Enter weblogic username/password (c) Choose "Administrator Server" (d) Enter the port number if anything other than 7001 is required (e) The configuration completes displaying the Admin Server URL: http://localhost:7001/console Fig 4.1:  Configuration Wizard Step 4: Start the WebLogic Server by invoking startWebLogic.sh from "WLS_HOME/user_projects/domains/base_domain/bin/startWebLogic.sh" Once the admin server is started, you can access the WebLogic console at http://localhost:7001/console.  Login with the username/password created from Step 4.  Fig 4.1: WebLogic Administrator Console Step 5: Create an Oracle Datasource from the console.  (a) Services --> Data Sources --> Click on New --> Choose JDBC or UCP or Active Grid Link (AGL) datasource from the drop down menu as shown in Fig 5.1. (b) Choose "Generic Data Source" (JDBC only) or "UCP Data Source"(UCP).  Enter the Name, JNDI Name and choose the Database Type. JNDI name will be used in the servlet.  Name this as "orcljdbc_ds" (c) Follow the steps of choosing the driver, entering the database information.  A summary page looks like Fig 5.2.  Click on "Test Connection" to verify the connection to the database.  Make sure that the connection is successful as shown in Fig 5.3 before we proceed with using this datasource in the Java servlet. (d) Make sure to associate the Target for the new datasource created as shown in Fig 5.4. Click on "FINISH" to save the datasource. Fig 5.1: Drop down menu of Datasources Fig 5.2: Database details summary page Fig 5.3: Successful connection test message Fig 5.4: Associate Target of the Datasource to an AdminServer Step 6: Create a Java Servlet to use the Oracle datasource(orcljdbc_ds) created to obtain a connection. A sample JDBCSample_Servlet.java is provided on Github for reference.  Below is the code fragment to obtain the connection. /* * Method that creates the datasource for the JNDI lookup */ private DataSource getDataSource() throws NamingException { Context ctx = new InitialContext(); // Look up for the JNDI datasource javax.sql.DataSource ds = (javax.sql.DataSource) ctx.lookup ("orcljdbc_ds"); return ds; } //For JDBC: Get the Oracle Datasource DataSource ds = getDataSource(); try (Connection connection = ds.getConnection()) { ... } //For UCP : Use PoolDataSource to get the connection PoolDataSource pds = (PoolDataSource) getDataSource(); try (Connection connection = ds.getConnection()) { ... } Step 7: Compile the Java Servlet created.  Use WLS_HOME/wlserver/server/lib/weblogic.jar that is required to compile the servlet.  Refer to the build.xml for more details that can be used to build the war file. Copy the war file to the location WLS_HOME/user_projects/domains/base_domain/JDBCSample.war Step 8: Deploy the war file and Invoke the servlet. Go to Deployments --> Install --> Choose the war file to be deployed and follow instructions.  Fig 8.1: Deploying a war file in WLS Fig 8.2: Screenshot after invoking the Servlet Step 9 : Questions and Answers Question: Where is the JDBC driver located ? Answer: $MW_HOME is the place where the WebLogic Server is installed. The JDBC driver is present at $MW_HOME/oracle_common/modules/oracle.jdbc/ojdbc8.jar. You can use $java -jar ojdbc8.jar command to check the JDBC version and more details as shown below. ➜  oracle.jdbc java -jar ojdbc8.jar Oracle 12.2.0.1.0 JDBC 4.2 compiled with javac 1.8.0_91 on Tue_Dec_13_06:08:31_PST_2016 #Default Connection Properties Resource #Fri May 25 15:37:18 PDT 2018 ***** JCE UNLIMITED STRENGTH IS INSTALLED **** Question: How do I use a different JDBC driver in WLS? Answer:  Make sure you place the JDBC driver that you want at $MW_HOME/oracle_common/modules/oracle.jdbc/*. If you are using any JDBC driver other than the one that is already present then you will need to add the path of this driver explicitly at $WLS_HOME/oracle_common/common/bin/commExtEnv.sh as shown here. WEBLOGIC_CLASSPATH="${MW_HOME}/oracle_common/modules/oracle.jdbc/ojdbc8.jar${CLASSPATHSEP}..." $WLS_HOME

Oracle WebLogic Server(WLS) is one of the popular Java EE containers.  In this blog, we want to highlight the steps required to create a Java servlet that connects to Oracle Database using Oracle JDBC...

Connecting Java Applications to Database Cloud Services

Oracle Database Service on Cloud offers access to the Oracle Database with Oracle providing the computing power, physical storage, and tools for maintenance and management operations.  The Oracle database with full access to the features and operations can be created within few minutes.  There are several database service deployment choices and these choices allow you to start at the cost and capability level suitable to your use case and then gives you the choices to adapt as your requirements change over time. This blog addresses the prerequisites and pointers for connecting Java applications, Java containers, and Plain Java programs to any database service on cloud. Oracle Database Cloud Service (DBCS) Oracle Database Cloud Service(DBCS) provides the full power of the Oracle Database in the cloud for any type of application.  Customers can connect to the compute node where the database instance is running through a SSH tunnel.   You can use all standard network(TCP) connections to connect to the database.  The following link furnishes a step-by-step instructions to verify the successful connection to DBCS. Using Java Applications and IDEs with Oracle Database Cloud Service (DBCS)   Oracle Cloud Infrastructure Database (OCI) Oracle Cloud Infrastructure Database (OCI)  offers the full power of the Oracle Database on a low latency, highly configurable and secure Virtual Cloud Network.  Customers can connect to the compute node where the database instance is running through a SSH tunnel.   You can use the standard network(TCP) connections to connect to the database. The following link furnishes a step-by-step instructions to verify the successful connection to OCI. Using Java Applications and IDEs with Oracle Cloud Infrastructure Database (OCI) Oracle Database Exadata Express Cloud Service (EECS) - Fully Managed Oracle Database Exadata Express Cloud Service (EECS) is a FULLY MANAGED database service targeted for production applications that consist of small to medium sized data.  It is also ideal for developers, testers, evaluators, and other users who are looking for a full Oracle Database experience at an affordable entry level price.  EECS mandates SSL connections using TLSv1.2.  Java applications require Java Key Store (JKS) file or Oracle Wallets while connecting to this database service.  The following links furnish a step-by-step instructions to verify the successful connection to EECS. Initial Steps for Using Java with Oracle Database Exadata Express Cloud Service (EECS) Using Java EE Containers with Oracle Database Exadata Express Cloud Service (EECS) Using Java Developer Tools with Oracle Database Exadata Express Cloud Service (EECS) JDBC Trouble Shooting Tips for Oracle Database Exadata Express Cloud Service (EECS)    

Oracle Database Service on Cloud offers access to the Oracle Database with Oracle providing the computing power, physical storage, and tools for maintenance and management operations.  The...

Using Universal Connection Pool (UCP) as a Pool Datasource in JBoss 7.0 EAP

In a previous article we detailed the steps to configure Universal Connection Pooling (UCP) running in JBoss AS 6.1 as a singleton service. This service allows consumers to acquire database connections with all the intrinsic benefits of UCP (also detailed in the previous article): . Runtime Connection Load Balancing (RCLB) . Fast Connection Failover (FCF) . Transaction Affinity . Built-in support for Database Resident Connection Pooling (DRCP) and Application Continuity (AC) In this article we are going to take a more generic approach -avoiding the usage of a wrapping service- to directly use UCP as a Pool Data Source defined in JBoss' configuration. We are also going to build a basic Servlet to retrieve the pool through JNDI and test it through an http request. We will deploy the web application in an updated version of JBoss (JBoss 7.0 EAP). 1. First step is the same as in the previous article which is downloading JBoss 7.0 EAP https://developers.redhat.com/products/eap/download 2. Also as in the previous article, optionally download JBoss Developer Studio https://developers.redhat.com/products/devstudio/download 3. Configure JBoss 7.0 EAP as a server in JBoss Developer Studio (Servers / New Server / Red Hat JBoss Enterprise Application Platform 7.0 and select installation directory) 4. Install UCP. JDBC driver and ONS as a module. First you need to download ucp.jar, ons.jar and ojdbc8.jar from OTN: https://www.oracle.com/technetwork/database/application-development/jdbc/downloads/jdbc-ucp-19c-5460552.html. Then you can either create the module using JBoss Command Line Interface (jboss-cli) with the following command: JBOSS_HOME/bin/jboss-cli.sh -c module add --name=com.oracle.ucp \ --resources=~/Downloads/ucp.jar:~/Downloads/ojdbc8.jar:~/Downloads/ons.jar \ --dependencies=javax.api,javax.transaction.api Or you can manually create the file JBOSS_HOME/modules/com/oracle/ucp/main/module.xml with the following content. If you executed this step with jboss-cli, you can use the following xml excerpt as a validation. <?xml version="1.0" ?> <module xmlns="urn:jboss:module:1.1" name="com.oracle.ucp" slot="main">     <resources>         <resource-root path="ucp.jar"/>         <resource-root path="ons.jar"/>         <resource-root path="ojdbc8.jar"/>     </resources>     <dependencies>         <module name="javax.api"/>         <module name="javax.transaction.api"/>     </dependencies> </module> In the same folder you should drop ucp.jar, ons.jar and ojdbc8.jar. 5. Create the driver reference. In this step you can also use jboss-cli executing the following command: JBOSS_HOME/bin/jboss-cli.sh -c /subsystem=datasources/jdbc-driver=oracle-ucp:\ add(driver-name=oracle-ucp, driver-module-name=com.oracle.ucp, \ driver-xa-datasource-class-name=oracle.ucp.jdbc.PoolXADataSourceImpl, \ driver-datasource-class-name=oracle.ucp.jdbc.PoolDataSourceImpl) Or you can manually edit JBOSS_HOME/standalone/configuration/standalone.xml adding the following driver under subsystem/datasources/drivers (notice module name should be the same as defined in previous step). If you executed this step using jboss-cli you can validate the result by looking at this file. <driver name="oracle-ucp" module="com.oracle.ucp">     <xa-datasource-class>oracle.ucp.jdbc.PoolXADataSourceImpl</xa-datasource-class>     <datasource-class>oracle.ucp.jdbc.PoolDataSourceImpl</datasource-class> </driver> 6. Create the datasource. Just like in the previous steps, you can create it using jboss-cli issuing the following command: JBOSS_HOME/bin/jboss-cli.sh -c data-source add --name=mypool --jndi-name="java:/datasources/mypool" \ --connection-properties=InactiveConnectionTimeout=0,PropertyCycle=900,\ ValidateConnectionOnBorrow=true,MaxPoolSize=40,MinPoolSize=0,\ FastConnectionFailoverEnabled=true,AbandonedConnectionTimeout=0,\ URL=jdbc:oracle:thin:@myhost:5521:mysid,ConnectionPoolName=mypool,\ ConnectionWaitTimeout=0,TimeToLiveConnectionTimeout=0,\ Password=tiger,User=scott,\ ConnectionFactoryClassName=oracle.jdbc.pool.OracleDataSource \ --driver-name=oracle-ucp \ --min-pool-size=0 \ --max-pool-size=20 \ --allow-multiple-users=false \ --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker \ --validate-on-match=true \ --use-fast-fail=true \ --stale-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.oracle.OracleStaleConnectionChecker \ --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter Or you can manually edit JBOSS_HOME/standalone/configuration/standalone.xml adding the following datasource under subsystem/datasources (the most important attribute to notice is the datasource-class, in which we inject UCP Pool Data Source). Just like in the previous steps, you can validate jboss-cli execution by looking at this file. <datasource jndi-name="java:/datasources/mypool" pool-name="mypool" enabled="true">     <connection-url>jdbc:oracle:thin:@myhost:5521:mysid</connection-url>     <datasource-class>oracle.ucp.jdbc.PoolDataSourceImpl</datasource- class>     <connection-property name="InactiveConnectionTimeout">0</connection- property>     <connection-property name="PropertyCycle">900</connection-property>     <connection-property name="ValidateConnectionOnBorrow">true</connection-property>     <connection-property name="MaxPoolSize">40</connection-property>     <connection-property name="MinPoolSize">0</connection-property>     <connection-property name="FastConnectionFailoverEnabled">true</connection-property>     <connection-property name="AbandonedConnectionTimeout">0</connection-property>     <connection-property name="URL">jdbc:oracle:thin:@myhost:5521:mysid</connection-property>     <connection-property name="ConnectionPoolName">mypool</connection-property>     <connection-property name="ConnectionWaitTimeout">0</connection-property>     <connection-property name="TimeToLiveConnectionTimeout">0</connection-property>     <connection-property name="Password">tiger</connection-property>     <connection-property name="User">scott</connection-property>     <connection-property name="ConnectionFactoryClassName">         oracle.jdbc.pool.OracleDataSource</connection-property>     <driver>oracle-ucp</driver>     <pool>         <min-pool-size>0</min-pool-size>         <max-pool-size>20</max-pool-size>         <allow-multiple-users>false</allow-multiple-users>     </pool>     <validation>         <valid-connection-checker class-   name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>         <validate-on-match>true</validate-on-match>         <use-fast-fail>true</use-fast-fail>         <stale-connection-checker class- name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleStaleConnectionChecker"/>           <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>     </validation> </datasource> 7. At this step you can already test the datasource in the web console. Start the application server by running JBOSS_HOME/bin/standalone.sh, or Right click on the server and “Start”. 8. Open the console in http://localhost:9990 and execute “Configuration / Subsystems / Datasources / Non – XA / MyPool / View / Connection / Test Connection” (you can also correlate here all the values you manually entered in the configuration file). 9. Create the Servlet. From JBoss Developer Studio execute “New / Project / Dynamic Web Project “ and end with default options. Be aware that the name of the project -that would be by default the name of the deployed war- is going to be the same as the relative path to your web app. You can do this step without JBoss Developer Studio, by just compiling below servlet and deploying it in JBoss EAP 7.0 manually wrapped in a war file with a web.xml descriptor. package sample; import java.io.IOException; import java.io.PrintWriter; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.naming.Context; import javax.naming.InitialContext; import javax.naming.NamingException; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.sql.DataSource; // URL to reach the Servlet @WebServlet("/OracleUCPJBoss") public class OracleUCPJBoss extends HttpServlet {   private static final long serialVersionUID = 1L;  // Pool Datasource reference, to be instantiated at init   private DataSource ds = null;   // Retrieve Datasource reference using JNDI   @Override   public void init() throws ServletException {     Context initContext;     try {       initContext = new InitialContext();ds = (DataSource) initContext.lookup("java:/datasources/mypool");     } catch (NamingException e) {       e.printStackTrace();     }   }   // GET request handling   protected void doGet(HttpServletRequest request, HttpServletResponse response)     throws ServletException, IOException {     // Retrieve connection from the pool     try (Connection conn = ds.getConnection(); Statement st = conn.createStatement()) {       // Initialize output and retrieve parameters       PrintWriter pw = response.getWriter();       String job = request.getParameter("job");       ResultSet rs = null;       // List employees. If job parameter is sent, filter this list       if (job == null) {         rs = st.executeQuery("select empno, ename, job from emp");       } else {         rs = st.executeQuery("select empno, ename, job from emp where job = '" + job + "'");       }       // Show list on browser       while (rs.next()) {         pw.println(rs.getString("empno") + " - " + rs.getString("ename") + " - " + rs.getString("job"));       }       // Debug info       pw.println("Served at: " + request.getContextPath());     } catch (SQLException e) {       e.printStackTrace();     }   }   // Re-route any Post request   protected void doPost(HttpServletRequest request, HttpServletResponse response)     throws ServletException, IOException {     doGet(request, response);   } } 10. Deploy the web application: right click on the project and execute “Run as / Run on Server” and select the JBoss EAP 7.0 server (or just copy the compiled war to JBOSS_HOME/standalone/deployments). Test the servlet by opening an browser on: http://localhost:8080/OracleUCPDS/OracleUCPJBoss And to test the filter: http://localhost:8080/OracleUCPDS/OracleUCPJBoss?job=CLERK              

In a previous article we detailed the steps to configure Universal Connection Pooling (UCP) running in JBoss AS 6.1 as a singleton service. This service allows consumers to acquire database connections...

Using UCP Multi-Tenant Shared Pool feature with Tomcat

div#blogentry{font-family: Georgia, "Bitstream Charter", serif !important;color: #333;font-size: 1.1em;/* line-height: 1.1em; *//* width: 635px !important; *//* border-right-style: solid; */}a:hover {color:red;}div#blogentry div.codeexample{white-space: pre-wrap;/*width: 635px !important;*/font-family: "Consolas", "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important;color: black !important;font-size: 1em;line-height: 1em;margin: 0 0 0 0px;padding: 0 0 0 15px;border-left-style:solid;border-color:#98bf21;white-space:wrap;}/* Line Number */#LN { color: #BBBBBB; background-color:#FFFFFF }/* Link Colours */#Classes A:link{ color: #000000; }#Classes A:visited{ color: #000000; }#Classes PRE { color: #000000; }/* Token Colours */#CharacerLiteral{ color: #FF00FF; }#StringLiteral{ color: #FF00FF; }#SingleLineComment{ color: #008000; }#FormalComment{ color: #008000; }#MultiLineComment{ color: #008000; }#Abstract{ color: #0000FF ; font-weight: bold }#Boolean{ color: #0000FF ; font-weight: bold }#Break{ color: #0000FF ; font-weight: bold }#Byte{ color: #0000FF ; font-weight: bold }#Case{ color: #0000FF ; font-weight: bold }#Catch{ color: #0000FF ; font-weight: bold }#Char{ color: #0000FF ; font-weight: bold }#Class{ color: #0000FF ; font-weight: bold }#Const{ color: #0000FF ; font-weight: bold }#Continue{ color: #0000FF ; font-weight: bold }#Default{ color: #0000FF ; font-weight: bold }#Do{ color: #0000FF ; font-weight: bold }#Double{ color: #0000FF ; font-weight: bold }#Else{ color: #0000FF ; font-weight: bold }#Extends{ color: #0000FF ; font-weight: bold }#False{ color: #0000FF ; font-weight: bold }#Final{ color: #0000FF ; font-weight: bold }#Finally{ color: #0000FF ; font-weight: bold }#Float{ color: #0000FF ; font-weight: bold }#For{ color: #0000FF ; font-weight: bold }#Goto{ color: #0000FF ; font-weight: bold }#If{ color: #0000FF ; font-weight: bold }#Implements{ color: #0000FF ; font-weight: bold }#Import{ color: #0000FF ; font-weight: bold }#InstanceOf{ color: #0000FF ; font-weight: bold }#Int{ color: #0000FF ; font-weight: bold }#Interface{ color: #0000FF ; font-weight: bold }#Long{ color: #0000FF ; font-weight: bold }#Native{ color: #0000FF ; font-weight: bold }#New{ color: #0000FF ; font-weight: bold }#Package{ color: #0000FF ; font-weight: bold }#Private{ color: #0000FF ; font-weight: bold }#Protected{ color: #0000FF ; font-weight: bold }#Public{ color: #0000FF ; font-weight: bold }#Return{ color: #0000FF ; font-weight: bold }#Short{ color: #0000FF ; font-weight: bold }#Static{ color: #0000FF ; font-weight: bold }#Super{ color: #0000FF ; font-weight: bold }#Switch{ color: #0000FF ; font-weight: bold }#Synchronized{ color: #0000FF ; font-weight: bold }#This{ color: #0000FF ; font-weight: bold }#Throw{ color: #0000FF ; font-weight: bold }#Throws{ color: #0000FF ; font-weight: bold }#Transient{ color: #0000FF ; font-weight: bold }#True{ color: #0000FF ; font-weight: bold }#Try{ color: #0000FF ; font-weight: bold }#Void{ color: #0000FF ; font-weight: bold }#Volatile{ color: #0000FF ; font-weight: bold }#While{ color: #0000FF ; font-weight: bold }#StrictFP{ color: #0000FF ; font-weight: bold }#Null{ color: #0000FF ; font-weight: bold }#IntegerLiteral{ color: #000000 }#DecimalLiteral{ color: #000000 }#HexLiteral{ color: #000000 }#OctalLiteral{ color: #000000 }#FloatPointLiteral{ color: #000000 }#XmlElement{ color: #3F7F7F }#XmlAttribute{ color: #7F007F }#XmlAttributeValue{ color: #2A00FF } Oracle Universal Connection Pool (UCP) adds a new capability called as "Shared Pool" in the Oracle Database 12c Release 2 (12.2). The blog "UCP Multi-Tenant Shared Pool Configuration" shows how a standalone application can use UCP shared pool. Tomcat can also leverage "Shared Pool" benefits by following few simple steps as shown here. Shared Pool Architecture: Required Setup Install Apache Tomcat -8.0.26. Copy ojdbc8.jar and ucp.jar into $CATALINA_HOME/lib directory. Create a XML configuration file. Refer blog to create UCP XML config file. Set the Java System property to specify XML configuration file location in catalina.sh. For Windows environment use catalina.bat. JAVA_OPTS="$JAVA_OPTS -Doracle.ucp.jdbc.xmlConfigFile='file:/test/ucp/config/SharedPool_config.xml'" There are two ways in which an application can access the data source defined in the XML configuration file. By Using UCP PoolDataSourceFactory API: Here we can pass data source name as "pds1" and "pds2" to getDataSource("pds1") method. The method returns preconfigured data source object whose properties are defined in UCP XML config file. Using this data source object we can get a connection to the database using getConnection() method. A sample code to access data source and connection will look like below - // Get the preconfigured data source which is defined in XML configuration file private DataSource getDataSource(String dataSourceName) throws SQLException { javax.sql.DataSource ds = (javax.sql.DataSource) PoolDataSourceFactory .getPoolDataSource(dataSourceName); return ds; } private Connection getConnection(DataSource ds) throws SQLException { return getConnection(); } By creating a JNDI Resource : Create a JNDI resource in Tomcat's server.xml file for each data source defined in the UCP XML config file. All the resources defined in Tomcat's config file must point to a data source in UCP XML config file. The mapping is done using a property called "dataSourceFromConfiguration". Here "pds1" and "pds2" are the data source names defined in the sample UCP XML config file used to write this article. Create resource links in server's context.xml file for above resources defined in server.xml. Create an application servlet and lookup the JNDI resource to get the data source instance and then call getConnection() API to get a database connection. // Get the data source instance defined in UCP XML config file using JNDI lookup private javax.sql.DataSource getDataSource(String dataSourceName) { try { Context initialContext = new InitialContext(); Context environmentContext = (Context) initialContext .lookup("java:comp/env"); javax.sql.DataSource ds = (javax.sql.DataSource) environmentContext .lookup(dataSourceName); return ds; } catch (NamingException ne) { ne.printStackTrace(); } return null; } private Connection getConnection(javax.sql.DataSource ds) throws SQLException { return ds.getConnection(); } Here we can pass the resource link name to getDataSource(dataSourceName) method to get the data source instance. In this case the resource link names are -"ucpDS1" and "ucpDS2". Using this data source instance we can get a connection to the database using getConnection() method.  

Oracle Universal Connection Pool (UCP) adds a new capability called as "Shared Pool" in the Oracle Database 12c Release 2 (12.2). The blog "UCP Multi-Tenant Shared Pool Configuration" shows how...

UCP Multi-Tenant Shared Pool Configuration

div#blogentry{font-family: Georgia, "Bitstream Charter", serif !important;color: #333;font-size: 1.1em;/* line-height: 1.1em; *//* width: 635px !important; *//* border-right-style: solid; */}a:hover {color:red;}div#blogentry div.codeexample{white-space: pre-wrap;/*width: 635px !important;*/font-family: "Consolas", "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important;color: black !important;font-size: 1em;line-height: 1em;margin: 0 0 0 0px;padding: 0 0 0 15px;border-left-style:solid;border-color:#98bf21;white-space:wrap;}/* Line Number */#LN { color: #BBBBBB; background-color:#FFFFFF }/* Link Colours */#Classes A:link{ color: #000000; }#Classes A:visited{ color: #000000; }#Classes PRE { color: #000000; }/* Token Colours */#CharacerLiteral{ color: #FF00FF; }#StringLiteral{ color: #FF00FF; }#SingleLineComment{ color: #008000; }#FormalComment{ color: #008000; }#MultiLineComment{ color: #008000; }#Abstract{ color: #0000FF ; font-weight: bold }#Boolean{ color: #0000FF ; font-weight: bold }#Break{ color: #0000FF ; font-weight: bold }#Byte{ color: #0000FF ; font-weight: bold }#Case{ color: #0000FF ; font-weight: bold }#Catch{ color: #0000FF ; font-weight: bold }#Char{ color: #0000FF ; font-weight: bold }#Class{ color: #0000FF ; font-weight: bold }#Const{ color: #0000FF ; font-weight: bold }#Continue{ color: #0000FF ; font-weight: bold }#Default{ color: #0000FF ; font-weight: bold }#Do{ color: #0000FF ; font-weight: bold }#Double{ color: #0000FF ; font-weight: bold }#Else{ color: #0000FF ; font-weight: bold }#Extends{ color: #0000FF ; font-weight: bold }#False{ color: #0000FF ; font-weight: bold }#Final{ color: #0000FF ; font-weight: bold }#Finally{ color: #0000FF ; font-weight: bold }#Float{ color: #0000FF ; font-weight: bold }#For{ color: #0000FF ; font-weight: bold }#Goto{ color: #0000FF ; font-weight: bold }#If{ color: #0000FF ; font-weight: bold }#Implements{ color: #0000FF ; font-weight: bold }#Import{ color: #0000FF ; font-weight: bold }#InstanceOf{ color: #0000FF ; font-weight: bold }#Int{ color: #0000FF ; font-weight: bold }#Interface{ color: #0000FF ; font-weight: bold }#Long{ color: #0000FF ; font-weight: bold }#Native{ color: #0000FF ; font-weight: bold }#New{ color: #0000FF ; font-weight: bold }#Package{ color: #0000FF ; font-weight: bold }#Private{ color: #0000FF ; font-weight: bold }#Protected{ color: #0000FF ; font-weight: bold }#Public{ color: #0000FF ; font-weight: bold }#Return{ color: #0000FF ; font-weight: bold }#Short{ color: #0000FF ; font-weight: bold }#Static{ color: #0000FF ; font-weight: bold }#Super{ color: #0000FF ; font-weight: bold }#Switch{ color: #0000FF ; font-weight: bold }#Synchronized{ color: #0000FF ; font-weight: bold }#This{ color: #0000FF ; font-weight: bold }#Throw{ color: #0000FF ; font-weight: bold }#Throws{ color: #0000FF ; font-weight: bold }#Transient{ color: #0000FF ; font-weight: bold }#True{ color: #0000FF ; font-weight: bold }#Try{ color: #0000FF ; font-weight: bold }#Void{ color: #0000FF ; font-weight: bold }#Volatile{ color: #0000FF ; font-weight: bold }#While{ color: #0000FF ; font-weight: bold }#StrictFP{ color: #0000FF ; font-weight: bold }#Null{ color: #0000FF ; font-weight: bold }#IntegerLiteral{ color: #000000 }#DecimalLiteral{ color: #000000 }#HexLiteral{ color: #000000 }#OctalLiteral{ color: #000000 }#FloatPointLiteral{ color: #000000 }#XmlElement{ color: #3F7F7F }#XmlAttribute{ color: #7F007F }#XmlAttributeValue{ color: #2A00FF } Oracle Universal Connection Pool (UCP) adds a new capability called as "Shared Pool" in the Oracle Database 12c Release 2 (12.2). 'Shared Pool' is particularly targeted for customers leveraging multi-tenant architecture that consists of multiple pluggable databases (PDBs)/tenants inside a single container database (CDB). Each PDB is an independent datasource configured using a connection string that contains its own database service name, username and password. 'Shared Pool' enables sharing a common pool of connections across multiple PDBs/datasources. The benefits of a 'Shared Pool' are highlighted below. Without Shared Pool With Shared Pool Using an individual pool per tenant/PDB would result in wastage of expensive resources Causes scalability, manageability and diagnose-ability issues when more tenants are added. 'Shared Pool' consolidates many datasources/PDBs by sharing connections across PDBs. Shared Pool decreases the number of database connections thus improving usage of resources, scalability, manageability and diagnose-ability Uneven distribution of load, as some PDBs are heavily loaded and others are lightly loaded Causes an inefficient usage of resources as idle connections cannot be moved between PDBs 'Shared Pool' adds flexibility and distributes the connections to datasources based on their work load. 'Shared Pool' also allows setting a maximum number of connections per service that prevents connection starvation and ensures a fair use of the shared pool A connection assigned to a tenant cannot be used for any other tenant Repurposing/reusing connections across PDBs is impossible when an individual pool per tenant is used For Public or Private Cloud multi-tenant deployments, a 'Shared Pool' with a common user is capable of repurposing connections from other tenants This new capability of switching the service between tenants leads to better resource sharing and improved density     Shared Pool Architecture: Pre-requisites for using a 'Shared Pool': Must specify the Shared Pool configurations through a UCP XML config file. Each datasource using a Shared Pool must be present in UCP XML config file. The services configured for tenants must be an application service. Also, services must be homogeneous (should have similar properties wrt AC, TG, DRCP etc.). Note that repurposing of connections is not supported with admin services or default PDB services. Repurposing of connections in the shared pool works ONLY when it is configured with a common user (user starting with C##) and satisfies the following requirements. Common user should have the privileges to create session, alter session and set container. Common user should have execute permission on 'dbms_service_prvt' package. Any specific roles or password for common user should also be specified in the UCP XML config file.   Sample Code to demonstrate "Shared Pool" feature: In the sample code two datasources "pds1" and "pds2" are accessing shared pool - "pool1" Pool is initialized with root service connections and each datasource requests connections for its pdb specific service using getConnection() method. On receiving a connection borrow request from a datasource the pool looks for an available connection for that particular pdb service and returns it to datasource If there are no available connections for the requested pdb service, the pool tries to repurpose existing available connections of other pdb services. If there are no available connections in the pool for any service then the pool creates a new connection for the service requested by the datasource. Using UCP Statistics we can print the count of number of connections repurposed by the pool. To validate/test the connections borrowed from the pool we are executing a simple query on the connection which prints the connection attributes like - connection username, service and DB name. In the example we have also configured "max-connections-per-service" attribute on pool which indicates that the pool cannot repurpose existing connections / can not create new connections for a service if max connections per service limit is reached for that particular service. It gives a fair chance to all the services configured in the shared pool.   Pre-requisites for running the sample code: Oracle JDBC driver 12.2 (ojdbc8.jar) UCP 12.2 (ucp.jar) Oracle Database 12c Release 2 (12.2) JDK8 The setup required to run Shared Pool sample code: An environment with one CDB and two PDBs. Following example uses a CDB service name of 'cdb_root_app_service_name' and PDB service names of 'pdb1_app_service_name' and 'pdb2_app_service_name'. UCP Config XML (SharedPool_config.xml) that contains all details about datasources using the shared pool. Refer to \oracle\ucp\xml\configuration.xsd present in ucp.jar. Specify the location of the UCP Config XML file through a system property "oracle.ucp.jdbc.xmlConfigFile". Below is the sample UCP Config XML file used in sample code. SharedPool_config.xml Refer to the code sample for configuring a Shared Pool using above UCP XML configuration file. SharedPoolConfig.java import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import oracle.ucp.UniversalConnectionPool; import oracle.ucp.UniversalConnectionPoolException; import oracle.ucp.admin.UniversalConnectionPoolManagerImpl; import oracle.ucp.jdbc.PoolDataSource; import oracle.ucp.jdbc.PoolDataSourceFactory; import oracle.ucp.jdbc.oracle.OracleJDBCConnectionPoolStatistics; public class SharedPoolConfig { static String xmlFileURI = "file:/test/ucp/config/SharedPool_config.xml"; public static void main(String[] args) { try { // Java system property to specify the location of xml configuration file System.setProperty("oracle.ucp.jdbc.xmlConfigFile", xmlFileURI); // Get the datasource instance, named as "pds1" in xml config file PoolDataSource pds1 = PoolDataSourceFactory.getPoolDataSource("pds1"); Connection pds1Conn = pds1.getConnection(); // Run a SQL query to test the connection testConnection(pds1Conn); pds1Conn.close(); // Get the datasource instance, named as "pds2" in xml config file PoolDataSource pds2 = PoolDataSourceFactory.getPoolDataSource("pds2"); Connection pds2Conn = pds2.getConnection(); testConnection(pds2Conn); pds2Conn.close(); final int COUNT = 5; Connection conn[] = new Connection[COUNT]; // Borrow 5 connections of pdb1 service using datasource pds1 for (int i = 0; i < COUNT; i++) { conn[i] = pds1.getConnection(); } // Return the connections to pool for (int i = 0; i < COUNT; i++) { if (conn[i] != null) conn[i].close(); } // Borrow 5 connections of pdb2 service using datasource pds2 for (int i = 0; i < COUNT; i++) { conn[i] = pds2.getConnection(); } // Return the connections to pool for (int i = 0; i < COUNT; i++) { if (conn[i] != null) conn[i].close(); } // Print UCP pool statistics for pool1 final String poolName = "pool1"; UniversalConnectionPool pool = UniversalConnectionPoolManagerImpl .getUniversalConnectionPoolManager().getConnectionPool(poolName); OracleJDBCConnectionPoolStatistics stats = (OracleJDBCConnectionPoolStatistics) pool .getStatistics(); System.out.println("Pool : " + poolName + " --> Connection # Repurpose Count = " + stats.getConnectionRepurposeCount()); System.out.println("Available Connection Count = " + stats.getAvailableConnectionsCount()); System.out.println("Borrowed Connection Count = " + stats.getBorrowedConnectionsCount()); } catch (SQLException sqlexc) { sqlexc.printStackTrace(); } catch (UniversalConnectionPoolException ucpEx) { ucpEx.printStackTrace(); } } static void testConnection(Connection conn) throws SQLException { Statement stmt = null; ResultSet rs = null; try { stmt = conn.createStatement(); String query = "select sys_context('userenv', 'instance_name')," + "sys_context('userenv', 'server_host')," + "sys_context('userenv', 'service_name')," + "sys_context('userenv', 'db_unique_name')" + ",user" + " from dual"; rs = stmt.executeQuery(query); if (rs.next()) { String serviceName = rs.getString(3); String dbName = rs.getString(4); String userName = rs.getString(5); System.out.println("Connection Db name from sys context=" + dbName); System.out.println("Connection Svc name from sys context=" + serviceName); System.out.println("Connection user Name : " + userName); } } catch (SQLException sqlexc) { throw sqlexc; } finally { if (rs != null) rs.close(); if (stmt != null) stmt.close(); } } } Also see blog which explains how we can use UCP Multi-Tenant Shared Pool feature with Tomcat.

Oracle Universal Connection Pool (UCP) adds a new capability called as "Shared Pool" in the Oracle Database 12c Release 2 (12.2). 'Shared Pool' is particularly targeted for customers leveraging...

Using Coherence with Universal Connection Pool (UCP)

Coherence is an in-memory data grid solution that addresses the issues of working with distributed objects in memory (cache). Amongst its many features, Pluggable Cache Stores allows caching (i.e., load/store) contents from any persistence layer. This way, the cache abstracts content persistence and loading through the CacheStore interface to be implemented by the user. The synchronization of updates, also decoupled from the persistence layer, is governed by the following strategies: Refresh-Ahead / Read-Through: whether you want data to be loaded in the cache before being actually requested vs staleness of the data in the cache Write-Behind / Write-Through: whether you expect better response time by doing the actualization of the data asynchronous vs immediate persistence of the change Relational databases is the most common option for persistence, but its selection forces you to define an object-relational mapping from Java classes to the underlying relational model (using for example: hibernate, TopLink or any ad-hoc JPA implementation). But that is only half of the job to be done. It is also paramount to define how you will manage the connections to execute this persistence. The following diagram depicts how Coherence persists in an RDBMS through the CacheStore interface: The CacheStore is responsible for handling the connection to the database. Not only in terms of connection creation/pooling but also in terms of how to detect changes in the instances of the database it is connected to. Connection management is critical when you are looking for extreme performance and high availability; hence it’s critical for the CacheStore to handle connection acquirement properly. Oracle Universal Connection Pool (UCP) provides you not only all the intrinsic advantages of connection pooling (connection re-use, management, availability, purge, etc.) but it also leverages the possibility to use all the features you have when connected to a RAC environment: Runtime Connection Load Balancing (RCLB) Fast Connection Failover (FCF) Transaction Affinity Built-in support for Database Resident Connection Pooling (DRCP) and Application Continuity (AC) By taking this approach, you are not only setting the relationship between your cache and your persistence layer. You are also optimizing the configuration and management with your underlying RAC, by exploiting all its features in terms of connection handling. For more information about UCP, please refer to: Introduction to UCP. In a previous article we already discussed these features and how to exploit them from a JBoss Web application: Using Universal Connection Pooling (UCP) with JBoss AS In this article we are going to show you how to use UCP from Coherence using Pluggable Cache Stores, hence also making it available for any type of Coherence client. 1. Download and install coherence standalone, UCP and Oracle JDBC Driver. You can skip this step if you already have coherence.jar, ucp.jar and ojdbc7.jar. Download “Coherence Stand-Alone Install” from this location. Unzip the downloaded file. Run the universal installation jar: java -jar fmw_12.2.1.0.0_coherence.jar During install, select an Oracle Home location. In this location you will find the coherence jar that we will be using during this demo:  ORACLE_HOME/coherence/lib/coherence.jar. Download UCP and Oracle JDBC Driver (ucp.jar and ojdbc.jar) UCP JDBC For this sample, I copied these jars are in the home directory (~) and coherence.jar to ~/ucpcoherence dir: ~/ucpcoherence/coherence.jar ~/ucp.jar ~/ojdbc8.jar 2. Configure Cache and CacheStore. You need to indicate coherence the scheme for your cache. The scheme will define its behavior (such as if it’s local, replicated or distributed, etc.). For our configuration, these are the most important things to understand: cache-mapping: this will indicate coherence what cache-names (ids) will match to this specific type (scheme). In our sample we’ll create cache “test1” to match it with “test*”, which in turns will associate with scheme-name “example-distributed” and under this scheme-name we’ll define our cache-scheme. class-name (in cachestore-scheme): here we will inject into our distributed cache the name of the class that will handle the persistence (load / loadAll / store / storeAll / erase / eraseAll) operations in our cache. In our case it will be ucp_samples.EmployeeCacheStore, which we will define later in this article. init-params (in class-scheme): here you can specify values that will be used in the constructor of our class. ~/ucpcoherence/example-ucp.xml: <?xml version="1.0"?> <!DOCTYPE cache-config SYSTEM "cache-config.dtd"> <cache-config> <caching-scheme-mapping> <cache-mapping> <cache-name>test*</cache-name> <scheme-name>example-distributed</scheme-name> </cache-mapping> </caching-scheme-mapping> <caching-schemes> <distributed-scheme> <scheme-name>example-distributed</scheme-name> <service-name>DistributedCache</service-name> <backing-map-scheme> <read-write-backing-map-scheme> <internal-cache-scheme> <local-scheme />  </internal-cache-scheme> <cachestore-scheme> <class-scheme>   <class-name>ucp_samples.EmployeeCacheStore</class-name> <init-params> <init-param> <param-type>java.lang.String</param-type> <param-value>jdbc:oracle:thin:@//localhost:1521/cdb1</param-value> </init-param> <init-param> <param-type>java.lang.String</param-type> <param-value>scott</param-value> </init-param> <init-param> <param-type>java.lang.String</param-type> <param-value>tiger</param-value> </init-param> </init-params> </class-scheme> </cachestore-scheme> </read-write-backing-map-scheme> </backing-map-scheme> <autostart>true</autostart> </distributed-scheme> </caching-schemes> </cache-config> 3. Provide CacheStore Implementation. As configured in previous file, you need to provide an implementation for CacheStore that will execute its methods when objects in the cache are added or requested (it will store or load if the object is not on the cache). We provide an implementation for the load method and the constructor of the class (which shows how to use UCP). You can define the behavior for the rest of the methods as part of your test. Notice that the values for UCP can be changed/monitored through JMX as it was explained in this article. To check more in how to use UCP with Hibernate you can check in this article. public class EmployeeCacheStore implements CacheStore { private PoolDataSource pds = null; private int INITIAL_POOL_SIZE = 5; private Connection getConnection() throws SQLException { return pds.getConnection(); } /** * Constructor for the CacheStore, parses initial values and sets the * connection pool. * * @param url * @param user * @param password */ public EmployeeCacheStore(String url, String user, String password) { try { // Create pool-enabled data source instance  pds = PoolDataSourceFactory.getPoolDataSource(); // set the connection properties on the data source pds.setConnectionFactoryClassName(     "oracle.jdbc.pool.OracleDataSource"); pds.setURL(url); pds.setUser(user); pds.setPassword(password); // Override pool properties pds.setInitialPoolSize(INITIAL_POOL_SIZE); pds.setConnectionPoolName(this.getClass().getName()); } catch (SQLException e) { e.printStackTrace(); } } /** * When an object is not in the cache, it will go through cache store to * retrieve it, using the connection pool. In this sample we execute a * manual object relational mapping, in a real-life scenario hibernate, or * any other ad-hoc JPA implementation should be used. */ @Override public Employee load(Object employeeId) { Employee employee = null; try { PreparedStatement ps = getConnection().prepareStatement("select * from employees where employee_id = ?"); ps.setObject(1, employeeId); ResultSet rs = ps.executeQuery(); if (rs.next()) { employee = new Employee(); employee.setEmployeeId(rs.getInt("employee_id")); employee.setFirstName(rs.getString("first_name")); employee.setLastName(rs.getString("last_name")); employee.setPhoneNumber(rs.getString("phone_number")); } ps.close(); rs.close();   } catch (SQLException e) { e.printStackTrace(); } return employee; } } 4. Provide the Java Bean. This is the class that will live in our cache. It’s a simple Java Bean which needs to implement Serializable in order to be able to be shared across the nodes of the cache. In a real-life scenario you will want to implement coherence’s PortableObject instead of only Serializable, so you will be using coherence ultra-optimized mechanisms to share/store objects: Portable Object Format Serialization (POF). Note: getters/setters/constructors are removed to be easier to read this example. Also note that your implementation of toString() is what you are going to see in coherence’s console. public class Employee implements Serializable { int employeeId; String firstName; String lastName; String email; String phoneNumber; Date hireDate; String jobId; @Override public String toString() { return getLastName() + ", " + getFirstName() + ": " + getPhoneNumber(); } } 5. Configure Operational File. In order to provide some specific operational values for your cache, you will need to provide the following operational configuration file. Take particular attention for the name of the cluser “cluster_ucp”, the address/port you will be synchronizing with other nodes of the cluster “localhost:6699” and the name of the configuration file you will use (you set this through the system property parameter “tangosol.coherence.cacheconfig=example-ucp.xml”, defined in 2nd step. Note: since version 12.2.1 you no longer need to use prefix “tangosol”. ~/ucpcoherence/tangosol-coherence-override.xml <?xml version='1.0'?> <!DOCTYPE coherence SYSTEM "coherence.dtd"> <coherence> <cluster-config> <member-identity> <cluster-name>cluster_ucp</cluster-name> </member-identity> <unicast-listener> <address>localhost</address> <port>6699</port> <time-to-live>0</time-to-live> <well-known-addresses> <socket-address id="1"> <address>localhost</address> <port>6699</port> </socket-address> </well-known-addresses> </unicast-listener> </cluster-config> <services> <service id="3"> <service-type>DistributedCache</service-type> <service-component>PartitionedService.PartitionedCache</service-component> </service> </services> <configurable-cache-factory-config> <class-name system-property="tangosol.coherence.cachefactory">com.tangosol.net.ExtensibleConfigurableCacheFactory</class-name> <init-params><init-param><param-type>java.lang.String</param-type> <param-value system-property="tangosol.coherence.cacheconfig">example-ucp.xml</param-value> </init-param></init-params> </configurable-cache-factory-config> </coherence> 6. Start Cache nodes and client. From command line you can start the nodes of your cache through DefaultCacheServer class and the following parameters: (from ~/ucpcocherence, being ./ucp_samples/bin the output folder for your eclipse project or the place where you have your compiled custom classes) ../jdk1.8.0_60/jre/bin/java -Dtangosol.coherence.override=tangosol-coherence-override.xml -Dtangosol.coherence.cluster=cluster_ucp -cp coherence.jar:../ucp.jar:../ojdbc8.jar:./ucp_samples/bin com.tangosol.net.DefaultCacheServer Note: as stated before, since 12.2.1 you no longer need to use tangosol prefix, it will also work with coherence.cluster. You can check by the output the name of the cluster (cluster_ucp), the addresses it’s listening (localhost:6699) and the name of the member you just started (Id=1).   If you issue the same command on a separate process to start 2nd node: You can check member id = 2 joining the cluster (it shows in both processes). This way now you have 2 nodes working for this cluster. Lastly, you start the client for the cluster (which indeed is a new member itself) using CacheFactory class and running this command: ../jdk1.8.0_60/jre/bin/java -Dtangosol.coherence.override=tangosol-coherence-override.xml -Dtangosol.coherence.cluster=cluster_ucp -cp coherence.jar:../ucp.jar:../ojdbc8.jar:./ucp_samples/bin com.tangosol.net.CacheFactory Note that if you wouldn’t want your client to be a storage member you can provide the option -Dtangosol.coherence.distributed.localstorage=false to achieve this behavior. This can also be done via configuration.   You see member id = 3 with command line option to interact with the cache. The first thing you need to do is to start a cache with the same scheme as the one we defined. In order to do that you use the same name pattern we defined in cache-mapping using a name matching “test*”. So you issue:   cache test1   And you will notice now that the prompt is with this cache   Map (test1):     Now you try to load the first object by issuing:   get 100     You’ll notice for each new object loaded in the cache the additional time it takes to read its value from the database and you’ll see the output of the object retrieved in the console (executing Employee.toString() method). Run again get 100 and you’ll notice the difference in the response time of using an object that’s already in the cache. 7. Interact with the cache from Java code To interact with the cache from a java class it’s even easier. The only thing you should do is add the VM parameter -Dtangosol.coherence.override=tangosol-coherence-override.xml (pointing to the same one that started the nodes) to the following code: public class EmployeeQueryCache { public static void main(String[] args) { // define cache NamedCache cache = CacheFactory.getCache("test1"); // retrieve object Employee employee = (Employee) cache.get(101); // update it employee.setPhoneNumber("(650)-500-5000"); // print in console System.out.println(employee); // store it cache.put(101, employee); } }   Since coherence 12.2.1 the interface NamedCache supports generics, so you might be able to update previous code with: NamedCache<Integer, Employee> cache = CacheFactory.getTypedCache("test1", TypeAssertion.withoutTypeChecking());    

Coherence is an in-memory data grid solution that addresses the issues of working with distributed objects in memory (cache). Amongst its many features, Pluggable Cache Stores allows caching (i.e.,...

Get Oracle JDBC drivers from the Oracle Maven Repository - NetBeans, Eclipse & Intellij

New !! Oracle 19.3 JDBC drivers and Universal Connection Pool (ucp.jar) are on Maven Central Repository !! Refer to the blog for more details.  div.codeexample{white-space: pre-wrap;/*width: 635px !important;*/font-family: "Consolas", "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important;color: black !important;font-size: 1em;line-height: 1em;margin: 0 0 0 0px;padding: 0 0 0 15px;border-left-style:solid;border-color:#98bf21;white-space:wrap;} Oracle JDBC drivers and Universal Connection Pool (ucp.jar) are available in the Oracle Maven Repository. The following versions of the JDBC drivers and UCP are available:  19.3.0.0, 18.3.00, 12.2.0.1, 12.1.0.2, 12.1.0.1, and 11.2.0.4.  In addition, companion jars such as simplefan.jar, ons.jar, orai18n.jar, xdb6.jar etc., required for specific features are also available. This blog outlines the steps for successfully downloading the required version of these jar files while using NetBeans, Eclipse and Intellij IDEs. Please refer to Get Oracle JDBC drivers and UCP from the Oracle Maven Repository (without IDEs) for a step-by-step instructions to create settings.xml, settings-security.xml and pom.xml required to download JDBC drivers and UCP. Steps to create a Maven project and download the Oracle JDBC drivers and UCP. Create a New Project: Create a maven Java application as per the screenshots Update pom.xml: Include GAV(Group ID, Artifact Id, Version ID) for ojdbc7.jar and/or ucp.jar in pom.xml as shown below. Refer to a sample pom.xml in the blog

New !! Oracle 19.3 JDBC drivers and Universal Connection Pool (ucp.jar) are on Maven Central Repository !! Refer to the blog for more details.  Oracle JDBC drivers and Universal Connection Pool (ucp.ja...

Get Oracle JDBC drivers and UCP from Oracle Maven Repository (without IDEs)

div.codeexample{white-space: pre-wrap;/*width: 635px !important;*/font-family: "Consolas", "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important;color: black !important;font-size: 1em;line-height: 1em;margin: 0 0 0 0px;padding: 0 0 0 15px;border-left-style:solid;border-color:#98bf21;white-space:wrap;} New !! Oracle 19.3 JDBC drivers and Universal Connection Pool (ucp.jar) are on Maven Central Repository !! Refer to the blog for more details.  The Oracle JDBC drivers and Universal Connection Pool (UCP) are also available on the Oracle Maven Repository. The following versions are available on the Oracle Maven repository:  19.3.0.0., 18.3.0.0, 12.2.0.1, 12.1.0.2, 12.1.0.1, and 11.2.0.4. In addition, the companion jars including simplefan.jar, ons.jar, orai18n.jar etc., required for specific features are also available.  GAV details for ojdbc8.jar from 19.3.0.0: <groupId>com.oracle.jdbc</groupId> <artifactId>ojdbc8</artifactId> <version>19.3.0.0</version> Here is the list of artifacts available on Oracle Maven repository: <groupid> is the same for all versions. Make sure to change the <artifactid> and <version> to the exact jar that you want to download. ojdbc8.jar: JDBC driver certified with JDK8 ucp.jar: Universal Connection Pool to use with JDK8 orai18n.jar: Classes for NLS or Internalization support simplefan.jar: Java APIs for subscribing to RAC events via ONS ons.jar: Oracle Notification Services (ONS) daemon osdt_core.jar, osdt_cert.jar, oraclepki.jar: Required to access Oracle Wallets from Java xdb6.jar and xmlparserv2.jar: To support standard java.sql.SQLXML interface ojdbc8_g.jar: Same as ojdbc8.jar except compiled with "javac -g" and contains tracing code ojdbc8dms.jar: Same as ojdbc8.jar, except that it supports DMS and limited java.util.logging calls ojdbc8dms_g.jar: Same as ojdbc8_g.jar except that it contains instrumentation to support DMS. Refer to Get Oracle JDBC drivers and UCP from the Oracle Maven Repository - NetBeans, Eclipse, Intellij for steps while using NetBeans, Eclipse, and Intellij.  Follow the steps mentioned in this blog to successfully download the required version of Oracle JDBC drivers, UCP, or any other companion jars.  Step #1: Download Maven Install Apache Maven if you do not have it already.  Update M2_HOME and JAVA_HOME environment variables: Make sure to update M2_HOME to point to the path where Maven is installed and JAVA_HOME to point to the location where the JDK is installed. If you require a specific Java version then set it using these system arguments. -Dmaven.compiler.source=1.8 -Dmaven.compiler.target=1.8 Step#2: Registering with the Oracle Maven site The Oracle Maven repository requires a valid user registration and the user should also accept the terms and conditions (T&Cs) by logging into http://maven.oracle.com. This username will be used in settings.xml. Step#3: Create a settings-security.xml Create a settings-security.xml file that holds the master password information under the local  maven repository (Example: {USER_HOME}/.m2/ ) that is usually under the user home directory.  For example: If the username is test then the path will be Unix : /home/test/.m2/settings.xml Windows : C:\Users\test\.m2\settings.xml Use the following commands to generate a master password and add the output of this command to settings-security.xml as shown in the sample mvn -encrypt-master-password <any_master_password> or mvn -emp <any_master_password> Sample settings-security.xml: <settingsSecurity> <master>{By8wW7YcTxAHof0MF4Z3wPKboywhGJvxHD9m0NvHA2U=}</master> </settingsSecurity> Step#4: Create a settings.xml file The settings.xml file is required for downloading Oracle JDBC drivers and UCP.  Encrypt the user password (the one used to accept T&Cs on http://maven.oracle.com) before using it in settings.xml.  Use the following commands to encrypt the user password and update settings.xml as shown in the sample mvn -encrypt-password <oracle_registered_password> or mvn -ep <oracle_registered_password> Sample settings.xml: <settings>   <proxies>     <proxy> <active>true</active> <protocol>http</protocol> <host>proxy.mycompany.com</host> <nonProxyHosts>mycompany.com</nonProxyHosts> </proxy> </proxies> <servers> <server> <id>maven.oracle.com </id> <username>firstname.lastname@test.com</username> <password>{pnwmhVnzdM8H3UAneUKLmaHGZCoaprbMQ/Ac5UktvsM=}</password> <configuration> <basicAuthScope> <host>ANY </host> <port>ANY </port> <realm>OAM 11g </realm> </basicAuthScope> <httpConfiguration> <all> <params> <property> <name>http.protocol.allow-circular-redirects </name> <value>%b,true </value> </property> </params> </all> </httpConfiguration> </configuration> </server> </servers> </settings> Create a project and test Maven download Step#1: Create a pom.xml and specify GAV for JDBC drivers and UCP Add the following GAV details for downloading JDBC driver or UCP from the required version. <dependencies> <dependency> <groupId>com.oracle.jdbc</groupId> <artifactId>ojdbc8</artifactId> <version>19.3.0.0</version> </dependency> </dependencies> <repositories> <repository> <id>maven.oracle.com</id> <name>oracle-maven-repo</name> <url>https://maven.oracle.com</url> <layout>default</layout> <releases> <enabled>true</enabled> <updatePolicy>always</updatePolicy> </releases> </repository> </repositories> <pluginRepositories> <pluginRepository> <id>maven.oracle.com</id> <name>oracle-maven-repo</name> <url>https://maven.oracle.com</url> <layout>default</layout> <releases> <enabled>true</enabled> <updatePolicy>always</updatePolicy> </releases> </pluginRepository> </pluginRepositories> Step#2: Create 'src' and 'target' directories All Java source files must be placed under src directory. Example: /src/main/java/<foldername>/<filename> In addition, create a target directory where the compiled classes will be placed. Step#3: Compile the Maven Project Use the following commands to compile or install or package to test the download. "mvn –s settings.xml package" or "mvn -s settings.xml compile" or "mvn -s settings.xml install" The output of a successful download will look as shown below: ➜  test mvn -s settings.xml compile [INFO] Scanning for projects... [INFO]                                                                          [INFO] ------------------------------------------------------------------------ [INFO] Building jdbc-driver-prod 1.0-SNAPSHOT [INFO] ------------------------------------------------------------------------ Downloading: https://maven.oracle.com/com/oracle/jdbc/ojdbc8/12.2.0.1/ojdbc8-12.2.0.1.pom Downloaded: https://maven.oracle.com/com/oracle/jdbc/ojdbc8/12.2.0.1/ojdbc8-12.2.0.1.pom (7 KB at 1.0 KB/sec) Downloading: https://maven.oracle.com/com/oracle/jdbc/ucp/12.2.0.1/ucp-12.2.0.1.pom Downloaded: https://maven.oracle.com/com/oracle/jdbc/ucp/12.2.0.1/ucp-12.2.0.1.pom (6 KB at 3.0 KB/sec) .... [INFO] ------------------------------------------------------------------------ [INFO] BUILD SUCCESS [INFO] ------------------------------------------------------------------------ [INFO] Total time: 16.248 s [INFO] Finished at: 2017-04-28T15:25:49-07:00 [INFO] Final Memory: 11M/202M [INFO] -----------------------------   ➜  maven-repo-prod mvn -s settings.xml package [INFO] Scanning for projects... [INFO]                                                                          [INFO] ------------------------------------------------------------------------ [INFO] Building jdbc-driver-prod 1.0-SNAPSHOT [INFO] ------------------------------------------------------------------------ Downloading: https://maven.oracle.com/com/oracle/jdbc/ojdbc8/18.3.0.0/ojdbc8-18.3.0.0.pom Downloaded: https://maven.oracle.com/com/oracle/jdbc/ojdbc8/18.3.0.0/ojdbc8-18.3.0.0.pom (7 KB at 1.2 KB/sec) Downloading: https://maven.oracle.com/com/oracle/jdbc/ucp/18.3.0.0/ucp-18.3.0.0.pom Downloaded: https://maven.oracle.com/com/oracle/jdbc/ucp/18.3.0.0/ucp-18.3.0.0.pom (6 KB at 1.2 KB/sec) Downloading: https://maven.oracle.com/com/oracle/jdbc/ojdbc8/18.3.0.0/ojdbc8-18.3.0.0.jar Downloading: https://maven.oracle.com/com/oracle/jdbc/ucp/18.3.0.0/ucp-18.3.0.0.jar Downloaded: https://maven.oracle.com/com/oracle/jdbc/ucp/18.3.0.0/ucp-18.3.0.0.jar (1366 KB at 218.5 KB/sec) Downloaded: https://maven.oracle.com/com/oracle/jdbc/ojdbc8/18.3.0.0/ojdbc8-18.3.0.0.jar (4065 KB at 224.8 KB/sec) [INFO] [INFO] [INFO] --- maven-jar-plugin:2.4:jar (default-jar) @ jdbc-driver-prod --- [INFO] ------------------------------------------------------------------------ [INFO] BUILD SUCCESS [INFO] ------------------------------------------------------------------------ [INFO] Total time: 30.240 s [INFO] Finished at: 2018-09-28T16:24:42-07:00 [INFO] Final Memory: 16M/54M [INFO] ------------------------------------------------------------------------ Step#4: Check the downloaded JDBC drivers and UCP If the build was successful, then the JARs and POMs will be downloaded onto the local maven repository at ~/.m2/repository/com/oracle/jdbc/ojdbc8/19.3.0.0/ . Note that ojdbc8.jar will download ucp.jar as well. Each download has its own JAR and POM files. Sample output: ➜  jdbc pwd /Users/test/.m2/repository/com/oracle/jdbc ➜  jdbc ls -lt total 0 drwxr-xr-x  3 nbsundar  staff  96 Sep 28 16:24 ucp drwxr-xr-x  3 nbsundar  staff  96 Sep 28 13:44 ojdbc8

New !! Oracle 19.3 JDBC drivers and Universal Connection Pool (ucp.jar) are on Maven Central Repository !! Refer to the blog for more details.  The Oracle JDBC drivers and Universal Connection Pool...

OOW SF 2015 - Session Recommendations

JavaOne SF 2015  Session recommendationsHigh Availability with Java EE Containers, JDBC, and Java Connection Pools [BOF7732]Monday, Oct 26, 8:00 p.m. | Parc 55—MissionImplement Cloud Data Services with Java 8 Nashorn [CON4405]Tuesday, Oct 27, 4:00 p.m. | Hilton—Continental Ballroom 1/2/3Java Connection Pool Performance and Scalability with Wait-Free Programming [CON2158]Wednesday, Oct 28, 4:30 p.m. | Hilton—Continental Ballroom 1/2/3OOW SF 2015 - Session recommendationsJava Virtual Machine Cookbook [UGF2720]Sunday, Oct 25, 9:00 a.m. | Moscone West—3011Next-Generation Database:  Implement Cloud Data Services with Java 8 Nashorn  [CON8461]Monday, Oct 26, 5:15 p.m. | Moscone South—308Next-Generation Database: Java Connection Pool for Multitenant and Sharded Databases [CON8460]Monday, Oct 26, 2:45 p.m. | Moscone South—308Integrate Master Data with Big Data on Hadoop and Spark [CON8459]Wednesday, Oct 28, 3:00 p.m. | Moscone South—308Next-Gen Database Enhancements for Java Application Performance and Scalability [CON10310]Thursday, Oct 29, 2:30 p.m. | Moscone South—307Dialog with the Oracle Database Java Developers and Architects [MTE9501]Tuesday, Oct 27, 7:15 p.m. | Moscone South—305

JavaOne SF 2015  Session recommendations High Availability with Java EE Containers, JDBC, and Java Connection Pools [BOF7732] Monday, Oct 26, 8:00 p.m. | Parc 55—Mission Implement Cloud Data Services...

Using Universal Connection Pooling (UCP) with JBoss AS

Using Universal Connection Pooling (UCP) with JBoss AS Even though WebLogic with Active GridlLink is Oracle's suggested approach to deploy Java applications that use Oracle Real Applications Clusters (RAC), there might be scenarios in which you can't make that choice (e.g.: certification issues, licensing, library dependency, etc.). Application servers and their database connection pool mechanisms might end up being a bottleneck in the architecture of your applications and a critical component to provide performance, scalability and high-availability. UCP, besides providing connection pooling and all its intrinsic benefits, leverages features specific from RAC. These features are: Runtime Connection Load Balancing (RCLB) Fast Connection Failover (FCF) Transaction Affinity Built-in support for Database Resident Connection Pooling (DRCP) and Application Continuity (AC) You won't be able to exploit this features by using out of the box JBoss' connection pools. In this simple example we will show you how to configure UCP to be used in a JBoss deployed application, wrapping UCP around a Singleton Enterprise Java Bean (EJB) and using a Servlet as client to the wrapper. We will also show you how to monitor and administer UCP using JBoss JMX-Console without needing extra configuration, coding or deployment. In an ideal scenario the application server would provide us the possibility to set a custom implementation of a connection pooling (by implementing an interface, for example), but that is not the case with JBoss. In JBoss you can configure all the necessary information to create a connection (driver, url, user, password, etc) and some configuration for the pool (minimum size, maximum size, etc), but you can't configure the class that is going to provide the implementation for connection pooling. There's a workaround for that in this article: https://blogs.oracle.com/dev2dev/entry/how_to_use_oracle_universal1 , which explains how to set a UCP data-source using Spring). But for both scenarios you're declaring beans, or wrappers, to contain the UCP configuration. 1. Download and start JBoss AS: You can download it from this site: http://jbossas.jboss.org/downloads/. For this sample we used “JBoss AS 6.1.0.Final”: http://download.jboss.org/jbossas/6.1/jboss-as-distribution-6.1.0.Final.zip. After unzipping the file you should set your JAVA_HOME env variable to an existing JDK directory (jdk7, it won't work with jdk8). To start the app server you go to the unzipped directory /bin and run standalone.sh or standalone.bat, depending on your OS (you can skip this step if you install JBoss Developer Studio. You can check successful start-up by opening a browser to http://localhost:8080/ 2. Download and install JBoss Developer Studio: This is also an optional step, you can download JBoss Developer Studio from http://www.jboss.org/products/devstudio/download/ after unzipping you start it by running jbdevstudio executable. This is an extension of Eclipse with JBoss specific plugins installed. 3. Configure JBoss AS in JBoss Developer Studio: In “servers” view choose “new” / “server”, select “JBoss Community, JBoss AS 6.x”, select “Home Directory” to the previously downloaded JBoss AS and leave default values. After this step you can start/stop application server from JBoss Developer Studio and deploy / un-deploy applications by selecting “Add and Remove..” in Server's contextual menu. 4. Add ucp.jar and ojdbc.jar both to Jboss's runtime: Add both jars in (JBOSS_HOME/common/lib) and in project's build path (project / properties / Java Build Path / libraries). You could also deploy this jars as specifics for the app, off course. 5. Create the Singleton Wrapper for UCP as this:     @Startup     @Singleton(name = "UcpDemoSingletonWrapper")     public class UcpDemoSingletonWrapper {         @Resource(name = "connectionFactoryClassName")         String connectionFactoryClassName;             @Resource(name = "url")         String url;         @Resource(name = "user")         String user;         @Resource(name = "password")         String password;         @Resource(name = "initialPoolSize")         Integer initialPoolSize;         /**         * Initialize method for the Singleton. This wrapper could also implement         * JMX APIs to be able to be managed through the JMX Console (or any other         * JMX-compliant Tool)         */         @PostConstruct         void init() {             try {                 // Retrieve values from bean's configuration                 pds.setConnectionFactoryClassName(connectionFactoryClassName);                 pds.setURL(url);                 pds.setUser(user);                 pds.setPassword(password);                 // Override example's desired pool properties                 // Will be showed through JMX-Console for this example                 pds.setInitialPoolSize(initialPoolSize);                 pds.setConnectionPoolName(this.getClass().getName());             } catch (Exception e) {                 e.printStackTrace();             }         }         // Wrapped pool         private PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();         /**         * Retrieves connections using wrapped pool         * @return a pooled connection         * @throws SQLException         */               public Connection getConnection() throws SQLException {                 /**             * Trivial implementation to retrieve connections. Logic intrinsic to             * the pool could be set here.             */             return pds.getConnection();         }     } 6. Create EJB configuration file (WEB-INF/ejb-jar.xml) with this values:     <ejb-jar xmlns="http://java.sun.com/xml/ns/javaee" version="3.1" metadata-complete="false">     <enterprise-beans>         <session>             <ejb-name>UcpDemoSingletonWrapper</ejb-name>             <env-entry>                 <env-entry-name>connectionFactoryClassName</env-entry-name>                 <env-entry-type>java.lang.String</env-entry-type>                 <env-entry-value>oracle.jdbc.pool.OracleDataSource</env-entry-value>             </env-entry>             <env-entry>                 <env-entry-name>url</env-entry-name>                 <env-entry-type>java.lang.String</env-entry-type>                 <env-entry-value>jdbc:oracle:thin:@//localhost:1521/cdb1</env-entry-value>             </env-entry>             <env-entry>                 <env-entry-name>user</env-entry-name>                 <env-entry-type>java.lang.String</env-entry-type>                 <env-entry-value>c##psilberk</env-entry-value>             </env-entry>             <env-entry>                 <env-entry-name>password</env-entry-name>                 <env-entry-type>java.lang.String</env-entry-type>                 <env-entry-value>oracle</env-entry-value>             </env-entry>             <env-entry>                 <env-entry-name>initialPoolSize</env-entry-name>                 <env-entry-type>java.lang.Integer</env-entry-type>                 <env-entry-value>5</env-entry-value>             </env-entry>             </session>     </enterprise-beans>     </ejb-jar> 7. Create the client as this sample servlet:     /**     * Simple client to show how to use wrapped ucp's EJB     *     */     @WebServlet("/UcpDemoServletClient")     public class UcpDemoServletClient extends HttpServlet {                 private static final long serialVersionUID = 1L;         @EJB UcpDemoSingletonWrapper ucpWrapper;             protected void doGet(HttpServletRequest request,             HttpServletResponse response) throws ServletException, IOException {             doPost(request, response);         }             protected void doPost(HttpServletRequest request,             HttpServletResponse response) throws ServletException, IOException {             try (                 // Get connection from pool, for this trivial example we avoid ORM                 Connection connection = ucpWrapper.getConnection();                 Statement statement = connection.createStatement();                 ResultSet resultSet = statement                     .executeQuery("select * from test_table")) {                 // Send output to response                 PrintWriter printWriter = response.getWriter();                 while (resultSet.next()) {                     printWriter.println(resultSet.getString(1));                 }             } catch (SQLException sqlException) {                 throw new ServletException(sqlException);             }         }     } 8. From Servers's view execute “Publish” and “Start” (or just execute from project's context menu “run in server”). 9. Administer EJB from JMX-Console: Login to http://localhost:8080/jmx-console/ Look for UCP's MBEANs: oracle.ucp.admin.UniversalConnectionPoolManagerMBean oracle.ucp.admin.UniversalConnectionPoolMBean  Invoke getConnectionPoolName (you will get what was setted on the EJB, “ucp_demo.UcpDemoSingletonWrapper"). Go through oracle.ucp.admin.UniversalConnectionPoolMBean and look for availableConnectionsCount. 10. Invoke servlet: Typing in a browser http://localhost:8080/ucp_jboss_demo_web_project/UcpDemoServletClient Check that after several executions the amount of available connections remains constant. Do the same but not closing the connections in the wrapper (remove from the try with resources block, avoiding connection.close()) Or just execute Connection connection = ucpWrapper.getConnection(); without closing the connection. You will check that available connections decreases until exception is thrown. Use JMX-console to administer life-cycle of UCP (start / stop / purge connection pool, etc.). A full-fledged test would include modifications in the RAC (shutting down/up nodes, checking load balance between them, validating transaction affinity, etc.), in these scenarios UCP would show all its capacities and features (as described at the beginning of this article).

Even though WebLogic with Active GridlLink is Oracle's suggested approach to deploy Java applications that use Oracle Real Applications Clusters (RAC), there might be scenarios in which you can't make...

UCP with Spring Framework

div.codeexample{white-space: pre-wrap;/*width: 635px !important;*/font-family: "Consolas", "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important;color: black !important;font-size: 1em;line-height: 1em;margin: 0 0 0 0px;padding: 0 0 0 15px;border-left-style:solid;border-color:#98bf21;white-space:wrap;}This article illustrates the steps to use Oracle Universal Connection Pool (UCP) with the Spring framework, using a sample application built using the JDBC template.Assume there is a simple table EMP in the database with a single column “name” that is loaded with employee information (i.e., employee names).Consider the following example DAO class in the package “test”:The following is an example of the Row mapper implementation class for the EMP table:The following class is the example of a java class that uses the JDBC Template for implementing the business logic:The XML configuration file should specify UCP's oracle.ucp.jdbc.PoolDataSourceImpl as the data source class along with relevant connection pool properties, such as the initial-pool-size, max-pool-size, etc. For this sample, the XML configuration file is named "HelloAppConf.xml".<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd"> <!-- Initialization for data source --> <bean id="dataSource" class="oracle.ucp.jdbc.PoolDataSourceImpl"> <property name="connectionFactoryClassName" value="oracle.jdbc.pool.OracleDataSource"/> <property name="URL" value="jdbc:oracle:thin:@//host:port/service_name"/> <property name="user" value="scott"/> <property name="password" value="tiger"/> <property name="maxPoolSize" value="10"/> <property name="initialPoolSize" value="5"/> </bean> <!-- Definition for EmpJDBCTemplate bean --> <bean id="EmpJDBCTemplate" class="test.EmpJDBCTemplate"> <property name="dataSource" ref="dataSource"/> </bean></beans>The main application code looks like the following. The application using the JDBC template will internally use the Universal Connection Pool (UCP) for connection check-outs and check-ins.

This article illustrates the steps to use Oracle Universal Connection Pool (UCP) with the Spring framework, using a sample application built using the JDBC template. Assume there is a simple table EMP...

Use UCP with Hibernate

div.codeexample{white-space: pre-wrap;/*width: 635px !important;*/font-family: "Consolas", "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important;color: black !important;font-size: 1em;line-height: 1em;margin: 0 0 0 0px;padding: 0 0 0 15px;border-left-style:solid;border-color:#98bf21;white-space:wrap;}Hibernate ORM is an object/relational mapping framework for Java. Out-of-box, Hibernate supports two open source connection pools C3P0 and Proxool.Universal Connection Pool (UCP) is Oracle’s feature-rich Java connection pool, replacing the Implicit Connection Cache (ICC), which has been de-supported in Oracle Database 12c. Besides standard connection pooling features, UCP has also been designed for scalability and high-availability during planned and unplanned database downtimes, with seamless support for Oracle Real Application Clusters (RAC), Active Data Guard (ADG) and Global Data Services (GDS).This article illustrates a couple of options for using Oracle Universal Connection Pool (UCP) with Hibernate. The code samples are for demonstration purpose only.Using UCP data source via JNDI lookupIf the target application can use JNDI to lookup a data source (for example, when using Tomcat), then users can specify a UCP data source for Hibernate to use, by using the Hibernate property "hibernate.connection.datasource". This can be done either declaratively in a Hibernate configuration file (XML or properties file), or programmatically on org.hibernate.cfg.Configuration.For example, after binding a UCP PoolDataSource in JNDI, users can specify in hibernate.cfg.xml:<hibernate-configuration> <session-factory> <property name="hibernate.connection.datasource"> java:comp/env/UCP_PoolDataSource_JNDI_NAME </property>All the UCP connection pool and data source configurations should be done on the PoolDataSource, before binding it in JNDI.Using UCP data source with Spring frameworkIf the target application can use Spring along with Hibernate, then UCP data sources can be configured in Spring configuration XML files.For example:<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd"> <!-- Initialization for data source --> <bean id="dataSource" class="oracle.ucp.jdbc.PoolDataSourceImpl"> <property name="connectionFactoryClassName" value="oracle.jdbc.pool.OracleDataSource"/> <property name="URL" value="jdbc:oracle:thin:@//host:port/service_name"/> <property name="user" value="scott"/> <property name="password" value="tiger"/> <property name="maxPoolSize" value="10"/> <property name="initialPoolSize" value="5"/> </bean> <!-- Definition for EmpJDBCTemplate bean --> <bean id="EmpJDBCTemplate" class="test.EmpJDBCTemplate"> <property name="dataSource" ref="dataSource"/> </bean></beans>Implementing UCP as a Hibernate ConnectionProviderIf the target application is standalone and cannot use the two options above, Hibernate provides the ConnectionProvider interface to integrate with a third-party JDBC connection provider. Users need to implement this interface and then specify the implementation class to Hibernate.The ConnectionProvider interface has changed with different Hibernate versions. As of Hibernate 4.x, the interface is org.hibernate.engine.jdbc.connections.spi.ConnectionProvider. We will use that version for illustration.The following is an example implementation of ConnectionProvider that plugs in a UCP data source. It has been tested using Hibernate 4.3.8. Note that the Hibernate 4.x version of the interface no longer provides the configure(Properties hibernateProperties) method, so this example simply overrides the DriverManagerConnectionProviderImpl which provides a similar method. An alternative is for the interface implementation to load the configuration properties from its own properties file or explicitly from the hibernate.properties file./* Copyright (c) 2015 Oracle and/or its affiliates. All rights reserved. */package oracle.ucp.hibernate.sample;import java.io.PrintWriter;import java.io.StringWriter;import java.io.Writer;import java.sql.Connection;import java.sql.SQLException;import java.util.Map;import java.util.logging.Logger;import oracle.ucp.UniversalConnectionPoolException;import oracle.ucp.admin.UniversalConnectionPoolManager;import oracle.ucp.admin.UniversalConnectionPoolManagerImpl;import oracle.ucp.jdbc.PoolDataSource;import oracle.ucp.jdbc.PoolDataSourceFactory; import org.hibernate.HibernateException;import org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl;import org.hibernate.engine.jdbc.connections.spi.ConnectionProvider;public class UCPConnectionProvider extends DriverManagerConnectionProviderImpl { private PoolDataSource pds; private static final Logger logger = Logger.getLogger(UCPConnectionProvider.class.getCanonicalName()); private static final String URL = "hibernate.ucp.url"; private static final String USER = "hibernate.ucp.user"; private static final String PASSWORD = "hibernate.ucp.password"; private static final String CONN_FACTORY = "hibernate.ucp.connectionFactoryClassName"; private static final String POOL_NAME = "hibernate.ucp.connectionPoolName"; private static final String MAX_POOL_SIZE = "hibernate.ucp.maxPoolSize"; private static final String MIN_POOL_SIZE = "hibernate.ucp.minPoolSize"; private static final String INITIAL_POOL_SIZE = "hibernate.ucp.initialPoolSize"; private static final String FAN_ENABLED = "hibernate.ucp.fastConnectionFailoverEnabled"; private static final String ONS_CONFIG = "hibernate.ucp.onsConfiguration"; private static final String CONN_VALIDATE = "hibernate.ucp.validateConnectionOnBorrow"; public UCPConnectionProvider() { try { pds = PoolDataSourceFactory.getPoolDataSource(); logger.finest("PoolDataSource initialized: " + pds); } catch (Exception exc) { logger.warning(getStackTraceString(exc)); } } public void configure(Map props) throws HibernateException { if(pds == null) throw new HibernateException("PoolDataSource was not initialized."); if (props == null) throw new HibernateException("Null configuration properties passed in."); try { logger.finest("Passed in properties: " + props); String tempval = (String) props.get(CONN_FACTORY); if (tempval != null) pds.setConnectionFactoryClassName(tempval); tempval = (String) props.get(URL); if (tempval != null) pds.setURL(tempval); tempval = (String) props.get(USER); if (tempval != null) pds.setUser(tempval); tempval = (String) props.get(PASSWORD); if (tempval != null) pds.setPassword(tempval); tempval = (String) props.get(POOL_NAME); if (tempval != null) pds.setConnectionPoolName(tempval); tempval = (String) props.get(MAX_POOL_SIZE); if (tempval != null) pds.setMaxPoolSize(Integer.parseInt(tempval)); tempval = (String) props.get(MIN_POOL_SIZE); if (tempval != null) pds.setMinPoolSize(Integer.parseInt(tempval)); tempval = (String) props.get(INITIAL_POOL_SIZE); if (tempval != null) pds.setInitialPoolSize(Integer.parseInt(tempval)); tempval = (String) props.get(FAN_ENABLED); if (tempval != null) pds.setFastConnectionFailoverEnabled(Boolean.parseBoolean(tempval)); tempval = (String) props.get(ONS_CONFIG); if (tempval != null) pds.setONSConfiguration(tempval); tempval = (String) props.get(CONN_VALIDATE); if (tempval != null) pds.setValidateConnectionOnBorrow(Boolean.parseBoolean(tempval)); } catch (SQLException sqlexc) { logger.warning(getStackTraceString(sqlexc)); } } public Connection getConnection() throws SQLException { final Connection conn = pds.getConnection(); logger.finest("Got connection " + conn + " from " + pds + ", number of available connections = " + pds.getAvailableConnectionsCount() + ", borrowed connections = " + pds.getBorrowedConnectionsCount()); return conn; } public void closeConnection(Connection conn) throws SQLException { conn.close(); logger.finest("Closed connection " + conn + " from " + pds + ", number of available connections = " + pds.getAvailableConnectionsCount() + ", borrowed connections = " + pds.getBorrowedConnectionsCount()); } public void close() { try { final UniversalConnectionPoolManager mgr = UniversalConnectionPoolManagerImpl.getUniversalConnectionPoolManager(); mgr.destroyConnectionPool(pds.getConnectionPoolName()); logger.finest("Closed PoolDataSource " + pds); } catch (UniversalConnectionPoolException exc) { logger.warning(getStackTraceString(exc)); } } public boolean supportsAggressiveRelease() { return true; } public boolean isUnwrappableAs(Class cls) { return false; } public <T> T unwrap(Class<T> cls) { return null; } private String getStackTraceString(Throwable exc) { final Writer stackTraceWriter = new StringWriter(1024); final PrintWriter pw = new PrintWriter(stackTraceWriter); exc.printStackTrace(pw); return stackTraceWriter.toString(); }}With UCP ConnectionProvider implementation ready, application can declaratively specify the implementation class name using the Hibernate property "hibernate.connection.provider_class".Below is an example hibernate.cfg.xml file including the UCP-specific configuration properties:<?xml version="1.0" encoding="utf-8"?><!DOCTYPE hibernate-configuration PUBLIC"-//Hibernate/Hibernate Configuration DTD 3.0//EN""http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"><!-- Copyright (c) 2015 Oracle and/or its affiliates. All rights reserved. --><hibernate-configuration> <session-factory> <property name="hibernate.connection.provider_class"> oracle.ucp.hibernate.sample.UCPConnectionProvider </property> <property name="hibernate.ucp.url"> jdbc:oracle:thin:@//host:1521/service_name </property> <property name="hibernate.ucp.connectionFactoryClassName"> oracle.jdbc.pool.OracleDataSource </property> <property name="hibernate.ucp.user">scott</property> <property name="hibernate.ucp.password">tiger</property> <property name="hibernate.ucp.maxPoolSize">2</property></session-factory></hibernate-configuration>ConclusionIn this article, we illustrated three options for using UCP with Hibernate. In all three cases, UCP will function as the connection pool for Hibernate’s JDBC connections and intercept JDBC connection checkouts and checkins without additional application code changes. This allows applications to utilize UCP’s full pooling capabilities including all the scalability and high-availability features for Oracle RAC, ADG, and GDS.

Hibernate ORM is an object/relational mapping framework for Java. Out-of-box, Hibernate supports two open source connection pools C3P0 and Proxool. Universal Connection Pool (UCP) is Oracle’s...

Write recovery code with Transaction Guard

div#blogentry{ font-family: Georgia, serif !important; color: #333; font-size: 1.1em; /* line-height: 1.1em; */ /* width: 635px !important; */ /* border-right-style: solid; */ } a:hover {color:red;} div#blogentry div.codeexample{ /*width: 635px !important;*/ white-space: pre; font-family: "Consolas", "Courier New", Courier, monospace !important; color: black !important; font-size: .7em; line-height: .7em; margin: 0 0 0 0px; padding: 0 0 0 15px; border-left-style:solid; border-color:#98bf21; } /* Line Number */ #LN { color: #BBBBBB; background-color:#FFFFFF } /* Link Colours */ #Classes A:link { color: #000000; } #Classes A:visited { color: #000000; } #Classes PRE { color: #000000; } /* Token Colours */ #CharacerLiteral { color: #FF00FF; } #StringLiteral { color: #FF00FF; } #SingleLineComment { color: #008000; } #FormalComment { color: #008000; } #MultiLineComment { color: #008000; } #Abstract { color: #0000FF ; font-weight: bold } #Boolean { color: #0000FF ; font-weight: bold } #Break { color: #0000FF ; font-weight: bold } #Byte { color: #0000FF ; font-weight: bold } #Case { color: #0000FF ; font-weight: bold } #Catch { color: #0000FF ; font-weight: bold } #Char { color: #0000FF ; font-weight: bold } #Class { color: #0000FF ; font-weight: bold } #Const { color: #0000FF ; font-weight: bold } #Continue { color: #0000FF ; font-weight: bold } #Default { color: #0000FF ; font-weight: bold } #Do { color: #0000FF ; font-weight: bold } #Double { color: #0000FF ; font-weight: bold } #Else { color: #0000FF ; font-weight: bold } #Extends { color: #0000FF ; font-weight: bold } #False { color: #0000FF ; font-weight: bold } #Final { color: #0000FF ; font-weight: bold } #Finally { color: #0000FF ; font-weight: bold } #Float { color: #0000FF ; font-weight: bold } #For { color: #0000FF ; font-weight: bold } #Goto { color: #0000FF ; font-weight: bold } #If { color: #0000FF ; font-weight: bold } #Implements { color: #0000FF ; font-weight: bold } #Import { color: #0000FF ; font-weight: bold } #InstanceOf { color: #0000FF ; font-weight: bold } #Int { color: #0000FF ; font-weight: bold } #Interface { color: #0000FF ; font-weight: bold } #Long { color: #0000FF ; font-weight: bold } #Native { color: #0000FF ; font-weight: bold } #New { color: #0000FF ; font-weight: bold } #Package { color: #0000FF ; font-weight: bold } #Private { color: #0000FF ; font-weight: bold } #Protected { color: #0000FF ; font-weight: bold } #Public { color: #0000FF ; font-weight: bold } #Return { color: #0000FF ; font-weight: bold } #Short { color: #0000FF ; font-weight: bold } #Static { color: #0000FF ; font-weight: bold } #Super { color: #0000FF ; font-weight: bold } #Switch { color: #0000FF ; font-weight: bold } #Synchronized { color: #0000FF ; font-weight: bold } #This { color: #0000FF ; font-weight: bold } #Throw { color: #0000FF ; font-weight: bold } #Throws { color: #0000FF ; font-weight: bold } #Transient { color: #0000FF ; font-weight: bold } #True { color: #0000FF ; font-weight: bold } #Try { color: #0000FF ; font-weight: bold } #Void { color: #0000FF ; font-weight: bold } #Volatile { color: #0000FF ; font-weight: bold } #While { color: #0000FF ; font-weight: bold } #StrictFP { color: #0000FF ; font-weight: bold } #IntegerLiteral { color: #000000 } #DecimalLiteral { color: #000000 } #HexLiteral { color: #000000 } #OctalLiteral { color: #000000 } #FloatPointLiteral { color: #000000 } Write recovery code with Transaction Guard When writing a servlet or any JDBC program, have you ever wondered how to recover from a database connection loss? A typical approach is to catch SQLRecoverableException exceptions and execute your recovery code. Such an exception is thrown by the driver when for example the socket is prematurely closed because of a temporary network outage, or if the database process has exited abnormally, etc. As the JavaDoc suggests, the first thing that usually happens in the recovery code is to close the already "dead" connection to cleanup resources in the driver, then open a new connection to the database, and finally re-execute the database request. The problem with this approach is that it may cause logical corruption by committing duplicate transactions. That's the problem Transaction Guard helps the developers solve. This article is an introduction to Transaction Guard, a new 12c Oracle Database feature, which helps developers write stronger recovery code. To illustrate the problem that Transaction Guard addresses, we will consider a basic code example. Let's assume we want to write a program that applies a 5% raise to all employees in the company. We'll use the EMP table to make things simple. Let's consider the following code snippet: Connection jdbcConnection = getConnection(); boolean isJobDone = false; while(!isJobDone) {   try {     // apply the raise (DML + commit):     giveRaiseToAllEmployees(jdbcConnection,5);     // no exception, we consider the job as done:     isJobDone = true;   } catch (SQLRecoverableException recoverableException) {     // if we get a SQLRecoverableException, we assume we can just retry the entire     // transaction so we'll just loop while isJobDone becomes true.     // Recovery first step is always to close the connection:     try {       jdbcConnection.close();     } catch(Exception ex) {} // ignore any exception     // Now reconnect so that we can retry:     jdbcConnection = getConnection();   } } Where the implementation of the actual database request would be something like this: void giveRaiseToAllEmployees(Connection conn, int percentage) throws SQLException {   Statement stmt = null;   try {     stmt = conn.createStatement();     stmt.executeUpdate("UPDATE emp SET sal=sal+(sal*"+percentage+"/100)");   } catch (SQLException sqle ) {     throw sqle;   }   finally {     if(stmt != null)       stmt.close();   }   // At the end of the request we commit our changes:   conn.commit(); } There is nothing obviously wrong in this recovery code. The database request contains a single transaction and so it looks safe to retry when the driver throws a SQLRecoverableException during its execution. The bug in this code is that the commit() call shouldn't be part of the request because if that is the call that throws the SQLRecoverableException, then it isn't safe to retry without further safety checks. Think of the case where the commit call made it through to the database, the database successfully executed the transaction but the commit outcome did not reach the driver because the connection was lost. In such a case the driver would throw a SQLRecoverableException: No more data to read from socket but there is no need to retry anything. The code snippet above would execute the transaction twice. This is where Transaction Guard is required to write a proper recovery code. Instead of blindly retrying the application can find out the outcome of the previous transaction and only retry if it wasn't committed. Connection jdbcConnection = getConnection(); boolean isJobDone = false; while(!isJobDone) {   try {     // apply the raise (DML + commit):     giveRaiseToAllEmployees(jdbcConnection,5);     // no exception, we consider the job as done:     isJobDone = true;   } catch (SQLRecoverableException recoverableException) {     // if we get a SQLRecoverableException, we assume we can just retry the entire     // transaction so we'll just loop while isJobDone becomes true.     // Recovery first step is always to close the connection:     try {       jdbcConnection.close();     } catch(Exception ex) {} // ignore any exception     // Now reconnect so that we can retry:     Connection newJDBCConnection = getConnection();     // This is where Transaction Guard becomes handy. Instead of blindly assuming that     // the entire request failed because we got a SQLRecoverableException, we rely on     // TG to verify the outcome of the previous attempt.     // To use TG, we first need to retrieve the LogicalTranasactionId from the original     // connection:     LogicalTransactionId ltxid        = ((OracleConnection)jdbcConnection).getLogicalTransactionId();     isJobDone = getTransactionOutcome(newJDBCConnection, ltxid);     jdbcConnection = newJDBCConnection;   } } Where the code for getTransactionOutcome is this: /**  * GET_LTXID_OUTCOME_WRAPPER is a wrapper for DBMS_APP_CONT.GET_LTXID_OUTCOME which we  * can't use directly because the JDBC thin driver doesn't support the PLSQL  * BOOLEAN type. The wrapper uses a NUMBER type instead.  * Note that starting in 12.2, the JDBC thin driver supports  * PLSQL BOOLEAN and so this wrapper isn't required anymore.  */ private static final String GET_LTXID_OUTCOME_WRAPPER =   "DECLARE PROCEDURE GET_LTXID_OUTCOME_WRAPPER("+   "  ltxid IN RAW,"+   "  is_committed OUT NUMBER ) "+   "IS " +   "  call_completed BOOLEAN; "+   "  committed BOOLEAN; "+   "BEGIN "+   "  DBMS_APP_CONT.GET_LTXID_OUTCOME(ltxid, committed, call_completed); "+   "  if committed then is_committed := 1; else is_committed := 0; end if; "+   "END; "+   "BEGIN GET_LTXID_OUTCOME_WRAPPER(?,?); END;"; /**  * Returns true if the LTXID committed or false otherwise.  */ boolean getTransactionOutcome(Connection conn, LogicalTransactionId ltxid) throws SQLException {   boolean committed = false;   CallableStatement cstmt = null;   try {     cstmt = conn.prepareCall(GET_LTXID_OUTCOME_WRAPPER);     cstmt.setObject(1, ltxid);     cstmt.registerOutParameter(2, OracleTypes.BIT);     cstmt.execute();     committed = cstmt.getBoolean(2);   }   catch (SQLException sqlexc) {     throw sqlexc;   }   finally {     if(cstmt != null)       cstmt.close();   }   return committed; } In the recovery code if getTransactionOutcome returns true for the previous attempt then the Oracle Database guarantees that the previous transaction successfully committed and the application doesn't need to retry. If on the other hand, getTransactionOutcome returns false, then the Oracle Database guarantees that the previous attempt didn't commit and will not commit; hence it is safe to retry. Transaction Guard is a new feature of the 12C Oracle Database. To run the code examples above you will need the 12C Oracle JDBC Thin driver (download here). The 12C JavaDoc for the JDBC Thin driver can be found here: JavaDoc. On the server, Transaction Guard also needs to be turned on through the service. On a RAC database you must use srvctl. For example: srvctl modify service -d orcl -service tgservice -commit_outcome true For a global service you have to use gdsctl. On a standalone non-RAC database you can use dbms_service like this:   alter system set service_names='tgservice';   /   declare     params dbms_service.svc_parameter_array;   begin     params('COMMIT_OUTCOME') := 'TRUE';     dbms_service.modify_service('tgservice', params);   end;   /   alter system register;   / Finally the DBMS_APP_CONT isn't executable by all users by default. To make it executable for user SCOTT:   GRANT EXECUTE ON DBMS_APP_CONT TO SCOTT; Here is a video of my Transaction Guard demo:

Write recovery code with Transaction Guard When writing a servlet or any JDBC program, have you ever wondered how to recover from a database connection loss? A typical approach is to catch SQLRecoverabl...