Testing DB Link - ORA-12154: TNS:could not resolve the connect identifier specified

Question

I know there is a load of times this question has been asked but I can't quite figure out how to apply this information to my situation.

I have two databases of which I'm trying to connect with a DB LINK. These are:

• BBEGMTD1
• OPEGMTP1.WORLD

A DB link has already been defined by someone, with the name PE_DBLINK.WORLD.

I can access both of these databases from my machine, so I can't see how tnsnames.ora would be affected.

However when I test the DB LINK I get the famous:

Link : "PE_DBLINK.WORLD" Error : ORA-12154: TNS:could not resolve the connect identifier specified

Could you please point me in the right direction, does the tnsnames.ora need to be modified on the BBEGMTD1 server?

As per @Chance comment, select * from ALL_DB_LINKS returns:

PUBLIC                        
PE_DBLINK.WORLD                                                                 
PRICING                       
OPEGMTP1.WORLD                                                                  
03-NOV-11

PUBLIC                        
EBPROJ.WORLD                                                                    
EBPROJ                        
MIDGMTP1                                                                        
17-JUN-09

MYOPEGMTP1WORLD.WORLD                                                           
PRICING                       
(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=ORA04SOUK) (PORT=1521) ) (CONNECT_D
ATA= (SERVER=dedicated) (SERVICE_NAME=OPEGMTT1) ) ) )                           
16-DEC-11

This is the TNSNames Editor in TOAD to show the details are being shown up:

Answer 1

1. Run this to check the host of the DBLink PE_DBLINK.WORLD:

   select * from ALL_DB_LINKS

2. Check your tnsnames.ora fro the HOST (i.e. OPEGMTP1.WORLD ) of that DBLink. Which is located at (your locale machine)

   %ORACLE_HOME%\network\admin\tnsnames.ora

3. If not found in your tnsnames.ora, then add it like this:

   OPEGMTP1.WORLD = (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=ORA04SOUK) (PORT=1521) ) (CONNECT_DATA= (SERVER=dedicated) (SERVICE_NAME=OPEGMTT1) ) )

4. Or create your own DBLink:

   CREATE DATABASE LINK MYOPEGMTP1.WORLD CONNECT TO user IDENTIFIED BY password USING '(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=ORA04SOUK) (PORT=1521) ) (CONNECT_DATA= (SERVER=dedicated) (SERVICE_NAME=OPEGMTT1) ) ) )'