TNSPING 可以，但 sqlplus 给出 ORA-12154? [英] TNSPING OK but sqlplus gives ORA-12154?
问题描述
我在 Windows 服务器上运行 Oracle 11，并且我登录到同一台服务器尝试使用 SQL Plus.当我尝试连接时，即使 TNSPING 和其他各种诊断看起来正常，我也会收到 ORA-12154.
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.
如果我像这样使用 EZCONNECT，我可以使用 sqlplus ..
I can use sqlplus if I use EZCONNECT like this ..
sqlplus EST/EST@192.168.10.15/ORCL
...但是如果我尝试像这样使用 TNSNAMES 进行连接...
... but if I try to connect using TNSNAMES like this ...
sqlplus EST/EST@ORCL
...我明白...
ORA-12154: TNS:could not resolve the connect identifier specified
TNSPING 工作正常
C:Documents and Settingsuser1>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:appAdministratorproduct11.2.0dbhome_2
etworkadminsqlnet.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)
监听器看起来像这样:
C:Documents and Settingsuser1>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
还有这个
C:Documents and Settingsuser1>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:appAdministratorproduct11.2.0dbhome_2
etworkadminlistener.ora
Listener Log File e:appadministratordiag nslsnrmariellisteneralertlog.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.pipeEXTPROC1521ipc)))
(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:appAdministratorproduct11.2.0dbhome_2
etworkadminlistener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = E:appAdministratorproduct11.2.0dbhome_2)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:E:appAdministratorproduct11.2.0dbhome_2inoraclr11.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.15)(PORT = 1521))
)
)
ADR_BASE_LISTENER = E:appAdministrator
tnsnames.ora
# tnsnames.ora Network Configuration File: E:appAdministratorproduct11.2.0dbhome_2
etworkadmin nsnames.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:appAdministratorproduct11.2.0dbhome_2
etworkadminsqlnet.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)
推荐答案
创建一个指向 tnsnames.ora 文件所在目录的环境变量 TNS_ADMIN.然后尝试用sqlplus连接.
Create an environment variable TNS_ADMIN that points to the directory where your tnsnames.ora file resides. Then try to connect with sqlplus.
如果可行，那么我猜您可能也安装了 Oracle 客户端软件，并且当您运行 sqlplus 时，它会在您的客户端主目录中查找 tnsnames.ora 文件.
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.
--windows中添加环境变量TNS_ADMIN的说明
1. 进入控制面板/系统
2.选择高级系统设置
3、选择高级"选项卡，最下方是环境变量按钮.
4. 创建新变量 TNS_ADMIN 并给出 .ora 文件的存储路径.例如C:apporacleproduct11.2.0client_1
etworkadmin
-- 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:apporacleproduct11.2.0client_1
etworkadmin
这篇关于TNSPING 可以，但 sqlplus 给出 ORA-12154?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
