ORACLE 遇到ORA 03113 数据库连接卡住
环境:RAC 双节点
发现两个节点的alert日志中出现大量的
opiodr aborting process unknown ospid (150950) as a result of ORA-609
TNS-12537: TNS:connection closed
ns secondary err code: 12560
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
首先查看下ORA-609解释
oerr ora 609
00609, 00000, "could not attach to incoming connection"
// *Cause: Oracle process could not answer incoming connection
// *Action: If the situation described in the next error on the stack
// can be corrected, do so; otherwise contact Oracle Support.
客户端通过监听器连接ORACLE数据库的过程:
1.客户端连接到监听器
2.监听派生fork一个子进程,交转化为专有服务器进程dedicated database process
3.第2步完成后,监听将客户端的连接转入此专有进程dedicated process
4.服务器进程收到从监听来的连接信息后,需要继续与客户端的连接进行handshake
5.服务器进程与客户端进程交换建立会话需要的信息,如用户名、密码等
6.以上OK后,SESSION OPEN。
在介于3、4步时客户端连接关闭,dedicated database process与客户端通信时发现客户端关闭了。
个人理解可能以下几个方向
1.网络连通不稳定
2.机器的资源
3.连接被防火墙等kill
4.client卡住、崩溃或做了超时设置
可能一、resource_limit 的值为 TRUE,闲置连接资源的大小,如果 当某个会话超过了为其分配的最大idle 时间时,就会抛出错误信息
可以通过错误中提到的ospid 进程号查出所属的会话及数据库用户
然后再通过username 去验证对应的Profile 是否有类似资源使用限制
解决方案:
禁用限制
alter system set resource_limit = false;
或者增加IDLE_TIME的设置值
三、DNS解析问题 /etc/resolv.conf
参数修改
Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180
Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120
1.测试tnsping
[oracle@racdb1 ~]$ tnsping test
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 25-NOV-2020 16:09:41
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = scan-ip)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test)))
OK (0 msec)
2.查看hosts文件配置
vi
/etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
172.16.0.101 testdb
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
可以尝试使用OS层面的跟踪
ps -ef|grep tnslsnr
grid 11270 1 0 2019 ? 04:00:46 /u01/app/12.2.0/grid/bin/tnslsnr ASMNET1LSNR_ASM -no_crs_notify -inherit
grid 60189 1 0 Apr17 ? 00:12:43 /u01/app/12.2.0/grid/bin/tnslsnr MGMTLSNR -no_crs_notify -inherit
grid 60381 1 5 Apr17 ? 13-06:29:50 /u01/app/12.2.0/grid/bin/tnslsnr LISTENER_SCAN1 -no_crs_notify -inherit
grid 150694 1 5 13:50 ? 00:09:13 /u01/app/12.2.0/grid//bin/tnslsnr LISTENER -inherit
如果使用TRACE跟踪,如下:
3. Oracle Net Level 16 Server tracing. Add to server side SQLNET.ORA file
DIAG_ADR_ENABLED=off # Disable ADR if version 11g
TRACE_LEVEL_SERVER = 16 # Enable level 16 trace
TRACE_TIMESTAMP_SERVER = ON # Set timestamp in the trace files
TRACE_DIRECTORY_SERVER = <DIRECTORY> # Control trace file location
TRACE_FILELEN_SERVER =<n> #Control size of trace set in kilobytes eg 20480
TRACE_FILENO_SERVER =<n> #Control number of trace files per process
使用Errorstack方法如下:
4. Errorstack: Setup errorstack to capture failure. This can be particular useful when capturing an Oracle Net client trace is not feasible.
SQL> alter session set events '609 errorstack(3)';
Once a few traces have been collected while the error is reproduced:
SQL> alter session set events '609 off';
2020-11-25T13:53:35.699028+08:00
Auto-tuning: Shutting down background process GTXg
2020-11-25T13:55:14.490613+08:00
Auto-tuning: Starting background process GTXg
Starting background process GTXg
2020-11-25T13:55:14.528882+08:00
GTXg started with pid=705, OS id=168025
2020-11-25T13:55:44.531647+08:00
Auto-tuning: Starting background process GTXh
Starting background process GTXh
2020-11-25T13:55:44.559985+08:00
GTXh started with pid=731, OS id=171140
2020-11-25T13:56:49.633296+08:00
Auto-tuning: Starting background process GTXi
Starting background process GTXi
2020-11-25T13:56:49.660700+08:00
GTXi started with pid=698, OS id=175892
[oracle@ecdb2 trace]$ tail -f alert_qqd2.log
Auto-tuning: Starting background process GTXj
Starting background process GTXj
2020-11-25T14:17:00.240968+08:00
GTXj started with pid=654, OS id=64586
参考链接:http://www.cnblogs.com/mfrbuaa/p/5347705.html
最新文章
- osi(open system interconnection)模型的通俗理解
- python 替换 文件夹下的 文件名称 及 文件内容
- 【java开发】数组基本学习
- 关于jsp页面将表单填入数据库出现中文乱码绝对解决方案
- JDK动态代理的实现原理
- 通过爬虫代理IP快速增加博客阅读量——亲测CSDN有效!
- IOS设计模式之一(MVC模式,单例模式)
- Js笔试题之parseInt()和.map()
- Android(java)学习笔记265:Android线程形态之 HandlerThread
- cocos2d-x 读取.plist文件
- cdoj 1250 喵哈哈的矩阵 数学题
- [BZOJ 3531] [Sdoi2014] 旅行 【离线+LCT】
- hdu 4502吉哥系列故事——临时工计划 (简单DP)
- exploit writing tutorial 阅读笔记总结
- 从数据库中导出.csv文件
- GLFW3出error adding symbols: DSO missing from command line解决
- python_如何快速下载安装第三方库?
- [AHOI 2009]chess 中国象棋
- sudo: no tty present and no askpass program specified
- U68364 _GC滑迷宫
热门文章
- Spark详解(07) - SparkStreaming
- C++ 之 cout 格式化输出
- MySQL join语句怎么优化?
- npm 启动项目报错 Cannot find module &#39;\@babel\compat-data\data\corejs3-shipped-proposals&#39;,解决办法
- java应用定位高cpu占用几步操作
- 前端如何实现将多页数据合并导出到Excel单Sheet页解决方案|内附代码
- Google_MapReduce中文版
- 大规模 IoT 边缘容器集群管理的几种架构-0-边缘容器及架构简介
- NETAPP 设备 C模式开关机顺序
- Distinct Paths