[20181206]关于一致性读取3.txt
--//简单演示一致性读取以及如何读取undo重构数据块的.我不想转储对应的undo块,解析那些复杂的过程.
1.环境:
SCOTT@book> @ 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用户登录,执行如下:
create table t (id number,name varchar2(10));
insert into t select 1 id ,lpad('A',10,'A') name from dual ;
commit ;
--//分析略.
SCOTT@book> select rowid,t.* from t;
ROWID ID NAME
------------------ ---------- --------------------
AAAWGdAAEAAAAIMAAA 1 AAAAAAAAAA
SCOTT@book> @ rowid AAAWGdAAEAAAAIMAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
90525 4 524 0 0x100020C 4,524 alter system dump datafile 4 block 524 ;
SQL> alter system set "_smu_debug_mode" = 45;
System altered.
--//可以指定回滚段.要重启才生效.
--//说明之所以这样,主要我发现执行dml使用的回滚段会使用相同的块.
2.建立测试脚本:
$ cat b1.txt
var x refcursor;
set numw 12
select current_scn from v$database;
exec open :x for select * from t where id=1;
pause
alter system flush buffer_cache;
alter system flush buffer_cache;
@ 10046on 12
print :x
@ 10046off
@ pp
quit
$ cat b4.txt
set linesize 300
column name new_value v_rollname
column name noprint
select name from v$rollname where usn=1;
set transaction use rollback segment "&&v_rollname";
update t set name=lpad('B',9,'B') where id=1;
@ xid
commit ;
select name from v$rollname where usn=2;
set transaction use rollback segment "&&v_rollname";
update t set name=lpad('C',8,'C') where id=1;
@ xid
commit;
select name from v$rollname where usn=3;
set transaction use rollback segment "&&v_rollname";
update t set name=lpad('D',7,'D') where id=1;
@ xid
commit ;
select name from v$rollname where usn=4;
set transaction use rollback segment "&&v_rollname";
update t set name=lpad('E',6,'E') where id=1;
@ xid
commit ;
column name print
3.执行脚本:
SCOTT@book> @ b1.txt
CURRENT_SCN
------------
13815694556
PL/SQL procedure successfully completed.
--//这里暂停执行脚本 b4.txt
System altered.
System altered.
old 1: alter session set events '10046 trace name context forever, level &1'
new 1: alter session set events '10046 trace name context forever, level 12'
Session altered.
ID NAME
------------ --------------------
1 AAAAAAAAAA
Session altered.
TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_38984.trc
SCOTT@book> @ b4.txt
old 1: set transaction use rollback segment "&&v_rollname"
new 1: set transaction use rollback segment "_SYSSMU1_3724004606$"
Transaction set.
1 row updated.
XIDUSN_XIDSLOT_XIDSQN
------------------------------
1.22.1560
C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE FLAG
---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------------- ------------------- ----------
ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU1_3724004606$' XID 1 22 1560; 1 22 1560 3 1356 768 11 ACTIVE 1 1 0100160018060000 0000000082360348 2018-12-06 08:48:44 -2.147E+09
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU1_3724004606$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 1356;
Commit complete.
old 1: set transaction use rollback segment "&&v_rollname"
new 1: set transaction use rollback segment "_SYSSMU2_2996391332$"
Transaction set.
1 row updated.
XIDUSN_XIDSLOT_XIDSQN
------------------------------
2.24.1784
C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE FLAG
---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------------- ------------------- ----------
ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU2_2996391332$' XID 2 24 1784; 2 24 1784 3 1217 559 46 ACTIVE 1 1 02001800F8060000 0000000082360348 2018-12-06 08:48:44 -2.147E+09
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU2_2996391332$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 1217;
Commit complete.
old 1: set transaction use rollback segment "&&v_rollname"
new 1: set transaction use rollback segment "_SYSSMU3_1723003836$"
Transaction set.
1 row updated.
XIDUSN_XIDSLOT_XIDSQN
------------------------------
3.18.1777
C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE FLAG
---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------------- ------------------- ----------
ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU3_1723003836$' XID 3 18 1777; 3 18 1777 3 1010 921 52 ACTIVE 1 1 03001200F1060000 0000000082360348 2018-12-06 08:48:44 -2.147E+09
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU3_1723003836$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 1010;
Commit complete.
old 1: set transaction use rollback segment "&&v_rollname"
new 1: set transaction use rollback segment "_SYSSMU4_1254879796$"
Transaction set.
1 row updated.
XIDUSN_XIDSLOT_XIDSQN
------------------------------
4.11.1571
C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE FLAG
---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------------- ------------------- ----------
ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU4_1254879796$' XID 4 11 1571; 4 11 1571 3 179 811 45 ACTIVE 1 1 04000B0023060000 0000000082360348 2018-12-06 08:48:44 -2.147E+09
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU4_1254879796$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 179;
Commit complete.
--//使用的回滚段保存前映像的块的dba是(3,179) (3,1010) (3,1217) (3,1356) [注:从尾部向前看.]
4.分析转储:
$ grep -i "nam='db file" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_38984.trc
WAIT #140008354428832: nam='db file sequential read' ela= 12 file#=4 block#=522 blocks=1 obj#=90525 tim=1544057363227135 --//读数据段段头.
WAIT #140008354428832: nam='db file scattered read' ela= 27 file#=4 block#=523 blocks=5 obj#=90525 tim=1544057363227276 --//读数据块,注意blocks=5,包括了dba=4,524.
WAIT #140008354428832: nam='db file sequential read' ela= 10 file#=3 block#=179 blocks=1 obj#=0 tim=1544057363227374 --//修改lpad('E',6,'E')的事务.
WAIT #140008354428832: nam='db file sequential read' ela= 11 file#=3 block#=1010 blocks=1 obj#=0 tim=1544057363227490 --//修改lpad('D',7,'D')的事务.
WAIT #140008354428832: nam='db file sequential read' ela= 8 file#=3 block#=1217 blocks=1 obj#=0 tim=1544057363227553 --//修改lpad('C',6,'C')的事务
WAIT #140008354428832: nam='db file sequential read' ela= 17 file#=3 block#=1356 blocks=1 obj#=0 tim=1544057363227624 --//修改lpad('B',6,'B')的事务.
SYS@book> @ bh 4 524
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
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000084D68628 4 524 1 data block cr 1 930792668 3 3 179 811 0000000076B3C000 T
0000000084D68628 4 524 1 data block xcur 0 0 0 0 0 0 0000000078784000 T
--//看看state='cr',ba=0000000063C2A000地址指向的块信息:
SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug peek 0x76B3C000 8192 1
[076B3C000, 076B3E000) = 0000A206 0100020C 377AC521 00000003 00000000 00000001 0001619D 377AC4F1 00000003 00320002 01000208 0018000A 000052D1 00C001B1 ...
SYS@book> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_39054.trc
--//检查转储:
*** 2018-12-06 08:53:44.097
Processing Oradebug command 'peek 0x76B3C000 8192 1'
[076B3C000, 076B3E000) = 0000A206 0100020C 377AC521 00000003 00000000 ...
Dump of memory from 0x076B3C014 to 0x076B3E000
076B3C010 00000001 0001619D 377AC4F1 [.....a....z7]
076B3C020 00000003 00320002 01000208 0018000A [......2.........]
076B3C030 000052D1 00C001B1 00070F3B 00038000 [.R......;.......]
076B3C040 377AC304 00000000 00000000 00000000 [..z7............]
076B3C050 00000000 00000000 00000000 00000000 [................]
076B3C060 00000000 00010100 0014FFFF 1F731F0F [..............s.]
076B3C070 00001F73 1F0F0001 00000000 00000000 [s...............]
076B3C080 00000000 00000000 00000000 00000000 [................]
Repeat 494 times
076B3DF70 2C000000 C1020200 41410A02 41414141 [...,......AAAAAA]
076B3DF80 41414141 0202002C 420902C1 42424242 [AAAA,......BBBBB]
076B3DF90 42424242 0202002C 430802C1 43434343 [BBBB,......CCCCC]
076B3DFA0 2C434343 C1020200 44440702 44444444 [CCC,......DDDDDD]
076B3DFB0 02012C44 0602C102 45454545 002C4545 [D,......EEEEEE,.]
076B3DFC0 02C10202 44444407 44444444 0202002C [.....DDDDDDD,...]
076B3DFD0 430802C1 43434343 2C434343 C1020200 [...CCCCCCCC,....]
076B3DFE0 42420902 42424242 2C424242 C1020200 [..BBBBBBBBB,....]
076B3DFF0 41410A02 41414141 41414141 C5210600 [..AAAAAAAAAA..!.]
*** 2018-12-06 08:53:44.098
Oradebug command 'peek 0x76B3C000 8192 1' console output:
[076B3C000, 076B3E000) = 0000A206 0100020C 377AC521 00000003 00000000 00000001 0001619D 377AC4F1 00000003 00320002 01000208 0018000A 000052D1 00C001B1 ...
--//你可以发现一致性读取如何通过undo重构数据块的信息,就好像反转操作.由于我每次修改长度都不等于原来的长度.这样看到转储的信息包含A,B,C,D,E等字符串2遍.
--//换一种方式转储数据缓存的对应数据块dba=4,524:
SYS@book> @ bbvi 4 524
BVI_COMMAND
----------------------------------------------------------------------------------------------------
bvi -b 4292608 -s 8192 /mnt/ramdisk/book/users01.dbf
xxd -c16 -g 2 -s 4292608 -l 8192 /mnt/ramdisk/book/users01.dbf
dd if=/mnt/ramdisk/book/users01.dbf bs=8192 skip=524 count=1 of=4_524.dd conv=notrunc 2>/dev/null
od -j 4292608 -N 8192 -t x1 -v /mnt/ramdisk/book/users01.dbf
hexdump -s 4292608 -n 8192 -C -v /mnt/ramdisk/book/users01.dbf
alter system dump datafile '/mnt/ramdisk/book/users01.dbf' block 524;
alter session set events 'immediate trace name set_tsn_p1 level 5';
alter session set events 'immediate trace name buffer level 16777740';
9 rows selected.
SYS@book> alter session set events 'immediate trace name set_tsn_p1 level 5';
Session altered.
SYS@book> alter session set events 'immediate trace name buffer level 16777740';
Session altered.
BH (0x76bf5770) file#: 4 rdba: 0x0100020c (4/524) class: 1 ba: 0x76b3c000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//ba地址与前面看到的一致.
set: 65 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 18,19
dbwrid: 1 obj: 90525 objn: 90525 tsn: 4 afn: 4 hint: f
hash: [0x787f8408,0x84d69f20] lru: [0x84bd22c0,0x783dbee0]
ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
st: CR md: NULL tch: 1
cr: [scn: 0x3.377ac4dc],[xid: 0x4.b.623],[uba: 0xc000b3.32b.2d],[cls: 0x3.377ac520],[sfl: 0x1],[lc: 0x3.377ac4f2]
flags: only_sequential_access
buffer tsn: 4 rdba: 0x0100020c (4/524)
scn: 0x0003.377ac521 seq: 0x00 flg: 0x00 tail: 0xc5210600
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000076B3C000 to 0x0000000076B3E000
076B3C000 0000A206 0100020C 377AC521 00000003 [........!.z7....]
076B3C010 00000000 00000001 0001619D 377AC4F1 [.........a....z7]
076B3C020 00000003 00320002 01000208 0018000A [......2.........]
076B3C030 000052D1 00C001B1 00070F3B 00038000 [.R......;.......]
076B3C040 377AC304 00000000 00000000 00000000 [..z7............]
076B3C050 00000000 00000000 00000000 00000000 [................]
076B3C060 00000000 00010100 0014FFFF 1F731F0F [..............s.]
076B3C070 00001F73 1F0F0001 00000000 00000000 [s...............]
076B3C080 00000000 00000000 00000000 00000000 [................]
Repeat 494 times
076B3DF70 2C000000 C1020200 41410A02 41414141 [...,......AAAAAA]
076B3DF80 41414141 0202002C 420902C1 42424242 [AAAA,......BBBBB]
076B3DF90 42424242 0202002C 430802C1 43434343 [BBBB,......CCCCC]
076B3DFA0 2C434343 C1020200 44440702 44444444 [CCC,......DDDDDD]
076B3DFB0 02012C44 0602C102 45454545 002C4545 [D,......EEEEEE,.]
076B3DFC0 02C10202 44444407 44444444 0202002C [.....DDDDDDD,...]
076B3DFD0 430802C1 43434343 2C434343 C1020200 [...CCCCCCCC,....]
076B3DFE0 42420902 42424242 2C424242 C1020200 [..BBBBBBBBB,....]
076B3DFF0 41410A02 41414141 41414141 C5210600 [..AAAAAAAAAA..!.]
Block header dump: 0x0100020c
Object id on Block? Y
seg/obj: 0x1619d csc: 0x03.377ac4f1 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.018.000052d1 0x00c001b1.0f3b.07 C--- 0 scn 0x0003.377ac304
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x0100020c
data_block_dump,data header at 0x76b3c064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x76b3c064
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f0f
avsp=0x1f73
tosp=0x1f73
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f0f
block_row_dump:
tab 0, row 0, @0x1f0f --//这个偏移与第1次插入的问题不同.
tl: 17 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 02
col 1: [10] 41 41 41 41 41 41 41 41 41 41
end_of_block_dump
4.换一种方式:
$ cat b5.txt
alter system flush buffer_cache;
alter system flush buffer_cache;
@ 10046on 12
select * from t as of scn 13815694556 where id=1;
@ 10046off
@ pp
SCOTT@book> @ b5.txt
System altered.
System altered.
old 1: alter session set events '10046 trace name context forever, level &1'
new 1: alter session set events '10046 trace name context forever, level 12'
Session altered.
ID NAME
---------- --------------------
1 AAAAAAAAAA
Session altered.
TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_39134.trc
$ grep -i "nam='db file" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_39134.trc
WAIT #140406431959032: nam='db file scattered read' ela= 45 file#=2 block#=416 blocks=8 obj#=272 tim=1544058316954648
WAIT #140406431959032: nam='db file scattered read' ela= 48 file#=2 block#=74816 blocks=8 obj#=272 tim=1544058316954781
WAIT #140406431959032: nam='db file scattered read' ela= 52 file#=2 block#=71376 blocks=8 obj#=270 tim=1544058316954972
--//上面是读 以下部分访问的数据块与前面测试一直.
WAIT #140406431960096: nam='db file sequential read' ela= 11 file#=4 block#=522 blocks=1 obj#=90525 tim=1544058316956075
WAIT #140406431960096: nam='db file scattered read' ela= 38 file#=4 block#=523 blocks=5 obj#=90525 tim=1544058316956220
WAIT #140406431960096: nam='db file sequential read' ela= 17 file#=3 block#=179 blocks=1 obj#=0 tim=1544058316956314
WAIT #140406431960096: nam='db file sequential read' ela= 10 file#=3 block#=1010 blocks=1 obj#=0 tim=1544058316956431
WAIT #140406431960096: nam='db file sequential read' ela= 10 file#=3 block#=1217 blocks=1 obj#=0 tim=1544058316956497
WAIT #140406431960096: nam='db file sequential read' ela= 9 file#=3 block#=1356 blocks=1 obj#=0 tim=1544058316956558
--//前面的3条访问的数据块是:
SCOTT@book> @ which_obj 2 416
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ --------------------- -------------- ------------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
SYS SMON_SCN_TIME_SCN_IDX INDEX SYSAUX 0 2 416 65536 8 2
SCOTT@book> @ which_obj 2 74816
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ --------------------- -------------- ------------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
SYS SMON_SCN_TIME_SCN_IDX INDEX SYSAUX 1 2 74816 65536 8 2
SCOTT@book> @ which_obj 2 71376
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ --------------------- -------------- ------------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
SYS SMON_SCN_TO_TIME_AUX CLUSTER SYSAUX 17 2 71296 1048576 128 2
--//如果建立唯一索引在id字段,你再重复前面的测试,你会发现结果不同,大家可以参考我以前写的:
--//链接:http://blog.itpub.net/267265/viewspace-772101/
--//附上测试脚本:
$ cat xid.sql
column XIDUSN_XIDSLOT_XIDSQN format a30
select dbms_transaction.local_transaction_id() XIDUSN_XIDSLOT_XIDSQN from dual ;
--select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC, UBASQN,STATUS,used_ublk,USED_UREC,xid,ADDR from v$transaction;
SELECT 'ALTER SYSTEM DUMP UNDO BLOCK '''
|| (SELECT name
FROM sys.undo$
WHERE us# = xidusn)
|| ''' XID '
|| xidusn
|| ' '
|| xidslot
|| ' '
|| xidsqn
|| ';'
|| CHR (10)
|| 'ALTER SYSTEM DUMP UNDO HEADER '''
|| (SELECT name
FROM sys.undo$
WHERE us# = xidusn)
|| ''''
|| ';'
|| CHR (10)
|| 'ALTER SYSTEM DUMP DATAFILE '
|| UBAFIL
|| ' BLOCK '
|| UBABLK
|| ';'
c70
,XIDUSN
,XIDSLOT
,XIDSQN
,UBAFIL
,UBABLK
,UBASQN
,UBAREC
,STATUS
,used_ublk
,USED_UREC
,xid
,ADDR
,START_DATE
,flag
FROM v$transaction;
$ cat bbvi.sql
column bvi_command format a100
select 'bvi -b '|| &2*BLOCK_SIZE||' -s '||block_size||' '||NAME bvi_command from v$datafile where file#=&1
union all
select 'xxd -c16 -g 2 -s '|| &2*BLOCK_SIZE||' -l '||block_size||' '||NAME bvi_command from v$datafile where file#=&1
union all
select 'dd if='||name||' bs='||block_size||' skip='||&2||' count=1 of='||&1||'_'||&2||'.dd conv=notrunc 2>/dev/null' bvi_command from v$datafile where file#=&1
union all
select 'od -j '||&2*BLOCK_SIZE||' -N '||block_size||' -t x1 -v '||name bvi_command from v$datafile where file#=&1
union all
select 'hexdump -s '||&2*BLOCK_SIZE||' -n '||block_size||' -C -v '||name bvi_command from v$datafile where file#=&1
union all
select 'alter system dump datafile '||''''||name||''''||' block '||&2||';' from v$datafile where file#=&1
union all
select '' bvi_command from dual
union all
select 'alter session set events ''immediate trace name set_tsn_p1 level '|| to_char(ts#+1)||''';' bvi_command from sys.ts$
where name in (select TABLESPACE_NAME from v$datafile_header where FILE# = &&1)
union all
select 'alter session set events ''immediate trace name buffer level '||dbms_utility.make_data_block_address(&&1,&&2)||''';' bvi_command from dual;