A Java Example for Oracle Application Continuity with Oracle RAC

Introduction

Oracle Application Continuity hides database interruptions from end-users and applications. A simple test using SQL*Plus was discussed in this blog post. However, SQL*Plus is usually not your real application that will be used in production. Also, SQL*Plus is not a pooled application, and it’s recommended to use Connection Pools for Application Continuity.

This blog post provides a simple Java example using a JDBC driver with Universal Connection Pool (UCP) for a quick start connecting to an Oracle RAC database. Refer to the official Oracle documentation at the end of this post for a complete list of recommendations for your production implementations.

The Environment

2-node Oracle RAC database version 19.17 running on Oracle Cloud.
Java JDK 11.
Eclipse IDE for Java Developers on my local Windows PC.

I’m using Eclipse here because it got the highest rate as the favorite Java IDE in my LinkedIn poll. Luckily, it’s my favorite too. Configurations in other IDEs should be very similar.

Database Side

Create Database Service

Create a custom database service as follows:

[oracle@racnode1 ~]$ srvctl add service -db RACCDB_fra -pdb MOVIEDB -service acsrv -preferred RACCDB1,RACCDB2 -stopoption IMMEDIATE -replay_init_time 600 -retention 86400 -drain_timeout 10 -notification TRUE -commit_outcome TRUE -failover_restore LEVEL1 -failovertype TRANSACTION

[oracle@racnode1 ~]$ srvctl start service -db RACCDB_fra -service acsrv

[oracle@racnode1 ~]$ srvctl status service -db RACCDB_fra -service acsrv
Service acsrv is running on instance(s) RACCDB1,RACCDB2

Application Continuity is enabled by -failover_restore LEVEL1 -failovertype TRANSACTION.

Create Database User

Create a user and a simple table for testing:

[oracle@racnode1 ~]$ sqlplus / as sysdba

SQL> alter session set container=moviedb;
Session altered.

SQL> create user acusr identified by VerySecretPW__2023 quota unlimited on users;
User created.

SQL> grant connect to acusr;
Grant succeeded.

SQL> create table acusr.actab (text varchar2(128));
Table created.

Enable Fast Application Notification (FAN)

The FAN port, usually 6200, must be opened on the database side:

On the client side, FAN is auto-configured out of the box starting with Grid Infrastructure 12c, Oracle Database 12c, and Oracle client 12c when using the recommended connection string. See below.

Client Side

Install JDK

If you are always using Java, you should have the JDK already in place. If you are new to Java:

For Windows, follow the installation as described here.

For Linux, you can follow the installation guide or use yum:

sudo yum install java-11-openjdk

Install Eclipse IDE for Java

If it’s not your favorite (yet) and you want to try it out, you can download it here.

For Windows, follow the installation instructions.

For Linux, simply unzip and execute the binary:

tar -zxvf eclipse-java-2022-12-R-linux-gtk-x86_64.tar.gz
chmod 744 eclipse
./eclipse

Download JDBC and UCP .jar Libraries

Download the zipped JDBC driver (ojdbc11.jar) and Companion Jars file ojdbc11-full.tar.gz from the JDBC and UC P download page.

Extract the zip file and add the following JAR files to your application’s CLASSPATH:

ons.jar, ojdbc11.jar, and ucp11.jar

In Eclipse, right-click on your project name, then Build Path, and Configure Build Path…:

Select Libraries, Classpath, click on Add External Jars…, select the .jar files mentioned above from the directory where you extract the zip file, and click Apply and Close:

Java Example

Create a new Java Class (AC.java) with the following example code:

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Statement;
import java.util.Properties;

//PoolDataSource
import oracle.jdbc.OracleConnection;
import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

public class AC {

	public static void main(String[] args) {
		usePoolDataSource();
	}
	
	public static void usePoolDataSource() {
    	try {   		
    		//use Universal Connection Pool (UCP)
    		PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
    		pds.setConnectionFactoryClassName("oracle.jdbc.datasource.impl.OracleDataSource");
    		pds.setConnectionPoolName("JDBC_UCP");
    		//pds.setInitialPoolSize(10);
    		pds.setMinPoolSize(4);
    		pds.setMaxPoolSize(20);
    		
    		//load properties
    		String PROP_FILE = "C:\\Users\\SPETRUS\\Documents\\spetrus\\development\\java\\ACproject\\src\\config.properties";
    		InputStream propInput = new FileInputStream(PROP_FILE);
    		Properties prop = new Properties();
    		prop.load(propInput);
	        
	        //set properties 
	        System.setProperty("oracle.net.tns_admin", prop.getProperty("tns_admin"));
	        pds.setURL("jdbc:oracle:thin:@"+prop.getProperty("tns_alias"));
	        pds.setUser(prop.getProperty("db_user"));
	        pds.setPassword(prop.getProperty("db_password"));
	        
	        //enable FAN and connection tests
    		pds.setConnectionWaitTimeout(3);
    		pds.setFastConnectionFailoverEnabled(true);
    		pds.setValidateConnectionOnBorrow(true);
    		
    		//disable auto-commit
	        pds.setConnectionProperty(OracleConnection.CONNECTION_PROPERTY_AUTOCOMMIT, "false");
	        pds.setConnectionProperty(OracleConnection.CONNECTION_PROPERTY_IMPLICIT_STATEMENT_CACHE_SIZE, "100");
	        
	        //get connection from the pool
	        Connection conn = pds.getConnection();
	        //conn.beginRequest(); //not needed, as connection pool sets explicit begin request boundary at getConnection()
		    conn.setAutoCommit(false);
		    
		    //execute query
		    Statement stmt = conn.createStatement();			    			    
		    String insert = "insert into acusr.actab values ('Using Application Continuity')";
		    int result = stmt.executeUpdate(insert);
		    System.out.println("insert result is: " + result);	
		    //stop here in bedug mode & (relocate | switchover | terminate the session)
		    conn.commit();
		    //conn.endRequest(); //not needed, as connection pool sets explicit end request boundary at close()
	        conn.close();
	        conn=null;	        
    	}
	    catch (Exception e) {
			e.printStackTrace();
		}  
    }
}

The config.properties file contains the following values in my case:

tns_admin=C:\\Users\\SPETRUS\\Documents\\spetrus\\development
tns_alias=acsrv
db_user=acusr
db_password=VerySecretPW__2023

For testing, you can also put the values directly in the code instead of using a properties file.

Connection String

Use the following recommended connection String in the tnsnames.ora file in the TNS_ADMIN directory:

ACSRV = (DESCRIPTION =
(CONNECT_TIMEOUT=90)(RETRY_COUNT=50)(RETRY_DELAY=3)(TRANSPORT_CONNECT_TIMEOUT=3)
	(ADDRESS_LIST =
		(LOAD_BALANCE=ON)
		(ADDRESS = (PROTOCOL = TCP)(HOST=racnode-scan.subnetpublic.vcnfra.oraclevcn.com)(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME = acsrv.subnetpublic.vcnfra.oraclevcn.com)))

If the SCAN is not resolvable from your client and you are using public host IPs for testing, add another ADDRESS to the ADDRESS_LIST:

ACSRV = (DESCRIPTION = 
(CONNECT_TIMEOUT=90)(RETRY_COUNT=50)(RETRY_DELAY=3)(TRANSPORT_CONNECT_TIMEOUT=3)
	(ADDRESS_LIST = 
		(LOAD_BALANCE=ON)
		(ADDRESS = (PROTOCOL = TCP)(HOST = 130.61.216.103)(PORT = 1521))
		(ADDRESS = (PROTOCOL = TCP)(HOST = 130.61.217.251)(PORT = 1521)))
(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME = acsrv.subnetpublic.vcnfra.oraclevcn.com)))

Time to Test

Set two break points before the commit executes and before the end of the request:

Execute in Debug mode:

The program will stop before the commit executes:

At this point, log in to the database and query the existing session for the user ACUSR:

set lines 300
col username for a10
col service_name for a20
SQL> select username, sid, serial#, inst_id, service_name from gv$session where username = 'ACUSR';

The user is connected to instance #1:

Now stop the service on instance #1:

[oracle@racnode1 ~]$ srvctl stop service -db  RACCDB_fra -service acsrv -instance RACCDB1 -drain_timeout 10
[oracle@racnode1 ~]$ srvctl status service -db  RACCDB_fra -service acsrv

Wait until the drain timeout specified by the service passes, in our case 10 seconds, and query the gv$session again:

Back to Eclipse, resume the execution:

The execution continues and stops at the next break point.

Most importantly, the application continued without getting any error messages, even though the service was stopped meanwhile:

Back to the database querying gv$session, you will see the user is now connected to instance #2:

The commit was successfully completed without any interruption for the application:

Conclusion

Oracle Application Continuity hides database interruptions from end-users by replaying in-flight transactions on available database instances, completely transparent to end-users and without any application code changes needed.

The Java code provided is an example to get you started using Oracle Application Continuity with Java. For production implementations, please follow the instructions and recommendations provided in Oracle documentation.

A Java Example for Oracle Application Continuity with Oracle RAC

Introduction

The Environment

Database Side

Create Database Service

Create Database User

Enable Fast Application Notification (FAN)

Client Side

Install JDK

Install Eclipse IDE for Java

Download JDBC and UCP .jar Libraries

Java Example

Connection String

Time to Test

Conclusion

Further Reading

Subscribe

Introduction

The Environment

Database Side

Create Database Service

Create Database User

Enable Fast Application Notification (FAN)

Client Side

Install JDK

Install Eclipse IDE for Java

Download JDBC and UCP .jar Libraries

Java Example

Connection String

Time to Test

Conclusion

Further Reading

Subscribe

Share this:

Related

Post navigation