TNSPING 可以,但 sqlplus 给出 ORA-12154?

2021-12-30 00:00:00 oracle11g oracle sqlplus ora-12514

我在 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

相关文章