ORA-12514在重新启动服务器后发生错误。

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 +服务器系统登录。除此之外，我不能以任何其他方式登录。服务器端sql开发人员也不工作。我已经从服务器提供了以下详细信息:

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\11.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

LISTENER.ORA

# listener.ora Network Configuration File: E:\app\administrator\product\11.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\11.2.0)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:E:\app\administrator\product\11.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

# sqlnet.ora Network Configuration File: E:\app\administrator\product\11.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\11.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\11.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\11.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:监听器目前不知道连接描述符中的服务请求。

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??

为什么lsnrctl状态不显示orcl的状态??我尝试了orcl服务。但它的说法已经开始了。有什么问题吗?

1 个解决方案

#1

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:

从注释看来，默认的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 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.

如果这样做——也就是说，lsnrctl服务现在显示了orcl——并且您对它感到满意，将内存更改为两个并重新执行，以便在下一次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.

另一个版本是在tnsname中定义侦听器。然后使用该别名作为local_listener值;如果静态IP发生了变化，这可能会使更改更容易，因为您只需要更改侦听器。奥拉,以及。ora(当然还有你所有的客户)，你不需要直接修改DB参数。这可能只会有用如果你有不同的人管理数据库和那些文件，这是不太可能的;但可能会更整洁一些。

#1

alter system set local_listener = '(ADDRESS=(PROTOCOL=TCP)(HOST=172.17.26.7)(PORT=1521))' scope=memory;
alter system register;