We already worked on configuring a multi pool in JBoss EWP [AS].In This article, we will see on how to configure multi pool in JBoss EWS [Tomcat].
As we are aware of how to configure a Datasource in Tomcat using the Context.xml file in /conf location. We use the same file to create a Datasource which allows the fail over capabilities.
The Resource element looks like this,
<Resource name="jdbc/StudentDB" auth="Container"
description="Multi Pool Configuration"
driverClassName="oracle.jdbc.OracleDriver"
factory="oracle.jdbc.pool.OracleDataSourceFactory"
user="username" password="password"
type="oracle.jdbc.pool.OracleDataSource"
url="jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = DO0601.oracle.nova.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = DO0602.oracle.nova.com)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DO06.NOVA.COM)(FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5))))" />
Just deploy a sample application which will make a lookup for the Datasource and access the database.try to modify the one of the Host and access the application. The application still works fine providing the database failover.
There may be some stale connections when Rac node gets down and Jdbc pool which was created by Tomcat may not get any notifications about the node.For this we can use Oracle Fast connection Failover(FCF) mechanism.
FCF leverages Oracle Notification Service (ONS) to get information about database events. With the help of ONS, a JDBC pool which has FCF enabled can see if a RAC node is up or down and can act accordingly.
Here is how we configure the Multi pool in Ews with ONS.Add the following resource element to the context.xml file as below,
<Resource name="jdbc/StudentDB" auth="Container"
connectionCachingEnabled="true"
description="Multi Pool Configuration"
driverClassName="oracle.jdbc.OracleDriver"
factory="oracle.jdbc.pool.OracleDataSourceFactory"
fastConnectionFailoverEnabled="true"
implicitCachingEnabled="true"
connectionCacheName="tomcatConnectionCache"
user="username" password="password"
type="oracle.jdbc.pool.OracleDataSource"
url="jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = DO0601.oracle.nova.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = DO0602.oracle.nova.com)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DO06.NOVA.COM)
(FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5))))" />
Let’s go one by one,
ConnectionCacheEnabled : The connection cache enabled should be set to true , else the Datasource would server physical connections to the applications.
Factory: Factory is normally a fully qualified name of the java class that creates these Datasource objects.
fastConnectionFailoverEnabled:This enables to client to get notified immediately of a planned or a unplanned outage.
implicitCachingEnabled:This should be set to true which will be used by the fastConnectionFailover.
connectionCacheName:It is highly recommended to set connectionCache Name for Each DS which is a unique value. When connection caching is turned on, each DataSource has exactly one cache associated with it. All connections obtained through that data source, no matter what username and password are used, are returned to the cache. When an application requests a connection from the data source, the cache either returns an existing connection or creates a new connection with matching authentication information.
Once the context.xml file is updated, write a sample jsp page which gets the connection using this Datasource. If we need to test the fail over capabilities, then modify the one of the URL and test the connection. You can see an error on command console and connection will be obtained from second URL.
Here is the Sample Code for accessing the Data Source ,
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
DataSource bean = (DataSource) envCtx.lookup("jdbc/StudentDB");
Connection con=(Connection)bean.getConnection();
PrintWriter pw=response.getWriter();
String sql = "Select * from sampleTable";
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
rs.next();
pw.println(rs.getInt(1));
pw.println(rs.getString(2));
con.close();
Note: You need to have ons.jar in your classpath.
Happy Coding. More Articles to Come…
