[20151218]未提交事务保存在那里.txt-CSDN博客

[20151218]dml未提交事务最新内容到底存储在什么地方.txt

--刚刚看了http://www.itpub.net/thread-1940809-1-1.html上的讨论。

--我自己的想法应该在buffer cache中，自己也做1些测试，说明问题：

1.环境：

SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create table t (id number, name varchar2(20)) ;
Table created.

SCOTT@book> insert into t values (1,'AAAA');
1 row created.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> select rowid,t.* from t;
ROWID ID NAME
------------------ ---------- --------------------
AAAVr7AAEAAAAIPAAA 1 AAAA

SCOTT@book> @ &r/rowid AAAVr7AAEAAAAIPAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
88827 4 527 0 4,527 alter system dump datafile 4 block 527 ;

--实际上如果你不写盘，通过bbed观察看到的内容是不对的。一般这个时候我要通过bbed观察要执行alter system checkpoint,或者刷新脏块到磁盘。
SCOTT@book> alter system checkpoint;
System altered.

--这个时候通过bbed观察可以发现数据已经写盘。
BBED> set dba 4,527
DBA 0x0100020f (16777743 4,527)

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8177 0x2c

BBED> x /rnc
rowdata[0] @8177
----------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x00
cols@8179: 2

col 0[2] @8180: 1
col 1[4] @8183: AAAA

2.开始测试：
SYS@book> @ &r/bh 4 527

HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000083EED060 4 527 1 data block xcur 1 0 0 0 0 0 000000007469A000 T

SCOTT@book> update t set name='BBBB' where id=1;
1 row updated.
--不提交！

SYS@book> @ &r/bh 4 527
HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000083EED060          4        527          1 data block         xcur                1          0          0          0          0          0 000000007B07E000 T
0000000083EED060          4        527          1 data block         cr                  1    1475062          0          0          0          0 000000007469A000 T

--可以发现两个STATE不一样。原来变成了BA=000000007469A000,xucr=>cr. XCUR表示当前块的状态。
-- BA 表示就是块地址，如果检查里面的内容就知道问题。数据块大小8192. 8192 = 0x2000.而且我仅仅插入一条应该靠近块的底板。

SYS@book> oradebug setmypid
Statement processed.

--0x000000007B07E000+0x2000-0x10 = 0x7B07FFF0
--0x000000007469A000+0x2000-0x10 = 0x7469BFF0

SYS@book> oradebug peek 0x7B07FFF0 16
[07B07FFF0, 07B080000) = 02022C00 0402C102 42424242 81FA0601

SYS@book> oradebug peek 0x7469BFF0 16
[07469BFF0, 07469C000) = 02002C00 0402C102 41414141 7FBF0602

--ASCII=0x41=>对应的就是A ， ASCII=0x42=>对应的就是B.从这里可以证明DML未提交事务最新内容到底存储在buffer cache中。
--实际上你这样执行一个alter system checkpoint;或者alter system flush buffer_cache;再转储就可以看到。

3.转储问题：
--实际上这个问题一直困扰我以前的学习，执行dml未提交如果这个时候参考转储的块，因为没有写盘，往往看到错误的信息。
--换1句话讲alter system dump datafile 4 block 527 ;看到的磁盘文件的信息，如何看内存的信息呢？可以参考我写另外一篇blog。
http://blog.itpub.net/267265/viewspace-1659981/

--继续测试：

SCOTT@book> commit ;
Commit complete.

SCOTT@book> update t set name='CCCC' where id=1;
1 row updated.

--不提交！
SYS@book> @ &r/bh 4 527
HLADDR              DBARFIL     DBABLK      CLASS CLASS_TYPE         STATE             TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA               OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000083EED060          4        527          1 data block         xcur                1          0          0          0          0          0 0000000077F3A000 T
0000000083EED060          4        527          1 data block         cr                  1    1476290          0          3       3014        498 000000007839A000 T
0000000083EED060          4        527          1 data block         cr                  1    1476289          0          3       3014        498 0000000078372000 T
0000000083EED060          4        527          1 data block         cr                  1    1476338          0          0          0          0 00000000786CC000 T
0000000083EED060          4        527          1 data block         free                0          0          0          0          0          0 000000007B07E000 T
0000000083EED060          4        527          1 data block         free                0          0          0          0          0          0 000000007469A000 T
6 rows selected.

--注意看STATE=xcur,BA=0x0000000077F3A000,0x0000000077F3A000+0x2000-0x10=0x77F3BFF0.

SYS@book> oradebug peek 0x77F3BFF0 16
[077F3BFF0, 077F3C000) = 02012C00 0402C102 43434343 86EF0601

SCOTT@book> select dump('CCCC',16) from dual ;
DUMP('CCCC',16)
-------------------------
Typ=96 Len=4: 43,43,43,43

--正好对上。

BBED> set dba 4,527
DBA 0x0100020f (16777743 4,527)
--DBA地址是16777743。

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8177 0x2c

BBED> x /rnc
rowdata[0] @8177
----------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x02
cols@8179: 2

col 0[2] @8180: 1
col 1[4] @8183: BBBB

--可以发现依旧是旧的信息。name='BBBB'.

SCOTT@book> alter session set events 'immediate trace name set_tsn_p1 level 5';
Session altered.
--注意这里的level 要在ts# 原来基础上+1，为什么我不知道?参考原来的测试：http://blog.itpub.net/267265/viewspace-1659981/

SCOTT@book> ALTER SESSION SET EVENTS 'immediate trace name buffer level 16777743';
Session altered.

--也可以执行ALTER SESSION SET EVENTS 'immediate trace name buffer level 0x0100020f';

Dump of buffer cache at level 10 for tsn=4 rdba=16777743
BH (0x77ff5638) file#: 4 rdba: 0x0100020f (4/527) class: 1 ba: 0x77f3a000
set: 56 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
dbwrid: 1 obj: 88827 objn: 88827 tsn: 4 afn: 4 hint: f
hash: [0x783f9170,0x83eee1e0] lru: [0x77ff5860,0x77ff55f0]
ckptq: [NULL] fileq: [NULL] objq: [0x7c43a348,0x7c43a348] objaq: [0x7c43a338,0x7c43a338]
st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
buffer tsn: 4 rdba: 0x0100020f (4/527)
scn: 0x0000.0016875c seq: 0x01 flg: 0x04 tail: 0x875c0601
frmt: 0x02 chkval: 0x6d86 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000077F3A000 to 0x0000000077F3C000
077F3A000 0000A206 0100020F 0016875C 04010000 [........\.......]
077F3A010 00006D86 00000001 00015AFB 001686F3 [.m.......Z......]
077F3A020 00000000 00320002 01000208 00120004 [......2.........]
077F3A030 000002FD 00C0014B 0020011C 00000001 [....K..... .....]
077F3A040 00000000 000D000A 00000A1A 00C00BC6 [................]
077F3A050 001801F2 00008000 001686EF 00000000 [................]
077F3A060 00000000 00010100 0014FFFF 1F791F8D [..............y.]
077F3A070 00001F79 1F8D0001 00000000 00000000 [y...............]
077F3A080 00000000 00000000 00000000 00000000 [................]
        Repeat 502 times
077F3BFF0 02012C00 0402C102 43434343 875C0601 [.,......CCCC..\.]
Block header dump: 0x0100020f
Object id on Block? Y
seg/obj: 0x15afb csc: 0x00.1686f3 itc: 2 flg: E typ: 1 - DATA
     brn: 0 bdba: 0x1000208 ver: 0x01 opc: 0
     inc: 0 exflg: 0

Itl           Xid                  Uba         Flag Lck        Scn/Fsc
0x01   0x0004.012.000002fd 0x00c0014b.011c.20 ----    1 fsc 0x0000.00000000
0x02   0x000a.00d.00000a1a 0x00c00bc6.01f2.18 C---    0 scn 0x0000.001686ef
bdba: 0x0100020f
data_block_dump,data header at 0x77f3a064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x77f3a064
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f8d
avsp=0x1f79
tosp=0x1f79
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f8d
block_row_dump:
tab 0, row 0, @0x1f8d
tl: 11 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 4] 43 43 43 43
end_of_block_dump

BH (0x783f90b8) file#: 4 rdba: 0x0100020f (4/527) class: 1 ba: 0x7839a000
set: 59 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 1,15
dbwrid: 1 obj: 88827 objn: 88827 tsn: 4 afn: 4 hint: f
hash: [0x783f7910,0x77ff56f0] lru: [0x7b3e5c18,0x783f9070]
ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
st: CR md: NULL tch: 1
cr: [scn: 0x0.1686c2],[xid: 0xa.d.a1a],[uba: 0xc00bc6.1f2.18],[cls: 0x0.1686c2],[sfl: 0x0],[lc: 0x0.1686c2]
flags: only_sequential_access
buffer tsn: 4 rdba: 0x0100020f (4/527)
scn: 0x0000.001686c3 seq: 0x00 flg: 0x00 tail: 0x86c30600
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000007839A000 to 0x000000007839C000
07839A000 0000A206 0100020F 001686C3 00000000 [................]
07839A010 00000000 00000001 00015AFB 001686C2 [.........Z......]
07839A020 00000000 00320002 01000208 0009000A [......2.........]
07839A030 00000A19 00C00BC2 001E01F2 00008000 [................]
07839A040 00167F29 00000000 00000000 00000000 [)...............]
07839A050 00000000 00000000 00000000 00000000 [................]
07839A060 00000000 00010100 0014FFFF 1F791F8D [..............y.]
07839A070 00001F79 1F8D0001 00000000 00000000 [y...............]
07839A080 00000000 00000000 00000000 00000000 [................]
        Repeat 502 times
07839BFF0 02002C00 0402C102 41414141 86C30600 [.,......AAAA....]
Block header dump: 0x0100020f
Object id on Block? Y
seg/obj: 0x15afb csc: 0x00.1686c2 itc: 2 flg: E typ: 1 - DATA
     brn: 0 bdba: 0x1000208 ver: 0x01 opc: 0
     inc: 0 exflg: 0

Itl           Xid                  Uba         Flag Lck        Scn/Fsc
0x01   0x000a.009.00000a19 0x00c00bc2.01f2.1e C---    0 scn 0x0000.00167f29
0x02   0x0000.000.00000000 0x00000000.0000.00 ----    0 fsc 0x0000.00000000
bdba: 0x0100020f
data_block_dump,data header at 0x7839a064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x7839a064
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f8d
avsp=0x1f79
tosp=0x1f79
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f8d
block_row_dump:
tab 0, row 0, @0x1f8d
tl: 11 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 02
col 1: [ 4] 41 41 41 41
end_of_block_dump