org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory ( 'v$session.osuser' , value is 'x')

1
votes

I am getting below error on my Tomcat7@openshift ,i am using jax-rs and spring4 and oracle for database connection(using JNDI lookup to get the datasource). Currently, the DataSource is being injected in the application with JNDI in using Spring configuration file:

seeing wierd exception .. and i can see only 1 instance out of 2 is working fine. what is wrong in configuration. can anybody point out.

I am using Jdk1.7,oracle-11 and driver ojdbc6-11.2.0.jar,tomcat7.

please see my configuration and error..

Context.xml and ApplicationContext.xml and errorstackTrace.

<Resource name="jdbc/DMDataSource"
            auth="Container"
            type="javax.sql.DataSource"
            driverClassName="oracle.jdbc.OracleDriver"
            url="jdbc:oracle:thin:@ldap://ldap-ldprd.cisco.com:5000/cn=OracleContext,dc=cisco,dc=com/TS2DMP"
            username="x"
            password="x"

            poolPreparedStatements="true"
            initialSize="5" 

            maxActive="50"
            maxIdle="5"
            minIdle="5"

            maxWait="20000" 

            testOnBorrow="true"

            testOnReturn="false" 
            testWhileIdle="false"

            validationQuery="select 1 from dual" 
            validationInterval="60000"

            removeAbandoned="true"
            removeAbandonedTimeout="60"
            abandonWhenPercentageFull="100"
            logAbandoned="true"

            accessToUnderlyingConnectionAllowed="true"

            timeBetweenEvictionRunsMillis="180000"
            minEvictableIdleTimeMillis="180000"/>

=======================================================================

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:jdbc="http://www.springframework.org/schema/jdbc"
        xmlns:context="http://www.springframework.org/schema/context"
        xmlns:jee="http://www.springframework.org/schema/jee"
        xsi:schemaLocation="
        http://www.springframework.org/schema/beans 
        http://www.springframework.org/schema/beans/spring-beans.xsd   
        http://www.springframework.org/schema/context 
        http://www.springframework.org/schema/context/spring-context.xsd 
        http://www.springframework.org/schema/jdbc 
        http://www.springframework.org/schema/jdbc/spring-jdbc.xsd
        http://www.springframework.org/schema/jee
        http://www.springframework.org/schema/jee/spring-jee.xsd
        ">

    <!-- to activate annotation on the registered beans with application context -->        
    <context:annotation-config />
    <!-- tag scans all classes & sub-classes under the value of base-package attribute and register them with the Spring container -->
    <context:component-scan base-package="com.cisco.b2b.gsws.services.rest.subscriptionApi.*" />
    <bean id="tmtclient" class="TMTClient"/>
    <bean id="sbpOagiMapper" class="util.SBPOagiMapper"/>
    <bean id="dmDbInputReq" class="com.cisco.b2b.gsws.services.rest.subscriptionApi.dto.UserProfileInDTO"/>
    <bean id="iFacade" class="com.cisco.b2b.gsws.services.rest.subscriptionApi.impl.Facade"/>
    <bean id="iServiceCall" class="com.cisco.b2b.gsws.services.rest.subscriptionApi.impl.SBPServiceCall"/>
    <bean id="dmdao" class="com.cisco.b2b.gsws.services.rest.subscriptionApi.impl.DmDaoImpl">
        <property name="dataSource" ref="dataSource" />
    </bean>
    <jee:jndi-lookup id="dataSource" jndi-name="java:/comp/env/jdbc/DMDataSource"/>


INFO: Deploying web application archive /var/lib/openshift/orderstatusws-oswsextrtpstage-1/app-root/runtime/dependencies/jbossews/webapps/ROOT.war
Jul 24, 2015 11:20:17 PM org.apache.naming.NamingContext lookup
WARNING: Unexpected exception resolving reference
org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Connection property: format error: Property is 'v$session.osuser' and value is 'orderstatusws-oswsextrtpstage-1')
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1551)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1390)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getLogWriter(BasicDataSource.java:1100)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory.createDataSource(BasicDataSourceFactory.java:350)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory.getObjectInstance(BasicDataSourceFactory.java:156)
    at org.apache.naming.factory.ResourceFactory.getObjectInstance(ResourceFactory.java:141)
    at javax.naming.spi.NamingManager.getObjectInstance(NamingManager.java:321)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:842)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:153)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:830)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:167)
    at org.apache.catalina.core.NamingContextListener.addResource(NamingContextListener.java:1103)
    at org.apache.catalina.core.NamingContextListener.createNamingContext(NamingContextListener.java:682)
    at org.apache.catalina.core.NamingContextListener.lifecycleEvent(NamingContextListener.java:271)
    at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:117)
    at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:90)
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5355)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
    at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:901)
    at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:877)
    at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:632)
    at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:1083)
    at org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:1880)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
    at java.util.concurrent.FutureTask.run(FutureTask.java:262)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Connection property: format error: Property is 'v$session.osuser' and value is 'orderstatusws-oswsextrtpstage-1'
    at oracle.jdbc.driver.T4CConnection.validateConnectionProperties(T4CConnection.java:4653)
    at oracle.jdbc.driver.PhysicalConnection.readConnectionProperties(PhysicalConnection.java:2397)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:527)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:236)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
    at org.apache.tomcat.dbcp.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
    at org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1558)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1547)
    ... 27 more
Jul 24, 2015 11:20:18 PM org.apache.catalina.core.NamingContextListener addResource
WARNING: Failed to register in JMX: javax.naming.NamingException: Cannot create PoolableConnectionFactory (Connection property: format error: Property is 'v$session.osuser' and value is 'orderstatusws-oswsextrtpstage-1')
Jul 24, 2015 11:20:18 PM org.springframework.web.context.ContextLoader initWebApplicationContext
INFO: Root WebApplicationContext: initialization started
Jul 24, 2015 11:20:18 PM org.springframework.web.context.support.XmlWebApplicationContext prepareRefresh
INFO: Refreshing Root WebApplicationContext: startup date [Fri Jul 24 23:20:18 EDT 2015]; root of context hierarchy
Jul 24, 2015 11:20:18 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [applicationContext.xml]
Jul 24, 2015 11:20:19 PM org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor <init>
INFO: JSR-330 'javax.inject.Inject' annotation found and supported for autowiring
2015-Jul-24 23:20:20.488 zzz [localhost-startStop-1] TRACE com.cisco.b2b.osws.services.rest.helpers.PropertiesCacheUtil - Inside the constructer-init() method 
2015-Jul-24 23:20:20.491 zzz [localhost-startStop-1] DEBUG com.cisco.b2b.osws.services.rest.helpers.PropertiesCacheUtil - cisco.env = null
2015-Jul-24 23:20:20.492 zzz [localhost-startStop-1] DEBUG com.cisco.b2b.osws.services.rest.helpers.PropertiesCacheUtil - cisco.life = stg
2015-Jul-24 23:20:20.496 zzz [localhost-startStop-1] DEBUG com.cisco.b2b.osws.services.rest.helpers.PropertiesCacheUtil - trying to read all properties from properties file under stg  enviroment
2015-Jul-24 23:20:20.497 zzz [localhost-startStop-1] DEBUG com.cisco.b2b.osws.services.rest.helpers.PropertiesCacheUtil - loaded all properties from  file under stg  enviroment
Jul 24, 2015 11:20:22 PM org.apache.naming.NamingContext lookup
WARNING: Unexpected exception resolving reference
org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Connection property: format error: Property is 'v$session.osuser' and value is 'orderstatusws-oswsextrtpstage-1')
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1551)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1390)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getLogWriter(BasicDataSource.java:1100)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory.createDataSource(BasicDataSourceFactory.java:350)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory.getObjectInstance(BasicDataSourceFactory.java:156)
    at org.apache.naming.factory.ResourceFactory.getObjectInstance(ResourceFactory.java:141)
    at javax.naming.spi.NamingManager.getObjectInstance(NamingManager.java:321)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:842)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:153)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:830)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:153)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:830)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:153)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:830)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:167)
    at org.apache.naming.SelectorContext.lookup(SelectorContext.java:156)
    at javax.naming.InitialContext.lookup(InitialContext.java:411)
    at org.springframework.jndi.JndiTemplate$1.doInContext(JndiTemplate.java:155)
    at org.springframework.jndi.JndiTemplate.execute(JndiTemplate.java:87)
    at org.springframework.jndi.JndiTemplate.lookup(JndiTemplate.java:152)
    at org.springframework.jndi.JndiTemplate.lookup(JndiTemplate.java:179)
    at org.springframework.jndi.JndiLocatorSupport.lookup(JndiLocatorSupport.java:95)
    at org.springframework.jndi.JndiObjectLocator.lookup(JndiObjectLocator.java:106)
    at org.springframework.jndi.JndiObjectFactoryBean.lookupWithFallback(JndiObjectFactoryBean.java:231)
    at org.springframework.jndi.JndiObjectFactoryBean.afterPropertiesSet(JndiObjectFactoryBean.java:217)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1633)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1570)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:539)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:476)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:303)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:299)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getTypeForFactoryBean(AbstractBeanFactory.java:1421)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.getTypeForFactoryBean(AbstractAutowireCapableBeanFactory.java:802)
    at org.springframework.beans.factory.support.AbstractBeanFactory.isTypeMatch(AbstractBeanFactory.java:542)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doGetBeanNamesForType(DefaultListableBeanFactory.java:436)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.getBeanNamesForType(DefaultListableBeanFactory.java:412)
    at org.springframework.beans.factory.BeanFactoryUtils.beanNamesForTypeIncludingAncestors(BeanFactoryUtils.java:186)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAutowireCandidates(DefaultListableBeanFactory.java:1105)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1044)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:942)
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:533)
    at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:88)
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:331)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1210)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:537)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:476)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:303)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:299)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:194)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:755)
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:757)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:480)
    at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:434)
    at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:306)
    at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:106)
    at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4973)
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5467)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
    at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:901)
    at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:877)
    at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:632)
    at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:1083)
    at org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:1880)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
    at java.util.concurrent.FutureTask.run(FutureTask.java:262)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Connection property: format error: Property is 'v$session.osuser' and value is 'orderstatusws-oswsextrtpstage-1'
    at oracle.jdbc.driver.T4CConnection.validateConnectionProperties(T4CConnection.java:4653)
    at oracle.jdbc.driver.PhysicalConnection.readConnectionProperties(PhysicalConnection.java:2397)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:527)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:236)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
    at org.apache.tomcat.dbcp.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
    at org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1558)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1547)
    ... 69 more
1
javaspringoracletomcatjdbc
Please show the configuration you use – Mark Rotteveel
@mark posted the configuration.. pls let me know if you need other info. – gurdeep.sabarwal
Also post the config of the data source, and the version of Oracle and the (full!) version of the Oracle JDBC driver in use. – Mark Rotteveel
I am using Jdk1.7,oracle-11 and driver ojdbc6-11.2.0.jar,tomcat7.. – gurdeep.sabarwal
Please add this information to your question by editing it in, doing it in comments makes it harder to read (and easier to not see). – Mark Rotteveel

1 Answers

1
votes

RESOLVED :after spending hours on google(researches), i learned that issue was in connection properties in context.xml... you can set the v$session.osuser value in connectionProperties in connection pooling configuration. like this 'connectionProperties="v$session.osuser=MyApp;"

<Resource 
        name="jdbc/DMDataSource"
        global="jdbc/DMDataSource"
        dataSourceJNDI="jdbc/DMDataSource"
        auth="Container"
        jmxEnabled="true"
        connectionProperties="v$session.osuser=MyApp;"
        factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
        type="javax.sql.DataSource"
        driverClassName="oracle.jdbc.OracleDriver"
        url="${OPENSHIFT_DM_DB_URL}"
        username="${OPENSHIFT_DM_DB_USERNAME}"
        password="${OPENSHIFT_DM_DB_PASSWORD}"
        initialSize="5"
        maxActive="50"
        maxIdle="10"
        maxWait="10000"
        validationQuery="select 1 from dual"
        validationInterval="60000"
        testOnBorrow="true"
        testOnReturn="false" 
        testWhileIdle="false"
        timeBetweenEvictionRunsMillis="1200000"
        minEvictableIdleTimeMillis="1800000"
        removeAbandoned="true"
        removeAbandonedTimeout="60"
        logAbandoned="true"
   />