oracle 重新启动服务器后出现 ORA-12514 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20969044/
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
ORA-12514 error after restarting the server
提问by Olivarsham
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:
我在服务器上安装了 oracle 11g，.Net oracle 客户端将访问数据库。
直到昨天，我还在使用此连接字符串从客户端进行连接：
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)))
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:
直到昨天，我才能在同一个客户端中使用 Oracle SQL Developer。
今天早上我们重新启动了服务器进行维护。重新启动服务器后，我无法从客户端 .Net 应用程序或 sqldeveloper 登录。
我可以通过服务器系统的sql plus登录。
除此之外，我无法通过任何其他方式登录。
服务器端 sql-developer 也不起作用。
我从服务器提供了以下详细信息：
lsnrctl services:
lsnrctl 服务：
C:\Users\Administrator>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 status:
lsnrctl 状态：
C:\Users\Administrator>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:\app\administrator\product.2.0\dbhome_1\network\a
dmin\listener.ora
Listener Log File e:\app\administrator\diag\tnslsnr\cplas3\listener\aler
t\log.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
听者.ORA
# listener.ora Network Configuration File: E:\app\administrator\product.2.0\dbhome_1\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)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:E:\app\administrator\product.2.0\dbhome_1\bin\oraclr11.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.26.7)(PORT = 1521))
)
)
ADR_BASE_LISTENER = E:\app\administrator
SQLNET.ORA
数据库
# sqlnet.ora Network Configuration File: E:\app\administrator\product.2.0\dbhome_1\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)
TNSFILES.ORA
# tnsnames.ora Network Configuration File: E:\app\administrator\product.2.0\dbhome_1\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 = 172.17.26.7)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.cplas3.com)
)
)
tnsping results - orcl.cplas3.com
tnsping 结果 - orcl.cplas3.com
C:\Users\Administrator>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:\app\administrator\product.2.0\dbhome_1\network\admin\sqlnet.ora
TNS-03505: Failed to resolve name
tnsping results - orcl
tnsping 结果 - orcl
C:\Users\Administrator>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:\app\administrator\product.2.0\dbhome_1\network\admin\sqlnet.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)
Errors:
From .Net Application:
ORA-12514 TNS:Listener does not currently know of service request in connect descriptor.
错误：
来自 .Net 应用程序：
ORA-12514 TNS:Listener 当前不知道连接描述符中的服务请求。
Why lsnrctl statusnot showing the status of orcl??
I tried starting the orcl services. But its saying already started..
What may be the problem??
为什么lsnrctl status不显示orcl的状态？？
我尝试启动 orcl 服务。但是它的说法已经开始了..
可能是什么问题？
回答by Alex Poole
From the comments it seems that the default local_listenerparameteris 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:
从评论看来，默认local_listener参数可能试图使用来自新网络适配器的动态 IP，因此它使用的地址与侦听器使用的地址不同。解决此问题的最简单方法可能是手动设置该参数：
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;
If that works - that is, lsnrctl servicesnow shows orcl- and you're happy with it, change the memoryto bothand re-execute so it persists across the next DB restart.
如果这有效 - 即lsnrctl services现在显示orcl- 并且您对它感到满意，请将其更改memory为both并重新执行，以便它在下一次数据库重新启动时保持不变。
Another version of this is to define the listener in the tnsnames.ora, and then use that alias for the local_listenervalue; that would maybe make it easier to make changes if the static IP ever changed, as you'd only need to change the listener.oraand 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.
另一个版本是在 中定义侦听器tnsnames.ora，然后使用该别名作为local_listener值；如果静态 IP 发生更改，这可能会使更改更容易，因为您只需要更改listener.ora和tnsnames.ora（当然还有您的所有客户端），您不必直接修改 DB 参数。这可能只有在您有不同的人管理数据库和那些文件时才有用，这不太可能；但可能会更整洁一些。
