Create database link ORA-2085
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Create database link ORA-2085

  1. #1
    Join Date
    Dec 2000
    Posts
    75
    I created a link from 817 to 805. The remote global_name has db_domain as WORLD;however, the local one(v. 817) db_domain is null. tnsping works fine but the link failed to do a simple desc on a table. Global_name is set to TRUE on both databases. The remote tnsnames entry is

    PSTEST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL= TCP)(Host= estwhp02)(Port= 1521))
    (CONNECT_DATA = (SID = PSTEST))
    )




    On the local, run the followings.

    1 create database link PSTEST
    2 CONNECT TO SYSADM IDENTIFIED BY PEANUT
    3* USING 'PSTEST'
    SQL_PSDEV8>/

    Database link created.

    SQL_PSDEV8>select * from global_name@PSTEST;
    select * from global_name@PSTEST
    *
    ERROR at line 1:
    ORA-02085: database link PSTEST connects to PSTEST.WORLD

    Any good ideas.
    Thx.



    goodhealth

  2. #2
    Join Date
    Jul 2000
    Posts
    68
    You need to set up tnsnames.ora to include domain name.

    Please try some thing like:

    (CONNECT_DATA = (SID = PSTEST)
    (SERVICE_NAME = PSTEST.WORLD) )


  3. #3
    Join Date
    Aug 2001
    Posts
    391
    Quick way to fix this problem is set GLOBAL NAMES = FALSE in init.ora

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Mike73 has the point. But remember changing would require a bounce on the instance.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  5. #5
    Join Date
    Dec 2000
    Posts
    75
    Before making new changes, I did some connection testing to the remote database such as sqlplus connection and it seemed to work fine. I don't think we need to change anything in tnsnames.ora on the remote database. Are there any other verifications we can do to clarify the problem? It seems like Oracle database link process got confused.



    On local host, PSDEV8:

    $ sqlplus sysadm/peanut@pstest

    SQL*Plus: Release 8.1.7.0.0 - Production on Tue Aug 28 15:14:40 2001

    (c) Copyright 2000 Oracle Corporation. All rights reserved.


    Connected to:
    Oracle8 Enterprise Edition Release 8.0.5.2.2 - Production
    With the Partitioning and Objects options
    PL/SQL Release 8.0.5.2.1 - Production


    SQL_PSTEST>
    goodhealth

  6. #6
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    You can do a tnsping to check that you are able to reach the remote database. But what you have confirmed is good enough to go.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  7. #7
    Join Date
    Dec 2000
    Posts
    75
    I aleady did tnsping and it worked fine.

    I also named my link the same as GLOBAL_NAMES but it still didn't work. I am not sure changing GLOBAL NAME is false will help. But I'll play with it.
    goodhealth

  8. #8
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    You would want to set the GLOBAL_NAMES=False, if you are giving your link a name. On the other hand GLOBAL_NAMES=TRUE would require the link name to be the same as the DB name for which the link was created.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  9. #9
    Join Date
    Oct 2000
    Location
    Halifax, Nova Scotia
    Posts
    197
    When you say remote tnsnames.ora do you mean the database you are connecting to with the DB link or the database you are connecting from

    If it is the database you are trying to connect to having the tnsnames entry won't help, you need it on the server you are connecting from.

    EX.
    DB1 on server1 has a db link that connects to DB2 on server2

    The tnsnames entry for DB2 has to be in server1's tnsnames.ora file

    A good test is to add the entry and log onto the server. Once on the server try to connect using SQL plus to the database you want the db link to connect to.

    Hope this helps
    Don't be afraid to try something new. Amateurs built the Ark, professionals built the Titanic

  10. #10
    Join Date
    Dec 2000
    Posts
    75
    I did created a entry for the remote db, PSTEST, on my local machine. Like I said earlier, I did sqlplus sysadm/sysadm@pstest and it worked fine. tnsping pstest worked. I tried naming my link as both PSTEST and PSTEST.WORLD on different time but it still failed. This shouldn't be that difficult to do but maybe I missed something trivial. I have created lots of links in the past but haven't come across this one.

    goodhealth

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