Database Links with Global Names common errors

October 30, 2008  |  dba

In a distributed database system, each database must have a unique name… a global database name. Global database names uniquely identify a database in the system.

To query a database’s global name issue this instruction:

SQL> SELECT * FROM global_name;

GLOBAL_NAME
-----------
db1.world
.

A global database name is formed by two components: a database name and a domain, as I said before, uniquely identifies it from any other database. An example global name might be mydb.beyondoracle.com. There are 2 parameters that define the global database name. The DB_NAME (no more than 8 chars) and the DB_DOMAIN (Must follow Internet conventions).

To change our database global name, do this:

SQL> ALTER DATABASE RENAME GLOBAL_NAME TO db1.beyondoracle.com

When we change / set the database to use global names (GLOBAL_NAMES parameter set to TRUE), all database links must be valid global names. When you create a database link with global names set to true, the database link will be formed by the database name plus the domain. In this environment Oracle enforces the requirement that the database.domain  portion of the database link name must match the complete global name of the remote database. For instance, if your remote database is called db1.beyondoracle.com then the database link you create to access this remote database, must have the same name… db1.beyondoracle.com!

When using or enabling global names, when we didn’t create the database link with the previous rules I talked about (db_name.db_domain), it’s common getting this error: ORA-02085: database link string connects to string.

When you create a database link we must mention the connect_string that you want to connect to. This connect_string is the tnsnames entry that you have on your database server tnsnames.ora file. For instance when you create this db link:

SQL> CREATE DATABASE LINK db1.beyondoracle.com
CONNECT TO myuser IDENTIFIED BY mypass USING ‘remotedb’;

Database Link Created

Then you must ensure that your database tnsnames.ora has the entry remotedb such as:

# connect string for database
remotedb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.beyondoracle.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = db1.beyondoracle.com)
   )
  )
# end of connect string

This tnsnames.ora connect string (remotedb) has nothing common with the database link name, but very often the same name is used. You can call your connect string anything you want!

Anytime you can check your database links by querying user_db_links or all_db_links.

SQL> SELECT * FROM user_db_links;

DB_LINK              USERNAME  PASSWORD  HOST                 CREATED
-----------          --------  --------  -------              -------------
db1.beyondoracle.com myuser              db1.beyondoracle.com 15-09-2008 15:22:56
.

To finish, remember that almost every oracle distributed environment using replication uses global names. There’s only some custom replication processes that don’t need it, but Oracle Replication technology, like Streams for instance, needs global names database compliance.

My prayers and thoughts go to Carl Backstrom and his family. Wherever you are, rest in peace!


1 Comment


  1. But, when you want to create a DBLINK in a global name enabled DB which connects to a global name disabled DB?
    e.g.
    DB-1 (global name enabled: DB-1.DOMAIN.COM)
    DB-2 (global name disabled.)

    And want to create a DBLINK in DB-1 to DB-2.
    How you do that?

    Since the .domain append is mandatory, I’m unable to create a DBLINK with no domainame.
    And if I create one, it returns the ORA-02085 error like:
    in DB-1:
    SQL> create database link DB-2 connect to myuser identified by mypassword using ‘DB-2’;

    Database link created.

    SQL> select * from dual@DB-2;
    ORA-02085: database link DB-2.DOMAIN.COM connects to DB-2

Leave a Reply