[20170420]关于延迟块清除2.txt
--昨天做延迟块清除测试时，有一个问题我自己一直没搞明白，就是把表空间设置为只读的情况下，当访问块时，
--由于没法更新对应块，不知道为什么每次重启数据库，正常undo的事务槽不可能这么块覆盖，为什么ora_rowscn
--总是变化，而且取的是control scn，要认真探究一下问题在那里。
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 deptx tablespace tea as select * from dept ;
Table created.
SCOTT@book> @ &r/qq deptx 1
ROWID OBJECT FILE BLOCK ROW ROWID_DBA DEPTNO DNAME LOC
------------------ ------------ ------------ ------------ ------------ -------------------- ------------ -------------- -------------
AAAWHfAAGAAAACBAAA 90591 6 129 0 0x1800081 10 ACCOUNTING NEW YORK
--//alter system dump datafile 6 block 129;
2.首先测试延迟块清除undo事务槽没有覆盖的情况：
SCOTT@book> update deptx set dname=lower(dname) where deptno=10;
1 row updated.
SCOTT@book> alter system flush buffer_cache;
System altered.
SYS@book> @ &r/bh1 6 129
DBARFIL DBABLK CLASS CLASS_TYPE STATE STATE1 TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ CR_UBA_REC CR_XID_USN CR_XID_SLT CR_XID_SQN CR_CLS_BAS CR_CLS_WRP LRBA_SEQ LRBA_BNO HSCN_BAS HSCN_WRP HSUB_SCN OBJECT_NAME
------- ----------- ----- ------------------ ----- ------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
6 129 1 data block 1 xcur 1 0 0 0 0 0 0 0 0 0 0 0 781 45803 1115447175 3 1 DEPTX
6 129 1 data block 0 free 0 0 0 0 0 0 0 0 0 0 0 0 3 1115447132 0 0 0 DEPTX
6 129 1 data block 0 free 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
3 rows selected.
--//注一定要执行多次alter system flush buffer_cache;，确保dba=6,129不在数据缓存中。我有几次测试结果是快速提交，问题可能在这里。
SCOTT@book> alter system flush buffer_cache;
System altered.
SCOTT@book> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
8.25.1784
C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE
---------------------------------------------------------------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ---------------- ------------ ------------ ---------------- ---------------- -------------------
ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU8_517538920$' XID 8 25 1784; 8 25 1784 3 379 905 15 ACTIVE 1 1 08001900F8060000 00000000818F3B20 2017-04-20 15:44:16
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU8_517538920$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 379;
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter system checkpoint ;
System altered.
SCOTT@book> alter system checkpoint ;
System altered.
SCOTT@book> alter system checkpoint ;
System altered.
SCOTT@book> ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$';
System altered.
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x19 9 0x00 0x06f8 0xffff 0x0003.427c64ac 0x00c0017b 0x0000.000.00000000 0x00000001 0x00000000 1492674456
--//从这里可以确定如果写入scn 应该是0x0003.427c64ac
3.转储块看看:
SCOTT@book> set numw 12
SCOTT@book> select ora_rowscn,deptx.*,current_scn from scott.deptx,v$database where rownum=1;
ORA_ROWSCN DEPTNO DNAME LOC CURRENT_SCN
------------ ------------ -------------- ------------- ------------
14000350380 10 accounting NEW YORK 14000350633
select 14000350380,trunc(14000350380/power(2,32)) scn_wrap,mod(14000350380,power(2,32)) scn_base from dual
14000350380 SCN_WRAP SCN_BASE SCN_WRAP16 SCN_BASE16
------------ ------------ ------------ ---------- ----------
14000350380 3 1115448492 3 427c64ac
select 14000350633,trunc(14000350633/power(2,32)) scn_wrap,mod(14000350633,power(2,32)) scn_base from dual
14000350633 SCN_WRAP SCN_BASE SCN_WRAP16 SCN_BASE16
------------ ------------ ------------ ---------- ----------
14000350633 3 1115448745 3 427c65a9
SCOTT@book> alter system checkpoint ;
System altered.
/
/
SCOTT@book> alter system dump datafile 6 block 129;
System altered.
Block dump from disk:
buffer tsn: 7 rdba: 0x01800081 (6/129)
scn: 0x0003.427c65a8 seq: 0x01 flg: 0x04 tail: 0x65a80601
~~~~~~~~~~~~~~~~~~~~~
frmt: 0x02 chkval: 0x98db type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F985DCAB200 to 0x00007F985DCAD200
...
Block header dump: 0x01800081
Object id on Block? Y
seg/obj: 0x161df csc: 0x03.427c65a8 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.427c63c1
0x02 0x0008.019.000006f8 0x00c0017b.0389.0f C--- 0 scn 0x0003.427c64ac
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
--//注意看下划线，这个是当时访问时的scn-1。估计使用 dbms_flashback.get_system_change_number 没有偏差了。
--//链接：http://blog.itpub.net/267265/viewspace-1419770/
--// csc: 0x03.427c65a8 = 就是访问块的scn。而undo事务槽没有覆盖，可以取回原值0x0003.427c64ac。lck的标识也被清除了。
3.继续测试：
--//看看表设置只读的情况:
SCOTT@book> update deptx set dname=lower(dname) where deptno=20;
1 row updated.
SCOTT@book> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
8.11.1786
C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE
---------------------------------------------------------------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ---------------- ------------ ------------ ---------------- ---------------- -------------------
ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU8_517538920$' XID 8 11 1786; 8 11 1786 3 379 905 16 ACTIVE 1 1 08000B00FA060000 00000000818F3B20 2017-04-20 15:57:25
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU8_517538920$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 379;
SYS@book> alter system flush buffer_cache;
System altered.
SYS@book> @ &r/bh1 6 129
DBARFIL DBABLK CLASS CLASS_TYPE STATE STATE1 TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ CR_UBA_REC CR_XID_USN CR_XID_SLT CR_XID_SQN CR_CLS_BAS CR_CLS_WRP LRBA_SEQ LRBA_BNO HSCN_BAS HSCN_WRP HSUB_SCN OBJECT_NAME
------- ----------- ----- ------------------ ----- ------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
6 129 1 data block 0 free 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 DEPTX
6 129 1 data block 0 free 0 0 0 0 0 0 0 0 0 0 0 0 3 1115448744 0 0 0 DEPTX
--//一定要保证清除缓存。
SCOTT@book> commit;
Commit complete.
--// 这次测试设置表read only。
SCOTT@book> alter table deptx read only;
Table altered.
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
------------ ------------ -------------- ------------- ------------------------
14000351406 10 accounting NEW YORK 14000351512
--//上班查看：
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
------------ ------------ -------------- ------------- ------------------------
14000351406 10 accounting NEW YORK 14000427006
select 14000351406,trunc(14000351406/power(2,32)) scn_wrap,mod(14000351406,power(2,32)) scn_base from dual
14000351406 SCN_WRAP SCN_BASE SCN_WRAP16 SCN_BASE16
------------ ------------ ------------ ---------- ----------
14000351406 3 1115449518 3 427c68ae
select 14000351512,trunc(14000351512/power(2,32)) scn_wrap,mod(14000351512,power(2,32)) scn_base from dual
14000351512 SCN_WRAP SCN_BASE SCN_WRAP16 SCN_BASE16
------------ ------------ ------------ ---------- ----------
14000351512 3 1115449624 3 427c6918
$ $ORACLE_HOME/bin/bbed parfile=bbed.par cmdfile=scn.cmd
ub4 bas_kcbh @8 0x427c6918
BBED> p dba 6,129 ktbbh.ktbbhcsc.kscnbas
ub4 kscnbas @28 0x427c6918
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 0x427c63c1
BBED> p dba 6,129 ktbbh.ktbbhitl[1].ktbitflg
ub2 ktbitflg @84 0x8000 (KTBFCOM)
BBED> p dba 6,129 ktbbh.ktbbhitl[1].ktbitbas
ub4 ktbitbas @88 0x427c64ac
BBED> p dba 6,129 ktbbh.ktbbhitl[2].ktbitflg
ub2 ktbitflg @108 0x8000 (KTBFCOM)
BBED> p dba 6,129 ktbbh.ktbbhitl[2].ktbitbas
ub4 ktbitbas @112 0x427c68ae
--//可以发现设置表只读，实际上还是"修改信息"的。
SCOTT@book> update deptx set dname=lower(dname) where deptno=20;
update deptx set dname=lower(dname) where deptno=20
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."DEPTX"
4.设置表空间只读看看：
SCOTT@book> alter table deptx read write ;
Table altered.
SCOTT@book> update deptx set dname=lower(dname) where deptno=30;
1 row updated.
SCOTT@book> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
2.12.1753
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 12 1753; 2 12 1753 3 146 610 39 ACTIVE 1 1 02000C00D9060000 0000000081949600 2017-04-21 08:38:51
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU2_2996391332$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 146;
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 2 0 0 0 0 0 000000006F528000 DEPTX
0000000084B29130 6 129 1 data block cr 2 1115525406 3 0 0 0 000000006B958000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 000000007633C000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 0000000072BD0000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 000000006B41C000 DEPTX
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 xcur 2 0 0 0 0 0 000000006F528000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 000000006B958000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 000000007633C000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 0000000072BD0000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 000000006B41C000 DEPTX
--//再次发现alter system flush buffer_cache;并不能完全清除"脏"数据。
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 000000006F528000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 000000006B958000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 000000007633C000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 0000000072BD0000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 000000006B41C000 DEPTX
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter tablespace tea read only ;
Tablespace altered.
SCOTT@book> ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU2_2996391332$';
System altered.
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x0c 9 0x00 0x06d9 0xffff 0x0003.427d9199 0x00c00092 0x0000.000.00000000 0x00000001 0x00000000 1492735253
--//提交时scn=0x0003.427d9199.
--//如果写盘应该scn0x=0003.427d9199。
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
------------ ------------ -------------- ------------- ------------------------
14000427417 10 accounting NEW YORK 14000427559
select 14000427417,trunc(14000427417/power(2,32)) scn_wrap,mod(14000427417,power(2,32)) scn_base from dual
14000427417 SCN_WRAP SCN_BASE SCN_WRAP16 SCN_BASE16
------------ ------------ ------------ ---------- ----------
14000427417 3 1115525529 3 427d9199
--//可以发现现在UNDO事务槽没有覆盖，取回提交是SCN。只不过现在表空间只读，无法写盘。现在重启数据库看看。
SYS@book> startup
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
Database opened.
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
------------ ------------ -------------- ------------- ------------------------
14000427417 10 accounting NEW YORK 14000428377
SCOTT@book> ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU2_2996391332$';
System altered.
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x0c 9 0x00 0x06d9 0x0000 0x0003.427d9199 0x00c00092 0x0000.000.00000000 0x00000001 0x00000000 1492735253
--//确实这样，也许前几次运气不好。
SCOTT@book> alter tablespace tea read write;
Tablespace altered.
SCOTT@book> set numw 12
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
------------ ------------ -------------- ------------- ------------------------
14000427417 10 accounting NEW YORK 14000432529
SCOTT@book> alter system flush buffer_cache;
System altered.
SCOTT@book> alter system flush buffer_cache;
System altered.
select 14000427417,trunc(14000427417/power(2,32)) scn_wrap,mod(14000427417,power(2,32)) scn_base from dual
14000427417 SCN_WRAP SCN_BASE SCN_WRAP16 SCN_BASE16
------------ ------------ ------------ ---------- ----------
14000427417 3 1115525529 3 427d9199
select 14000432529,trunc(14000432529/power(2,32)) scn_wrap,mod(14000432529,power(2,32)) scn_base from dual
14000432529 SCN_WRAP SCN_BASE SCN_WRAP16 SCN_BASE16
------------ ------------ ------------ ---------- ----------
14000432529 3 1115530641 3 427da591
$ $ORACLE_HOME/bin/bbed parfile=bbed.par cmdfile=scn.cmd
BBED> p dba 6,129 kcbh.bas_kcbh
ub4 bas_kcbh @8 0x427da591
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
BBED> p dba 6,129 ktbbh.ktbbhcsc.kscnbas
ub4 kscnbas @28 0x427da591
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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 0x427d9199
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
BBED> p dba 6,129 ktbbh.ktbbhitl[1].ktbitflg
ub2 ktbitflg @84 0x8000 (KTBFCOM)
BBED> p dba 6,129 ktbbh.ktbbhitl[1].ktbitbas
ub4 ktbitbas @88 0x427c64ac
BBED> p dba 6,129 ktbbh.ktbbhitl[2].ktbitflg
ub2 ktbitflg @108 0x8000 (KTBFCOM)
BBED> p dba 6,129 ktbbh.ktbbhitl[2].ktbitbas
ub4 ktbitbas @112 0x427c68ae
--//注意看下划线部分,可以发现块kcbh.bas_kcbh=ktbbh.ktbbhcsc.kscnbas=第1次访问时的scn。
--// ktbbh.ktbbhitl[0].ktbitbas 的更新来自undo事务槽，如果被覆盖来时undo 的control scn(不再测试)
总结：
1.测试还是乱，做这样测试确实有点难。
2.alter system flush buffer_cache; 并不会导致马上脏块写盘。要执行多次，这也是测试反反复复的原因。确实很困惑。
3.alter system checkpoint ;也存在类似问题，不知道是否是IMU作怪。
4.其中许多细节自己还要慢慢理解。
5.关于每次重启数据库,在表空间只读的情况下，执行select ora_rowscn,deptx.*,dbms_flashback.get_system_change_number from scott.deptx where rownum=1;
ora_rowscn都会变化的问题，也许我自己运气不好。实在不好理解。
--//补充alter system flush buffer_cache;不会导致马上将脏块写盘。
SCOTT@book> alter database flashback on;
Database altered.
--//这样相当于关闭IMU。发现确实这样就不存在问题了。