oracle 非默认监听器(多个监听器)的 trace file 跟踪文件

服务端 监听器设置 准备

-- 第一个监听器(默认)
[oracle@itpuxhsdb52:/home/oracle]$ cat /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = itpuxhsdb)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /oracle/app/oracle

-- 第二个监听器
FGHSBD52 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.52)(PORT = 1552))
    )
  )

ADR_BASE_FGHSBD52 = /oracle/app/oracle


-- 两个监听器已启动
[oracle@itpuxhsdb52:/home/oracle]$ps -ef|grep tns
root         19      2  0 10:09 ?        00:00:00 [netns]
oracle    20086      1  0 10:10 ?        00:00:00 /oracle/app/oracle/product/11.2.0/db_1/bin/tnslsnr LISTENER -inherit
oracle    20316      1  0 10:16 ?        00:00:00 /oracle/app/oracle/product/11.2.0/db_1/bin/tnslsnr FGHSBD52 -inherit
oracle    21125  20671  0 10:42 pts/1    00:00:00 grep --color=auto tns
[oracle@itpuxhsdb52:/home/oracle]$


-- 服务端修改local_listener为FGHSBD52
SYS@fghsdb> show parameter listen
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string      (DESCRIPTION =(ADDRESS = (PROT
                                                 OCOL = TCP)(HOST = 192.168.1.5
                                                 2)(PORT = 1552)))
remote_listener                      string


复制

服务端 开始监听器的trace设置

[oracle@itpuxhsdb52:/home/oracle]$lsnrctl
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 22-JUN-2023 10:43:18
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Welcome to LSNRCTL, type help for information.

-- set命令用法
LSNRCTL> set
The following operations are available after set
An asterisk (*) denotes a modifier or extended command:

password                             rawmode
displaymode                          trc_file
trc_directory                        trc_level
log_file                             log_directory
log_status                           current_listener
inbound_connect_timeout              startup_waittime
save_config_on_stop                  dynamic_registration
enable_global_dynamic_endpoint       connection_rate_limit
valid_node_checking_registration     registration_invited_nodes

-- 重点 
LSNRCTL> set current_listener FGHSBD52
Current Listener is FGHSBD52

-- 状态是off
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.52)(PORT=1552)))
STATUS of the LISTENER
------------------------
Alias                     FGHSBD52
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                22-JUN-2023 10:16:45
Uptime                    0 days 0 hr. 7 min. 38 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /oracle/app/oracle/diag/tnslsnr/itpuxhsdb52/fghsbd52/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.52)(PORT=1552)))
The listener supports no services
The command completed successfully

-- 开启trace
LSNRCTL>  set trc_level support
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.52)(PORT=1552)))
FGHSBD52 parameter "trc_level" set to support
The command completed successfully
LSNRCTL>
LSNRCTL>
LSNRCTL>

-- 查看状态
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.52)(PORT=1552)))
STATUS of the LISTENER
------------------------
Alias                     FGHSBD52 -- 监听器是FGHSBD52 不是默认的第一个监听器
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                22-JUN-2023 10:16:45
Uptime                    0 days 0 hr. 8 min. 3 sec
Trace Level               support   -- 代表已开启
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /oracle/app/oracle/diag/tnslsnr/itpuxhsdb52/fghsbd52/alert/log.xml
Listener Trace File       /oracle/app/oracle/diag/tnslsnr/itpuxhsdb52/fghsbd52/trace/ora_20316_140248626882368.trc -- trace文件
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.52)(PORT=1552)))
The listener supports no services
The command completed successfully


-- 保存设置
LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.52)(PORT=1552)))
Saved FGHSBD52 configuration parameters.
Listener Parameter File   /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Old Parameter File   /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.bak
The command completed successfully





复制

客户端 连接测试

-- 配置tnsnames.ora
[oracle@fgzcdb56:/oracle/app/oracle/product/19c/db_1/network/admin]$cat tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/app/oracle/product/19c/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

FGZCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = fgzcdb56)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = fgzcdb)
    )
  )

LISTENER_FGZCDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = fgzcdb56)(PORT = 1521))

-- 
FGHSDB52 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.52)(PORT = 1552))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = fghsdb)
    )
  )


-- 连接到服务端
[oracle@fgzcdb56:/oracle/app/oracle/product/19c/db_1/network/admin]$sqlplus itpux/itpux@FGHSDB52

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 22 10:50:31 2023
Version 19.7.0.0.0

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ITPUX@FGHSDB52>
ITPUX@FGHSDB52>
ITPUX@FGHSDB52>
ITPUX@FGHSDB52> select open_mode from v$database;

OPEN_MODE
------------------------------------------------------------
READ WRITE




复制

服务端 监听器 trace文件 解析

[oracle@itpuxhsdb52:/home/oracle]$trcasst /oracle/app/oracle/diag/tnslsnr/itpuxhsdb52/fghsbd52/trace/ora_20316_140248626882368.trc > fghsdb52.txt
[oracle@itpuxhsdb52:/home/oracle]$
[oracle@itpuxhsdb52:/home/oracle]$
[oracle@itpuxhsdb52:/home/oracle]$grep 56 fghsdb52.txt
<--- Received 247 bytes - Connect packet  timestamp=023-06-22 10:26:55.056093
    =(PROGRAM=sqlplus)(HOST=fgzcdb56)(USER=oracle)))(ADDRESS=(PROTOCOL=TCP
    =(PROGRAM=sqlplus)(HOST=fgzcdb56)(USER=oracle)))(ADDRESS=(PROTOCOL=TCP
    NNECT_DATA=(SERVICE_NAME=fghsdb)(CID=(PROGRAM=sqlplus)(HOST=fgzcdb56)(
    NNECT_DATA=(SERVICE_NAME=fghsdb)(CID=(PROGRAM=sqlplus)(HOST=fgzcdb56)(
---> Send 871 bytes - Data packet  timestamp=023-06-22 10:37:05.256829
    NNECT_DATA=(SERVICE_NAME=fghsdb)(CID=(PROGRAM=sqlplus)(HOST=fgzcdb56)(
    NNECT_DATA=(SERVICE_NAME=fghsdb)(CID=(PROGRAM=sqlplus)(HOST=fgzcdb56)(
---> Send 242 bytes - Data packet  timestamp=023-06-22 10:50:35.130560
    NNECT_DATA=(SERVICE_NAME=fghsdb)(CID=(PROGRAM=sqlplus)(HOST=fgzcdb56)(  -- 能看到fgzcdb56连接到fghsdb52
---> Send 8 bytes - Resend packet  timestamp=023-06-22 10:53:10.265601
[oracle@itpuxhsdb52:/home/oracle]$
[oracle@itpuxhsdb52:/home/oracle]$



复制