We are using WLS 10.3.6.0 with WebCenter Content (WCC) as the application.
For the production back end, RAC will be be the production DB and we need to support failover to Data Guard.
We set up a Multi Data Source (MDS) with the first simple Data Source pointing to RAC and the second Data Source pointing to Data Guard.
Upon testing, this scenario basically works correctly: when RAC is shutdown and Data Guard enabled, the switch occurs and the app works against Data Guard. And conversely, when Data Guard is shut down and RAC brought up, MDS will revert to pointing to RAC and the app will work correctly.
However the DBA insists, that the alternate Data Source should not need to be completely down but should be able to be in a read-only state and the MDS should detect that if it is in a read-only state, it will be counted as unavailable and try to use the alternate Data Source. This does not work with the default set up, I see in the logs that a connection can be made to the read-only DB instance which will not work at all with the app and is not what is desired by the customer.
Is there a way to configure the MDS so that if a Data SOurce is only available as read-only, the alternate Data Source will be used?
I may be able to describe a trick... Try setting the "test table" property of the DataGuard DataSource
to some update statement, ideally one to a otherwise unused table. eg:
SQL update myJunkTable set foo = 1 where 1 = 0
This won't actually update the table (which could have zero rows even) but I hope the
read-only DBMS would throw an exception anyway, and this will give WLS the impression
it can't get healthy connections, and That DataSource won't be used, until the read-only
status is removed.
However, from a standard point of view, a read-only DBMS may be perfectly useful for some applications,
so that is not of itself a reason for WLS not to use it.