tcServer data source causing TNS Listener problems

[simonwbaker]

I apologise for the scarcity of information here but this is reaching areas well beyond my knowledge so I hope someone might be able to help.

I am frequently (though not always) getting connection errors of the form:

Caused by: javax.naming.NamingException: Io exception: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=168822016)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))

reported in my Tomcat log files on tcServer startup. This seems to indicate an "ORA-12505: TNS:listener does not currently know of SID given in connect descriptor" error.

I have 3 data sources configured in each of 2 tcServer instances. After having these problems with data sources configured via the GUI I reverted to as close as I can get to those configured in other standalone Tomcat 6.0.18 instances which don't exhibit the problem.

I see that ORA-12505 would seem to have a few potential causes. I'm pretty confident that the data source resources are configured correctly since the problems do not always occur with tcServer and appear never to occur with plain Tomcat.

Once the problem has occurred I then get "ORA-12516: TNS:listener could not find available handler with matching protocol stack" when attempting to connect to the database at all (such as via SQL-Plus).

An example of the <Resource> element defining the data source (I've put this in context.xml to mirror the situation with my plain Tomcat instances) is:

<Resource name="jdbc/job"
          auth="Container"
          type="javax.sql.DataSource"
          driverClassName="oracle.jdbc.driver.OracleDriver"
          url="jdbc:oracle:thin:@martin:1521:casdev"
          removeAbandoned="true"
          removeAbandonedTimeout="60"
          logAbandoned="true"
          maxActive="75"
          maxIdle="30"
          maxWait="-1"
          username="(schema name)"
          password="(schema password)"
          validationQuery="select 1 from dual" />

The other 2 sources are very similar. Note that the above is simply from my investigations with Tomcat (which I was unfamiliar with until recently) and not in any way optimised for a production environment.

I see the same thing if I have my data sources configured in server.xml and referenced by <ResourceLink> elements in context.xml as is the case when configuring the sources via the AMS (the context.xml entries are manually entered in this case as per a previous thread).

These problems may potentially be within our database but I'm no DBA and things seem to be fine using the same data source configurations in the plain Tomcat instances so I wonder whether those I have in tcServer are not cleaning up neatly after themselves.

I know there are a lot of unknowns here but would appreciate if anyone has pointers for me to consider next or to know whether anyone else has experienced similar difficulties.

Cheers,
Simon

[fhanik]

Sometimes this error happens simply cause you have the wrong JDBC driver (jar file) in Tomcat.
http://www.dbforums.com/java/971271-...n-refused.html

Now, if you don't want your password in server.xml, you can do this

open catalina.properties, insert

job.username=myusername
job.password=mypassword

and in server.xml

<Resource name="jdbc/job"
          auth="Container"
          type="javax.sql.DataSource"
          driverClassName="oracle.jdbc.driver.OracleDriver"
          url="jdbc:oracle:thin:@martin:1521:casdev"
          removeAbandoned="true"
          removeAbandonedTimeout="60"
          logAbandoned="true"
          maxActive="75"
          maxIdle="30"
          maxWait="-1"
          username="${job.username}"
          password="${job.password}"
          validationQuery="select 1 from dual" />

[simonwbaker]

I am using a driver (oracle.jdbc.driver.OracleDriver) from a "classes12.jar" file I've added to the Tomcat lib directory. This is the same as with my plain Tomcat instances that work OK.

The difference that now springs to mind is that the tcServer instances are running under a 1.6 version of the JDK (as suggested in the "Getting Started with tc Server" document) whilst the plain Tomcat instances are running under 1.5. I wonder if there is some compatibility issue.

p.s. Thanks for the password tip. I will look into this kind of thing if we get Tomcat anywhere near production.

[fhanik]

I am using a driver (oracle.jdbc.driver.OracleDriver) from a "classes12.jar" file I've added to the Tomcat lib directory. This is the same as with my plain Tomcat instances that work OK.

The difference that now springs to mind is that the tcServer instances are running under a 1.6 version of the JDK (as suggested in the "Getting Started with tc Server" document) whilst the plain Tomcat instances are running under 1.5. I wonder if there is some compatibility issue.

You can run it with 1.5, the tomcat under the hood is identical. So it would be very unlikely that it is a tcServer vs Tomcat issue.

Are there other errors in the logs?

Filip

[simonwbaker]

I am actually getting 2 different (though very similar) errors in the logs. That mentioned previously (referring to the ORA-12505) is one of the root cause exceptions showing in the logs. The other shows an ORA-12519 ("TNS:no appropriate service handler found").

More fully the last part of the stack trace (showing the ORA-12519 root cause) is:

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'myDataSource' defined in ServletContext resource [/WEB-INF/my-data.xml]: Invocation of init method failed; nested exception is javax.naming.NamingException: Io exception: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=168822016)(ERR=12519)(ERROR_STACK=(ERROR=(CODE=12519)(EMFI=4))))
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1302)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:463)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory$1.run(AbstractAutowireCapableBeanFactory.java:404)
	at java.security.AccessController.doPrivileged(Native Method)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:375)
	at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:263)
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:170)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:260)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:184)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:163)
	at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:269)
	... 108 more
Caused by: javax.naming.NamingException: Io exception: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=168822016)(ERR=12519)(ERROR_STACK=(ERROR=(CODE=12519)(EMFI=4))))
	at org.apache.naming.NamingContext.lookup(NamingContext.java:805)
	at org.apache.naming.NamingContext.lookup(NamingContext.java:140)
	at org.apache.naming.NamingContext.lookup(NamingContext.java:781)
	at org.apache.naming.NamingContext.lookup(NamingContext.java:140)
	at org.apache.naming.NamingContext.lookup(NamingContext.java:781)
	at org.apache.naming.NamingContext.lookup(NamingContext.java:140)
	at org.apache.naming.NamingContext.lookup(NamingContext.java:781)
	at org.apache.naming.NamingContext.lookup(NamingContext.java:153)
	at org.apache.naming.SelectorContext.lookup(SelectorContext.java:137)
	at javax.naming.InitialContext.lookup(Unknown Source)
	at org.springframework.jndi.JndiTemplate$1.doInContext(JndiTemplate.java:132)
	at org.springframework.jndi.JndiTemplate.execute(JndiTemplate.java:88)
	at org.springframework.jndi.JndiTemplate.lookup(JndiTemplate.java:130)
	at org.springframework.jndi.JndiTemplate.lookup(JndiTemplate.java:155)
	at org.springframework.jndi.JndiLocatorSupport.lookup(JndiLocatorSupport.java:93)
	at org.springframework.jndi.JndiObjectLocator.lookup(JndiObjectLocator.java:105)
	at org.springframework.jndi.JndiObjectFactoryBean.lookupWithFallback(JndiObjectFactoryBean.java:192)
	at org.springframework.jndi.JndiObjectFactoryBean.afterPropertiesSet(JndiObjectFactoryBean.java:179)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1333)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1299)
	... 118 more

The beginning of the stack trace is application-specific leading to the attempt to create the "myDataSource" bean.

I have tried the plain Tomcat instances using the same JDK (1.6) as I'm using for the tcServer instances but they remain fine.

I agree that is seems unlikely to be a tcServer vs Tomcat issue. However, given that the error does not occur using the same JDK, data source configuration and application on Tomcat I don't currently have anything that looks any more likely (note that the instances are not quite identical - my plain Tomcat is version 6.0.18 and that in tcServer is labelled 6.0.19.A).

I suspect that I may have some configuration wrong somewhere but am currently at a bit of a loss to understand where.

[fhanik]

hi Simon,
So far there are three errors in your post
ORA-12519
ORA-12516
ORA-12505

There is one slight difference between tcServer and Tomcat. It ships with two different connection pools. The default one is a highly optimized, new pool. But it requires some configuration to do cleanup where code doesn't, let me explain.

First, to verify this problem, you would add

factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"

to your Resource definitions.

This switches to the regular pool. If this one works for you, we can continue working with the new pool, since it would require some new settings

Here is what is different with the new pool

In order to be as optimized as possible, the new pool, tomcat-jdbc, doesn't do any statement cleanup in the default configuration. This means, if the application doesn't properly cleanup statements and resultsets, one must tell the new pool to configure it for you.

Try to add the factory, see if you still see the error. Good chances will be that you will not.

And if you don't, and still have time, I would like to work with you to see if we can get the new pool work the same way for you.

best
Filip

[simonwbaker]

Thanks Filip,

That seems to work. It would seem, then, that the "regular" pool is doing the extra cleanup work you refer to whilst I don't have the new "high concurrency" pool correctly configured.

You imply that the application is not properly cleaning up statements and result sets. The application is using Spring-JDBC with a JNDI data source. As far as I understand it (and judging by execution on other servers, Tomcat and OC4J) the Spring framework code deals with the boilerplate job of performing these cleanups. Is there some configuration I should be checking for this?

Thanks for the offer to work further to get the new pool sorted. I would be interested to do so (I'm hoping that tcServer will prove to be a viable replacement for OC4J for us). Over the next couple of weeks I will not be in the office (partly due to Spring One) so it may have to wait until the beginning of May.

Cheers,
Simon

[fhanik]

As far as I understand it (and judging by execution on other servers, Tomcat and OC4J) the Spring framework code deals with the boilerplate job of performing these cleanups. Is there some configuration I should be checking for this?

That should be the case.
The error 'Connection refused' may indicate that you have reached the 'oracle process limit'.
If you revert to the old config, run it till you see the error, and do a netstat -nao
then send us the lines, it will confirm if this is the case. and if so, then it could be cleanup related.

[fhanik]

That should be the case.
The error 'Connection refused' may indicate that you have reached the 'oracle process limit'.
If you revert to the old config, run it till you see the error, and do a netstat -nao
then send us the lines, it will confirm if this is the case. and if so, then it could be cleanup related.

The new connection pool also allows for more concurrency, since validation happens concurrently, where as with DBCP, validation is one thread at a time. In most oracle instances, this means you have a set number of processes configured, and you can hit that limit much faster using the new pool.
http://forums.oracle.com/forums/thre...hreadID=305117

So while maxActive on either pool essentially means the same thing, the DBCP pool still only takes one connection and validates it. During that validation, no other threads can request connections out of the pool, significantly lowering the concurrency of the pool.

Let me know when you come back from SpringOne, and we can work out the rest.

[simonwbaker]

Hi Filip,

Thanks for your help. I do intend to investigate tcServer further but I'm back on a development project at the moment. The problem I have is that, not being a DBA, investigating the type of thing you suggest is time-consuming for me so I can't do it as a background task whilst working on other projects. I hope to get some time to delve further in a few weeks.

Simon