database link error
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: database link error

  1. #1
    Join Date
    Feb 2007
    Posts
    212

    database link error

    Hi Friends,

    Can you help me with my database link problem please.
    Something just went wrong here :(

    You see, I have 2 10g DBs on both WIN2003 (ORCL, AND ORCL2).
    On ORCL server I created a database link connecting to ORCL2.
    I also make a connection test first, say:
    At db server 1,
    C:\> sqlplus scott/tiger@orcl (connected)
    C:\> sqlplus scott/tiger@orcl2 (connected)

    Connecting back to orcl (conn scott/tiger@orcl) I then create a db link:

    SQL> create database link orcl2_lnk connect to scott identified by
    tiger using 'orcl2';

    SQL> select * from tab@orcl2_lnk;
    select * from tab@orcl2_lnk
    *
    ERROR at line 1:
    ORA-12154: TNS:could not resolve the connect identifier specified


    Why is this error pleaseeeeeeeee

    Thanks a lot

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    The connection desription for the orcl2 database has to be in tnsnames.ora on the orcl server, not on the client.

    As an option, you can put the whole connection description into the create database link statement:

    SQL> create database link orcl2_lnk connect to scott identified by
    tiger using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(PORT = orcl2_port)(HOST = orcl2_host))) (CONNECT_DATA = (SERVICE_NAME = orcl2_service_name)))';
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Be sure you have your orcl2 TNSNAMES entry in orcl://${ORACLE_HOME}/network/admin/tnsnames.ora then test if from box orcl by doing tnsping orcl2
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Feb 2007
    Posts
    212
    Of course hunnies I know that I have made several db link connections before, like 100s of times and this is my first time to fail
    Tha fact that I can connect to orcl2 at sever1
    C:\> sqlplus scott/tiger@orcl2 (connected)
    means I have entry of orcl2 in the tnsnames.ora at server1

    My tnsnames.ora at server01 is
    ORCL =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER01)(PORT = 1522))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcl)
    )
    )
    ORCL2 =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER02)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = orcl)
    )
    )


    I am working only on these 2 servers talking with each other and i dont
    use any client PCs yet. I can tsnping orcl2.

    Any more ideas pls.
    Last edited by jennifer2007; 03-25-2007 at 08:18 PM.

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    congratulations, you've just given everybody SCOTT's access rights, good luck on that security audit

    Anyway, you have an issue with your networking, period. Either your tnsnames.ora file is messed up (most likely) or your listener.ora file is not listening correctly (less likely).
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Feb 2007
    Posts
    212
    its funny i just drop and recreated the db links with the same command and
    its ok now

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width