不论是单实例还是RAC,对于非缺省端口下(1521)的监听器,pmon进程不会将service/instance注册到监听器,即不会实现动态注册。与单实例相同,RAC非缺省端口的监听器也是通过设置参数local_listener来达到目的。除此之外,还可以对实例进行远程注册,以达到负载均衡的目的。这是通过一个参数remote_listener来实现。
有关Oracle 网络配置相关基础以及概念性的问题请参考:
配置ORACLE 客户端连接到数据库 配置非默认端口的动态服务注册
配置sqlnet.ora限制IP访问Oracle
Oracle 监听器日志配置与管理
设置 Oracle 监听器密码(LISTENER)
一、创建非缺省的监听器
使用netca新建一个非缺省的listener,当然也可以直接修改各节点上的listener.ora
oracle@bo2dbp:~> export DISPLAY=192.168.7.133:0.0
oracle@bo2dbp:~> netca
--选择cluster configuration
--选择所有的节点
--选择listener configuration
--选择add
--设定一个新的监听器的名字,假定为LISTENER_NEW
--选择tcp
--设定非缺省的端口号,此处设定为1314
--选择no,点击next等待完成
--如之前已经存在缺省的监听器,此时出现提示选择启动那个监听,选择刚建的LISTENER_NEW
--next,提示完成, finish
二、缺省监听器与非缺省监听器对照
- oracle@bo2dbp:~> ps -ef | grep lsnr
- oracle 21097 1 0 17:40 ? 00:00:00 /u01/oracle/db/bin/tnslsnr LISTENER_BO2DBP -inherit
- oracle 26228 1 0 17:58 ? 00:00:00 /u01/oracle/db/bin/tnslsnr LISTENER_NEW_BO2DBP -inherit
- oracle 28842 19468 0 17:58 pts/1 00:00:00 grep lsnr
- 此时可以看到有两个监听器,一个是原来缺省的,一个是新增加的,注意监听器的命名,RAC环境下是监听器的名字加上hostname
- 下面的listener.ora的内容已经包含了两个监听器,一个是缺省的,一个是非缺省的。
- 相应地,listener.ora中也多出了刚刚创建的非缺省监听器
- oracle@bo2dbp:~> more $ORACLE_HOME/network/admin/listener.ora
- # listener.ora.bo2dbp Network Configuration File: /u01/oracle/db/network/admin/listener.ora.bo2dbp
- # Generated by Oracle configuration tools.
- LISTENER_NEW_BO2DBP =
- (DESCRIPTION_LIST =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = bo2dbp-vip.2gotrade.com)(PORT = 1314)(IP = FIRST))
- (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.51)(PORT = 1314)(IP = FIRST))
- )
- )
- LISTENER_BO2DBP =
- (DESCRIPTION_LIST =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = bo2dbp-vip.2gotrade.com)(PORT = 1521)(IP = FIRST))
- (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.51)(PORT = 1521)(IP = FIRST))
- )
- )
- SID_LIST_LISTENER_NEW_BO2DBP =
- (SID_LIST =
- (SID_DESC =
- (SID_NAME = PLSExtProc)
- (ORACLE_HOME = /u01/oracle/db)
- (PROGRAM = extproc)
- )
- )
- SID_LIST_LISTENER_BO2DBP =
- (SID_LIST =
- (SID_DESC =
- (SID_NAME = PLSExtProc)
- (ORACLE_HOME = /u01/oracle/db)
- (PROGRAM = extproc)
- )
- )
- #查看监听器的状态
- oracle@bo2dbp:~> ./crs_stat.sh
- Resource name Target State
- -------------- ------ -----
- ora.GOBO4.GOBO4A.inst ONLINE ONLINE on bo2dbp
- ora.GOBO4.GOBO4B.inst ONLINE ONLINE on bo2dbs
- ora.GOBO4.db ONLINE ONLINE on bo2dbp
- ora.bo2dbp.ASM1.asm ONLINE ONLINE on bo2dbp
- ora.bo2dbp.LISTENER_BO2DBP.lsnr ONLINE ONLINE on bo2dbp
- ora.bo2dbp.LISTENER_NEW_BO2DBP.lsnr ONLINE ONLINE on bo2dbp
- ora.bo2dbp.gsd ONLINE ONLINE on bo2dbp
- ora.bo2dbp.ons ONLINE ONLINE on bo2dbp
- ora.bo2dbp.vip ONLINE ONLINE on bo2dbp
- ora.bo2dbs.ASM2.asm ONLINE ONLINE on bo2dbs
- ora.bo2dbs.LISTENER_BO2DBS.lsnr ONLINE ONLINE on bo2dbs
- ora.bo2dbs.LISTENER_NEW_BO2DBS.lsnr ONLINE ONLINE on bo2dbs
- ora.bo2dbs.gsd ONLINE ONLINE on bo2dbs
- ora.bo2dbs.ons ONLINE ONLINE on bo2dbs
- ora.bo2dbs.vip ONLINE ONLINE on bo2dbs
- ora.ora10g.db ONLINE ONLINE on bo2dbp
- ora.ora10g.ora10g1.inst ONLINE ONLINE on bo2dbp
- ora.ora10g.ora10g2.inst ONLINE ONLINE on bo2dbs
- #比较缺省监听器与非缺省监听器的状态
- oracle@bo2dbp:~> lsnrctl
- LSNRCTL> set current_listener LISTENER_NEW_BO2DBP
- LSNRCTL> status #端口号为非缺省的情形下仅存在Service "PLSExtProc",这是因为没有动态注册的原因
- Services Summary...
- Service "PLSExtProc" has 1 instance(s).
- Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
- The command completed successfully
- LSNRCTL> set current_listener LISTENER_BO2DBP
- Current Listener is LISTENER_BO2DBP
- LSNRCTL> status
- .........
- Listening Endpoints Summary...
- (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.61)(PORT=1521)))
- (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.51)(PORT=1521)))
- Services Summary...
- .........
- Service "ora10g" has 2 instance(s).
- Instance "ora10g1", status READY, has 2 handler(s) for this service...
- Instance "ora10g2", status READY, has 1 handler(s) for this service...
- .........
- The command completed successfully
- oracle@bo2dbp:~> lsnrctl status #查看缺省监听器的状态(即断口号为1521)
- .......
- (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.61)(PORT=1521)))
- (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.51)(PORT=1521)))
- Services Summary...
- .......
- Service "ora10g" has 2 instance(s).
- Instance "ora10g1", status READY, has 2 handler(s) for this service...
- Instance "ora10g2", status READY, has 1 handler(s) for this service...
- .......
- The command completed successfully
- #查看此时local_listener与remote_listener参数
- #Author: Robinson Cheng
- #Blog : http://blog.csdn.net/robinson_0612
- SQL> show parameter instance_name
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- instance_name string ora10g1
- SQL> show parameter listener
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- local_listener string
- remote_listener string LISTENERS_ORA10G
- 结论,与单实例相同,如果未设定非缺省的listener,则使用listener 与LISTENER_BO2DBP查看到相同的结果
- 也就是说lsnrctl status [listener_nam]查看的本身就是缺省端口监听器的信息
- 对于非缺省端口的监听器,未设置local_listener时不会有数据库实例注册
- #关闭缺省的监听器
- SQL> ho srvctl stop listener -n bo2dbp -l LISTENER_BO2DBP
- SQL> ho srvctl stop listener -n bo2dbs -l LISTENER_BO2DBS
- SQL> ho ./crs_stat.sh
- Resource name Target State
- -------------- ------ -----
- ora.GOBO4.GOBO4A.inst OFFLINE OFFLINE
- ora.GOBO4.GOBO4B.inst OFFLINE OFFLINE
- ora.GOBO4.db OFFLINE OFFLINE
- ora.bo2dbp.ASM1.asm ONLINE ONLINE on bo2dbp
- ora.bo2dbp.LISTENER_BO2DBP.lsnr OFFLINE OFFLINE
- ora.bo2dbp.LISTENER_NEW_BO2DBP.lsnr ONLINE ONLINE on bo2dbp
- ora.bo2dbp.gsd ONLINE ONLINE on bo2dbp
- ora.bo2dbp.ons ONLINE ONLINE on bo2dbp
- ora.bo2dbp.vip ONLINE ONLINE on bo2dbp
- ora.bo2dbs.ASM2.asm ONLINE ONLINE on bo2dbs
- ora.bo2dbs.LISTENER_BO2DBS.lsnr OFFLINE OFFLINE
- ora.bo2dbs.LISTENER_NEW_BO2DBS.lsnr ONLINE ONLINE on bo2dbs
- ora.bo2dbs.gsd ONLINE ONLINE on bo2dbs
- ora.bo2dbs.ons ONLINE ONLINE on bo2dbs
- ora.bo2dbs.vip ONLINE ONLINE on bo2dbs
- ora.ora10g.db ONLINE ONLINE on bo2dbp
- ora.ora10g.ora10g1.inst ONLINE ONLINE on bo2dbp
- ora.ora10g.ora10g2.inst ONLINE ONLINE on bo2dbs
三、配置非缺省监听器的动态注册
- oracle@bo2dbp:/u01/oracle/db/network/admin> echo "
- > LISTENERS_ORA10G1 =
- > (ADDRESS_LIST =
- > (ADDRESS = (PROTOCOL = TCP)(HOST = bo2dbp-vip.2gotrade.com)(PORT = 1314))
- > )">>tnsnames.ora #使用echo添加新的连接标识符到tnsnames.ora
- oracle@bo2dbp:/u01/oracle/db/network/admin> tail -5 tnsnames.ora
- LISTENERS_ORA10G1 =
- (ADDRESS_LIST =
- (ADDRESS = (PROTOCOL = TCP)(HOST = bo2dbp-vip.2gotrade.com)(PORT = 1314))
- )
- SQL> alter system set local_listener=LISTENERS_ORA10G1 scope=both sid='ora10g1';
- System altered.
- SQL> ho lsnrctl status LISTENER_NEW_BO2DBP
- LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 09-OCT-2012 11:56:15
- Copyright (c) 1991, 2006, Oracle. All rights reserved.
- Listening Endpoints Summary...
- (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.61)(PORT=1314)))
- (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.51)(PORT=1314)))
- Services Summary...
- Service "ora10g" has 1 instance(s).
- Instance "ora10g1", status READY, has 1 handler(s) for this service...
- ........
- #此时已经看到ora10g1已经注册到非缺省的1314监听器LISTENER_NEW_BO2DBP中
- #修改tnsnames.ora中ora10g中的端口号为1314
- #下面测试一下修改后的情形,可以看出1314端口已经被使用
- oracle@bo2dbp:~> tnsping ora10g
- Used TNSNAMES adapter to resolve the alias
- Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = bo2dbp-vip.2gotrade.com)(PORT = 1314))
- (ADDRESS = (PROTOCOL = TCP)(HOST = bo2dbs-vip.2gotrade.com)(PORT = 1314)) (LOAD_BALANCE = yes)
- (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ora10g)))
- OK (10 msec)
- oracle@bo2dbp:~> sqlplus scott/tiger@ora10g
- SQL> show parameter instance_name
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- instance_name string ora10g1
- 使用与上述相同的方法设置第二个节点上的local_listener.
- 即先修改tnsnames.ora,再设置local_listener.注意尽可能使得连接符名字不同用于区分,如设置为LISTENERS_ORA10G2,注意主机名以及端口号
- SQL> show parameter instance_name
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- instance_name string ora10g2
- SQL> alter system set local_listener=LISTENERS_ORA10G2 scope=both sid='ora10g2';
- System altered.
- SQL> alter system register;
- System altered.
- SQL> ho lsnrctl status LISTENER_NEW_BO2DBS
- Listening Endpoints Summary...
- (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.62)(PORT=1314))) #可以看到此时端口号为1314
- (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.7.52)(PORT=1314)))
- Service "ora10g" has 1 instance(s).
- Instance "ora10g2", status READY, has 1 handler(s) for this service... #instance ora10g2已注册
- ..............
四、设置非缺省监听器的远程注册
监听器的远程注册主要用于实现负载均衡。通常情况下,客户端发出的连接请求会首先被local_listener接受,然后由master instance来决定当前的连接请求应该由哪个目标instance发出server process响应这个连接请求。在启用了负载均衡的情形下,master instance会将请求转发到负载较小的实例,如果此时remote_listener中指定的实例负载较小,则当前的请求被重定向到负载较小的实例中来建立连接,派生服务器进程进行相应连接。
- SQL> show parameter listener
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- local_listener string LISTENERS_ORA10G1
- remote_listener string LISTENERS_ORA10G
- 上面remote_listener的值LISTENERS_ORA10G是在创建数据库的时候创建的,而此时我们使用了非缺省端口的监听器,因此有两种方法来处
- 理,一是将tnsnamas.ora中对应的LISTENERS_ORA10G连接字符中的端口号更改为1314,其次是添加一个新的连接串,下面采用第二种方法.
- -->首先清空原来的remote_listener
- SQL> alter system reset remote_listener scope=both sid='*';
- System altered.
- SQL> show parameter listener
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- local_listener string LISTENERS_ORA10G1
- remote_listener string
- -->添加字符串到tnsnames.ora
- oracle@bo2dbp:~> echo "
- > remote_lsnr_ora10g =
- > (ADDRESS_LIST =
- > (ADDRESS = (PROTOCOL = TCP)(HOST = bo2dbp-vip.2gotrade.com)(PORT = 1314))
- > (ADDRESS = (PROTOCOL = TCP)(HOST = bo2dbs-vip.2gotrade.com)(PORT = 1314))
- > )">>$ORACLE_HOME/network/admin/tnsnames.ora
- oracle@bo2dbp:~> tail -6 $ORACLE_HOME/network/admin/tnsnames.ora
- remote_lsnr_ora10g =
- (ADDRESS_LIST =
- (ADDRESS = (PROTOCOL = TCP)(HOST = bo2dbp-vip.2gotrade.com)(PORT = 1314))
- (ADDRESS = (PROTOCOL = TCP)(HOST = bo2dbs-vip.2gotrade.com)(PORT = 1314))
- )
- 采用类似的方法将上面的内容添加到节点2上的tnsnames.ora中
- SQL> alter system set remote_listener='remote_lsnr_ora10g' scope=both sid='*';
- System altered.
- SQL> show parameter listener
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- local_listener string LISTENERS_ORA10G1
- remote_listener string remote_lsnr_ora10g
- SQL> alter system register;
- System altered.
- SQL> ho lsnrctl status LISTENER_NEW_BO2DBP
- Service "ora10g" has 2 instance(s). #服务ora10g有2个instance可以提供服务
- Instance "ora10g1", status READY, has 2 handler(s) for this service...
- Instance "ora10g2", status READY, has 1 handler(s) for this service...
- .................
六、RAC 上配置监听器的步骤总结
1、为每个监听器在listener.ora中生成相应的条目,host的内容尽可能使用vip地址(建议直接指定ip,以避免dns解析错误)
2、为每个节点配置相应的tns条目,同样使用vip
LISTENERS_CLUSTERNAME = #全局配置,对应remote_listener,即如果有2个节点列出两个vip,3个节点应列出3个vip
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip)(PORT = 1521))
)
LISTENERS_CLUSTER_1 = #本地配置,仅列出当前实例的节点ip
(ADDRESS_LIST = #节点2上可以设置为LISTENERS_CLUSTER_1,相应地vip为节点2的vip
(ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521))
) #也可以在一个节点上配置所有的local_listener连接标识符,然后将tnsnames.ora复制到所有节点
3、设置全局remote_listener参数
alter system set remote_listener='LISTENERS_CLUSTERNAME' scope=both sid='*';
4、设置本地local_listener参数(各个节点单独设置)
alter system set remote_listener='LISTENERS_CLUSTER_1' scope=both sid='node1';
或者直接将ip,port值设置到local_listener,如果这样第3步中的LISTENERS_CLUSTER_1可以不用配置,如下:
alter system set LOCAL_LISTENER='(ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521))' sid='node1';
5、在每一节点使用alter system register实现立即注册。此步骤可以省略。因为我们设置了local_listener与remote_listener之后等待片刻就可以实现动态注册。alter system register仅仅用于加快注册。
6、使用lsnrctl stauts LISTENER_<hostname> 查看监听器的状态
转:http://blog.csdn.net/leshami/article/details/8053007