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/hosts127.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
