Oracle 异常关闭undo segment header研究

Oracle异常关闭，再次open时，如果undo表空间出现问题，数据库往往不能正常启动，本文初步探讨关于undo的一些机制。
创建一事务，查看该事务的XID,XIDUSN,XIDSLOT,XIDSQN。

[quote]SQL> insert into t select * from t;

51686 rows created.

SQL> select * from v$mystat where rownum=1;

SID STATISTIC# VALUE
---------- ---------- ----------
142 0 1

SQL> select XID,XIDUSN,XIDSLOT,XIDSQN,v$transaction.STATUS
2 from v$transaction,v$session
3 where v$transaction.SES_ADDR=v$session.SADDR
4 and v$session.sid=142;

XID XIDUSN XIDSLOT XIDSQN STATUS
---------------- ---------- ---------- ---------- ----------------
03001B0079A60000 3 27 42617 ACTIVE


SQL> select name from v$rollname where usn=3;

NAME
------------------------------
_SYSSMU3$[/quote]

dump该回滚段
[quote]SQL> ALTER SYSTEM DUMP UNDO HEADER "_SYSSMU3$";

System altered.[/quote]
查看跟踪文件，可以看到一事务，没有提交，注意其flag标记，进一步可以看到XIDUSN,XIDSLOT,XIDSQN在跟踪文件中均有标记。

[quote] 0x11 9 0x00 0xa67a 0x0026 0x08dd.03b78ea1 0x00800289 0x0000.000.00000000 0x00000001 0x00000000 1263367005
0x12 9 0x00 0xa679 0x001f 0x08dd.03b78ebe 0x00800289 0x0000.000.00000000 0x00000001 0x00000000 1263367005
0x13 9 0x00 0xa67b 0xffff 0x08dd.03b93ee2 0x0080028d 0x0000.000.00000000 0x00000001 0x00000000 1263426136
0x14 9 0x00 0xa679 0x0022 0x08dd.03b78f02 0x0080028b 0x0000.000.00000000 0x00000001 0x00000000 1263367005
0x15 9 0x00 0xa679 0x0027 0x08dd.03b78ea9 0x00800289 0x0000.000.00000000 0x00000001 0x00000000 1263367005
0x16 9 0x00 0xa679 0x002b 0x08dd.03b78eb7 0x00800289 0x0000.000.00000000 0x00000001 0x00000000 1263367005
0x17 9 0x00 0xa679 0x002d 0x08dd.03b78ef3 0x0080028a 0x0000.000.00000000 0x00000001 0x00000000 1263367005
0x18 9 0x00 0xa678 0x0017 0x08dd.03b78eeb 0x0080028a 0x0000.000.00000000 0x00000001 0x00000000 1263367005
0x19 9 0x00 0xa679 0x002a 0x08dd.03b7906a 0x0080028b 0x0000.000.00000000 0x00000001 0x00000000 1263367605
0x1a 9 0x00 0xa676 0x0003 0x08dd.03b78eb3 0x00800289 0x0000.000.00000000 0x00000001 0x00000000 1263367005
[color=red] 0x1b 10 0x80 0xa679 0x0010 0x08dd.03b93e94 0x008002a6 0x0000.000.00000000 0x0000001a 0x00000000 0[/color]
0x1c 9 0x00 0xa678 0x002c 0x08dd.03b78e99 0x00800289 0x0000.000.00000000 0x00000001 0x00000000 1263367005
0x1d 9 0x00 0xa677 0x0013 0x08dd.03b93ed0 0x0080028d 0x0000.000.00000000 0x00000001 0x00000000 1263426136
0x1e 9 0x00 0xa678 0x0007 0x08dd.03b78eaf 0x00800289 0x0000.000.00000000 0x00000001 0x00000000 1263367005
0x1f 9 0x00 0xa676 0x0025 0x08dd.03b78ec5 0x0080028a 0x0000.000.00000000 0x00000001 0x00000000 1263367005
[/quote]

进一步可以研究该事务的相关信息，其跟踪文件，本文不再涉及
[quote]SQL> ALTER SYSTEM DUMP UNDO BLOCK "_SYSSMU3$" XID 3 27 42617;

System altered.
[/quote]

假如现在数据库异常关机之后，导致回滚段_SYSSMU3$出现问题，我们可以告知Oracle，下次启动启动时无须将该回滚段online，当然与该回滚段涉及的事务也将不处理，显示此设置会带来数据库一致性问题，需要谨慎。
[quote]SQL> alter system set "_corrupted_rollback_segments"="_SYSSMU3$" scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size 1262020 bytes
Variable Size 973082172 bytes
Database Buffers 587202560 bytes
Redo Buffers 15511552 bytes
Database mounted.
Database opened.[/quote]

可以看到_SYSSMU$3没有出现在数据字典中

[quote]SQL> select usn,name from v$rollname;

USN NAME
---------- ------------------------------
0 SYSTEM
1 _SYSSMU1$
2 _SYSSMU2$
4 _SYSSMU4$
5 _SYSSMU5$
6 _SYSSMU6$
7 _SYSSMU7$
8 _SYSSMU8$
9 _SYSSMU9$
10 _SYSSMU10$
11 _SYSSMU11$

11 rows selected.[/quote]

假如dump _SYSSMU$3，alert日志显示如下错误
[quote]SQL> ALTER SYSTEM DUMP UNDO HEADER "_SYSSMU3$";
ALTER SYSTEM DUMP UNDO HEADER "_SYSSMU3$"
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


Thu Jan 14 08:16:51 2010
Errors in file /ora10g/app/admin/super/udump/super_ora_32719.trc:
ORA-07445: exception encountered: core dump [ktefdsh()+18] [SIGSEGV] [Address not mapped to object] [0xFFFFFFEC] [] [][/quote]

除了以上方法，屏蔽smon进程恢复事务外，Oracle还提供一event参数，使得Oracle启动时不进行事务恢复。

重置_corrupted_rollback_segments参数，重启数据库

[quote]SQL> alter system set event="10513 trace name context forever, level 2" scope=spfile;

System altered.

SQL> select usn,name from v$rollname;

USN NAME
---------- ------------------------------
0 SYSTEM
1 _SYSSMU1$
2 _SYSSMU2$
3 _SYSSMU3$
4 _SYSSMU4$
5 _SYSSMU5$
6 _SYSSMU6$
7 _SYSSMU7$
8 _SYSSMU8$
9 _SYSSMU9$
10 _SYSSMU10$

11 rows selected.[/quote]

再次dump undo segment header，查看事务有无恢复

[quote]SQL> ALTER SYSTEM DUMP UNDO HEADER "_SYSSMU3$";

System altered.[/quote]

查看跟踪文件，该事务没有恢复，注意其flag标记的变化
[quote]
0x17 9 0x00 0xa679 0x002d 0x08dd.03b78ef3 0x0080028a 0x0000.000.00000000 0x00000001 0x00000000 1263367005
0x18 9 0x00 0xa678 0x0017 0x08dd.03b78eeb 0x0080028a 0x0000.000.00000000 0x00000001 0x00000000 1263367005
0x19 9 0x00 0xa679 0x002a 0x08dd.03b7906a 0x0080028b 0x0000.000.00000000 0x00000001 0x00000000 1263367605
0x1a 9 0x00 0xa676 0x0003 0x08dd.03b78eb3 0x00800289 0x0000.000.00000000 0x00000001 0x00000000 1263367005
[color=red] 0x1b 10 0x90 0xa679 0x0010 0x08dd.03b93e94 0x008002a6 0x0000.000.00000000 0x0000001a 0x00000000 0[/color]
0x1c 9 0x00 0xa679 0xffff 0x08dd.03b9e33d 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1263428662
0x1d 9 0x00 0xa677 0x0013 0x08dd.03b93ed0 0x0080028d 0x0000.000.00000000 0x00000001 0x00000000 1263426136
0x1e 9 0x00 0xa678 0x0007 0x08dd.03b78eaf 0x00800289 0x0000.000.00000000 0x00000001 0x00000000 1263367005
0x1f 9 0x00 0xa676 0x0025 0x08dd.03b78ec5 0x0080028a 0x0000.000.00000000 0x00000001 0x00000000 1263367005
0x20 9 0x00 0xa678 0x001d 0x08dd.03b93ebe 0x0080028d 0x0000.000.00000000 0x00000001 0x00000000 1263426136[/quote]

最后将event 参数重置，恢复初始值，重启数据库
[quote]
SQL> alter system reset event scope=spfile sid='*';

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size 1262020 bytes
Variable Size 973082172 bytes
Database Buffers 587202560 bytes
Redo Buffers 15511552 bytes
Database mounted.
Database opened.

SQL> ALTER SYSTEM DUMP UNDO HEADER "_SYSSMU3$";

System altered.[/quote]

可以看到事务已经恢复，但flag标记不同
[quote]
0x15 9 0x00 0xa679 0x0027 0x08dd.03b78ea9 0x00800289 0x0000.000.00000000 0x00000001 0x00000000 1263367005
0x16 9 0x00 0xa679 0x002b 0x08dd.03b78eb7 0x00800289 0x0000.000.00000000 0x00000001 0x00000000 1263367005
0x17 9 0x00 0xa679 0x002d 0x08dd.03b78ef3 0x0080028a 0x0000.000.00000000 0x00000001 0x00000000 1263367005
0x18 9 0x00 0xa678 0x0017 0x08dd.03b78eeb 0x0080028a 0x0000.000.00000000 0x00000001 0x00000000 1263367005
0x19 9 0x00 0xa679 0x002a 0x08dd.03b7906a 0x0080028b 0x0000.000.00000000 0x00000001 0x00000000 1263367605
0x1a 9 0x00 0xa676 0x0003 0x08dd.03b78eb3 0x00800289 0x0000.000.00000000 0x00000001 0x00000000 1263367005
[color=red] 0x1b 9 0x10 0xa679 0x002c 0x08dd.03ba325b 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1263428922[/color]
0x1c 9 0x00 0xa679 0x001b 0x08dd.03b9e33d 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1263428662
0x1d 9 0x00 0xa677 0x0013 0x08dd.03b93ed0 0x0080028d 0x0000.000.00000000 0x00000001 0x00000000 1263426136
0x1e 9 0x00 0xa678 0x0007 0x08dd.03b78eaf 0x00800289 0x0000.000.00000000 0x00000001 0x00000000 1263367005
0x1f 9 0x00 0xa676 0x0025 0x08dd.03b78ec5 0x0080028a 0x0000.000.00000000 0x00000001 0x00000000 1263367005
0x20 9 0x00 0xa678 0x001d 0x08dd.03b93ebe 0x0080028d 0x0000.000.00000000 0x00000001 0x00000000 1263426136
[/quote]
以上记录，仅供测试。