UCP with Tomcat 7 and Oracle 11g - MT AG

Ashley Lewis | Download | HTML Embed
  • May 22, 2012
  • Views: 23
  • Page(s): 19
  • Size: 1.07 MB
  • Report

Share

Transcript

1 UCP WITH TOMCAT 7 AND ORACLE 11G BY VOLKER KOSTER, MT AG This paper describes the process of setting up a pooled data source, implemented by Oracles latest JDBC architecture for pooled connections, the Universal Connection Pool UCP, on Apache Tomcat 7 against on Oracle 11g XE database.

2 1 CONTENT 2 Abstract ............................................................................................................................... 2 3 Motivation........................................................................................................................... 2 4 Introduction ........................................................................................................................ 2 5 Software Versions ............................................................................................................... 3 6 Standard Tomcat Connection pooling ................................................................................ 3 7 Connection pooling with Oracle ......................................................................................... 3 7.1 Previous Architectures................................................................................................. 3 7.2 Universal Connection Pool .......................................................................................... 3 7.3 Oracles Resident Connection Pool ............................................................................. 4 8 Setting up UCP .................................................................................................................... 5 8.1 Prequisites ................................................................................................................... 5 8.2 Datasource Configuration............................................................................................ 5 8.3 Placing of Context.xml ................................................................................................. 7 8.4 Tomcat Resource Factories ......................................................................................... 7 8.4.1 Using a Standard Resource Factory...................................................................... 7 8.4.2 Writing a Custom Resource Factory ................................................................... 10 9 Writing a Test Servlet ........................................................................................................ 11 9.1 What the Servlet does ............................................................................................... 11 9.2 Referencing the data source in web.xml ................................................................... 12 9.3 Borrowing a Connection ............................................................................................ 13 9.4 Verifying the results................................................................................................... 14 10 Conclusion ......................................................................................................................... 17 11 Acknowledgments............................................................................................................. 18 12 Readings ............................................................................................................................ 18 13 About the Autor ................................................................................................................ 18 USP with Tomcat 7 and Oracle 11g by Volker Koster, MT AG Seite 1

3 2 ABSTRACT This paper describes the process of setting up a pooled data source, implemented by Oracles latest JDBC architecture for pooled connections, the Universal Connection Pool UCP, on Apache Tomcat 7 against on Oracle 11g XE database. 3 MOTIVATION Why use Oracle UCP? This is a valid question, because you will probably have no advantage from using UCP right away. UCP offers a cache of pooled connections, so what? When working with Tomcat, you automatically get connection pooling implemented by Apaches DBCP project out of the box. DBCP is the common denominator for a whole bunch of databases. This is its strength but also its weakness. UCP is Oracle specific. It works with Oracle specific features concerning high availability in clustered database environments, the Oracle Real Application Cluster or RAC. By way of configuration, i.e. without having to write further code, it enables your java database client application to make use of features like Fast Connection Failover, Runtime Connection Load- Balancing and Connection Affinity. If you are facing this kind of environment, UCP is for you. A second reason to deal with UCP may be that you have been using ICC, Oracles Implicit Connection Caching technology, in the past. ICC is deprecated in 11g so you are really well advised to switch over to UCP. It gives you all ICC could do and a lot more. 4 INTRODUCTION What you are about to read here is a compilation of procedures and findings concerning the topic of UCP on Tomcat 7 resulting from a recent BPM software assessment we conducted here at MT. While the BPM software itself was not even certified yet for Tomcat 7 and Oracle 11g, we still figured that this will be our preferred productive setup in a not to distant future. So why not just go for it? We had a minor issue with UCP working in our specific environment: The connection pool was not instantiated with configured number of upfront connections. Our work around consisted of writing our own custom resource factory for Tomcat. This work around will be described in detail in this paper. We are going to discuss some general data pooling topics before going into the details of setting up a UCP data source and testing it with a most simple test servlet. We aim for the test servlet to connect with Oracle, borrow a first connection and hope for UCP to fill up the data source with additional connections according to our data source specifications (and we really want to see those connections in SQL Developer and jConsole ). USP with Tomcat 7 and Oracle 11g by Volker Koster, MT AG Seite 2

4 5 SOFTWARE VERSIONS Here are the software versions being: Tomcat 7.0.16 Oracle 11g Express Edition Release 11.2.0.2.0 Production JDK 1.6.0_11 JDBC ojdbc6 release 11.2.0.1.0, UCP for Oracle 11.2.0.x OS Windows XP Professional SP3 IDE Eclipse Helios JEE Bundle 6 STANDARD TOMCAT CONNECTION POOLING If you dont do anything special within Tomcat while defining your data source, which means youre using Tomcats standard resource factory for data sources, youll still end up with a pooled data source. Tomcat ships with database connection pooling implemented by the Apache Commons DBCP project. Im not going to match UCP against DBCP here. Right now, I really dont care which performs better under what circumstances and the like. Its just that I know Im going to work against an Oracle 11g database, using an Oracle JDBC implementation and for an Oracle aficionado like me, it is therefore practically imperative to also use the newest Oracle pooling technology available. 7 CONNECTION POOLING WITH ORACLE 7.1 PREVIOUS ARCHITECTURES The Implicit Connection Cache was Oracles connection pooling architecture up to Oracle 11g Release 1. Only with Release 2 ICC has been replaced by UCP so that ICC is now deprecated Oracle 11g R2 databases. Switching from ICC to UCP is painless from a functional point of view. All that could be done with ICC can now be done with UCP. 7.2 UNIVERSAL CONNECTION POOL The Universal Connection Pool is now the recommended architecture for Java 1.6 clients connecting to Oracle 11g databases. UCP is designed to work with single Oracle instance as well as with a whole Oracle Real Application Cluster. UCP is the most recent technology for managing pooled connections and is what this paper focuses on. USP with Tomcat 7 and Oracle 11g by Volker Koster, MT AG Seite 3

5 7.3 ORACLES RESIDENT CONNECTION POOL One thing Id like to mention here is that you can benefit from Oracles connection pooling even if your application cannot for some reason use one of Oracles pool-supporting database drivers (OCI, OCCI, JDBC, ODP.NET etc.). Oracle supplies the so called Database Resident Connection Pool DRCP, a connection pool managed directly within the database itself. This connection pool has to be set up by your database administrators via some PLSQL API. You can configure the pool like this (which is optional and only needed, if you have to change the default settings): SQL>execute dbms_connection_pool.configure_pool(null, minsize=>10, maxsize=>100, inactivity_timeout=>300, max_think_time=>600, ); This pool has to be started, before clients can borrow and return connections: SQL> execute dbms_connection_pool.start_pool; From the application side, usage of this pool is specified within your SqlNet TNS-Names connect string. Hers is an example: . . . (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=myhost.dom.com) (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sales) (SERVER=POOLED))) . . . Oracle 11g supplies a special view to get information from this database resident pool: dba_cpool_info Selecting from this view will give you the following info (and more): select * from dba_cpool_info Figure 1: Selecting from DBA_CPOOL_INFO You can see that in my database this pool is inactive (you have to start it like described above). The Database Resident Connection Pool is a feature available since Oracle 11g. USP with Tomcat 7 and Oracle 11g by Volker Koster, MT AG Seite 4

6 8 SETTING UP UCP When we were trying to configure UCP data sources on Tomcat 7, we naturally Googled the topic and of course found a couple of examples demonstrating different configurations. We had a standard procedure of evaluating a configuration: Define the data source in Tomcat Add the configuration to our test servlet Connect to the database Monitor the pool and check for the connections created The common denominator with all tested configuration was that everything worked fine, except for the initial creation of the connection pool. The pool was simply not initialized with configured number of connections on first connect. Our final solution was to write our own custom resource factory for Tomcat, which was quite easy to do and gave as absolutely reliable results. We will describe this process here too. 8.1 PREQUISITES Oracle delivers UCP in a separate file, so you have to download two files: Odbc6.jar Ucp.jar Remember, you have to work with Java 1.6 or above to use this JDBC version. Copy both files into Tomcats lib directory: \lib. 8.2 DATASOURCE CONFIGURATION With Tomcat, data source configurations can be specified either globally, i.e. accessible for all deployed applications, or on a per application basis. Global resources are specified in \conf\server.xml whereas application specific resources are defined in the applications context.xml file. Either way the resource is defined in a element. In this paper we go for an application specific data source, so the configuration goes into a separate Context.xml file. USP with Tomcat 7 and Oracle 11g by Volker Koster, MT AG Seite 5

7 The factory property needs further explanation which will follow soon. Everything else is quite straight forward: name: the jndi name of the data source once your application has successfully acquired an Initial Context, you will use this name to look up the data source (Tomcat attribute) auth: Container or Application let the Container sign on to the resource manager (Tomcat attribute) type: fully qualified java class name represents the object that will be returned by the resource factory, i.e. the JNDI look- up (Tomcat attribute) ConnectionFactoryClassName property and value taken from UCP manual minPoolSize: pool size wont drop below this value property taken from UCP manual maxPoolSize: pool size wont grow larger than this property taken from UCP manual user: Oracle user name property taken from UCP manual password: Oracle password for the user above property taken from UCP manual url: Oracle connect string property taken from UCP manual connectionPoolName: the name for this pool property taken from UCP manual The attributes name, auth and type are properties from Tomcats element. The rest are UCP properties taken from the UCP manual, which describes how to create and configure a UCP data source. Please note that these are just the basic ones. There a whole bunch left for you to integrate into your configuration to make these data sources do what you require. The UCP manual is listed in the Further Readings section. USP with Tomcat 7 and Oracle 11g by Volker Koster, MT AG Seite 6

8 8.3 PLACING OF CONTEXT.XML If you go for an application specific context.xml file, you again have two options: you can place it into your applications META-INF directory under the name of context.xml or you can copy it into $CATALINA_BASE/conf/[enginename]/[hostname]/[webappname].xml Take a note to rename context.xml into [webappname].xml if you go for the second option. Also be careful to realize that this context descriptor task precedence over anything you specify in an application specific context descriptor subsequently. Personally I like my context descriptors application specific. I will copy the context.xml file into the META-INF directory of my test servlet later on. 8.4 TOMCAT RESOURCE FACTORIES 8.4.1 USING A STANDARD RESOURCE FACTORY My favorite resource concerning Oracle UCP data pools with Apache Tomcat is Michael Pilones article on the subject listed here: http://drdobbs.com/java/222700353. Modifying his data source configuration to suite our environment looks like this:

9 Using the above configuration, we get this result when connecting to Oracle: Figure 2: Connection via Standard Connection Factory Problem here is, the pool is not created with the correct number of connection as specified in the initialPoolSize parameter. USP with Tomcat 7 and Oracle 11g by Volker Koster, MT AG Seite 8

10 A look into jConsole seems to support this: Figure 3: jConsole inspecting initialPoolSize Parameter Much to ones surprise, all other configurations were accepted, as the next figure shows: Figure 4: Inspected Pool Properties This is a minor problem which you will probably never notice. But if it bothers you, one work-around is to program your own custom resource factory. We will do this in the next section. USP with Tomcat 7 and Oracle 11g by Volker Koster, MT AG Seite 9

11 8.4.2 WRITING A CUSTOM RESOURCE FACTORY Writing a Custom Resource Factory for Tomcat is described within the Tomcat documentation: http://tomcat.apache.org/tomcat-7.0-doc/jndi-resources-howto.html. At the end of this document, youll find the chapter Adding Custom Resource Factories. Using this as a guide, well do the following: Create a Java project in Eclipse Reference all the jars from \lib Create a new class that implements javax.naming.spi.ObjectFactory package com.mtag.oraclefactory; import java.util.Enumeration; import java.util.Hashtable; import java.util.logging.Logger; import javax.naming.Context; import javax.naming.Name; import javax.naming.RefAddr; import javax.naming.Reference; import javax.naming.spi.ObjectFactory; import oracle.ucp.jdbc.PoolDataSource; import oracle.ucp.jdbc.PoolDataSourceFactory; /** * Properties, die diese UCP-Datasource versteht * o connectionFactoryClassname * o url * o user * o password * o connectionPoolName * o minPoolSize * o maxPoolSize * o initialPoolSize * * @author vkoster * */ public class MtOracleUcpFactory implements ObjectFactory { private static Logger log = Logger.getLogger("com.mtag.oraclefactory.MtOracleUcpFactory"); private PoolDataSource pds; @Override public Object getObjectInstance(Object obj, Name name, Context nameCtx, Hashtable environment) throws Exception { log.info("MtOracleFactory.getObjectInstance aufgerufen"); pds = PoolDataSourceFactory.getPoolDataSource(); log.info("UCP PooldDataSource besorgt"); USP with Tomcat 7 and Oracle 11g by Volker Koster, MT AG Seite 10

12 Reference ref = (Reference)obj; Enumeration addrs = ref.getAll(); while (addrs.hasMoreElements()){ RefAddr addr = (RefAddr)addrs.nextElement(); String propName = addr.getType(); String propValue = (String)addr.getContent(); if (propName.toLowerCase().equals("connectionfactoryclassname")) { pds.setConnectionFactoryClassName(propValue); } else if (propName.toLowerCase().equals("url")) { pds.setURL(propValue); } else if (propName.toLowerCase().equals("user")) { pds.setUser(propValue); } else if (propName.toLowerCase().equals("password")) { pds.setPassword(propValue); } else if (propName.toLowerCase().equals("connectionpoolname")) { pds.setConnectionPoolName(propValue); } else if (propName.toLowerCase().equals("minpoolsize")) { pds.setMinPoolSize(Integer.parseInt(propValue)); } else if (propName.toLowerCase().equals("maxpoolsize")) { pds.setMaxPoolSize(Integer.parseInt(propValue)); } else if (propName.toLowerCase().equals("initialpoolsize")) { pds.setInitialPoolSize(Integer.parseInt(propValue)); } } log.info("UCP PooledDataSource konfiguriert und zurckgeliefert"); return pds; } } There is only one method we have to implement: getObjectInstance. Tomcat calls this method with a lot of parameters, but we are only interested the first one. Formally typed as an Object, Tomcat passes a javax.naming.Reference object. From this Reference we can extract a list of name-value pairs, where each pair represents a data source property that we specified in our Tomcat Conext.xml file. The implementation does nothing more than to match the name-part of a given name-value pair with a list of properties Im interested in and sets the respective property to the value- part of the pair. You can do this in a much more flexible manner using reflection, but for demonstration purposes this hard-coded matching will do. At the end we simply return the now configured data source. Finally, export your custom resource factory to a jar-file and copy this into Tomcats lib directory \lib. 9 WRITING A TEST SERVLET 9.1 WHAT THE SERVLET DOES I wrote a very simple web-app to test different data source configurations. A static web page invites you to click on a specific data source to be established, one of them being our UCP connection pool. USP with Tomcat 7 and Oracle 11g by Volker Koster, MT AG Seite 11

13 On clicking one of them, the servlet is invoked doing the following: Looking up the respective data source borrow a connection select and display some information from Oracles gv$session table close, i.e. return the connection maintain a protocol of what happened and display the results Here are the screenshots: Figure 5: Static HTML offering different data sources This page offers a couple of connections to try out. We are interested in the Custom UCP Source connect. Clicking the link invokes the servlet with the selected data source as a parameter. Within the servlet, we look up the data source, borrow a connection and query some rows from the database. We collect the results along the way and write them to the servlets response. 9.2 REFERENCING THE DATA SOURCE IN WEB.XML Make the use of your data source visible by placing a reference into your applications web.xml file located in the WEB-INF directory. My reference looks like this:

14 http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0"> DataPool index.html Referenz auf die Beschreibung der JNDI-Resource im Context-Element Web-App jdbc/oralocal oracle.jdbc.pool.OracleDataSource As you can see from the element, my test servlet will be called DataPool. The data source is referenced by a element which can be used here instead of a element because authentication is already specified in the context.xml file. Please take a note that this entry is for documentation purposes only, because we specified everything in our context.xml file already (this is described in Tomcats documentation within the JNDI Resources HOW-TO, http://tomcat.apache.org/tomcat-7.0-doc/jndi- resources-howto.html in the context.xml configuration part). 9.3 BORROWING A CONNECTION Borrowing a connection from the pool works just like you would expect with any data source. Heres the code: import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; import javax.naming.Context; import javax.naming.InitialContext; import javax.sql.DataSource; public class Connect { String protocol ="Trying to connect to Oracle"; public String connectToPool(String dsn){ // Obtain our environment naming context try { Context initCtx = new InitialContext(); protocol = protocol + "Initial Context: created"; Context envCtx = (Context) initCtx.lookup("java:comp/env"); protocol = protocol + "Context: java:comp/env"; USP with Tomcat 7 and Oracle 11g by Volker Koster, MT AG Seite 13

15 // // Look up our data source DataSource ds = (DataSource)envCtx.lookup(dsn); protocol = protocol + "Datasource: "+dsn + ""; // // Allocate and use a connection from the pool Connection conn = ds.getConnection(); protocol = protocol + "Connection: borrowed ok"; // query the database Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery("select sid, username, status, program from gv$session where upper(username)='VK'"); protocol = protocol+"select sid, username, status, program from gv$session where username ='VK'"; while (rset.next()) { protocol = protocol+rset.getString(1)+" "+ rset.getString(2)+" "+ rset.getString(3)+" "+ rset.getString(4)+" "+""; } // close everything... rset.close(); protocol = protocol + "ResultSet closed"; stmt.close(); protocol = protocol + "Statement closed"; conn.close(); protocol = protocol + "Connection: closed"; protocol = protocol + "Connection handling finished OK!"; } catch (Exception e) { System.out.println(e); } return protocol; } } Tomcat ensures that the getObjectInstance-method is called only once for your application. After this initial call, the object reference is stored in the JNDI-context of your application for subsequent use. 9.4 VERIFYING THE RESULTS Now we want to make sure that things are running according to our expectations. My context.xml file reveals my connection details. You can see that the data source will be created as user VK and password VK. Im using this account to connect to my SqlDeveloper to Oracle. Next I query all the sessions of this user from gv$session. USP with Tomcat 7 and Oracle 11g by Volker Koster, MT AG Seite 14

16 I expect there to be exactly on session as of now (namely my current SqlDeveloper Session): Figure 6: Preliminary Actions Next I point my browser to the location of my test WebApp: http://localhost:8080/DataPool and click for the UCP data source. This is the result: Figure 7: Borrowing a Connection USP with Tomcat 7 and Oracle 11g by Volker Koster, MT AG Seite 15

17 The servlet reflects the steps of borrowing a connection. This connection is then used to retrieve all session of user VK from the database. You can see that we now have 5 JDBC sessions for user VK. This corresponds to the initial pool size we specified in our context.xml file. Notice that all are marked as INACTIVE except the one that is actually running this query. After running the query, the servlet closed the connection, i.e gave it back to pool. We will now run the same query from SqlDeveloper expecting the connection pool to be still running. Figure 8: State of Connection Pool after Closing the Connection The Connection Pool remains intact but now, all JDBC Connections are INACTIVE. The SqlDeveloper session is the only one ACTIV, running this query. On the console running Tomcat, we can see that our custom resource factory was used to create the UCP data pool: Figure 9: Tomcat Console We put some logging into our custom resource factory so you can now trace back the way our code was called by Tomcat. USP with Tomcat 7 and Oracle 11g by Volker Koster, MT AG Seite 16

18 Lets finally take a look at jConsole to see the UCP classes in action: Figure 10: Oracles UCP MBean A drill down into Oracles UCP MBean confirms that the pool has been established according to our specifications. 10 CONCLUSION In this paper we demonstrated a way to connect a test servlet running on Tomcat 7 to an Oracle 11g Database using Oracles Universal Connection Pool Architecture. We did this to enable further clients to profit from running in clustered Oracle environments. For some reason not yet clear, we could not take the easy way and accomplish this just by means of configuration. Instead we had to use a workaround, implementing our own custom resource factory to get the job done. Using the work around, UCP worked fine and we could make sure that got what we aimed for. We would very much appreciate any feedback as to why this workaround was needed by the way. USP with Tomcat 7 and Oracle 11g by Volker Koster, MT AG Seite 17

19 11 ACKNOWLEDGMENTS Thanks to my colleges at MT for reviewing my scribbling and making this readable. Thanks to Peter Rossbach for shedding some light on custom resource factories. 12 READINGS Apache Tomcat 7 documentation: http://tomcat.apache.org/tomcat-7.0- doc/index.html Oracle Database JDBC Developer's Guide and Reference, 11g Release 1 (11.1) Oracle Universal Connection Pool for JDBC Developers Guide Oracle Database JDBC Developers Guide and Reference http://martincarstenbach.wordpress.com/2011/02/03/getting-up-and-running-with- universal-connection-pool/ JDBC Fast Connection Failover with Oracle RAC Configuring and testing with Tomcat and the Spring framework By Michael Pilone February 08, 2010 URL:http://drdobbs.com/java/222700353 13 ABOUT THE AUTOR Volker Koster is an Executive Architect at MT AG, Ratingen, Germany. Hes been on software projects since 1990, mostly in Oracle and Java environments. You can contact him by eMail at [email protected] Your comments are very welcome. Copyright 2012, MT AG Ratingen Balcke-Drr-Allee 9 40882 Ratingen Tel. +49 (0) 21 02 309 61-0 Fax +49 (0) 21 02 309 61-10 [email protected] www.mt-ag.com USP with Tomcat 7 and Oracle 11g by Volker Koster, MT AG Seite 18

Load More