Using JDBC and UCP


Options



Before You Begin

Purpose

In this tutorial, you will learn how to develop and and deploy fast, scalable, highly available and secure Java applications using new features in JDBC and UCP. You will experience JDBC support for new database types, UCP, Database Resident Connection Pool, Multitenant Datasource, Transaction Guard, Application Continuity for Java, and Advanced Security Data Encryption and Integrity

Time to Complete

Approximately 2 hours.

Prerequisites

Before starting this tutorial, you should have:

  1. Installed Oracle Database 12c Release 2 with 1 container database and two pluggable databases.

  2. Start the database instance. login to SQL*PLUS as the SYS user and execute the following command.

    sqlplus / as sysdba

    startup

    Note: The startup must be executed every time you restart the machine.

  3. Installed JDK1.8

  4. Installed ant 1.9.7

  5. Installed Apache Tomcat 9. Change the permission of tomcat directory and make oracle as the owner of that directory.

    sudo chown -R oracle .

  6. Download the JCE Unlimited Strength Jurisdiction Policy Files from here.

    Note: The standard place for JCE jurisdiction policy JAR files is:
    /lib/security [Unix]
    \lib\security [Windows]

  7. Unzipped the jdbchol.zip into the /u01/oracle directory.

Note: The above steps are completed for you.

The environment used to develop this tutorial is as follows:
  • ORACLE_HOME: /u01/app/oracle/product/12.2.0/dbhome_1

  • Container database SID:

    • orcl

  • Pluggable databases SID(in orcl):

    • pdb1

    • pdb2

JDBC in Oracle Database 12c

This section provides description about various features of JDBC on Oracle Database 12c

  1. Open a new terminal and navigate to the u01/oracle/jdbchol/JDBC_Demo directory. All the demos related to JDBC are present within this directory. Execute ls to see the contents inside this directory.

    $ cd /u01/oracle/jdbchol/JDBC_Demo/
    $ ls
    build.xml  ExplStmtCache.xml   ImplStmtCache.xml    PLSQLTypeBindsDemo.xml  src
    classes    ImplicitResult.xml  InvisibleColumn.xml  SetObjectDemo.xml
    

Working with Invisible Columns

Starting from 12c release, Oracle Database supports invisible columns. Using this feature, you can add a column to the table in hidden mode and make it visible later. JDBC provides APIs to retrieve information about invisible columns. This program demonstrates how to declare an attribute invisible and retrieve later.

  1. Open and examine the InvisibleColumn.java file.

    cat src/InvisibleColumn.java

    $ cat src/InvisibleColumn.java 
    
    import java.sql.*;
    import java.util.Properties;
    import oracle.jdbc.*;
    import oracle.jdbc.pool.*;
    
    public class InvisibleColumn 
    {
     public static void main(String args[]) throws SQLException
      {
        String jdbcURL =
          "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=localhost))(CONNECT_DATA=(SERVICE_NAME=pdb1)))";
        String user = "hr";
        String passwd = "hr";
        OracleDataSource ods = new OracleDataSource();
        ods.setURL(jdbcURL);
        ods.setUser(user);
        ods.setPassword(passwd);
        Connection conn = ods.getConnection();
    
        try
        { 
          createTables(conn);
    
  2. Use ant to build and run the build.xml file.

    ant InvisibleColumn

    $ ant InvisibleColumn 
    Buildfile: /u01/oracle/jdbchol/JDBC_Demo/build.xml
    
    InvisibleColumn:
    
    print.properties:
         [echo] InvisibleColumn: Sample for Invisible Columns.
         [echo]    Look at Readme.txt for details
    
    run:
        [javac] Compiling 1 source file to /u01/oracle/jdbchol/JDBC_Demo/classes
         [java] Invisibility information of columns
         [java] column name:A
         [java] column1 value:somedata
         [java] Invisibility:false
         [java] column name:B
         [java] column2 value:1
         [java] Invisibility:true
         [java] column name:A
         [java] column1 value:newdata
         [java] Invisibility:false
         [java] column name:B
         [java] column2 value:2
         [java] Invisibility:true
         [java] 
         [java] getColumns for table with invisible columns
         [java] ColCount: 24
    
    BUILD SUCCESSFUL
    Total time: 4 seconds
    

Observe the output.

Working with Implicit Results

Starting from 12c release, Oracle Database supports results of SQL statements executed in a stored procedure to be returned implicitly to the client applications without the need to explicitly use a REF CURSOR. There are different methods to retrieve and process the implicit results returned by PL/SQL procedures or blocks.

  1. Open and examine the ImplicitResult.java file.

    cat src/ImplicitResult.java

    $ cat src/ImplicitResult.java
    import java.sql.*;
    import oracle.jdbc.*;
    import oracle.sql.*;
    import java.io.*;
    import oracle.jdbc.pool.*;
    
    public class ImplicitResult 
    {
      public static void main(String args[]) throws SQLException 
      {
        String jdbcURL =
          "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST= localhost))(CONNECT_DATA=(SERVICE_NAME=pdb1)))";
        String user = "hr";
        String passwd = "hr";
        OracleDataSource ods = new OracleDataSource();
        ods.setURL(jdbcURL);
        ods.setUser(user);
        ods.setPassword(passwd);
        Connection conn = ods.getConnection();
    
         try
         {
    
    
    
  2. Use ant to build and run the build.xml file.

    ant ImplicitResult

    $ ant ImplicitResult
    Buildfile: /u01/oracle/jdbchol/JDBC_Demo/build.xml
    
    ImplicitResult:
    
    print.properties:
         [echo] Implicit Result: Sample for getting the Implicit Results
         [echo]    Look at Readme.txt for details
    
    run:
        [javac] Compiling 1 source file to /u01/oracle/jdbchol/JDBC_Demo/classes
         [java] The return value is: true
         [java] ** ResultSet Implicit Results 1 **
         [java] Ellen
         [java] Sundar
         [java] Mozhe
         [java] David
         [java] Hermann
         [java] Shelli
         [java] Amit
         [java] Elizabeth
         [java] Sarah
         [java] David
         [java] Laura
         [java] Harrison
         [java] Alexis
         [java] Anthony
         [java] Gerald
         [java] Nanette
    
         ...
         [java] 199
         [java] 200
         [java] 201
         [java] 202
         [java] 203
         [java] 204
         [java] 205
         [java] 206
         [java] The return value is: false
    
    BUILD SUCCESSFUL
    Total time: 3 seconds
    

Observe the output.

Examining the ImpStmtCache Program

The Oracle JDBC driver automatically caches the PreparedStatement or the CallableStatement objects upon the invocation of the close() method.

A cache entry is automatically allocated to a new SQL statement if not already in the cache upon the invocation of preparedStatement() and prepareCall() methods. The Oracle JDBC driver transparently searches the cache for a match using the following criteria:

  • The SQL string must be identical.
  • The statement type must be identical PreparedStatement or CallableStatement.
  • The type of scrollable result sets FORWARD-ONLY or SCROLLABLE must be the same.

If a match is found, then the cached statement is returned; otherwise, a new statement is created and returned.

In this program you will examine how implicit statement caching is enabled by invoking ((OracleConnection)conn).setImplicitCachingEnabled(true); on the connection object.

  1. Open and examine the ImplStmtCache.java file.

    cat src/ImplStmtCache.java

    $ cat src/ImplStmtCache.java/*
     *   Implicit Statement Caching
     * 
     */ 
    // import the java.sql package 
    import java.sql.*;
    import javax.sql.*;
    import oracle.jdbc.*;
    import java.util.Properties;
    import oracle.jdbc.pool.OracleDataSource;
    
    class ImplStmtCache
    {
     public static void main (String args [])
     throws SQLException
     {
      long  start;
      long end;
      long elapsed;
    
      String name = null;
      OracleConnection conn = null;
      OraclePreparedStatement pstmt = null;        
      OracleDataSource ods = new OracleDataSource(); 
    // Set the URL (user name, and password)
  2. Use ant to build and run the build.xml file.

    ant ImplStmtCache

    $ ant ImplStmtCache
    Buildfile: /u01/oracle/jdbchol/JDBC_Demo/build.xml
    
    ImplStmtCache:
    
    print.properties:
         [echo] ImplStmtCache: Sample for Implicit Statement Cache.
         [echo]    Look at Readme.txt for details
    
    run:
        [javac] Compiling 1 source file to /u01/oracle/jdbchol/JDBC_Demo/classes
         [java] PredStmt is select EMPLOYEE_ID, FIRST_NAME from EMPLOYEES
         [java]  Time to prepare and execute NON CACHED query 10 times is: 293
         [java]  Time to prepare and execute CACHED query 10 times is: 10
    
    BUILD SUCCESSFUL
    Total time: 2 seconds
    

Observe the output.

Examining the ExplStmtCache Program

Explicit statement caching is managed by the application, because it requires explicit instructions for caching and searching using a user-defined key. Explicit statement caching is enabled by invoking setExplicitStatementCaching(true)> on the connection object.

Determining whether explicit caching is enabled is achieved by invoking getExplicitStatementCachingEnabled(). This method returns true if explicit caching is enabled. It returns false is explicit caching in not enabled. A cache entry is allocated to a new SQL statement (if not already in the cache) upon the invocation of the createStatement(), prepareStatement(), and prepareCall() methods. It is up to the application to search the cache for a match using the user-defined key (a Java string) specified during the closeWithKey(String) method, using the following:

  • getStatementWithKey(String);
  • getCallWithKey(String);

If a match is found, then the cached statement with the parameter and metadata defined in the last usage is returned. Otherwise, a null value is returned to the application.

In this program, explicit statement cache is done using getStatementWithKey(String) method.

  1. Open and examine the ExplStmtCache.java file.

    cat src/ExplStmtCache.java

    $ cat src/ExplStmtCache.java
    Buildfile: /u01/oracle/jdbchol/JDBC_Demo/build.xml * Explicit Statement Caching * */ // import the java.sql package import java.sql.*; import javax.sql.*; import oracle.jdbc.*; import oracle.jdbc.pool.OracleDataSource; class ExplStmtCache { public static void main (String args []) throws SQLException { long start; long end; long elapsed; String name = null; OracleConnection conn = null; OraclePreparedStatement pstmt = null; OracleDataSource ods = new OracleDataSource(); // Set the URL (user name, and password) String url = "jdbc:oracle:thin:hr/hr@//localhost:1521/pdb1"; ods.setURL(url);
  2. Use ant to build and run the build.xml file.

    ant ExplStmtCache

    $ ant ExplStmtCache                                                                                                          Buildfile: /u01/oracle/jdbchol/JDBC_Demo/build.xml
    
    ExplStmtCache:
    
    print.properties:
         [echo] ExplStmtCache: Sample for Explicit Statement Cache.
         [echo]    Look at Readme.txt for details
    
    run:
        [javac] Compiling 1 source file to /u01/oracle/jdbchol/JDBC_Demo/classes
         [java] PredStmt is select EMPLOYEE_ID, FIRST_NAME from EMPLOYEES
         [java]  Time to prepare and execute NON CACHED query 10 times is : 270
         [java]  Time to prepare and execute EXPLICITLY CACHED query 10 times is 8
    
    BUILD SUCCESSFUL
    Total time: 2 seconds

Observe the output.

Binding Types declared in PL/SQL Packages

Starting from Oracle Database 12c Release 1, you can map schema-level objects or collections into generic STRUCT classes, or alternatively, into custom Java classes. So, instead of creating schema-level types that are mapped to PL/SQL package types for binding, you can describe and bind PL/SQL types using only the JDBC APIs. The SQL Data Types that are supported by JDBC Drivers are BFILE, BLOB, CHAR, CLOB, DATE, NCHAR VARYING, NUMBER, RAW, REF, ROWID, UROWID and VARCHAR2.

  1. Login to sql into the pdb1 using the hr user.

    sqlplus hr/hr@pdb1;

    $ sqlplus hr/hr@pdb1;
    
    SQL*Plus: Release 12.2.0.1.0 Production on Wed Jul 20 09:21:13 2016
    
    Copyright (c) 1982, 2016, Oracle.  All rights reserved.
    
    Last Successful login time: Wed Jul 20 2016 09:16:32 +00:00
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    
    SQL> 
    
  2. Create and execute the below package in sql

    create or replace package TEST_PKG is
    type V_TYP is varray(10) of varchar2(200);
    type R_TYP is record(c1 pls_integer, c2 varchar2(100));
    procedure VARR_PROC(p1 in V_TYP, p2 OUT V_TYP);
    procedure REC_PROC(p1 in R_TYP, p2 OUT R_TYP);
    end;
    /

  3. Create and execute the below package in sql and exit from sql.

    create or replace package body TEST_PKG is
    procedure VARR_PROC(p1 in V_TYP, p2 OUT V_TYP) is
    begin
    p2 := p1;
    end;
    procedure REC_PROC(p1 in R_TYP, p2 OUT R_TYP) is
    begin
    p2 := p1;
    end;
    end;
    /

    exit;

  4. Open and examine the PLSQLTypeBindsDemo.java file.

    cat src/PLSQLTypeBindsDemo.java

    $cat src/PLSQLTypeBindsDemo.java
    import java.sql.Array;
    import java.sql.CallableStatement;
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.sql.Struct;
    import java.sql.Types;
    
    import oracle.jdbc.OracleConnection;
    
    public class PLSQLTypeBindsDemo
    {
       public static void main(String[] args) throws SQLException
       {
          System.out.println("begin...");
          Connection conn = null;
          oracle.jdbc.pool.OracleDataSource ods = new oracle.jdbc.pool.OracleDataSource();
          ods.setURL("jdbc:oracle:thin:@localhost:1521/pdb1");
          ods.setUser("hr");
          ods.setPassword("hr");
          // get connection
          conn = ods.getConnection();
          
          PLSQLTypeBindsDemo demo = new PLSQLTypeBindsDemo();
          
          demo.varrayDemo(conn);
          
          demo.recordDemo(conn);
    
          if (conn != null)
             conn.close();
    
          System.out.println("done!");
       }
       ...                             
  5. Use ant to build and run the build.xml file.

    ant PLSQLTypeBindsDemo

    $ ant PLSQLTypeBindsDemo
    Buildfile: /u01/oracle/jdbchol/JDBC_Demo/build.xml
    
    PLSQLTypeBindsDemo:
    
    print.properties:
         [echo] PLSQLTypeBindsDemo: Sample for PLSQLTypeBindsDemo.
         [echo]    Look at Readme.txt for details
    
    run:
        [javac] Compiling 1 source file to /u01/oracle/jdbchol/JDBC_Demo/classes
         [java] begin...
         [java] varrayDemo - 
         [java] elem[0] = A, elem[[1] = B
         [java] recordDemo - 
         [java] attr[0] = 12345, attr[[1] = B
         [java] done!
    
    BUILD SUCCESSFUL
    Total time: 3 seconds  

    Observe the output.

getObject() and setObject()

This section describes getObject() and setObject()

  1. Open and examine the SetObjectDemo.java file.

    cat src/SetObjectDemo.java

    $ cat src/SetObjectDemo.java
    /* Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.*/
    /*
       DESCRIPTION    
        Latest JDBC 4.2 APIs   
        
        Step 1: Enter the Database details in this file. 
                DB_USER, DB_PASSWORD and DB_URL are required
        Step 2: Run the sample with "ant JDBCLatestSpec42"
      
       NOTES
        Use JDK 1.7 and above
    
       MODIFIED    (MM/DD/YY)
        nbsundar    02/17/15 - Creation 
     */
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.sql.PreparedStatement;
    import java.util.Properties;
    
    import oracle.jdbc.pool.OracleDataSource;
    import oracle.jdbc.OracleConnection;
    import oracle.jdbc.OracleType;
    
    
    public class SetObjectDemo{  
      // The recommended format of a connection URL is the long format with the
      // connection descriptor.
      //final static String DB_URL= "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=myhost)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=myorcldbservicename)))";
      final static String DB_URL= "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=localhost)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=pdb1)))";
      final static String DB_USER = "hr";
      final static String DB_PASSWORD = "hr";
    
     /*
     ...
  2. Use ant to build and run the build.xml file.

    ant SetObjectDemo

    $ ant SetObjectDemo
    Buildfile: /u01/oracle/jdbchol/JDBC_Demo/build.xml
    
    SetObjectDemo:
    
    print.properties:
         [echo] SetObjectDemo: Sample for SetObjectDemo
         [echo]    Look at Readme.txt for details
    
    run:
        [javac] Compiling 1 source file to /u01/oracle/jdbchol/JDBC_Demo/classes
         [java] EMPLOYEE_ID  FIRST_NAME   LAST_NAME
         [java] -----------  ----------   ---------
         [java] 100 Steven King 
         [java] 101 Neena Kochhar 
         [java] 102 Lex De Haan 
         [java] 103 Alexander Hunold 
         [java] 104 Bruce Ernst 
         [java] 105 David Austin 
         [java] 106 Valli Pataballa 
         [java] 107 Diana Lorentz 
         [java] 108 Nancy Greenberg 
         [java] 109 Daniel Faviet 
         [java] 110 John Chen 
         [java] 111 Ismael Sciarra 
         [java] 112 Jose Manuel Urman 
         [java] 113 Luis Popp 
         [java] 114 Den Raphaely 
         [java] 115 Alexander Khoo 
         [java] 116 Shelli Baida 
         [java] 117 Sigal Tobias 
         [java] 118 Guy Himuro 
         [java] 119 Karen Colmenares 
    ...
    
    ...
    [java] 205 Shelley Higgins 
         [java] 206 William Gietz 
         [java] 
         [java] ************************************
         [java] Print employee by id = 102
         [java] FIRST_NAME  LAST_NAME
         [java] ---------------------
         [java] Lex Lex 
         [java] 
         [java] ************************************
         [java] Print employee by id = 154
         [java] FIRST_NAME  LAST_NAME
         [java] ---------------------
         [java] Nanette Nanette 
         [java] 
         [java] ************************************
         [java] Print employee by id = 205
         [java] FIRST_NAME  LAST_NAME
         [java] ---------------------
         [java] Shelley Shelley 
    
    BUILD SUCCESSFUL
    Total time: 3 seconds
    
    

Observe the output.

Universal Connection Pool (UCP)

The Oracle Universal Connection Pool (UCP) for JDBC is a full-featured connection pool for managing database connections. Java applications that are database-intensive use the connection pool to improve performance and better utilize system resources. Oracle Universal Connection Pool (UCP) for JDBC has following features:

  • Can support
    • Any type of connection: JDBC, JCA, LDAP
    • Any Database (Oracle, non-Oracle)
    • Any App Server (Oracle, non-Oracle)
  • Has stand-alone deployment (BPEL, Toplink)
  • Can be integrated seamlessly with the Oracle Database - RAC and Non-RAC
  • Addresses Apps/Fusion requirements to replace AOL connection pools and DataSource layer

UCP Demo in a stock ticker demo application

This demo is a stock ticker application that uses the Universal Connection Pool to retrieve stock price information from the database. Using this demo, you may view the Universal Connection Pool properties, change the properties at runtime without shutting down the pool instance, view Universal Connection Statistics etc. To perform this demo execute the following steps:

  1. Open a new terminal. Navigate to the u01/oracle/jdbchol/UCP_Demo directory. Execute ls to see the contents inside this directory.

    cd /u01/oracle/jdbchol/UCP_Demo

    $ cd /u01/oracle/jdbchol/UCP_Demo/
    ls
    build.xml  classes  ClientSimulator  DbConfig.properties  src
    

  2. Open and examine the DbConfig.properties file.

    cat DbConfig.properties

    $ cat DbConfig.properties                                                                                                        DB_USER =hr
    DB_PASSWORD =hr
    DB_URL =jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1)))

  3. Open and examine the UCPDemo.java file.

    cat src/UCPDemo.java

    $ cat src/UCPDemo.java                                                                                                           import java.io.IOException;
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Date;
    
    import oracle.ucp.UniversalConnectionPoolException;
    import oracle.ucp.UniversalConnectionPoolStatistics;
    import oracle.ucp.admin.UniversalConnectionPoolManagerImpl;
    import oracle.ucp.jdbc.PoolDataSource;
    import oracle.ucp.jdbc.PoolDataSourceImpl;
    
    /**
     * UCP stock ticker demo web service
     * 
     * @author ydolgov
     *
     */
    public class UCPDemo
    {
      public final static String UCP_POOL = "UCP-pool";
      
      private final PoolDataSource pds = new PoolDataSourceImpl();
    
      /**
       * 
       */
      public UCPDemo()
      {
        super();
      }
    ...
    

  4. Use ant to build and run the build.xml file.

    ant

    $ ant 
    Buildfile: /u01/oracle/jdbchol/UCP_Demo/build.xml
    
    print.properties:
         [echo] UCPDemo: That runs UCPDemo
    
    run:
        [javac] Compiling 1 source file to /u01/oracle/jdbchol/UCP_Demo/classes
        [javac] /u01/oracle/jdbchol/UCP_Demo/build.xml:38: warning: 'includeantruntime' was not set, defaulting to build.sysclasspath=last; set to false for repeatable builds
         [java] preparing stock database, please wait till notified...
         [java] Stock data wrote for Wed Jul 20 11:00:00 UTC 2016
         [java] Stock data wrote for Wed Jul 20 11:15:00 UTC 2016
         [java] Stock data wrote for Wed Jul 20 11:30:00 UTC 2016
         [java] Stock data wrote for Wed Jul 20 11:45:00 UTC 2016
         [java] Stock data wrote for Wed Jul 20 12:00:00 UTC 2016
         [java] Stock data wrote for Wed Jul 20 12:15:00 UTC 2016
         [java] Stock data wrote for Wed Jul 20 12:30:00 UTC 2016
         [java] Stock data wrote for Wed Jul 20 12:45:00 UTC 2016
         [java] Stock data wrote for Wed Jul 20 13:00:00 UTC 2016
         [java] Stock data wrote for Wed Jul 20 13:15:00 UTC 2016
         [java] Stock data wrote for Wed Jul 20 13:30:00 UTC 2016
         [java] Stock data wrote for Wed Jul 20 13:45:00 UTC 2016
         [java] Stock data wrote for Wed Jul 20 14:00:00 UTC 2016
         [java] Stock data wrote for Wed Jul 20 14:15:00 UTC 2016                                                                    ....
         ...
         [java] Stock data wrote for Sat Jul 23 07:00:00 UTC 2016
         [java] Stock data wrote for Sat Jul 23 07:15:00 UTC 2016
         [java] Stock data wrote for Sat Jul 23 07:30:00 UTC 2016
         [java] Stock data wrote for Sat Jul 23 07:45:00 UTC 2016
         [java] Stock data wrote for Sat Jul 23 08:00:00 UTC 2016
         [java] Stock data wrote for Sat Jul 23 08:15:00 UTC 2016
         [java] Stock data wrote for Sat Jul 23 08:30:00 UTC 2016
         [java] Stock data wrote for Sat Jul 23 08:45:00 UTC 2016
         [java] Stock data wrote for Sat Jul 23 09:00:00 UTC 2016
         [java] Stock data wrote for Sat Jul 23 09:15:00 UTC 2016
         [java] Stock data wrote for Sat Jul 23 09:30:00 UTC 2016
         [java] Stock data wrote for Sat Jul 23 09:45:00 UTC 2016
         [java] Stock data wrote for Sat Jul 23 10:00:00 UTC 2016
         [java] Stock data wrote for Sat Jul 23 10:15:00 UTC 2016
         [java] Stock data wrote for Sat Jul 23 10:30:00 UTC 2016
         [java] Stock data wrote for Sat Jul 23 10:45:00 UTC 2016
         [java] ...ready to go!

    Note: Upon startup the UCPDemo will spend a few seconds to store the stock price data onto the database, so wait until the message "...ready to go!" is printed out on the screen.

  5. Open Firefox web browser and navigate to the http://localhost:8081 url, which is the home page of the UCP Connection Pool Demo. Explore various links on the site.

    http://localhost:8081

    UCP Demo in a stock ticker demo application

    UCP Demo in a stock ticker demo application

    UCP Demo in a stock ticker demo application

    UCP Demo in a stock ticker demo application

    UCP Demo in a stock ticker demo application

  6. Observe the terminal. Notice the status of thread operations.

         ...
         [java] Stock data wrote for Sat Jul 23 10:45:00 UTC 2016
         [java] ...ready to go!
         [java] thread-0 started
         [java] thread-0 finished
         [java] thread-0 started
         [java] thread-0 finished
         [java] thread-0 started
         [java] thread-0 finished
         [java] thread-0 started
         [java] thread-0 finished
         [java] thread-0 started
         [java] thread-0 finished
         [java] thread-0 started
         [java] thread-0 finished
    
    
  7. To simulate many stock ticker clients using the service simultaneously you can use the Client Simulator.

    Open a new terminal. Navigate to the u01/oracle/jdbchol/UCP_Demo/ClientSimulator directory. Execute ls to see the contents inside this directory.

    cd /u01/oracle/jdbchol/UCP_Demo/ClientSimulator
  8. Open and examine the ClientSimulator.java file.

    cat src/ClientSimulator.java

    $ cat src/ClientSimulator.java
    import java.io.BufferedReader;
    import java.io.IOException;
    import java.io.InputStreamReader;
    import java.net.HttpURLConnection;
    import java.net.MalformedURLException;
    import java.net.URL;
    import java.util.ArrayList;
    import java.util.Collection;
    
    /**
     * Multisession multithreaded stock ticker
     * client simulator
     * 
     * @author ydolgov
     *
     */
    public class ClientSimulator
    {
    
      /**
       * 
       */
      public ClientSimulator()
      {
        super();
      }                                                                                                                           ....
    

  9. Use ant to build and run the build.xml file.

    ant

    $ ant
         [java] polled
         [java] polled
         [java] polled
         [java] polled
         [java] polled
         [java] polled
         [java] polled
         [java] polled
         [java] polled
         [java] polled
         [java] polled
         [java] polled
         [java] client simulator finished
    
    BUILD SUCCESSFUL
    Total time: 9 seconds

    Note: Observe the terminal. Notice the status of thread operations.

UCP demo with Tomcat using UCP XML configuration file

This section describes UCP with Tomcat using new UCP XML config file

  1. Open a new terminal and navigate to apache tomcat directory. Execute ls to see the contents inside this directory.

    cd /usr/apache-tomcat-9.0.0.M8/

    $ cd /usr/apache-tomcat-9.0.0.M8/
    $ ls
    bin   lib      logs    RELEASE-NOTES  temp     work
    conf  LICENSE  NOTICE  RUNNING.txt    webapps 
  2. Navigate into webapps directory and execute ls to see the contents inside this directory.

    cd webapps

    $ cd webapps
    $ ls
      docs  host-manager  ROOT   examples  manager       
  3. Move the UCPConfigXML.war file in to the webapps directory. Execute ls to see the contents inside webapps directory.

    cp /u01/oracle/jdbchol/UCPConfigXML_Demo/UCPConfigXML.war .
  4. Navigate to apache-tomcat-9.0.0.M8/conf directory. Execute ls to see the contents inside this directory.

    $ cd /usr/apache-tomcat-9.0.0.M8/conf/
    $ ls
    Catalina             context.xml           logging.properties  tomcat-users.xsd
    catalina.policy      jaspic-providers.xml  server.xml          web.xml
    catalina.properties  jaspic-providers.xsd  tomcat-users.xml
    
  5. Edit the server.xmlfile. Modify the port value to 8085. This is done to ensure that the port number is not conflicting with any other open ports.

    vi server.xml


    UCP demo with Tomcat using UCP XML configuration file

  6. Navigate into the apache-tomcat-9.0.0.M8/bin directory.

    cd /usr/apache-tomcat-9.0.0.M8/bin

  7. Start the tomcat and immediately stop the tomcat. This is done so that the UCPConfigXML.war gets deployed.

    ./startup.sh

    ./shutdown.sh

    $ ./startup.sh
    Using CATALINA_BASE:   /usr/apache-tomcat-9.0.0.M8
    Using CATALINA_HOME:   /usr/apache-tomcat-9.0.0.M8
    Using CATALINA_TMPDIR: /usr/apache-tomcat-9.0.0.M8/temp
    Using JRE_HOME:        /usr/java/jdk1.8.0_91
    Using CLASSPATH:       /usr/apache-tomcat-9.0.0.M8/bin/bootstrap.jar:/usr/apache-tomcat-9.0.0.M8/bin/tomcat-juli.jar
    Tomcat started.
    $ ./shutdown.sh
    Using CATALINA_BASE:   /usr/apache-tomcat-9.0.0.M8
    Using CATALINA_HOME:   /usr/apache-tomcat-9.0.0.M8
    Using CATALINA_TMPDIR: /usr/apache-tomcat-9.0.0.M8/temp
    Using JRE_HOME:        /usr/java/jdk1.8.0_91
    Using CLASSPATH:       /usr/apache-tomcat-9.0.0.M8/bin/bootstrap.jar:/usr/apache-tomcat-9.0.0.M8/bin/tomcat-juli.jar
    [oracle@host01 bin]$ 
  8. Navigate into the apache-tomcat-9.0.0.M8/webapps directory. Execute ls to see the contents inside this directory. Notice that a new directory UCPConfigXML with the same name as the UCPConfigXML.war file got created with contents in it.

    cd /usr/apache-tomcat-9.0.0.M8/webapps

    $ cd /usr/apache-tomcat-9.0.0.M8/webapps/
    $ ls
    docs      host-manager  ROOT    UCPConfigXML.war 
    examples  manager       UCPConfigXML 
    
  9. Navigate into the UCPConfigXML directory. Execute ls to see the contents inside this directory.

    cd UCPConfigXML

    $ cd UCPConfigXML
    $ ls 
    build.xml  dist  META-INF  README.txt  src  WEB-INF
  10. Navigate into the META-INF directory. Execute ls to see the contents inside this directory.

    cd META-INF

    $ cd META-INF/
    $ ls
    context_old.xml  InitialConfig_nir.xml  MANIFEST.MF
    context.xml      InitialConfig.xml      war-tracker
  11. Create a new file context.xml and edit it.

    vi context.xml

  12. Insert the following code in to the context.xml file.

    <Context 
        docBase="UCPTomcat" 
        path="/UCPTomcat"
        reloadable="true" 
        source="org.eclipse.jst.jee.server:UCPTomcat">
    
            <Resource 
                name="tomcat/UCPPool" 
                auth="Container"
                factory="oracle.ucp.jdbc.PoolDataSourceImpl" 
                type="oracle.ucp.jdbc.PoolDataSource"
                description="UCP Pool in Tomcat" 
                connectionFactoryClassName="oracle.jdbc.pool.OracleDataSource"
                dataSourceFromConfiguration="UCPDatasource" />  
    </Context>
  13. Execute ls to see the contents inside this directory. Verify that the context.xml file is present.

    ls

    $ ls
    context_old.xml  InitialConfig_nir.xml  MANIFEST.MF
    context.xml      InitialConfig.xml      war-tracker
  14. Edit the InitialConfig.xml file.

    vi InitialConfig.xml

  15. Modify the url, user, password properties with the values as given below. Save the file.

    url="jdbc:oracle:thin:@(DESCRIPTION=
                                (ADDRESS=(HOST=localhost)(PORT=1521)(PROTOCOL=tcp))
                                (CONNECT_DATA=(SERVICE_NAME=pdb1)))" 
    user="hr"
    password="hr"

    <?xml version="1.0" encoding="UTF-8"?>
    <ucp-properties>
            <connection-pool 
                    connection-pool-name="ucp_pool1" 
                    connection-factory-class-name="oracle.jdbc.pool.OracleDataSource" 
                    url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=localhost))(CONNECT_DATA=(SERVICE_NAME=pdb1)))";
                    user = "hr";
                    passwd = "hr"
                    initial-pool-size="10" 
                    min-pool-size="5"  
                    max-pool-size="20" 
                    validate-connection-on-borrow="true" 
                    sql-for-validate-connection="select 1 from dual"
                    shared = "true" 
            >
            <data-source 
                    data-source-name="UCPDatasource" 
                    description="UCP datasource"
            >
            </data-source>
    </connection-pool>
    </ucp-properties>
    
  16. Navigate to apache-tomcat-9.0.0.M8/webapps/UCPConfigXML directory. Execute ls to see the contents inside this directory.

    cd /usr/apache-tomcat-9.0.0.M8/webapps/UCPConfigXML
    ls
  17. Edit the build.xml file.

    vi build.xml

  18. Modify the TOMCAT_HOME property value and give the current tomcat home path. Save the file.

    <property name="TOMCAT_HOME" value="/usr/apache-tomcat-9.0.0.M8"/>
    <project name="UCPTomcatDemo" default="all">
            <property name="classes" value="WEB-INF/classes" />
            <property name="src" value="src" />
            <property name="dist" value="dist" />
    <property name="TOMCAT_HOME" value="/usr/apache-tomcat-9.0.0.M8"/>
    
      <path id="UCPTomcatDemo_classpath">
        <pathelement location="${classes}"/>
        <fileset dir="${TOMCAT_HOME}/lib" includes="*.jar" />
        <fileset dir="WEB-INF/lib" includes="*.jar" />
    </path>
    
      <target name="displayMessage">
            <echo message="-------------------------------------------------"/>
            <echo message="        BUILDING UCP Tomcat Demo                 "/>
            <echo message="-------------------------------------------------"/>
        </target>
    
    <target name="clean">
    <delete dir="${classes}"/>
    <delete dir="${dist}" />
    </target>
    ....
    
    
  19. Navigate to apache-tomcat-9.0.0.M8/bin directory. Execute ls to see the contents inside this directory.

    cd /usr/apache-tomcat-9.0.0.M8/bin

    $ cd /usr/apache-tomcat-9.0.0.M8/bin/
    $ ls
    bootstrap.jar                 daemon.sh         startup.sh
    catalina.bat                  digest.bat        tomcat-juli.jar
    catalina.sh                   digest.sh         tomcat-native.tar.gz
    catalina-tasks.xml            setclasspath.bat  tool-wrapper.bat
    commons-daemon.jar            setclasspath.sh   tool-wrapper.sh
    commons-daemon-native.tar.gz  shutdown.bat      version.bat
    configtest.bat                shutdown.sh       version.sh
    configtest.sh                 startup.bat
  20. Edit the catalina.sh file.

    vi catalina.sh

  21. Modify the JAVA_OPTS value. Give the path of InitialConfig.xml file.

    JAVA_OPTS="$JAVA_OPTS -Doracle.ucp.jdbc.xmlConfigFile='file:///usr/apache-tomcat-9.0.0.M8/webapps/UCPConfigXML/META-INF/InitialConfig.xml'"
    UCP demo with Tomcat using UCP XML configuration file

  22. Start the tomcat.

    ./startup.sh

    $ ./startup.sh
    Using CATALINA_BASE:   /usr/apache-tomcat-9.0.0.M8
    Using CATALINA_HOME:   /usr/apache-tomcat-9.0.0.M8
    Using CATALINA_TMPDIR: /usr/apache-tomcat-9.0.0.M8/temp
    Using JRE_HOME:        /usr/java/jdk1.8.0_91
    Using CLASSPATH:       /usr/apache-tomcat-9.0.0.M8/bin/bootstrap.jar:/usr/apache-tomcat-9.0.0.M8/bin/tomcat-juli.jar
    Tomcat started.
  23. Open Firefox web browser and navigate to the below link. This link will contain the output of the demo.

    http://localhost:8085/UCPConfigXML/DemoServlet

    UCP demo with Tomcat using UCP XML configuration file

    Notice that the output is generated by executing the Java file present inside the UCPConfigXML.

UCP demo using Tomcat

In this section you will:

  • Deploy a Tomcat/UCP workload (Java servlets) in Oracle RAC and Active Data Guard (ADG) environments
  • Configure Oracle RAC & ADG and UCP for planned maintenance
  • Configure Oracle RAC & ADG and UCP for unplanned downtime
  • Confgure Oracle RAC & ADG for runtime load balancing

Perform the following operations:

  1. Open a new terminal. Navigate to the u01/oracle/jdbchol/UCPTomcat_Demo directory. Execute ls to see the contents inside this directory.

    cd /u01/oracle/jdbchol/UCPTomcat_Demo

  2. Copy the UCPTomcat.war file from your working directory to the webapps folder of Tomcat. Open the terminal and run this command:

    cp UCPTomcat.war /usr/apache-tomcat-9.0.0.M8/webapps 
    ls /usr/apache-tomcat-9.0.0.M8/webapps
  3. Start the Tomcat server. Tomcat can be started by executing the startup.sh file from the bin folder.

    cd /usr/apache-tomcat-9.0.0.M8/bin
    ./startup.sh

    $ cd /usr/apache-tomcat-9.0.0.M8/bin/
    $ ./startup.sh 
    Using CATALINA_BASE:   /usr/apache-tomcat-9.0.0.M8
    Using CATALINA_HOME:   /usr/apache-tomcat-9.0.0.M8
    Using CATALINA_TMPDIR: /usr/apache-tomcat-9.0.0.M8/temp
    Using JRE_HOME:        /usr/java/jdk1.8.0_91
    Using CLASSPATH:       /usr/apache-tomcat-9.0.0.M8/bin/bootstrap.jar:/usr/apache-tomcat-9.0.0.M8/bin/tomcat-juli.jar
    Tomcat started.
    

    Starting the Tomcat server will automatically unzip the contents of the UCPTomcat.war file and create a folder called UCPTomcat with all the contents.

  4. Navigate into the apache-tomcat-9.0.0.M8/webapps directory. Execute ls to see the contents inside this directory. Notice that a new directory UCPTomcat with the same name as the UCPTomcat.war file got created with contents in it. 

    cd /usr/apache-tomcat-9.0.0.M8/webapps
    ls
    $ cd /usr/apache-tomcat-9.0.0.M8/webapps/
    $ ls
    docs      host-manager  ROOT    UCPConfigXML.war UCPTomcat.war
    examples  manager       UCPConfigXML      UCPTomcat
    
  5. Open the UCPTomcat folder and edit the context.xml file under the META-INF folder. The context.xml file consists of the UCP properties. Replace the url, user, password and onsConfiguration to point to your own database instance.

    cd UCPTomcat/META-INF/

    $ cd UCPTomcat/META-INF/
    $ ls                                                                                                                        context.xml   MANIFEST.MF     war-tracker                                                                                                                                                                                                               $ vi context.xml
    
  6. <Context docBase="UCPTomcat" path="/UCPTomcat"
       reloadable="true" source="org.eclipse.jst.jee.server:UCPTomcat">
    <Resource name="tomcat/UCPPool" auth="Container"
       factory="oracle.ucp.jdbc.PoolDataSourceImpl" 
       type="oracle.ucp.jdbc.PoolDataSource"
       description="UCP Pool in Tomcat" 
       connectionFactoryClassName="oracle.jdbc.replay.OracleDataSourceImpl"
       minPoolSize="2" 
       maxPoolSize="60" 
       initialPoolSize="15" 
       autoCommit="true"
       user="hr"
       password="hr" 
       fastConnectionFailoverEnabled="true"
       url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=localhost)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=pdb1)))"
        onsConfiguration="nodes=host01:6250"
    />
    
    <context-param>
            <param-name>oracle.ucp.PlannedDrainingPeriod</param-name>
            <param-value>30</param-value>
    </context-param>
    
    

    Update the startcyclops file in the UCPTomcat folder with the corresponding RAC or Database nodes.

    $ vi startcyclops
    
    
    !/bin/sh
    java -Dstore_stats=true -Xbootclasspath/a:$SRCHOME/opmn/lib/ons.jar:. -jar WEB-INF/lib/cyclops.jar -ONS="nodes=host01:6250" -csvstats 5556:host01.example.com:6150

    Open the build.xml file present in the UCPTomcat directory and change the value of TOMCAT_HOME.

    
    <project name="UCPTomcatDemo" default="all">
            <property name="classes" value="WEB-INF/classes" />
            <property name="src" value="src" />
            <property name="dist" value="dist" />
    <property name="TOMCAT_HOME" value="/usr/apache-tomcat-9.0.0.M8" />
    
      <path id="UCPTomcatDemo_classpath">
        <pathelement location="${classes}"/>
        <fileset dir="${TOMCAT_HOME}/lib" includes="*.jar" />
        <fileset dir="WEB-INF/lib" includes="*.jar" />
    </path>
    
      <target name="displayMessage">
            <echo message="-------------------------------------------------"/>
            <echo message="        BUILDING UCP Tomcat Demo                 "/>
            <echo message="-------------------------------------------------"/>
        </target>
    
    <target name="clean">
    <delete dir="${classes}"/>
    <delete dir="${dist}" />
    </target>
    

  7. Navigate to the UCPTomcat folder in the webapps directory and run the demo.

    cd /u01/oracle/apache-tomcat-9.0.0.M8/webapps/UCPTomcat/
    $ ant
    Buildfile: /usr/apache-tomcat-9.0.0.M8/webapps/UCPTomcat/build.xml
    
    displayMessage:
         [echo] -------------------------------------------------
         [echo]         BUILDING UCP Tomcat Demo                 
         [echo] -------------------------------------------------
    
    clean:
       [delete] Deleting directory /usr/apache-tomcat-9.0.0.M8/webapps/UCPTomcat/WEB-INF/classes
       [delete] Deleting directory /usr/apache-tomcat-9.0.0.M8/webapps/UCPTomcat/dist
    
    init:
        [mkdir] Created dir: /usr/apache-tomcat-9.0.0.M8/webapps/UCPTomcat/WEB-INF/classes
    
    compile:
        [javac] Compiling 3 source files to /usr/apache-tomcat-9.0.0.M8/webapps/UCPTomcat/WEB-INF/classes
    
    displayMessage2:
         [echo] --------------------------------------------------
         [echo]                      CREATING a WAR FILE          
         [echo] --------------------------------------------------
    
    prepare_war:
        [mkdir] Created dir: /usr/apache-tomcat-9.0.0.M8/webapps/UCPTomcat/dist
          [jar] Building jar: /usr/apache-tomcat-9.0.0.M8/webapps/UCPTomcat/dist/UCPTomcat.war
    
    all:
    
    BUILD SUCCESSFUL
    Total time: 1 second
  8. Start cyclops to monitor the connections. The startcyclops file is an executable to start the cyclops which will demonstrate how the load balancing is done between two instances or available instances.

    chmod +x startcyclops
    ./startcyclops

    $ ./startcyclops
    16/07/21-06:32:18 ONS: Node Lists
    NodeList default[1] active
    Connection default-host01:6250 status=Connecting alive=false
    Proxy (#1) started

    Open another terminal and run initializeLoad to add more threads and add more load.

    chmod +x initializeLoad
    ./initializeLoad

  9. Check if the servlet is up:

    http://localhost:<portnumber>/UCPTomcat/DemoServlet

    Replace the <portnumber> with the port that Tomcat is configured to use on your system.

    UCP with Tomcat and Cyclops

    Observe the output.

JDBC, UCP with Database Resident Connection Pool (DRCP)

In middle-tier connection pools, every connection cache maintains a minimum number of connections to the server. These connections are not shared with any other middle-tier and are retained in the cache even if some of these are idle. However, a large number of such middle-tier connection pools increase the number of connections to the Database server significantly and waste a lot of Database resources because all the connections do not remain active simultaneously.

The Database Resident Connection Pool implementation creates a pool on the server side, which is shared across multiple client pools. This significantly lowers memory consumption because of reduced number of server processes on the server and increases the scalability of the Database server.

In this section you learn to create a DRCP connection on the server side.

Note: drcpdemo.jar, drcpdemo.properties and drcpdemo are included in the classpath.

  1. Open a new terminal. Navigate to the u01/oracle/jdbchol/DRCP_Demo directory. Execute ls to see the contents inside this directory.

    cd /u01/oracle/jdbchol/DRCP_Demo

    $ cd /u01/oracle/jdbchol/DRCP_Demo
    $ ls
    build.xml  drcpdemo.properties  MANIFEST.MF  ojdbc6.jar  src  ucp.jar
  2. Open and examine the drcpdemo.properties file.

    cat drcpdemo.properties

    $ cat drcpdemo.properties 
    username=hr
    password=hr
    autoCommit=false
    
    ######### USE DRCP #############
    #url=jdbc:oracle:thin:@//localhost:1521/pdb1:pooled
    ######### USE DEDICATED ########
    url=jdbc:oracle:thin:@//localhost:1521/pdb1
    
    
    # UCP MIN=MAX setting:
    ucp_pool_size=5
    
    # Time taken to process the results from the database. Time in milliseconds.
    # -1 means no sleep.
    delay_between_requests=40
    
    # Number of concurrent threads running in the application
    # UCP is tuned to have MAX and MIN limit set to this
    number_of_threads=10
    
    verbose=true
    
  3. Use ant to build and run the build.xml file.

    ant

    $ ant
    Buildfile: /u01/oracle/jdbchol/DRCP_Demo/build.xml
    
    clean:
    
    init:
        [mkdir] Created dir: /u01/oracle/jdbchol/DRCP_Demo/classes
    
    compile:
        [javac] Compiling 3 source files to /u01/oracle/jdbchol/DRCP_Demo/classes
    
    dist:
          [jar] Building jar: /u01/oracle/jdbchol/DRCP_Demo/drcpdemo.jar
    
    all:
    
    BUILD SUCCESSFUL
    Total time: 1 second
    
  4. Execute ls to see the contents inside this directory. You could notice that a new drcpdemo.jar file has been created.

    ls

    $ ls
    build.xml  drcpdemo.jar         MANIFEST.MF  src
    classes    drcpdemo.properties  ojdbc6.jar   ucp.jar
  5. Execute the drcpdemo.jar file. Observe the output.

    java -jar drcpdemo.jar

    $ java -jar drcpdemo.jar 
    ######################################################
    Connecting to jdbc:oracle:thin:@//localhost:1521/pdb1
      # of Threads             : 10
      UCP pool size            : 5
      Delay between requests   : 40 ms
    ######################################################
    
    0 Connections active, avg response time from db 34 ms
    4 Connections active, avg response time from db 21 ms
    1 Connections active, avg response time from db 17 ms
    5 Connections active, avg response time from db 19 ms
    5 Connections active, avg response time from db 17 ms
    4 Connections active, avg response time from db 14 ms
    0 Connections active, avg response time from db 12 ms
    0 Connections active, avg response time from db 11 ms
    0 Connections active, avg response time from db 13 ms

    Press Ctrl+C to stop and return to the file.

Connecting to Oracle Exadata Express Cloud Using JDBC Thin Client

In this section you learn how to set properties to connect to Oracle Exadata Express Cloud using JDBC Thin Client. You must enable SQL*Net for your service before continuing with the following steps. 

  1. Create a new folder called cloud and unzip cloud.zip.

  2. Create a new directory lib inside cloud folder and place all required configuration files tnsnames.ora, truststore.jks, and keystore.jks.

    $ mkdir lib                                                                                                                  keystore.jks tnsnames.ora  truststore.jks
  3. Make sure that this jar is in the CLASSPATH.

  4. Create a new file run.sh and edit it. 

    vi run.sh
    java -classpath ./lib/ojdbc7.jar:. \
    -Doracle.net.tns_admin=/home/oracle/Desktop/cloud/lib \
    -Djavax.net.ssl.trustStore=/home/oracle/Desktop/cloud/lib/truststore.jks \
    -Djavax.net.ssl.trustStorePassword=Welcome12# \
    -Djavax.net.ssl.keyStore=/home/oracle/Desktop/cloud/lib/keystore.jks \
    -Djavax.net.ssl.keyStorePassword=Welcome12# \
    -Doracle.net.ssl_server_dn_match=true \
    -Doracle.net.ssl_version=1.2 \
    SimpleTest
    

    Note: Replace “welcome1” in the above code with your wallet password provided during wallet download.

  5. Create a new file SimpleTest.java and edit it.

  6. vi SimpleTest.java
  7. Insert the following code in to the SimpleTest.java file.

    import oracle.jdbc.pool.OracleDataSource;
    import oracle.jdbc.OracleConnection;
    import java.sql.DatabaseMetaData;
    import java.util.Properties;
    
    public class SimpleTest {
    
      // Connection URL. Get the details from tnsnames.ora file
      final static String DB_URL = "jdbc:oracle:thin:@dbaccess";
      final static String DB_USER = "pdb_admin";
      final static String DB_PASSWORD = "welcome1";
    
      public static void main(String[] args) throws Exception {
    
        Properties prop = new Properties();
        prop.put(OracleConnection.CONNECTION_PROPERTY_USER_NAME, DB_USER);
        prop.put(OracleConnection.CONNECTION_PROPERTY_PASSWORD, DB_PASSWORD);
        OracleDataSource ods = new OracleDataSource();
        ods.setURL(DB_URL);
        ods.setConnectionProperties(prop);
    
      // Get an connection to the cloud database
    
        // Get the JDBC driver name and version
        OracleConnection connection = (OracleConnection) ods.getConnection();
        DatabaseMetaData dbmd = connection.getMetaData();
        System.out.println("Driver Name: " + dbmd.getDriverName());
        System.out.println("Driver Version: " + dbmd.getDriverVersion());
        System.out.println("Successfully connected to a cloud database");
      }
    }
  8. Run the run.sh file in the terminal.
    $ ./run.sh                                                                                                                  
    Driver Name: Oracle JDBC driver
    Driver Version: 12.1.0.2.0
    Successfully connected to a cloud database
    

Transaction Guard for Java

Transaction Guard provides applications with a tool for determining the status of an in-flight transaction following a recoverable outage. Using Transaction Guard, an application can ensure that a transaction executes no more than once. For example, if an online bookstore application determines that the previously submitted commit failed, then the application can safely resubmit.

Transaction Guard provides a tool for at-most-once execution to avoid the application executing duplicate submissions. Transaction Guard provides a known outcome for every transaction.

Transaction Guard is a core Oracle Database capability. Application Continuity uses Transaction Guard when masking outages from end users. Without Transaction Guard, an application retrying after an error may cause duplicate transactions to be committed.

Setting Up and Testing Transaction Guard

  1. Open a new terminal. Navigate to the u01/oracle/jdbchol/TG_Demo directory. Execute ls to see the contents inside this directory.

    cd /u01/oracle/jdbchol/TG_Demo

    $ cd /u01/oracle/jdbchol/TG_Demo/
    $ ls
    build.xml  lib  src  TGDemo_setup.sql
  2. Open and examine the TGDemo_setup.sql file.

    cat TGDemo_setup.sql

    $ cat TGDemo_setup.sql
    ---TGDemo_setup.sql
    
    rem Copyright (c) 2013, Oracle and/or its affiliates. All rights reserved.
    
    set echo on
    
    connect sys/Welcome1@pdb1 as sysdba
    
    execute dbms_service.stop_service(service_name => 'pdb1_tg');
    execute dbms_service.delete_service(service_name => 'pdb1_tg');
    
    execute dbms_service.create_service(service_name => 'pdb1_tg', network_name => 'pdb1_tg');
    execute dbms_service.start_service(service_name => 'pdb1_tg');
    
    --rem modify service to enable TG
    
    declare
    params dbms_service.svc_parameter_array;
    begin
    params('commit_outcome'):='true';
    dbms_service.modify_service('pdb1_tg',params);
    end;
    /
    ...
    

  3. Login to Oracle with SYSDBA crerdentials. Execute the TGDemo_setup.sql file. Exit from sql after execution.


    $ sqlplus / as sysdba
    
    SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 25 10:44:26 2016
    
    Copyright (c) 1982, 2016, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    
    SQL> @TGDemo_setup.sql
    SQL> 
    SQL> connect sys/Welcome1@pdb1 as sysdba
    Connected.
    SQL> 
    SQL> execute dbms_service.stop_service(service_name => 'pdb1_tg');
    BEGIN dbms_service.stop_service(service_name => 'pdb1_tg'); END;                                                            ...
    

  4. Open and examine the TGDemo.java file.

    cat src/TGDemo.java

    Output:

    /* Copyright (c) 2013, Oracle and/or its affiliates. All rights reserved. */
    
    import java.sql.CallableStatement;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.SQLRecoverableException;
    import java.sql.Statement;
    import java.sql.Types;
    
    import oracle.jdbc.LogicalTransactionId;
    import oracle.jdbc.OracleConnection;
    import oracle.jdbc.pool.OracleDataSource;
    
    /**
     * This demo illustrates how application utilizes the Oracle 12c
     * Transaction Guard (TG) feature in Java.
     *
     * It covers two scenarios: with auto-commit mode ON and OFF on the
     * connection used. In each scenario, application does a database
     * update and starts a local transaction; an outage is injected
     * and affects the transaction commit.
     *
     * After outage, application uses the TG APIs to first retrieve
     * the logical transaction id (LTXID) from the original connection,
     * and then obtains the outcome information for that LTXID from
     * the Oracle database server.
     *
     * For more information regarding TG, please refer to Oracle 12c
     * documentation including Oracle JDBC driver's javadoc.
     *
     * @author tzhou
     */
    public class TGDemo {
    
      static final String host = "localhost";
      static final String port = "1521";
      static final String service = "pdb1_tg";
      static final String jdbcURL =
        "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)"+"(HOST="+host+")(PORT="+port+"))(CONNECT_DATA=(SERVICE_NAME="+service+")))";
    
      static final String user = "hr";
      static final String passwd = "hr";
    
      static OracleDataSource ods = null;
    
      public static void main(String[] args) throws Exception {
    
        show("== Demo begins ==");
    
        TGDemo demo = new TGDemo();
        demo.test();
    
        show("\n== Demo completes ==");
      }
    
      void test() throws Exception {
    
        test1(true);  // auto-commit ON
        test1(false); // auto-commit OFF
      }
    
      void setup(OracleConnection conn) throws Exception {
    
        show("\n  Setting up tables");
    
        trySQL(conn, "drop table TGDemo_tab");
        doSQL(conn, "create table TGDemo_tab (c1 number, c2 varchar2(20))");
      }
    
      OracleConnection getOneConnection() throws Exception {
    
        show("\nConnect URL: " + jdbcURL);
    
        if (ods == null) {
          ods = new OracleDataSource();
          ods.setURL(jdbcURL);
          ods.setUser(user);
          ods.setPassword(passwd);
        }
    
        final OracleConnection newConn = (OracleConnection) ods.getConnection();
        show("\n  Obtained a new connection");
    
        return newConn;
      }
    
      void test1(boolean autoCommit) throws Exception {
    
        String testDescription = autoCommit ? "auto-commit ON" : "auto-commit OFF";
        show("\n-- Test case: "+testDescription+" --");
    
        try
        {
          OracleConnection conn = getOneConnection();
          setup(conn);
    
          conn.setAutoCommit(autoCommit);
          show ("\n  Connection.getAutoCommit() = " + conn.getAutoCommit());
    
          Statement stmt = conn.createStatement();
          if (autoCommit)
            injectOutage(conn);
    
          try {
            stmt.execute("insert into TGDemo_tab values(200, 'value200')");
            show("\n  Perform database update");
    
            if (!autoCommit) {
              injectOutage(conn);
    
              show("\n  Committing local transaction");
              conn.commit();
              show("\n  Local transaction is committed");
            }
          } catch (SQLRecoverableException sqlrexc) {
    
            show("\nConnection hit by outage");
    
            LogicalTransactionId ltxid = getLtxid(conn);
            show("  Current LTXID: " + ((ltxid==null) ? "null" : ltxid));
    
            show ("\n  " + checkLtxidOutcome(ltxid));
    
            // Optional: recovery actions by app
          }
    
          show ("\n  Close the connection");
          conn.abort();
          conn.close();
    
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
    
      LogicalTransactionId getLtxid(OracleConnection oconn)
        throws Exception {
    
        show ("\n  Obtaining LTXID from connection hit by outage");
        LogicalTransactionId ltxid = oconn.getLogicalTransactionId();
    
        return ltxid;
      }
    
      String checkLtxidOutcome(LogicalTransactionId ltxid)
        throws Exception {
    
        OracleConnection newConn = getOneConnection();
        String ret = checkLtxidOutcome(newConn, ltxid);
        newConn.close();
    
        return ret;
      }
    
      String checkLtxidOutcome(
        Connection conn,
        LogicalTransactionId ltxid)
        throws Exception {
    
        String ret = "";
        CallableStatement cstmt = null;
        try {
          String call = "{call get_ltxid_outcome_wrap(?, ?, ?) } ";
          cstmt = conn.prepareCall(call);
          cstmt.setBytes(1, ltxid.getBytes());
          cstmt.registerOutParameter(2, Types.INTEGER);
          cstmt.registerOutParameter(3, Types.INTEGER);
          cstmt.execute();
    
          int committed = cstmt.getInt(2);
          int user_call_completed = cstmt.getInt(3);
          ret = "LTXID OUTCOME: committed="+(committed==1? "true":"false")+
            " user_call_completed="+(user_call_completed==1? "true":"false");
        } catch (SQLException e) {
          ret = e.toString();
        } finally {
          try {
            cstmt.close();
          } catch (SQLException e) { }
        }
    
        return ret;
      }
    
      void injectOutage(OracleConnection conn) throws Exception {
    
        show("\nOutage happens");
    
        Connection sysConn =
          DriverManager.getConnection(jdbcURL, "system", "oracle");
        String sql = "begin DBMS_SERVICE.DISCONNECT_SESSION('"+service+"', 1); end;";
        doSQL(sysConn, sql);
        sysConn.close();
      }
    /*
    
      // Alternative implementation that injects outage from server-side
      void injectOutage(OracleConnection conn) {
    
        show("\nOutage happens");
    
        CallableStatement cstmt = null;
        try {
          String call = "{call reset_tst_outage_tbl } ";
          cstmt = conn.prepareCall(call);
          cstmt.execute();
    
          call = "{call inject_outage_once } ";
          cstmt = conn.prepareCall(call);
          cstmt.execute();
        } catch (SQLException e) {
          // Outage triggers this exception
          // e.printStackTrace();
        } finally {
          try {
            cstmt.close();
          } catch (SQLException e) { }
        }
      }
    
    */
      void trySQL(Connection connection, String sql)
        throws SQLException {
    
        Statement stmt = connection.createStatement ();
        try {
          stmt.execute (sql);
        } catch (SQLException e) {
    
        } finally {
          stmt.close ();
        }
      }
    
      void doSQL(Connection connection, String sql)
        throws SQLException {
    
        Statement stmt = null;
        try {
          stmt = connection.createStatement ();
          stmt.execute (sql);
        } finally {
          if (stmt != null)
            stmt.close();
        }
      }
    
      static void show(String msg) {
        System.out.println(msg);
      }
    }
    

  5. Use ant to build and run the build.xml file.

    ant

    Observe the output.

    /*
    Buildfile: /u01/oracle/jdbchol/TG_Demo/build.xml
    
    print.properties:
         [echo] TGDemo: Sample demonstrating Transaction Guard
         [echo]    Look at Readme.txt for details
    
    init:
       [delete] Deleting directory /u01/oracle/jdbchol/TG_Demo/classes
        [mkdir] Created dir: /u01/oracle/jdbchol/TG_Demo/classes
    
    run:
        [javac] Compiling 1 source file to /u01/oracle/jdbchol/TG_Demo/classes
         [java] == Demo begins ==
         [java] 
         [java] -- Test case: auto-commit ON --
         [java] 
         [java] Connect URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1_tg)))
         [java] 
         [java]   Obtained a new connection
         [java] 
         [java]   Setting up tables
         [java] 
         [java]   Connection.getAutoCommit() = true
         [java] 
         [java] Outage happens
         [java] 
         [java] Connection hit by outage
         [java] 
         [java]   Obtaining LTXID from connection hit by outage
         [java]   Current LTXID: oracle.jdbc.driver.LogicalTransactionId@6f7fd0e6
         [java] 
         [java] Connect URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1_tg)))
         [java] 
         [java]   Obtained a new connection
         [java] 
         [java]   LTXID OUTCOME: committed=false user_call_completed=false
         [java] 
         [java]   Close the connection
         [java] 
         [java] -- Test case: auto-commit OFF --
         [java] 
         [java] Connect URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1_tg)))
         [java] 
         [java]   Obtained a new connection
         [java] 
         [java]   Setting up tables
         [java] 
         [java]   Connection.getAutoCommit() = false
         [java] 
         [java]   Perform database update
         [java] 
         [java] Outage happens
         [java] 
         [java]   Committing local transaction
         [java] 
         [java] Connection hit by outage
         [java] 
         [java]   Obtaining LTXID from connection hit by outage
         [java]   Current LTXID: oracle.jdbc.driver.LogicalTransactionId@2698dc7
         [java] 
         [java] Connect URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1_tg)))
         [java] 
         [java]   Obtained a new connection
         [java] 
         [java]   LTXID OUTCOME: committed=false user_call_completed=false
         [java] 
         [java]   Close the connection
         [java] 
         [java] == Demo completes ==
    
    BUILD SUCCESSFUL
    Total time: 2 seconds
    [oracle@host01 TG_Demo]$ 

Application Continuity for Java

It is complex for application development to mask outages of the database session and as a result errors and timeouts are often exposed to the end users and applications. Application Continuity attempts to mask outages from end users and applications by recovering the database session following recoverable outages. Application Continuity performs this recovery beneath the application so that the outage appears to the application as a delayed execution. For the recovery to succeed, the data and the messages restored to the client by Application Continuity must be exactly the same as those that the application has seen and potentially made decisions on.

Application Continuity is invoked for outages that are recoverable, typically related to underlying software, foreground, hardware, communications, network or storage layers. Application Continuity is used to improve the user experience when handling both unplanned outages and planned outages.

Application Continuity is a feature that enables the replay, in a non-disruptive and rapid manner, of a request against the database after a recoverable error that makes the database session unavailable. After a successful replay, the application can continue where the database session left off, instead of having users left in doubt not knowing what happened to their transaction. With Application Continuity, the end user experience is improved by masking many outages, planned and unplanned, without the application developer needing to attempt to recover the request.

In this section, you run an application that uses the standard JDBC and the new Replay JDBC drivers to connect to the database. Upon database failure, the Replay driver masks the outage with a slight additional latency while the standard JDBC driver will raise a SQLException to the application.

After extracting the files as described in the Prerequisites section, configure the server for Application Continuity

Starting up Demo Application

To test Application Continuity in Java, you execute two copies of the same application, one using the normal JDBC driver and the other using the 'Replay' JDBC driver with a Replay datasource. Upon database failure, the Replay driver masks the outage to the application with slight additional latency, while the standard JDBC driver throws a SQLException to the application. The database failure in a single database instance is simulated by killing the session's foreground processes.

  1. Open a new terminal. Navigate to the u01/oracle/jdbchol/AC_Demo directory. Execute ls to see the contents inside this directory.

    cd /u01/oracle/jdbchol/AC_Demo

    $ cd /u01/oracle/jdbchol/AC_Demo/
    $ ls
    actest.jar                  actest_replay.properties  runnoreplay
    actest_noreplay.properties  actest_setup.sql          runreplay
    
  2. Open and examine the actest_setup.sql file.

    cat actest_setup.sql

    $ cat actest_setup.sql 
    
    rem Copyright (c) 2012, 2013, Oracle and/or its affiliates. All rights reserved.
    
    set echo on
    
    connect sys/Welcome1@pdb1 as sysdba
    
    execute dbms_service.stop_service(service_name => 'pdb1_ac');
    execute dbms_service.delete_service(service_name => 'pdb1_ac');
    
    execute dbms_service.create_service(service_name => 'pdb1_ac', network_name => 'pdb1_ac');
    execute dbms_service.start_service(service_name => 'pdb1_ac');
    
    --rem modify service to enable AC
    
    declare
    params dbms_service.svc_parameter_array;
    begin
    params('FAILOVER_TYPE'):='TRANSACTION';
    params('REPLAY_INITIATION_TIMEOUT'):=1800;
    params('RETENTION_TIMEOUT'):=86400;
    params('FAILOVER_DELAY'):=10;
    params('FAILOVER_RETRIES'):=30;
    params('commit_outcome'):='true';
    params('aq_ha_notifications'):='true';
    dbms_service.modify_service('pdb1_ac',params);
    end;
    /
    commit;
    
    connect hr/hr@pdb1;
    
    drop table emp;
    create table emp(
     empno number(4) not null,
     ename varchar2(10),
     job char(9),
     mgr number(4),
     hiredate date,
     sal number(7,2),
     comm number(7,2),
     deptno number(2));
    
    insert into emp values(8839,'KING','PRESIDENT',NULL,'17-NOV-81',50000,NULL,10);
    insert into emp values(8698,'BLAKE','MANAGER',NULL,'17-NOV-81',8000,NULL,10);
    insert into emp values(8782,'CLARK','MANAGER',NULL,'17-NOV-81',8000,NULL,10);
    insert into emp values(8566,'JONES','MANAGER',NULL,'17-NOV-81',8000,NULL,10);
    insert into emp values(8654,'MARTIN','SALESMAN',NULL,'17-NOV-81',7000,NULL,10);
    insert into emp values(8499,'ALLEN','MANAGER',NULL,'17-NOV-81',9000,NULL,10);
    insert into emp values(8844,'TURNER','CLERK',NULL,'17-NOV-81',5000,NULL,10);
    insert into emp values(8900,'JAMES','MANAGER',NULL,'17-NOV-81',9000,NULL,10);
    insert into emp values(8521,'WARD','PRGRMMER',NULL,'17-NOV-81',9000,NULL,10);
    insert into emp values(8902,'FORD','SALESMAN',NULL,'17-NOV-81',7000,NULL,10);
    insert into emp values(8369,'SMITH','PRGRMMER',NULL,'17-NOV-81',8000,NULL,10);
    insert into emp values(8788,'SCOTT','CLERK',NULL,'17-NOV-81',6000,NULL,10);
    insert into emp values(8876,'ADAMS','PRGRMMER',NULL,'17-NOV-81',7000,NULL,10);
    insert into emp values(8934,'MILLER','SALESMAN',NULL,'17-NOV-81',9000,NULL,10);
    
    commit;
    
    disconnect
    
    
  3. Login to Oracle. Execute the actest_setup.sql file. Exit from sql after execution.

    sqlplus / as sysdba

    @actest_setup.sql

    exit

    $ sqlplus / as sysdba
                               
    SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 26 10:22:34 2016
    
    Copyright (c) 1982, 2016, Oracle.  All rights reserved.
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    
    SQL> @actest_setup.sql
    SQL> 
    SQL> connect sys/Welcome1@pdb1 as sysdba
    Connected.
    SQL> 
    SQL> execute dbms_service.stop_service(service_name => 'pdb1_ac');
    BEGIN dbms_service.stop_service(service_name => 'pdb1_ac'); END;
    
    *
    ERROR at line 1:
    ORA-44304: service pdb1_ac does not exist
    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
    ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 23
    ORA-06512: at "SYS.DBMS_SERVICE", line 519
    ORA-06512: at line 1
    
    
    SQL> execute dbms_service.delete_service(service_name => 'pdb1_ac');
    BEGIN dbms_service.delete_service(service_name => 'pdb1_ac'); END;
    
    *
    ERROR at line 1:
    ORA-44304: service pdb1_ac does not exist
    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
    ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 23
    ORA-06512: at "SYS.DBMS_SERVICE", line 453
    ORA-06512: at line 1
    
    
    SQL> 
    SQL> execute dbms_service.create_service(service_name => 'pdb1_ac', network_name => 'pdb1_ac');
    
    PL/SQL procedure successfully completed.
    
    SQL> execute dbms_service.start_service(service_name => 'pdb1_ac');
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SQL> --rem modify service to enable AC
    SQL> 
    SQL> declare
      2  params dbms_service.svc_parameter_array;
      3  begin
      4  params('FAILOVER_TYPE'):='TRANSACTION';
      5  params('REPLAY_INITIATION_TIMEOUT'):=1800;
      6  params('RETENTION_TIMEOUT'):=86400;
      7  params('FAILOVER_DELAY'):=10;
      8  params('FAILOVER_RETRIES'):=30;
      9  params('commit_outcome'):='true';
     10  params('aq_ha_notifications'):='true';
     11  dbms_service.modify_service('pdb1_ac',params);
     12  end;
     13  /
    
    PL/SQL procedure successfully completed.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> 
    SQL> connect hr/hr@pdb1;
    Connected.
    SQL> 
    SQL> drop table emp;
    
    Table dropped.
    
    SQL> create table emp(
      2   empno number(4) not null,
      3   ename varchar2(10),
      4   job char(9),
      5   mgr number(4),
      6   hiredate date,
      7   sal number(7,2),
      8   comm number(7,2),
      9   deptno number(2));
    
    Table created.
    
    SQL> 
    SQL> insert into emp values(8839,'KING','PRESIDENT',NULL,'17-NOV-81',50000,NULL,10);
    
    1 row created.
    
    SQL> insert into emp values(8698,'BLAKE','MANAGER',NULL,'17-NOV-81',8000,NULL,10);
    
    1 row created.
    
    SQL> insert into emp values(8782,'CLARK','MANAGER',NULL,'17-NOV-81',8000,NULL,10);
    
    1 row created.
    
    SQL> insert into emp values(8566,'JONES','MANAGER',NULL,'17-NOV-81',8000,NULL,10);
    
    1 row created.
    
    SQL> insert into emp values(8654,'MARTIN','SALESMAN',NULL,'17-NOV-81',7000,NULL,10);
    
    1 row created.
    
    SQL> insert into emp values(8499,'ALLEN','MANAGER',NULL,'17-NOV-81',9000,NULL,10);
    
    1 row created.
    
    SQL> insert into emp values(8844,'TURNER','CLERK',NULL,'17-NOV-81',5000,NULL,10);
    
    1 row created.
    
    SQL> insert into emp values(8900,'JAMES','MANAGER',NULL,'17-NOV-81',9000,NULL,10);
    
    1 row created.
    
    SQL> insert into emp values(8521,'WARD','PRGRMMER',NULL,'17-NOV-81',9000,NULL,10);
    
    1 row created.
    
    SQL> insert into emp values(8902,'FORD','SALESMAN',NULL,'17-NOV-81',7000,NULL,10);
    
    1 row created.
    
    SQL> insert into emp values(8369,'SMITH','PRGRMMER',NULL,'17-NOV-81',8000,NULL,10);
    
    1 row created.
    
    SQL> insert into emp values(8788,'SCOTT','CLERK',NULL,'17-NOV-81',6000,NULL,10);
    
    1 row created.
    
    SQL> insert into emp values(8876,'ADAMS','PRGRMMER',NULL,'17-NOV-81',7000,NULL,10);
    
    1 row created.
    
    SQL> insert into emp values(8934,'MILLER','SALESMAN',NULL,'17-NOV-81',9000,NULL,10);
    
    1 row created.
    
    SQL> 
    SQL> commit;
    
    Commit complete.
    
    SQL> 
  4. Open and examine the actest_replay.properties file.

    cat actest_replay.properties

    
    $ cat actest_replay.properties 
    username=hr
    password=hr
    autoCommit=false
    
    # Use new 12.1 replay datasource
    datasource=oracle.jdbc.replay.OracleDataSourceImpl
    
    url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1_ac)))
    
    # UCP setting:
    ucp_pool_size=2
    ucp_validate_connection_on_borrow=true
    ucp_connection_wait_timeout=20
    
    # Think Time taken to process the results from the database. Time in milliseconds.
    # -1 means no sleep.
    thread_think_time=20
    
    # Number of concurrent threads running in the application
    # UCP is tuned to have MAX and MIN limit set to this
    number_of_threads=6
    
    verbose=true
  5. Open and examine the actest_noreplay.properties file.

    cat actest_noreplay.properties

    
    $ cat actest_noreplay.properties 
    username=hr
    password=hr
    autoCommit=false
    
    # Use standard  12.1 datasource no replay
    datasource=oracle.jdbc.pool.OracleDataSource
    #replay_datasource=oracle.jdbc.replay.OracleDataSourceImpl
    
    url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1_ac)))
    
    # UCP setting:
    ucp_pool_size=2
    ucp_validate_connection_on_borrow=true
    ucp_connection_wait_timeout=20
    
    # Think Time taken to process the results from the database. Time in milliseconds.
    # -1 means no sleep.
    thread_think_time=20
    
    # Number of concurrent threads running in the application
    # UCP is tuned to have MAX and MIN limit set to this
    number_of_threads=6
    
    verbose=true
  6. Open two terminal windows. Perform the following steps in both the terminals:

    1. In Both the terminals, Navigate to the u01/oracle/jdbchol/AC_Demo directory. Execute ls to see the contents inside this directory.

      cd /u01/oracle/jdbchol/AC_Demo

      $ cd /u01/oracle/jdbchol/AC_Demo/
      $ ls
      actest.jar                  actest_replay.properties  runnoreplay
      actest_noreplay.properties  actest_setup.sql          runreplay
      
    2. In Both the terminals, Setup the ORACLE_HOME environment variable to point to the Oracle database installation directory. Provide the given values when prompted.

      . oraenv

      ORACLE_SID = [orcl] ?  orcl
      $ . oraenv
      ORACLE_SID = [oracle] ? orcl
      The Oracle base has been set to /u01/app/oracle
      
    3. In One of the terminal execute runreplay and in the other terminal execute runnoreplay.

      sh runreplay

      sh runnoreplay

      $ sh runreplay
      ######################################################
      Connecting to jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1_ac)))
        # of Threads             : 6
        UCP pool size            : 2
        Thread think time        : 20 ms
      ######################################################
      
      2 active connections, avg response time from db 24 ms
      2 active connections, avg response time from db 28 ms
      2 active connections, avg response time from db 21 ms
      2 active connections, avg response time from db 20 ms
      2 active connections, avg response time from db 17 ms
      1 active connections, avg response time from db 17 ms
      2 active connections, avg response time from db 16 ms
      ...

      $ sh runreplay
      
      ######################################################
      Connecting to jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1_ac)))
        # of Threads             : 6
        UCP pool size            : 2
        Thread think time        : 20 ms
      ######################################################
      
      2 active connections, avg response time from db 32 ms
      2 active connections, avg response time from db 18 ms
      2 active connections, avg response time from db 18 ms
      2 active connections, avg response time from db 15 ms
      2 active connections, avg response time from db 15 ms
      2 active connections, avg response time from db 16 ms
      ...

      Note: In the first terminal window, execute the runnoreplay script. In the second window, execute the runreplay script. These scripts are found in the files folder that you extracted to your working directory as part of the prerequisites. Both the scripts perform a series of database operations in an infinite loop. At periodic intervals, they display the number of connections in the connection pool and the average response time for the database operations. Any exceptions encountered are displayed on the terminal's console.
      The runnoreplay script uses the standard JDBC driver while the runreplay script uses the Replay JDBC driver

      Observe the output.

      Note: Please donot close any terminals as it will be required in the "Simulating a Database Failure in the Application" section.

Simulating a Database Failure in the Application

You can simulate database failure in code.

  1. Login to Oracle. Execute the following PL/SQL procedure to disconnect all the connected database sessions. This simulates a database failure. Exit from sql after execution.

    sqlplus sys/Welcome1 as sysdba

    execute dbms_service.disconnect_session('pdb1_ac',dbms_service.immediate);

    exit

    $ sqlplus sys/Welcome1 as sysdba
    
    SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 28 06:26:34 2016
    
    Copyright (c) 1982, 2016, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    
    SQL> execute dbms_service.disconnect_session('pdb1_ac',dbms_service.immediate);
    
    PL/SQL procedure successfully completed.
    
    SQL> exit

Observe the output in all the terminals.

Note: Notice that the application running with the standard JDBC driver encounters a SQLException when the database session was disconnected. However, the application using the Replay JDBC driver executes successfully even when its database session has been disconnected. You will notice that there is a slightly larger latency immediately after the session has been has been terminated.

Multitenant Data Sources

Multitenant architecture is a new Oracle database architecture which allows operating multiple databases (Pluggable databases or PDBs) using a single instance (a.k.a. Container database or CDB).

Starting from Oracle Database 12c Release 2 (12.2.0.1), multiple data sources of multitenant data sources can share a common pool of connections in UCP and repurpose connections in the common connection pool, whenever needed. This common connection pool is called as Shared Pool. The Shared Pool optimizes system resources for a scalable deployment of multitenant Java applications in Oracle Database Multitenant environment.

By combining these databases into a CDB, you can make better use of your hardware resources and database administrator resources. In addition, you can move PDBs from one CDB to another without requiring changes to the applications that depend on the PDB.

To use this feature, you must use an XML configuration file.

Multitenant common pool in 12.2.0.1

  1. Open a new terminal. Navigate to the u01/oracle/jdbchol/SharedPoolTest directory. Execute ls to see the contents inside this directory.

    cd /u01/oracle/jdbchol/SharedPoolTest
    $ cd /u01/oracle/jdbchol/SharedPoolTest/
    $ ls
    build.xml  SharedPoolTest_setup.sql  src
    

  2. Open and examine the SharedPoolTest_setup.sql file.

    cat SharedPoolTest_setup.sql

    $ cat SharedPoolTest_setup.sql 
    ---SharedPoolTest_setup.sql
    
    rem Copyright (c) 2013, Oracle and/or its affiliates. All rights reserved.
    
    set echo on
    connect sys/Welcome1 as sysdba
    set echo on
    drop user c##common_user;
    create user c##common_user identified by c##common_user default tablespace system quota unlimited on system container=all;
    grant connect, resource, unlimited tablespace, create any directory,drop any directory to c##common_user container=all;
    grant create view, create session, create synonym, create database link, create sequence,                                   CREATE TABLE to c##common_user container=all;
    show errors
    disconn
     

  3. Login to Oracle with SYSDBA crerdentials. Execute the SharedPoolTest_setup.sql file. Exit from sql after execution.

    sqlplus / as sysdba

    exit

    $ sqlplus / as sysdba
    
    SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 28 06:31:30 2016
    
    Copyright (c) 1982, 2016, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    
    SQL> @SharedPoolTest_setup.sql
    SQL> 
    SQL> connect sys/Welcome1 as sysdba
    Connected.
    
    SQL> 
    SQL> set echo on
    SQL> 
    SQL> drop user c##common_user;
    
    User dropped.
    
    
    SQL> create user c##common_user identified by c##common_user default tablespace system quota unlimited on system container=all;
    
    User created.
    
     
    SQL> grant connect, resource, unlimited tablespace, create any directory,drop any directory to c##common_user container=all;
    
    Grant succeeded.
    
    
    SQL> grant create view, create session, create synonym, create database link, create sequence, CREATE TABLE to c##common_user container=all;
    
    Grant succeeded.
    
    
    SQL> show errors
    No errors.
    SQL> 
    Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    SQL> 
    
    

  4. Specify the initial configuration of Shared Pools through an XML configuration file.  You can specify the XML configuration file for UCP through the system property oracle.ucp.jdbc.xmlConfigFile. Open and examine the SharedPoolTest.xml file.

    cd /u01/oracle/jdbchol/SharedPoolTest/
    cat SharedPooltest.xml
    <?xml version="1.0" encoding="UTF-8"?>
    
    <ucp-properties>
    
    
    <connection-pool
    connection-pool-name="pool1"
    connection-factory-class-name="oracle.jdbc.pool.OracleDataSource"
    url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)))"
    user="c##common_user" 
    password="c##common_user" 
    initial-pool-size="10"  
    min-pool-size="2" 
    max-pool-size="30" 
    max-connections-per-service="15" 
    shared="true">
    <connection-property value="20000" name="oracle.jdbc.ReadTimeout"/>
    
    <connection-property value="20000" name="oracle.net.OUTBOUND_CONNECT_TIMEOUT"/>
    
    <data-source description="pdb1 data source" service="pdb1" data-source-name="pds1"/>
    
    <data-source description="pdb2 data source" service="pdb2" data-source-name="pds2"/>
    
    </connection-pool>
    
    </ucp-properties>
    
    
  5. Open and examine the SharedPoolTest.java file.

    cat src/SharedPoolTest.java

    $ cat src/SharedPoolTest.java
    
    
    /**
     * This sample test demonstrates the functionality of multi-tenant shared pools.
     * With the use of shared pools now it is possible for more than one datasources
     * to share a common pool provided they are connecting to the same database with
     * a single url. To use shared pool functionality, all the datasources
     * accessing shared pools must be defined in xml configuration file along with
     * the pool properties. Similar to shared pools, we can also define non-shared
     * pool configuration in xml. The code example shows how to get a connection from a
     * pool which is defined in xml, pool reconfiguration, datasource reconfiguration,
     * and adding a new datasource to running pool.
     * 
     */
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Properties;
    
    import oracle.ucp.admin.UniversalConnectionPoolManager;
    import oracle.ucp.admin.UniversalConnectionPoolManagerImpl;
    import oracle.ucp.jdbc.PoolDataSource;
    import oracle.ucp.jdbc.PoolDataSourceFactory;
    
    public class SharedPoolTest {
    
      static String xmlFileURI ="file:/u01/oracle/jdbchol/SharedPoolTest/SharedPoolTest.xml";
      static String poolName = "pool1";
    
      public static void main(String[] args) throws Exception {
        System.out
            .println("----------------- Multi-Tenant shared pool configuration using XML----------------------- ---");
    
        // Java system property to specify the location of xml configuration file
        // which has pool and datasource properties defined in it.
         System.setProperty("oracle.ucp.jdbc.xmlConfigFile", xmlFileURI);
    
        // The xml file used in this code example defines one connection pool with
        // connection-pool-name -"pool1" and two datasources with datasource-name
        // "pds1" and "pds2" which are using this shared pool.
    
        System.out
            .println("\n################ UseCase:1  Initial Pool/DataSource Configuration using XML     ###############");
    
        // Get the datasource instance, named as "pds1" in xml config file
        PoolDataSource pds1 = PoolDataSourceFactory.getPoolDataSource("pds1");
        Connection pds1Conn = pds1.getConnection();
    
        System.out.println("\nTest connection obtained using datasource1 -pds1");
        testConnection(pds1Conn);
        pds1Conn.close();
    
        // Get the datasource instance, named as "pds2" in xml config file
        PoolDataSource pds2 = PoolDataSourceFactory.getPoolDataSource("pds2");
        Connection pds2Conn = pds2.getConnection();
    
        System.out.println("\nTest connection obtained using datasource2 -pds2");
        testConnection(pds2Conn);
        pds2Conn.close();
    
        System.out.println("pds1.getAvailableConnectionsCount() :"
            + pds1.getAvailableConnectionsCount());
        System.out.println("pds1.getBorrowedConnectionsCount() :"
            + pds1.getBorrowedConnectionsCount());
    
        System.out
            .println(" \n\n################ UseCase:2 Pool Reconfiguration    ##########################");
    
        UniversalConnectionPoolManager pool_mgr;
        pool_mgr = UniversalConnectionPoolManagerImpl
            .getUniversalConnectionPoolManager();
    
        System.out.println("\nPool properties before pool reconfiguration");
        printPoolProperties(pds1);
    
        // reconfigure the connection pool to modify the pool properties, new pool
        // properties are passed to the API in form of key value pair.
        pool_mgr.reconfigureConnectionPool(poolName, getPoolReconfigProperties());
    
        System.out.println("\nPool properties after pool reconfiguration");
        printPoolProperties(pds1);
    
        System.out
            .println(" \n\n################ UseCase:3 Existing DataSource Reconfiguration   ##########################");
    
        System.out
            .println("\nDatasource1 - pds1 properties before datasource reconfiguration");
    
        printDataSourceProperties(pds1);
    
        // reconfigure the existing datasource, new properties are passed as
        // key-value pair.
        pds1.reconfigureDataSource(getPDS1ReconfigProperties());
    
        System.out
            .println("\nDatasource1 - pds1 properties after datasource reconfiguration");
    
        printDataSourceProperties(pds1);
    
        System.out
            .println("\nDatasource2 - pds2 properties before datasource reconfiguration");
        printDataSourceProperties(pds2);
    
        // reconfigure the existing datasource, new properties are passed as
        // key-value pair.
        pds2.reconfigureDataSource(getPDS2ReconfigProperties());
    
        System.out
            .println("\nDatasource2 - pds2 properties after datasource reconfiguration");
        printDataSourceProperties(pds2);
    
        System.out
            .println(" \n\n################ UseCase:4 Add new DataSource to Running pool   ##########################");
    
        // adds a new datasource to pool, new datasource properties are passed as
        // key-value pair.
        PoolDataSource pds3 = PoolDataSourceFactory
            .getPoolDataSource(getNewDataSourceReconfigProperties());
        Connection pds3Conn = pds3.getConnection();
    
        System.out.println("\nTest connection obtained using new datasource -pds3");
        testConnection(pds3Conn);
        pds3Conn.close();
    
        System.out.println("\nPrint new datasource - pds3 properties");
        printDataSourceProperties(pds3);
    
        System.out
            .println("\n-------------------------------    End of Test Case -------------------------------------------------");
    
      }
    
      private static Properties getPoolReconfigProperties() {
        Properties properties = new Properties();
        properties.put("initialPoolSize", "12");
        properties.put("maxPoolSize", "40");
        return properties;
      }
    
      private static Properties getPDS1ReconfigProperties() {
        Properties properties = new Properties();
        properties.put("description", "pdb1 datasource reconfigured");
        properties.put("serviceName", "pdb2");
        return properties;
      }
    
      private static Properties getPDS2ReconfigProperties() {
        Properties properties = new Properties();
        properties.put("description", "pdb2 datasource reconfigured");
        properties.put("serviceName", "pdb1");
        return properties;
      }
    
      private static Properties getNewDataSourceReconfigProperties() {
        Properties properties = new Properties();
        properties.put("connectionPoolName", poolName);
        properties.put("dataSourceName", "pds3");
        properties.put("description", "pds3 datasource added at run time");
        properties.put("serviceName", "pdb1");
        return properties;
      }
    
      static void printDataSourceProperties(PoolDataSource pds) throws SQLException {
        System.out.println("DataSource Name = " + pds.getDataSourceName());
        System.out.println("Description = " + pds.getDescription());
        System.out.println("Service Name = " + pds.getServiceName());
      }
    
      static void printPoolProperties(PoolDataSource pds) throws SQLException {
        System.out.println("Max pool Size = " + pds.getMaxPoolSize());
        System.out.println("Initial pool Size = " + pds.getInitialPoolSize());
        System.out.println("Min pool Size = " + pds.getMinPoolSize());
      }
    
      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();
        }
    
      }
    
    }
    
    

  6. Use ant to build and run the build.xml file.

    ant

    $ ant
    Buildfile: /u01/oracle/jdbchol/SharedPoolTest/build.xml
    
    print.properties:
         [echo] Multitenant: Sample demonstrating Multitenant database
         [echo]    Look at Readme.txt for details
    
    init:
       [delete] Deleting directory /u01/oracle/jdbchol/SharedPoolTest/classes
        [mkdir] Created dir: /u01/oracle/jdbchol/SharedPoolTest/classes
    
    run:
        [javac] /u01/oracle/jdbchol/SharedPoolTest/build.xml:32: warning: 'includeantruntime' was not set, defaulting to build.sysclasspath=last; set to false for repeatable builds
        [javac] Compiling 1 source file to /u01/oracle/jdbchol/SharedPoolTest/classes
         [java] ----------------- Multi-Tenant shared pool configuration using XML----------------------- ---
         [java] 
         [java] ################ UseCase:1  Initial Pool/DataSource Configuration using XML     ###############
         [java] 
         [java] Test connection obtained using datasource1 -pds1
         [java] Connection Db name from sys context=orcl
         [java] Connection Svc name from sys context=pdb1
         [java] Connection user Name :  C##COMMON_USER
         [java] 
         [java] Test connection obtained using datasource2 -pds2
         [java] Connection Db name from sys context=orcl
         [java] Connection Svc name from sys context=pdb2
         [java] Connection user Name :  C##COMMON_USER
         [java] pds1.getAvailableConnectionsCount() :12
         [java] pds1.getBorrowedConnectionsCount() :0
         [java]  
         [java] 
         [java] ################ UseCase:2 Pool Reconfiguration    ##########################
         [java] 
         [java] Pool properties before pool reconfiguration
         [java] Max pool Size = 30
         [java] Initial pool Size = 10
         [java] Min pool Size = 2
         [java] 
         [java] Pool properties after pool reconfiguration
         [java] Max pool Size = 40
         [java] Initial pool Size = 12
         [java] Min pool Size = 2
         [java]  
         [java] 
         [java] ################ UseCase:3 Existing DataSource Reconfiguration   ##########################
         [java] 
         [java] Datasource1 - pds1 properties before datasource reconfiguration
         [java] DataSource Name = pds1
         [java] Description = pdb1 data source
         [java] Service Name = pdb1
         [java] 
         [java] Datasource1 - pds1 properties after datasource reconfiguration
         [java] DataSource Name = pds1
         [java] Description = pdb1 datasource reconfigured
         [java] Service Name = pdb2
         [java] 
         [java] Datasource2 - pds2 properties before datasource reconfiguration
         [java] DataSource Name = pds2
         [java] Description = pdb2 data source
         [java] Service Name = pdb2
         [java] 
         [java] Datasource2 - pds2 properties after datasource reconfiguration
         [java] DataSource Name = pds2
         [java] Description = pdb2 datasource reconfigured
         [java] Service Name = pdb1
         [java]  
         [java] 
         [java] ################ UseCase:4 Add new DataSource to Running pool   ##########################
         [java] 
         [java] Test connection obtained using new datasource -pds3
         [java] Connection Db name from sys context=orcl
         [java] Connection Svc name from sys context=pdb1
         [java] Connection user Name :  C##COMMON_USER
         [java] 
         [java] Print new datasource - pds3 properties
         [java] DataSource Name = pds3
         [java] Description = pds3 datasource added at run time
         [java] Service Name = pdb1
         [java] 
         [java] -------------------------------    End of Test Case -------------------------------------------------
    
    BUILD SUCCESSFUL
    Total time: 21 seconds
    

Observe the output.

JDBC and UCP support for Database Sharding

Oracle JDBC and UCP support new Oracle database sharding architecture to help achieve a complete, end to end solution for our customers. The JDBC driver can create a connection to the right shard when an appropriate sharding key is passed. UCP, a features rich Java connection pool is not only capable of creating connections based on the sharding key, it also caches the shard routing info which avoids going to the shard director thereby fastening future connection requests. UCP also transparently handles re-sharding and chunk movements.

Some of the important APIs introduced as part of Sharding are highlighted below.

Building Oracle Sharding Key

OracleDataSource (JDBC) and PoolDataSource (UCP) furnish the following APIs for building shard keys. subkey is the key used for partitioning the data. Note that for composite sharding, superShardingKey is created using the same method createShardingKeyBuilder().

OracleShardingKey shardKey =  pds.createShardingKeyBuilder()
                                     .subkey(<email>, OracleType.VARCHAR2) 
                                     .subkey(<custid>, OracleType.NUMBER)
                                     .build();

OracleShardingKey shardKey =  ods.createShardingKeyBuilder() 
                                     .subkey(<email>, OracleType.VARCHAR2) 
                                     .subkey(<custid>, OracleType.NUMBER)
                                     .build();

JDBC Connection with Sharding Key

JDBC Connection with Sharding Key feature creates a JDBC connection to a Shard based on the sharding key.

OracleDataSource ods = new OracleDataSource();
ods.setURL(<DB_URL>);
ods.setUser(<DB_USER>);
ods.setPassword(<DB_PASSWORD>);
Connection connection = ods.createConnectionBuilder()
                                 .shardingKey(<OracleShardingKey>)
                                 .build();

Pool creation

Pool creation feature retreives a connection from UCP based on the Sharding key.

PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();

pds.setURL(<DB_URL>);
pds.setUser(<DB_USER>);
pds.setPassword(<DB_PASSWORD>);
pds.initialPoolSize(10);
pds.maxPoolSize(20);
pds.minPoolSize(5);

//Connection borrowing
Connection connection = pds.createConnectionBuilder()
                                .shardingKey(<OracleShardingKey>) 
                                .build();

JDBC Connection String for accessing a single shard

Single shardJDBC can be accessed using Connection String.The connection URL string takes the SHARDING_KEY and the SUPER_SHARDING_KEY as additional attributes.

jdbc:oracle:thin:@(DESCRIPTION=
			(ADDRESS=
				(HOST=bpodb12s)
				(PORT=1571)
				(PROTOCOL=tcp)
			)
			(CONNECT_DATA=
				(SERVICE_NAME=oltp_rw_srvc.cust_sdb.oradbcloud)
				(SHARDING_KEY=test@test.com)
				(SUPER_SHARDING_KEY=gold)
			)
		)

Shard key setting

Capability to set the sharding key even after a connection is obtained is also provided.

connection.setShardingKey(<ShardingKey>, <SuperShardingKey>)
connection.setShardingKeyIfValid(<sharding_key>,<super_sharding_key>,timeout);

Advanced Security Data Encryption and Integrity

To ensure data integrity during transmission, Oracle Advanced Security generates a cryptographically secure message digest. Starting from Oracle Database 12c Release 1, the SHA-2 list of hashing algorithms are also supported and Oracle Advanced Security uses the following hashing algorithms to generate the secure message digest and includes it with each message sent across a network:

SHA256
SHA384
SHA512

This protects the communicated data from attacks, such as data modification, deleted packets, and replay attacks.

In this section you will learn how to calculate the checksum using SHA256 algorithm:

  1. Open a new terminal. Navigate to the u01/oracle/jdbchol/AES_Demo directory. Execute ls to see the contents inside this directory.

    cd /u01/oracle/jdbchol/AES_Demo

    $ cd /u01/oracle/jdbchol/AES_Demo/
    ls
    build.xml  src
    
  2. Open and examine the AESAndSHA256.java file.

    cat src/AESAndSHA256.java

    $ cat src/AESAndSHA256.java 
    import java.sql.*;
    import java.util.Properties;
    import oracle.net.ano.AnoServices;
    import oracle.jdbc.*;
     
    public class AESAndSHA256
    {
      static final String USERNAME= "hr";
      static final String PASSWORD= "hr";
      static final String URL = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))" +"(CONNECT_DATA=(SERVICE_NAME=pdb1)))";
     
      public static final void main(String[] argv)
      {
        AESAndSHA256 demo = new AESAndSHA256();
        try
        {
          demo.run();
        }catch(SQLException ex)
        {
          ex.printStackTrace();
        }
      }
     
      void run() throws SQLException
      {
        OracleDriver dr = new OracleDriver();
        Properties prop = new Properties();
     
        // We require the connection to be encrypted with either AES256 or AES192.
        // If the database doesn't accept such a security level, then the connection attempt will fail.
        
        prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_ENCRYPTION_LEVEL,AnoServices.ANO_REQUIRED);
        prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_ENCRYPTION_TYPES,"( " + AnoServices.ENCRYPTION_AES256 + "," + AnoServices.ENCRYPTION_AES192 + ")"); 
        // We also require the use of the SHA1 algorithm for data integrity checking.    
        prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_CHECKSUM_LEVEL,AnoServices.ANO_REQUIRED);
    //    prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_CHECKSUM_TYPES,"( " + AnoServices.CHECKSUM_SHA2 + " )");
    prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_CHECKSUM_TYPES,"( " + AnoServices.CHECKSUM_SHA256 + " )");
        prop.setProperty("user",AESAndSHA256.USERNAME);
        prop.setProperty("password",AESAndSHA256.PASSWORD);
        OracleConnection oraConn = (OracleConnection)dr.connect(AESAndSHA256.URL,prop);
        System.out.println("Connection created! Encryption algorithm is: " + oraConn.getEncryptionAlgorithmName() + ", dataintegrity algorithm is: " + oraConn.getDataIntegrityAlgorithmName());    
        oraConn.close();
      }
      
    }
    [oracle@host01 AES_Demo]$ 
  3. Use ant to build and run the build.xml file.

    ant

    
    $ ant
    Buildfile: /u01/oracle/jdbchol/AES_Demo/build.xml
    
    print.properties:
         [echo] AESAndSHA256: Sample for Oracle Advanced Security.
         [echo]    Look at Readme.txt for details
    
    init:
        [mkdir] Created dir: /u01/oracle/jdbchol/AES_Demo/classes
    
    run:
        [javac] Compiling 1 source file to /u01/oracle/jdbchol/AES_Demo/classes
         [java] Connection created! Encryption algorithm is: AES256, dataintegrity algorithm is: SHA256
    
    BUILD SUCCESSFUL
    Total time: 2 seconds

    Observe the output.

Want to Learn More.

In this tutorial, you have learned how to:

  • Work with Invisible Columns
  • Work with Implicit Columns
  • Examine the ImplStmtCache Program
  • Examine the ExplStmtCache Program
  • Binding Types declared in PL/SQL Packages
  • getObject() and setObject()
  • Use Universal Connection Pool
  • Integrate JDBC and UCP with DRCP
  • Work with multitenant databases
  • JDBC and UCP support for Database Sharding
  • Use Transaction Guard and Application Continuity for Java

These tutorials will later be available on Oracle Learning Library.

Resources

More information about the topics covered is available at: