Changes in tnsnames and restart application server to refresh the new setting??

[ricky wong]

Dear all,

I am a production application support. A new application (websphere + oracle RAC) will be on production later, it uses OCI driver and websphere connection pool for JDBC connection. Our application development team tells us that if any change in ADDRESS entry in tnsnames.ora file of Oracle Client, we need to restart websphere application (refresh the connection pool) to refresh the new tnsnames.ora setting in JDBC connection. The datasource setting in Websphere does not have any connection validation configure. Does it mean that the connection pool caches up the setting in tnsnames.ora. Could anyone tell me it is correct or not? Thanks in advance.

Thanks.
Regards,
Ricky

[Jan Cumps]

Does it mean that the connection pool caches up the setting in tnsnames.ora.

No. It caches connections.
If connection check would have been configured, a Websphere restart should not be necessary. It should detect if the old connection is broken, and would read tnsnames when it tries to fetch a new connection to replace the broken one.

Why would you have to change the tnsnames while your application is running? Did your team give you a scenario where this would be needed?

[ricky wong]

Hi Jan,

Thanks for your reply. From your statement "If connection check would have been configured, a Websphere restart should not be necessary.", it means thas WAS should be restarted if it is not configured to validate the DB connection but tnsnames.ora is changed. Am I correct?

There was an accident happened in our testing environment before that due to one of Oracle server (in Oracle RAC env) was hanged up, our Oracle DBA commented such Oracle Server Address entry in "tnsnames.ora" to avoid any db connection to connect such problematic Oracle and also stop the listener of such Oracle server. Then the application team restarted WAS to refresh the new tnsnames.ora setting. But after that, the stopped Oracle Listener was suddenly brought up again and it was found that some db connections were connected to such problematic Oracle server. We were confused why such Oracle Address entry was commented in tnsnames.ora (=> it should not connect to this problematic Oracle server even its listener was started) but some db connections were still connected to this problematic Oracle successfully. We are not sure the root cause whether is related to any setting in WAS, JDBC or Oracle (e.g. in RAC).

Although it was happened in testing env, it should be also happened in production env.

Appreciate if you can help and give me more details or direction.

Thanks.
Regards,
Ricky

[Jan Cumps]

The listener does not play a role anymore once you are connected.

If your pool has a connection to database A,
and you stop the listener, change tns to database B, and restart the listener,
The connection in the pool will still be connected to database A. Because stopping the listener, or changing tnsnames, does not invalidate a connection.

So if A was still up, but probblematic, you would indeed still have connections to A, even after changes to tnsnames.
This would not have been prevented by using a validation query, because it would have detected that the connection to A was still ok.

[ricky wong]

Hi Jan,

Thanks for your reply. In our case, after changed the tnsnames.ora in Oracle client, we also restarted WAS to refresh the DB connnection pool. In addition, the tnsnames.ora was also configured to use "DEDICATED" in CONNECTION_DATA parameter. So in our understanding, even the problematic oracle listerner was up again, there should be not possible that any db connection was connected to such problematic oracle server. But it was the fact that some db connections were still connected to such problematic oracle server. Do you know why? Thanks in advance.


Thanks.
Regards,
Ricky

[Jan Cumps]

you know why? Thanks in advance.

No. I don't know why.
If you restart WAS, the connection pool brand new, and creates all new connections.

[lihangel Lll]

Have you login Websphere console, and checked all "Connection Pool" settings?

[lihangel Lll]

ricky wong wrote:We were confused why such Oracle Address entry was commented in tnsnames.ora (=> it should not connect to this problematic Oracle server even its listener was started) but some db connections were still connected to this problematic Oracle successfully. We are not sure the root cause whether is related to any setting in WAS, JDBC or Oracle (e.g. in RAC).

Have you login Websphere console, and checked all "Connection Pool" settings?