12c 单实例local_listener参数问题导致监听无法动态注册 一、问题现象 二、问题排查
12.2 DB单实例测试环境,修改主机名称后，监听无法动态注册。
主机名称c12修改为c11
$ lsnrctl status
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 05-MAR-2021 00:07:36
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=c11)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 04-MAR-2021 23:49:37
Uptime 0 days 0 hr. 17 min. 59 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /picclife/app/oracle/product/12.2.0/db_1/network/admin/listener.ora
Listener Log File /picclife/app/oracle/diag/tnslsnr/c11/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=c11)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
二、问题排查
2.1 观察local_listener对应的配置信息
SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
local_listener string LISTENER_C11
SQL> alter system register;
# tail -200f /picclife/app/oracle/diag/tnslsnr/c11/listener/trace/listener.log
2021-03-05T00:07:15.371755+08:00
System parameter file is /picclife/app/oracle/product/12.2.0/db_1/network/admin/listener.ora
Trace information written to /picclife/app/oracle/diag/tnslsnr/c11/listener/trace/ora_15512_139669486215552.trc
Trace level is currently 0
Log messages written to /picclife/app/oracle/diag/tnslsnr/c11/listener/alert/log.xml
05-MAR-2021 00:07:15 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=c11)(USER=oracle))(COMMAND=reload)(ARGUMENTS=64)(SERVICE=LISTENER)
(VERSION=203424000)) * reload * 0
2021-03-05T00:07:19.207414+08:00
WARNING: Subscription for node down event still pending
$ cat /picclife/app/oracle/product/12.2.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /picclife/app/oracle/product/12.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = c11)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SQL> host tnsping listener_c11
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 05-MAR-2021 00:13:09
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/picclife/app/oracle/product/12.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (ADDRESS = (PROTOCOL = TCP)(HOST = c12)(PORT = 1521))
TNS-12541: TNS:no listener
$ cat /picclife/app/oracle/product/12.2.0/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /picclife/app/oracle/product/12.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_C11 =
(ADDRESS = (PROTOCOL = TCP)(HOST = c12)(PORT = 1521))
修改后
LISTENER_C11 =
(ADDRESS = (PROTOCOL = TCP)(HOST = c11)(PORT = 1521))
Local_listener 参数为本地实例注册监听的指向！
有两种配置方式，1.直接参数写上ip+port; 2.写一个别名，别名的内容指向tnsnames.ora的配置信息，配置信息包含ip+port
本次修改了listener.ora 配置文件主机名，但是没有修改local_listener 对应的LISTENER_C11别名，指向tnsnames.ora中的host名称！
但是上述修改后！ 监听还是无法动态注册？？？
2.1 观察local_listener对应的配置信息
1) 对监听日志整个trace看一下是否存在注册的信息，或者说报错异常的现象！
LSNRCTL> show current_listener Current Listener is LISTENER LSNRCTL> set trc_level support Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=c11)(PORT=1521))) LISTENER parameter "trc_level" set to support The command completed successfully SQL> alter system register; LSNRCTL> set trc_level off Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=c11)(PORT=1521))) LISTENER parameter "trc_level" set to off The command completed successfully LSNRCTL> show trc_directory Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=c11)(PORT=1521))) LISTENER parameter "trc_directory" set to /picclife/app/oracle/diag/tnslsnr/c11/listener/trace $ trcasst ora_20952_139636188664192.trc >trace_lis.txt (DESCRIPTION=(CONNECT_DATA=(CID=(PROGRAM=)(HOST=c11)(USER=oracle))(COM MAND=trc_level)(TRACE=off)(ARGUMENTS=4)(SERVICE=LISTENER)(VERSION=2034 24000))) ······ (DESCRIPTION=(CONNECT_DATA=(CID=(PROGRAM=)(HOST=c11)(USER=oracle))(COM MAND=trc_level)(TRACE=off)(ARGUMENTS=4)(SERVICE=LISTENER)(VERSION=2034 24000))) ---------------------- 跟踪文件统计数据: ---------------------- 开始时间戳: 021-03-05 01:15:48.443 结束时间戳: 021-03-05 01:17:17.666 会话总数: 3 DATABASE: 操作计数: 打开次数 0, 语法分析次数 0, 执行次数 0, 提取次数 0 ORACLE NET SERVICES: 总调用数: 已发送 3 个, 已接收 3 个, 0 oci 总字节数: 已发送 148 字节, 已接收 447 字节 平均字节数: 每包发送 49 字节, 每包接收 149 字节 最大字节数: 已发送 86 字节, 已接收 218 字节 总计包数: 已发送 3 个, 已接收 3 个 也就是说ALTER SYSTEM REGISTER这个动作几乎是没有效果！没发现存在注册的内容？？？
2）修改Local_listener 参数
手工修改参数
SQL>alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.98)(PORT=1521))';
或者设置为null
SQL> alter system set local_listener='';
再次设置参数等同原来的值也可以了？？？
！！！ 说明啥？说明最初由于local_listener的文件tnsnames.ora host没修改异常，导致oracle并没有真正的再次进行注册，参数重置之后就可以了
SQL> alter system set local_listener='LISTENER_C11';
上述三种方法都能解决本次问题！ 本质就是参数需要重置一下！
3）再次trace一下，观察动态注册的信息
LSNRCTL> stop
LSNRCTL> set trc_level support
SQL> alter system register;
LSNRCTL> set trc_level off
$ trcasst ora_25948_139684925370752.trc >trace2.txt
<--- Received 116 bytes - Connect packet timestamp=021-03-05 01:56:57.122
Current NS version number is: 316.
Lowest NS version number can accommodate is: 300.
Maximum SDU size: 8192
Maximum TDU size: 2097152
NT protocol characteristics:
Test for more data
Test operation
Full duplex I/O
Urgent data support
Generate SIGURG signal
Generate SIGPIPE signal
Generate SIGIO signal
Handoff connection to another
Line turnaround value: 0
Connect data length: 46
Connect data offset: 70
Connect data maximum size: 0
(CONNECT_DATA=(COMMAND=service_register_NSGR))