Error Creating Database Link---Please Help
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Error Creating Database Link---Please Help

Hybrid View

  1. #1
    Join Date
    Sep 2000
    Posts
    362

    Angry

    Hi,
    I have 2 databases on the same machine and they are called sid7 and sid8.

    The service names for them are
    louise_company and louise_news.

    Now I am trying to connect a database link in the company database(sid7) which will point to the news_owner user in the sid8 database.

    This is the command I am giving to create the link

    SQL> CREATE PUBLIC DATABASE LINK "NEWS_DBLINK" CONNECT TO
    2 "NEWS_OWNER" IDENTIFIED BY "sid8" USING 'LOUISE_NEWS' ;

    Database link created.

    BUT WHEN i TRY TO USE THE DATABASE LINK IT GIVES ME THE FOLOWING ERROR

    SQL> select * from company@NEWS_DBLINK
    2 /
    select * from company@NEWS_DBLINK
    *
    ERROR at line 1:
    ORA-02085: database link NEWS_DBLINK.HEROLD.COM connects to SID8.WORLD

    The error message indicates that

    ORA-02085 database link name connects to name

    Cause The database link attempted to connect to a database with a different name. The name of the database link must be the same name as the name of the database.
    Action Create a database link with the same name as the database to which it connects.

    Why should I be forced to give the link name same as the database name.

    Infact I even tried that but stil it doesnt work. Please Help

    Thanks
    Anurag
    Appreciation is a wonderful thing;
    It makes what is excellent in others belong to us as well.


  2. #2
    Join Date
    Nov 2000
    Posts
    344
    Try :

    CREATE PUBLIC DATABASE LINK "NEWS_DBLINK"
    CONNECT TO "NEWS_OWNER.WORLD" IDENTIFIED BY "sid8" USING 'LOUISE_NEWS'

    OR

    CREATE PUBLIC DATABASE LINK "NEWS_DBLINK"
    CONNECT TO "NEWS_OWNER.HEROLD.COM" IDENTIFIED BY "sid8" USING 'LOUISE_NEWS'

    I forget exactly which syntax it wants, but it is getting
    confused because of the domain name. Play around with it
    a bit and please post whichever one works when you find out
    so the rest of us can remember!

    -John

  3. #3
    Join Date
    Sep 2000
    Posts
    362
    Originally posted by jdorlon
    Try :

    CREATE PUBLIC DATABASE LINK "NEWS_DBLINK"
    CONNECT TO "NEWS_OWNER.WORLD" IDENTIFIED BY "sid8" USING 'LOUISE_NEWS'

    OR

    CREATE PUBLIC DATABASE LINK "NEWS_DBLINK"
    CONNECT TO "NEWS_OWNER.HEROLD.COM" IDENTIFIED BY "sid8" USING 'LOUISE_NEWS'

    I forget exactly which syntax it wants, but it is getting
    confused because of the domain name. Play around with it
    a bit and please post whichever one works when you find out
    so the rest of us can remember!

    -John
    Hi John,
    I tried the following 3 syntaxes but still it does not work

    CREATE PUBLIC DATABASE LINK "TEST" CONNECT TO "NEWS_OWNER"
    IDENTIFIED BY "sid8" USING 'LOUISE_NEWS' ;


    CREATE PUBLIC DATABASE LINK "TEST1" CONNECT TO "NEWS_OWNER.WORLD" IDENTIFIED BY "sid8" USING 'LOUISE_NEWS'

    CREATE PUBLIC DATABASE LINK "TEST2" CONNECT TO "NEWS_OWNER.HEROLD.COM" IDENTIFIED BY "sid8" USING 'LOUISE_NEWS'

    Also the db_domain parameter in both the databases is set to World.

    Also I noted one thing that I am able to create a db link from sid8 to sid7 like thes and it is working fine

    CREATE PUBLIC DATABASE LINK "TEST2" CONNECT TO "COMPANY" IDENTIFIED BY "company" USING 'LOUISE_COMPANY'

    why is it not allowing me to use the link from sid7 to sid8 then.

    Please suggest.

    Thanks a Lot
    Anurag

    [Edited by anuragmin on 07-23-2001 at 11:15 AM]
    Appreciation is a wonderful thing;
    It makes what is excellent in others belong to us as well.


  4. #4
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    what is after connect to is the username on the distant db, I guess you do not have a user that is called News_owner.herold.com !!!
    what you should do is :
    create database link ...
    connect to user
    identified by pwd
    using 'sid8.world'

  5. #5
    Join Date
    Sep 2000
    Posts
    362
    The user i am trying to connect to is called news_owner.

    I tried this also but it does not work.

    CREATE PUBLIC DATABASE LINK "TEST4" CONNECT TO "NEWS_OWNER"
    IDENTIFIED BY "sid8" USING 'SID8.WORLD'

    Actually its ok that it is not working because i have no service name defined as sid8.world. The service name I have defined on the server side tnsnames.ora is louise_news and even with that it does not work.


    Please Help.

    Thanks
    Anurag
    Appreciation is a wonderful thing;
    It makes what is excellent in others belong to us as well.


  6. #6
    Join Date
    Mar 2001
    Posts
    314
    Set your GLOBAL_NAMES init.ora parameter to false and it will work. You can also test it using "ALTER SESSION SET GLOBAL_NAMES=FALSE".

    -amar

  7. #7
    Join Date
    Nov 2000
    Posts
    344
    OK, sorry for my first incorrect post. I just tested it,
    and I can get it to work on my database like this

    Create Public Database Link SID8.world
    connect to news_owner
    Identified by sid8
    using 'SID8'
    /

    Then, when you use it, specify it like this :

    select * from dual@sid8.world;

    -John

  8. #8
    Join Date
    Sep 2000
    Posts
    362
    I tried using the exact same command also but it says

    SQL> select * from company@sid8.world;
    select * from company@sid8.world
    *
    ERROR at line 1:
    ORA-12154: TNS:could not resolve service name

    and thats right because the service name pointing to the database is louise_news and not sid8.

    btw which service names do the database links use...
    clients side or the servers side.

    Thanks
    Anurag
    Appreciation is a wonderful thing;
    It makes what is excellent in others belong to us as well.


  9. #9
    Join Date
    Sep 2000
    Posts
    362
    I created a link called sid8.world and provided the service name as louise_news and now it is giving me the following error.


    SQL> select * from company@sid8.world;
    select * from company@sid8.world
    *
    ERROR at line 1:
    ORA-12640: Authentication adapter initialization failed

    I dont know what settings i changed but i was playing around with sqlnet.ora to fix the link creation problem and now this error is coming.

    Please help me out with the db link problem as i cannot take help from the metalink site as it is down.

    Thanks
    Anurag
    Appreciation is a wonderful thing;
    It makes what is excellent in others belong to us as well.


  10. #10
    Join Date
    Feb 2001
    Posts
    203
    Try this.

    Create Public Database Link SID8.world
    connect to news_owner
    Identified by sid8
    using 'LOUISE_NEWS'
    /

    then select the values from company table.

    select * from company@sid8;
    sree

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