查看: 5205|回复: 10

[笔记] 一次因delete操作大表导致产生的ORA-01555问题的解决过程

[复制链接]
招聘 : Linux运维
论坛徽章:
4
参与2009年中国云计算大会纪念
日期:2009-06-05 10:02:28优秀写手
日期:2014-03-15 06:00:03沸羊羊
日期:2015-03-04 14:51:522015年新春福章
日期:2015-03-06 11:58:18
发表于 2014-3-5 10:36 | 显示全部楼层 |阅读模式
/*一次因delete操作大表导致产生的ORA-01555 caused by SQL statement below (SQL ID: ayy6p2k33w7r8, Query Duration=1361 sec, SCN: 0x0007.1419f729):错误的解决过程(11g r2 rac)*/

--1、检查数据库alert_log日志查看错误提示和发生错误的时间点,并检查数据库是否存在阻塞情况;
select (sysdate-m.logon_time)*24*60 minutes,m.username,m.blocking_instance block_ins1,m.blocking_session block_sid1,m.inst_id inid1,m.sid sid1,m.serial# ser1,m.sql_id sqlid1,
a.blocking_instance block_ins2,a.blocking_session block_sid2,a.inst_id inid2,a.SID sid2,a.SERIAL# ser2,a.sql_id sqlid2,
m.event,m.p1,m.p2,m.p3,m.logon_time
from (
select *
from gv$session se where wait_class!='Idle' and blocking_session is not null
) m
left join gv$session a on a.INST_ID=m.blocking_instance and a.SID=m.blocking_session;

--2、查看是否存在长时间等待的会话,但是sql_id值为空的非系统会话。
/*如果存在last_call_et时间值特别大,说明存在未提交的事务或者长时间没有向中间件反馈结果的会话*/
select ff.inst_id,ff.PADDR,'alter system kill session '''||ff.sid||','||ff.serial#||''';' kill,'kill -9 '||vv.spid osid,ff.sql_id,ff.last_call_et,ff.event,ff.PREV_SQL_ID,ff.MACHINE,ff.WAIT_CLASS
from gv$session ff,gv$process vv where ff.paddr=vv.addr and ff.inst_id=vv.inst_id and ff.last_call_et>0
and ff.username is not null and ff.sql_id is null
and ff.status='ACTIVE' and ff.event not like 'SQL%' and ff.event not like 'rdbms%' and ff.program like 'JDBC%' order by ff.last_call_et desc;

--3、因网络不存在延时情况,说明中间件与数据库之间不存在通讯问题,检查是否存在正在执行未提交的大事务。
select a.sid,
a.serial#,
a.user#,
a.username,
b.addr,
b.USED_UBLK,
b.USED_UREC
from v$transaction b, v$session a
where /*b.addr in (select a.taddr from v$session a where a.sid = '') and*/ b.addr=a.taddr;
/*多次刷新并关注USED_UBLK和USED_UREC,如果后者特别大,而且前者无明显变化,说明存在未提交的大事务,而且事务效率特别底下*/

--4、可以根据alert_log日志中出现错误提示的时间和长时间等待的会话SID,并且通过查看dba_hist_active_sess_history视图获得出现此类问题的根源。
select distinct ss.dbid,ss.session_id,ss.session_serial#,
ss.sql_opname,ss.sql_id from dba_hist_active_sess_history ss where ss.instance_number=2
and to_date(to_char(ss.sample_time,'yyyymmdd hh24:mi:ss'),'yyyymmdd hh24:mi:ss')>to_date('2012-08-13 08:00:00','yyyy-mm-dd hh24:mi:ss')
and to_date(to_char(ss.sample_time,'yyyymmdd hh24:mi:ss'),'yyyymmdd hh24:mi:ss')<to_date('2012-08-14 08:00:00','yyyy-mm-dd hh24:mi:ss')
and ss.session_id='119' and ss.session_serial#='8782' and ss.sql_id is not null;

----------------------
SESSION_ID    SESSION_SERIAL#    SQL_OPNAME    SQL_ID
119            8782               DELETE    0fxmkcpmq52yj
119            8782               DELETE    4g912np148236
/*从结果中我们看到,有两个sql语句执行了DELETE的操作*/
---再看看这两个语句是什么
select * from dba_hist_sqltext sq where sq.SQL_ID='4g912np148236'
/*执行了delete语句,而且是全表删除'delete rm_route_timelimit_history',进一步检查这个表,发现这个表有11915483行记录。由此可以认为这个操作时间超过了,回滚段的保留时间900秒,导致客户端频繁报ORA-01555回滚段过旧的错误*/



--5、经过与研发负责人沟通以后,确定首先可以kill掉此类进程。
select 'kill -9 '||vv.spid,ff.sid,ff.sql_id,vv.spid from v$session ff ,v$process vv where ff.paddr=vv.addr /*and ff.USERNAME='BOSDATA'*/ /*and ff.SQL_ID is not null*/
and ff.sid='119' /*and vv.PID='400'*/

--6、kill进程以后,观察是否存在表的回滚操作。

select * from v$process where pid in (select pid from v$fast_start_servers where undoblocksdone>0);
select ff.UNDOBLOCKSDONE,ff.UNDOBLOCKSTOTAL,ff.CPUTIME,round((ff.UNDOBLOCKSTOTAL-ff.UNDOBLOCKSDONE)/(ff.UNDOBLOCKSDONE/ff.CPUTIME)/60,2) send
from v$fast_start_transactions ff where ff.STATE='RECOVERING';

--查看回滚进程的详细信息
select ff.sid,
      ff.serial#,
       ff.machine,
       ff.username,
       ff.program,
       ff.sql_id,
       ff.PREV_SQL_ID,
       ff.LOGON_TIME,
       ff.EVENT,
       ff.WAIT_CLASS,
       ff.SECONDS_IN_WAIT,
       ff.STATE,
       ff.STATUS,
       to_char(ff.logon_time, 'yyyy/mm/dd hh24:mi:ss') as login_time
       ,'kill -9 '||vv.spid "OS ID"
       ,ob.OBJECT_NAME object_name
       ,ob.OBJECT_TYPE object_type
       ,fl.FILE_NAME file_name
       ,'alter system kill session'''||ff.sid||','||ff.SERIAL#||''';' kkk
       ,ff.SQL_HASH_VALUE
  from v$session ff,v$process vv,dba_objects ob,dba_data_files fl
where  ff.paddr=vv.addr and ff.ROW_WAIT_OBJ#=ob.OBJECT_ID and ff.ROW_WAIT_FILE#=fl.FILE_ID and vv.PID='400'
/*and ff.event='gc current request'*//* and ff.sql_id is null*/ order by ff.LOGON_TIME desc;

--7、最终解决办法
--沟通开发方对此类功能进行优化,全表删除可以考虑使用truncate table代替delete。
--为了预防此类情况,数据库可以改一些参数,我们调整了默认回滚段保留时间由以前的900秒,改为10800秒。
alter system set undo_retention=10800 scope=both sid='*';
论坛徽章:
0
发表于 2014-3-5 11:43 | 显示全部楼层
楼主太帅了!好好学习一下分析过程。。。

使用道具 举报

回复
论坛徽章:
6
ITPUB十周年纪念徽章
日期:2011-11-01 16:26:292012新春纪念徽章
日期:2012-01-04 11:57:56ITPUB 11周年纪念徽章
日期:2012-10-09 18:16:002013年新春福章
日期:2013-02-25 14:51:242015年新春福章
日期:2015-03-04 14:53:162015年新春福章
日期:2015-03-06 11:58:39
发表于 2014-3-6 14:26 | 显示全部楼层
可以用删除批量提交的存储过程解决。

使用道具 举报

回复
求职 : 数据库管理员
论坛徽章:
3
ITPUB社区千里马徽章
日期:2013-08-22 09:58:032014年新春福章
日期:2014-02-18 16:48:49马上加薪
日期:2014-02-18 16:48:49
发表于 2014-3-14 13:08 | 显示全部楼层
回归段不够用了?直接truncate不用写日志和回滚了

使用道具 举报

回复
认证徽章
论坛徽章:
249
Jeep
日期:2013-09-04 19:17:57Jeep
日期:2013-10-08 09:46:02Jeep
日期:2013-10-08 16:38:27Jeep
日期:2013-11-22 14:53:46Jeep
日期:2013-11-08 23:59:45Jeep
日期:2013-11-22 17:15:17Jeep
日期:2013-11-22 17:15:17Jeep
日期:2013-11-17 09:59:04季节之章:夏
日期:2015-01-28 14:58:51季节之章:春
日期:2014-12-25 16:20:50
发表于 2014-3-14 14:52 | 显示全部楼层
楼主强悍,分析的不错

使用道具 举报

回复
论坛徽章:
73
2010新春纪念徽章
日期:2010-03-01 11:06:132011新春纪念徽章
日期:2011-02-18 11:43:332012新春纪念徽章
日期:2012-01-04 11:55:422013年新春福章
日期:2013-02-25 14:51:242014年新春福章
日期:2014-02-18 16:44:08马上有车
日期:2015-01-19 09:45:33最佳人气徽章
日期:2012-03-13 17:39:18ITPUB季度 技术新星
日期:2012-11-27 10:16:10问答徽章
日期:2013-10-23 15:13:22林肯
日期:2013-11-02 08:34:46
发表于 2014-3-14 17:03 | 显示全部楼层
good!

使用道具 举报

回复
论坛徽章:
0
发表于 2014-3-14 17:20 | 显示全部楼层
不错~

使用道具 举报

回复
论坛徽章:
0
发表于 2014-3-16 10:13 | 显示全部楼层
值得学习,多谢楼主

使用道具 举报

回复
论坛徽章:
16
蛋疼蛋
日期:2012-04-27 23:09:45罗罗诺亚·索隆
日期:2016-11-04 21:04:09懒羊羊
日期:2015-06-23 09:05:532015年新春福章
日期:2015-04-01 14:21:232015年新春福章
日期:2015-03-06 11:58:39慢羊羊
日期:2015-03-04 14:53:33青年奥林匹克运动会-击剑
日期:2014-08-29 15:44:44马上加薪
日期:2014-02-18 16:48:492014年新春福章
日期:2014-02-18 16:48:49一汽
日期:2013-12-24 18:12:09
发表于 2014-3-16 10:47 | 显示全部楼层
顶,如果单纯的调整undo_retention参数可能导致undo表空间的不断扩展,这点需要注意一下。

使用道具 举报

回复
认证徽章
论坛徽章:
82
2013系统架构师大会纪念章
日期:2015-07-31 17:48:20探花
日期:2015-08-17 14:58:32榜眼
日期:2015-08-17 14:59:28进士
日期:2015-08-17 15:00:55举人
日期:2015-08-17 15:00:55秀才
日期:2015-08-19 09:36:37秀才
日期:2015-08-20 08:50:41嫦娥
日期:2015-08-21 09:11:54秀才
日期:2015-08-24 09:48:07进士
日期:2015-08-17 14:58:18
发表于 2014-3-17 14:40 | 显示全部楼层
学习了!

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表