Global_Names and ORA-02085

This was tested on Oracle 9. I created 2 database links to the same target database:

 

SQL> conn / as sysdba

Connected.

SQL> create database link any_name.world

  2  connect to link_schema

  3  identified by link_schema

  4  using 'REMOTEDB.WORLD'

  5  /

 

Database link created.

 

SQL> create database link remotedb.world

  2  connect to link_schema

  3  identified by link_schema

  4  using 'REMOTEDB.WORLD'

  5  /

 

Database link created.

 

SQL>

 

I checked the value of the source database’s global_names parameter:

 

SQL> select value from v$parameter

  2  where name = 'global_names'

  3  /

 

VALUE

----------

FALSE

 

SQL>

 

I tested both links and they worked OK:

 

SQL> select sysdate from dual@any_name.world

  2  /

 

SYSDATE

---------

26-APR-12

 

SQL> select sysdate from dual@remotedb.world

  2  /

 

SYSDATE

---------

26-APR-12

 

SQL>

 

I changed global_names to true in the source database:

 

SQL> alter system set global_names = true

  2  /

 

System altered.

 

SQL> select value from v$parameter

  2  where name = 'global_names'

  3  /

 

VALUE

----------

TRUE

 

SQL>

 

This stopped the any_name.world link working as the link name in the source database and the global_name of the target database must match if global_names is set to true in the source database:

 

SQL> select sysdate from dual@any_name.world

  2  /

select sysdate from dual@any_name.world

                         *

ERROR at line 1:

ORA-02085: database link ANY_NAME.WORLD connects to

REMOTEDB.WORLD

 

SQL>

 

But the remotedb.world link still worked:

 

SQL> select sysdate from dual@remotedb.world

  2  /

 

SYSDATE

---------

26-APR-12

 

SQL>

 

How do I know that the check is made against the target database’s global name? I connected to the target database and changed its global name to any_name.world:

 

SQL> conn /@remotedb.world

Connected.

SQL> alter database rename global_name

  2  to any_name.world

  3  /

 

Database altered.

 

I reconnected to the source database: 

SQL> conn / as sysdba

Connected.

SQL>

 

Then the any_name.world link worked:

 

SQL> select sysdate from dual@any_name.world

  2  /

 

SYSDATE

---------

26-APR-12

 

SQL>

 

But the remotedb.world link didn’t:

 

SQL> select sysdate from dual@remotedb.world

  2  /

select sysdate from dual@remotedb.world

                         *

ERROR at line 1:

ORA-02085: database link REMOTEDB.WORLD connects to

ANY_NAME.WORLD

 

SQL>

ORA-02019

I had not seen this problem for a long time so, when it happened again recently, it took me a while to see the cause. I therefore decided to record it for future reference. First I created a database link:

ORCL /export/home/oracle/andrew > sqlplus /

SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 17 11:55:54 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create database link link1.world

  2  connect to andrew

  3  identified by reid

  4  using 'REMOTEDB'
  5  /

Database link created.

SQL>

… but when I tried to use the link, I got an ORA-02019:

SQL> select * from dual@link1

  2  /

select * from dual@link1

                   *

ERROR at line 1:

ORA-02019: connection description for remote database not found

SQL>

I checked the database’s GLOBAL_NAME:

SQL> l

  1* select * from global_name

SQL> /

GLOBAL_NAME

------------------------------

ORCL

SQL>

I changed it as follows:

SQL> alter database rename global_name to ORCL.WORLD

  2  /

Database altered.

SQL> select * from global_name

  2  /

GLOBAL_NAME

------------------------------

ORCL.WORLD

SQL>

… and the database link worked:

SQL> select * from dual@link1

  2  /

D

-

X

SQL>