• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Liutauras Vilda
  • Jeanne Boyarsky
  • paul wheaton
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Henry Wong
Saloon Keepers:
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Tim Moores
  • Mikalai Zaikin
Bartenders:
  • Frits Walraven

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

 
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Bartender
Posts: 2662
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Bartender
Posts: 2662
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Bartender
Posts: 2662
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Have you login Websphere console, and checked all "Connection Pool" settings?
 
lihangel Lll
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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?
 
Been there. Done that. Went back for more. But this time, I took this tiny ad with me:
Gift giving made easy with the permaculture playing cards
https://coderanch.com/t/777758/Gift-giving-easy-permaculture-playing
reply
    Bookmark Topic Watch Topic
  • New Topic