Troubleshooting Database Links

Each Oracle DBA knowns this story of course ... sorry, but not every Oracle user is a DBA. One early morning your management staff complains about missing sales data, which must be ready for the daily trend analysis. What's happen ... nobody changed the calculation procedure during the night ... here it is ... the database link is down. Why ... well the remote DBA changed all passwords. This is only one of the famous database link trobleshooting stories. Read the following article and you have less annoyance with database links.

Oracle enforces the requirement that the database.domain portion of the database link name must match the complete global name of the remote database by setting GLOBAL_NAMES to TRUE in the initialization parameter file initSID.ora.

Example: Local DB is 'SOL3' (Oracle 8.1.6), remote DB is 'SOL1' (Oracle 7.3.4)

# Parameter file initSOL3.ora for Database SOL3
#
### Global Naming
### -------------
# Enforce that a dblink has same name as the DB it connects to

global_names = TRUE

# Parameter file initSOL1.ora for Database SOL1
#
### Global database name is db_name.db_domain
### -----------------------------------------

db_name = SOL1
db_domain = world

Our database link points from the local database SOL3 to the remote database SOL1. Therefore we need the global database name for SOL1. Ask the remote database administrator for these information or connect to SOL1 and execute the following query on SOL1:

SQL> select GLOBAL_NAME from GLOBAL_NAME;

GLOBAL_NAME
-----------
SOL1.WORLD

We found the database link name 'SOL1.WORLD' for our local database SOL3. Now connect to the local database database SOL3 as a user, who has the privilege to create a database link and create the following named database link to SOL1.

$ sqlplus jones/lion@SOL3

SQL> CREATE DATABASE LINK sol1.world
CONNECT TO scott IDENTIFIED BY tiger using 'SOL1';

With this DB-Link, you (jones/lion) can connect to the remote database SOL1 as user scott/tiger. This user must exist on the remote database SOL1. Test the database link as user jones/lion from SOL3.

SQL> SELECT * FROM emp@SOL1.WORLD;

You may ask, what's this strange 'SOL1' in ..... using 'SOL1' means ?. Well this is the so called connect_string (or net_connect_string in Oracle8i). This string has nothing common with the DB-Link name, but very often the same name is used. The connect string must be defined in the Net8 configuration file TNSNAMES.ORA, if you don't use Oracle Names.

#
# TNSNAMES.ORA for SOL3 ###############################
#
SOL1.world = (DESCRIPTION = (ADDRESS = (COMMUNITY = tcp.world)
             (PROTOCOL = TCP) (Host = saturn) (Port = 1521))
             (CONNECT_DATA = (SID = SOL1) (GLOBAL_NAME = SOL1.world)
             (SERVER = DEDICATED)))

Well, now you understand our short story at the beginning of this article. If the DBA on SOL1 changes scott's password to snake, we have the disaster with our missing sales data ... poor management.

Oracle allows three kinds of external references to DB-links, which are resolved as follows:

Named Link: The username specified in the link is used. You specify the username and password used to connect to the remote database (this database link is sometimes called fixed user database link).

CREATE DATABASE LINK sol1.world
CONNECT TO scott IDENTIFIED BY tiger USING 'sol1';

Anonymous Link: The session username is used. If you omit the CONNECT TO clause, the database link uses the username and password of each user who is connected to the database (this database link is sometimes called connected user database link).

CREATE DATABASE LINK sol1.world USING 'sol1';

Privileged Link: The username of the invoker is used. The current user must be a 'global' user with a valid account on the remote database for the link to succeed. If the database link is used directly, that is, not from within a stored object, then the current user is the same as the connected user.

When executing a stored object (such as a procedure, view, or trigger) that initiates a database link, CURRENT_USER is the username that owns the stored object, and not the username that called the object. For example, if the database link appears inside procedure SCOTT.show_emp (created by SCOTT), and user JONES calls procedure SCOTT.show_emp, the current user is SCOTT.

However, if the stored object is an invoker-rights function, procedure, or package (new in Oracle8i), the invoker's authorization ID is used to connect as a remote user. For example, if the privileged database link appears inside procedure SCOTT.show_emp (an invoker-rights procedure created by SCOTT), and user JONES calls procedure SCOTT.show_emp, then CURRENT_USER is JONES and the procedure executes with JONES's privileges. For more information on invoker-rights functions click here

CREATE DATABASE LINK sol1.world
CONNECT TO CURRENT_USER USING 'sol1';

Besides these often used DB-Links, you can create a database link as PUBLIC. Be very careful with PUBLIC database links, they may open a door for everybody to a remote database. We suggest, NOT TO USE public database links without Authentication.

A shared PUPLIC DB-Link with Authentication uses a single network connection to create a PUBLIC database link that can be shared between multiple users with more security. This DB-Link is available only with the multi-threaded server configuration.

Example

SQL> CREATE SHARED PUBLIC DATABASE LINK sol1.world
     CONNECT TO scott IDENTIFIED BY tiger
     AUTHENTICATED BY jones IDENTIFIED BY lion
     USING 'sol1';

The AUTHENTICATED clause specifies the username and password (JONES/LION) on the target instance (SOL1). This clause authenticates the user to the remote server and is required for security. The specified username and password (JONES/LION) must be a valid username and password on the remote instance (SOL1). The username and password are used only for authentication. No other operations are performed on behalf of this user.