CODEWITHSUNDEEP
databaseoracle-databaseoracle10gtoaddblink
m.edmondson
m.edmondson

Reputation: 30922

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

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:

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

Upvotes: 2

Views: 64132

Answers (6)

JUSTIN RAJU
JUSTIN RAJU

Reputation: 1

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.

Upvotes: 0

Davi
Davi

Reputation: 105

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

Upvotes: 4

user5582933
user5582933

Reputation: 11

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.

Upvotes: 0

user1617237
user1617237

Reputation: 143

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

Upvotes: 0

Karl
Karl

Reputation: 3372

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.

Upvotes: 2

chance
chance

Reputation: 6507

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

Upvotes: 5

Related Questions