[20161227]打开多个监听端口.txt
--安全等保的需求,需要在1台数据库打开2个监听端口,并且还不能是1521缺省端口.看了一些文档,做一些记录.
--参考连接:http://blog.itpub.net/267265/viewspace-2083455/
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--设置打开2个端口,15210,15211.
2.修改listener.ora如下:
--注:先stop监听,再修改.
$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
#SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU=32767)
(GLOBAL_DBNAME = book)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
(SID_NAME = book)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 15210))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 15210))
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 15211))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 15211))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
--重启监听就ok了.
$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 27-DEC-2016 08:31:36
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=15210)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 27-DEC-2016 08:29:33
Uptime 0 days 0 hr. 2 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/gxqyydg4/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=15210)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.78)(PORT=15210)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=15211)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.78)(PORT=15211)))
Services Summary...
Service "book" has 1 instance(s).
Instance "book", status UNKNOWN, has 1 handler(s) for this service...
~~~~~~~~~~~~~~~~~~~UNKBOWN 说明是静态监听.
The command completed successfully
--使用ezconnect连接测试: sqlplus scott/book@192.168.100.78:15211/book ,通过.
# lsof -i -P -n | grep 1521| grep LISTEN
tnslsnr 1997 oracle 8u IPv4 21318788 0t0 TCP 127.0.0.1:15210 (LISTEN)
tnslsnr 1997 oracle 11u IPv4 21318790 0t0 TCP 192.168.100.78:15210 (LISTEN)
tnslsnr 1997 oracle 12u IPv4 21318791 0t0 TCP 127.0.0.1:15211 (LISTEN)
tnslsnr 1997 oracle 13u IPv4 21318792 0t0 TCP 192.168.100.78:15211 (LISTEN)
--这样最简单的.但是需要配置静态监听.如果注解前面的静态监听呢?
3.无静态监听:
$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
#SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
# SID_LIST_LISTENER =
# (SID_LIST =
# (SID_DESC =
# (SDU=32767)
# (GLOBAL_DBNAME = book)
# (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
# (SID_NAME = book)
# )
# )
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 15210))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 15210))
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 15211))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 15211))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
--重启监听.
$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 27-DEC-2016 08:44:48
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=15210)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 27-DEC-2016 08:43:16
Uptime 0 days 0 hr. 1 min. 32 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/gxqyydg4/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=15210)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.78)(PORT=15210)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=15211)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.78)(PORT=15211)))
The listener supports no services
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The command completed successfully
--注意后面2行,The listener supports no services.即使你执行alter system register ;也一样,因为缺省是注册1521端口.而服务端
--没有开放1521端口,无效.这个时候就需要修改参数local_listener.
4.修改参数local_listener:
--有2种方式:一种直接写入spfile中,这样写入内容稍微长一点:
alter system set local_listener="(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 15210)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 15210)) (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 15211)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 15211)))" scope=memory;
--我遇到如下提示:
*
ERROR at line 1:
ORA-00972: identifier is too long
--先不注册127.0.0.1地址看看.
alter system set local_listener="(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 15210))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 15211))
)" scope=memory;
$ oerr ora 972
00972, 00000, "identifier is too long"
// *Cause: An identifier with more than 30 characters was specified.
// *Action: Specify at most 30 characters.
--昏限制30个字符.我写成如下:
SYS@book> alter system set local_listener="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.78)(PORT=15210))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.78)(PORT=15211)))" scope=memory;
System altered.
--我的测试写成1行,里面没有空格就ok了.但是也超过30个字符限制啊,写成如下也报错.
alter system set local_listener="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=15210))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.78)(PORT=15210))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=15211))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.78)(PORT=15211)))" scope=memory;
SYS@book> show parameter local_listener
NAME TYPE VALUE
-------------- ------ ----------------------------------------------------------------------------------------------------
local_listener string (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.78)(PORT=15210))(ADDRESS=(PROTOCOL=TCP)(HOST=1
92.168.100.78)(PORT=15211)))
$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 27-DEC-2016 09:03:06
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=15210)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 27-DEC-2016 08:48:33
Uptime 0 days 0 hr. 14 min. 33 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/gxqyydg4/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=15210)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.78)(PORT=15210)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=15211)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.78)(PORT=15211)))
Services Summary...
Service "book" has 1 instance(s).
Instance "book", status READY, has 2 handler(s) for this service...
~~~~~~~~~~~~~~=> READ 表示动态监听注册.
Service "bookXDB" has 1 instance(s).
Instance "book", status READY, has 1 handler(s) for this service...
The command completed successfully
--可以发现现在动态注册ok.实际上还有另外的修改方式把以上内容写入tnsnames.ora文件中.看下面的测试.
5.修改参数local_listener:
--在tnsnames.ora文件加入如下内容:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 15210))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 15210))
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 15211))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 15211))
)
)
--LISTENER 可以换自己喜欢的名字.
SYS@book> alter system set local_listener="LISTENER" scope=memory;
System altered.
--使用ezconnect连接测试: sqlplus scott/book@192.168.100.78:15211/book ,通过.
