驗證commit之後是否把其他session沒有提交的事務所產生的reodo也寫入了logfile

這個試驗的目的是想驗證一下一個session提交之後lgwr是否把其他session裡的事務還沒有提交所產生的redo也一起寫入logfile了.

在驗證之前結論已經很清楚，commit的時候不管log buffer裡的內容是否已經提交，lgwr會一起寫入logfile，這樣做的目的當然是保證寫的速度，同時恢復的時候也會使用logfile“連續"恢復.

透過dump logfile的內容發現session 76 commit之後把session 164沒有提交的reod也一起寫入logfile了，不過依據是什麼似乎沒有觀察清楚，莫非是dump內容裡的version 185599488？commit一次寫入logfile之後oracle是用什麼來標識他們是一次寫入logfile的?

[@more@]

session 1:
SQL> select group#,status,sequence#,archived from v$log;

GROUP# STATUS SEQUENCE# ARC
---------- ---------------- ---------- ---
4 INACTIVE 90 YES
5 CURRENT 91 NO
6 UNUSED 0 YES

SQL> select distinct sid from v$mystat;

SID
----------
164

SQL> col member format a40
SQL>
SQL> select group#,member from v$logfile;

GROUP# MEMBER
---------- ----------------------------------------
4 E:APPWCHORADATATESTREDO04.LOG
5 E:APPWCHORADATATESTREDO05.LOG
6 E:APPWCHORADATATESTREDO06.LOG

SQL> create table t(id int ,name varchar2(10)) tablespace users;

表已建立。

SQL> insert into t values(1,'a');

已建立 1 行。

SQL> insert into t values(2,'b');

已建立 1 行。

SQL> commit;

提交完成。

SQL> update t set name='abcd' where id=1;

已更新 1 行。
--===========================
session 2:
SQL> select distinct sid from v$mystat;

SID
----------
76
SQL> select object_id,data_object_id from dba_objects where object_name='T' and
owner='SYS';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
52099 52099

SQL> select * from t;

ID NAME
---------- ----------
1 a
2 b

SQL> delete from t where id=2;

已刪除 1 行。

SQL> commit;

提交完成。

SQL> alter system dump logfile 'E:APPWCHORADATATESTREDO05.LOG';

系統已更改。
--=============================
REDO RECORD - Thread:1 RBA: 0x00005b.000000f2.0010 LEN: 0x0218 VLD: 0x05
SCN: 0x0000.0075bea3 SUBSCN: 1 07/26/2010 21:42:01
CHANGE #1 TYP:0 CLS:45 AFN:5 DBA:0x01400049 OBJ:4294967295 SCN:0x0000.0075be86 SEQ: 1 OP:5.2
ktudh redo: slt: 0x001a sqn: 0x0000016a flg: 0x0012 siz: 172 fbi: 0
uba: 0x01405823.0213.1c pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:46 AFN:5 DBA:0x01405823 OBJ:4294967295 SCN:0x0000.0075be85 SEQ: 1 OP:5.1
ktudb redo: siz: 172 spc: 2116 flg: 0x0012 seq: 0x0213 rec: 0x1c
xid: 0x000f.01a.0000016a
ktubl redo: slt: 26 rci: 0 opc: 11.1 objn: 52099 objd: 52099 tsn: 4
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x01405823.0213.1b
prev ctl max cmt scn: 0x0000.0075ba76 prev tx cmt scn: 0x0000.0075ba94
txn start scn: 0xffff.ffffffff logon user: 0 prev brb: 20994079 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01000010 hdba: 0x0100000b
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: -3
col 1: [ 1] 61 --session 164修改前的a放在了undo裡
CHANGE #3 TYP:2 CLS: 1 AFN:4 DBA:0x01000010 OBJ:52099 SCN:0x0000.0075be97 SEQ: 1 OP:11.5
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: F xid: 0x000f.01a.0000016a uba: 0x01405823.0213.1c
Block cleanout record, scn: 0x0000.0075bea3 ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.0075be97
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01000010 hdba: 0x0100000b
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
ncol: 2 nnew: 1 size: 3
col 1: [ 4] 61 62 63 64 --session 164修改後的值
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:5.20
session number = 164
serial number = 177
transaction name =
version 185599488
audit sessionid 4294967295
--============================================================================
REDO RECORD - Thread:1 RBA: 0x00005b.000000fe.0080 LEN: 0x0090 VLD: 0x01
SCN: 0x0000.0075beb0 SUBSCN: 1 07/26/2010 21:42:13
CHANGE #1 TYP:0 CLS:37 AFN:5 DBA:0x01400009 OBJ:4294967295 SCN:0x0000.0075beaf SEQ: 1 OP:5.4
ktucm redo: slt: 0x0008 sqn: 0x0000015b srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x01405a1d.012b.10 ext: 2 spc: 4934 fbi: 0
CHANGE #2 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:24.11
KRV CMT marker usn: 0xb start scn: 0x0000.0075beaf
Recursive transaction

REDO RECORD - Thread:1 RBA: 0x00005b.000000ff.0010 LEN: 0x006c VLD: 0x05
SCN: 0x0000.0075beb1 SUBSCN: 1 07/26/2010 21:42:19
CHANGE #1 TYP:0 CLS: 1 AFN:4 DBA:0x01000010 OBJ:52099 SCN:0x0000.0075beaa SEQ: 1 OP:4.1
Block cleanout record, scn: 0x0000.0075beb1 ver: 0x01 opt: 0x01, entries follow...

REDO RECORD - Thread:1 RBA: 0x00005b.000000ff.007c LEN: 0x0040 VLD: 0x01
SCN: 0x0000.0075beb2 SUBSCN: 1 07/26/2010 21:42:19
CHANGE #1 TYP:0 CLS: 1 AFN:4 DBA:0x01000010 OBJ:52099 SCN:0x0000.0075beb1 SEQ: 1 OP:4.1
Block cleanout record, scn: 0x0000.0075beb2 ver: 0x01 opt: 0x01, entries follow...

REDO RECORD - Thread:1 RBA: 0x00005b.000000ff.00bc LEN: 0x01c4 VLD: 0x01
SCN: 0x0000.0075beb3 SUBSCN: 1 07/26/2010 21:42:19
CHANGE #1 TYP:0 CLS:51 AFN:5 DBA:0x01400079 OBJ:4294967295 SCN:0x0000.0075be95 SEQ: 1 OP:5.2
ktudh redo: slt: 0x000b sqn: 0x0000015e flg: 0x0012 siz: 200 fbi: 0
uba: 0x014000c8.0103.11 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:52 AFN:5 DBA:0x014000c8 OBJ:4294967295 SCN:0x0000.0075be94 SEQ: 1 OP:5.1
ktudb redo: siz: 200 spc: 4470 flg: 0x0012 seq: 0x0103 rec: 0x11
xid: 0x0012.00b.0000015e
ktubl redo: slt: 11 rci: 0 opc: 11.1 objn: 52099 objd: 52099 tsn: 4
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x014000c8.0103.10
prev ctl max cmt scn: 0x0000.0075ba85 prev tx cmt scn: 0x0000.0075baa2
txn start scn: 0xffff.ffffffff logon user: 0 prev brb: 20971719 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: L itl: xid: 0x0013.001.0000016d uba: 0x01409c57.019d.02
flg: C--- lkc: 0 scn: 0x0000.0075be97
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01000010 hdba: 0x0100000b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 1(0x1) size/delt: 8
fb: --H-FL-- lb: 0x0 cc: 2
null: --
col 0: [ 2] c1 03
col 1: [ 1] 62 --session 76刪除t表中的資料(2,'b')並且事務已經commit(從lb: 0x0 可以看出)
CHANGE #3 TYP:0 CLS: 1 AFN:4 DBA:0x01000010 OBJ:52099 SCN:0x0000.0075beb2 SEQ: 1 OP:11.3
KTB Redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: F xid: 0x0012.00b.0000015e uba: 0x014000c8.0103.11
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01000010 hdba: 0x0100000b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 1(0x1)
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:5.20
session number = 76
serial number = 57
transaction name =
version 185599488
audit sessionid 4294967295