I am using tnsnames.ora for my Oracle database connection. DB servers are clustered so tns names entry looks like:
ABCD =
(DESCRIPTION =
(ADDRESS_LIST =
(FAILOVER = on)
(LOAD_BALANCE = off)
(ADDRESS = (PROTOCOL = TCP)(HOST = 1.2.3.4)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 1.2.3.5)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 1.2.3.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = abcd)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
)
)
)
Supposedly, if 1.2.3.4 does not respond, it is to failover and try 1.2.3.5, but it doesn't.
Even if 1.2.3.5 is online and .4 is offline, I get oracle connection error. When I change the order and put .5 on top, connection error goes away.
In web.config, connection string is defined as:
<add name="ABCDConnectionString" connectionString="Data Source=ABCD;User Id=my_id;Password=my_pwd;" providerName="Oracle.DataAccess.Client" />
Am I missing something when trying to use this tns names entry? I am pretty sure changing the order is not the solution as it defeats the purpose of having failover.