Create database link ORA-2085

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.

You need to set up tnsnames.ora to include domain name.

Please try some thing like:

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

Quick way to fix this problem is set GLOBAL NAMES = FALSE in init.ora

Mike73 has the point. But remember changing would require a bounce on the instance.

Sam

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>

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

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.

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

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

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.

Do you have your global_name turned off? What is the error message you are getting, post it including the error number....

Sam

I am sure you need change tnsnames to supply the "SERVICE_NAMES" even if currently you can tnsping ***.

Change global_names to false does the trick! However I should be able to name my link the same as SID with global_name equal true. Global_name basically forces your link name to be the same as your db or sid. Follow this rule should work also.

I reset global_name to true and it worked. However when I use DESC on a remote table: for exp; DESC PS_TAX@REMOTE_DB.WORLD, it came backup invalid connection string message. However doing SELECT on a remote table works fine. Why didn't DESC work, it used to work.

Quote:

---

Originally posted by goodhealth
Change global_names to false does the trick! However I should be able to name my link the same as SID with global_name equal true. Global_name basically forces your link name to be the same as your db or sid. Follow this rule should work also.

---

In OPS, you cannot name the DB link the same as the DB, for you have at leas 2 instances. Metalink has some wrong info on creating DB links in an Oracle Paralell Server enviroment in 8i. I remember it took me hours to figure out how to create my DB links :-)

See if this helps you ?

Found this in a Metalink article (DocID=1024124.6)

This is not documented in the Oracle manuals and can be useful (even required) when the init.ora parameter GLOBAL_NAMES is set to TRUE.....
Note that if GLOBAL_NAMES is set to FALSE, you can name the dblink anything you desire.

Database Links
--------------
Database links are created using the following syntax:

SQL> create database link
connect to identified by
using '';

The name of the database link should match the global name of the target
database if GLOBAL_NAMES=TRUE. This may seem restricting since then there can
be only one database link per schema to a given database if global_names is
set to true. To overcome this use database link qualifiers. For example:

SQL> create database link oradb@link1
using 'D:BOSTON-MFG';

NOTE: in this example, 'link1' is database link qualifier
and 'D:BOSTON-MFG' is the connect string

My NOTE : your select to the remote database would look like this :
select * from table_name@oradb@link1;

-Tom