P09 IDS UNDO故障处理记录：
1 项目组报undo空间消耗严重，帮忙处理：
检查undo实时消耗：
1 SELECT r.name,
2 s.serial#,
3 s.sid,
4 s.username,
5 t.status,
6 t.cr_get,
7 t.phy_io,
8 t.used_ublk,
9 used_urec,
10 t.noundo,
11 substr(s.program, 1, 78)
12 FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r
13* WHERE t.addr = s.taddr and t.xidusn = r.usn
SQL> /
NAME SERIAL# SID USERNAME STATUS CR_GET PHY_IO USED_UBLK USED_UREC NOU
------------------------------ ---------- ---------- ------------------------------ ---------------- ---------- ---------- ---------- ---------- ---
SUBSTR(S.PROGRAM,1,78)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
_SYSSMU77$ 49078 278 BOE ACTIVE 127 0 1 12 NO
boe_cmsd@CXBOEAPP8 (TNS V1-V3)
_SYSSMU82$ 16470 861 BLACKLIST4 ACTIVE 7154911 15424145 466041 932080 NO
sqlplus@CXETLDB2 (TNS V1-V3)
_SYSSMU41$ 6873 543 ENQR ACTIVE 7 0 1 7 NO
NAME SERIAL# SID USERNAME STATUS CR_GET PHY_IO USED_UBLK USED_UREC NOU
------------------------------ ---------- ---------- ------------------------------ ---------------- ---------- ---------- ---------- ---------- ---
SUBSTR(S.PROGRAM,1,78)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
_SYSSMU20$ 55172 719 ATOM ACTIVE 2218636 4158010 1 3 NO
plsqldev.exe
_SYSSMU18$ 39432 232 RPT ACTIVE 251188 249420 1 1 NO
sqlplus@CXETLDB2 (TNS V1-V3)
_SYSSMU9$ 10646 215 JS ACTIVE 132247637 9845968 2 22 NO
plsqldev.7.10.1339.exe
NAME SERIAL# SID USERNAME STATUS CR_GET PHY_IO USED_UBLK USED_UREC NOU
------------------------------ ---------- ---------- ------------------------------ ---------------- ---------- ---------- ---------- ---------- ---
SUBSTR(S.PROGRAM,1,78)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
_SYSSMU48$ 44973 552 ATOM ACTIVE 9698828 3727183 995853 45907197 NO
sqlplus@CXETLDB2 (TNS V1-V3)
发现552和861 会话消耗了大量undo：
检查这两个会话的操作：
552 和861 都占用了很大的undo
SQL> select status,sid,username,machine,program from v$session where sid in (552,861);
STATUS SID USERNAME MACHINE PROGRAM
-------- ---------- ------------------------------ ---------------------------------------------------------------- ------------------------------------------------
ACTIVE 552 ATOM CXETLDB2 sqlplus@CXETLDB2 (TNS V1-V3)
ACTIVE 861 BLACKLIST4 CXETLDB2 sqlplus@CXETLDB2 (TNS V1-V3)
SQL> select event,sid from v$session_wait where sid in (552,861);
EVENT SID
---------------------------------------------------------------- ----------
db file sequential read 552
db file sequential read 861
执行的都是insert 操作，
项目组要求kill掉一个session：552，保留861
Kill 后死事务需要回滚，
1* select begin_time,end_time,undotsn,undoblks,txncount,activeblks,unexpiredblks,expiredblks from v$undostat order by 1
BEGIN_TIME END_TIME UNDOTSN UNDOBLKS TXNCOUNT ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS
------------------- ------------------- ---------- ---------- ---------- ---------- ------------- -----------
2010-04-02 12:10:58 2010-04-02 12:20:58 5 24947 7082 57664 4452184 1105424
2010-04-02 12:20:58 2010-04-02 12:30:58 5 268615 79481 181952 4191960 1259664
2010-04-02 12:30:58 2010-04-02 12:40:58 5 532587 161388 707136 2654536 2801056
2010-04-02 12:40:58 2010-04-02 12:50:58 5 618236 205942 1337432 4052464 896272
2010-04-02 12:50:58 2010-04-02 13:00:58 5 1349206 446156 2538744 1826080 1925560
2010-04-02 13:00:58 2010-04-02 13:10:58 5 1548541 510067 4085416 681328 1468344
2010-04-02 13:10:58 2010-04-02 13:20:58 5 1491005 480055 5549976 105384 604816
2010-04-02 13:20:58 2010-04-02 13:30:58 5 539016 179249 6271920 10264 9224
2010-04-02 13:30:58 2010-04-02 13:40:58 5 34811 20530 6267728 13544 9544
2010-04-02 13:40:58 2010-04-02 13:50:58 5 46132 15782 6283504 6872 1032
2010-04-02 13:50:58 2010-04-02 14:00:58 5 36460 6797 6267664 22712 1032
BEGIN_TIME END_TIME UNDOTSN UNDOBLKS TXNCOUNT ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS
------------------- ------------------- ---------- ---------- ---------- ---------- ------------- -----------
2010-04-02 14:00:58 2010-04-02 14:10:58 5 12645 14259 6267600 22616 1192
2010-04-02 14:10:58 2010-04-02 14:20:58 5 17126 9253 6267728 23232 448
2010-04-02 14:20:58 2010-04-02 14:30:58 5 20506 9820 6267584 23776 48
2010-04-02 14:30:58 2010-04-02 14:31:52 5 1398 123 6267584 23776 48
回滚段是在12：40 后暴增的
查询回滚段信息：
SQL> Select rn.Name "Rollback Segment", rs.RSSize/1024 "Size (KB)", rs.Gets "Gets",
2 rs.waits "Waits", (rs.Waits/rs.Gets)*100 "% Waits",
3 rs.Shrinks "# Shrinks", rs.Extends "# Extends"
4 from sys.v_$RollName rn, sys.v_$RollStat rs
5 where rn.usn = rs.usn;
Rollback Segment Size (KB) Gets Waits % Waits # Shrinks # Extends
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
SYSTEM 376 152101 2 .001314916 0 0
_SYSSMU1$ 120 56021162 9756 .017414848 3227 25560
_SYSSMU2$ 120 47459491 7419 .015632279 2651 18192
_SYSSMU3$ 120 41812296 8511 .020355256 2601 16960
_SYSSMU4$ 120 44189645 7329 .016585334 2403 16848
_SYSSMU5$ 120 51001595 10705 .02098954 2963 21132
_SYSSMU6$ 120 51457078 10776 .020941725 2754 19141
_SYSSMU7$ 120 47735049 7115 .014905191 2694 17007
_SYSSMU8$ 120 62508686 9670 .01546985 3622 24675
_SYSSMU9$ 24696 51094011 8305 .016254351 2559 18591
_SYSSMU10$ 120 58916319 7872 .013361324 3749 22100
Rollback Segment Size (KB) Gets Waits % Waits # Shrinks # Extends
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
_SYSSMU11$ 120 68208929 9049 .013266592 3419 27610
_SYSSMU12$ 184 62672318 11865 .018931803 2717 18321
_SYSSMU13$ 120 60866759 9308 .015292419 3293 21922
_SYSSMU14$ 120 66988262 20756 .030984533 4426 30605
_SYSSMU15$ 120 50258354 8803 .017515496 2586 16314
_SYSSMU16$ 120 56659587 9840 .017366876 2589 17050
_SYSSMU17$ 120 56963538 10738 .018850655 3250 20878
_SYSSMU18$ 120 72897759 9877 .013549113 3505 24728
_SYSSMU19$ 29944 59770203 9357 .015654958 3214 19779
_SYSSMU20$ 120 67805143 9580 .014128722 4182 26091
_SYSSMU21$ 120 85040916 16143 .018982627 3969 29273
Rollback Segment Size (KB) Gets Waits % Waits # Shrinks # Extends
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
_SYSSMU22$ 120 55968970 11524 .02058998 3059 20065
_SYSSMU23$ 120 55742329 9812 .017602422 3227 24323
_SYSSMU24$ 34424 53590656 10041 .018736475 2952 20104
_SYSSMU25$ 120 58052115 12054 .020764101 3574 26584
_SYSSMU26$ 120 42554823 7550 .01774182 2162 14125
_SYSSMU27$ 120 43261316 8439 .019507035 3155 18524
_SYSSMU28$ 120 66111409 13287 .020097893 4211 29985
_SYSSMU29$ 120 45371939 7935 .017488783 2693 17800
_SYSSMU30$ 248 85555794 15960 .018654493 4217 32121
_SYSSMU31$ 120 13668985 1954 .014295136 499 4450
_SYSSMU32$ 120 47540306 9305 .019572865 2989 20157
Rollback Segment Size (KB) Gets Waits % Waits # Shrinks # Extends
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
_SYSSMU33$ 696 47306494 8730 .018454126 2568 17752
_SYSSMU34$ 120 66660034 8424 .012637257 4006 26353
_SYSSMU35$ 120 65738977 10783 .01640275 3537 25910
_SYSSMU36$ 120 51569185 9200 .017840111 2563 17597
_SYSSMU37$ 120 72180993 8881 .012303793 4089 30201
_SYSSMU38$ 120 85766804 12094 .014101027 4841 31383
_SYSSMU39$ 120 24544488 3442 .014023515 786 6707
_SYSSMU40$ 120 57699743 8807 .0152635 2057 13004
_SYSSMU41$ 120 49636786 10159 .020466676 1335 9629
_SYSSMU42$ 120 21110981 3421 .016204837 402 2282
_SYSSMU43$ 120 18447524 3842 .020826643 445 1947
Rollback Segment Size (KB) Gets Waits % Waits # Shrinks # Extends
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
_SYSSMU44$ 120 17959541 2315 .012890084 507 2749
_SYSSMU45$ 120 13831290 2231 .016130093 560 2685
_SYSSMU46$ 312 22693835 2243 .009883742 630 2420
_SYSSMU47$ 120 12422546 1662 .0133789 551 2188
_SYSSMU48$ 846600 20104881 2694 .013399731 161 5378
_SYSSMU49$ 120 11568765 3795 .032803847 380 1101
_SYSSMU50$ 65008 20222108 11295 .055854711 353 1211
_SYSSMU51$ 184 24751899 12453 .050311291 174 245
_SYSSMU52$ 120 9319361 1801 .019325359 56 310
_SYSSMU53$ 120 8181540 1725 .02108405 106 123
_SYSSMU54$ 120 16346452 1920 .011745668 174 204
Rollback Segment Size (KB) Gets Waits % Waits # Shrinks # Extends
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
_SYSSMU55$ 120 8999063 2913 .032370037 188 412
_SYSSMU56$ 120 7317748 1291 .01764204 117 134
_SYSSMU57$ 120 7011466 1587 .022634354 268 463
_SYSSMU66$ 120 8092300 2166 .026766185 81 344
_SYSSMU67$ 1208 9702816 3054 .031475398 173 429
_SYSSMU68$ 120 15439969 1537 .009954683 198 212
_SYSSMU69$ 27960 9980708 3813 .038203703 426 714
_SYSSMU70$ 120 10045692 1173 .011676647 114 136
_SYSSMU71$ 1720 10637586 1659 .015595644 141 199
_SYSSMU72$ 120 8802145 1757 .019961044 259 279
_SYSSMU73$ 120 15702768 6459 .041132875 161 204
Rollback Segment Size (KB) Gets Waits % Waits # Shrinks # Extends
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
_SYSSMU74$ 120 5649113 1380 .024428614 220 240
_SYSSMU75$ 120 7308039 793 .010851064 211 241
_SYSSMU76$ 120 9098629 1529 .01680473 182 529
_SYSSMU77$ 120 6467111 888 .013731015 174 269
_SYSSMU78$ 120 6134182 1105 .018013812 232 368
_SYSSMU79$ 120 7352217 1160 .015777554 158 237
_SYSSMU80$ 120 5009302 774 .015451254 306 354
_SYSSMU81$ 30136 2408826 522 .021670307 233 487
_SYSSMU82$ 3121352 8267838 639 .007728744 0 3794
_SYSSMU83$ 120 1836469 460 .025048068 177 246
_SYSSMU84$ 120 1573384 208 .013219913 152 216
48 和82 回滚段上有很大的事务
这个两个回滚段上的状态都只有active
QL> select status, sum(blocks)
2 from dba_undo_extents
3 where segment_name='_SYSSMU48$'
4 group by status;
STATUS SUM(BLOCKS)
--------- -----------
ACTIVE 3251560
SQL> select status, sum(blocks)
from dba_undo_extents
2 3 where segment_name='_SYSSMU82$'
4 group by status;
STATUS SUM(BLOCKS)
--------- -----------
ACTIVE 3011624
其中48 回滚段上没有事务，基本上可以确定死事务是在48 上了
SQL> select
2 r.name "RBS name",
3 t.start_time,
4 t.used_ublk "Undo blocks",
5 t.used_urec "Undo recs"
6 from
7 gv$transaction t,
8 v$rollname r
9 where r.usn = t.xidusn
10 and r.name = '_SYSSMU48$';
no rows selected
SQL> select
2 r.name "RBS name",
3 t.start_time,
4 t.used_ublk "Undo blocks",
5 t.used_urec "Undo recs"
6 from
7 gv$transaction t,
8 v$rollname r
9 where r.usn = t.xidusn
10 and r.name = '_SYSSMU82$';
RBS name START_TIME Undo blocks Undo recs
------------------------------ -------------------- ----------- ----------
_SYSSMU82$ 04/02/10 11:08:16 331476 662950
检查死事务状态：
SQL> select distinct KTUXECFL,count(*) from x$ktuxe group by KTUXECFL;
KTUXECFL COUNT(*)
------------------------ ----------
NONE 4177
DEAD 1
SQL>
SQL> select * from x$ktuxe where KTUXECFL='DEAD';
ADDR INDX INST_ID KTUXEUSN KTUXESLT KTUXESQN KTUXERDBF KTUXERDBB KTUXESCNB KTUXESCNW KTUXESTA KTUXECFL KTUXEUEL KTUXEDDBF KTUXEDDBB KTUXEPUSN KTUXEPSLT
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ------------------------ ---------- ---------- ---------- ---------- ----------
KTUXEPSQN KTUXESIZ
---------- ----------
000000011055FDC8 2358 1 48 4 33387 304 964506 4279856868 20 ACTIVE DEAD 4219 0 0 0 0
0 725971
发生在48 的槽4 上,和前面的猜测一致
检查恢复进度：
SQL> set time on
14:40:14 SQL> /
ADDR KTUXEUSN KTUXESLT KTUXESQN KTUXESIZ
---------------- ---------- ---------- ---------- ----------
000000011055FDC8 48 4 33387 712617
14:40:16 SQL> /
ADDR KTUXEUSN KTUXESLT KTUXESQN KTUXESIZ
---------------- ---------- ---------- ---------- ----------
000000011055FDC8 48 4 33387 709325
12 s 恢复了3200多个块，计算下大概还要43分钟才能恢复完了，只能慢慢等了。
[@more@]来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24071676/viewspace-1034426/ ，如需转载，请注明出处，否则将追究法律责任。
