2

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:

enter image description here

m.edmondson
  • 30,382
  • 27
  • 123
  • 206
  • run this and post the result here:`select * from ALL_DB_LINKS` – chance Dec 16 '11 at 10:26
  • Post what see from TOAD the contenet of tnsnames.ora. And post the content of the table ALL_DB_LINKS again. – chance Dec 16 '11 at 11:44
  • I've added the TNS Viewer however I'm having trouble executing your create link command (not correctly ended) although I did create a link through the GUI to no avail – m.edmondson Dec 16 '11 at 11:56
  • Can you show me the content of table ALL_DB_LINKS again? – chance Dec 16 '11 at 12:02

6 Answers6

5

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

chance
  • 6,307
  • 13
  • 46
  • 70
  • 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 Dec 16 '11 at 10:32
  • Also there are no entries with `HOST=OPEGMTP1.WORLD` is that what I'm missing? – m.edmondson Dec 16 '11 at 10:34
  • I think so. Or you can alter that DBLink. – chance Dec 16 '11 at 10:39
  • Cheers for that - I've still got the same problem after closing + opening TOAD – m.edmondson Dec 16 '11 at 10:42
  • Are are sure you have editted the right tnsnames.ora correctly? – chance Dec 16 '11 at 10:44
  • 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\tnsnames.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). – m.edmondson Dec 16 '11 at 10:47
  • View the tnsnames.ora from TOAD to see if you have changed the right one. Or try to create your own DBLINK from BBEGMTD1. – chance Dec 16 '11 at 10:57
  • 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!` – m.edmondson Dec 16 '11 at 11:16
  • Option 4 is the one that works for me. Best one because it doesn't use tnsname.ora file... – BartmanDilaw Jul 03 '20 at 15:00
4

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>)
    )
)';
Davi
  • 95
  • 8
2

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.

Karl
  • 3,312
  • 21
  • 27
0

If it's a private DB link, you need to login as the DB Link Owner to test.

user1617237
  • 143
  • 1
  • 3
  • 11
0

If you face any issue related to ORA-12514.

Scenario: customer is trying to connect to server A from server B using DB link then he gets ORA-12514 error. if the server A doesn’t not have the SID_LIST_LISTENER entry in the listener of server A ( this generally happens after upgrade) then he might get the error.

Error Code : ORA-12514: TNS: listener does not currently know of service requested in connect descriptor

Resolution : Add the SID_LIST_LISTENER entry in target Listener ( of server A)

Note # most of you must be aware.

JUSTIN RAJU
  • 1
-1

db_link are from db server to db server, not from client to server; so please make sure your both database servers have correct tns entries for service.

tnsnames.ora on Database server should have tns entries for service.

Christian
  • 7,062
  • 9
  • 53
  • 79
user5582933
  • 1