同一实例多个监听的问题

chunchun2012

数据库服务器上有两块网卡，IP分别为
192.168.11.23
192.168.12.23
因为EM配置的原因，需要对同一个实例配置 两个监听，
现listener.ora文件配置如下
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u0/oracle/product/10g)
      (PROGRAM = extproc)
    )
  )

list1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.23)(PORT = 1521))
    )
  )

list2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.23)(PORT = 1522))
    )
  )
配置后启动监听
[oracle@OCPDemo admin]$ lsnrctl start list1

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 26-APR-2012 19:29:28

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /u0/oracle/product/10g/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u0/oracle/product/10g/network/admin/listener.ora
Log messages written to /u0/oracle/product/10g/network/log/list1.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.23)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.11.23)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     list1
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                26-APR-2012 19:29:29
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u0/oracle/product/10g/network/admin/listener.ora
Listener Log File         /u0/oracle/product/10g/network/log/list1.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.23)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@OCPDemo admin]$ lsnrctl start list2

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 26-APR-2012 19:29:36

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /u0/oracle/product/10g/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u0/oracle/product/10g/network/admin/listener.ora
Log messages written to /u0/oracle/product/10g/network/log/list2.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.12.23)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.12.23)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     list2
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                26-APR-2012 19:29:36
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u0/oracle/product/10g/network/admin/listener.ora
Listener Log File         /u0/oracle/product/10g/network/log/list2.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.12.23)(PORT=1522)))
The listener supports no services
The command completed successfully
启动后查看监听状态如下
[oracle@OCPDemo admin]$ lsnrctl status list1

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 26-APR-2012 19:30:12

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.11.23)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     list1
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                26-APR-2012 19:29:29
Uptime                    0 days 0 hr. 0 min. 43 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u0/oracle/product/10g/network/admin/listener.ora
Listener Log File         /u0/oracle/product/10g/network/log/list1.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.11.23)(PORT=1521)))
Services Summary...
Service "OCPDemo" has 1 instance(s).
  Instance "OCPDemo", status READY, has 1 handler(s) for this service...
Service "OCPDemoXDB" has 1 instance(s).
  Instance "OCPDemo", status READY, has 1 handler(s) for this service...
Service "OCPDemo_XPT" has 1 instance(s).
  Instance "OCPDemo", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@OCPDemo admin]$ lsnrctl status list2

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 26-APR-2012 19:30:15

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.12.23)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     list2
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                26-APR-2012 19:29:36
Uptime                    0 days 0 hr. 0 min. 38 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u0/oracle/product/10g/network/admin/listener.ora
Listener Log File         /u0/oracle/product/10g/network/log/list2.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.12.23)(PORT=1522)))
The listener supports no services
The command completed successfully
请问红色部分是什么意思？
这么配置后，监听运行正常么？

chunchun2012

谢谢，我昨天自己捣腾，后来也发现了这个问题，最后得出的方法与maclean的方法是一样的。呵呵！
方法一：
SID_LIST_LIST1 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u0/oracle/product/10g)
      (PROGRAM = extproc)
    )
    (SID_DESC =
        (GLOBAL_DBNAME = OCPDemo)
        (ORACLE_HOME = /u0/oracle/product/10g)
        (SID_NAME = OCPDemo)
    )
  )
SID_LIST_LIST2 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u0/oracle/product/10g)
      (PROGRAM = extproc)
    )
    (SID_DESC =
        (GLOBAL_DBNAME = OCPDemo)
        (ORACLE_HOME = /u0/oracle/product/10g)
        (SID_NAME = OCPDemo)
    )
  )

LIST1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.23)(PORT = 1521))
      (ADDRESS = (protocol = IPC)(key  = extproc))
    )
  )
LIST2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.23)(PORT = 1522))
      (ADDRESS = (protocol = IPC)(key  = extproc_1))
    )
  )
方法二：
SID_LIST_LIST1 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u0/oracle/product/10g)
      (PROGRAM = extproc)
    )
    (SID_DESC =
        (GLOBAL_DBNAME = OCPDemo)
        (ORACLE_HOME = /u0/oracle/product/10g)
        (SID_NAME = OCPDemo)
    )
  )


LIST1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.23)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.23)(PORT = 1522))
      (ADDRESS = (protocol = IPC)(key  = extproc))
    )
  )

方法一麻烦一点，但是配置完以后，192.168.11.23和192.168.12.23两个IP可以独立监听，相互不影响，方法二简单一点，但是配置完以后，两个IP上的监听只能同时启动或停止。

Maclean Liu(刘相兵

这是 discuz 6的bug ，尽可能在文本编辑器 中 编辑好内容再贴出来吧

yanshoupeng

在tnsnames.ora里增加

LISTENERS =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.23)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.24)(PORT = 1522))

设置实例的local_listener参数为LISTENERS

miloluo

@Maclean：
建议下，我原来编辑的格式会在预览后，看不到code tag以为是不起作用，后面发出来了才看到。
并且所有文字的格式会都连成一条。字大于1000还有限制，以后回的时候，还得估算下字个数，看需要分几段。
浏览器使用的是firefox 12。
谢谢。

miloluo

4. listener.ora的文件里加入sid为监听加入sid list
加入如下代码到listener.ora里

1. SID_LIST_LISTENER_[b]RAC11[/b] =
   
2.   (SID_LIST =
   
3.     (SID_DESC =
   
4.       (SID_NAME = [b]racdb1[/b])
   
5.       (ORACLE_HOME = /opt/oracle/product/10.2.0/db1)
   
6.       (PROGRAM = extproc)
   
7.     )
   
8.   )

复制代码

5. 重新启动下监听：

1. [oracle@rac1 ~]$ lsnrctl start LISTENER_RAC11
   
2. 
   
3. LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 27-APR-2012 14:21:21
   
4. 
   
5. Copyright (c) 1991, 2007, Oracle.  All rights reserved.
   
6. 
   
7. Starting /opt/oracle/product/10.2.0/db1/bin/tnslsnr: please wait...
   
8. 
   
9. TNSLSNR for Linux: Version 10.2.0.4.0 - Production
   
10. System parameter file is /opt/oracle/product/10.2.0/db1/network/admin/listener.ora
    
11. Log messages written to /opt/oracle/product/10.2.0/db1/network/log/listener_rac11.log
    
12. Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=100.100.100.1)(PORT=1522)))
    
13. 
    
14. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=100.100.100.1)(PORT=1522)(IP=FIRST)))
    
15. STATUS of the LISTENER
    
16. ------------------------
    
17. Alias                     LISTENER_RAC11
    
18. Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
    
19. Start Date                27-APR-2012 14:21:21
    
20. Uptime                    0 days 0 hr. 0 min. 0 sec
    
21. Trace Level               off
    
22. Security                  ON: Local OS Authentication
    
23. SNMP                      OFF
    
24. Listener Parameter File   /opt/oracle/product/10.2.0/db1/network/admin/listener.ora
    
25. Listener Log File         /opt/oracle/product/10.2.0/db1/network/log/listener_rac11.log
    
26. Listening Endpoints Summary...
    
27.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=100.100.100.1)(PORT=1522)))
    
28. Services Summary...
    
29. Service "racdb1" has 1 instance(s).
    
30.   Instance "racdb1", status UNKNOWN, has 1 handler(s) for this service...
    
31. The command completed successfully

复制代码

6. 发现100.100.100.1：1522口已经监听。

1. [oracle@rac1 ~]$ netstat -antpl | grep 100.100.100.1
   
2. (Not all processes could be identified, non-owned process info
   
3. will not be shown, you would have to be root to see it all.)
   
4. tcp        0      0 100.100.100.1:49895         0.0.0.0:*                   LISTEN      -                  
   
5. tcp        0      0 100.100.100.1:49896         0.0.0.0:*                   LISTEN      -                  
   
6. tcp        0      0 100.100.100.1:49897         0.0.0.0:*                   LISTEN      3992/evmd.bin      
   
7. tcp        0      0 100.100.100.1:49898         0.0.0.0:*                   LISTEN      3992/evmd.bin      
   
8. [b]tcp        0      0 100.100.100.1:1522          0.0.0.0:*                   LISTEN      11212/tnslsnr[/b]

复制代码

我做了这个实验，发现这么做事行的。 有什么不足，大家接着补充。
谢谢。

miloluo

1. “The listener supports no services”

复制代码

这个话的意思是，监听没有检测到相应的服务。

自己做了个实验，发现需要写一个给listner写一个sid_list。

过程如下：

说明下，这是在一个rac环境上(RHEL 5.4 64bit, Oracle 10.2.0.4 )的一节点做的实验。

ip地址如下， 要做的是把100.100.100.1加入监听里：

1. [oracle@rac1 ~]$ cat /etc/hosts
   
2. # Do not remove the following line, or various programs
   
3. # that require network functionality will fail.
   
4. 127.0.0.1       localhost.localdomain localhost
   
5. ::1             localhost6.localdomain6 localhost6
   
6. 
   
7. 192.168.0.31   rac1
   
8. 100.100.100.1  rac1-priv
   
9. 192.168.0.231  rac1-vip

复制代码

1. 首先，查看现有监听情况：

1. [oracle@rac1 ~]$ lsnrctl status LISTENER_RAC1
   
2. 
   
3. LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 27-APR-2012 14:16:56
   
4. 
   
5. Copyright (c) 1991, 2007, Oracle.  All rights reserved.
   
6. 
   
7. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1521)(IP=FIRST)))
   
8. STATUS of the LISTENER
   
9. ------------------------
   
10. Alias                     LISTENER_RAC1
    
11. Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
    
12. Start Date                27-APR-2012 14:16:34
    
13. Uptime                    0 days 0 hr. 0 min. 21 sec
    
14. Trace Level               off
    
15. Security                  ON: Local OS Authentication
    
16. SNMP                      OFF
    
17. Listener Parameter File   /opt/oracle/product/10.2.0/db1/network/admin/listener.ora
    
18. Listener Log File         /opt/oracle/product/10.2.0/db1/network/log/listener_rac1.log
    
19. Listening Endpoints Summary...
    
20.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.231)(PORT=1521)))
    
21.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.31)(PORT=1521)))
    
22. Services Summary...
    
23. Service "PLSExtProc" has 1 instance(s).
    
24.   Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    
25. Service "racdb" has 1 instance(s).
    
26.   Instance "racdb1", status READY, has 2 handler(s) for this service...
    
27. Service "racdbXDB" has 1 instance(s).
    
28.   Instance "racdb1", status READY, has 1 handler(s) for this service...
    
29. Service "racdb_XPT" has 1 instance(s).
    
30.   Instance "racdb1", status READY, has 2 handler(s) for this service...
    
31. The command completed successfully
    
32. [oracle@rac1 ~]$ netstat -antpl | grep :15
    
33. (Not all processes could be identified, non-owned process info
    
34. will not be shown, you would have to be root to see it all.)
    
35. tcp        0      0 192.168.0.31:1521           0.0.0.0:*                   LISTEN      9298/tnslsnr        
    
36. tcp        0      0 192.168.0.231:1521          0.0.0.0:*                   LISTEN      9298/tnslsnr        
    
37. tcp        0      0 192.168.0.231:63769         192.168.0.231:1521          ESTABLISHED 5231/ora_pmon_racdb
    
38. tcp        0      0 192.168.0.231:63766         192.168.0.231:1521          TIME_WAIT   -                  
    
39. tcp        0      0 192.168.0.231:63761         192.168.0.231:1521          TIME_WAIT   -                  
    
40. tcp        0      0 192.168.0.231:1521          192.168.0.231:63769         ESTABLISHED 9298/tnslsnr        
    
41. tcp        0      0 192.168.0.231:1521          192.168.0.231:63771         TIME_WAIT   -                  
    
42. tcp        0      0 192.168.0.231:1521          192.168.0.231:11566         TIME_WAIT   -                  
    
43. tcp        0      0 192.168.0.31:1521           192.168.0.31:50224          ESTABLISHED 9298/tnslsnr        
    
44. tcp        0      0 192.168.0.31:50224          192.168.0.31:1521           ESTABLISHED 5231/ora_pmon_racdb
    
45. tcp        0      0 192.168.0.31:1521           192.168.0.31:24380          TIME_WAIT   -                  
    
46. tcp        0      0 192.168.0.31:1521           192.168.0.31:24378          TIME_WAIT   -                  

复制代码

现有监听会监听192.168.0.31和192.168.0.231的1521端口。





2. 此时修改listener.ora文件，加入监听名为“LISTENER_RAC11”
加入如下内容：

1. LISTENER_RAC11 =
   
2.   (DESCRIPTION_LIST =
   
3.     (DESCRIPTION =
   
4.       (ADDRESS = (PROTOCOL = TCP)(HOST = 100.100.100.1)(PORT = 1522)(IP = FIRST))
   
5.     )
   
6.   )

复制代码

3. 启动该监听

1. [oracle@rac1 ~]$ lsnrctl start LISTENER_RAC11
   
2. 
   
3. LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 27-APR-2012 14:17:26
   
4. 
   
5. Copyright (c) 1991, 2007, Oracle.  All rights reserved.
   
6. 
   
7. Starting /opt/oracle/product/10.2.0/db1/bin/tnslsnr: please wait...
   
8. 
   
9. TNSLSNR for Linux: Version 10.2.0.4.0 - Production
   
10. System parameter file is /opt/oracle/product/10.2.0/db1/network/admin/listener.ora
    
11. Log messages written to /opt/oracle/product/10.2.0/db1/network/log/listener_rac11.log
    
12. Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=100.100.100.1)(PORT=1522)))
    
13. 
    
14. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=100.100.100.1)(PORT=1522)(IP=FIRST)))
    
15. STATUS of the LISTENER
    
16. ------------------------
    
17. Alias                     LISTENER_RAC11
    
18. Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
    
19. Start Date                27-APR-2012 14:17:26
    
20. Uptime                    0 days 0 hr. 0 min. 0 sec
    
21. Trace Level               off
    
22. Security                  ON: Local OS Authentication
    
23. SNMP                      OFF
    
24. Listener Parameter File   /opt/oracle/product/10.2.0/db1/network/admin/listener.ora
    
25. Listener Log File         /opt/oracle/product/10.2.0/db1/network/log/listener_rac11.log
    
26. Listening Endpoints Summary...
    
27.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=100.100.100.1)(PORT=1522)))
    
28. The listener supports no services
    
29. The command completed successfully
    
30. [oracle@rac1 ~]$ sqlplus  / as sysdba
    
31. 
    
32. SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 27 14:17:31 2012
    
33. 
    
34. Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
    
35. 
    
36. 
    
37. Connected to:
    
38. Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    
39. With the Partitioning, Real Application Clusters, OLAP, Data Mining
    
40. and Real Application Testing options
    
41. 
    
42. SYS#10g@racdb1>alter system register;
    
43. 
    
44. System altered.
    
45. 
    
46. SYS#10g@racdb1>exit
    
47. Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    
48. With the Partitioning, Real Application Clusters, OLAP, Data Mining
    
49. and Real Application Testing options
    
50. [oracle@rac1 ~]$
    
51. [oracle@rac1 ~]$
    
52. [oracle@rac1 ~]$
    
53. 
    
54. [oracle@rac1 ~]$ lsnrctl status LISTENER_RAC11
    
55. 
    
56. LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 27-APR-2012 14:17:50
    
57. 
    
58. Copyright (c) 1991, 2007, Oracle.  All rights reserved.
    
59. 
    
60. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=100.100.100.1)(PORT=1522)(IP=FIRST)))
    
61. STATUS of the LISTENER
    
62. ------------------------
    
63. Alias                     LISTENER_RAC11
    
64. Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
    
65. Start Date                27-APR-2012 14:17:26
    
66. Uptime                    0 days 0 hr. 0 min. 24 sec
    
67. Trace Level               off
    
68. Security                  ON: Local OS Authentication
    
69. SNMP                      OFF
    
70. Listener Parameter File   /opt/oracle/product/10.2.0/db1/network/admin/listener.ora
    
71. Listener Log File         /opt/oracle/product/10.2.0/db1/network/log/listener_rac11.log
    
72. Listening Endpoints Summary...
    
73.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=100.100.100.1)(PORT=1522)))
    
74. The listener supports no services
    
75. The command completed successfully

复制代码

出现了和你一样的情况。

[ 本帖最后由 miloluo 于 2012-4-27 14:57 编辑 ]