DEV Community

umzzil nng
umzzil nng

Posted on Originally published at oraerror.com

Oracle ORA-12154 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-12154: TNS Could Not Resolve the Connect Identifier Specified

ORA-12154 is one of the most common Oracle connectivity errors, occurring when the Oracle client cannot find or resolve the TNS alias (connect identifier) you specified in your connection string. This typically means Oracle cannot locate the alias in tnsnames.ora, cannot find the file itself, or the naming method is misconfigured. After 30 years as an Oracle DBA, I can say this error is almost always a configuration issue, not a database problem.

Top 3 Causes

1. Missing or Incorrect Entry in tnsnames.ora

The most frequent cause: the TNS alias doesn't exist in tnsnames.ora, or the file isn't where Oracle expects it.

-- Verify Oracle is reading the correct tnsnames.ora location
-- Run this inside the database to check environment
SELECT name, value
FROM v$parameter
WHERE name IN ('service_names', 'db_name', 'instance_name');
Enter fullscreen mode Exit fullscreen mode 
# Check which tnsnames.ora Oracle is using
echo $TNS_ADMIN
echo $ORACLE_HOME
tnsping MYDB   # Quick validation after any change
Enter fullscreen mode Exit fullscreen mode

A correct tnsnames.ora entry looks like this:

-- tnsnames.ora correct format
MYDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db-host.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MYDB.example.com)
    )
  )
Enter fullscreen mode Exit fullscreen mode

2. Typo or Mismatch in the Connect Identifier

The alias in your connection string doesn't exactly match what's defined in tnsnames.ora. Hidden spaces or invisible characters introduced by copy-paste are surprisingly common culprits.

-- Always test with tnsping before blaming the database
-- tnsping MYDB

-- Use Easy Connect to bypass tnsnames.ora entirely for quick testing
-- sqlplus username/password@//db-host.example.com:1521/MYDB

-- Creating a DB Link using direct connection string (no tnsnames.ora needed)
CREATE DATABASE LINK test_link
  CONNECT TO remote_user IDENTIFIED BY remote_pass
  USING '//db-host.example.com:1521/REMOTEDB';

-- Test the DB Link
SELECT * FROM dual@test_link;
Enter fullscreen mode Exit fullscreen mode

3. Incorrect NAMES.DIRECTORY_PATH in sqlnet.ora

If sqlnet.ora is configured to use LDAP or Oracle Names as the primary lookup method and those services are unavailable, Oracle fails to resolve the identifier and throws ORA-12154.

-- sqlnet.ora: simplest and most reliable setting
-- File location: $ORACLE_HOME/network/admin/sqlnet.ora

-- Recommended setting for most environments:
-- NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)

-- If LDAP is required but causing issues, move TNSNAMES first:
-- NAMES.DIRECTORY_PATH = (TNSNAMES, LDAP, EZCONNECT)

-- Check listener and service registration inside the DB
SELECT name, value
FROM v$parameter
WHERE name IN ('local_listener', 'remote_listener', 'service_names');
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Option A – Fix the TNS entry directly:

# Set TNS_ADMIN explicitly to ensure correct file is used
export TNS_ADMIN=/u01/app/oracle/product/19.0.0/dbhome_1/network/admin

# Validate immediately
tnsping MYDB
Enter fullscreen mode Exit fullscreen mode

Option B – Use Easy Connect (skip tnsnames.ora entirely):

-- SQL*Plus Easy Connect syntax
-- sqlplus user/pass@//hostname:1521/service_name

-- JDBC equivalent
-- jdbc:oracle:thin:@//hostname:1521/service_name
Enter fullscreen mode Exit fullscreen mode

Option C – Use Oracle Wallet to centralize credentials:

-- After configuring Oracle Wallet, connect without exposing credentials
-- sqlplus /@MYDB

-- sqlnet.ora wallet configuration
-- WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/wallet)))
-- SQLNET.WALLET_OVERRIDE = TRUE
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Version-control your TNS files. Store tnsnames.ora, sqlnet.ora, and listener.ora in Git. Always run tnsping [alias] after every change, and never modify production TNS files without a backup and a change management ticket.

  2. Prefer Easy Connect or Oracle Wallet in modern environments. Oracle 12c and above support Easy Connect Plus, which eliminates tnsnames.ora dependency entirely. For enterprise environments, Oracle Wallet provides centralized, secure credential and connection management, dramatically reducing ORA-12154 occurrences.

Related Errors

Error Code Description
ORA-12541 TNS: no listener – listener not running
ORA-12514 Listener does not know the requested service
ORA-12545 Host or object not found on network
ORA-12170 Connect timeout – firewall or routing issue

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)