在window服务中关闭了oracle监听和服务实例在重启监听和服务实例后，提示监听程序当前无法识别连接描述符中请求的服务

参考：https://blog.csdn.net/hfdgjjg/article/details/86693131

一、报错如下

1、在window服务中关闭oracle监听和服务实例如下图： 2、再次重启oracle监听和服务实例，客户端连接oracle时报错：RACLE11g ORA-12514 TNS 监听程序当前无法识别连接描述符中请求的服务，如下图：

二、解决方式

1、首先查看本地TNSPING

1）、查看本地的tns配置：

ORCL =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xx.xx)(PORT = 1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = orcl)
     )
)

2）、查看本地的tnsping是否ok

C:\Users\Administrator>tnsping WXX
TNS Ping Utility for 32-bit Windows:  Version 11.2.0.1.0 - Production on 26-10月-2020 15:01:04
Copyright (c) 1997, 2010, Oracle.  All rights reserved.

已使用的参数文件:
D:\app\Administrator\product\11.2.0\client_2\network\admin\sqlnet.ora
已使用 TNSNAMES 适配器来解析别名

尝试连接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xx.xx)(PORT  = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (30 毫秒)

C:\Users\Administrator>

3)、由此可见本地的tnsping暂时看来是正常的，是ok的。

2、查看网络连接是否ok 1)、命令如下：

C:\Users\Administrator>telnet  192.168.xx.xx 1521

2）、由此可见 telnet 192.168.xx.xx 1521 如果能进去，证明网络ok.

3、查看服务器的lsnrctl服务

1）、查看服务器的lsnrctl服务状态命令如下：

C:\Users\Administrator> lsnrctl  status 
LSNRCTL for Linux: Version 11.2.0.1.0 -  Production on 26-OCT-2020 15:06:32
Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to  (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version  11.2.0.1.0 - Production
Start Date                26-OCT-2016 11:09:33 
Uptime                    0 days 0 hr. 23 min. 59  sec
Trace Level               off 
Security                  ON: Local OS Authentication
SNMP                      OFF 
Listener Parameter File    /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora 
Listener Log File          /usr/oracle/app/diag/tnslsnr/iZ23vluqpmmZ/listener/alert/log.xml
Listening Endpoints Summary...
   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.xx.xx)(PORT=1521)))
The listener supports no services
The command completed successfully

C:\Users\Administrator>

2)、看到no services，也就是说lsnrctl监听服务启动起来，没有任何oracle实例的，所以问题就来了，没有oracle实例，客户端通过lsnrctl监听的话，就连接不到实例信息。所以这里我们需要添加实例信息，添加实例信息，就在listener.ora配置文件里面。

4、在服务端（lz的目录是）C:\app\localadmin\product\11.2.0\dbhome_1\network\admin\listener.ora）下的listener.ora里面添加实例信息

# listener.ora Network Configuration File: C:\app\localadmin\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 = C:\app\localadmin\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\localadmin\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
    )
  )
  
#  下面就是添加的新实例信息 start

SID_LIST_LISTENER  =  
(SID_LIST =  
  (SID_DESC =  
  (GLOBAL_DBNAME = ORCL)
  (SID_NAME = ORCL)
  )
)

ADR_BASE_LISTENER = C:\app\localadmin

# 添加的新实例信息 end

5、设置完后，重启监听lsnrctl服务

1）先停止lsnrctl服务

C:\Users\Administrator> lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.1.0 -  Production on 26-OCT-2020 15:12:29
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
Connecting to  (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
C:\Users\Administrator>

2）在启动lsnrctl服务

C:\Users\Administrator> lsnrctl  start
LSNRCTL for Linux: Version 11.2.0.1.0 -  Production on 26-OCT-2020 15:13:33
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
Starting  /usr/oracle/app/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 -  Production

System parameter file is  app\localadmin\product\11.2.0\dbhome_1\network\admin\listener.ora
 
....

Listening on:  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Listening on:  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xx.xx)(PORT=1521)))

Connecting to  (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
 
STATUS of the LISTENER
------------------------
Alias                     LISTENER 
Version                   TNSLSNR for Linux: Version 11.2.0.1.0  - Production 
Start Date                26-OCT-2020 15:17:33
Uptime                    0 days 0 hr. 1 min. 3 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File    /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora 
...
Listening Endpoints Summary...
   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xx.xx)(PORT=1521)))
Services Summary...

Service  "ORCL" has 1 instance(s).
   Instance "ORCL", status UNKNOWN, has 1 handler(s) for this  service...
The command completed successfully 
C:\Users\Administrator>

6、再次连接，即可成功

三、报错原因

在window服务中关闭了oracle监听和服务实例时，oracle服务端的目录下的监听文件listener.ora 中的实例被删除掉了，重新加回来再重启lsnrctl服务即可。