Connection to Oracle via TNS is not working

Question

I have a Spring Boot app that works smoothly when it connects to an Oracle instance in the classic way:

jdbc:oracle:thin:@<server name>:<port num>/<service name>

However, when I turn to a connection via tns through a tnsnames.ora stored in my local drive "C:\ORACLE\Client11g\network\admin" something doesn't work and I get this exception:

Caused by: org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:267)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:231)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:210)
    at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:51)
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:94)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:240)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:210)
    at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.handleTypes(MetadataBuildingProcess.java:352)
    at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:111)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:858)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:885)
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:60)
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:353)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:373)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:362)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1642)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1579)
    ... 36 more
Caused by: org.hibernate.HibernateException: Access to DialectResolutionInfo cannot be null when 'hibernate.dialect' not set
    at org.hibernate.engine.jdbc.dialect.internal.DialectFactoryImpl.determineDialect(DialectFactoryImpl.java:100)
    at org.hibernate.engine.jdbc.dialect.internal.DialectFactoryImpl.buildDialect(DialectFactoryImpl.java:54)
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:137)
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35)
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:88)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:257)
    ... 52 more

10:37:32,201 ERROR [org.jboss.msc.service.fail] (ServerService Thread Pool -- 381) MSC000001: Failed to start service jboss.undertow.deployment.default-server.default-host./my_service: org.jboss.msc.service.StartException in service jboss.undertow.deployment.default-server.default-host./my_service: java.lang.RuntimeException: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [app/persistence/OracleConfiguration.class]: Invocation of init method failed; nested exception is org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
    at org.wildfly.extension.undertow.deployment.UndertowDeploymentService$1.run(UndertowDeploymentService.java:85)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
    at org.jboss.threads.JBossThread.run(JBossThread.java:320)
Caused by: java.lang.RuntimeException: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [app/persistence/OracleConfiguration.class]: Invocation of init method failed; nested exception is org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
    at io.undertow.servlet.core.DeploymentManagerImpl.deploy(DeploymentManagerImpl.java:236)
    at org.wildfly.extension.undertow.deployment.UndertowDeploymentService.startContext(UndertowDeploymentService.java:100)
    at org.wildfly.extension.undertow.deployment.UndertowDeploymentService$1.run(UndertowDeploymentService.java:82)
    ... 6 more
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [app/persistence/OracleConfiguration.class]: Invocation of init method failed; nested exception is org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1583)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:553)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1081)
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:856)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:542)
    at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:122)
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:761)
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:371)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:315)
    at org.springframework.boot.web.support.SpringBootServletInitializer.run(SpringBootServletInitializer.java:151)
    at org.springframework.boot.web.support.SpringBootServletInitializer.createRootApplicationContext(SpringBootServletInitializer.java:131)
    at org.springframework.boot.web.support.SpringBootServletInitializer.onStartup(SpringBootServletInitializer.java:86)
    at org.springframework.web.SpringServletContainerInitializer.onStartup(SpringServletContainerInitializer.java:169)
    at io.undertow.servlet.core.DeploymentManagerImpl$1.call(DeploymentManagerImpl.java:186)
    at io.undertow.servlet.core.DeploymentManagerImpl$1.call(DeploymentManagerImpl.java:171)
    at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:42)
    at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)
    at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
    at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
    at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
    at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
    at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
    at io.undertow.servlet.api.LegacyThreadSetupActionWrapper$1.call(LegacyThreadSetupActionWrapper.java:44)
    at io.undertow.servlet.core.DeploymentManagerImpl.deploy(DeploymentManagerImpl.java:234)
    ... 8 more

The following is my application.properties + application-oracleprod.properties files. Keep in mind that this approach works when I use a SID connection as previously said.

application.properties:

spring.profiles.active=oracleprod

spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect
spring.jpa.hibernate.ddl-auto=validate
hibernate.show_sql=true
hibernate.hbm2ddl.auto=update
hibernate.generate_statistics=true

application-oracleprod.properties (here the magic should happen due to this property "oracle.net.tns_admin" which sets the "tnsnames.ora" folder plus the link "@MY_SERVICE" which is obviously present in the "tnsnames.ora" file, but I really don't see what Oracle does under the hood):

spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

#/tnsnames.ora
oracle.net.tns_admin=C:/ORACLE/Client11g/network/admin

oracle.username=my_user
oracle.password=xyz
oracle.url=jdbc:oracle:thin:@MY_SERVICE

The Configuration class is clean and straightforward:

@Configuration
@ConfigurationProperties(prefix="oracle" )
public class OracleConfiguration {

    @NotNull
    private String username;

    @NotNull
    private String password;

    @NotNull
    private String url;

    /*@Value("${oracle.net.tns_admin}")
    private String tns;*/

    public void setUsername(String username) {
        this.username = username;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    /*public void setTns(String tns) {
        this.tns = tns;
    }*/

    @Bean
    DataSource dataSource() throws SQLException {
        OracleDataSource dataSource = new OracleDataSource(); // we use oracle data source API but it could be mysql
        dataSource.setURL(this.url);
        dataSource.setUser(this.username);
        dataSource.setPassword(this.password);
        dataSource.setImplicitCachingEnabled(true);
        dataSource.setFastConnectionFailoverEnabled(true);
        return dataSource;
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {

        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        try {
            em.setDataSource(dataSource());
        } catch (SQLException e) {
            e.printStackTrace();
        }

        em.setPackagesToScan(new String[] { "app.persistence.entity" }); // put the name of entity classes package 

        JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);

        //em.setJpaProperties(additionalProperties()); // this properties are set by "application-oracle.properties" file

        return em;
    }

    @Bean
    public PlatformTransactionManager transactionManager(EntityManagerFactory emf) {

        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(emf);
        return transactionManager;
    }

To summarize, I have followed the approach suggested in the Oracle documentation in combination with Spring Boot configuration files/class with no luck.

I have also tried different path formats:

C:/ORACLE/Client11g/network/admin/tnsnames.ora
C:\ORACLE\Client11g\network\admin
C:\\ORACLE\\Client11g\\network\\admin

I think I'm doing well because with a SID connection everything is perfect but probably something hidden is missing.

UPDATE

It turned out the db url is not correct, at least for a TNS connection. The thing is we have something like multiple hosts and not only a single one:

    MY_SERVICE =
  (DESCRIPTION_LIST =
    (LOAD_BALANCE = off)
    (FAILOVER = on)
    (DESCRIPTION =
      (CONNECT_TIMEOUT = 5)
      (TRANSPORT_CONNECT_TIMEOUT = 3)
      (RETRY_COUNT = 3)
      (ADDRESS_LIST =
        (LOAD_BALANCE = on)
        (ADDRESS = (PROTOCOL = TCP)(HOST = <ip_1> )(PORT = 1621))
        (ADDRESS = (PROTOCOL = TCP)(HOST = <ip_2> )(PORT = 1621))
      )
      (CONNECT_DATA =
        (SERVICE_NAME = <my_service_name>)
      )
    )
    (DESCRIPTION =
      (CONNECT_TIMEOUT = 5)
      (TRANSPORT_CONNECT_TIMEOUT = 3)
      (RETRY_COUNT = 3)
      (ADDRESS_LIST =
        (LOAD_BALANCE = on)
        (ADDRESS = (PROTOCOL = TCP)(HOST = <ip_3> )(PORT = 1621))
        (ADDRESS = (PROTOCOL = TCP)(HOST = <ip_4> )(PORT = 1621))
      )
      (CONNECT_DATA =
        (SERVICE_NAME = <my_service_name> )
      )
    )
  )

And I got:

 javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory
    Caused by: java.lang.RuntimeException: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaAutoConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory
    Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaAutoConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory
    Caused by: javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory
    Caused by: org.hibernate.exception.JDBCConnectionException: Unable to open JDBC Connection for DDL execution
    Caused by: java.sql.SQLRecoverableException: Errore di I/O: Unknown host specified 
    Caused by: oracle.net.ns.NetException: Unknown host specified "

Again, I'm a little bit confused how to tell Spring how to read this TNS configuration.

Any idea?

Answer 1

With your class you are basically disabling auto-configuration and thus rendering, a large part, of your application.properties useless.

There is nothing in your OracleConfiguration that isn't (or can be) automatically configured for you.

Note: I'm assuming 2 things, first you are using HikariCP as the connection pool and second you are using Spring Boot 1.4.x.

So for starters remove the OracleConfiguration class and fix the application.properties accordingly.

# DataSource
spring.datasource.type=com.zaxxer.hikari.HikariDataSource

#JPA   
spring.jpa.hibernate.ddl-auto=validate

#Hibernate
spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.generate_statistics=true

You don't need the spring.datasource.driver-class-name as that will be derived from the URL.

In your application-oracle.properties (or whatever profile name you use) set the following

# DataSource
spring.datasource.username=my_user
spring.datasource.password=xyz
spring.datasource.url=jdbc:oracle:thin:@MY_SERVICE

spring.datasource.hikari.dataSourceClassName=oracle.jdbc.pool.OracleDataSource
spring.datasource.hikari.dataSourceProperties.implicitCachingEnabled=true
spring.datasource.hikari.dataSourceProperties.fastConnectionFailoverEnabled=true

#JPA   
spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect

This configures the oracle specific JDBC properties.

The spring.datasource.hikari.dataSourceProperties are needed to set the additional properties in the OracleDataSource.

This should allow Spring Boot to automatically create the DataSource and appropriate EntityManagerFactory without you needing to create a custom configuration class at all.

Update (Config without Hikari and plain OracleDataSource)

If you want to use the plain OracleDataSource instead of a connection pool you could remove the spring.datasource.type from the default application.properties and add the following to the application-oracle.properties.

# DataSource
spring.datasource.type=oracle.jdbc.pool.OracleDataSource
spring.datasource.username=my_user
spring.datasource.password=xyz
spring.datasource.url=jdbc:oracle:thin:@MY_SERVICE

Which results in more or less the same as you manually configured.

Update #2

As you want to use TNS you also need to set a system property named oracle.net.tns_admin. (See this question).

So in your application initializer (if not set on your app server) you need to set it manually.

private static void determineAndSetTnsHome() {
    String tnsAdmin = System.getenv("TNS_ADMIN");
    if (tnsAdmin == null) {
        String oracleHome = System.getenv("ORACLE_HOME");
        if (oracleHome == null) {
            return; //failed to find any useful env variables            
        }
        tnsAdmin = oracleHome + File.separatorChar + "network" + File.separatorChar + "admin";
    }
    System.setProperty("oracle.net.tns_admin", tnsAdmin);
}

Call this method from your main and onStartup or configure method. This assumes you have the ORACLE_HOME or TNS_ADMIN variable set in your environment.

Answer 2

Lame method actually, but it worked perfectly

Put this in your Main class.

    System.setProperty("oracle.net.tns_admin",
            "<Path of tnsnames.ora>");

Use this in your properties file

spring.datasource.url=jdbc:oracle:thin:@ORCLPDB

I am still digging more. Will give update here

Answer 3

For some reason, for me only worked:

spring.datasource.url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=TODO)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TODO)))

instead of spring.datasource.url=jdbc:oracle:thin:@MY_SERVICE.

Also I'm calling the method determineAndSetTnsHome() suggested by the user M. Deinum in the accepted answer:

public static void main(String[] args) {
        SpringApplication.run(EvaApplication.class, args);
        determineAndSetTnsHome();
    }