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

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.

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

Recommended reading: