Spring Boot: How to set retry attempts for Oracle Production database connections by external configuration properties in spring.datasource

Question

I am new to Spring and I would like set retry attempts for Oracle which is associated with our Spring Boot Java Application. I have few spring.datasource properties which are specified inside application.yml file.

datasource:

driverClassName: oracle.jdbc.driver.OracleDriver
url: "jdbc:oracle:thin:@//xxx-xxx-x-xxx:1521/database"
username: scott
password: tiger

I have refered this link which provides a list common Spring Boot properties but I could not find property which sets number of retry attempts for spring boot data-source. Does spring boot automatically retries when connection get failed? Is that make sense to set number of retry attempts for data-source?

I am frequently getting the connection reset error so I am planning to add retries attempt to make sure it attempts to retry on connection failure. Here is the stack-trace:

java.sql.SQLRecoverableException: IO Error: Connection reset
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:682) ~[ojdbc7-12.1.0.1.jar!/:12.1.0.1.0]
        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:715) ~[ojdbc7-12.1.0.1.jar!/:12.1.0.1.0]
        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:385) ~[ojdbc7-12.1.0.1.jar!/:12.1.0.1.0]
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:30) ~[ojdbc7-12.1.0.1.jar!/:12.1.0.1.0]
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:564) ~[ojdbc7-12.1.0.1.jar!/:12.1.0.1.0]
        at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:307) ~[tomcat-jdbc-8.0.33.jar!/:na]
        at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:200) ~[tomcat-jdbc-8.0.33.jar!/:na]
        at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:708) [tomcat-jdbc-8.0.33.jar!/:na]
        at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:642) [tomcat-jdbc-8.0.33.jar!/:na]
        at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:464) [tomcat-jdbc-8.0.33.jar!/:na]
        at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:141) [tomcat-jdbc-8.0.33.jar!/:na]
        at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:115) [tomcat-jdbc-8.0.33.jar!/:na]
        at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:102) [tomcat-jdbc-8.0.33.jar!/:na]
        at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:126) [tomcat-jdbc-8.0.33.jar!/:na]
        at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:139) [hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.engine.jdbc.internal.JdbcServicesImpl$ConnectionProviderJdbcConnectionAccess.obtainConnection(JdbcServicesImpl.java:279) [hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:124) [hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:111) [hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:234) [hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:206) [hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.cfg.Configuration.buildTypeRegistrations(Configuration.java:1887) [hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1845) [hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl$4.perform(EntityManagerFactoryBuilderImpl.java:857) [hibernate-entitymanager-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl$4.perform(EntityManagerFactoryBuilderImpl.java:850) [hibernate-entitymanager-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.boot.registry.classloading.internal.ClassLoaderServiceImpl.withTccl(ClassLoaderServiceImpl.java:425) [hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:849) [hibernate-entitymanager-4.3.11.Final.jar!/:4.3.11.Final]
        at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:60) [spring-orm-4.2.6.RELEASE.jar!/:4.2.6.RELEASE]
        at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:343) [spring-orm-4.2.6.RELEASE.jar!/:4.2.6.RELEASE]
        at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:319) [spring-orm-4.2.6.RELEASE.jar!/:4.2.6.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1637) [spring-beans-4.2.6.RELEASE.jar!/:4.2.6.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1574) [spring-beans-4.2.6.RELEASE.jar!/:4.2.6.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:545) [spring-beans-4.2.6.RELEASE.jar!/:4.2.6.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482) [spring-beans-4.2.6.RELEASE.jar!/:4.2.6.RELEASE]
        at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) [spring-beans-4.2.6.RELEASE.jar!/:4.2.6.RELEASE]
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) [spring-beans-4.2.6.RELEASE.jar!/:4.2.6.RELEASE]
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) [spring-beans-4.2.6.RELEASE.jar!/:4.2.6.RELEASE]
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) [spring-beans-4.2.6.RELEASE.jar!/:4.2.6.RELEASE]
        at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1054) [spring-context-4.2.6.RELEASE.jar!/:4.2.6.RELEASE]
        at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:829) [spring-context-4.2.6.RELEASE.jar!/:4.2.6.RELEASE]
        at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:538) [spring-context-4.2.6.RELEASE.jar!/:4.2.6.RELEASE]
        at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:766) [spring-boot-1.3.5.RELEASE.jar!/:1.3.5.RELEASE]
        at org.springframework.boot.SpringApplication.createAndRefreshContext(SpringApplication.java:361) [spring-boot-1.3.5.RELEASE.jar!/:1.3.5.RELEASE]
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:307) [spring-boot-1.3.5.RELEASE.jar!/:1.3.5.RELEASE]
        at org.springframework.boot.builder.SpringApplicationBuilder.run(SpringApplicationBuilder.java:134) [spring-boot-1.3.5.RELEASE.jar!/:1.3.5.RELEASE]

score 2 · Answer 1 · answered Sep 28 '16 at 17:28

We recommend using long form connection URL instead of Easy connect. You have the ability to pass connection descriptors such as RETRY_COUNT and RETRY_DELAY. Sample conneciton URL is below.

jdbc:oracle:thin:@ (DESCRIPTION= (CONNECT_TIMEOUT=15)(RETRY_COUNT=20) (RETRY_DELAY=3) (ADDRESS_LIST = (LOAD_BALANCE=ON) (ADDRESS=(PROTOCOL=tcp)(HOST=primaryscan)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=myorcldbservicename)))

CONNECT_TIMEOUT: When enabled, this parameter instructs Oracle Net services to wait for the specified number of seconds (15 seconds in the example) for the completion of the connection establishment. This is equivalent to SQLNET.OUTBOUND_CONNECT_TIMEOUT which specifies the time for the client to establish a connection to the oracle database instance. CONNECT_TIMEOUT overrides SQLNET.OUTBOUND_CONNECT_TIMEOUT.

RETRY_COUNT: It specifies the number of network connect retry attempts before returning a failure message to the client. In the example above, Oracle Net retries 3 times before returning an error message to the client. This helps in increasing the possibility of getting a connection and thus improves the performance.

RETRY_DELAY: This parameter specifies the wait time in seconds between reconnection attempts. It works in conjunction with RETRY_COUNT. So, it is advised to use RETRY_DELAY and RETRY_COUNT together to avoid unnecessary CPU cycles.

Does spring boot support this? I am not sure if we can this option with spring boot application? — Ashish Pancholi, Sep 29 '16 at 05:31
seems to be working for us, more on this RETRY_COUNT here https://community.oracle.com/thread/4056866 and in the official docs (for oracle 12 but it still works) https://docs.oracle.com/database/121/JJDBC/appcontnew.htm#JJDBC29073 — Andrzej Rehmann, Jan 18 '18 at 10:58

score 0 · Answer 2 · edited May 23 '17 at 11:48

0

That's a feature of the connection pool. If you have no opinion, we do have one.

You're probably using the Tomcat data source so check its documentation

I don't think retrying is the way to fix your issue. You probably have a connection that is invalid and the pool is not validating it before returning it for you. Spring Boot 1.4 does it by default. If you're on 1.3, this answer gives some additional information.

edited May 23 '17 at 11:48

Community

1
1

answered Sep 20 '16 at 07:56

Stephane Nicoll

31,977
9
97
89

Thanks. Yes I am using `Spring Boot 1.3.5`. `spring.datasource.testOnBorrow` and `spring.datasource.validationQuery` are not being recommended in prod box. – Ashish Pancholi Sep 20 '16 at 10:15

Spring Boot: How to set retry attempts for Oracle Production database connections by external configuration properties in spring.datasource

2 Answers2