[20170421]警惕打开IMU对测试的影响.txt
--这一系列的问题都来源与IMU有关，我以前的测试环境都是dg环境，使用dgmgrl管理，为了一些测试切换我两边数据库都打开flashback，
--这样IMU是失效的。
--问题起源别人重复测试,无法还原我当时的测试结果。
[20140507]实例crash恢复.txt => http://blog.itpub.net/267265/viewspace-1156043/
--于是我重复测试，链接
[20170322]实例crash恢复2.txt => http://blog.itpub.net/267265/viewspace-2135771/
--发现是IMU在作怪，关闭IMU就可以还原我原来的测试。顺便还看了一些IMU方面的知识:
[20170322]关于IMU.txt => http://blog.itpub.net/267265/viewspace-2135813/
--//有几种情况关闭IMU特性.
1.打开flashback on功能.
2.开启附加日志.alter database add supplemental log data;
3.rac环境.
4._in_memory_undo=false.
--在以后的问题中有遇到alter system checkpoing并不会将全部脏块写盘。
0324alter system checkpoing会写脏块吗 => http://blog.itpub.net/267265/viewspace-2136017/
--最近做块延迟清除的测试也发现一些问题，这些实际上可能都与IMU有关，以后注意，在写执行alter system flush buffer_cache;的例子：
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> select flashback_on from v$database ;
FLASHBACK_ON
------------------
NO
--//现在IMU是开启的。也可以查询视图X$KCRFSTRAND确定。
SELECT INDX
,last_buf_kcrfa
,PNEXT_BUF_KCRFA_CLN nxtbufadr
,NEXT_BUF_NUM_KCRFA_CLN nxtbuf#
,BYTES_IN_BUF_KCRFA_CLN "B/buf"
,PVT_STRAND_STATE_KCRFA_CLN state
,STRAND_NUM_ORDINAL_KCRFA_CLN strand#
,PTR_KCRF_PVT_STRAND stradr
,INDEX_KCRF_PVT_STRAND stridx
,SPACE_KCRF_PVT_STRAND strspc
,TXN_KCRF_PVT_STRAND txn
,TOTAL_BUFS_KCRFA totbufs#
,STRAND_SIZE_KCRFA strsz
FROM X$KCRFSTRAND ;
2.测试1：
SCOTT@book> create table deptx tablespace tea as select * from dept ;
Table created.
--//正常将把信息写入数据文件6,129块中，因为那个表空间没有任何信息在create之前。
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 00000000758D4000
--//注意看state=free,实际上oracle取了一个巧，在ctas时并没有读取dba=6,129块，而且在内存中构造1个块，这个叫块的新化。
SCOTT@book> update deptx set dname=lower(dname) where deptno=10;
1 row updated.
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 0000000074F8A000 DEPTX
0000000084B29130 6 129 1 data block cr 1 1115535851 3 0 0 0 0000000074F8C000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 00000000758D4000
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 1 0 0 0 0 0 0000000074F8A000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 0000000074F8C000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 00000000758D4000
--//可以有1个状态还是STATE =xcur,也就是脏块没有写盘。
SCOTT@book> alter system dump datafile 6 block 129;
System altered.
7FFCF15527A0 0203002C 4F0A29C1 41524550 4E4F4954 [,....).OPERATION]
7FFCF15527B0 4F420653 4E4F5453 0203002C 53051FC1 [S.BOSTON,......S]
7FFCF15527C0 53454C41 49484307 4F474143 0203002C [ALES.CHICAGO,...]
7FFCF15527D0 520815C1 41455345 06484352 4C4C4144 [...RESEARCH.DALL]
7FFCF15527E0 002C5341 0BC10203 4343410A 544E554F [AS,......ACCOUNT]
7FFCF15527F0 08474E49 2057454E 4B524F59 B9150602 [ING.NEW YORK....]
--//可以发现记录还是大写的信息。
SYS@book> alter system flush buffer_cache;
System altered.
SYS@book> @ &r/bh 6 129
old 23: dbarfil = &1 and
new 23: dbarfil = 6 and
old 24: dbablk = &2
new 24: dbablk = 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 0000000074F8A000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 0000000074F8C000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 00000000758D4000
SCOTT@book> alter system dump datafile 6 block 129;
System altered.
7FFCF15527A0 0203002C 4F0A29C1 41524550 4E4F4954 [,....).OPERATION]
7FFCF15527B0 4F420653 4E4F5453 0203002C 53051FC1 [S.BOSTON,......S]
7FFCF15527C0 53454C41 49484307 4F474143 0203002C [ALES.CHICAGO,...]
7FFCF15527D0 520815C1 41455345 06484352 4C4C4144 [...RESEARCH.DALL]
7FFCF15527E0 022C5341 0BC10203 6363610A 746E756F [AS,......account]
7FFCF15527F0 08676E69 2057454E 4B524F59 BA1C0601 [ing.NEW YORK....]
--//这样才真正写盘。这也是我前面测试延迟块清除反反复复的原因。
3.如果你关闭IMU就可以发现没有这个问题。
SYS@book> alter database flashback on ;
Database 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 0000000074F8A000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 0000000074F8C000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 00000000758D4000
SCOTT@book> update deptx set dname=lower(dname) where deptno=20;
1 row updated.
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 0000000073896000 DEPTX
0000000084B29130 6 129 1 data block cr 2 1115537108 3 0 0 0 00000000738A2000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 0000000073AD0000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 0000000073AD2000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 0000000074F8A000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 0000000074F8C000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 00000000758D4000
7 rows selected.
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 0000000073896000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 00000000738A2000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 0000000073AD0000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 0000000073AD2000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 0000000074F8A000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 0000000074F8C000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 00000000758D4000
7 rows selected.
--//state= free ,我反复多次执行alter system flush buffer_cache;后state=free.而IMU打开的情况下，就不会这样。
--//这一切实际上都与IMU的工作机制有关。这也是我前面测试遇到的奇怪问题。
--// 附上bh.sql脚本。
set echo off
--------------------------------------------------------------------------------
-- @name: bh
-- @author: dion cho
-- @note: show block header
-- @usage: @bh f# b#
--------------------------------------------------------------------------------
col object_name format a20
col state format a10
select
b.hladdr,
b.dbarfil,
b.dbablk,
b.class,
decode(b.class,1,'data block',2,'sort block',3,'save undo block', 4,
'segment header',5,'save undo header',6,'free list',7,'extent map',
8,'1st level bmb',9,'2nd level bmb',10,'3rd level bmb', 11,'bitmap block',
12,'bitmap index block',13,'file header block',14,'unused',
15,'system undo header',16,'system undo block', 17,'undo header',
18,'undo block') class_type,
decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated') as state,
b.tch,
cr_scn_bas,
cr_scn_wrp,
cr_uba_fil,
cr_uba_blk,
cr_uba_seq,
ba,
(select object_name from dba_objects where data_object_id = b.obj) as object_name
from x$bh b
where
dbarfil = &1 and
dbablk = &2
;
