TNSPING OK but sqlplus gives ORA-12154?

Question

I have Oracle 11 running on a Windows server and I'm logged onto the same server trying to use SQL Plus. When I try to connect I get a ORA-12154 even though TNSPING and various other diagnostics look OK.

Can anyone suggest why ? Loads of detail below.

I can use sqlplus if I use EZCONNECT like this ..

sqlplus EST/EST@192.168.10.15/ORCL

... but if I try to connect using TNSNAMES like this ...

sqlplus EST/EST@ORCL

... I get ...

ORA-12154: TNS:could not resolve the connect identifier specified

TNSPING works OK

C:\Documents and Settings\user1>tnsping ORCL

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 12-NOV-2013 12:41:14

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
E:\app\Administrator\product\11.2.0\dbhome_2\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.15)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL))
)
OK (20 msec)

And the listener looks like this :

C:\Documents and Settings\user1>lsnrctl services

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 14-NOV-2013 12:02:59

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:55 refused:0 state:ready
         LOCAL SERVER
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: MARIEL, pid: 2400>
         (ADDRESS=(PROTOCOL=tcp)(HOST=mariel)(PORT=1045))
The command completed successfully

And this

C:\Documents and Settings\user1>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 14-NOV-2013 12:29:21

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date                14-NOV-2013 11:41:10
Uptime                    0 days 0 hr. 48 min. 11 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   E:\app\Administrator\product\11.2.0\dbhome_2\network\admin\listener.ora
Listener Log File         e:\app\administrator\diag\tnslsnr\mariel\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.15)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

Here are the various config files:

listener.ora

# listener.ora Network Configuration File: E:\app\Administrator\product\11.2.0\dbhome_2\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = E:\app\Administrator\product\11.2.0\dbhome_2)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:E:\app\Administrator\product\11.2.0\dbhome_2\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.15)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = E:\app\Administrator

tnsnames.ora

# tnsnames.ora Network Configuration File: E:\app\Administrator\product\11.2.0\dbhome_2\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.15)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

sqlnet.ora

# sqlnet.ora Network Configuration File: E:\app\Administrator\product\11.2.0\dbhome_2\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

score 18 · Accepted Answer · edited Apr 08 '15 at 20:28

18

Create an environment variable TNS_ADMIN that points to the directory where your tnsnames.ora file resides. Then try to connect with sqlplus.

If that works, then my guess is you maybe installed the Oracle client software too, and when you run sqlplus, it looks for the tnsnames.ora file in your client home.

-- Instructions for Adding the Environment variable TNS_ADMIN in windows
1. Go to control panel / system
2. select Advanced system settings
3. Select "Advanced" tab, and the environment variable button is at the bottom.
4. create new variable TNS_ADMIN and give the path where the .ora files are stored. e.g. C:\app\oracle\product\11.2.0\client_1\network\admin

edited Apr 08 '15 at 20:28

Community

1
1

answered Nov 14 '13 at 01:28

DCookie

42,630
11
83
92

2

Thanks. This worked. I still fail to understand how tnsping can find tnsnames.ora just fine, but sqlplus needs help from an environment variable?! – Baodad Nov 26 '18 at 22:01
The Environment Variable fixed for me. The strange part is that I have configured the tnsnames.ora for other links, and it worked. When I added the new one, tnsping was ok, but not the sqlplus connection, while the old ones worked. After added the Env Variable the new link worked. – msuzuki Feb 04 '22 at 12:19
@Baodad, it depends on what Oracle software you've installed. If you've got sqlplus installed in multiple places, it all depends on which one you're running. – DCookie Feb 05 '22 at 18:05

score 10 · Answer 2 · answered Aug 07 '17 at 22:04

10

Sqlplus will give this error if you have an at-sign (@) in your password, which you do. Sqlplus thinks you are inputting the connection string as a parameter. Change your password (you can do this with SQL Developer).

Moral: Don't use at-signs in Oracle passwords.

answered Aug 07 '17 at 22:04

Gary

101
1
2

And here I was spending hours scratching my head what I did wrong. What a stupid thing to do in a password field by SQL*Plus!! Thanks very much for your answer, I would not have figured this out on my own. – toddlermenot Sep 23 '17 at 18:34
This was my issue. I wouldn't have guessed that since I didn't enter the password using the single command for connect. I waited for the prompt. This is ridiculous! – Bob Apr 04 '18 at 16:07

score 0 · Answer 3 · answered Nov 12 '19 at 07:59

0

tnslsnr is up but database is down.

Check that database is running

ps aux | fgrep pmon

If there are no such a process, try to start database manually

su - oracle
export ORACLE_SID=XE
sqlplus sys as sysdba

And then in sql console

startup

answered Nov 12 '19 at 07:59

user3132194

2,381
23
17

score 0 · Answer 4 · answered Mar 24 '21 at 22:02

0

Make sure that there is no special characters like @ on your password. It does on my case.

Check below, if it shows an invalid password then the special character is the reason.

sqlplus anyword/anyword@yourServiceName

answered Mar 24 '21 at 22:02

DBA James Choi

51
4

TNSPING OK but sqlplus gives ORA-12154?

4 Answers4

Linked