I have a laptop with Oracle 8.1.6 server and client installed on it, and I have one instance on the laptop called tst.
On our network, we have an AIX machine running Oracle 8.1.6 with several instances installed, one of which is prod.
With my laptop attached to the network, I can connect through SQL*PLUS, DBA Studio, Toad, etc. to my local instance (tst) and the AIX instances (prod). For example, right now, I have the local instance and two network instances open in Toad.
However, when I create a database link on my local instance to one of the network instances, using
create database link prod connect to
identified by using 'PROD.WORLD'
trying to access that link results in an Oracle error. The error says "ORA-02085: database link PROD connects to PROD.ABC.COM".
The prod above is *exactly* what it is in the TNSNAMES.ORA, and it is what I use to connect through SQL*PLUS, DBA Studio, etc. to the instance. The SQLNET.ORA has a
table.default_domain = 'WORLD'
in it. The TNSNAMES.ORA for the database is
(ADDRESS = (PROTOCOL = TCP)(HOST = a.b.c.d)(PORT = 1521))
(CONNECT DATA =
(SID = PROD)
(SERVER = DEDICATED)
The entry for tst looks identical, except the host address is my local machine name and the SID is tst. I can tnsping all databases fine. I have tried with this with several instances on the AIX machine, and none of them will work, even though I can connect to all of them.
Why would I be able to connect to the database, but be able to link to it? What else can I look at?
I suggest you to name the db link as 'PROD.WORLD' if not
set global_names to false:
ALTER SESSION SET global_names=FALSE;
On your PROD database, isue:
SELECT * FROM global_name;
I belive you'll get the following answer: ''PROD.ABC.COM".
So you have the folowing options:
1. Name your database link PROD.ABC.COM (Note: this should be the *name* of your database link, your connect string in "CREATE DATABASE LINK PROD.ABC.COM ... USING 'PROD.WORLD'; can remain uncahnged)
2. Set GLOBAL_NAMES=FALSE on your PROD database.
3. Change the global name of your PROD database to PROD.WORLD by using "ALTER DATABASE RENAME GLOBAL_NAME ..."
Try this Action
02085, 00000, "database link %s connects to %s"
// *Cause: a database link connected to a database with a different name.
// The connection is rejected.
// *Action: create a database link with the same name as the database it
// connects to, or set global_names=false.