| Broken DBLinks in Pooled connection [message #60713] |
Sat, 28 February 2004 04:33  |
DK
Messages: 11
Registered: July 2002
|
Junior Member |
|
|
We have very large web application and oracle database with few DBLinks. Since this is web application the application uses pooled connections.
We are facing ocasionally broken DBLinks due to N/W outage or remote DB shutdown. Since connection from app to main datbase is pooled, How to refresh these DB links
without ( AKA alter session or Some thing else) without affecting application functionality ?.
We are using Weblogic app server.
Any suggession appreciated.
|
|
|
|
| Re: Broken DBLinks in Pooled connection [message #60715 is a reply to message #60713] |
Sat, 28 February 2004 10:26  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
What do you mean "refresh" the db links? Oracle will automatically try to access the link when it need to go across the link. I have a case in 8i where some code has "autonomous" transactions and others which have "db links" The two won't co-exist in the same transaction. If transaction A (for the same pooled connection) opens a DB link and then transaction B having an "autonomous" transaction is invoked, then it fails. The solution was for each transaction to close any DB links it had open when it completes. In 9iAS you can specify code to run either before or after a transaction, so the fix can be applied to the DAD (connection).
Do something like this as the end of your transaction:
-- must commit/rollback first
commit;
FOR i IN (SELECT * FROM SYS.v_$dblink)
LOOP
EXECUTE IMMEDIATE 'alter session close database link ' || i.db_link;
END LOOP;
|
|
|
|
| Re: Broken DBLinks in Pooled connection [message #60719 is a reply to message #60713] |
Sun, 29 February 2004 13:45 |
Rohan
Messages: 6
Registered: February 2004
|
Junior Member |
|
|
The pooled connections are usually controlled from the weblogic end, so if a connection is lost weblogic needs to initiate the new connection. I don't believe anything can be done from the Oracle end.
I got the following from a bea newsgroup (http://newsgroups.bea.com/cgi-bin/dnewsweb?utag=&group=weblogic.developer.interest.jdbc&xrelated=1090&cmd_thread_next.x=48&cmd_thread_next.y=12).
Basically, you need to make a change in the weblogic server console. In the Testing tab for your jdbc connection pool, you need to set 'Test Reserved Connections' and/or 'Test Released Connections' to true, and nominate a test table (probably one that's always going to be small). This means that weblogic will test if a connection is active before assigning it to a user session or returning it to the pool. This will, however, cause some slight performance degradation.
How's that, some bea advice in an Oracle mailing list!!
|
|
|
|
| Re: Broken DBLinks in Pooled connection [message #60731 is a reply to message #60715] |
Mon, 01 March 2004 12:06 |
DK
Messages: 11
Registered: July 2002
|
Junior Member |
|
|
Well, If we execute this query every time, probaly there is performance penalty from oracle side. Since our database may have quite a few db links, closing them every time may impose additional performance penalty. This will also defeat the purpose of connection pooling.
I am looking for solution that will notify me re-actively if there are any connection issues.
The solution you propose is pro-active one.
I guess there is nothing we can do re-actively.
Thanks
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
