Spotfire Ideas Portal
Our Spotfire IM (information model) sources from a database running on a multi-node Oracle Real Application Cluster (Oracle RAC). When a single node of this RAC goes down (either because of a hardware problem or for routine maintenance) the Oracle database can continue to service requests on the other nodes in the RAC. From Oracle's perspective the database is not down.
If a user has a query running against the database (via either the Web Player or the Professional Client) when the Oracle node they are currently connected to goes down, how will Spotfire react? Will Spotfire detect this scenario and retry/restart the query on another node a couple of times before giving up? Or will the user just get the hard error from Oracle, indicating the database is down when the database isn't really down? Is there any way to configure the Spotfire behavior (e.g., tell Spotfire to retry these "database down" type of errors 3 times, waiting 5 seconds between each re-try before giving up)? Has Spotfire considered leveraging Oracle RAC Fast Application Notification to better handle such scenarios?
---
I believe it is completely up to the application to leverage Oracle RAC resiliency. When an Oracle node goes down unexpectedly the in-flight connection to that node is lost. Other enterprise applications are configured to detect these types of errors and automatically retry failed operations. Or they can make use of the "Universal Connection Pool for JDBC" (UCP) to do this for them:
http://docs.oracle.com/cd/E11882_01/java.112/e12265/intro.htm
http://docs.oracle.com/cd/E11882_01/java.112/e12265/rac.htm
If Tibco Spotfire wants to position itself as an enterprise BI tool, I suggest you add such functionality to your product. As it stands now, any time an Oracle node goes down, Spotfire end users will get errors and they will have to manually re-run their operation. This does not provide the best user experience, and it doesn't appear there is anything we can do about it here at Quintiles, because it has to be handled by the Spotfire application code.
---
I am asking Spotfire to catch the error and automatically retry the operation for the user, so the user is oblivious to the fact there was any problem in the first place. That, I believe, requires making Spotfire an "enterprise ready" application that can leverage what Oracle offers.
---
The suggestion is that Spotfire leverage FCF or UCP, to remove dead connections from the pool and restart failed transactions.
---
FCF = Fast Connection Failover (deprecated in Oracle 11g Release 2)
UCP = Universal Connection Pool for JDBC (replaces FCF)
---
I guess I consider the suggestion to be in 2 parts:
1) Idle connection handling: Ensure that idle connections in the pool to an Oracle RAC node that becomes unavailable will not be used, nor cause additional failures, and will be replaced with new, good connections to the remaining available nodes as needed.
2) Active connection handling: Automatically detect node-related failures, and retry queries to ensure seamless failover of active connections to another Oracle RAC node that becomes available.
Part 1 is probably more "out-of-the-box" with something like UCP, whereas Part 2 probably requires additional Spotfire coding.
--------------------------------------------------------