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:

Solution 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) ) ) )'

Solution 2

It worked fine for me without any additional changes to tnsnames.ora file:

CREATE DATABASE LINK <link> CONNECT TO <user> IDENTIFIED BY <password> USING
'(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <host>)(PORT = <port>))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <service_name>)
    )
)';

Solution 3

Check on the source DB host for valid TNS entries. The DB Link is created from RDBMS Host to RDBMS Host, your PC is irrelevant.

Comments

[m.edmondson]

It's not there, although I did add: PE_DBLINK.WORLD= (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=ORA04SOUK) (PORT=1521) ) (CONNECT_DATA= (SERVER=dedicated) (SERVICE_NAME=OPEGMTT1) ) ) to tnsames.ora to no avail

[m.edmondson]

Also there are no entries with HOST=OPEGMTP1.WORLD is that what I'm missing?

[chance]

I think so. Or you can alter that DBLink.

[m.edmondson]

Cheers for that - I've still got the same problem after closing + opening TOAD

[chance]

Are are sure you have editted the right tnsnames.ora correctly?

[m.edmondson]

When I connect to the database I'm using a connection who's ORACLE_HOME is C:\app\medmondson\product\11.2.0\client_3\ and the file I'm editing is C:\app\medmondson\product\11.2.0\client_3\network\admin\tnsn‌​ames.ora. I've added your entry but it's still the same. Although to edit the file I have to copy it away and back again (another process is using it).

[chance]

View the tnsnames.ora from TOAD to see if you have changed the right one. Or try to create your own DBLINK from BBEGMTD1.

[m.edmondson]

Tried both - TOAD certainly sees the correct file, and creating a new link generates the exact same message Link : "link" Error : ORA-12154: TNS:could not resolve the connect identifier specified!

[BartmanDilaw]

Option 4 is the one that works for me. Best one because it doesn't use tnsname.ora file...