Home » SQL & PL/SQL » SQL & PL/SQL » URGENT: ORA-24778 - Cannot open connections error (Oracle 10 g, VS2003)
URGENT: ORA-24778 - Cannot open connections error [message #394529] Fri, 27 March 2009 12:32 Go to next message
pjsr
Messages: 5
Registered: March 2009
Junior Member
Hi

We recently moved from having one DB server to two, brining in a new Archive DB server. So now our data resides in 2 DB which reside on separate servers. Now, While querying the Archive DB over a DBLink in .NET code [special stress here, only the .NET code], we are getting an oracle error
ORA-24778:

This is what we got when we did a google on this -

ORA-24778: cannot open connections
Cause: The migratable transaction tried to access a remote database when the session itself had opened connections to remote database(s).
Action: Close the connection(s) in the session and then try to access the remote database from the migratable transaction. If the error still occurs, contact Oracle customer support


As per our understanding, this is because:
we are calling a .NET Comp Plus component in a transaction, which in turns tries to execute a SELECT over the DBLink.
Now even a SELECT over a DBLink opens up a distributed transaction. So we are having 2 transactions here which is causing issues.

If we call the Comp Plus component in NO transaction mode and then execute SELECT over the DBLink, it works fine. But our problem is we need to call in Transaction mode ONLY and this cannot be modified since this requires a lot of changes in the .NET code. So basically modifying the code is ruled out of the picture (or stays as the last alternative).

Is there an alternative, in which we can successfully do a SELECT query over the DBLink without getting this error. We are using Oracle10g. Please let me know if I need to provide any other details.

Thanks in advance.
Re: URGENT: ORA-24778 - Cannot open connections error [message #394531 is a reply to message #394529] Fri, 27 March 2009 12:41 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, I don't know a thing about .NET and how it opens the connection.

But one thing I would try is creating local views or synonyms to the remote dblink tables, and see if that works.
Re: URGENT: ORA-24778 - Cannot open connections error [message #394532 is a reply to message #394531] Fri, 27 March 2009 12:44 Go to previous messageGo to next message
pjsr
Messages: 5
Registered: March 2009
Junior Member
Hi

Thanks for the replying. We are accessing the remote tables via synonyms only. We did try having views but even those don't work.
I am looking primarily towards oracle DB management, if something can be done. I don't have much idea about the features of Oracle. So if there's a feature that can bring about a work around, that would be great.

Thanks
Jzee
Re: URGENT: ORA-24778 - Cannot open connections error [message #394534 is a reply to message #394529] Fri, 27 March 2009 12:54 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>If the error still occurs, contact Oracle customer support
What is/was Oracle's response?
Re: URGENT: ORA-24778 - Cannot open connections error [message #394540 is a reply to message #394534] Fri, 27 March 2009 13:12 Go to previous messageGo to next message
pjsr
Messages: 5
Registered: March 2009
Junior Member
Still waiting to hear from them.
Re: URGENT: ORA-24778 - Cannot open connections error [message #394560 is a reply to message #394540] Fri, 27 March 2009 15:19 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I did a little googling, does the "Microsoft Transaction Server" have any parts in this?

If yes, maybe you can find something here.
Re: URGENT: ORA-24778 - Cannot open connections error [message #394565 is a reply to message #394560] Fri, 27 March 2009 15:33 Go to previous messageGo to next message
pjsr
Messages: 5
Registered: March 2009
Junior Member
I seem to have no idea around this. Could you please explain a bit how this can have an impact? Sorry I missed looking at the link that you provided. Going through that.

Update: Not sure how this could help in resolving the issue. Could you please throw in some more thoughts?

[Updated on: Fri, 27 March 2009 15:40]

Report message to a moderator

Re: URGENT: ORA-24778 - Cannot open connections error [message #394567 is a reply to message #394565] Fri, 27 March 2009 15:39 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Not really.

I googled for "migratable transaction", and it seemed to have something to do with the Microsoft Transaction Server which seems to be plugged between .NET and Oracle, and that's about all I know now in that direction.
Re: URGENT: ORA-24778 - Cannot open connections error [message #394568 is a reply to message #394567] Fri, 27 March 2009 15:41 Go to previous message
pjsr
Messages: 5
Registered: March 2009
Junior Member
Thanks for the pointer. I will try to find something from this point onwards. It would be great if you can also look for something.
Previous Topic: Query in 9i & 10g
Next Topic: Error executing statement: ORA-06550
Goto Forum:
  


Current Time: Sat Dec 10 05:19:45 CST 2016

Total time taken to generate the page: 0.11231 seconds