SMON: recover undo segment 与 事务恢复(by eygle)

在一些系统异常之后，尤其是异常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-

上文表达的目的就是希望在abort数据库重启后尽量先诊断，别先让SMON来恢复那些死事务或者没有提交但已经写入到数据文件的事务的操作，因为有可能SMON也会Crash这样就会破坏回滚段导致数据无法恢复了

 

PS:dead transaction的定义：一般希望定期清理死事务

an uncommitted transaction and all the file and directory changes associated with it. This could happen for several reasons: perhaps the client operation was inelegantly terminated by the user, or a network failure occurred in the middle of an operation. Regardless of the reason, dead transactions can happen. They don't do any real harm, other than consuming disk space. A fastidious administrator may nonetheless wish to remove them.

 

下面这段信息是用来查询数据库中死事务的方法：

The transaction can be considered DEAD for number of reasons. You can see the
status of the transaction at anypoint of time using by querying the X$KTUXE.

KTUXESTA will give you the transaction status for any given transaction and KTUXEFL will give the transaction flag (DEAD if it is DEAD transaction) and the
KTUXESTA will show the STATUS of the transaction (INACTIVE/ACTIVE/COMMITED)

To find the transactions which are DEAD in your database you can use this sql.

SELECT *from X$KTUXE where KTUXECFL='DEAD';

Transaction STATUS is exposed via V$TRANSACTION and you can query the V$transaction
for a known transaction id.

There are also few events , like 10013/10015 to trace the instance recovery/rollback
segment recovery. I would suggest you set this events to identify the DEAD transactions.