The listener supports no services解决一例
Listener动态监听静态监听注册实例
今天做Advacned Replication实验的时候碰到一个问题，启动目标库监听时，出现The listener supports no services
[oracle@adrep ~]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 14-SEP-2013 10:11:38
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/adrep/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adrep)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adrep)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 14-SEP-2013 10:11:38
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/adrep/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adrep)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
由于这台主机之前做的是stream实验，修改了一下hostname，之前的监听和服务名也用netca重新配置过了，但是启动监听一致是这个状态，动态监听注册不上
本地登录到Oracle实例，手动注册实例也无果，问题依旧
SQL> alter system register;
System altered.
SQL> !
[oracle@adrep ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 14-SEP-2013 10:13:03
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adrep)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 14-SEP-2013 10:11:38
Uptime 0 days 0 hr. 1 min. 25 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/adrep/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adrep)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@adrep ~]$ lsnrctl reload
Listener Log File /u01/app/
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 14-SEP-2013 10:16:13
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adrep)(PORT=1521)))
The command completed successfully
[oracle@adrep ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 14-SEP-2013 10:13:18
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adrep)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 14-SEP-2013 10:11:38
Uptime 0 days 0 hr. 1 min. 40 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/adrep/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adrep)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@adrep ~]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 14-SEP-2013 10:17:03
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adrep)(PORT=1521)))
The command completed successfully
[oracle@adrep ~]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 14-SEP-2013 10:18:53
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/adrep/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adrep)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adrep)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 14-SEP-2013 10:18:53
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/adrep/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adrep)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@adrep ~]$
于是考虑通过mgr配置静态监听，但是让我郁闷的是，总是提示需要输入一个合法的SID
这个SID绝对是strmtarget没错的，global name即service name也确实是target，这个可以由以下参数获得佐证
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string strmtarg
db_unique_name string strmtarget
global_names boolean TRUE
instance_name string strmtarget
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string target
也可以通过v$instance视图查到SID是正确的
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
strmtarget
难道就没有办法设置静态监听了吗？NO，这里还有一种方法就是手动修改listener.ora文件，添加以下几行内容
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = target)
(SID_NAME = strmtarget)
)
)
其作用就是配置一个实例静态监听，只不过用这里跳过了图形化界面修改而已，如果是用netmgr成功修改了实例的静态监听，也是会生成以上几行代码的
这里可以先把listener.ora用xftp工具拷贝出来，修改完再拷贝回相同位置即可
这里只需要重新加载一次监听就可以注册上实例了
[oracle@adrep ~]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 14-SEP-2013 10:32:06
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adrep)(PORT=1521)))
The command completed successfully
[oracle@adrep ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 14-SEP-2013 10:32:14
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adrep)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 14-SEP-2013 10:18:53
Uptime 0 days 0 hr. 13 min. 21 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/adrep/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adrep)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "target" has 1 instance(s).
Instance "strmtarget", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@adrep ~]$
这里target实例通过以上方法，成功地把实例注册上了，注意"UNKNOWN"就表示是静态注册的，如果是"READY"则表示是动态注册的
注意：动态和静态监听可以共存，如：
[oracle@zlm ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 14-SEP-2013 09:52:23
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=zlm)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 14-SEP-2013 09:15:04
Uptime 0 days 0 hr. 37 min. 19 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/zlm/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=zlm)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "zlm11g" has 2 instance(s).
Instance "zlm11g", status UNKNOWN, has 1 handler(s) for this service...
Instance "zlm11g", status READY, has 1 handler(s) for this service...
Service "zlm11gXDB" has 1 instance(s).
Instance "zlm11g", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@zlm ~]$
以上是我在zlm主机上的监听状态，可以发现，同时配置了动态和静态的监听
总结：以上利用了手动修改配置静态监听的方法成功注册了Oracle实例，动态监听往往需要等实例启动后，过一段时间才会注册上，而静态监听则 可以马上注册。而且一旦实例重启会造成动态监听暂时无效，有些版本的bug甚至会导致动态监听的注册就再也起不来了。所以我对于监听的观点是，尽量配置静 态监听，这样就可以避免很多由监听带来的问题，诸如我们经常会碰到的几个ora error：ora-12541、ora-12514、ora-12154等，尤其是在RAC和DG中，一个监听的问题，往往会带来很大的麻烦，尤其需要 引起大家的注意。