Public Database Link fails with ORA-12154: TNS:could not resolve the connect identifier specified

Error Description:
Select from database link fails with following error.

12154. 00000 -  "TNS:could not resolve the connect identifier specified"
*Cause:    A connection to a database or other service was requested using
           a connect identifier, and the connect identifier specified could not
           be resolved into a connect descriptor using one of the naming methods
           configured. For example, if the type of connect identifier used was a
           net service name then the net service name could not be found in a
           naming method repository, or the repository could not be
           located or reached.


Solution Description:
I created a database link in the following fashion.
Added a TNS entry to the database TNSNAMES.ora file.
testdb01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 11.108.40.65)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdb01)
    )
  )

SQL> create database link testdb connect to scott identified by tiger using 'testdb01';

Database link created.

 When I am trying to select from the database link by directly connecting to the database (not through TNS) it is working properly. But it is not working from a client machine (SQLPLUS/SQLdeveloper) through TNS. It says ORA-12154: TNS:could not resolve the connect identifier specified

There are different ways to create the Database link. You can bypass the TNS if you use the following format.

SQL> create database link testdb connect to siebel identified by siebel using '11.108.40.65:1521/testdb01';

Database link created.

SQL> select 1 from dual@testdb;

         1
----------
         1
Another way of creating the db link is as follows.

SQL> CREATE DATABASE LINK testdb
CONNECT TO siebel IDENTIFIED BY siebel
USING '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 11.108.40.65)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =testdb01)))'; 

Database link created.

SQL> select 1 from dual@testdb;

         1
----------
         1

These options will resolve your issue. Try out!!!
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google