The Blog from the DBA Classroom

By: Joel Goodman

Do You Think About Your Database Links

Posted by Joel Goodman on 29/07/2009


I have written and presented a Master Class on Database links this year to two UKOUG DBA SIGs and added it to my “Articles and Presentations” page.  This forms part one of a two part series on DB Links which covers: the various DB Link Options, Remote Queries, Performance implications for DB Links, Security using DB Links and Implications for Administration. Part two will be presented at the UKOUG annual conference and will  cover:  remote and distributed queries, remote and distributed transactions; Oracle two-phase commit processing and recovery implications of distributed systems.

If you are interested in DB Links and Distributed Systems then have a look at Database Links Master Class Part 1.

Advertisements

2 Responses to “Do You Think About Your Database Links”

  1. The doc does not require shared server to use shared links – it is meant to cut down the number of server processes on the source database and network connections by allowing the servers to be reused but I have only ever used them together with shared server connections so I would try this. But there have been some reports of similar symptoms in the past involving XA and Shared DB links. I can not resolve it for you as that requires Oracle support but suggest that you try using shared server on the local database first to see if that resolves the problem. If not then raise an SR.

    Joel

  2. Kumar Ramalingam said

    Hi Joel,
    I have a question on configuring shared database links for our application that uses XA connections. Currently I have defined it as a shared database link (public) and it connects to a user in a remote database and authenticated by a different user in the remote database. This “Authenticated By” user has only create session privileges.
    While our application runs and calls the db link from inside a trigger (to synchronize the remote table) , we keep getting ora-24778 error. This happens intermittently and takes time to recover. Oracle support suggests that we should use SHARED SERVER configuration for this to work properly. I did not see in the documentation about any mention that we should use SHARED SERVER should be used for SHARED DATABASE LINKs. Would you please throw some light on this issue?

    To give you more background, our databases (source and target) are 11gR2 on Linux. Whenever I connect from SQL*Plus I see two sessions on the remote DB (one each for authenticating user and connect to user). It stays alive till I disconnect my local session. In the case of our app,which uses XA connections, the remote connections are not always in pairs (auth user and connect to user combo). At some point in time I see 4 Auth user connections (the SERVER column on v$session is DEDICATED) and 20 connect to user(SERVER column is PSEUDO here. This is the user who actually performs the DML operations).
    Not sure how this is occurring. would you please suggest some tracing mechanisms to see what is happening?

    Your invaluable suggestions are greatly appreciated.
    Thank you
    Kumar Ramalingam

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: