Tessell’s High Availability (HA) implementation for Oracle uses DataGuard with FastStartFailover.
An HA event will cause the IP address of the Primary database to change. Applications using connection pools need to be able to handle the state change event, recycle the pool, and connect to the new IP address with little to no disruption to the application.
Failover Configuration
First, it is important to understand how .NET handles connections pools underlying your own applications and the Oracle Data Provider (ODP.NET driver) code.
According to Microsoft documentation in MSDN
If MinPoolSize is either not specified in the connection string or is specified as zero, the connections in the pool will be closed after a period of inactivity. However, if the specified MinPoolSize is greater than zero, the connection pool is not destroyed until the AppDomain is unloaded and the process ends. Maintenance of inactive or empty pools involves minimal system overhead.
To summarize, with default settings an application will never recycle a connection pool unless the application process is restarted. This is done to minimize the use of resources unless the user explicitly defines different connection pool strategies.
Second, it is important to understand how the ODP driver handles HA events. Under normal conditions Oracle DataGuard will signal that an HA event has happened. However, the ODP configuration has to be explicitly activated to respect the signal. Oracle states:
ODP applications can enable FCF through the High Availability Events, "HA Events", connection string attribute. When HA Events are enabled:
ODP connection pool proactively removes connections from the pool when a node, or database goes down.
ODP proactively forces threads waiting for responses from the downed database to exit out from the existing call to avoid any hangs. When such a connection is then returned to the pool, any resource associated with that connection is freed.
ODP establishes connections to existing Oracle instances if the removal of severed connections brings the total number of connections below the "min pool size", upon the next connection request.
By default this feature is disabled. To enable HA events, include "HA Events=true" in the connection string.
Thus, a basic connect string to enable this feature would look like
data source=myServiceName; User Id=myuser; Password=mypassword; HA events=true;
However, there may be other cases where the HA events flag may not be enough to survive a disaster. You need to ensure you have other methods to time out and recycle connections, etc. The pool will never close all connections until they time out which by default could take a very long time and in some cases could be persistent. We can tune for thist in the connection string by adding configurations such as:
data source=myServiceName; User Id=myuser; Password=mypassword; HA events=true; Min Pool Size=0; Max Pool Size=200; Connection Lifetime=60; Connection Timeout=30; Incr Pool Size=20; Decr Pool Size= 5;
Note: There is also an attribute to validate connections but this has not been tested in the scope of this document. You can add the below config to your connection string if needed.
The Validate Connection attribute validates connections coming out of the pool. This attribute should be used only when absolutely necessary, because it causes a round-trip to the database to validate each connection immediately before it is provided to the application. If invalid connections are uncommon, developers can create their own event handler to retrieve and validate a new connection, rather than using the Validate Connection attribute. This generally provides better performance.
Validate Connection=true;
The following configuration settings are typically used to manage connection pooling behavior
Connection Lifetime = <seconds>
Connection Timeout = <secomnds>
Decr Pool Size = <integer> # how many connections to close at a time
HA Events = <true/false> # Respect HA dataguard events
Incr Pool Size = <integer> # how many connections to add at a time
Max Pool Size = <integer>
Min Pool Size = <integer>
Pooling = <true/false> # this disables it completely
Validate Connection = <true/false>
Please note that configurations can also be placed in .NET configuration files. As a sanity check you should make sure the .NET machine.config file in C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config (or similar version directory) does not have any hard-coded connection strings or settings. You will find a <connectionStrings> section in the file. We can also check if any settings have been placed to control the ODP behavior in the <oracle.manageddataaccess.client> section. machine.config applies global settings, but this is not the only .NET config file where configurations can be defined. Config files in the applications themselves could also contain hardcoded settings.
If you are troubleshooting connection pooling issues you can turn on tracing forODP in the .NET machine.config on the server. In the machine.config place the example below within the <configuration> section and after <configSections></configSections>
<oracle.manageddataaccess.client>
<version number="*">
<settings>
<setting name="TraceLevel" value="7" />
<setting name="TraceFileLocation" value="C:\traces\"/>
</settings>
</version>
</oracle.manageddataaccess.client>
After applying configurations to the connect string, machine.config, etc you may need to restart the application to recycle the connection pools and inherit the new configuration.
The following links provide additional reference and reading:
Developer's Guide
https://www.oracle.com/technetwork/database/availability/client-failover-2280805.pdf