[20170428]延迟块清除测试.txt
--//做一个延迟块清除测试,前面的测试太乱了,思路非常不清楚.
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> alter database flashback on;
Database altered.
--//打开flashback ，就关闭了IMU,这样测试稳妥一些,不会出现奇怪的问题.
SCOTT@book> select flashback_on from v$database ;
FLASHBACK_ON
------------------
YES
CREATE TABLESPACE TEA DATAFILE
'/mnt/ramdisk/book/tea01.dbf' SIZE 40M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;
--//安全起见重启数据库再测试。
2.测试:
SCOTT@book> create table deptx tablespace tea as select * from dept ;
Table created.
--//dba = 6,129 .
SCOTT@book> update deptx set dname=lower(dname) where deptno=10;
1 row updated.
SCOTT@book> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
2.21.1789
C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE
---------------------------------------------------------------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ---------------- ------------ ------------ ---------------- ---------------- -------------------
ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU2_2996391332$' XID 2 21 1789; 2 21 1789 3 4210 652 57 ACTIVE 1 1 02001500FD060000 00000000818AE050 2017-04-28 10:45:47
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU2_2996391332$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 4210;
SYS@book> alter system flush buffer_cache;
System altered.
SCOTT@book> alter system dump datafile '/mnt/ramdisk/book/tea01.dbf' block 129;
System altered.
Block header dump: 0x01800081
Object id on Block? Y
seg/obj: 0x1677c csc: 0x03.4281a558 itc: 3 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0003.4281a558
0x02 0x0002.015.000006fd 0x00c01072.028c.39 ---- 1 fsc 0x0000.00000000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01800081
block_row_dump:
tab 0, row 0, @0x1f6e
tl: 26 fb: --H-FL-- lb: 0x2 cc: 3
~~~~~~~~~~~~~~~~~~~~=> 使用第2个事务槽.
col 0: [ 2] c1 0b
col 1: [10] 61 63 63 6f 75 6e 74 69 6e 67
col 2: [ 8] 4e 45 57 20 59 4f 52 4b
--//lock没有清除. xid相关信息也和前面能对上.
SYS@book> ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU2_2996391332$';
System altered.
TRN CTL:: seq: 0x028c chd: 0x001b ctl: 0x000e inc: 0x00000000 nfb: 0x0001
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00c01077.028c.26 scn: 0x0003.4281871a
Version: 0x01
FREE BLOCK POOL::
uba: 0x00c01077.028c.26 ext: 0x14 spc: 0x1142
uba: 0x00000000.028c.19 ext: 0x14 spc: 0x1510
uba: 0x00000000.028c.25 ext: 0x14 spc: 0x11cc
uba: 0x00000000.021b.01 ext: 0x2 spc: 0x1f84
uba: 0x00000000.021b.01 ext: 0x2 spc: 0x1f84
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x14 9 0x00 0x06fc 0x000b 0x0003.42819ec6 0x00c01072 0x0000.000.00000000 0x00000001 0x00000000 1493346210
0x15 10 0x80 0x06fd 0x0014 0x0003.4281a5b5 0x00c01072 0x0000.000.00000000 0x00000001 0x00000000 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0x16 9 0x00 0x06fb 0x000e 0x0003.4281a41d 0x00c01072 0x0000.000.00000000 0x00000001 0x00000000 1493347262
--//scn = 0x0003.4281a5b5.
SCOTT@book> @ &r/scn10 0x0003.4281a5b5
C20 SCN_ SCN_BASE SCN_WRAP SCN_BASE SCN
-------------------- ---- -------- ------------ ------------ ------------
0x0003.4281a5b5 0003 4281a5b5 3 1115792821 14000694709
SCOTT@book> select ora_rowscn,deptx.*,dbms_flashback.get_system_change_number from scott.deptx where rownum=1;
ORA_ROWSCN DEPTNO DNAME LOC GET_SYSTEM_CHANGE_NUMBER
------------ ------------ -------------- ------------- ------------------------
14000694616 10 accounting NEW YORK 14000694958
select 14000694616,trunc(14000694616/power(2,32)) scn_wrap,mod(14000694616,power(2,32)) scn_base from dual
14000694616 SCN_WRAP SCN_BASE SCN_WRAP16 SCN_BASE16
------------ ------------ ------------ ---------- ----------
14000694616 3 1115792728 3 4281a558
--//看看来之哪里:
$ cat scn.cmd
set count 8192
set width 210
p dba 6,129 kcbh.bas_kcbh
p dba 6,129 ktbbh.ktbbhcsc.kscnbas
p dba 6,129 ktbbh.ktbbhitl[0].ktbitflg
p dba 6,129 ktbbh.ktbbhitl[0].ktbitbas
p dba 6,129 ktbbh.ktbbhitl[1].ktbitflg
p dba 6,129 ktbbh.ktbbhitl[1].ktbitbas
p dba 6,129 ktbbh.ktbbhitl[2].ktbitflg
p dba 6,129 ktbbh.ktbbhitl[2].ktbitbas
quit
$ $ORACLE_HOME/bin/bbed parfile=bbed.par cmdfile=scn.cmd
BBED> p dba 6,129 kcbh.bas_kcbh
ub4 bas_kcbh @8 0x4281a5b5 <==块scn,现在对应undo事务槽的scn
BBED> p dba 6,129 ktbbh.ktbbhcsc.kscnbas
ub4 kscnbas @28 0x4281a558
BBED> p dba 6,129 ktbbh.ktbbhitl[0].ktbitflg
ub2 ktbitflg @60 0x8000 (KTBFCOM)
BBED> p dba 6,129 ktbbh.ktbbhitl[0].ktbitbas
ub4 ktbitbas @64 0x4281a558 <==对应伪列的ORA_ROWSCN.
BBED> p dba 6,129 ktbbh.ktbbhitl[1].ktbitflg
ub2 ktbitflg @84 0x0001 (NONE)
BBED> p dba 6,129 ktbbh.ktbbhitl[1].ktbitbas
ub4 ktbitbas @88 0x00000000
BBED> p dba 6,129 ktbbh.ktbbhitl[2].ktbitflg
ub2 ktbitflg @108 0x0000 (NONE)
BBED> p dba 6,129 ktbbh.ktbbhitl[2].ktbitbas
ub4 ktbitbas @112 0x00000000
BBED> quit
--//终于清晰了.
3.提交在看看情况:(块延迟清除的情况)
SYS@book> @ &r/bh 6 129
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
---------------- ------- ------ ----- ---------- ----- --- ---------- ---------- ---------- ---------- ---------- ---------------- -----------
0000000084B29130 6 129 1 data block xcur 1 0 0 0 0 0 0000000074DFE000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 0000000074A74000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 0000000074A76000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 000000007500C000
SYS@book> alter system flush buffer_cache;
System altered.
SYS@book> @ &r/bh 6 129
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
---------------- ------- ------ ----- ---------- ----- --- ---------- ---------- ---------- ---------- ---------- ---------------- -----------
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 0000000074DFE000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 0000000074A74000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 0000000074A76000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 000000007500C000
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter system checkpoint ;
System altered.
SCOTT@book> ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU2_2996391332$';
System altered.
TRN CTL:: seq: 0x028c chd: 0x001b ctl: 0x0015 inc: 0x00000000 nfb: 0x0002
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00c01077.028c.26 scn: 0x0003.4281871a
Version: 0x01
FREE BLOCK POOL::
uba: 0x00c01077.028c.26 ext: 0x14 spc: 0x1142
uba: 0x00c01072.028c.39 ext: 0x14 spc: 0x2dc
uba: 0x00000000.028c.25 ext: 0x14 spc: 0x11cc
uba: 0x00000000.021b.01 ext: 0x2 spc: 0x1f84
uba: 0x00000000.021b.01 ext: 0x2 spc: 0x1f84
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x14 9 0x00 0x06fc 0x000b 0x0003.42819ec6 0x00c01072 0x0000.000.00000000 0x00000001 0x00000000 1493346210
0x15 9 0x00 0x06fd 0xffff 0x0003.4281a86c 0x00c01072 0x0000.000.00000000 0x00000001 0x00000000 1493348218
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0x16 9 0x00 0x06fb 0x000e 0x0003.4281a41d 0x00c01072 0x0000.000.00000000 0x00000001 0x00000000 1493347262
--//当前已经提交,事务scn=0x0003.4281a86c
SCOTT@book> @ &r/scn10 0x0003.4281a86c
C20 SCN_ SCN_BASE SCN_WRAP SCN_BASE SCN
-------------------- ---- -------- ------------ ------------ ------------
0x0003.4281a86c 0003 4281a86c 3 1115793516 14000695404
$ $ORACLE_HOME/bin/bbed parfile=bbed.par cmdfile=scn.cmd
BBED> p dba 6,129 kcbh.bas_kcbh
ub4 bas_kcbh @8 0x4281a5b5
BBED> p dba 6,129 ktbbh.ktbbhcsc.kscnbas
ub4 kscnbas @28 0x4281a558
BBED> p dba 6,129 ktbbh.ktbbhitl[0].ktbitflg
ub2 ktbitflg @60 0x8000 (KTBFCOM)
BBED> p dba 6,129 ktbbh.ktbbhitl[0].ktbitbas
ub4 ktbitbas @64 0x4281a558
BBED> p dba 6,129 ktbbh.ktbbhitl[1].ktbitflg
ub2 ktbitflg @84 0x0001 (NONE)
BBED> p dba 6,129 ktbbh.ktbbhitl[1].ktbitbas
ub4 ktbitbas @88 0x00000000
BBED> p dba 6,129 ktbbh.ktbbhitl[2].ktbitflg
ub2 ktbitflg @108 0x0000 (NONE)
BBED> p dba 6,129 ktbbh.ktbbhitl[2].ktbitbas
ub4 ktbitbas @112 0x00000000
BBED> quit
--//可以发现块没有写入提交信息.因为块dba=6,129不在数据缓存,采用延迟块提交.
4.现在访问看看:
SCOTT@book> select ora_rowscn,deptx.*,dbms_flashback.get_system_change_number from scott.deptx where rownum=1;
ORA_ROWSCN DEPTNO DNAME LOC GET_SYSTEM_CHANGE_NUMBER
------------ ------------ -------------- ------------- ------------------------
14000695404 10 accounting NEW YORK 14000695738
select 14000695404,trunc(14000695404/power(2,32)) scn_wrap,mod(14000695404,power(2,32)) scn_base from dual
14000695404 SCN_WRAP SCN_BASE SCN_WRAP16 SCN_BASE16
------------ ------------ ------------ ---------- ----------
14000695404 3 1115793516 3 4281a86c
--//现在ora_rowscn=14000695404,与undo事务槽的scn=0x0003.4281a86c.
select 14000695738,trunc(14000695738/power(2,32)) scn_wrap,mod(14000695738,power(2,32)) scn_base from dual
14000695738 SCN_WRAP SCN_BASE SCN_WRAP16 SCN_BASE16
------------ ------------ ------------ ---------- ----------
14000695738 3 1115793850 3 4281a9ba
SCOTT@book> alter system checkpoint ;
System altered.
SCOTT@book> alter system dump datafile '/mnt/ramdisk/book/tea01.dbf' block 129;
System altered.
Block header dump: 0x01800081
Object id on Block? Y
seg/obj: 0x1677c csc: 0x03.4281a9ba itc: 3 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0003.4281a558
0x02 0x0002.015.000006fd 0x00c01072.028c.39 C--- 0 scn 0x0003.4281a86c
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01800081
data_block_dump,data header at 0x7f97dcb9a274
..
block_row_dump:
tab 0, row 0, @0x1f6e
tl: 26 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 0b
col 1: [10] 61 63 63 6f 75 6e 74 69 6e 67
col 2: [ 8] 4e 45 57 20 59 4f 52 4b
--//看见了吗,提交了,块的lock表示也清除了.flag=C---. scn=0x0003.4281a86c
$ $ORACLE_HOME/bin/bbed parfile=bbed.par cmdfile=scn.cmd
BBED> p dba 6,129 kcbh.bas_kcbh
ub4 bas_kcbh @8 0x4281a9ba <== dbms_flashback.get_system_change_number =0x3.4281a9ba
BBED> p dba 6,129 ktbbh.ktbbhcsc.kscnbas
ub4 kscnbas @28 0x4281a9ba <== csc 也对上了,最后一次块清除csc.
BBED> p dba 6,129 ktbbh.ktbbhitl[0].ktbitflg
ub2 ktbitflg @60 0x8000 (KTBFCOM)
BBED> p dba 6,129 ktbbh.ktbbhitl[0].ktbitbas
ub4 ktbitbas @64 0x4281a558
BBED> p dba 6,129 ktbbh.ktbbhitl[1].ktbitflg
ub2 ktbitflg @84 0x8000 (KTBFCOM) <==表示 C---.表示提交.
BBED> p dba 6,129 ktbbh.ktbbhitl[1].ktbitbas
ub4 ktbitbas @88 0x4281a86c <==通过undo事务槽(没有覆盖)找到提交时scn.
BBED> p dba 6,129 ktbbh.ktbbhitl[2].ktbitflg
ub2 ktbitflg @108 0x0000 (NONE)
BBED> p dba 6,129 ktbbh.ktbbhitl[2].ktbitbas
ub4 ktbitbas @112 0x00000000
BBED> quit
--//终于完成了完整的分析过程,理解许多知识.
