« 祝大家虎年大吉 新年快乐 | Blog首页 | ORA-00600 3020 错误案例一则 »

SMON: recover undo segment与事务恢复

在一些系统异常之后，尤其是异常Down机之后，数据库里可能会看到类似如下的提示信息：

SMON: about to recover undo segment 23
SMON: mark undo segment 23 as available

这些信息意味着，数据库需要这些回滚段的信息去恢复一些未完成事务，多数情况下，这些事务都能够得到恢复，但是如果不幸，在异常宕机时丢失了部分Write操作，则可能无法完成恢复，出现灾难。

以下一段日志来自生产数据库的日志恢复：

SMON: about to recover undo segment 23
SMON: mark undo segment 23 as available
SMON: about to recover undo segment 24
SMON: mark undo segment 24 as available
SMON: about to recover undo segment 25
Dead transaction 0x0019.01b.00009fc7 recovered by SMON
SMON: mark undo segment 25 as available
SMON: about to recover undo segment 26
SMON: mark undo segment 26 as available
SMON: about to recover undo segment 27
SMON: mark undo segment 27 as available

在这些日志中可以看到，Oracle在恢复一个死事务，0x0019.01b.00009fc7，这个事务使用的回滚段是0x19号，也就是25号回滚段，可以看到在随后的日志中，Oracle启用25号回滚段。

这就是由事务恢复引发的回滚段操作，以下日志可以作为类似的参考：

Dead transaction 0x0044.010.00007746 recovered by SMON
SMON: mark undo segment 68 as available
SMON: about to recover undo segment 69
SMON: mark undo segment 69 as available
SMON: about to recover undo segment 70
SMON: mark undo segment 70 as available
SMON: about to recover undo segment 71
SMON: mark undo segment 71 as available
SMON: about to recover undo segment 72
SMON: mark undo segment 72 as available
SMON: about to recover undo segment 75
SMON: mark undo segment 75 as available
SMON: about to recover undo segment 76
SMON: mark undo segment 76 as available
SMON: about to recover undo segment 77
SMON: mark undo segment 77 as available
SMON: about to recover undo segment 78
SMON: mark undo segment 78 as available
SMON: about to recover undo segment 79
Dead transaction 0x004f.00e.00005d5e recovered by SMON
SMON: mark undo segment 79 as available
SMON: about to recover undo segment 81
SMON: about to recover undo segment 120
SMON: mark undo segment 120 as available
SMON: about to recover undo segment 122
Dead transaction 0x009a.006.00003056 recovered by SMON
SMON: mark undo segment 154 as available
SMON: about to recover undo segment 156
SMON: mark undo segment 156 as available

某些恢复可能需要很长的时间，在此期间的一些异常可能导致SMON Crash，进而数据库Instance崩溃。
Oracle的一些内部事件允许设置debug_mode，以便允许在AUM模式下，手工进行回滚段的处理：

alert session set "_smu_debug_mode"=4;
alert rollback segment "_SYSSMU10$" ONLINE;

了解这些手段，但是需要谨慎使用，特别是当你决定DROP某个回滚段时。

设置10513事件，可以阻止SMON在启动数据库后执行恢复，这可以给我们一些诊断时间，在参数文件中如下设置：
event   = 10513 trace name context forever,level 2

总之，和UNDO相关的操作极度危险，在一些金融领域，任何一个丢失的事务都可能成为灾难，所以了解任何一个动作及其可能带来的影响是对我们的重大考验。

判断永远比操作更重要！

-The End-

By eygle on 2010-02-23 07:54 | Comments (1) | Backup&Recovery | Internal | 2507 |

RecoveryUNDO