oracle.net.ns.NetException: Socket read timed out

Question

I have this error after executing my batch :

2015-11-06 15:45:32,179 [SchemaValidator                         ] ERROR | could not get database metadata
java.sql.SQLRecoverableException: Erreur d'E/S: Socket read timed out
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:419)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:536)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:228)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
    at java.sql.DriverManager.getConnection(DriverManager.java:571)
    at java.sql.DriverManager.getConnection(DriverManager.java:187)
    at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:173)
    at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:164)
    at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:149)
    at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:119)
    at org.hibernate.ejb.connection.InjectedDataSourceConnectionProvider.getConnection(InjectedDataSourceConnectionProvider.java:46)
    at org.hibernate.tool.hbm2ddl.SuppliedConnectionProviderConnectionHelper.prepare(SuppliedConnectionProviderConnectionHelper.java:51)
    at org.hibernate.tool.hbm2ddl.SchemaValidator.validate(SchemaValidator.java:130)
    at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:349)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1327)
    at org.hibernate.cfg.AnnotationConfiguration.buildSessionFactory(AnnotationConfiguration.java:867)
    at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:669)
    at org.hibernate.ejb.HibernatePersistence.createContainerEntityManagerFactory(HibernatePersistence.java:132)
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:225)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:308)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1477)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1417)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:519)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
    at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:322)
    at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:106)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1325)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1086)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:517)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
    at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:322)
    at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:106)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1325)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1086)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:517)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
    at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:322)
    at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:106)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1325)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1086)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:517)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:580)
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:895)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:425)
    at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:139)
    at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:83)
    at org.springframework.batch.core.launch.support.CommandLineJobRunner.start(CommandLineJobRunner.java:282)
    at org.springframework.batch.core.launch.support.CommandLineJobRunner.main(CommandLineJobRunner.java:574)
Caused by: oracle.net.ns.NetException: Socket read timed out
    at oracle.net.ns.Packet.receive(Packet.java:321)
    at oracle.net.ns.DataPacket.receive(DataPacket.java:103)
    at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:230)
    at oracle.net.ns.NetInputStream.read(NetInputStream.java:175)
    at oracle.net.ns.NetInputStream.read(NetInputStream.java:100)
    at oracle.net.ns.Packet.extractData(Packet.java:423)
    at oracle.net.ns.AcceptPacket.<init>(AcceptPacket.java:136)
    at oracle.net.ns.NSProtocol.connect(NSProtocol.java:312)
    at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1054)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:308)

This my DB configuration from a .properties :

batch.jdbc.driver=oracle.jdbc.driver.OracleDriver
batch.jdbc.url=jdbc:oracle:thin:@*********:****/*****
batch.jdbc.user=*****
batch.jdbc.pwd=*****

and this is the config.xml :

<?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:util="http://www.springframework.org/schema/util"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
    http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.0.xsd">

    <bean id="dataSourceCredentialsProvider"
        class="com.natixis.sphinx.security.credentials.AlcasarCredentialsProvider">
        <property name="ldapEntry" value="${batch.jdbc.ldapEntry}" />
        <property name="application" ref="application" />
    </bean>

    <bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName"
            value="oracle.jdbc.driver.OracleDriver" />
        <property name="url" value="${batch.jdbc.url}" />
        <property name="username" value="${batch.jdbc.user}" />
        <property name="password" value="${batch.jdbc.pwd}" />
    </bean>

    <!-- Transaction manager for a single JPA EntityManagerFactory (alternative to JTA) -->
    <bean id="transactionManager"
        class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory"
            ref="entityManagerFactory" />
    </bean>

    <!-- Spring Configuration Entity Manager Factory -->
    <bean id="entityManagerFactory"
        class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">

        <property name="jpaVendorAdapter">
            <bean
                class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
        </property>
        <property name="jpaPropertyMap">
            <map>
                <entry key="hibernate.cache.use_second_level_cache"
                    value="true" />
                <entry key="hibernate.cache.use_query_cache"
                    value="true" />
                <entry key="hibernate.cache.provider_class"
                    value="net.sf.ehcache.hibernate.SingletonEhCacheProvider" />
                <entry key="hibernate.show_sql" value="true" />
                <entry key="hibernate.use_sql_comments" value="false" />
                <entry key="hibernate.format_sql" value="false" />
                <entry key="hibernate.hbm2ddl.auto" value="validate" />
                <entry key="hibernate.dialect"
                    value="org.hibernate.dialect.Oracle10gDialect" />
                <entry key="hibernate.temp.use_jdbc_metadata_defaults"
                    value="false" />
            </map>
        </property>
        <property name="dataSource" ref="dataSource" />
    </bean>
    <bean id="namedParameterJdbcTemplate"
        class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
        <constructor-arg ref="template" />
    </bean>
    <bean id="template"
        class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource" />
        <!-- <property name="fetchSize" value="#{pageSize+1}" /> -->
    </bean>

</beans>

Actually I could access easily to our local web application, and I could see in logs the connection trace to DB, but it is not the case for the batch.

What I notice in the logs before throwing the error, it get stuck some seconds at this log level :

2015-11-09 09:56:55,728 [DozerBeanMapper                         ] INFO  | Initializing a new instance of the dozer bean mapper.

And after I have this :

2015-11-09 09:57:59,227 [JDBCExceptionReporter                   ] WARN  | SQL Error: 17002, SQLState: 08006
2015-11-09 09:57:59,227 [JDBCExceptionReporter                   ] ERROR | Erreur d'E/S: Socket read timed out
2015-11-09 09:57:59,232 [CommandLineJobRunner                    ] ERROR | Job Terminated in error: Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: Cannot open connection
org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: Cannot open connection

Any help ?

Solution 1

might be missing the "//" in the Service Name format ?

Oracle JDBC Thin Driver Formats

Oracle JDBC Thin using a Service Name:

jdbc:oracle:thin:@//<host>:<port>/<service_name>

Example: jdbc:oracle:thin:@//192.168.2.1:1521/XE

Oracle JDBC Thin using an SID:

jdbc:oracle:thin:@<host>:<port>:<SID>

Example: jdbc:oracle:thin:192.168.2.1:1521:X01A

Note: Support for SID is being phased out. Oracle recommends that users switch over to using service names.

Oracle JDBC Thin using a TNSName:

jdbc:oracle:thin:@<TNSName>

Example: jdbc:oracle:thin:@GL

Note: Support for TNSNames was added in the driver release 10.2.0.1

Solution 2

I resolved the problem. I was using the bad JRE version. I used jdk1.7 instead of WASD8 JRE. To do this change, you must go to run configuration --> JRE --> tick 'Alternate JRE' and choose in the list WASD8 JRE (maybe in your case it should be diffrent)

Comments

[nabeel]

looks OS IP layer or firewall or network device that detects inactive connections for more than specific time and force closes them.. So either to configure your connection pool to close idle connections before that network layer does it, or configure "keep alive" in it.

[Nabil Salah]

thank you for your answer. could you please help me how to configure this in my case ?

[nabeel]

i'm not sure i can help since i never done that, but is this connection (user/pass@url) working for other processes ? or exception occurs only after some time ?

[Nabil Salah]

this exception happens only for batches. I could connect easly to database when I deploy my web application

[nabeel]

why do you have "/old3804a" at the end of the url ? after look at it again and at the exception, your process not able to connect so issue in configurations

[Nabil Salah]

it is the url we use to connect to database. This is not the cause of the issue.

[Nabil Salah]

Hi nabeel. Thank you for your answer. I did the suggested change : same error as before. I updated my question with more details at the end that could help understand better my issue.