Troubleshoot oracle database link errors

Always test these 'troubleshoot oracle database link errors' commands on a test server
before implementing them on a production server.

There are a number of errors that you can face when working with database links in Oracle.

Below are a list of some of the more common ones and suggestions on how to troubleshoot oracle database link errors .

Oracle10 Tip
ORA-02021
ORA-02080
ORA-02085
ORA-12154
ORA-12224
ORA-12305

Background

What happens during database link usage ?
Some information to help you troubleshoot oracle database link errors.

SQL> select * from dual@remotedb;

D
-
X

Oracle resolves the dblink name to a host name, using the value it can find in the local server's tnsnames.ora file.

It connects to this host's listener via TCP/IP (using whichever naming resolution is in place at OS level).

The remote listener for the relevant port resolves the SID or Service Name as defined in its listener.ora and completes the connection to the database.

How do you find or create the correct entry in tnsnames.ora ?

Find your server's tnsnames.ora, it will usually be in $ORACLE_HOME/network/admin.

Make sure you do not have a $TNS_ADMIN environment variable set, if you do, your tnsnames.ora will be in that directory.

Scan it and look for an alias which contains the hostname and database name of the remote database you want to access.

Test its validity by using sql*plus on your local server and connecting to that alias with the username and password you intend using in your database link.

Oracle10 Tip

The first tip does not have an error associated with it, but is just a tip:

If you have recently upgraded your database from Oracle9 to Oracle10 and your database links are no longer
working, simply recreate them as a first step to troubleshoot oracle database link errors .

ORA-02021: DDL operations are not allowed on a remote database

Self-explanatory, no need to troubleshoot oracle database link errors any further :

Do not try to run a DDL command through a database link.

SQL> alter table dept@remotedb add column 
     (manager varchar2(30));
alter table dept@remotedb add column 
     (manager varchar2(30))
                 *
ERROR at line 1:
ORA-02021: DDL operations are not allowed on 
a remote database

ORA-02080: database link is in use

This can come up when you want to close a db link.

Solution: commit or rollback the transaction, then close the database link.

SQL> alter session close database link remotedb;
ERROR:
ORA-02080: database link is in use


SQL> commit;

Commit complete.

SQL> alter session close database link remotedb;

Session altered.

ORA-02085 : database link xxx connects to yyy

If your local database's 'GLOBAL_NAMES' initialization parameter is set to true, you will receive the ORA-02085 error when USING the db link:

SQL> connect scott/tiger
Connected

SQL> create database link remotedb connect to scott 
     identified by tiger using 'otherremotedb';

Database link created.

SQL> select * from dual@remotedb;
select * from dual@remotedb
                   *
ERROR at line 1:
ORA-02085 : database link REMOTEDB connects to 
            OTHERREMOTEDB

Solution:
On your local database, connected as sysdba,
see what your parameter GLOBAL_NAMES is set to :

SQL> show parameter global_names

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



SQL> alter system set global_names=false 
     scope=both;

System altered.



SQL> connect scott/tiger

Connected.
SQL> select * from dual@remotedb;

D
-
X

Tip : if you cannot change your local database's GLOBAL_NAMES setting,
change the name of the dblink to the global_name of the remote database.

To find out what it is , log in to the remote database as sysdba:

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------
REMOTEDB

Another problem to look into when you troubleshoot oracle database link errors like ORA-02085
is a difference in domain names between your local database and the remote database.
This is only a problem if you do not specify the domain name in your db link name.

For example, The remote database's domain name is world.net and your local database's domain name is world.com

You create a db link as follows:

SQL> create database link remotedb 
      using 'remotedb';
Database link created.

SQL> select * from dual@remotedb;
ORA-2085 "database link REMOTEDB.WORLD.COM 
         connects to REMOTEDB.WORLD.NET"

To resolve this , drop the db link and recreate it by specifying the correct domain name:

SQL> drop database link remotedb;
Database link dropped.

SQL> create database link remotedb.world.net 
     using 'remotedb';
Database link created.

SQL> select * from dual@remotedb;

D
-
X

Alternatively you can change the global name of the remote database:

See what it is first:

SQL> connect / as sysdba
Connected

SQL> select * from global_name;

GLOBAL_NAME
------------
REMOTEDB



SQL>alter database rename global_name 
    to remotedb.world.com;
Database altered.

SQL> select * from global_name;

GLOBAL_NAME
-------------------
REMOTEB.WORLD.COM

Now you can create the db link on the local server without specifying the domain name:

SQL> drop database link remotedb;
Database link dropped.


SQL> create database link remotedb 
      using 'remotedb';
Database link created.

SQL> select * from dual@remotedb;

D
-
X

ORA-12154 : TNS:could not resolve service name

When you troubleshoot oracle database link errors like ORA-12154, you need to look into your networking configuration files.

Double-check the alias you are using in your database link against a functional one from the local server's tnsnames.ora.

Are you looking at the correct tnsnames.ora ?

If you have multiple Oracle Homes on your local server, you could be looking at the wrong one.

Also check for the existence of the $TNS_ADMIN environment variable, the correct tnsnames.ora file will be there, not where you expected it to be.

The best test is to copy and paste the alias from your tnsnames.ora file,
then do an sql*plus connection to it with the username and password you are using in the db link.

Once that is checked, retest your database link to see if the ORA-12154 has gone away.

ORA-12224 : TNS: no listener

Make sure the remote server's listener is running and listening on the correct port as shown in your local server's tnsnames.ora.
Fix what is wrong and retry the db link query to see if the ORA-12224 is resolved.

ORA-12305 : TNS:listener could not resolve SID given in connect descriptor

Make sure the remote server's listener is listening for the SID or service name as shown in your local server's tnsnames.ora.
To check:
Log onto the remote server and see what the listener is listening for : lsnrctl services or lsnrctl status