I’ve just updated the “Thinking Clearly” paper to include an absolutely vital section that was, regrettably, missing from the first revision. It’s a section on the subject of skew.

I hope you enjoy.

车东@FlickR posted a photo:

data.cnzz.com/main.php?s=brow_detail&brow=360SE&s...

回顾过去，展望未来，09年年会于1月29日在广州鹿鸣酒家举行。为答谢oracle和ibm过去一年对我们公司的帮助，及来年对我们的继续支持，我们诚意邀请了他们参加我们的年会。 随着公司人员在...

作者：eygle 发布在 eygle.com

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

相关文章|Related Articles

• Oracle Database 11g回滚段命名的变化
  
• 修改默认的undo_retention参数设置
  
• ORA-600 kcbzpbuf_1 坏块的恢复案例一则
  
• 使用ora_rowscn识别误操作数据时间点
  
• 断电故障导致 ASM DiskGroup 故障及恢复案例
  

评论数量(0)|Add Comments

本文网址：http://www.eygle.com/archives/2010/02/smon_recover_undo_segment.html

通知:

From Adam,

I'm currently working with VIHA as part of the BI Data Warehousing team.  I've also recently completed a project looking at the effective use of quantitative information in healthcare administration. As information needs, and the technologies available to support them, are continually changing, its an interesting field to be in.

I will talk a little bit about tools  (Cognos, SQL Server & SSIS)  we use and methodologies (relational & star schemas).  I'd also like to share with you about some of the challenges we've faced in designing and building the data warehouse, as well as around what we're attempting around metadata and data quality feedback loops.

If we have time, it would also be good to look at the different types of analysis and reporting one needs support with data, - and on their implications for data warehouse design and development work.

Have a good Friday, - and I'll look forward to seeing you on Saturday.

Adam  


时间: Feb 20 (Saturday) 2:30pm
地址 Address: 2121 Cedar Hill Cross Rd.


欢迎各位IT同仁.


Victoria IT Club hold free IT Seminars on 3rd Saturday 2:30pm every month at EBC church second floor meeting room.