157 积分	0 好友	14 主题

发消息

smon tx recovery求教

1^#

发表于 2012-6-13 16:53:36 | 查看: 6644| 回复: 5

在执行shutdown immediate时，oracle会对未提交事务进行回滚，在回滚的过程中又在另一个会话执行了shutdown abort.
那么下次启动时的smon tx recovery是重新回滚死事务呢还是断点续回？

分享0

收藏0 回复只看该作者道具举报

Maclean Liu(刘相兵

6^#

发表于 2012-6-13 20:37:01

举一个例子来说明该问题：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0    Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> select count(*) from maclean_large;

  COUNT(*)
----------
  39058944

SQL>  delete maclean_large;

执行以上DELETE语句耗费大量时间在中途KILL 该前台进程

[oracle@vrh8 bdump]$ kill -9 4185

PMON 发现dead process 后会post smon让他来 recover dead transaction

SQL> select sum(distinct(ktuxesiz)) from x$ktuxe where ktuxecfl = 'DEAD';

SUM(DISTINCT(KTUXESIZ))
-----------------------
               151405

SQL> select sum(distinct(ktuxesiz)) from x$ktuxe where ktuxecfl = 'DEAD';

SUM(DISTINCT(KTUXESIZ))
-----------------------
               149543

x$KTUXE 视图的ktuxesiz=》number of undo blocks used by the transaction ， ktuxesiz减少说明正在回滚rollback

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

SQL> alter database open;

SQL> select sum(distinct(ktuxesiz)) from x$ktuxe where ktuxecfl = 'DEAD';

SUM(DISTINCT(KTUXESIZ))
-----------------------
               144585

SQL> select sum(distinct(ktuxesiz)) from x$ktuxe where ktuxecfl = 'DEAD';

SUM(DISTINCT(KTUXESIZ))
-----------------------
               143118

SQL> select sum(distinct(ktuxesiz)) from x$ktuxe where ktuxecfl = 'DEAD';

SUM(DISTINCT(KTUXESIZ))
-----------------------
               141861

可以看到 KTUXESIZ 是延续之前的rollback成果的  ，所以SMON 不是在做trans recovery时被打断就需要从头开始，  仔细想一下这样也是不科学的，所以 SMON的trans recovery是可continuable的

回复只看该作者道具举报