Datasource changes to secondary on run time if primary is offline

Question

I have to deal with the following scenario for spring application with Oracle database:

1. Spring application uses the primary database. In the meantime the secondary database stores data for disaster recovery (from primary).

The first step is currently provided. At this moment I have to implement:

2. When the primary database gets offline application should change the connection to the secondary database).

The implementation should be programmatically. How can I achieve that without changing the code that currently exists? Is there any working solution (library)?

I think about AbstractRoutingDataSource and ping databases (e.g. every 5 seconds) but I'm not sure about this solution.

Answer 1

So, let's to summarize the issue. I was unable to use Oracle RAC (Real Application Cluster). If the implementation should be programmatically you can try AbstractRoutingDataSource approche.

I have implemented timer that pings current database every 1 second (you can use validation query and check if you can read from database... if no we assume there is no connection and we can switch a datasource).

Thanks to that I was able to change datasource on runtime when current datasource is offline. What is more important it was automatic.

On the other hand, there are disadvantages:

• For short time user can see the errors if the database is not switched yet.
• Some part of application may stop working if it is not properly secured against the lack of connection to the database.