[20161227]打开多个监听端口.txt

[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 ,通过.

上一篇:临床安全监测 甲骨文Argus 8.0解决方案


下一篇:MFC单文档应用程序显示图像