联系：手机/微信(+86 17813235971) QQ(107644445)
标题：ORA-01092 ORA-00604 ORA-01558故障处理
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
客户一个11.2.0.1的库,在重启之前报ORA-00604和ORA-01558: out of transaction ID’s in rollback segment SYSTEM错误
Sat Mar 16 05:51:44 2024 Errors in file /opt/oracle/app/diag/rdbms/xff/xff/trace/xff_smon_47709.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-01558: out of transaction ID's in rollback segment SYSTEM Sat Mar 16 05:51:55 2024 Errors in file /opt/oracle/app/diag/rdbms/xff/xff/trace/xff_smon_47709.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-01558: out of transaction ID's in rollback segment SYSTEM
数据库关闭之后无法open,报ORA-01092 ORA-00604 ORA-01558错误
SQL> alter database open; alter database open * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00604: error occurred at recursive SQL level 1 ORA-01558: out of transaction ID's in rollback segment SYSTEM Process ID: 44308 Session ID: 769 Serial number: 3
对应的alert日志信息
Fri Aug 30 20:05:40 2024 alter database open Beginning crash recovery of 1 threads Fri Aug 30 20:06:08 2024 parallel recovery started with 32 processes Started redo scan Completed redo scan read 0 KB redo, 0 data blocks need recovery Started redo application at Thread 1: logseq 7583120, block 130906, scn 832944617073 Recovery of Online Redo Log: Thread 1 Group 6 Seq 7583120 Reading mem 0 Mem# 0: /OracleData/db_mc/xff/redo06.log Completed redo application of 0.00MB Completed crash recovery at Thread 1: logseq 7583120, block 130906, scn 832944637074 0 data blocks read, 0 data blocks written, 0 redo k-bytes read Fri Aug 30 20:06:09 2024 Thread 1 advanced to log sequence 7583121 (thread open) Thread 1 opened at log sequence 7583121 Current log# 4 seq# 7583121 mem# 0: /OracleData/db_mc/xff/redo04.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Fri Aug 30 20:06:09 2024 SMON: enabling cache recovery Errors in file /opt/oracle/app/diag/rdbms/xff/xff/trace/xff_ora_6066.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-01558: out of transaction ID's in rollback segment SYSTEM Errors in file /opt/oracle/app/diag/rdbms/xff/xff/trace/xff_ora_6066.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-01558: out of transaction ID's in rollback segment SYSTEM Error 604 happened during db open, shutting down database USER (ospid: 6066): terminating the instance due to error 604 Instance terminated by USER, pid = 6066 ORA-1092 signalled during: alter database open... opiodr aborting process unknown ospid (6066) as a result of ORA-1092
通过对异常block进行dump
index state cflags wrap# uel scn dba parent-xid nub stmt_num ------------------------------------------------------------------------------------------------ 0x00 9 0x00 0xfffffffe 0x0059 0x00c1.ee6ab58f 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x01 9 0x00 0xfffffffe 0x0008 0x00c1.ee6ab5a7 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x02 9 0x00 0xfffffffe 0x005b 0x00c1.ee6ab59f 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x03 9 0x00 0xfffffffe 0x0007 0x00c1.ee6ab5b5 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x04 9 0x00 0xfffffffe 0x005f 0x00c1.ee6ab593 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x05 9 0x00 0xfffffffe 0x000a 0x00c1.ee6ab5ad 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x06 9 0x00 0xfffffffe 0x0019 0x00c1.ee6ab5dd 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x07 9 0x00 0xfffffffe 0x0013 0x00c1.ee6ab5b7 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x08 9 0x00 0xfffffffe 0x0035 0x00c1.ee6ab5a9 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x09 9 0x00 0xfffffffe 0x0002 0x00c1.ee6ab59d 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x0a 9 0x00 0xfffffffe 0x003c 0x00c1.ee6ab5af 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x0b 9 0x00 0xfffffffe 0x0011 0x00c1.ee6ab5d7 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x0c 9 0x00 0xfffffffe 0x0026 0x00c1.ee6ab605 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x0d 9 0x00 0xfffffffe 0x000f 0x00c1.ee6ab5c5 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x0e 9 0x00 0xfffffffe 0x0014 0x00c1.ee6ab5d3 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x0f 9 0x00 0xfffffffe 0x0012 0x00c1.ee6ab5c7 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x10 9 0x00 0xfffffffe 0x0029 0x00c1.ee6ab5c1 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x11 9 0x00 0xfffffffe 0x0018 0x00c1.ee6ab5d9 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x12 9 0x00 0xfffffffe 0x001e 0x00c1.ee6ab5c9 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x13 9 0x00 0xfffffffe 0x001b 0x00c1.ee6ab5b9 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x14 9 0x00 0xfffffffe 0x000b 0x00c1.ee6ab5d5 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x15 9 0x00 0xfffffffe 0x0003 0x00c1.ee6ab5b3 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x16 9 0x00 0xfffffffe 0x0023 0x00c1.ee6ab5f1 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x17 9 0x00 0xfffffffe 0x0021 0x00c1.ee6ab5bd 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x18 9 0x00 0xfffffffe 0x0006 0x00c1.ee6ab5db 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x19 9 0x00 0xfffffffe 0x0049 0x00c1.ee6ab5df 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x1a 9 0x00 0xfffffffe 0x0043 0x00c1.ee6ab617 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x1b 9 0x00 0xfffffffe 0x0017 0x00c1.ee6ab5bb 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x1c 9 0x00 0xfffffffe 0x002e 0x00c1.ee6ab5e9 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x1d 9 0x00 0xfffffffe 0x001c 0x00c1.ee6ab5e7 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x1e 9 0x00 0xfffffffe 0x002a 0x00c1.ee6ab5cb 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x1f 9 0x00 0xffffff28 0x003e 0x00c1.ee6ab62d 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x20 9 0x00 0xfffffffe 0x0022 0x00c1.ee6ab5cf 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x21 9 0x00 0xfffffffe 0x0010 0x00c1.ee6ab5bf 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x22 9 0x00 0xfffffffe 0x000e 0x00c1.ee6ab5d1 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x23 9 0x00 0xfffffffe 0x0025 0x00c1.ee6ab5f3 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x24 9 0x00 0xfffffffe 0x0032 0x00c1.ee6ab5f7 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x25 9 0x00 0xfffffffd 0x0024 0x00c1.ee6ab5f5 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x26 9 0x00 0xfffffffe 0x003d 0x00c1.ee6ab607 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x27 9 0x00 0xfffffffe 0x0041 0x00c1.ee6ab63b 0x03000439 0x0000.000.00000000 0x00000001 0x00000000 0x28 9 0x00 0xfffffffe 0x003f 0x00c1.ee6ab613 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x29 9 0x00 0xfffffffb 0x000d 0x00c1.ee6ab5c3 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x2a 9 0x00 0xfffffffe 0x0020 0x00c1.ee6ab5cd 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x2b 9 0x00 0xfffffffe 0x0037 0x00c1.ee6ab60d 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x2c 9 0x00 0xfffffffe 0x002d 0x00c1.ee6ab5ed 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x2d 9 0x00 0xfffffffe 0x0016 0x00c1.ee6ab5ef 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x2e 9 0x00 0xfffffffe 0x002c 0x00c1.ee6ab5eb 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x2f 9 0x00 0xfffffffe 0x0034 0x00c1.ee6ab5ff 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x30 9 0x00 0xfffffffe 0x001f 0x00c1.ee6ab62b 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x31 9 0x00 0xfffffffe 0x000c 0x00c1.ee6ab603 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x32 9 0x00 0xfffffffe 0x003b 0x00c1.ee6ab5f9 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x33 9 0x00 0xfffffffa 0x0038 0x00c1.ee6ab61b 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x34 9 0x00 0xfffffffe 0x0031 0x00c1.ee6ab601 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x35 9 0x00 0xfffffffd 0x0005 0x00c1.ee6ab5ab 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x36 9 0x00 0xfffffffe 0x0028 0x00c1.ee6ab611 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x37 9 0x00 0xfffffffe 0x0036 0x00c1.ee6ab60f 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x38 9 0x00 0xfffffffe 0x0048 0x00c1.ee6ab61d 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x39 9 0x00 0xfffffffe 0x0052 0x00c1.ee6ab64d 0x03000439 0x0000.000.00000000 0x00000001 0x00000000 0x3a 9 0x00 0xfffffffe 0x002f 0x00c1.ee6ab5fd 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x3b 9 0x00 0xfffffffe 0x003a 0x00c1.ee6ab5fb 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x3c 9 0x00 0xfffffffd 0x0015 0x00c1.ee6ab5b1 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x3d 9 0x00 0xfffffffe 0x0061 0x00c1.ee6ab609 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x3e 9 0x00 0xfffffffe 0x004d 0x00c1.ee6ab62f 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x3f 9 0x00 0xfffffffe 0x001a 0x00c1.ee6ab615 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x40 9 0x00 0xfffffffd 0x001d 0x00c1.ee6ab5e5 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x41 9 0x00 0xfffffffe 0x0058 0x00c1.ee6ab63d 0x03000439 0x0000.000.00000000 0x00000001 0x00000000 0x42 9 0x00 0xfffffffe 0x0027 0x00c1.ee6ab639 0x03000439 0x0000.000.00000000 0x00000001 0x00000000 0x43 9 0x00 0xfffffffe 0x0033 0x00c1.ee6ab619 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x44 9 0x00 0xfffffffe 0x0050 0x00c1.ee6ab635 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x45 9 0x00 0xfffffffe 0x0044 0x00c1.ee6ab633 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x46 9 0x00 0xfffffffe 0x0057 0x00c1.ee6ab625 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x47 9 0x00 0xfffffffe 0x0030 0x00c1.ee6ab629 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x48 9 0x00 0xfffffffe 0x004c 0x00c1.ee6ab61f 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x49 9 0x00 0xfffffffd 0x004e 0x00c1.ee6ab5e1 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x4a 9 0x00 0xfffffffe 0x0054 0x00c1.ee6ab641 0x03000439 0x0000.000.00000000 0x00000001 0x00000000 0x4b 9 0x00 0xfffffffe 0x0046 0x00c1.ee6ab623 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x4c 9 0x00 0xfffffffe 0x004b 0x00c1.ee6ab621 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x4d 9 0x00 0xfffffffe 0x0045 0x00c1.ee6ab631 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x4e 9 0x00 0xfffffffd 0x0040 0x00c1.ee6ab5e3 0x03000437 0x0000.000.00000000 0x00000001 0x00000000 0x4f 9 0x00 0xfffffffd 0x005a 0x00c1.ee6ab599 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x50 9 0x00 0xfffffffe 0x0042 0x00c1.ee6ab637 0x03000439 0x0000.000.00000000 0x00000001 0x00000000 0x51 9 0x00 0xfffffffe 0x0053 0x00c1.ee6ab645 0x03000439 0x0000.000.00000000 0x00000001 0x00000000 0x52 9 0x00 0xfffffffe 0x0060 0x00c1.ee6ab64f 0x03000439 0x0000.000.00000000 0x00000001 0x00000000 0x53 9 0x00 0xfffffffe 0x0056 0x00c1.ee6ab647 0x03000439 0x0000.000.00000000 0x00000001 0x00000000 0x54 9 0x00 0xfffffffe 0x0051 0x00c1.ee6ab643 0x03000439 0x0000.000.00000000 0x00000001 0x00000000 0x55 9 0x00 0xfffffffe 0x0039 0x00c1.ee6ab64b 0x03000439 0x0000.000.00000000 0x00000001 0x00000000 0x56 9 0x00 0xfffffffe 0x0055 0x00c1.ee6ab649 0x03000439 0x0000.000.00000000 0x00000001 0x00000000 0x57 9 0x00 0xfffffffe 0x0047 0x00c1.ee6ab627 0x03000438 0x0000.000.00000000 0x00000001 0x00000000 0x58 9 0x00 0xfffffffe 0x004a 0x00c1.ee6ab63f 0x03000439 0x0000.000.00000000 0x00000001 0x00000000 0x59 9 0x00 0xfffffffd 0x0004 0x00c1.ee6ab591 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x5a 9 0x00 0xfffffffd 0x0009 0x00c1.ee6ab59b 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x5b 9 0x00 0xfffffffd 0x005e 0x00c1.ee6ab5a1 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x5c 9 0x00 0xfffffffd 0x0001 0x00c1.ee6ab5a5 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x5d 9 0x00 0xfffffffd 0x004f 0x00c1.ee6ab597 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x5e 9 0x00 0xfffffffd 0x005c 0x00c1.ee6ab5a3 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x5f 9 0x00 0xfffffffd 0x005d 0x00c1.ee6ab595 0x03000436 0x0000.000.00000000 0x00000001 0x00000000 0x60 9 0x00 0xfffffffe 0xffff 0x00c1.ee6ab651 0x03000439 0x0000.000.00000000 0x00000001 0x00000000 0x61 9 0x00 0xfffffdb4 0x002b 0x00c1.ee6ab60b 0x03000438 0x0000.000.00000000 0x00000001 0x00000000
确实是Wrap#达到了极限值,通过bbed也可以进一步确认
BBED> p ktuxe[1]
struct ktuxe[1], 40 bytes @4292
ub4 ktuxexid @4292 0xfffffffe
ub4 ktuxebrb @4296 0x03000436
struct ktuxescn, 8 bytes @4300
ub4 kscnbas @4300 0xee6ab5a7
ub2 kscnwrp @4304 0x00c1
sb4 ktuxesta @4308 524297 (KTU_SCO, KTU_DEAD)
ub1 ktuxecfl @4309 0x00
sb2 ktuxeuel @4310 8
BBED> p ktuxe[2]
struct ktuxe[2], 40 bytes @4332
ub4 ktuxexid @4332 0xfffffffe
ub4 ktuxebrb @4336 0x03000436
struct ktuxescn, 8 bytes @4340
ub4 kscnbas @4340 0xee6ab59f
ub2 kscnwrp @4344 0x00c1
sb4 ktuxesta @4348 5963785 (KTU_SCO, KTU_DEAD)
ub1 ktuxecfl @4349 0x00
sb2 ktuxeuel @4350 91
处理方法,通过bbed对异常的block进行编辑,修改Wrap#的值,重新dumpblock进行确认
index state cflags wrap# uel scn dba parent-xid nub stmt_num ------------------------------------------------------------------------------------------------ 0x00 9 0x00 0x0015 0x0003 0x0000.11e1a313 0x00400223 0x0000.000.00000000 0x00000001 0x00000000 0x01 9 0x00 0x0015 0x0007 0x0000.11e1a317 0x00400223 0x0000.000.00000000 0x00000001 0x00000000 0x02 9 0x00 0x0015 0x000c 0x0000.11e32087 0x00400224 0x0000.000.00000000 0x00000001 0x00000000 0x03 9 0x00 0x0015 0x005a 0x0000.11e1a314 0x00400223 0x0000.000.00000000 0x00000001 0x00000000 0x04 9 0x00 0x0015 0x0060 0x0000.11e3206e 0x00400223 0x0000.000.00000000 0x00000001 0x00000000 0x05 9 0x00 0x0015 0x005d 0x0000.11e1a311 0x00400223 0x0000.000.00000000 0x00000001 0x00000000 0x06 9 0x00 0x0015 0x0015 0x0000.11e3208b 0x00400224 0x0000.000.00000000 0x00000001 0x00000000 0x07 9 0x00 0x0015 0x0004 0x0000.11e1a318 0x00400223 0x0000.000.00000000 0x00000001 0x00000000 0x08 9 0x00 0x0015 0x0014 0x0000.11e32084 0x00400224 0x0000.000.00000000 0x00000001 0x00000000 0x09 9 0x00 0x0015 0x0001 0x0000.11e1a316 0x00400223 0x0000.000.00000000 0x00000001 0x00000000 0x0a 9 0x00 0x0015 0x0013 0x0000.11e3207d 0x00400224 0x0000.000.00000000 0x00000001 0x00000000 0x0b 9 0x00 0x0015 0x0011 0x0000.11e3207f 0x00400224 0x0000.000.00000000 0x00000001 0x00000000 0x0c 9 0x00 0x0015 0x0059 0x0000.11e32089 0x00400224 0x0000.000.00000000 0x00000001 0x00000000 0x0d 9 0x00 0x0015 0x000a 0x0000.11e3207c 0x00400224 0x0000.000.00000000 0x00000001 0x00000000 0x0e 9 0x00 0x0015 0x0017 0x0000.11e32082 0x00400224 0x0000.000.00000000 0x00000001 0x00000000 0x0f 9 0x00 0x0015 0x002a 0x0000.120964dc 0x00400225 0x0000.000.00000000 0x00000001 0x00000000 0x10 9 0x00 0x0015 0x001a 0x0000.11e3208d 0x00400224 0x0000.000.00000000 0x00000001 0x00000000 0x11 9 0x00 0x0015 0x005f 0x0000.11e32080 0x00400224 0x0000.000.00000000 0x00000001 0x00000000 0x12 9 0x00 0x0015 0x0028 0x0000.120964d9 0x00400225 0x0000.000.00000000 0x00000001 0x00000000 0x13 9 0x00 0x0015 0x000b 0x0000.11e3207e 0x00400224 0x0000.000.00000000 0x00000001 0x00000000 0x14 9 0x00 0x0015 0x0018 0x0000.11e32085 0x00400224 0x0000.000.00000000 0x00000001 0x00000000 0x15 9 0x00 0x0015 0x0010 0x0000.11e3208c 0x00400224 0x0000.000.00000000 0x00000001 0x00000000 0x16 9 0x00 0x0015 0x0032 0x0000.120964e2 0x00400225 0x0000.000.00000000 0x00000001 0x00000000 0x17 9 0x00 0x0015 0x0008 0x0000.11e32083 0x00400224 0x0000.000.00000000 0x00000001 0x00000000 0x18 9 0x00 0x0015 0x0002 0x0000.11e32086 0x00400224 0x0000.000.00000000 0x00000001 0x00000000 0x19 9 0x00 0x0015 0x0020 0x0000.120964d3 0x00400225 0x0000.000.00000000 0x00000001 0x00000000 0x1a 9 0x00 0x0015 0x0025 0x0000.120964c5 0x00400224 0x0000.000.00000000 0x00000001 0x00000000 0x1b 9 0x00 0x0015 0x0021 0x0000.120964d6 0x00400225 0x0000.000.00000000 0x00000001 0x00000000 0x1c 9 0x00 0x0015 0x001b 0x0000.120964d5 0x00400225 0x0000.000.00000000 0x00000001 0x00000000 0x1d 9 0x00 0x0015 0x0019 0x0000.120964d2 0x00400225 0x0000.000.00000000 0x00000001 0x00000000 0x1e 9 0x00 0x0015 0x0030 0x0000.1210ab57 0x00400225 0x0000.000.00000000 0x00000001 0x00000000 0x1f 9 0x00 0x0015 0x000f 0x0000.120964db 0x00400225 0x0000.000.00000000 0x00000001 0x00000000 0x20 9 0x00 0x0015 0x001c 0x0000.120964d4 0x00400225 0x0000.000.00000000 0x00000001 0x00000000 0x21 9 0x00 0x0015 0x0012 0x0000.120964d8 0x00400225 0x0000.000.00000000 0x00000001 0x00000000 0x22 9 0x00 0x0015 0x0029 0x0000.120964de 0x00400225 0x0000.000.00000000 0x00000001 0x00000000 0x23 9 0x00 0x0015 0x0031 0x0000.120964e4 0x00400225 0x0000.000.00000000 0x00000001 0x00000000 0x24 9 0x00 0x0015 0x001d 0x0000.120964d1 0x00400224 0x0000.000.00000000 0x00000001 0x00000000 0x25 9 0x00 0x0014 0x0024 0x0000.120964cf 0x00400224 0x0000.000.00000000 0x00000001 0x00000000 0x26 9 0x00 0x0015 0x003b 0x0000.1210ab5b 0x00400226 0x0000.000.00000000 0x00000001 0x00000000 0x27 9 0x00 0x0014 0x0058 0x0000.0ec9fb55 0x00400222 0x0000.000.00000000 0x00000001 0x00000000 0x28 9 0x00 0x0015 0x001f 0x0000.120964da 0x00400225 0x0000.000.00000000 0x00000001 0x00000000 0x29 9 0x00 0x0015 0x002b 0x0000.120964df 0x00400225 0x0000.000.00000000 0x00000001 0x00000000 0x2a 9 0x00 0x0015 0x0022 0x0000.120964dd 0x00400225 0x0000.000.00000000 0x00000001 0x00000000 0x2b 9 0x00 0x0015 0x002d 0x0000.120964e0 0x00400225 0x0000.000.00000000 0x00000001 0x00000000 0x2c 9 0x00 0x0015 0x003d 0x0000.1210ab60 0x00400226 0x0000.000.00000000 0x00000001 0x00000000 0x2d 9 0x00 0x0015 0x0016 0x0000.120964e1 0x00400225 0x0000.000.00000000 0x00000001 0x00000000 0x2e 9 0x00 0x0014 0x003c 0x0000.0ec9fb51 0x00400222 0x0000.000.00000000 0x00000001 0x00000000 0x2f 9 0x00 0x0015 0x0045 0x0000.1210ab65 0x00400226 0x0000.000.00000000 0x00000001 0x00000000 0x30 9 0x00 0x0015 0x0034 0x0000.1210ab58 0x00400225 0x0000.000.00000000 0x00000001 0x00000000 0x31 9 0x00 0x0015 0x0036 0x0000.1210ab4b 0x00400225 0x0000.000.00000000 0x00000001 0x00000000 0x32 9 0x00 0x0015 0x0023 0x0000.120964e3 0x00400225 0x0000.000.00000000 0x00000001 0x00000000 0x33 9 0x00 0x0015 0x0041 0x0000.1210ab67 0x00400226 0x0000.000.00000000 0x00000001 0x00000000 0x34 9 0x00 0x0015 0x003a 0x0000.1210ab59 0x00400226 0x0000.000.00000000 0x00000001 0x00000000 0x35 9 0x00 0x0015 0x002c 0x0000.1210ab5e 0x00400226 0x0000.000.00000000 0x00000001 0x00000000 0x36 9 0x00 0x0015 0x001e 0x0000.1210ab55 0x00400225 0x0000.000.00000000 0x00000001 0x00000000 0x37 9 0x00 0x0014 0x0050 0x0000.0ec9fb53 0x00400222 0x0000.000.00000000 0x00000001 0x00000000 0x38 9 0x00 0x0015 0x003f 0x0000.1210ab62 0x00400226 0x0000.000.00000000 0x00000001 0x00000000 0x39 9 0x00 0x0015 0x0035 0x0000.1210ab5d 0x00400226 0x0000.000.00000000 0x00000001 0x00000000 0x3a 9 0x00 0x0015 0x0026 0x0000.1210ab5a 0x00400226 0x0000.000.00000000 0x00000001 0x00000000 0x3b 9 0x00 0x0015 0x0039 0x0000.1210ab5c 0x00400226 0x0000.000.00000000 0x00000001 0x00000000 0x3c 9 0x00 0x0014 0x0037 0x0000.0ec9fb52 0x00400222 0x0000.000.00000000 0x00000001 0x00000000 0x3d 9 0x00 0x0015 0x0038 0x0000.1210ab61 0x00400226 0x0000.000.00000000 0x00000001 0x00000000 0x3e 9 0x00 0x0014 0x0040 0x0000.0ec9fb49 0x00400222 0x0000.000.00000000 0x00000001 0x00000000 0x3f 9 0x00 0x0015 0x0044 0x0000.1210ab63 0x00400226 0x0000.000.00000000 0x00000001 0x00000000 0x40 9 0x00 0x0014 0x004b 0x0000.0ec9fb4a 0x00400222 0x0000.000.00000000 0x00000001 0x00000000 0x41 9 0x00 0x0015 0x0043 0x0000.1210ab68 0x00400226 0x0000.000.00000000 0x00000001 0x00000000 0x42 9 0x00 0x0015 0xffff 0x0000.1210ab6b 0x00400226 0x0000.000.00000000 0x00000001 0x00000000 0x43 9 0x00 0x0015 0x0042 0x0000.1210ab69 0x00400226 0x0000.000.00000000 0x00000001 0x00000000 0x44 9 0x00 0x0015 0x002f 0x0000.1210ab64 0x00400226 0x0000.000.00000000 0x00000001 0x00000000 0x45 9 0x00 0x0015 0x0033 0x0000.1210ab66 0x00400226 0x0000.000.00000000 0x00000001 0x00000000 0x46 9 0x00 0x0014 0x004e 0x0000.0ec9fb4e 0x00400222 0x0000.000.00000000 0x00000001 0x00000000 0x47 9 0x00 0x0014 0x004f 0x0000.0ec9fb4c 0x00400222 0x0000.000.00000000 0x00000001 0x00000000 0x48 9 0x00 0x0014 0x0054 0x0000.11e1a306 0x00400223 0x0000.000.00000000 0x00000001 0x00000000 0x49 9 0x00 0x0014 0x0055 0x0000.0ec9fd5b 0x00400223 0x0000.000.00000000 0x00000002 0x00000000 0x4a 9 0x00 0x0014 0x003e 0x0000.0ec9fb48 0x00400222 0x0000.000.00000000 0x00000001 0x00000000 0x4b 9 0x00 0x0014 0x0047 0x0000.0ec9fb4b 0x00400222 0x0000.000.00000000 0x00000001 0x00000000 0x4c 9 0x00 0x0014 0x000d 0x0000.11e3207b 0x00400224 0x0000.000.00000000 0x00000001 0x00000000 0x4d 9 0x00 0x0014 0x0061 0x0000.11e1a30f 0x00400223 0x0000.000.00000000 0x00000001 0x00000000 0x4e 9 0x00 0x0014 0x002e 0x0000.0ec9fb4f 0x00400222 0x0000.000.00000000 0x00000001 0x00000000 0x4f 9 0x00 0x0014 0x0046 0x0000.0ec9fb4d 0x00400222 0x0000.000.00000000 0x00000001 0x00000000 0x50 9 0x00 0x0014 0x0027 0x0000.0ec9fb54 0x00400222 0x0000.000.00000000 0x00000001 0x00000000 0x51 9 0x00 0x0014 0x0053 0x0000.11e1a30a 0x00400223 0x0000.000.00000000 0x00000001 0x00000000 0x52 9 0x00 0x0014 0x005b 0x0000.11e1a30c 0x00400223 0x0000.000.00000000 0x00000001 0x00000000 0x53 9 0x00 0x0014 0x0052 0x0000.11e1a30b 0x00400223 0x0000.000.00000000 0x00000001 0x00000000 0x54 9 0x00 0x0014 0x0057 0x0000.11e1a307 0x00400223 0x0000.000.00000000 0x00000001 0x00000000 0x55 9 0x00 0x0014 0x0048 0x0000.11e1a304 0x00400223 0x0000.000.00000000 0x00000001 0x00000000 0x56 9 0x00 0x0014 0x0051 0x0000.11e1a309 0x00400223 0x0000.000.00000000 0x00000001 0x00000000 0x57 9 0x00 0x0014 0x0056 0x0000.11e1a308 0x00400223 0x0000.000.00000000 0x00000001 0x00000000 0x58 9 0x00 0x0014 0x0049 0x0000.0ec9fb56 0x00400222 0x0000.000.00000000 0x00000001 0x00000000 0x59 9 0x00 0x0014 0x0006 0x0000.11e3208a 0x00400224 0x0000.000.00000000 0x00000001 0x00000000 0x5a 9 0x00 0x0014 0x0009 0x0000.11e1a315 0x00400223 0x0000.000.00000000 0x00000001 0x00000000 0x5b 9 0x00 0x0014 0x005e 0x0000.11e1a30d 0x00400223 0x0000.000.00000000 0x00000001 0x00000000 0x5c 9 0x00 0x0014 0x004c 0x0000.11e3207a 0x00400224 0x0000.000.00000000 0x00000001 0x00000000 0x5d 9 0x00 0x0014 0x0000 0x0000.11e1a312 0x00400223 0x0000.000.00000000 0x00000001 0x00000000 0x5e 9 0x00 0x0014 0x004d 0x0000.11e1a30e 0x00400223 0x0000.000.00000000 0x00000001 0x00000000 0x5f 9 0x00 0x0014 0x000e 0x0000.11e32081 0x00400224 0x0000.000.00000000 0x00000001 0x00000000 0x60 9 0x00 0x0014 0x005c 0x0000.11e32078 0x00400223 0x0000.000.00000000 0x00000001 0x00000000 0x61 9 0x00 0x0014 0x0005 0x0000.11e1a310 0x00400223 0x0000.000.00000000 0x00000001 0x00000000
直接启动数据库成功
[oracle@www.xifenfei.com ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 21 13:45:23 2024 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount pfile='/tmp/pfile'; ORACLE instance started. Total System Global Area 8551575552 bytes Fixed Size 2215064 bytes Variable Size 2483028840 bytes Database Buffers 6039797760 bytes Redo Buffers 26533888 bytes Database mounted. SQL> recover database; Media recovery complete. SQL> alter database open; Database altered.
由于客户遇到故障之后,第一时间保护了现场,没有进行二次破坏,使用bbed进行修改block,实现完美恢复.
以前有过一次在11.2.0.4的环境中恢复的同样案例:ORA-01558: out of transaction ID’s in rollback segment SYSTEM
通过查询mos,确认类似Bug 19700135 – ORA-600 [4187] when the undo segment wrap# is close to the max value of 0xffffffff (Doc ID 19700135.8)
Description
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.
With this fix in place an error ORA-1558 is eventually produced for the affected undo segment
which still requires dropping the undo segment:
ORA-1558 "out of transaction ID's in rollback segment %s"
Cause: All the available transaction id's have been used
Action: Shutdown the instance and restart using other rollback segment(s),
then drop the rollback segment that has no more transaction id's.
Workaround
Once the ORA-600 [4187] is produced, drop the affected undo segment or undo tablespace.
由于本次故障是在system的rollback回滚段上,无法删除该回滚段.所以处理起来比较棘手.
