Re: db link help needed

From: <satar_at_my-dejanews.com>
Date: Wed, 14 Oct 1998 18:00:04 GMT
Message-ID: <702or4$p5k$1@nnrp1.dejanews.com>

I hope Oracle Corporation does not kill me for posting this...but here you go. Satar Naghshineh

Article-ID:         <Note:60234.1>
Circulation:        PUBLISHED (EXTERNAL)
Folder:             server.Gateway
Platform:           GENERIC  Generic issue
Topic:              ALL GATEWAYS
Subject:            GLOBAL_NAMES and ORA-2085
Modified-Date:      15-APR-1998 22:18:35
Document-Type:      BULLETIN
Content-Type:       TEXT/PLAIN
Impact:             MEDIUM


               ORA-2085 -- Oracle Gateways and Global Names
               ============================================

If you encounter the following error:

   SQL> select * from dept_at_db2400t;
   select * from dept_at_db2400t
   *
   ERROR at line 1:
   ORA-02085: database link DB2400T.WORLD connects to TGDB2400.WORLD

then you have a mismatch between the database link name and the gateway name, and GLOBAL_NAMES set to TRUE.

The quick fix is to change the GLOBAL_NAMES setting in the integrating kernel from TRUE to FALSE. You can override the setting at the session level, or in Oracle 7.3 and higher at the system level.

To override it at the session level use the following statement:

alter session set global_names=false;

The override is effective only in the session where it is entered and only for the duration of the session.

To override it at the system level use the following statement:

alter system set global_names=false;

This override is effective for all sessions until the database is restarted.

To make the change permanent you must edit the database init.ora file. It should include the following record (which is not case sensitive):

global_names=false

For operational reasons you may not want to change the system value of GLOBAL_NAMES from TRUE to FALSE. If only the gateway DB_NAME and database link names are different you could (drop the existing database link and) create a new database link with the matching name. Alternatively, you must change the values of DB_NAME and DB_DOMAIN in the gateway init.ora file. The gateway DB_NAME and the database link name must match, and the integrating kernel and gateway DB_DOMAIN names must match.

Example

The integrating kernel init.ora contains:

   ...
   db_domain=acme.com
   ...

The gateway init.ora contains:

   ...
   db_name=TG4STUFF
   ...

You try the following in SQL*Plus:

SQL> create database link gateway connect to userid identified by password

       using 'gateway';
  SQL> select * from emp_at_gateway;
  select * from emp_at_gateway
  *
  ERROR at line 1:
  ORA-02085: database link GATEWAY.ACME.COM connects to TG4STUFF.WORLD

To fix the mismatch edit the gateway init.ora file and make these changes:

   ...
   db_name=gateway
   db_domain=acme.com
   ...

(The values are not case sensitive)

Oracle Support Services.

In article <3623BC0E.A5C976C_at_arconsultinginc.com>, Aric Rosenbaum <aricr_at_arconsultinginc.com> wrote:
> I'm trying to setup a database link between two O7.3 servers by issuing
> the following:
>
> CREATE DATABASE LINK AspenOracle7 CONNECT TO scott IDENTIFIED BY
> tiger USING 'AspenOracle7';
>
> However, when I'm logged into another the "other" server and issue a
> query such as:
>
> SELECT * FROM scott.dept_at_aspenoracle7;
>
> I get the following error message:
>
> ORA-02085: database link ASPENORACLE7.WORLD connects to ORACLE.WORLD
>
> FYI - The two servers are named ASPEN and CHAMONIX w/ TNS names setup
> for AspenOracle7 and ChamonixOracle7. Both instances have a SID of
> ORCL.
>
> How can I get this to work? Thanks in advance.
>
> -- Aric
>
>

--
Oracle DBA/UNIX System Admin
Advanced Enterprise Solutions
(949) 756-0588
Oracle Re-Seller

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Oct 14 1998 - 13:00:04 CDT