Oracle ORA-03113 引发的DBLINK隐患

前言

  在8月5号、8月9号连续接到过2次ORA-03113 报警从日志未能看到有效信息，与开发沟通了解，有个视图用到DBLINK进行跨库查询。除了ORA-03113、ORA-02063 这两个报错，两个库都没有任何异常信息，只能先观察了。

• 日志报错

Fri Aug 09 21:30:00 2024
Error 3113 trapped in 2PC on transaction 194.32.4726760. Cleaning up.
Error stack returned to user:
ORA-03113: 通信通道的文件结尾
ORA-02063: 紧接着 line (起自 DB_READ)
Fri Aug 09 21:30:00 2024
Error 3113 trapped in 2PC on transaction 11.11.6198971. Cleaning up.
Error stack returned to user:
ORA-03113: 通信通道的文件结尾
ORA-02063: 紧接着 line (起自 DB_READ)

• 环境背景
  最近刚经历"深夜一声惊雷.客户炸了群"做了次“ORACLE-主备备-Failover”切换，而且DBlink 的tns 也做了对应的调整。这错报的我心里很忐忑。
• 补录：2024-08-19
  今天看到熊老师的文章：Oracle与防火墙有这么一段话：
  
  于是我怀疑应该是的调了tns的连接信息，新连接走新的tns到了新库，旧连接因程序的连接池的配置会话仍保持着。然后“目前不确认是被谁检测到了原会话不可用了”统一报了：ORA-03113，后续再深挖一下希望能测试出结果。

发现隐患

• DBlink 连接在逐渐递增
  
• 问题：
  在此意识到程序的连接有连接池控制连接，但数据库PROFILE设置UNLIMITED,sqlnet.ora也没有配置，如果dblink的网络异常，程序频繁发起Session又释放不掉资源，会不会把数据库的Session 直接撑爆？

模拟测试

• 测试思路
  DB2 创建视图：T_ORDER_VIEW，视图是通过DBLINK连接到DB1的表：T_ORDER，然后应用访问DB2的视图：T_ORDER_VIEW，如果应用访问DB2的Session异常断了，DB2至DB1的Session会不会释放？
  

测试准备

• DB1 测试表

create table T_ORDER
(
  dbno            NUMBER(10),
  dbname          VARCHAR2(64),
  create_date     date
);
-- db1 
insert into T_ORDER (dbno,dbname,create_date) values(1,'db1',sysdate);

• TNS

db1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.100)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db1 )
    )
  )

• DB2 创建到DB1的DBLINK

create database link db1_link connect to two identified by two using 'db1';

• DB2 创建查询DB1表：T_ORDER的视图

CREATE OR REPLACE VIEW T_ORDER_VIEW AS
select  dbno,dbname,create_date
from T_ORDER@db1_link;

• 查看当时会话信息：

col pid for 999
col SID for 999
col spid for a10
select 
    s.sid,
    s.serial#,
    p.pid,
    p.spid, 
    s.status
from v$session s
inner join v$process p on s.paddr=p.addr
where s.sid=(select sid from v$mystat where rownum=1);

• 查看测试用户：two的连接,目前为null

col pid for 999
col SID for 999
col spid for a10
col USERNAME for a5
SELECT 
       s.username,
       s.sid,
       s.serial#,
       p.pid,
       p.spid,
       s.status,
       to_char(s.logon_time,'yyyymmdd hh24:mi:ss') logon_time
FROM v$session s ,v$process p 
WHERE  TYPE!='BACKGROUND' 
and s.username ='TWO'
and s.PADDR=p.addr;

开始测试

模拟1：测试kill应用连接进程

• 应用连接到DB2（通过@tns 模拟应用连接）
  
• 应用查询视图：T_ORDER_VIEW
  
• 杀掉应用连接：
  
• DB1 DB2 Session都释放掉了
  

模拟2：模拟网络异常：拔网线

• PL/SQL Developer 模拟应用连接— 查询视图
  
• 查看DB1、DB2连接已进来
  
• 拔网线— 17：22 已断网（注意标红）
  
• DB1、DB2 Session 还在
  
• 监控脚本： Check_Session.sh

#!/bin/bash
source /home/oracle/.bash_profile
export con_user='sqlplus -s system/oracle'
echo '-------------'` date `'----------------------------'
$con_user << EOF
col pid for 999
col SID for 999
col spid for a10
col USERNAME for a5
SELECT 
       s.username,
       s.sid,
       s.serial#,
       p.pid,
       p.spid,
       s.status,
       to_char(s.logon_time,'yyyymmdd hh24:mi:ss') logon_time
FROM v\$session s ,v\$process p 
WHERE  TYPE!='BACKGROUND' 
and s.username ='TWO'
and s.PADDR=p.addr;
exit;
EOF

• 定时每2分钟监控1次

*/2  *  *  *  * oracle sh /home/oracle/Check_Session.sh >> /home/oracle/Check_Session.log

• 日志结果分析：差不多存活了2小时
  
• Oracle alter 日志输出：：Fatal NI connect error 12170
• 19：35：27 （PORT=50872）time out
• 19：36：25 （PORT=50827）time out
  

解决思路：

• 显示关闭dblink：
  1.此方式只对当前session起作用，所以管理员SYS去关闭也是不成功的
  2.在使用此方式的命令之前需要用commit令或rollback;
  否则会出现ORA-02080: database link is in use

alter session close database link dblink-name;
或
dbms_session.close_database_link('dblink-name');

• 测试示例1：alter session close database link dblink-name;
  
• 测试示例2:dbms_session.close_database_link
  

结论：

• KILL应用会话，应用到DB1的Session会断掉，同时DB1到DB2的Session也被释放；
• 网络异常DB1、DB2的Sesssion 一直存活2小时左右才会被数据库检查到，并到alert日志输出：timed out ，感觉像是PMON进程将此Session当作dead process清理“Oracle PMON进程清洗功能”（这里的机制需要后续再验证）；
• 数据库在Session上如果未做限制,使用dblink远程连接数据库时就要考虑增加关闭dblink连接操作，否则有连接无限扩张的风险；
• 至于：ORA-03113: 通信通道的文件结尾，ORA-02063: 紧接着 line (起自 DB_READ)报错查阅许多网上资料，引发报错的可能性太多《ORA-03113错误分析》，目前看此次报错的原因应该是我调了tns信息的原因引起的;
• 后期深挖一下，熊老师的这两篇文章：《Oracle与防火墙》，《防火墙、DCD与TCP Keep alive》应该会找到答案;