Oracle动态监听和静态监听非1521端口配置

maoxiang123 2022-08-01 数据库 122 0 百度已收录

ECS 云服务器 SWAS 轻量应用服务器 RDS 云数据库 Redis 云数据库 CDN 内容分发 OSS 对象存储 SLB 负载均衡 NAT 网关 DNS 云解析 MAIL 企业邮箱 WAF 应用防火墙 DDoS 高防 SMS 短信包 MK 云市场 XIN 心选 IM 商标注册 JZ 自营建站

动态监听配置非1521端口

服务器端配置listener.ora、tnsname.ora、local_listener参数

listener.ora配置

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HISDB)(PORT = 1522))
    )
  )

tnsnames.ora 配置

test01 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST = 
        (ADDRESS = (PROTOCOL = TCP)(HOST = HISDB)(PORT = 1522))
      )
    )
  )
local_listener配置（与tnsnames.ora中设置的保持一致）

SQL> alter system set local_listener='test01';

系统已更改。

SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
local_listener                       string      test01

验证

C:\Users\Administrator>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.4.0 - Production on 31-7月 -2022 15:46:38

Copyright (c) 1991, 2013, \product\11.2.0\dbhome_1\network\admin\listener.ora
监听程序日志文件          D:\Oracle\diag\tnslsnr\HISDB\listener\alert\log.xml
监听端点概要...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=HISDB)(PORT=1522)))
服务摘要..
服务 "docare" 包含 1 个实例。
  实例 "docare", 状态 READY, 包含此服务的 1 个...
命令执行成功

静态监听配置非1521端口

服务器端配置listener.ora、tnsname.ora

listener.ora配置

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = test01)
      (ORACLE_HOME = D:\Oracle\product\11.2.0\dbhome_1)
      (SID_NAME = DOCARE)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST = 
        (ADDRESS = (PROTOCOL = TCP)(HOST = HISDB)(PORT = 1523))
      )
    )
  )

tnsnames.ora 配置

test01 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST = 
        (ADDRESS = (PROTOCOL = TCP)(HOST = HISDB)(PORT = 1523))
      )
    )
  )

验证

C:\Users\Administrator>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.4.0 - Production on 31-7月 -2022 15:53:36

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

正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=HISDB)(PORT=1523)))
LISTENER 的 STATUS
------------------------
别名                      LISTENER
版本                      TNSLSNR for 64-bit Windows: Version 11.2.0.4.0 - Production
启动日期                  31-7月 -2022 15:53:21
正常运行时间              0 天 0 小时 0 分 15 秒
跟踪级别                  off
安全性                    ON: Local OS Authentication
SNMP                      OFF
监听程序参数文件          D:\Oracle\product\11.2.0\dbhome_1\network\admin\listener.ora
监听程序日志文件          D:\Oracle\diag\tnslsnr\HISDB\listener\alert\log.xml
监听端点概要...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=HISDB)(PORT=1523)))
服务摘要..
服务 "test01" 包含 1 个实例。
  实例 "DOCARE", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
命令执行成功
示例：

说明：1521、1524端口静态监听，1522、1523端口动态监听

listener.ora配置

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = test01)
      (ORACLE_HOME = D:\Oracle\product\11.2.0\dbhome_1)
      (SID_NAME = DOCARE)
    )
  )

SID_LIST_LISTENER1524 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = test04)
      (ORACLE_HOME = D:\Oracle\product\11.2.0\dbhome_1)
      (SID_NAME = DOCARE)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST = 
        (ADDRESS = (PROTOCOL = TCP)(HOST = HISDB)(PORT = 1521))
      )
    )
  )

LISTENER1522 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST = 
        (ADDRESS = (PROTOCOL = TCP)(HOST = HISDB)(PORT = 1522))
      )
    )
  )

LISTENER1523 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST = 
        (ADDRESS = (PROTOCOL = TCP)(HOST = HISDB)(PORT = 1523))
      )
    )
  )

LISTENER1524 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST = 
        (ADDRESS = (PROTOCOL = TCP)(HOST = HISDB)(PORT = 1524))
      )
    )
  )

tnsnames.ora 配置

test01 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST = 
        (ADDRESS = (PROTOCOL = TCP)(HOST = HISDB)(PORT = 1521))
      )
    )
  )

test02 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST = 
        (ADDRESS = (PROTOCOL = TCP)(HOST = HISDB)(PORT = 1522))
      )
    )
  )

test03 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST = 
        (ADDRESS = (PROTOCOL = TCP)(HOST = HISDB)(PORT = 1523))
      )
    )
  )

test04 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST = 
        (ADDRESS = (PROTOCOL = TCP)(HOST = HISDB)(PORT = 1524))
      )
    )
  )

local_listener参数设置(动态监听)

SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
local_listener                       string      test02, test03

service_names参数设置(动态监听配置其他服务名)

SQL> show parameter service_names

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
service_names                        string      test02,test03

检查监听状态

Oracle动态监听和静态监听非1521端口配置