Using Oracle Global Database Links

by Jonathan Gennick
12/13/2000

Database links are used in distributed Oracle environments to define a communications path between two databases. A database link defines a network connection, and, optionally, a username, to use in contacting a remote database. The purpose of using a link is to isolate SQL statements from the underlying, physical network topology. If the location of a remote database changes, only the link need be updated.

A database link is usually defined in a specific database, and may be used only by users of that database. If you want to use the same database link name from all databases in your network, you must identically define links with that name in each of your databases. As you might imagine, as the number of links increases, this can become a maintenance nightmare. Enter the global database link. A global database link is one that you define in a central location, and is used from all databases.

Recently, I decided to do a little research into global database links and how they are created. First, I found that global database links can only be defined in Oracle Names (DNS-like name resolution software), so if you're not an Oracle Names user, you can't take advantage of them. I do use Oracle Names, so I was OK on that point. The second thing I discovered was that Oracle's online documentation (You've got to be an Oracle Technology Network member to use it.) is frustratingly vague on how to actually define a global database link, and also on how to configure your environment to enable them to be used. Here's a quick summary of what I discovered after some research:

Global database link names must match database global names
Your GLOBAL_NAMES initialization parameter should be set to TRUE
You must enable the plug and play setting for your listeners

Database Global Names

Every Oracle database has, or should have, a global name that is unique on your network. The global name is a combination of the database name and domain. You define the global name when you first create a database by placing parameters in your initialization file that specify those two values. For example:

db_name = herman
db_domain = gennick.org

Once a database has been created, the global name is stored within the control file, and the parameter values in the initialization file no longer matter. To change a global name for an existing database, you must use the ALTER DATABASE RENAME GLOBAL_NAME command.

The GLOBAL_NAMES Parameter

You used to be able to dream up any name for a database link, but for the past several years Oracle has strongly recommended that database link names match the global names of the target databases. Thus, if you were going to create a link to the database named herman.gennick.org, you would name the link herman.gennick.org as well.

Oracle feels strongly enough about this naming convention that they even provide an enforcement mechanism for you to use. By adding the following parameter setting to all your parameter files, you effectively prevent anyone from using a database link with a name that violates this naming convention:

GLOBAL_NAMES = TRUE

It turns out that this has ramifications for global database links. Oracle Names doesn't even appear to support the creation of a global database link with a name that doesn't match the target database's global name. So if you are using Oracle Names, you must follow the preferred naming convention.

Listener Plug and Play

Listener plug and play is an Oracle feature that causes the Net8 listener and Oracle Names to automatically find each other and communicate information about registered databases. You enable listener plug and play by placing the following entry in your listener.ora file:

USE_PLUG_AND_PLAY_LISTENER = ON

The important thing to know about this setting with respect to global database links is that it causes the Net8 listener to register global database names with Oracle Names servers. Any databases that you register with the listener are now also automatically registered with Oracle Names.

Defining a Global Database Link

After I properly configured my environment, it was time to create a global database link and test it out. To create the link, I wanted to use the Names Control command-line utility. Unfortunately, the documentation for the REGISTER command did not explain how to register a database link. Even a search on Metalink did not turn up any help in this regard. Giving up on the command-line approach, I turned to the Net8 Assistant--a GUI utility that, among other things, allows you to manage Oracle Names. Figure 1 shows the global database link named donna.gennick.org being defined:

The really odd thing about defining a global database link, at least using Net8 Assistant, is that I was allowed to only define the link name. Usually, you define a database link in terms of both a link name and a Net service name that points to the destination database. The fact that I could only define the link name meant that Oracle Names must be making some assumptions about the target database. Oracle Names must assume that the link name matches a global database name that has been registered by the Net8 listener. In my case, this was as a result of my enabling the USE_PLUG_AND_PLAY option. This underscores the importance of following Oracle's preferred naming convention, and also of enabling plug and play for your listener.

Testing a Global Database Link

My final task was to test the new link that I had created. It did work, as the following example shows:

SQL> SELECT * FROM dual@donna.gennick.org;

D
-
X

Not only did my new link work, but it worked from all my databases. By using Oracle Names to create a global database link, I was able to define the link just once. No longer do I need to define the same link multiple times in each database. I can now define global database links centrally in Oracle Names, and they will immediately be available across my network.

Jonathan Gennick is a writer and an editor at O'Reilly & Associates. His writing career began in 1997, when he coauthored Teach Yourself PL/SQL in 21 Days (Sams). Since then, he has written Oracle SQL*Plus: The Definitive Guide (1998) and Oracle SQL*Plus Pocket Reference (2000), both for O'Reilly & Associates. In his previous life, Jonathan was a manager and a database administrator in KPMG Consulting's Public Services practice. He has more than a decade of experience with relational databases.

O'Reilly & Associates will soon release (December 2000) Oracle Net8 Configuration and Troubleshooting.

Sample Chapter 1, "Oracle's Network Architecture and Products," is available free online.
You can also look at the Table of Contents, the Index, and the Full Description of the book.
For more information, or to order the book, click here.

Return to: oracle.oreilly.com