重新启动服务器后出现 ORA-12514 错误
我在服务器上安装了 oracle 11g,.Net oracle 客户端将访问数据库.
直到昨天,我还在使用此连接字符串从客户端进行连接:
I have oracle 11g installed on server and .Net oracle clients will access the database.
Till yesterday i was connecting from clients using this connection string:
User ID=dbcplas;Password=pwd123;Data Source=(DESCRIPTION = (ADDRESS =(PROTOCOL = TCP)(HOST = 172.17.26.7)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl.cplas3.com)))
直到昨天我才能够在同一个客户端中使用 Oracle SQL Developer.
今天早上我们重新启动了服务器进行维护.重新启动服务器后,我无法从客户端 .Net 应用程序或 sqldeveloper 登录.
我可以通过服务器系统的sql plus登录.
除此之外,我无法通过任何其他方式登录.
服务器端 sql-developer 也不工作.
我从服务器提供了以下详细信息:
Till yesterday I was able to use Oracle SQL Developer in the same client.
Today morning we restarted the server for maintanance. After restarting the server, I cannot able to login from client .Net application or sqldeveloper.
I can able to log-in thru sql plus of server system.
Apart from that i cannot able to log in by any other means.
Server side sql-developer also not working.
I have give below details from the server:
lsnrctl 服务:
C:UsersAdministrator>lsnrctl services
LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 07-JAN-2014 15:
:45
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.17.26.7)(PORT=1521
)
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
The command completed successfully
lsnrctl 状态:
C:UsersAdministrator>lsnrctl status
LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 07-JAN-2014 15:20
:42
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.17.26.7)(PORT=1521))
)
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date 07-JAN-2014 13:17:40
Uptime 0 days 2 hr. 3 min. 7 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File E:appadministratorproduct11.2.0dbhome_1
etworka
dminlistener.ora
Listener Log File e:appadministratordiag nslsnrcplas3listeneraler
tlog.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.26.7)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LISTENER.ORA
# listener.ora Network Configuration File: E:appadministratorproduct11.2.0dbhome_1
etworkadminlistener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = E:appadministratorproduct11.2.0)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:E:appadministratorproduct11.2.0dbhome_1inoraclr11.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.26.7)(PORT = 1521))
)
)
ADR_BASE_LISTENER = E:appadministrator
SQLNET.ORA
# sqlnet.ora Network Configuration File: E:appadministratorproduct11.2.0dbhome_1
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)
TNSFILES.ORA
# tnsnames.ora Network Configuration File: E:appadministratorproduct11.2.0dbhome_1
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 = 172.17.26.7)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.cplas3.com)
)
)
tnsping 结果 - orcl.cplas3.com
C:UsersAdministrator>tnsping orcl.cplas3.com
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 07-JAN-2
014 15:30:44
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
E:appadministratorproduct11.2.0dbhome_1
etworkadminsqlnet.ora
TNS-03505: Failed to resolve name
tnsping 结果 - orcl
C:UsersAdministrator>tnsping orcl
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 07-JAN-2
014 15:31:22
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
E:appadministratorproduct11.2.0dbhome_1
etworkadminsqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.2
6.7)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.cpl
as3.com)))
OK (1560 msec)
错误:
来自 .Net 应用程序:
ORA-12514 TNS:Listener 当前不知道连接描述符中的服务请求.
Errors:
From .Net Application:
ORA-12514 TNS:Listener does not currently know of service request in connect descriptor.
为什么 lsnrctl status
不显示 orcl 的状态??
我尝试启动 orcl 服务.但它的说法已经开始了..
可能是什么问题??
Why lsnrctl status
not showing the status of orcl??
I tried starting the orcl services. But its saying already started..
What may be the problem??
推荐答案
从评论看来 默认 local_listener
参数 可能正在尝试使用来自新网络适配器的动态 IP,因此它使用的地址与侦听器使用的地址不同.解决此问题的最简单方法可能是手动设置该参数:
From the comments it seems that the default local_listener
parameter is probably trying to use the dynamic IP from your new network adaptor, so it isn't using the same address the listener is using. The simplest way to fix this is probably to manually set that parameter:
alter system set local_listener = '(ADDRESS=(PROTOCOL=TCP)(HOST=172.17.26.7)(PORT=1521))' scope=memory;
alter system register;
或者我认为您可以使用较短的版本,但我现在无法验证:
Or I think you can use a shorter version, but I can't verify that right now:
alter system set local_listener = '172.17.26.7:1521' scope=memory;
alter system register;
如果可行 - 也就是说,lsnrctl services
现在显示 orcl
- 并且您对此感到满意,请将 memory
更改为 both
并重新执行,以便它在下次数据库重启时保持不变.
If that works - that is, lsnrctl services
now shows orcl
- and you're happy with it, change the memory
to both
and re-execute so it persists across the next DB restart.
另一个版本是在 tnsnames.ora
中定义监听器,然后使用该别名作为 local_listener
值;如果静态 IP 发生更改,这可能会使更改更容易,因为您只需要更改 listener.ora
和 tnsnames.ora
(以及您所有的客户端,当然),您不必直接修改 DB 参数.这可能只有在您有不同的人管理数据库和那些文件时才有用,这不太可能;但可能会更整洁一些.
Another version of this is to define the listener in the tnsnames.ora
, and then use that alias for the local_listener
value; that would maybe make it easier to make changes if the static IP ever changed, as you'd only need to change the listener.ora
and tnsnames.ora
(and all your clients, of course), you wouldn't have to modify the DB parameter directly. That's probably only useful if you have different people managing the DB and those files, which is not very likely; but might be a bit neater.
相关文章