refreshrouteevent是有那个listner类监听的 remote_listener原理

转载

flybirdfly 2024-08-13 08:15:07

文章标签 数据库 service oracle tcp linux 文章分类 架构后端开发

使用remote_listener实现数据库与监听器分离

作者：leexi 发布：2011-12-15 13:46 分类： Oracle

remote_listener一般用在RAC环境中，单实例数据库是否可以使用该参数呢？如果可以，是否可以让listener 和数据库放在不同主机上？
下面我们构造一个环境来测试下：
环境：
数据库在192.168.0.81上（RHEL5)
listener在192.168.0.80上(RHEL5)
client在192.168.0.52上(solaris10）

1，在数据库修改remote_listener参数：

SQL> alter system set service_names=’test’;
 SQL> alter system set remote_listener=’test’;
 alter system set remote_listener=’test’ *
 ERROR at line 1:
 ORA-02097: parameter cannot be modified because specified value is invalid
 ORA-00119: invalid specification for system parameter REMOTE_LISTENER
 ORA-00132: syntax error or unresolved network name ‘test’

在tnsnames.ora里加入test条目重新运行：

vi /u01/app/oracle/product/10.2.3/av_1/network/admin/tnsnames.or
 test =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.80)(PORT = 1521))
 )
 ) SQL> alter system set remote_listener=test;
 System altered. SQL> alter system register;
 System altered.

然后查看本地listener和远程listener已经注册test服务：
本地：

[oracle@avagent ~]$ lsnrctl status
 LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 12-DEC-2011 21:02:53
 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 09-DEC-2011 19:34:58
 Uptime 3 days 1 hr. 27 min. 54 sec
 Trace Level off
 Security ON: Local OS Authentication
 SNMP OFF
 Listener Parameter File /u01/app/oracle/product/10.2.3/av_1/network/admin/listener.ora
 Listener Log File /u01/app/oracle/diag/tnslsnr/avagent/listener/alert/log.xml
 Listening Endpoints Summary…
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=avagent.com)(PORT=1521)))
 Services Summary…
 Service “orcl.com” has 1 instance(s).
 Instance “orcl”, status READY, has 1 handler(s) for this service…
 Service “orclXDB.com” has 1 instance(s).
 Instance “orcl”, status READY, has 1 handler(s) for this service…
 Service “test.com” has 1 instance(s).
 Instance “orcl”, status READY, has 1 handler(s) for this service…
 The command completed successfully

远程：

[oracle@avserver admin]$ lsnrctl status
 LSNRCTL for Linux: Version 10.2.0.3.0 – Production on 15-DEC-2011 20:42:33
 Copyright (c) 1991, 2006, Oracle. All rights reserved.
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
 STATUS of the LISTENER
 ————————
 Alias LISTENER
 Version TNSLSNR for Linux: Version 10.2.0.3.0 – Production
 Start Date 15-DEC-2011 20:20:03
 Uptime 0 days 0 hr. 22 min. 29 sec
 Trace Level off
 Security ON: Local OS Authentication
 SNMP OFF
 Listener Parameter File /u01/app/oracle/product/10.2.3/av_1/network/admin/listener.ora
 Listener Log File /u01/app/oracle/product/10.2.3/av_1/network/log/listener.log
 Listening Endpoints Summary…
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=avserver.com)(PORT=1521)))
 Services Summary…
 Service “PLSExtProc” has 1 instance(s).
 Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
 Service “orcl.com” has 1 instance(s).
 Instance “orcl”, status READY, has 1 handler(s) for this service…
 Service “orclXDB.com” has 1 instance(s).
 Instance “orcl”, status READY, has 1 handler(s) for this service…
 Service “test.com” has 1 instance(s).
 Instance “orcl”, status READY, has 1 handler(s) for this service…
 The command completed successfully

通过lsnrctl status ，我们可以发现，数据库已经像本地和远程的监听注册了test服务。

下面我们尝试使用远程监听连接数据库：
首先在远程监听所在主机上添加tnsnames.ora

test=
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.80)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = test.com)
 )
 )

这时我们注意到，test的host使用的远程监听的ip地址
然后尝试连接：

[oracle@avserver admin]$ sqlplus system/oracle@test SQL*Plus: Release 10.2.0.3.0 – Production on Thu Dec 15 20:23:43 2011
 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. ERROR:
 ORA-12520: TNS:listener could not find available handler for requested type of server

结果是无法连接，即使远程监听已经注册test服务
我们在solaris再次尝试连接：

test=
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.80)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = test.com)
 )
 )
 -bash-3.00$ sqlplus system/oracle@test SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 15 20:55:21 2011
 Copyright (c) 1982, 2011, Oracle. All rights reserved.
 ERROR:
 ORA-12516: TNS:listener could not find available handler with matching protocol stack

虽然错误号不一致，但仍然连不进去数据库。
使用lsnrctl service命令查看服务状态：

[oracle@avserver admin]$ lsnrctl service
 LSNRCTL for Linux: Version 10.2.0.3.0 – Production on 15-DEC-2011 21:07:54
 Copyright (c) 1991, 2006, Oracle. All rights reserved.
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
 Services Summary…
 Service “PLSExtProc” has 1 instance(s).
 Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
 Handler(s):
 “DEDICATED” established:0 refused:0
 LOCAL SERVER
 Service “orcl.com” has 1 instance(s).
 Instance “orcl”, status READY, has 1 handler(s) for this service…
 Handler(s):
 “DEDICATED” established:0 refused:0 state:blocked
 REMOTE SERVER
 (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.81)(PORT=1521))
 Service “orclXDB.com” has 1 instance(s).
 Instance “orcl”, status READY, has 1 handler(s) for this service…
 Handler(s):
 “D000″ established:0 refused:0 current:0 max:1022 state:ready
 DISPATCHER <machine: avagent.com, pid: 5590>
 (ADDRESS=(PROTOCOL=tcp)(HOST=avagent.com)(PORT=34266))
 Service “test.com” has 1 instance(s).
 Instance “orcl”, status READY, has 1 handler(s) for this service…
 Handler(s):
 “DEDICATED” established:0 refused:0 state:blocked
 REMOTE SERVER
 (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.81)(PORT=1521))
 The command completed successfully

注意到test.com的状态是blocked。
根据tns错误号和服务状态为：blocked。根据普通情况，我们可以定位到是数据库的local_listener未设置，
设置本地local_listener：

SQL> alter system set local_listener= ‘(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.81)(PORT=1521))’ ;

然后确保本地监听已经开启：

[oracle@avagent ~]$ lsnrctl status
 LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 12-DEC-2011 22:07:06
 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 12-DEC-2011 22:06:57
 Uptime 0 days 0 hr. 0 min. 9 sec
 Trace Level off
 Security ON: Local OS Authentication
 SNMP OFF
 Listener Parameter File /u01/app/oracle/product/10.2.3/av_1/network/admin/listener.ora
 Listener Log File /u01/app/oracle/diag/tnslsnr/avagent/listener/alert/log.xml
 Listening Endpoints Summary…
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=avagent.com)(PORT=1521)))
 Services Summary…
 Service “orcl.com” has 1 instance(s).
 Instance “orcl”, status READY, has 1 handler(s) for this service…
 Service “orclXDB.com” has 1 instance(s).
 Instance “orcl”, status READY, has 1 handler(s) for this service…
 Service “test.com” has 1 instance(s).
 Instance “orcl”, status READY, has 1 handler(s) for this service…
 The command completed successfully
 [oracle@avagent ~]$

然后在remote端查看服务状态：

[oracle@avserver admin]$ lsnrctl service
 LSNRCTL for Linux: Version 10.2.0.3.0 – Production on 15-DEC-2011 21:21:43
 Copyright (c) 1991, 2006, Oracle. All rights reserved.
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
 Services Summary…
 Service “PLSExtProc” has 1 instance(s).
 Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
 Handler(s):
 “DEDICATED” established:0 refused:0
 LOCAL SERVER
 Service “orcl.com” has 1 instance(s).
 Instance “orcl”, status READY, has 1 handler(s) for this service…
 Handler(s):
 “DEDICATED” established:2 refused:0 state:ready
 REMOTE SERVER
 (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.81)(PORT=1521))
 Service “orclXDB.com” has 1 instance(s).
 Instance “orcl”, status READY, has 1 handler(s) for this service…
 Handler(s):
 “D000″ established:0 refused:0 current:0 max:1022 state:ready
 DISPATCHER <machine: avagent.com, pid: 5590>
 (ADDRESS=(PROTOCOL=tcp)(HOST=avagent.com)(PORT=34266))
 Service “test.com” has 1 instance(s).
 Instance “orcl”, status READY, has 1 handler(s) for this service…
 Handler(s):
 “DEDICATED” established:2 refused:0 state:ready
 REMOTE SERVER
 (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.81)(PORT=1521))
 The command completed successfully

发现此时test.com的状态已经变为ready
再次在远程listener节点上尝试通过remote_listener连接数据库：

[oracle@avserver admin]$ sqlplus system/oracle@test SQL*Plus: Release 10.2.0.3.0 – Production on Thu Dec 15 21:18:59 2011
 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select instance_name,status from v$instance;
 INSTANCE_NAME STATUS
 —————- ————
 orcl OPEN SQL>

再次再solaris上尝试通过remote_listener连接数据库

-bash-3.00$ sqlplus system/oracle@test SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 15 21:19:36 2011
 Copyright (c) 1982, 2011, Oracle. All rights reserved.
 Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select instance_name,status from v$instance;
 INSTANCE_NAME STATUS
 —————- ————
 orcl OPEN

至此，我们实现了通过远程监听连接数据库的测试：

限制：
使用remote_listener连接数据库时，本地监听也要状态正常。单独remote_listener无法连接到数据库，这使得remote_listener的作用非常鸡肋，他无法作为一个独立的监听单独存在，因为他会把收到的监听请求发生给本地监听。因此无法作为容灾监听器或者负载均衡监听器。
但remote_listener也有其作用，在remote_listener和数据库之间可以使用防火墙保证网络安全。

以上均为个人总结，并未参考任何oracle note，仅作测试用途。

问题：
在数据库向远程节点注册时，remote的listener.log出现如下报错：

Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.80)(PORT=1521)))
 15-DEC-2011 21:29:39 * service_register * orcl * 12542
 TNS-12542: TNS:address already in use
 TNS-12560: TNS:protocol adapter error
 TNS-00512: Address already in use
 Linux Error: 98: Address already in use

通过查找metalink，找到解决方案：
Listener Fails to Start on Linux, Errors with TNS-12542, Linux Error: 98: Address already in use [ID 301014.1]
在listener.ora使用ip代替主机名即可。
重新进行远程节点注册，错误消失：

TNSLSNR for Linux: Version 10.2.0.3.0 – Production on 15-DEC-2011 21:36:14
 Copyright (c) 1991, 2006, Oracle. All rights reserved.
 System parameter file is /u01/app/oracle/product/10.2.3/av_1/network/admin/listener.ora
 Log messages written to /u01/app/oracle/product/10.2.3/av_1/network/log/listener.log
 Trace information written to /u01/app/oracle/product/10.2.3/av_1/network/trace/listener.trc
 Trace level is currently 0
 Started with pid=7219
 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.80)(PORT=1521)))
 Listener completed notification to CRS on start TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
 WARNING: Subscription for node down event still pending
 15-DEC-2011 21:36:14 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=avserver.com)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169870080)) * status * 0 15-DEC-2011 21:36:45 * service_register * orcl * 0
 15-DEC-2011 21:36:54 * service_update * orcl * 0
 15-DEC-2011 21:37:00 * service_update * orcl * 0
 15-DEC-2011 21:37:09 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=test.com)(CID=(PROGRAM=sqlplus)(HOST=avserver.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.80)(PORT=33252)) * establish * test.com * 0

但该问题与限制无关。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。