拨云见日：揭开ORA-00600:[4193]的神秘面纱

墨墨导读：从 Oracle 9i 开始，Oracle引入了一种管理前镜像的新方式。之前的版本是通过 RollBack Segment 进行的，或称为 manual undo(手动 undo)。

Oracle引入回滚段的目的： 1、事务回滚 2、数据库恢复 3、提供读一致性 4、数据库闪回查询(9i引入) 5、利用闪回特性可以恢复。

一、问题表象：

最近客户遇到一个奇怪的问题，oracle数据库大量undo段迟迟不expired, 最终导致undo不够用，引发一系列undo相关报错，关于隐患参数_undo_autotune是false。v$undostat 始终只有一行，数据一直累加，而正常环境应该是5分钟有一条新纪录。多次尝试重建undo没作用，Undo unexpire还是持续上涨， 修改undo_retention为10800（3小时), _highthreshold_undoretention也改为10800，仍然不起作用。

smon在启动后报了报错：

*** 2020-08-11 17:35:57.551SMON: following errors trapped and ignored:ORA-01595: error freeing extent (13934) of rollback segment (168))ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []
*** 2020-08-11 17:35:17.268Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x505E2AE3] [PC:0x981A396, kgegpa()+40] [flags: 0x0, count: 1]DDE previous invocation failed before phase II

当出现断电或硬件故障数据库崩溃或者bug等，通常会发生此问题。启动时，数据库先进行正常的前滚（重做），然后再进行回滚（撤消），这是在回滚时生成错误的地方。

根据官方文档：ORA-600 [4193] “seq# mismatch while adding undo record” (Doc ID 39282.1)的描述，在重做记录和回滚记录之间检测到不匹配。

  Format: ORA-600 [4193] [a] [b]VERSIONS:  versions 6.0 to 12.1
DESCRIPTION:
  A mismatch has been detected between Redo records and Rollback (Undo)  records.
  We are validating the Undo block sequence number in the undo block against  the Redo block sequence number relating to the change being applied.
  This error is reported when this validation fails.
ARGUMENTS:  Arg [a] Undo record seq number  Arg [b] Redo record seq number

常规情况，可以通过重建undo来解决，可以参考文档：Step by step to resolve ORA-600 4194 4193 4197 on database crash (Doc ID 1428786.1)

Best practice to create a new undo tablespace.This method includes segment check.
1. Create pfile from spfile to editSQL> Create pfile='/tmp/initsid.ora' from spfile;
2. Shutdown the instance
3. set the following parameters in the pfile /tmp/initsid.ora    undo_management = manual    event = '10513 trace name context forever, level 2'
4. SQL>>startup restrict pfile='/tmp/initsid.ora'
5. SQL>select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';
If all offline then continue to the next step
6. Create new undo tablespace - exampleSQL>create undo tablespace