欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

[20181206]关于一致性读取3.txt

程序员文章站 2023-12-21 10:53:22
[20181206]关于一致性读取3.txt--//简单演示一致性读取以及如何读取undo重构数据块的.我不想转储对应的undo块,解析那些复杂的过程.1.环境:SCOTT@book> @ ver1PORT_STRING VERSION BANNER x86_64/Linux 2.4.xx 11.2 ......

[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;
      

上一篇:

下一篇: