ORA-02019: Connection Description Not Found - Fix DB Links

ORA-02019: Connection Description for Remote Database Not Found

Error Overview

Error Text: ORA-02019: connection description for remote database not found

ORA-02019 occurs when Oracle attempts to use a database link and cannot find or resolve the connection descriptor for the remote database. The error fires at the moment a SQL statement or PL/SQL block first uses the database link — not when the link is created. A database link can be created successfully with a completely invalid connection description; ORA-02019 only surfaces at usage time.

The error is functionally similar to ORA-12154 (“TNS: could not resolve the connect identifier specified”) but is specific to the database link resolution path, which looks up connection information differently from a client connecting directly. The Oracle server process must resolve the service name using the server-side tnsnames.ora, LDAP directory, or the connect string embedded in the link definition.

Common Causes

1. Database Link References a Service Name Not in Server tnsnames.ora

• The CONNECT TO ... USING 'service_name' clause names a service not defined on the database server
• Client-side tnsnames.ora has the entry but the server-side file does not
• Link was created in development where TNS was configured; production server lacks the entry

2. TNS_ADMIN Environment Variable Points to Wrong Directory

• Oracle server process uses a different tnsnames.ora than expected
• TNS_ADMIN set to a directory where the file exists but is outdated or missing the entry
• Multiple Oracle homes exist; the wrong home’s tnsnames.ora is being used

3. Database Link Created With an Inline Connect String That Is Wrong

• USING clause contains a misspelled host, port, or service name
• Inline connect string syntax error (missing parenthesis, wrong keyword)
• Link created with an Easy Connect string (host:port/service) where the target rejects it

4. LDAP/OID Resolution Failure

• sqlnet.ora specifies NAMES.DIRECTORY_PATH = (LDAP, TNSNAMES) but LDAP is unreachable
• The service name is registered in LDAP but the LDAP server is down or misconfigured
• ldap.ora points to a wrong LDAP host or port

5. Database Link Definition Itself Is Corrupt or Stale

• Link was created pointing to an old database that no longer exists or has been renamed
• Service name was valid previously but the remote database’s service name changed
• Link definition in SYS.LINK$ contains a garbled or truncated connect string

Diagnostic Queries

Inspect the Database Link Definition

-- View all database links accessible to current user:
SELECT
  owner,
  db_link,
  username,
  host,
  TO_CHAR(created, 'YYYY-MM-DD HH24:MI:SS') AS created
FROM dba_db_links
ORDER BY owner, db_link;

-- Examine the raw link definition in SYS.LINK$:
SELECT
  name                AS link_name,
  userid              AS link_user,
  host                AS connect_string,
  TO_CHAR(ctime, 'YYYY-MM-DD HH24:MI:SS') AS created
FROM sys.link$
ORDER BY name;

-- Full detail on a specific link:
SELECT
  owner,
  db_link,
  username,
  host
FROM dba_db_links
WHERE db_link = UPPER('&link_name');

Check TNS Configuration on the Server

-- Confirm TNS_ADMIN parameter (if set in init.ora / spfile):
SELECT name, value FROM v$parameter WHERE name = 'tns_admin';

-- Confirm Oracle Net files location via environment:
-- (Run at OS level from oracle user)
-- echo $TNS_ADMIN
-- echo $ORACLE_HOME/network/admin/tnsnames.ora

-- Check sqlnet.ora NAMES.DIRECTORY_PATH:
-- (Contents of $TNS_ADMIN/sqlnet.ora or $ORACLE_HOME/network/admin/sqlnet.ora)
-- Should typically contain: NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Test Connectivity From the Database Server

-- If tnsping is available on the server, run from OS as oracle user:
-- tnsping <service_name_used_in_db_link>

-- Test the link after fixing TNS:
SELECT * FROM dual@&link_name;

-- If the link uses a fixed connect string, check it:
SELECT host FROM dba_db_links WHERE db_link = UPPER('&link_name');
-- Then manually tnsping or connect using that string.

Identify All Objects Depending on the Failing Link

-- Find all stored procedures/views/packages using this database link:
SELECT
  owner,
  name,
  type,
  referenced_owner,
  referenced_name
FROM dba_dependencies
WHERE referenced_name = UPPER('&link_name')
   OR referenced_name LIKE '%@' || UPPER('&link_name')
ORDER BY owner, type, name;

-- Find synonyms pointing to the link:
SELECT owner, synonym_name, table_owner, table_name, db_link
FROM dba_synonyms
WHERE db_link = UPPER('&link_name')
ORDER BY owner, synonym_name;

Step-by-Step Resolution

1. Confirm the Exact Service Name the Link Is Using

SELECT host FROM dba_db_links WHERE db_link = UPPER('&link_name');

The HOST column contains the service name or inline connect descriptor. Note the exact value.

2. Verify the Entry Exists in Server-Side tnsnames.ora

# On the database server as the oracle OS user:
cat $ORACLE_HOME/network/admin/tnsnames.ora | grep -A 10 -i "<service_name>"
# or if TNS_ADMIN is set:
cat $TNS_ADMIN/tnsnames.ora | grep -A 10 -i "<service_name>"

# Test resolution:
tnsping <service_name>

If the entry is missing, add it to tnsnames.ora. Example entry:

REMOTEPROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = remote-db-host)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = remoteprod.example.com)
    )
  )

3. Drop and Recreate the Database Link With Correct Information

-- Drop the old link:
DROP DATABASE LINK &link_name;
-- Or for a public link:
DROP PUBLIC DATABASE LINK &link_name;

-- Recreate with correct service name (from tnsnames.ora):
CREATE DATABASE LINK &link_name
  CONNECT TO remote_user IDENTIFIED BY password
  USING 'REMOTEPROD';

-- Or with an inline connect descriptor (no tnsnames.ora required):
CREATE DATABASE LINK &link_name
  CONNECT TO remote_user IDENTIFIED BY password
  USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=remote-host)(PORT=1521))
          (CONNECT_DATA=(SERVICE_NAME=remoteprod.example.com)))';

4. Fix TNS_ADMIN if the Wrong tnsnames.ora Is Being Used

# Set TNS_ADMIN to the correct directory:
# In the Oracle user profile (~/.bash_profile or ~/.bashrc):
export TNS_ADMIN=/u01/app/oracle/product/19.0.0/dbhome_1/network/admin

# Apply immediately:
source ~/.bash_profile

-- Or set it as an Oracle parameter (12c+):
ALTER SYSTEM SET tns_admin = '/u01/app/oracle/product/19.0.0/dbhome_1/network/admin'
  SCOPE=BOTH;

5. Fix sqlnet.ora NAMES.DIRECTORY_PATH If LDAP Is Failing

# Edit $TNS_ADMIN/sqlnet.ora
# Change or add:
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
# Remove LDAP if LDAP is not available, or fix ldap.ora if it should be used.

6. Test the Fixed Link

-- Basic connectivity test:
SELECT * FROM dual@&link_name;

-- If the link connects to a specific schema, test a simple query:
SELECT COUNT(*) FROM remote_table@&link_name;

7. Flush Shared Pool If Old Link State Is Cached

-- Oracle may cache failed link state. Flush to force re-resolution:
ALTER SYSTEM FLUSH SHARED_POOL;

-- Then retry:
SELECT * FROM dual@&link_name;

Prevention Strategies

1. Test Database Links Immediately After Creation

-- Always run a test query after creating a link:
CREATE DATABASE LINK new_link
  CONNECT TO remote_user IDENTIFIED BY password
  USING 'REMOTE_SVC';

-- Immediately verify:
SELECT * FROM dual@new_link;

2. Document Database Links and Their Dependencies

-- Generate a link inventory report:
SELECT
  l.owner,
  l.db_link,
  l.username,
  l.host,
  l.created,
  COUNT(d.name) AS dependent_objects
FROM dba_db_links l
LEFT JOIN dba_dependencies d ON d.referenced_name = l.db_link
GROUP BY l.owner, l.db_link, l.username, l.host, l.created
ORDER BY l.owner, l.db_link;

3. Use Oracle Wallet for Link Passwords Instead of Plaintext

-- Create a link using Oracle Wallet (password stored securely):
-- First add the credential to the wallet, then:
CREATE DATABASE LINK secure_link
  CONNECT TO remote_user IDENTIFIED BY EXTERNAL
  USING 'REMOTE_SVC';

4. Monitor Link Failures in the Alert Log

-- Look for recurring ORA-02019 patterns:
SELECT originating_timestamp, message_text
FROM x$dbgalertext
WHERE message_text LIKE '%ORA-02019%'
ORDER BY originating_timestamp DESC
FETCH FIRST 20 ROWS ONLY;

Related Errors

• ORA-12154 - TNS could not resolve connect identifier
• ORA-02063 - Errors from remote database link
• ORA-12541 - TNS no listener
• ORA-12560 - TNS protocol adapter error

Emergency Response

Quick Fix Steps

-- 1. Check the link definition:
SELECT host FROM dba_db_links WHERE db_link = UPPER('&link_name');

-- 2. Drop and recreate with corrected info:
DROP DATABASE LINK &link_name;
CREATE DATABASE LINK &link_name
  CONNECT TO remote_user IDENTIFIED BY password
  USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=correct-host)(PORT=1521))
          (CONNECT_DATA=(SERVICE_NAME=correct_service)))';

-- 3. Test:
SELECT * FROM dual@&link_name;

If tnsnames.ora Cannot Be Modified

-- Use an inline full connect descriptor in the link definition to bypass tnsnames.ora:
DROP DATABASE LINK &link_name;
CREATE DATABASE LINK &link_name
  CONNECT TO remote_user IDENTIFIED BY password
  USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost.example.com)(PORT=1521))
          (CONNECT_DATA=(SERVICE_NAME=remote.example.com)))';
SELECT * FROM dual@&link_name;