ORA-12154 means the Oracle client could not turn the name after the @ in a connect string into a database address. A name such as app_db is a net service alias, which the client looks up in a tnsnames.ora file, or in a directory or LDAP server. When the alias is not in the file the client reads, or the client reads a different file than you think, the connection fails before it reaches any database. Oracle AI Database 26ai clients say exactly which file they searched.
Reproduce the Error
A test user is created for the examples:
Example:
-- a test user for the examples drop user if exists demo_app cascade; create user demo_app identified by "Demo_Pass_26" default tablespace users; grant create session to demo_app;
Connecting with an alias that no tnsnames.ora defines:
Example:
sqlplus -s -L demo_app/Demo_Pass_26@app_db <<'SQL' select user from dual; SQL
Output:
ERROR: ORA-12154: Cannot connect to database. Cannot find alias app_db in /opt/oracle/product/26ai/dbhomeFree/network/admin/tnsnames.ora. SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus
Fix 1: Use an Easy Connect String
host:port/service_name needs no alias and no tnsnames.ora:
Example:
# host:port/service needs no tnsnames.ora entry
sqlplus -s -L demo_app/Demo_Pass_26@localhost:1521/FREEPDB1 <<'SQL'
select user, sys_context('userenv', 'service_name') as service from dual;
SQLOutput:
USER SERVICE ---------- ---------- DEMO_APP freepdb1
Fix 2: Define the Alias in tnsnames.ora
Add the alias to tnsnames.ora, in the directory the client reads, which is $TNS_ADMIN when it is set and $ORACLE_HOME/network/admin otherwise:
Example:
# define the alias in a tnsnames.ora, in the directory TNS_ADMIN points to
mkdir -p /tmp/demo_tns
cat > /tmp/demo_tns/tnsnames.ora <<'TNS'
APP_DB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = FREEPDB1)))
TNS
export TNS_ADMIN=/tmp/demo_tns
sqlplus -s -L demo_app/Demo_Pass_26@app_db <<'SQL'
select user from dual;
SQL
rm -r /tmp/demo_tnsOutput:
USER ---------- DEMO_APP
Other Common Causes
- Several Oracle clients are installed, and the program uses one whose tnsnames.ora lacks the alias.
- A typing error in tnsnames.ora, such as an unbalanced parenthesis, makes the entries after it unreadable.
- Special characters in the password, such as @, split the connect string in the wrong place; put the password in double quotes.
- sqlnet.ora sets NAMES.DEFAULT_DOMAIN, so the client looks up app_db.domain instead of app_db.
Related Guides
Conclusion
ORA-12154 means the client could not find the alias in the connect string. Use an Easy Connect string such as host:1521/service, or add the alias to the tnsnames.ora in the directory the client really reads.
See my articles more often when you search Google.
