新生儿早教音乐歌单:使用remote_listener实现数据库与监听器分离 | HelloDML

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

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

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
(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
(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
但该问题与限制无关。

本文固定链接:http://www.hellodml.com/2011/12/%e4%bd%bf%e7%94%a8remote_listener%e5%ae%9e%e7%8e%b0%e6%95%b0%e6%8d%ae%e5%ba%93%e4%b8%8e%e7%9b%91%e5%90%ac%e5%99%a8%e5%88%86%e7%a6%bb/ | HelloDML