Oracle服务端启动监听的时候出现The listener supports no services报错,刚开始没注意到,导致了在远程client端连接数据库的时候老是报各种各样的错误,比如:ORA-12545: Connect failed because target host or object does not exist 或者connection refused等等。后来发现启动监听的时候没有仔细看报了The listener supports no services的错误,监听没有启动成功。
这种错误的原因是动态监听注册不上,可能是因为之前在配置中改过IP。 处理办法,在服务端做下面修改:
1. 手工注册: alter system register;
2.重新加载监听: lsnrctl reload
3.重启监听: lsnrctl stop/lsnrctl start
三项操作之后都检查状态: lsnrctl status, 同样还是都报错: The listener supports no services
最终解决办法: 设置静态监听
手动修改listener.ora文件,添加以下几行内容:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(SID_NAME = orcl)
)
)
notes: 可以通过show parameter name查询,GLOBAL_DBNAME即service_names;instance_name即为SID_NAME,或者看./bash_profile里的ORACLE_SID
修改过listener.ora文件之后,其全部内容变为:
# listener.ora Network Configuration File: /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.232.139)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(SID_NAME = orcl)
)
)
修改好之后,重新加载监听即可: lsnrctl reload
这时候再到client端即可连接数据库:
[oracle@test ~]$ sqlplus system/oracle@orcl
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 25 00:13:12 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Mon Jun 24 2019 07:40:39 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>