RE: DB links

I want to append to this thread because I've been having many the same problems 
this week and couldn't get my
answer out of this thread which I saved.

I copied my production database and moved it to another server.
I changed the global_name to xxxxx.standby using the  ALTER DATABASE RENAME 
GLOBAL_NAME TO xxxxx command.
I had an existing (copied) db link in this database copy. 
It stopped working because the 'standby' appended to the db_link.
We restored the name on the copy by updating global_name. You can't change it 
back by using rename. It wants
'something.something' and will not name it back to 'something.'   And, no, I 
don't recommend making any changes
to the data dictionary this way and wouldn't do it on a production system. I 
tested the db link and it worked. 

I dropped the db link again, changed the name of the database back to 
xxxxx.standby and recreated the db_link and 
got the error caused by the database name being different than the link name 
plus
global name.

I altered session and set global_name to false;

My error changed to a "TNSNames cannot resolve service name"

I couldn't solve it.  I created the db link again with the fully qualified 
host.  That didn't work.

CREATE PUBLIC DATABASE LINK xxx 
CONNECT TO xxxxxx IDENTIFIED BY xxxxxxx
USING 'xxxxxxxxx =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxxxxxx)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID =xxxxxxx)
      (SERVER = DEDICATED)
    )
  )
'; 

I dropped it, recreated the link again back to the db link alias.

CREATE PUBLIC DATABASE LINK xxx 
CONNECT TO xxxxxx IDENTIFIED BY xxxxxxx
USING 'xxxxxxxxx'
 
It worked.(??)

I think the key was that the link had to have been created after the 'alter 
session set global_name to false';  I'm 
not sure why the fully qualified host didn't work. I'm not positive what I did 
to fix it. I've reconstructed it as best
I could.  I thought this might be useful the next time somebody gets stuck.



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Ana Choto
Sent: Thursday, February 12, 2004 10:52 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: DB links






I'm not sure if you found a solution for this yet.  But, one thing I had
trouble with when using a link was that I was using the service name on my
select statement instead of the name of the link's.

i.e., select from xxx@servicename instead of select from xxx@dblink_name

Thanks

Ana E. Choto
American University
e-Operations - Information Technology
Phone (202) 885-2275
Fax      (202) 885-2224


                                                                           
             Michael Milligan                                              
             <Michael.Milligan                                             
             @ingenix.com>                                              To 
             Sent by:                  "'oracle-l@xxxxxxxxxxxxx'"          
             oracle-l-bounce@f         <oracle-l@xxxxxxxxxxxxx>            
             reelists.org                                               cc 
                                                                           
                                                                   Subject 
             02/11/2004 06:50          DB links                            
             PM                                                            
                                                                           
                                                                           
             Please respond to                                             
             oracle-l@freelist                                             
                   s.org                                                   
                                                                           
                                                                           




Has anyone had trouble using a link created for just certain databases,
when
everything is set right. In other words, no reason can be seen why it
wouldn't work? We changed the global name, etc., etc. I can connect to the
database but when I create the link, then try to select from dual from
another database, it says "TNSNames cannot resolve service name". But using
the same service name, I immediately connect successfully to the database!

Driving us crazy. Any help would be greatly appreciated.

Thanks,

Mike


This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity to
which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified
that
any dissemination, distribution or copying of this e-mail is prohibited. If
you have received this e-mail in error, please notify the sender by
replying
to this message and delete this e-mail immediately.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: