分类目录归档：Oracle 监听

监听报TNS-12541 TNS-12560 TNS-00511错误

发表于 2024 年 9 月 5 日由惜分飞

有客户运行在win平台上的oracle 11.2.0.1,监听无法正常工作,使用status查看,卡很长时间然后出现如下TNS-12541 TNS-12560 TNS-00511 64-bit Windows Error: 2: No such file or directory错误

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=LOCALHOST)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 61: Unknown error

根据以往经验,监听服务本身启动成功,运行status hang很久,有可能和监听日志有关系,通过查看确认监听日志达到4G,关闭服务,重命名监听日志,再次启动监听,状态正常
listener

关于win平台监听超过4G,引起监听hang住的文章:windows平台listener.log超过4G导致监听异常

发表在 Oracle 监听 | 标签为 TNS-12541 TNS-12560 TNS-00511 | 评论关闭

TNS-12518: TNS:listener could not hand off client connection

发表于 2024 年 3 月 12 日由惜分飞

客户反馈业务经常性出现数据库连接异常,通过工具访问ORACLE进程报ORA-12170

通过分析发现lsnrctl status几乎hang住，tnsping延迟特别大

进一步分析监听日志发现TNS-12518: TNS:listener could not hand off client connection错误

12-MAR-2024 15:34:50 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)
(HOST=__jdbc__)(USER=Administrator))(SERVICE_NAME=ilas)) * (ADDRESS=(PROTOCOL=tcp)
(HOST=ip)(PORT=52854)) * establish * ilas * 12518
TNS-12518: TNS:listener could not hand off client connection
 TNS-12547: TNS:lost contact
  TNS-12560: TNS:protocol adapter error
   TNS-00517: Lost contact
    Linux Error: 32: Broken pipe

根据经验和Troubleshooting Guide for TNS-12518 TNS listener could not hand off client connection描述,检查监听文件配置

[oracle@xff admin]$ cat listener.ora
# listener.ora Network Configuration File:/home/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = /home/u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:/home/u01/app/oracle/product/11.2.0/dbhome_1/bin/oraclr11.dll")
    )
  )

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

ADR_BASE_LISTENER = /home/u01/app/oracle

根据经验和客户的业务进行分析,确认他们不会使用 external procedures方式访问数据库,直接修改监听配置

[oracle@xff admin]$ cat listener.ora
# listener.ora Network Configuration File:/home/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = xff)
      (ORACLE_HOME = /home/u01/app/oracle/product/11.2.0/dbhome_1)
      (GLOBAL_DBNAME = xff)
    )
  )

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

ADR_BASE_LISTENER = /home/u01/app/oracle

然后reload配置,再使用lsnrctl status查看结果秒出,tnsping也非常快

让客户测试应用也恢复正常,一切ok,问题在最小修改的情况下解决,和最初供应商建议的重装系统,双机,数据库等解决方案大大简化

发表在 Oracle 监听 | 标签为 Linux Error: 32: Broken pipe, TNS-12518 | 评论关闭

ORA-01034 ORA-27101故障分析

发表于 2020 年 10 月 18 日由惜分飞

客户的数据库通过监听访问报ORA-01034 ORA-27101错误无法正常使用：

SQL> conn app/xxxxx@192.168.129.145/orcl
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0

SQL>  conn  app/xxxxx
Connected.

检查数据库状态和tnsping都正常

SQL> select open_mode from v$database;

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

SQL> !tnsping orcl

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 18-OCT-2020 22:11:49

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.129.1)
(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (0 msec)

检查主机之间ping操作也正常：

[XIFENFEI@DB1 trace]$
[XIFENFEI@DB1 trace]$ ping 192.168.129.1
PING 192.168.129.1 (192.168.129.1) 56(84) bytes of data.
64 bytes from 192.168.129.1: icmp_seq=1 ttl=64 time=0.025 ms
64 bytes from 192.168.129.1: icmp_seq=2 ttl=64 time=0.032 ms
64 bytes from 192.168.129.1: icmp_seq=3 ttl=64 time=0.034 ms
^C
--- 192.168.129.1 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2318ms
rtt min/avg/max/mdev = 0.025/0.030/0.034/0.006 ms

检查数据库和监听配置：

[XIFENFEI@DB1 trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 18 22:13:50 2020

Copyright (c) 1982, 2013, 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

SQL> show parameter name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      orcl
db_unique_name                       string      orcl
global_names                         boolean     FALSE
instance_name                        string      oracle
lock_name_space                      string
log_file_name_convert                string
processor_group_name                 string
service_names                        string      orcl
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[XIFENFEI@DB1 trace]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 18-OCT-2020 22:15:16

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                18-OCT-2020 22:05:04
Uptime                    0 days 0 hr. 10 min. 12 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /usr/local/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /usr/local/oracle/diag/tnslsnr/DB1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.129.1)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[XIFENFEI@DB1 trace]$ cat /usr/local/oracle/product/11.2.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.


SID_LIST_LISTENER =
  (SID_LIST =
   (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = /usr/local/oracle/product/11.2.0/db_1)
      (GLOBAL_DBNAME= orcl)
    )
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /usr/local/oracle/product/11.2.0/db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =

 (DESCRIPTION_LIST =

   (DESCRIPTION =

    (ADDRESS = (PROTOCOL = IPC)  (KEY = EXTPROC1521))

    (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.129.1) (PORT = 1521))

   )

 )



ADR_BASE_LISTENER = /usr/local/oracle
[XIFENFEI@DB1 trace]$
[XIFENFEI@DB1 trace]$

这里看到数据库配置和监听配置不匹配，因此需要调整。

[XIFENFEI@DB1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 18 22:21:16 2020

Copyright (c) 1982, 2013, 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


SQL> conn app/xxxxx@192.168.129.145/orcl
Connected.
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[XIFENFEI@DB1 admin]$

发表在 Oracle 监听 | 标签为 ORA-01034, ORA-01034 ORA-27101, ORA-27101 | 评论关闭

17813235971

QQ 咨询

分类目录归档：Oracle 监听

监听报TNS-12541 TNS-12560 TNS-00511错误

TNS-12518: TNS:listener could not hand off client connection

ORA-01034 ORA-27101故障分析

文章分类

最近发表

分类目录归档：Oracle 监听

监听报TNS-12541 TNS-12560 TNS-00511错误

TNS-12518: TNS:listener could not hand off client connection

ORA-01034 ORA-27101故障分析

标签云

文章分类

最近发表