Database Link

How to Use GLOBAL_NAMES for Database Links

GLOBAL_NAMES

Normally, we turned off GLOBAL_NAMES to prevent additional check on remote database before connecting to it. This is because we know where we are going to very clearly.

But what if we are in a complicated distributed environment, how to prevent connecting to the wrong destination? In such situation, Oracle recommends to turn on GLOBAL_NAMES to prevent us from connecting to wrong databases.

In this post, I will show you how to implement database links when GLOBAL_NAMES parameter is enabled (TRUE) by the following steps:

1. Check Remote Global Name

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
BOSTONDB

It shows that the global name of remote database is BOSTONDB. Then we go back to the local database.

2. Test Connectivity to the Remote Database

[oracle@primary ~]$ tnsping BSTN
...
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BOSTONDB)))
OK (20 msec)

3. Check Current Setting of Local Database

SQL> conn / as sysdba
Connected.
SQL> show parameter global_names;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_names                         boolean     FALSE

4. Create a Database Link and Test It

SQL> conn hr/hr
Connected.
SQL> create database link bstn_hr connect to hr identified by hr using 'BSTN';

Database link created.

SQL> select sysdate from dual@bstn_hr;

SYSDATE
---------
01-JUL-19

5. Turn On GLOBAL_NAMES

SQL> conn / as sysdba
Connected.
SQL> alter system set global_names=TRUE scope=memory;

System altered.

SQL> show parameter global_names;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_names                         boolean     TRUE

Then we test the database link again.

SQL> conn hr/hr
Connected.
SQL> select sysdate from dual@bstn_hr;
select sysdate from dual@bstn_hr
                         *
ERROR at line 1:
ORA-02085: database link BSTN_HR connects to BOSTONDB

ORA-02085 told us that our database link is not qualified. More specifically, it’s the name of database link not qualified. This is because GLOBAL_NAMES requires the name of database link to be identical to the global name of remote database.

6. Create a Database Link and Test It

We have to create a database link with the same name as the global name of remote database.

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

Database link created.

SQL> select sysdate from dual@BOSTONDB;

SYSDATE
---------
01-JUL-19

In short, to use GLOBAL_NAMES in a distributed environment, the name of database link must be the same as the global name of remote database. Which is:

Database Link Name = Remote Global Name

Leave a Reply

Your email address will not be published. Required fields are marked *