mysql实体监听器_使用remote_listener实现数据库与监听器分离 | HelloDML

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 invalidORA-00119: invalid specification for system parameter REMOTE_LISTENERORA-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 statusLSNRCTL for Linux: Version 11.2.0.1.0 – Production on 12-DEC-2011 21:02:53Copyright (c) 1991, 2009, Oracle. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))STATUS of the LISTENER————————Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.1.0 – ProductionStart Date 09-DEC-2011 19:34:58Uptime 3 days 1 hr. 27 min. 54 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/oracle/product/10.2.3/av_1/network/admin/listener.oraListener Log File /u01/app/oracle/diag/tnslsnr/avagent/listener/alert/log.xmlListening 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 statusLSNRCTL for Linux: Version 10.2.0.3.0 – Production on 15-DEC-2011 20:42:33Copyright (c) 1991, 2006, Oracle. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))STATUS of the LISTENER————————Alias LISTENERVersion TNSLSNR for Linux: Version 10.2.0.3.0 – ProductionStart Date 15-DEC-2011 20:20:03Uptime 0 days 0 hr. 22 min. 29 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/oracle/product/10.2.3/av_1/network/admin/listener.oraListener Log File /u01/app/oracle/product/10.2.3/av_1/network/log/listener.logListening 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.oratest=(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@testSQL*Plus: Release 10.2.0.3.0 – Production on Thu Dec 15 20:23:43 2011Copyright (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@testSQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 15 20:55:21 2011Copyright (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 serviceLSNRCTL for Linux: Version 10.2.0.3.0 – Production on 15-DEC-2011 21:07:54Copyright (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:0LOCAL SERVERService “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:blockedREMOTE 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:readyDISPATCHER (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:blockedREMOTE 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:06Copyright (c) 1991, 2009, Oracle. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))STATUS of the LISTENER————————Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.1.0 – ProductionStart Date 12-DEC-2011 22:06:57Uptime 0 days 0 hr. 0 min. 9 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/oracle/product/10.2.3/av_1/network/admin/listener.oraListener Log File /u01/app/oracle/diag/tnslsnr/avagent/listener/alert/log.xmlListening 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 serviceLSNRCTL for Linux: Version 10.2.0.3.0 – Production on 15-DEC-2011 21:21:43Copyright (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:0LOCAL SERVERService “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:readyREMOTE 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:readyDISPATCHER (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:readyREMOTE 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@testSQL*Plus: Release 10.2.0.3.0 – Production on Thu Dec 15 21:18:59 2011Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit ProductionWith 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@testSQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 15 21:19:36 2011Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit ProductionWith 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 * 12542TNS-12542: TNS:address already in useTNS-12560: TNS:protocol adapter errorTNS-00512: Address already in useLinux 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:14Copyright (c) 1991, 2006, Oracle. All rights reserved.System parameter file is /u01/app/oracle/product/10.2.3/av_1/network/admin/listener.oraLog messages written to /u01/app/oracle/product/10.2.3/av_1/network/log/listener.logTrace information written to /u01/app/oracle/product/10.2.3/av_1/network/trace/listener.trcTrace level is currently 0Started with pid=7219Listening 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 CODEWARNING: Subscription for node down event still pending15-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 * 015-DEC-2011 21:36:54 * service_update * orcl * 015-DEC-2011 21:37:00 * service_update * orcl * 015-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但该问题与限制无关。