1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

A question about CREATE PUBLIC DATABASE LINK and ORA-12154 error

Discussion in 'Server Administration and Options' started by dariyoosh, Nov 17, 2009.

  1. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Dear all,


    I have a problem about public database creation and I would appreciate if you could kindly give me a hand. I have the following connection parameters in my tnsnames.ora file:

    Code (Text):

    ...
    DGPAPROD.WORLD =
        (DESCRIPTION =
          (ADDRESS_LIST =
           (ADDRESS = (COMMUNITY=tcp.world)
            (PROTOCOL=TCP)(HOST=ORASR001)(PORT=1521)
            )
          )
           (CONNECT_DATA = (SID = DGPAPROD))
        )
    ...
     
    Having the above mentioned parameters I can connect to this remote database
    directly in a SQL*Plus shell:
    Code (Text):

    $ sqlplus username/password@DGPAPROD.WORLD
     
    This works pretty well and the connection is established without any problem.

    Now, what I would like to do is to create a public database link to this remote database in order to avoid the user/connection switching for viewing the content of this database. I proceeded according to the syntax indicated in the Oracle online documentation:

    http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_5005.htm#SQLRF01205

    Therefore I run the following in order to create a public database link
    Code (Text):

    CREATE PUBLIC DATABASE LINK SR001_dblink CONNECT TO user IDENTIFIED BY password USING 'DGPAPROD.WORLD';
     

    Apparently there is no error and the link is created successfully. However it cannot resolves the remote host and whenever I run the following query
    Code (Text):

    SELECT *
    FROM myenterprise@SR001_dblink

    ERROR at line 1:
    ORA-12154: TNS:could not resolve the connect identifier specified
     

    What causes this problem?

    Thanks in advance,


    Kind Regards,
    Dariyoosh
    :)
     
  2. Kirti

    Kirti Forum Advisor

    Messages:
    46
    Likes Received:
    12
    Trophy Points:
    130
    Hi

    In which database are you creating this Database Link? On the database where you are creating this DB link, is DGPAPROD.WORLD present in the tnsnames.ora?

    Alternately you can bypass tnsnames and create the DB link directly using the hostname and port and SID, something like this:
    Code (SQL):

    CREATE DATABASE link "TEST"
    CONNECT TO APPS
    IDENTIFIED BY "apps"
    USING '10.31.4.121:1524/DEVR12'
     
  3. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260


    Hello there,


    Thanks a lot for your help and this nice solution. In fact providing directly the connection parameters allows to create the database, I proceeded in the following way:


    Code (Text):

    CREATE PUBLIC DATABASE LINK SR001_dblink CONNECT TO user IDENTIFIED BY password USING '(DESCRIPTION =  (ADDRESS_LIST =  (ADDRESS = (COMMUNITY=tcp.world)
    (PROTOCOL=TCP)(HOST=ip_adresse)(PORT=1521)))(CONNECT_DATA = (SID = GPAPROD)))';
     
    And it works! However according to what I observed, you must use directly the IP address instead of hostname, otherwise it will not work. I don't know why!


    Thank you very much for solving my problem.

    Kind Regards,
    Dariyoosh
    :hurray
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You were told why; the tnsnames.ora file on the database server does not include the entry your local tnsnames.ora file contains. Fix this issue and your database link, as originally created, will work.