Martins Blog

Trying to explain complex things in simple terms

Getting up and running with UCP and Application Continuity

Posted by Martin Bach on December 20, 2013

I have already posted a couple of articles on the use of Oracle’s Universal Connection Pool in the past with regards to Workload Management and Oracle RAC 11.2. Since then a lot happened, with the release of Oracle 12c being the most notable event. With 12c you get lots of interesting new features for JDBC, and the one I would like to present today is Application Continuity. This continues the previous post on playing with Application Continuity outside of a midlle-tier environment. Well, if you allow me to call Tomcat 7 “middle-tier” that is.

The aim of this post is to extend my previous posts about setting up UCP with Application Continuity. The basic setup remains unchanged, but this time I tested with JDK 1.6 (build 1.6.0_45-b06) and Tomcat 7.0.47 on Oracle Enterprise Linux 6.4 64bit.

For those of you not remembering straight away what equipment I  was using for the last post, here is a quick summary. I am using the same 2 node RAC cluster which is home to the CDB RAC12C. Both instances, RAC12C1 and RAC12C2 are open. A little PDB, named DEMOPDB (open on both instances too) is used for this demo, mainly because I was curious how Application Continuity works with PDBs. In summary: it does :)

I am not going to repeat all the details about setting up Tomcat for use with UCP, please refer to part 1 and part 2 for the basics. The following example also uses the sample web application.

Necessary Changes for Application Continuity

The first change necessary was to make sure the connection factory returned the required class for the connection. This required a change to the URL property. In addition I had to use the way-more-verbose-connection descriptor as I found in my previous post (independently confirmed by Martin Nash too). Here is the new

[root@oracledev ucp]# cat web/META-INF/context.xml
<Context>
 <Resource
 name="jdbc/UCPPool"
 auth="Container"
 factory="oracle.ucp.jdbc.PoolDataSourceImpl"
 type="oracle.ucp.jdbc.PoolDataSource"
 description="UCP JNDI Connection Pool for Application Continuity"
 connectionFactoryClassName="oracle.jdbc.replay.OracleDataSourceImpl"
 onsConfiguration="nodes=rac12node1:6200,rac12node2:6200"
 initialPoolSize="10"
 minPoolSize="10"
 maxPoolSize="50"
 inactiveConnectionTimeout="20"
 user="martin"
 password="secret"
 url="jdbc:oracle:thin:@(DESCRIPTION = (TRANSPORT_CONNECT_TIMEOUT=3) (RETRY_COUNT=20)(FAILOVER=ON) 
(ADDRESS = (PROTOCOL = TCP)(HOST = rac12scan.gns.example.com)(PORT = 1521)) 
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ACTEST)))"
 connectionPoolName="UCPPool"
 validateConnectionOnBorrow="true"
 fastConnectionFailoverEnabled="true"
 sqlForValidateConnection="select 1 from DUAL" />
</Context>

Note that I added the initialPoolSize to match the minPoolSize and changed the connectionFactoryClassName plus the aforementioned change to the URL (this has been line wrapped for readability) which was actually all there was to do to change the application.

When reviewing the servlet code I quickly realised that the stored procedure somehow had to be called asynchronously so I spent about 2 hours trying to figure out how to do this in Java with a Java Server Page. I eventually came to the conclusion that it would have to be AJAX so I spent another 30 minutes hacking something together for which I apologise to all web developers. On the upside, it does work and what I needed it to do. As a reminder, here is the code in the stored procedure:

PROCEDURE	   "CANICARRYON" (pi_message varchar2)
as
begin
 insert into acdemotab (message) values (pi_message);
 dbms_lock.sleep(20);
 commit;
end;

I have lowered the sleep to 20 seconds so as not having to wait too long. The service definition has not changed from the first test with Application Continuity, see here for the srvctl syntax. So with all that said, here is what changed. I am using a new JSP “index.jsp” for a welcome page. It in turn uses class UCPHelper to create the connection pool and eventually to execute the stored procedure. Should I find the time I’ll extend it to print pool statistics, for now it is just the basic JNDI lookup. The code is shown here:

package mypackage;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.ValidConnection;
import java.sql.ResultSet;
import javax.naming.*;

public class UCPHelper {
	PoolDataSource m_pds;

	public Connection getConnection(String jndiName) 
		throws Exception
	{
		Context ctx = new InitialContext();
		Context envContext = (Context) ctx.lookup("java:/comp/env");
		
		m_pds = (PoolDataSource) envContext.lookup (jndiName);

		return m_pds.getConnection();
	}

	public boolean isReplayConnection(Connection conn) {
		if (conn instanceof oracle.jdbc.replay.ReplayableConnection) {
			return true;
		} else {
			return false;
		}
	}

	public void executePLSQL(String message, Connection conn) 
		throws Exception
	{
		java.sql.CallableStatement cstmt = conn.prepareCall("{call canIcarryOn(?)}");
		cstmt.setString(1, message);
		cstmt.execute();
	}

}

The index.jsp contents are shown here:

<!DOCTYPE html>
<%@ page contentType="text/html;charset=UTF-8"%>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
        <title>index</title>
        <script>
          function executeCMD(str) {
              var xreq;

              if (str == "") {
                  document.getElementById("showtext").innerHTML = "<p>Enter a value!</p>";
                  return;
              }
              if (window.XMLHttpRequest) {
                  xreq = new XMLHttpRequest();
              }
              else {
                  xreq = new ActiveXObject("Microsoft.XMLHTTP");
              }
              xreq.onreadystatechange = function () {
                  if (xreq.readyState == 4) {
                      if (xreq.status == 200) {
                         document.getElementById("showtext").innerHTML = xreq.responseText;
                      } else {
                         document.getElementById("showtext").innerHTML = "<p>something went wrong with the request</p>";
                      }
                  }
              }

              xreq.open("get", "getRequest.jsp?q=" + str, "true");
              xreq.send();
              document.getElementById("showtext").innerHTML = "<p>request has been submitted-waiting for answer</p>";
          }
        </script>
    </head>
    <body>
        <h1>A test for Application Continuity</h1>
         
        <form>
            <table>
                <tr>
                    <td>Enter a message to be sent asynchronously to the database</td>
                    <td>
                        <input type="text" id="messageField"/>
                    </td>
                    <td>
                        <input type="button" value="Submit"
                               onclick="executeCMD(document.getElementById('messageField').value)"/>
                    </td>
                </tr>
            </table>
        </form>
        <p>The processing will be performed asynchronously</p>
        <div id="showtext">request has not yet been submitted</div>
    </body>
</html>

The asynchronous part is in the script tag in the html header: the XMLHttpRequest allows me to send data back to the server and wait for the result. The lower div element is updated according to user input. If there is no input, nothing happens and a message “enter a value” is shown. Once the input is present, it is sent off to the server. The response is then parsed. if the readyState is 4, and the HTTP code 200 everything is fine and I print the output from the called JSP, getRequest.jsp. Sometimes with UCP you get HTTP 500 error codes which I am trapping too.

The JSP dealing with the request processing, getRequest (because I’m using a HTTP get) performs all the work with the database. It’s shown here:

<%@ page import="mypackage.*" %>
<%@ page import="java.sql.Connection " %>
<%@ page import="java.sql.SQLException " %>
<%@ page import="java.sql.Statement " %>
<%@ page import="oracle.ucp.jdbc.PoolDataSourceFactory " %>
<%@ page import="oracle.ucp.jdbc.PoolDataSource " %>
<%@ page import="oracle.ucp.jdbc.ValidConnection " %>
<%@ page import="java.sql.ResultSet " %>

<html>
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
	<title>Application Continuity with RAC 12c Example JSP</title>
</head>
<body bgcolor="white">

	<p>request started</p>

<%
	String message = request.getParameter("q");

	UCPHelper helper = new UCPHelper();
	Connection conn = helper.getConnection("jdbc/UCPPool");

	helper.executePLSQL(message, conn);
%>

	<p>request completed</p>

</body>
</html>

Test

The test was successful although it took a very long time to fail the service over to the “availble” node. Thanks to the fat connection string the connection pool has been re-established with the correct number of sessions. In one scenario I had 20 sessions (10 more than initial/min pool size) and they were all created on the available instance.

I didn’t yet work out where the delay was with the service failover, that’s for another post…

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 2,249 other followers

%d bloggers like this: