oracle-解决ORA-12514 监听程序当前无法识别连接描述符中请求的服务

发表于 分类于

解决 oracle 中 tns listener报错问题记录

listener.ora 文件

修改 listener.ora 文件

该文件在oracle安装目录下，示例C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora

这里主要在 SID_LIST_LISTENER 增加 一个 SID_DESC

示例，增加 gzfy sid 服务

1
2
3
4
5
(SID_DESC =
  (GLOBAL_DBNAME = GZFY)
  (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)  
  (SID_NAME = GZFY)
)

完整文件示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# listener.ora Network Configuration File: C:\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 = C:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)  
      (SID_NAME = ORCL)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = GZFY)
      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)  
      (SID_NAME = GZFY)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pc130)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = C:\app\Administrator

tnsname.ora

对应上面配置的 sid 描述，在 tnsname.ora 文件中配置

1
2
3
4
5
6
7
8
GZFY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = pc130)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = gzfy)
    )
  )

完整文件示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
# tnsnames.ora Network Configuration File: C:\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)
    )
  )

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = pc130)(PORT = 1521))


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

GZFY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = pc130)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = gzfy)
    )
  )

重启服务

重启监听服务即可解决

0%
隐藏