• oracle10g连接自动断开,报ORA03135错误


    问题描述:
    Linux oracle使用过一段时间,连接断开,报ORA-03135错误。
     
    问题挖掘:
    用pl/sql和sqlplus连接oracle,也存在该问题,确定该问题与连接方式无关。
    查看服务器,发现没有防火墙,防火墙因素排除。
    ping -t 服务器地址,发现没有丢包,都100%收到,网络通畅。
    基本可以肯定问题出在oracle参数配置上。但也不排除其他因素。
     
    解决过程:
     
    根据ora-03135查询到oracle官方的解决方案:
    ORA-03135: connection lost contact
    Cause: 1) Server unexpectedly terminated or was forced to terminate. 2) Server timed out the connection.
    Action: 1) Check if the server session was terminated. 2) Check if the timeout parameters are set properly in sqlnet.ora.
     
    查询相关资料,发现该问题可能与sqlnet.ora设置参数SQLNET.EXPIRE_TIME 有关。因此在server上面的sqlnet.ora设置参数SQLNET.EXPIRE_TIME = 5(需在服务器监听reload一下使参数生效:lsnrctl reload),而在client不设置该参数,。等待一段时间后,没有出现该问题了,问题解决。
     
    知识扩展:
    在server端的sqlnet.ora文件中设置SQLNET.EXPIRE_TIME这一参数可以启用DCD功能,DCD是Dead Connection Detection的缩写,用于检查死掉但没有断开的session。

    例如:
    SQLNET.EXPIRE_TIME = 20
    可以设置为任意数值,单位分钟。
    重启listener
    bash-2.05$ lsnrctl reload
    ……
    The command completed successfully
     
    DCD可以用于防止防火墙的timeout。
     
    当新连接建立的时候,会读取sqlnet.ora中的这一设定,当20分钟间隔到达时,对inactive的session发送”probe” SQL*NET packet来确认client是否活着。如果已经死掉,那么相对应的resource会被自动清除。
    如果client和server中间有防火墙,防火墙设有timeout参数,例如设置为一个小时,当一个小时中client和server间没有数据传输的时候,防火墙会断开连接。开启DCD后,通过设置SQLNET.EXPIRE_TIME的值小于防火墙的timeout参数可以避免这一情况。 “probe” package确保防火墙不会断开idle的session。
     
     大多数情况下,这样操作就好了,要是还是不行就进行停止oracle实例和重启操作
    1、启动数据库:
    oracle@suse92:~> sqlplus /nolog
    SQL*Plus: Release 9.2.0.4.0 - Production on Fri Jan 20 02:29:37 2006
    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
    SQL> connect /as sysdba
    Connected to an idle instance.
    SQL> startup
    ORACLE instance started.
    Total System Global Area 135352820 bytes
    Fixed Size 455156 bytes
    Variable Size 109051904 bytes
    Database Buffers 25165824 bytes
    Redo Buffers 679936 bytes
    Database mounted.
     

    2、关闭数据库:
    oracle@suse92:~> sqlplus /nolog
    SQL*Plus: Release 9.2.0.4.0 - Production on Fri Jan 20 02:29:37 2006
    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
    SQL> connect /as sysdba
    Connected to an idle instance.
    SQL> shutdwon abort;
     

    3、启动监听器
    oracle@suse92:~> lsnrctl start
     

    4、停止监听器
    oracle@suse92:~> lsnrctl stop
     

    5、查看监听器状态
      lsnrctl">oracle@suse92:~>lsnrctl
      LSNRCTL> status
      LSNRCTL> exit
     
     
     
     
     
     
    参考资料:
    sqlnet.ora http://www.orafaq.com/wiki/Sqlnet.ora
    oracle DCD  http://www.dbafan.com/blog/?p=174
    sqlnet.expire_time and IDLE_TIME http://space.itpub.net/10687595/viewspace-420407
     
     
    SQLNET.EXPIRE_TIME 的官方说明
    Purpose
    Use parameter SQLNET.EXPIRE_TIME to specify a the time interval, in minutes, to send a probe to verify that client/server connections are active. Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination. If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing the server process to exit. This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.
     
    Limitations on using this terminated connection detection feature are:
    ·         It is not allowed on bequeathed connections.
    ·         Though very small, a probe packet generates additional traffic that may downgrade network performance.
    ·         Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in degraded network performance.
    Default
    0
    Minimum Value
    0
    Recommended Value
    10
    Example
    SQLNET.EXPIRE_TIME=10
  • 相关阅读:
    通过远程linux shell传输文件
    Windows下 postgresql安装报错
    python安装tesserocr错误解决办法
    django makemigrations
    Windows下eric安装的几个小问题
    _WinMain结构
    python学习第七天——输入输出、file
    python学习第六天——模块的概念(重要)
    python学习第五天——函数(重点)
    python学习第四天——控制流
  • 原文地址:https://www.cnblogs.com/ios9/p/16366874.html
Copyright © 2020-2023  润新知