重新启动服务器后出现 ORA-12514 错误 [英] ORA-12514 error after restarting the server

问题描述

我在服务器上安装了 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.

这篇关于重新启动服务器后出现 ORA-12514 错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！