oracle TNSPING 确定，但 sqlplus 给出 ORA-12154？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19966848/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
TNSPING OK but sqlplus gives ORA-12154?
提问by glaucon
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.
我在 Windows 服务器上运行 Oracle 11，并且我登录到同一台服务器上尝试使用 SQL Plus。当我尝试连接时，即使 TNSPING 和其他各种诊断看起来正常，我也会收到 ORA-12154。
Can anyone suggest why ? Loads of detail below.
谁能建议为什么？下面详细介绍。
I can use sqlplus if I use EZCONNECTlike this ..
如果我像这样使用EZCONNECT，我可以使用 sqlplus ..
sqlplus EST/[email protected]/ORCL
... but if I try to connect using TNSNAMESlike this ...
...但是如果我尝试像这样使用TNSNAMES进行连接...
sqlplus EST/EST@ORCL
... I get ...
……我明白了……
ORA-12154: TNS:could not resolve the connect identifier specified
TNSPINGworks OK
TNSPING工作正常
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.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 listenerlooks 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.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
听者.ora
# listener.ora Network Configuration File: E:\app\Administrator\product.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.2.0\dbhome_2)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:E:\app\Administrator\product.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
# tnsnames.ora Network Configuration File: E:\app\Administrator\product.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
# sqlnet.ora Network Configuration File: E:\app\Administrator\product.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)
回答by DCookie
Create an environment variable TNS_ADMIN that points to the directory where your tnsnames.ora file resides. Then try to connect with sqlplus.
创建一个指向 tnsnames.ora 文件所在目录的环境变量 TNS_ADMIN。然后尝试用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.
如果可行，那么我猜您可能也安装了 Oracle 客户端软件，并且当您运行 sqlplus 时，它会在您的客户端主目录中查找 tnsnames.ora 文件。
-- 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
-- windows中添加环境变量TNS_ADMIN的说明
1.进入控制面板/系统
2.选择高级系统设置
3.选择“高级”选项卡，环境变量按钮在底部。
4. 创建新变量 TNS_ADMIN 并给出 .ora 文件的存储路径。例如 C:\app\oracle\product\11.2.0\client_1\network\admin
回答by Gary
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).
如果您的密码中有 at 符号 (@)，Sqlplus 将给出此错误。Sqlplus 认为您正在输入连接字符串作为参数。更改您的密码（您可以使用 SQL Developer 执行此操作）。
Moral: Don't use at-signs in Oracle passwords.
道德：不要在 Oracle 密码中使用 at 符号。
回答by user3132194
tnslsnris up but database is down.
tnslsnr已启动但数据库已关闭。
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
然后在 sql 控制台中
startup
