Oracle配置多个监听

2022-07-16 21:46:46

本文参考了官方帮助文档：
Oracle Database Net Services Administrator’s Guide-》9 Configuring and Administering Oracle Net Listener章节：

主要知识点有如下：

动态监听

This dynamic registration feature is called service registration. The registration is performed by the PMON process, an instance background process of each database instance that is configured in the database initialization parameter file. Dynamic service registration does not require any manual configuration in the listener.ora file
A process monitor database process that performs process recovery when a user process fails. PMON is responsible for cleaning up the cache and freeing resources that the process was using. PMON also checks on dispatcher and server processes and restarts them if they have failed. As a part of service registration, PMON registers instance information with the listener。
Service registration offers the following benefits:
■Simplified configuration
Service registration reduces the need for the SID_LIST_listener_name parameter setting, which specifies information about the databases served by the listener, in the listener.ora file.
■Connect-time failover
Because the listener always monitors the state of the instances, service registration facilitates automatic failover of a client connect request to a different instance if one instance is down.
■Connection load balancing
Service registration enables the listener to forward client connect requests to the least-loaded instance and dispatcher or dedicated server. Service registration balances the load across the service handlers and nodes.
■High-availability for Oracle Real Application Clusters and Oracle Data Guard

动态注册也叫作服务器注册，该注册是由PMON（PROCESS MONITOR）来完成的，它是实例的一个后台进程，在数据库的初始化参数文件中配置的，动态注册不需要再listener.ora文件进行任何手动配置，完全是自动的。
一个数据库的进程监控器主要用于当用户进程失败后，进行进程的恢复操作，PMON用于清楚进行使用的缓冲以及释放进程所使用的资源，PMON也检查分发器和服务器进程，并且当它们失败后，重启上述进程，作为服务器注册的一部分，PMON将实例注册到监听中。
服务端配置主要有以下几个优点：
1、简化配置
减少了在SID_LIST_listener的配置
2、连接故障转移
由于listener监听实例的状态，动态注册发现一个实例down掉后，会自动的进行故障转移
3、连接时，均衡负载
服务器注册可以使监听将请求分发到压力最小的实例、分发器、专用进程，服务器注册器通过节点和服务处理器来实现均衡负载
4、RAC和Oracle DG的高可用性

静态监听

为什么要配置静态监听：

Configuration of static service information is necessary if you require remote database startup from a tool other than Oracle Enterprise Manager, or you have Oracle Database releases earlier than Oracle8i.

如果你想通过工具远程启动数据库，而不是通过OEM，或者你的数据库版本在Oracle 8I之前，你必须要配置静态监听。

下面是配置静态监听的方法：

下图是静态监听必须包含的参数：

在listener中增加如下代码：

    (SID_DESC =
       (SID_NAME = ORCL )
       (ORACLE_HOME = C:\app\Dyj\product\11.2.0\dbhome_1) 
       (GLOBAL_DBNAME = ORCL)
     )

如下图所示：

重启监听后，静态监听注册成功，下方出现unkown的标识（表示静态监听注册成功）：

这时候就可以使用如下的方式连接数据库，数据库连接成功，此时是使用静态监听连接数据库的，因为如上图所示，还不到60S，动态监听尚未注册警来：

C:\Users\Administrator>sqlplus sys/cape@orcl as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期日 2月 19 22:59:18 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

连接到:
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>

local_listener参数的使用

To have PMON register with a local listener that does not use TCP/IP, port 1521, configure the LOCAL_LISTENER parameter in the initialization parameter file to locate the local listener.
如果要使用PMON注册本地的监听，并且不适用TCP/IP，不使用默认端口1521，使用参数LOCAL_LISTENER来确定本地监听。
For a shared server environment, you can use the LISTENER attribute of the DISPATCHERS parameter in the initialization parameter file to register the dispatchers with a nondefault local listener. Because the LOCAL_LISTENER parameter and the LISTENER attribute enable PMON to register dispatcher information with the listener, it is not necessary to specify both the parameter and the attribute if the listener values are the same.
对于共享服务的环境下，你也可以使用初始化参数DISPATCHER参数的LISTENER参数属性，来给分发器注册一个非默认的端口，由于上述两个参数目的都是使PMON进程完成动态注册，当两个参数的值一样时，就不需要分别设置，只需要设置一个即可。
LOCAL_LISTENER is a comma-delimited list parameter. If a comma appears in the string, then the entire string must be enclosed in double quotation marks. Set the LOCAL_LISTENER parameter as follows:
ALTER SYSTEM SET LOCAL LISTENER=[“]listener_address[“][,…];
For example, if the listener address “ab,cd” is entered, then it resolves to one listener address. If the address is entered as ab,cd, then it resolves to two listener addresses, ab and cd.
For shared server connections, set the LISTENER attribute as follows:
ALTER SYSTEM SET DISPATCHERS=”(PROTOCOL=tcp)(LISTENER=listener_address)”;
In the preceding command, listener_address is resolved to the listener protocol addresses through a naming method, such as a tnsnames.ora file on the database server.

修改配置文件listener.ora文件

增加配置文件，此处一定要去掉上方拷贝的IPC协议

SID_LIST_LISTENER1 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\Dyj\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Dyj\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dongyj)(PORT = 1522))
    )
  )

修改配置文件tnsnames.ora

LISTENER_ORCL1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))

配置数据库参数值：local_listener

SQL> alter system set local_listener=LISTENER_ORCL,LISTENER_ORCL1;

系统已更改。

SQL> show parameters local_listener;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER_ORCL, LISTENER_ORCL1

启动监听，并完成动态注册

启动监听：listener

启动监听：listener1

执行动态注册命令：alter system register

listener动态监听成功

listener1动态监听成功

连接测试

在tnsnames.ora中加入一段代码，测试1522端口号：

ORCL1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

测试结果：1521端口

C:\Users\Administrator>sqlplus sys/cape@orcl as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期一 2月 20 22:07:36 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
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>

1522端口

C:\Users\Administrator>sqlplus sys/cape@orcl1 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期一 2月 20 22:08:13 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
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>

也可以使用如下方式，直接指定端口号方式，而不用服务名连接：

C:\Users\Administrator>sqlplus sys/cape@localhost:1521/orcl as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期一 2月 20 22:08:59 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
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> quit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开

C:\Users\Administrator>sqlplus sys/cape@localhost:1522/orcl as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期一 2月 20 22:09:21 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
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>

总结

alter system set local_listener=LISTENER_ORCL,LISTENER_ORCL1，其中LISTENER_ORCL,LISTENER_ORCL1的值必须在tnsnames.ora声明

声明如下：

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

LISTENER_ORCL1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))

否则会出现如下的错误；

SQL> alter system set local_listener=a;
alter system set local_listener=a
*
第 1 行出现错误:
ORA-02097: 无法修改参数, 因为指定的值无效
ORA-00119: 系统参数 LOCAL_LISTENER 的说明无效
ORA-00132: 语法错误或无法解析的网络名称 ‘A‘

但是经过测试，发现一个BUG，校验过程中，只检测第一个参数的值，例如

alter system set local_listener=listener,a;

如果listener的值存在，而a的值，不存在。

在listener.ora中只能存在一个IPC协议，否则启动的监听都指向同一个监听，测试如下：

先保留IPC协议

SID_LIST_LISTENER1 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\Dyj\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Dyj\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dongyj)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

启动监听过程中，会发现，单独启动，都指向了同一个监听

关于只能启动一个IPC协议的问题，请参考网址：
http://www.xifenfei.com/2012/06/%E5%9B%A0ipc%E5%AF%BC%E8%87%B4%E5%A4%9A%E4%B8%AA%E7%9B%91%E5%90%AC%E4%B8%8D%E8%83%BD%E6%AD%A3%E5%B8%B8%E5%90%AF%E5%8A%A8.html