I need to establish DB connectionfailover in SpringBoot application(embedded tomcat) for ibatis with jdbctemplate which uses c3p0 for connection pooling.Below is the applicaiton.properties of my applicaiton.I have primary and secondary databases.If primary fails then secondary should be used for the DB connection failover in runtime. Please help me to achieve this.I tried to include multiple DB urls as part of configuration but which is not working.
c3p0 Java Database Pooling, failover configuration https://docs.genesys.com/Documentation/Composer/8.1.4/Help/ConnectionPooling
application.properties:( error is coming)
# connection properties for data source
##########################################################################################################
spring.datasource.c3p0.driverClass=oracle.jdbc.driver.OracleDriver
spring.datasource.c3p0.maxConnectionAge=3600
spring.datasource.c3p0.maxIdleTime=600
spring.datasource.c3p0.initialPoolSize=5
spring.datasource.c3p0.maxPoolSize=10
spring.datasource.c3p0.minPoolSize=5
spring.datasource.c3p0.acquireIncrement=1
##########################################################################################################
spring.datasource.url=jdbc:oracle:thin:@primary.com:1521:db1,jdbc:oracle:thin:@secondary.com:1521:db2
spring.datasource.username=user
spring.datasource.password=password
As far as I know the failover configuration is dependent on the JDBC driver. In case of Oracle you configure it with connect descriptors. So in your case you would put this in tnsnames.ora
:
CONNECTION_WITH_FAILOVER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = secondary.com)(PORT = 1521))
(LOAD_BALANCE = no)
(FAILOVER = yes)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db)
(FAILOVER_MODE =
(TYPE = select)
(METHOD = preconnect)
(RETRIES = 180)
(DELAY = 10)
)
)
)
and then in your configuration:
spring.datasource.url=jdbc:oracle:thin:@CONNECTION_WITH_FAILOVER
Of course, you may not have or want to use tnsnames.ora
, in which case you can use connect descriptor as a part of JDBC connection string:
spring.datasource.url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=primary.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=secondary.com)(PORT=1521))(LOAD_BALANCE=no)(FAILOVER=yes))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db)(FAILOVER_MODE=(TYPE=select)(METHOD=preconnect)(RETRIES=180)(DELAY=10))))
See these links for more details on connection descriptors, tnsnames.ora
and configuring failover for Oracle DB:
tnsnames.ora
Note that the service name must be the same on all databases, so I replaced db1
and db2
from your configuration with db
.
If you want to have different service names you must configure separate data sources programmatically (as described by Sheetal Mohan Sharma).
Edit:
I reread the documentation (specifically the PDF I linked above) more carefully today and it seems that it is possible to specify secondary service name in the connect string, so in your case entry in tnsnames.ora would be:
CONNECTION_WITH_FAILOVER = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = primary.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = secondary.com)(PORT = 1521)) (LOAD_BALANCE = no) (FAILOVER = yes) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db1) (FAILOVER_MODE = (TYPE = select) (METHOD = preconnect) (RETRIES = 180) (DELAY = 10) (BACKUP = db2) ) ) )
and as a JDBC URL in application.properties:
spring.datasource.url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=primary.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=secondary.com)(PORT=1521))(LOAD_BALANCE=no)(FAILOVER=yes))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db1)(FAILOVER_MODE=(TYPE=select)(METHOD=preconnect)(RETRIES=180)(DELAY=10)(BACKUP = db2))))
I do not have such setup currently configured, so I put this in my tnsnames.ora:
CONNECTION_WITH_FAILOVER = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = google.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = my-actual-database)(PORT = my-db-port)) (LOAD_BALANCE = no) (FAILOVER = yes) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = my-service-name) (FAILOVER_MODE = (TYPE = select) (METHOD = preconnect) (RETRIES = 1) (DELAY = 1) ) ) )
to simulate primary connection failure (because obviously there is no Oracle DB running on google.com) and managed to connect to my database with DataGrip using connection URL: jdbc:oracle:thin:@CONNECTION_WITH_FAILOVER
I also tried this with connect descriptor directly in the JDBC URL:
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=google.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=my-actual-database)(PORT=my-db-port))(LOAD_BALANCE=no)(FAILOVER=yes))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=my-service-name)(FAILOVER_MODE=(TYPE=select)(METHOD=preconnect)(RETRIES=1)(DELAY=5))))
and it worked as well, although both times it took quite some time for the connection to be established (but that may be related to my network configuration or possibly the connection timeout value for the driver)
FAILOVER_MODE
parameter to suit your needs - consider especially the RETIRES and DELAY values -in the example I provided I used 180 retries and 10 second delay between retries, add to that the connection timeout on every retry and it might take a very long time before the driver actually switches to the failover connection. You need to define 2 set of properties and refer them separately - notice the difference in url keys
#set1
app.datasource.url=jdbc:mysql://localhost/test
app.datasource.username=dbuser
app.datasource.password=dbpass
app.datasource.pool-size=30
#set2
app.datasource.jdbc-url=jdbc:mysql://localhost/test
app.datasource.username=dbuser
app.datasource.password=dbpass
app.datasource.maximum-pool-size=30
But you can mark one of them as @Primary if you are using the default auto-configuration for JDBC or JPA (then that one will be picked up by any @Autowired injections).
ref - Spring docs
@Bean
@Primary
@ConfigurationProperties(prefix="datasource.primary")
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties(prefix="datasource.secondary")
public DataSource secondaryDataSource() {
return DataSourceBuilder.create().build();
}
You can also refer to step by step guide here.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With