作者：IT邦德
中国DBA联盟(ACDU)成员，10余年DBA工作经验，
Oracle、PostgreSQL ACE
博客专家及B站知名UP主，全网粉丝10万+
擅长主流Oracle、MySQL、PG、
高斯及Greenplum备份恢复，
安装迁移，性能优化、故障应急处理


文章目录

  • 1.故障现象
  • 2.排查过程
  • 2.1 阻塞的队列排查
  • 2.2 SQL对应的文本
  • 3.故障处理
  • 3.1 session定位
  • 3.2 服务器进程KILL
  • 3.3 Oracle删除列正确的方式
  • 4.负载均衡的解读
  • 4.总结


突发故障，业务系统挂了，只因为业务人员在高峰期做了DDL操作，导致锁，数据库夯住！

作为DBA，要为客户做好节前的最后保障，这不本来想提前可以回家的，突发故障，业务系统挂了，只因为业务人员在高峰期做了DDL操作，导致锁，数据库夯住！

1.故障现象

首先产线反馈应用出现卡顿，开发人员客户端无法连接，出现以下报错。

操作系统： OEL7.9
数据库：ODA 19.20.0.0.0（RAC）

DB Error MSG=[ORA-01013]: 
user requested cancel of current operation
ORA-00060: 
deadlock detected while waiting for resource

2.排查过程

所有的故障都是变化（变更）引起的，那么问题发生的第一时刻就联系了IT部门，得知业务人员在下午业务高峰期间，对核心业务表做了相关的DDL操作！那么基本可以判断是锁的问题了。

2.1 阻塞的队列排查

select aa.snap_id,
aa.session_id,
aa.blocking_session,
aa.session_serial#, 
aa.blocking_session_status,
aa.event,aa.program,aa.sql_id
from DBA_HIST_ACTIVE_SESS_HISTORY aa
where 1=1
and SAMPLE_TIME >
TO_TIMESTAMP ('2024-09-14 14:00:00','yyyy-mm-dd hh24:mi:ss')
and SAMPLE_TIME 
<TO_TIMESTAMP ('2024-09-14 15:00:00','yyyy-mm-dd hh24:mi:ss')

检查历史会话发现大量tx锁，
锁对象id是389517（id对应的表名是materialot），
阻塞的session为5993
定位被锁的对象
select * from dba_objects where object_id=389517

同时在AWR报告能详细的看到这些信息,列出的锁表时出现的等待事件以及GC等待事件跟故障现象匹配。

锁表导致系统挂了,谨慎DDL操作_sql

锁表导致系统挂了,谨慎DDL操作_数据库_02

2.2 SQL对应的文本

select sql_text from 
gv$sqlarea where sql_id='dd6uizh6v0d20' 
ALTER TABLE materialot DROP COLUMN TWHOUNT;
很明显，开发人员做了列的删除，造成锁导致数据库夯住

有时候如果出现SQL文本找不出来的情况怎么办呢？一般是因为出现硬解析错误导致，就像曾经其他客户出现的一个故障，也是行锁，但是怎么都找不到对应的SQL，那么就只能通过日志挖掘找相关的事务了，以下分享一下日志挖掘的步骤

1.添加归档日志
execute dbms_logmnr.add_logfile(logfilename=>
'/mccdb/archivelog/1_269564_839952465.dbf',
options=>dbms_logmnr.addfile);

2.启动日志挖掘
execute dbms_logmnr.start_logmnr(options=>
dbms_logmnr.dict_from_online_catalog);

3.日志在内存中，转储在表中
create table prod.test as select * from v$logmnr_contents;

4.结束日志挖掘
execute dbms_logmnr.end_logmnr;

3.故障处理

3.1 session定位

死锁标准处理方式如下：
SELECT SESS.SID,  
SESS.SERIAL#,  
LO.ORACLE_USERNAME,  
LO.OS_USER_NAME,  
AO.OBJECT_NAME 被锁对象名, 
LO.LOCKED_MODE 锁模式, 
sess.LOGON_TIME 登录数据库时间,
'ALTER SYSTEM KILL SESSION ''' 
|| SESS.SID || ','||SESS.SERIAL#||'''' FREESQL
FROM V$LOCKED_OBJECT LO,  DBA_OBJECTS AO,  V$SESSION SESS 
WHERE AO.OBJECT_ID = LO.OBJECT_ID 
AND LO.SESSION_ID = SESS.SID 
ORDER BY sid, sess.serial#;

alter system kill session '2039,31796';

3.2 服务器进程KILL

复制完执行可能会报错：
ORA-00031: session marked for kill，
这表示ORACLE已经把它标记为一个杀死的进程，
但暂时无法将其彻底杀死，
这个时候需要我们执行下面的sql，
查出它在服务器上的进程id：

# sid 为上面sql 查出来的 sid
select spid, osuser, s.program
   from v$session s,v$process p
   where s.paddr=p.addr
   and s.sid=''2039' 

通过上方 sql 可以得到服务器上的进程 id，
登录数据库所在服务器，
利用 kill 命令将其杀死即可：

kill -9 12009（查出来的spid）

3.3 Oracle删除列正确的方式

DDL这些操作可能改变表的结构，
Oracle会在这些操作进行时锁定表，以防止其他事务对表的访问

我们需要把字段先设置为UNUSED，
然后再业务低的时候删掉
ALTER TABLE TEST SET UNUSED(COL1);
ALTER TABLE TEST DROP UNUSED  COLUMN;

4.负载均衡的解读

在这里要跟大家在解读一下最近群里讨论比较多的Oracle RAC关于负载均衡的配置方式，

从这套库来看，节点1的报告期内连接数为674, 节点2的报告期内连接数为593，两个节点会话分配较为均匀，这是因为应用配置了LOAD_BALANCE为yes.

锁表导致系统挂了,谨慎DDL操作_数据库_03

生产上我们是建议这样配置的，通过2个VIP来做负载均衡，而不是用scanip，因为大多数是没有配置dns，所以scanip放到/etc/hosts里其实起不到作用负载均衡作用的，只有放到dns里才能轮巡。

jdbc:oracle:thin:@(DESCRIPTION =(
ADDRESS_LIST =(FAILOVER=on)
(LOAD_BALANCE=yes)
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.21)
(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.22)
(PORT = 1521)))
(CONNECT_DATA =(SERVER = DEDICATED)
(SERVICE_NAME = test)))
真正的负载均衡由两部分配置完成：
一个是服务 service
一个就是连接串

锁表导致系统挂了,谨慎DDL操作_负载均衡_04

4.总结

请谨慎DDL操作,可能导致其他DML操作被长时间锁定，这可能会对繁忙的系统造成严重问题，并且相关的执行计划也会随之发生变化！