Search code examples
databaseoracle-databasespring-boothibernatedatasource

In springboot, can I connect with two database so that if one database is giving exception then I can connect to the other database dynamically

Requirement in detail:

So I have two databases (both are in sync) and somehow one goes down and Spring Boot application starts giving exceptions. In this case I want the application to connect to the second database.

Please help me with this.

Thanks in advance.

Solution

  • As you have a DataGuard implementation in Oracle with a Primary database and another one in Standby mode, Oracle Transparent Application Failover is the way to go.

    Transparent Application Failover (TAF) is a feature of the Java Database Connectivity (JDBC) Oracle Call Interface (OCI) driver. It enables the application to automatically reconnect to a database, if the database instance to which the connection is made fails. In this case, the active transactions roll back.

    Database Setup

    I am assuming your implementation of DG uses Oracle Restart.

    Datatase: TESTDB Service in TAF: TESTDB_HA

    Primary site

    srvctl add service -d testdb -s testdb_ha -l PRIMARY -y AUTOMATIC -e select -m BASIC -z 200 -w 1
srvctl start service -d testdb -s testdbha

    Standby site

    srvctl add service -d testdb -s testdb_ha-l PRIMARY -y AUTOMATIC -e select -m BASIC -z 200 -w 1
srvctl modify service -d testdb -s testdb_ha -failovermethod basic

    Your JDBC connection

    jdbc:oracle:thin:@(description=(address=(host=primaryserver)(protocol=tcp)(port=yourdbport))(address=(host=standbyserver)(protocol=tcp)(port=yourport))(failover=yes)(connect_data=(service_name=testdb_ha)(failover_mode=(type=select)(method=basic))))

    In this setup, in case a failover from Primary to Standby, the connection will keep working once the failover is completed without manual intervention.

    I am using this configuration currently in applications store in Kubernetes, using Spring Boot and/or Hibernate, and in normal Jboss Java applications. I have personally tested failover scenarios totally transparent for the applications. Obviously, if you have a transaction or query running in the moment the failover is being performed, you will get an error. But you don't need to manually change any jdbc settings in case of switch from primary site to standby site.