User cannot establish a DB link connection in Toad or wen trying to query for a table through a DB link, user get an ORA-12154 error. The normal connection can be made successfully.
The TNSNames file on the remote and current databases need to have the correct configuration to include the database alias name. GLOBAL_NAMES parameter is set to TRUE.
The TNSNames file on the remote and current databases need to have the correct configuration to include the database alias names. Also, ensure GLOBAL_NAMES Oracle Parameter is set to FALSE. Global name of database can be obtain from "Select * from Global_Name" when logged into that database.
Additional Information:
Following are some trouble shooting tips from Metalink:
One of the most common problems with this error and database links is a misunderstanding of where to put the configuration files.
When a client issues a database link connection:
SQL> SELECT * FROM emp@TEST_DBLINK;
TEST_DBLINK's TNS address is not resolved on the client, it is resolved on the server that client's SQL*Plus session is connected to. If a connection is made from a client to Server1 and then this database link is created:
SQL> CREATE DATABASE LINK TEST_DBLINK CONNECT TO scott IDENTIFIED BY tiger USING 'DEV1';
Server 1 needs a TNSNAMES.ORA file that contains the alias 'DEV1' which will point to Server 2. See table 6-1 for more information. Table 6-1 Components Needed to Create a Database Link
Client -----> Connected to -> Server1 ----> DB Link ---> Server2
TNSNAMES.ORA file TNSNAMES.ORA file containing an entry for DEV1 Server with DEV1 instance that contains an alias pointing to Server1
If there is a TNSNAMES.ORA file on the server, the make sure the file is in the <ORACLE_HOME>\network\admin directory. If there is not a TNSNAMES.ORA file, copy one over to the server and modify it. If there is a TNSNAMES.ORA file in the <ORACLE_HOME>\network\admin, and the TNS-12154 still occurs, start a SQL*Plus session on the server where the database link is being initiated and make a connection to the remote database. For example: % sqlplus scott/tiger@dev1
If a successful connection to the remote database is made from SQL*Plus but the database link is failing, skip to section 6.2. If the connection was not successful, use the table of contents of this bulletin to go to the chapter describing what problem is occurring (i.e. TNS-12154 from a Unix Client).
6.2 If SQL*Plus connected but the database link failed, start a Server Manager session by typing:
% svrmgrl
Connect internal to the database and issue the following query:
SVRMGRL> SELECT db_link, host FROM dba_db_links;
DB_LINK HOST
-------------------------------
TEST_DBLINK dev1.world
The column DB_LINK shows the name of the database link, and the column HOST shows the name of the TNSNAMES alias being used by the database link.
Rename the SQLNET.ORA file (if there is one) and make sure the value of the HOST column exactly matches the alias being used in the TNSNAMES.ORA file on the server.
Also try recreating the database link again using the complete description instead of the alias:
CREATE DATABASE LINK linkname
CONNECT TO user IDENTIFIED BY password
USING '(description=(address=(protocol=TCP)
(host=xxx.xxx.x.xx)(port=1521))(connect_data=(sid=sidname)))';
