首页 » ORACLE 9i-23c » Troubleshooting ORA-600 [4187] Undo segment sequence number is exhausted
Troubleshooting ORA-600 [4187] Undo segment sequence number is exhausted
每个事务都有一个唯一的 ID，称为 XID，基于Undo segment number 、Undo segment slot number and Undo segment sequence number (wrap#)，为每个事务在第一个 DML语句期间分配一个事务 ID（XID）, 例如：0002.028.000004DA. 要查明transaction是否真的被提交，需要查看transaction table的描述符。transaction table在每个undo 段头可以转储如alter system dump undo header ‘xxx’, 每次transaction table的slot重用时, slot wrap(sequence number) number就会增加.当slot重用(或wrap#)达到maximum value 4,294,967,295 (2^32 – 1)(0xffffffff)次数时，当再次增加 wrap number 1或多值时，通常会提示ORA-1558 或ORA-600[4187]出现，由于撤销段分配的 AUM 算法，这也应该是非常罕见的。但是执行量非常大事务的库，在undo段非常少时还是有发生的概率，最近这个客户就遇到了（ORACLE RAC 11.2.0.4）。
现象
Fri Nov 19 15:10:12 2021
Thread 1 advanced to log sequence 54671 (LGWR switch)
Current log# 5 seq# 54671 mem# 0: +DATA/anbob/onlinelog/group_5.287.904064243
Fri Nov 19 15:10:16 2021
Archived Log entry 52856 added for thread 1 sequence 54670 ID 0xfe89eeec dest 1:
Fri Nov 19 15:12:09 2021
Errors in file /u01/oracle/app/oracle/diag/rdbms/anbob/anbob1/trace/anbob1_ora_23451.trc (incident=189693):
ORA-00600: internal error code, arguments: [4187], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/oracle/app/oracle/diag/rdbms/anbob/anbob1/incident/incdir_189693/anbob1_ora_23451_i189693.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Alert中出现的ora-600 [4187]，部分事务无法继续，随后系统会生成较大的trace日志，有可能会导致ORACLE_HOME磁盘空间耗尽， 通常这种情况重启实例也无法彻底解决，客户在反复重启后，可能会出现下面其它的ORA-600 错误。
行 5958: ORA-00600: internal error code, arguments: [4187], [], [], [], [], [], [], [], [], [], [], [] 行 5963: ORA-00600: internal error code, arguments: [kfncSlaveMsgFree1], [0], [0], [0], [], [], [], [], [], [], [], [] 行 5965: ORA-00600: internal error code, arguments: [4187], [], [], [], [], [], [], [], [], [], [], [] 行 5972: ORA-00600: internal error code, arguments: [kfgRecoverCreation_0], [], [], [], [], [], [], [], [], [], [], [] 行 5973: ORA-00600: internal error code, arguments: [kfncSlaveMsgFree1], [0], [0], [0], [], [], [], [], [], [], [], [] 行 5975: ORA-00600: internal error code, arguments: [4187], [], [], [], [], [], [], [], [], [], [], [] 行 5984: ORA-00600: internal error code, arguments: [kfioTranslateIO03], [], [], [], [], [], [], [], [], [], [], [] 行 5985: ORA-00600: internal error code, arguments: [kfgRecoverCreation_0], [], [], [], [], [], [], [], [], [], [], [] 行 5986: ORA-00600: internal error code, arguments: [kfncSlaveMsgFree1], [0], [0], [0], [], [], [], [], [], [], [], []
因为事务无法继续，可能在数据库层分析性能SQL就容易被误解，毕竟事务挂起无法继续。事务中断回滚清理、UNDO header Block recovery等，甚至可能存在SMON实例回滚时的内部错误，如果smon遇到100次内部错误则会重启实例（参数_smon_internal_errlimit控制）。在实例重启后出现ORA-15221 raised in ASM I/O path等ora-600 KF 相关的ASM事务报错，PMON会终止实例。
PMON TRACE
*** 2021-11-19 16:27:44.257 *** SESSION ID:(567.1) 2021-11-19 16:27:44.257 *** CLIENT ID:() 2021-11-19 16:27:44.257 *** SERVICE NAME:(SYS$BACKGROUND) 2021-11-19 16:27:44.257 *** MODULE NAME:() 2021-11-19 16:27:44.257 *** ACTION NAME:() 2021-11-19 16:27:44.257 Doing block recovery for file 3 block 272 Block header before block recovery: buffer tsn: 2 rdba: 0x00c00110 (3/272) scn: 0x0f9b.ed0bc9e7 seq: 0x01 flg: 0x04 tail: 0xc9e72601 frmt: 0x02 chkval: 0x5ec8 type: 0x26=KTU SMU HEADER BLOCK Resuming block recovery (PMON) for file 3 block 272 Block recovery from logseq 54683, block 996 to scn 17162371681671 *** 2021-11-19 16:27:44.270 Recovery of Online Redo Log: Thread 1 Group 5 Seq 54683 Reading mem 0 Block recovery completed at rba 54683.998.16, scn 3995.3977334373 ==== Redo read statistics for thread 1 ==== Total physical reads (from disk and memory): 1541Kb -- Redo read_disk statistics -- Read rate (ASYNC): 0Kb in 0.01s => 0.00 Mb/sec -- Redo read_memory statistics -- Read disk 0Kb and read memory 1541Kb, hit-ratio=1.00 Precise schema: 1541Kb valid, 0Kb invalid, valid-ratio 1.00 Longest record: 0Kb, moves: 0/4 (0%) Longest LWN: 1Kb, moves: 0/3 (0%), moved: 0Mb Last redo scn: 0x0f9b.ed114d64 (17162371681636) ---------------------------------------------- Block image after block recovery: buffer tsn: 2 rdba: 0x00c00110 (3/272) scn: 0x0f9b.ed0bc9e7 seq: 0x01 flg: 0x04 tail: 0xc9e72601 frmt: 0x02 chkval: 0x5ec8 type: 0x26=KTU SMU HEADER BLOCK Retention Table ----------------------------------------------------------- Extent Number:0 Commit Time: 1637305903 Extent Number:1 Commit Time: 1637305905 Extent Number:2 Commit Time: 1637305905 Extent Number:3 Commit Time: 1637303989 Extent Number:4 Commit Time: 1637304038 ... ... Extent Number:28 Commit Time: 1637305842 Extent Number:29 Commit Time: 1637305901 Extent Number:30 Commit Time: 1637305901 TRN CTL:: seq: 0xd14f chd: 0x0009 ctl: 0x0004 inc: 0x00000000 nfb: 0x0000 mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe) uba: 0x00c06434.d14f.2c scn: 0x0f9b.ed0bc826 Version: 0x01 FREE BLOCK POOL:: uba: 0x00000000.d14f.2b ext: 0x2 spc: 0x1dc uba: 0x00000000.d14f.2a ext: 0x2 spc: 0x70e uba: 0x00000000.d14b.02 ext: 0x1e spc: 0x1f02 uba: 0x00000000.ce3f.02 ext: 0x12 spc: 0x14da uba: 0x00000000.3226.02 ext: 0x32 spc: 0x14ae TRN TBL:: index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt ------------------------------------------------------------------------------------------------ 0x00 9 0x00 0xfffffa0c 0x000c 0x0f9b.ed0bc8f1 0x00c06434 0x0000.000.00000000 0x00000001 0x00000000 1637305928 0x01 9 0x00 0xfffff4ab 0x0016 0x0f9b.ed0bc84e 0x00c06434 0x0000.000.00000000 0x00000001 0x00000000 1637305927 0x02 9 0x00 0xfffff3aa 0x0008 0x0f9b.ed0bc934 0x00c06434 0x0000.000.00000000 0x00000001 0x00000000 1637305928 0x03 9 0x00 0xfffff8d9 0x000e 0x0f9b.ed0bc985 0x00c06434 0x0000.000.00000000 0x00000001 0x00000000 1637305928 0x04 9 0x00 0xfffffce8 0xffff 0x0f9b.ed0bc9e7 0x00c06434 0x0000.000.00000000 0x00000001 0x00000000 1637305928 0x05 9 0x00 0xfffff627 0x001a 0x0f9b.ed0bc833 0x00c06434 0x0000.000.00000000 0x00000001 0x00000000 1637305927 0x06 9 0x00 0xfffff4e6 0x0004 0x0f9b.ed0bc9cb 0x00c06434 0x0000.000.00000000 0x00000001 0x00000000 1637305928 0x07 9 0x00 0xffffece5 0x000b 0x0f9b.ed0bc85c 0x00c06434 0x0000.000.00000000 0x00000001 0x00000000 1637305927 0x08 9 0x00 0xfffff724 0x0021 0x0f9b.ed0bc93a 0x00c06434 0x0000.000.00000000 0x00000001 0x00000000 1637305928 0x09 9 0x00 0xfffffff3 0x0015 0x0f9b.ed0bc828 0x00c06434 0x0000.000.00000000 0x00000001 0x00000000 1637305927 0x0a 9 0x00 0xfffffaf2 0x0018 0x0f9b.ed0bc90c 0x00c06434 0x0000.000.00000000 0x00000001 0x00000000 1637305928 0x0b 9 0x00 0xfffff671 0x0010 0x0f9b.ed0bc867 0x00c06434 0x0000.000.00000000 0x00000001 0x00000000 1637305927 0x0c 9 0x00 0xfffffec0 0x001e 0x0f9b.ed0bc900 0x00c06434 0x0000.000.00000000 0x00000001 0x00000000 1637305928 0x0d 9 0x00 0xfffff8bf 0x0020 0x0f9b.ed0bc889 0x00c06434 0x0000.000.00000000 0x00000001 0x00000000 1637305928 0x0e 9 0x00 0xfffff4ce 0x0013 0x0f9b.ed0bc9ab 0x00c06434 0x0000.000.00000000 0x00000001 0x00000000 1637305928 0x0f 9 0x00 0xfffff64d 0x000d 0x0f9b.ed0bc875 0x00c06434 0x0000.000.00000000 0x00000001 0x00000000 1637305927 0x10 9 0x00 0xfffff5ec 0x000f 0x0f9b.ed0bc86b 0x00c06434 0x0000.000.00000000 0x00000001 0x00000000 1637305927 ... 0x1d 9 0x00 0xfffff23f 0x0019 0x0f9b.ed0bc91e 0x00c06434 0x0000.000.00000000 0x00000001 0x00000000 1637305928 0x1e 9 0x00 0xfffff67e 0x000a 0x0f9b.ed0bc908 0x00c06434 0x0000.000.00000000 0x00000001 0x00000000 1637305928 0x1f 9 0x00 0xfffff1ad 0x0003 0x0f9b.ed0bc982 0x00c06434 0x0000.000.00000000 0x00000001 0x00000000 1637305928 0x20 9 0x00 0xfffffb0c 0x001b 0x0f9b.ed0bc8ba 0x00c06434 0x0000.000.00000000 0x00000001 0x00000000 1637305928 0x21 9 0x00 0xfffff1eb 0x0011 0x0f9b.ed0bc943 0x00c06434 0x0000.000.00000000 0x00000001 0x00000000 1637305928 EXT TRN CTL:: Incident 328031 created, dump file: /u01/oracle/app/oracle/diag/rdbms/lcfa/LCFA1/incident/incdir_328031/LCFA1_pmon_11307_i328031.trc ORA-00600: internal error code, arguments: [kfgRecoverCreation_0], [], [], [], [], [], [], [], [], [], [], [] PMON: fatal error while deleting s.o. 0x6ae9d8a18 in this tree: ---------------------------------------- SO: 0x6d91e2ea0, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3 proc=0x6d91e2ea0, name=process, file=ksu.h LINE:12721, pg=0 (process) Oracle pid:50, ser:3, calls cur/top: 0x65863abc0/0x65863abc0 flags : (0x1) DEAD flags2: (0x8000), flags3: (0x10) intr error: 0, call error: 0, sess error: 0, txn error 0 intr queue: empty ksudlp FALSE at location: 0 Cleanup details: Found dead = 1 sec ago Total Cleanup attempts = 1, Cleanup time = 0 sec, Cleanup timer = 0.000 sec Last attempt (full) started 1 sec ago, Length = in progress (post info) last post received: 0 0 51 last post received-location: ksv2.h LINE:1679 ID:ksvpst: workdone last process to post me: 0x6d51f8310 1 2 last post sent: 0 0 26 last post sent-location: ksa2.h LINE:285 ID:ksasnd last process posted by me: 0x6ed26d1a0 1 6 (latch info) wait_event=0 bits=0x0 Process Group: DEFAULT, pseudo proc: 0x6ed5819c8 O/S info: user: grid, term: UNKNOWN, ospid: 12107 (DEAD) OSD pid info: Unix process pid: 12107, image: oracle@ ...
Kernel Transaction Undo tranXaction Control (KTUXC): Maintains control information to rollback changes to the transaction table itself.
从上面transaction table header(ktuxc)显示chd 9# slot， 而slot 9的wrap#为 0xfffffff3已经非常接近上限0xffffffff，并且其它slot同样也非常高。
wrap#增长当前采用的是在执行ktubnd函数为事务绑定undo段时，会调用kjqghd去计算出一个重用slot递增值delta，这个delta限制必须小于16(由KTU_MAX_KSQN_DELTA定义)，所以就可能会出现 0xfffffff3 +delta的值超过KSQNMAXVAL，显示为负值。
从MOS中可以确认存在该问题Doc ID 19700135.8
ORA-600 [4187] can occur for undo segments where wrap# is close to the max value of 0xffffffff (KSQNMAXVAL).
This normally affects databases with high transaction rate that have existed for a relatively long time.To identify undo segments causing the above error and others that may potentially cause it
in the future, run the next query:select b.segment_name, b.tablespace_name
,a.ktuxeusn “Undo Segment Number”
,a.ktuxeslt “Slot”
,a.ktuxesqn “Wrap#”
from x$ktuxe a, dba_rollback_segs b
where a.ktuxesqn > -429496730 and a.ktuxesqn < 0
and a.ktuxeusn = b.segment_id;Then drop the undo segments or the undo tablespace from the output above.
数据库中查询以上SQL
SQL> set lines 999
SQL> select b.segment_name, b.tablespace_name
2 ,a.ktuxeusn "Undo Segment Number"
3 ,a.ktuxeslt "Slot"
4 ,a.ktuxesqn "Wrap#"
5 from x$ktuxe a, dba_rollback_segs b
6 where a.ktuxesqn > -429496730 and a.ktuxesqn < 0
7 and a.ktuxeusn = b.segment_id;
SEGMENT_NAME TABLESPACE_NAME Undo Segment Number Slot Wrap#
-------------------------------- ------------------------ ------------------- ---------- ----------
_SYSSMU10_562384330$ UNDOTBS1 10 0 -1524
_SYSSMU10_562384330$ UNDOTBS1 10 1 -2901
_SYSSMU10_562384330$ UNDOTBS1 10 2 -3158
_SYSSMU10_562384330$ UNDOTBS1 10 3 -1831
_SYSSMU10_562384330$ UNDOTBS1 10 4 -792
_SYSSMU10_562384330$ UNDOTBS1 10 5 -2521
_SYSSMU10_562384330$ UNDOTBS1 10 6 -2842
_SYSSMU10_562384330$ UNDOTBS1 10 7 -4891
_SYSSMU10_562384330$ UNDOTBS1 10 8 -2268
_SYSSMU10_562384330$ UNDOTBS1 10 9 -13
_SYSSMU10_562384330$ UNDOTBS1 10 10 -1294
...
...
_SYSSMU10_562384330$ UNDOTBS1 10 22 -666
_SYSSMU10_562384330$ UNDOTBS1 10 23 -555
_SYSSMU10_562384330$ UNDOTBS1 10 24 -3596
_SYSSMU10_562384330$ UNDOTBS1 10 25 -3325
_SYSSMU10_562384330$ UNDOTBS1 10 26 -2670
_SYSSMU10_562384330$ UNDOTBS1 10 27 -1551
_SYSSMU10_562384330$ UNDOTBS1 10 28 -4384
_SYSSMU10_562384330$ UNDOTBS1 10 29 -3521
_SYSSMU10_562384330$ UNDOTBS1 10 30 -2434
_SYSSMU10_562384330$ UNDOTBS1 10 31 -3667
_SYSSMU10_562384330$ UNDOTBS1 10 32 -1268
'''
Note:
大量slot的wrap# 已经出现的溢出为负值。
transaction table的说明
To find out whether the transaction has really been committed, it is necessary to look at its descriptor in the transaction table.
The transaction ID (or XID) enables you to find the transaction descriptor:
• usn: Number of the undo segment which was assigned to the transaction
• slot: Number of the entry in the transaction table (contained in the header of the undo segment) corresponding to the transaction descriptor.
This is the index column in the transaction table.
• seq: Sequence number indicating reuse of the slot. This number is incremented as each slot is reused. It corresponds to the wrap# column in the transaction table.
All this information is collected in a memory area called the cleanout information area. This information is then used to actually apply the changes.
The transaction table holds a pointer to the head and tail of the committed transaction table slots. Each inactive entry in the transaction table points to the next committed (inactive) slot.
They are ordered by commit SCN, starting at the oldest transaction to have committed. If the committed list is empty after several attempts at traversing it, the transaction table remains full
and an error is reported. This indicates that there are not enough undo segments for the amount of concurrent transactions in the database. However, since AUM tries to allocate each concurrent transaction to their
own undo segment, this should be extremely rare to see. If this error does occur, the solution is to increase the size of the undo tablespace to allow more undo segments to be created.
At instance startup each undo tablespace will contain 10 undo segments. The number of additional segments brought online during instance startup is based on the SESSIONS parameter.
Oracle allocates approximately one undo segment for every five sessions. These are sized according to the auto-allocate algorithm for locally managed tablespaces. The basic algorithm
is that the first 16 extents are 64 KB in size. During subsequent allocation the next 63 extents are 1 MB, the next 120 extents are 8 MB and all additional extents are 64 MB.
事务绑定UNDO段的原则是尽可能的将活动事务平均的各个undo段上，具体算法为：
1，在当前undo tablespace中的online undo segment中寻找事务表中没有活动事务的undo segment
2，如果没有找到则尝试在当前undo tablespace online 那些处于offline状态的undo segment
3，如果没有找到则尝试在当前undo tablespace创建undo segment并online
4，如果无法创建则会寻找最近最少使用的undo segment
到这一步既然确认了是业务特性高频事务导致undo 段的slot wrap#耗尽，检查一下UNDO段个数，发现当前的undo 表空间为大小1.5G （数据文件自动扩展），判断应该是大量的小事务，如loop 中insert value ommit等， 对于这么高频率的事务的数据库节点,同时因为UNDO段个数少，即使是undo段使用平衡,也会存在耗尽的风险， 如果个别segment的slot wrap#高，可以drop undo segment，但是如果普遍undo段wrap#使用都高，可以先创建新的UNDO表空间，修改数据库默认表空间解决，后期删聊原UNDO表空间。
预防
1， 初始化更多的UNDO段，使用_rollback_segment_count参数
2， 增加巡检项
select b.segment_name, b.tablespace_name
,a.ktuxeusn "Undo Segment Number"
,a.ktuxeslt "Slot"
,a.ktuxesqn "Wrap#"
from x$ktuxe a, dba_rollback_segs b
where a.ktuxeusn = b.segment_id
and ktuxesqn>power(2,20); -- change
下面带这个问题做个测试：
一个SESSION 大量小事务会不会只使用1个rollback seg? 重启实例rollback seg wrap#会不会重置?
-- env 11.2.0.4 RAC --
SQL> select * from v$rollname
2 ;
USN NAME
---------- ------------------------------------------------------------
0 SYSTEM
3001 _SYSSMU3001_2524665243$
3002 _SYSSMU3002_3274207932$
3003 _SYSSMU3003_551481765$
3004 _SYSSMU3004_3828969324$
3005 _SYSSMU3005_2166630833$
3006 _SYSSMU3006_2644181912$
3007 _SYSSMU3007_2679219495$
3008 _SYSSMU3008_2173666674$
3009 _SYSSMU3009_3685259267$
3010 _SYSSMU3010_1327269793$
11 rows selected.
SQL> alter system dump undo header '_SYSSMU3010_1327269793$';
System altered.
SQL> @t
TRACEFILE
---------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/rac11g/rac11g2/trace/rac11g2_ora_7583.trc
SQL> host
oracle@11g-node2:/home/oracle/tpt-oracle-master $vi /u01/app/oracle/diag/rdbms/rac11g/rac11g2/trace/rac11g2_ora_7583.trc
0x01 9 0x00 0x04f8 0x0008 0x0000.03fcd3b4 0x014089d6 0x0000.000.00000000 0x00000001 0x00000000 1714102509
0x02 9 0x00 0x04fa 0xffff 0x0000.03fd3944 0x014089e1 0x0000.000.00000000 0x00000004 0x00000000 1714102536
0x03 9 0x00 0x04e3 0x0020 0x0000.03fc9460 0x014089d3 0x0000.000.00000000 0x00000001 0x00000000 1714101009
0x04 9 0x00 0x04f7 0x000b 0x0000.03fc93c6 0x014089d3 0x0000.000.00000000 0x00000001 0x00000000 1714101009
0x05 9 0x00 0x04f3 0x0011 0x0000.03fc809c 0x014089d3 0x0000.000.00000000 0x00000001 0x00000000 1714100707
0x06 9 0x00 0x04f5 0x001d 0x0000.03fcbca7 0x014089d3 0x0000.000.00000000 0x00000001 0x00000000 1714101791
0x07 9 0x00 0x04f0 0x0019 0x0000.03fc6a27 0x014089d3 0x0000.000.00000000 0x00000001 0x00000000 1714100408
0x08 9 0x00 0x04f5 0x001c 0x0000.03fcd3b8 0x014089d6 0x0000.000.00000000 0x00000001 0x00000000 1714102510
0x09 9 0x00 0x04f1 0x000a 0x0000.03fc813a 0x014089d3 0x0000.000.00000000 0x00000001 0x00000000 1714100709
0x0a 9 0x00 0x04ee 0x0004 0x0000.03fc815a 0x014089d3 0x0000.000.00000000 0x00000001 0x00000000 1714100710
0x0b 9 0x00 0x04ed 0x000d 0x0000.03fc93ca 0x014089d3 0x0000.000.00000000 0x00000001 0x00000000 1714101009
0x0c 9 0x00 0x04fb 0x0012 0x0000.03fc9415 0x014089d3 0x0000.000.00000000 0x00000001 0x00000000 1714101009
0x0d 9 0x00 0x04fa 0x000f 0x0000.03fc93d8 0x014089d3 0x0000.000.00000000 0x00000001 0x00000000 1714101009
0x0e 9 0x00 0x04fa 0x001b 0x0000.03fd307f 0x014089d6 0x0000.000.00000000 0x00000001 0x00000000 1714102535
0x0f 9 0x00 0x04f9 0x001f 0x0000.03fc93e4 0x014089d3 0x0000.000.00000000 0x00000001 0x00000000 1714101009
0x10 9 0x00 0x04f8 0x0001 0x0000.03fcd05e 0x014089d3 0x0000.000.00000000 0x00000001 0x00000000 1714102208
0x11 9 0x00 0x04fa 0x0009 0x0000.03fc809f 0x014089d3 0x0000.000.00000000 0x00000001 0x00000000 1714100707
0x12 9 0x00 0x04f8 0x0014 0x0000.03fc9436 0x014089d3 0x0000.000.00000000 0x00000001 0x00000000 1714101009
0x13 9 0x00 0x04f3 0x0018 0x0000.03fc6a7d 0x014089d3 0x0000.000.00000000 0x00000001 0x00000000 1714100408
0x14 9 0x00 0x04ee 0x001e 0x0000.03fc9455 0x014089d3 0x0000.000.00000000 0x00000001 0x00000000 1714101009
0x15 9 0x00 0x04eb 0x000e 0x0000.03fd307b 0x014089d6 0x0000.000.00000000 0x00000001 0x00000000 1714102535
0x16 9 0x00 0x04ee 0x0000 0x0000.03fd3922 0x014089dc 0x0000.000.00000000 0x00000003 0x00000000 1714102536
0x17 9 0x00 0x04ed 0x0003 0x0000.03fc945c 0x014089d3 0x0000.000.00000000 0x00000001 0x00000000 1714101009
0x18 9 0x00 0x04ef 0x0005 0x0000.03fc6a82 0x014089d3 0x0000.000.00000000 0x00000001 0x00000000 1714100408
0x19 9 0x00 0x04ee 0x0013 0x0000.03fc6a64 0x014089d3 0x0000.000.00000000 0x00000001 0x00000000 1714100408
0x1a 9 0x00 0x04f1 0x0006 0x0000.03fcbca2 0x014089d3 0x0000.000.00000000 0x00000001 0x00000000 1714101791
0x1b 9 0x00 0x04f1 0x0016 0x0000.03fd3914 0x014089da 0x0000.000.00000000 0x00000003 0x00000000 1714102536
0x1c 9 0x00 0x04f0 0x0021 0x0000.03fd2d4d 0x014089d6 0x0000.000.00000000 0x00000001 0x00000000 1714102532
0x1d 9 0x00 0x04ea 0x0010 0x0000.03fcbcab 0x014089d3 0x0000.000.00000000 0x00000001 0x00000000 1714101791
0x1e 9 0x00 0x04ef 0x0017 0x0000.03fc9457 0x014089d3 0x0000.000.00000000 0x00000001 0x00000000 1714101009
0x1f 9 0x00 0x04ee 0x000c 0x0000.03fc93f3 0x014089d3 0x0000.000.00000000 0x00000001 0x00000000 1714101009
0x20 9 0x00 0x04ea 0x001a 0x0000.03fc9729 0x014089d3 0x0000.000.00000000 0x00000001 0x00000000 1714101310
0x21 9 0x00 0x04ec 0x0015 0x0000.03fd306f 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1714102534
EXT TRN CTL::
usn: 3010
SQL> create table anbob.test_undo(id int);
Table created.
SQL> insert into anbob.test_undo values(1);
1 row created.
SQL> commit;
Commit complete.
# lots of transactions
declare
i int:=0 ;
begin
while i<=1e6 loop
update anbob.test_undo set id=id+1;
i:=i+1;
commit;
end loop;
end;
/
# check rollback segment
SQL> @trans
SID SERIAL# USERNAME TADDR SES_ADDR USED_UBLK USED_UREC 0xFLAG STATUS START_DATE XIDUSN XIDSLOT XIDSQN XID PRV_XID PTX_XID
---------- ---------- ----------- ---------------- ---------------- ---------- ---------- ------------------ --------------- ------------------- ---------- ---------- ---------- ---------------- ---------------- ----------------
1347 1 SYS 000000014ECC2108 000000015A6E5F40 1 1 1E03 INACTIVE 2024-04-26 11:53:12 3001 1 1765 B90B0100E5060000 0000000000000000 0000000000000000
SQL> /
SID SERIAL# USERNAME TADDR SES_ADDR USED_UBLK USED_UREC 0xFLAG STATUS START_DATE XIDUSN XIDSLOT XIDSQN XID PRV_XID PTX_XID
---------- ---------- ----------- ---------------- ---------------- ---------- ---------- ------------------ --------------- ------------------- ---------- ---------- ---------- ---------------- ---------------- ----------------
1347 1 SYS 000000014ECC2108 000000015A6E5F40 1 1 1E03 INACTIVE 2024-04-26 11:53:12 3010 2 3609 C20B0200190E0000 0000000000000000 0000000000000000
SQL> /
SID SERIAL# USERNAME TADDR SES_ADDR USED_UBLK USED_UREC 0xFLAG STATUS START_DATE XIDUSN XIDSLOT XIDSQN XID PRV_XID PTX_XID
---------- ---------- ----------- ---------------- ---------------- ---------- ---------- ------------------ --------------- ------------------- ---------- ---------- ---------- ---------------- ---------------- ----------------
1347 1 SYS 000000014ECC2108 000000015A6E5F40 1 1 C0B INACTIVE 2024-04-26 11:53:13 3008 22 1842 C00B160032070000 0000000000000000 0000000000000000
SQL> /
SID SERIAL# USERNAME TADDR SES_ADDR USED_UBLK USED_UREC 0xFLAG STATUS START_DATE XIDUSN XIDSLOT XIDSQN XID PRV_XID PTX_XID
---------- ---------- ----------- ---------------- ---------------- ---------- ---------- ------------------ --------------- ------------------- ---------- ---------- ---------- ---------------- ---------------- ----------------
1347 1 SYS 000000014ECC2108 000000015A6E5F40 0 0 1203 INACTIVE 2024-04-26 11:53:17 3004 14 1978 BC0B0E00BA070000 0000000000000000 0000000000000000
SQL> /
SID SERIAL# USERNAME TADDR SES_ADDR USED_UBLK USED_UREC 0xFLAG STATUS START_DATE XIDUSN XIDSLOT XIDSQN XID PRV_XID PTX_XID
---------- ---------- ----------- ---------------- ---------------- ---------- ---------- ------------------ --------------- ------------------- ---------- ---------- ---------- ---------------- ---------------- ----------------
1347 1 SYS 000000014ECC2108 000000015A6E5F40 1 1 1E03 INACTIVE 2024-04-26 11:53:17 3008 31 2021 C00B1F00E5070000 0000000000000000 0000000000000000
SQL> select * from v$rollname；
USN NAME
---------- ------------------------------------------------------------
0 SYSTEM
3001 _SYSSMU3001_2524665243$
3002 _SYSSMU3002_3274207932$
3003 _SYSSMU3003_551481765$
3004 _SYSSMU3004_3828969324$
3005 _SYSSMU3005_2166630833$
3006 _SYSSMU3006_2644181912$
3007 _SYSSMU3007_2679219495$
3008 _SYSSMU3008_2173666674$
3009 _SYSSMU3009_3685259267$
3010 _SYSSMU3010_1327269793$
11 rows selected.
SQL> alter system dump undo header '_SYSSMU3008_2173666674$';
System altered.
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x19d9 0x0002 0x0000.041d937f 0x0140e838 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x01 9 0x00 0x19d8 0x0008 0x0000.041d9349 0x0140e838 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x02 9 0x00 0x19da 0x0007 0x0000.041d9387 0x0140e838 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x03 9 0x00 0x19c4 0x0020 0x0000.041d9475 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x04 9 0x00 0x19d8 0x000b 0x0000.041d93ff 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x05 9 0x00 0x19d4 0x0011 0x0000.041d93e1 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x06 9 0x00 0x19d5 0x001d 0x0000.041d931e 0x0140e838 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x07 9 0x00 0x19d1 0x0019 0x0000.041d9392 0x0140e838 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x08 9 0x00 0x19d5 0x001c 0x0000.041d934d 0x0140e838 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x09 9 0x00 0x19d2 0x000a 0x0000.041d93f6 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x0a 9 0x00 0x19cf 0x0004 0x0000.041d93f7 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x0b 9 0x00 0x19ce 0x000d 0x0000.041d9403 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x0c 9 0x00 0x19dc 0x0012 0x0000.041d9421 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x0d 9 0x00 0x19db 0x000f 0x0000.041d940f 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x0e 9 0x00 0x19da 0x001b 0x0000.041d9366 0x0140e838 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x0f 9 0x00 0x19da 0x001f 0x0000.041d9411 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x10 9 0x00 0x19d8 0x0001 0x0000.041d933a 0x0140e838 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x11 9 0x00 0x19db 0x0009 0x0000.041d93eb 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x12 9 0x00 0x19d9 0x0014 0x0000.041d9429 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x13 9 0x00 0x19d4 0x0018 0x0000.041d93b0 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x14 9 0x00 0x19cf 0x001e 0x0000.041d943d 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x15 9 0x00 0x19cb 0x000e 0x0000.041d9364 0x0140e838 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x16 9 0x00 0x19ce 0x0000 0x0000.041d9379 0x0140e838 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x17 9 0x00 0x19ce 0x0003 0x0000.041d9461 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x18 9 0x00 0x19d0 0x0005 0x0000.041d93b6 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x19 9 0x00 0x19cf 0x0013 0x0000.041d939f 0x0140e838 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x1a 9 0x00 0x19d1 0x0006 0x0000.041d931c 0x0140e838 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x1b 9 0x00 0x19d1 0x0016 0x0000.041d936f 0x0140e838 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x1c 9 0x00 0x19d0 0x0021 0x0000.041d9351 0x0140e838 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x1d 9 0x00 0x19ca 0x0010 0x0000.041d932c 0x0140e838 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x1e 9 0x00 0x19d0 0x0017 0x0000.041d9449 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x1f 9 0x00 0x19cf 0x000c 0x0000.041d941b 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x20 9 0x00 0x19cb 0xffff 0x0000.041d947a 0x0140e83a 0x0000.000.00000000 0x00000002 0x00000000 1714103733
SQL> STARTUP FORCE
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2260088 bytes
Variable Size 1291846536 bytes
Database Buffers 2969567232 bytes
Redo Buffers 12107776 bytes
Database mounted.
Database opened.
oracle@11g-node2:/home/oracle $ora
SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 26 12:05:13 2024
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> alter system dump undo header '_SYSSMU3010_1327269793$';
System altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/rac11g/rac11g2/trace/rac11g2_ora_15681.trc
SQL> host vi /u01/app/oracle/diag/rdbms/rac11g/rac11g2/trace/rac11g2_ora_15681.trc
uba: 0x00000000.00bc.1a ext: 0x3 spc: 0xf3a
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x19da 0x0002 0x0000.041e0dbf 0x0140e83e 0x0000.000.00000000 0x00000001 0x00000000 1714104309
0x01 9 0x00 0x19d9 0x0008 0x0000.041deb1c 0x0140e83a 0x0000.000.00000000 0x00000001 0x00000000 1714103997
0x02 9 0x00 0x19db 0x0007 0x0000.041e1333 0x0140e83e 0x0000.000.00000000 0x00000001 0x00000000 1714104311
0x03 9 0x00 0x19c4 0x0020 0x0000.041d9475 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x04 9 0x00 0x19d8 0x000b 0x0000.041d93ff 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x05 9 0x00 0x19d4 0x0011 0x0000.041d93e1 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x06 9 0x00 0x19d6 0x001d 0x0000.041de843 0x0140e83a 0x0000.000.00000000 0x00000001 0x00000000 1714103995
0x07 9 0x00 0x19d2 0xffff 0x0000.041e13e4 0x0140e83e 0x0000.000.00000000 0x00000001 0x00000000 1714104311
0x08 9 0x00 0x19d6 0x001c 0x0000.041df3ba 0x0140e83a 0x0000.000.00000000 0x00000001 0x00000000 1714104001
0x09 9 0x00 0x19d2 0x000a 0x0000.041d93f6 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x0a 9 0x00 0x19cf 0x0004 0x0000.041d93f7 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x0b 9 0x00 0x19ce 0x000d 0x0000.041d9403 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x0c 9 0x00 0x19dc 0x0012 0x0000.041d9421 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x0d 9 0x00 0x19db 0x000f 0x0000.041d940f 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x0e 9 0x00 0x19db 0x0015 0x0000.041df58c 0x0140e83f 0x0000.000.00000000 0x00000003 0x00000000 1714104009
0x0f 9 0x00 0x19da 0x001f 0x0000.041d9411 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x10 9 0x00 0x19d9 0x0001 0x0000.041de95d 0x0140e83a 0x0000.000.00000000 0x00000001 0x00000000 1714103996
0x11 9 0x00 0x19db 0x0009 0x0000.041d93eb 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x12 9 0x00 0x19d9 0x0014 0x0000.041d9429 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x13 9 0x00 0x19d4 0x0018 0x0000.041d93b0 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x14 9 0x00 0x19cf 0x001e 0x0000.041d943d 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x15 9 0x00 0x19cc 0x0016 0x0000.041df593 0x0140e83b 0x0000.000.00000000 0x00000002 0x00000000 1714104009
0x16 9 0x00 0x19cf 0x0000 0x0000.041df5a9 0x0140e83e 0x0000.000.00000000 0x00000001 0x00000000 1714104009
0x17 9 0x00 0x19ce 0x0003 0x0000.041d9461 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x18 9 0x00 0x19d0 0x0005 0x0000.041d93b6 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x19 9 0x00 0x19cf 0x0013 0x0000.041d939f 0x0140e838 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x1a 9 0x00 0x19d2 0x0006 0x0000.041de834 0x0140e83a 0x0000.000.00000000 0x00000001 0x00000000 1714103995
0x1b 9 0x00 0x19d2 0x000e 0x0000.041df58a 0x0140e83e 0x0000.000.00000000 0x00000001 0x00000000 1714104009
0x1c 9 0x00 0x19d1 0x0021 0x0000.041df3d8 0x0140e83a 0x0000.000.00000000 0x00000001 0x00000000 1714104002
0x1d 9 0x00 0x19cb 0x0010 0x0000.041de845 0x0140e83a 0x0000.000.00000000 0x00000001 0x00000000 1714103995
0x1e 9 0x00 0x19d0 0x0017 0x0000.041d9449 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x1f 9 0x00 0x19cf 0x000c 0x0000.041d941b 0x0140e839 0x0000.000.00000000 0x00000001 0x00000000 1714103733
0x20 9 0x00 0x19cb 0x001a 0x0000.041d947a 0x0140e83a 0x0000.000.00000000 0x00000002 0x00000000 1714103733
0x21 9 0x00 0x19cd 0x001b 0x0000.041df52a 0x0140e83a 0x0000.000.00000000 0x00000001 0x00000000 1714104009
EXT TRN CTL::
Note:
可见即使在1个session做大量事务，也会均匀的使用多个undo segment, 对于rollback segment的使用可以查看DIS 402 Space and Transaction Management. 重启也不会导致undo segment wrap重置。
Solution:
可修复的方法，如创建新UNDO，切换新UNDO；如果仅个别UNDO segment wrap耗尽，可考虑删除该UNDO 段或使用隐藏参数标记undo段不可用。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。