1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-24778: cannot open connections:URGENT

Discussion in 'Server Administration and Options' started by pjsr, Mar 27, 2009.

  1. pjsr

    pjsr Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    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 -
    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.
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    ORA-24778 is actually a rare error... Obviously your main issue is not the database but .NET. If you are already using synonyms(I presume so), I would suggest creating views in your local database from the DB Linked database and try to use those views in your .NET code.
     
  3. pjsr

    pjsr Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Thanks for the reply. Yes we are using synonyms and even tried creating views, but without any luck.
     
  4. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    in that case a temporary solution can be by creating materialized views instead of a regular view. You can create a job to periodically refresh the materialized view.
     
  5. pjsr

    pjsr Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Actually that would be an overhead from the database maintainence point of view since there are a whole lot of tables involved. I thought may be there was some feature in Oracle which can help us getting over this issue.
     
  6. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    well it might be a maintenance overhead but if you can't get anything else to work and don't want to touch your .NET code then what else can u do?

    I would say open an SR with Oracle, (if you already haven't). Hear what they have to say. There might be a simpler solution that you and I don't know about.
     
  7. pjsr

    pjsr Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Yes we have already initiated that. Hope they can come out with something.
    Thanks for your answers. Will post, if we can get it working.