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:
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.
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)
Check Current Setting of Local Database
SQL> conn / as sysdba
Connected.
SQL> show parameter global_names;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE
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.
Since the parameter GLOBAL_NAMES is turned off (FALSE) in the local database, so we don't need to use remote global_name to name the DB link.
Let's do a connection test via the new DB link.
SQL> select sysdate from dual@bstn_hr;
SYSDATE
---------
01-JUL-19
Turn On GLOBAL_NAMES
A database with GLOBAL_NAMES turned on will make the database link stricter than off. Let's see what will happen when GLOBAL_NAMES is turned on.
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.
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:
That's why I said, it's stricter than normal when GLOBAL_NAMES is TRUE (turned on).