I have multiple Tomcat instances running behind Apache load balancer. Now we need to have Oracle 9.2 multiple instances associated to each server. How to specify that in the configuration? Is there any mechanism where the database switching is automatic based on the instance state?
Please let me know any solution.
You can't do this invisibly in Tomcat, your JDBC connection pool points to only one database, the only way to change it is to modify your server.xml and restart.
There are two ways to achieve automated switching in software based on state:
1. Set up a connection pool for each database; write code to pick a connection from the right pool based on state. Pros: simple to implement. Cons: several pools, many database connections.
2. Write your own connection pool that manages a single pool of connections to all your databases, each pooled connection would have to be tagged with the relevant state, and getConnection would accept state as an argument and return a connection appropriate to that state. Pros: only one pool. Cons: you have to write your own pooling code and configuration.