2017.03.19的实验探究,这里补上
实验目的:
1.找到事务修改的数据行所在的文件号、数据块
2.找到事务所用的回滚段段名
3.找到undo块(文件号、数据块) ,有两种方式找到undo,验证两个方式的结果是否一致
4.分解Xid、Uba的含义
环境说明:
1.操作系统版本
- [oracle@oracle ~]$ uname -a
- Linux oracle.example.com 2.6.32-431.el6.x86_64 #1 SMP Sun Nov 10 22:19:54 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
- [oracle@oracle ~]$ lsb_release -a
- LSB Version: :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
- Distributor ID: RedHatEnterpriseServer
- Description: Red Hat Enterprise Linux Server release 6.5 (Santiago)
- Release: 6.5
- Codename: Santiago
- SYS@proc> select * from v$version where rownum=1;
-
- BANNER
- --------------------------------------------------------------------------------
- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
实验过程:
1.开始一个事务,查询事务相关信息。
- SYS@prod> select * from t1;
-
- ID NAME
- ---------- ----------
- 1 aaa
- 2 bbb
- 3 ccc
- 4 ddd
- 5 eee
-
- SYS@prod> update t1 set id=1 where id=1;
-
- 1 row updated.
-
- SYS@prod> select xidusn,xidslot,xidsqn,ubafil,ubablk from v$transaction;
-
- XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK
- ---------- ---------- ---------- ---------- ----------
- 6 41 7754 2 52665 --事务前镜像所在undo块是2号文件的52665块
- SYS@prod> select * from v$rollname where usn=6;
-
- USN NAME
- ---------- ------------------------------
- 6 _SYSSMU6$(undo段名)
-
-
- SYS@prod> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,t1.* from t1 where id=1;
-
- FILE# BLOCK# ID NAME
- ---------- ---------- ---------- ----------
- 1 60578 1 aaa
-
-
- SYS@prod> alter system dump undo header '_SYSSMU6$';
-
- System altered.
- [oracle@oracle udump]$ ll
- total 16
- -rw-r----- 1 oracle oinstall 16159 Mar 16 19:38 prod_ora_4236.trc
- [oracle@oracle udump]$ mv prod_ora_4236.trc undo_header.trc
- [oracle@oracle udump]$ pwd
- /u01/app/oracle/admin/prod/udump
-
- SYS@prod> alter system dump datafile 1 block 60578;
-
- System altered.
- [oracle@oracle udump]$ ll
- total 20
- -rw-r----- 1 oracle oinstall 3277 Mar 16 19:43 data_block.trc
- -rw-r----- 1 oracle oinstall 16159 Mar 16 19:38 undo_header.trc
- Itl Xid Uba Flag Lck Scn/Fsc
- 0x01 0x0004.024.00001e21 0x0080cd28.0192.2d C--- 0 scn 0x0000.016938ac
- 0x02 0x0006.029.00001e4a 0x0080cdb9.01f7.03 ---- 1 fsc 0x0000.00000000
- 0x03 0x000a.00b.00001e16 0x0080c822.023a.15 C--- 0 scn 0x0000.016938a1
-
- data_block_dump,data header at 0xdff1474
- ===============
- ...省略部分内容...
- block_row_dump:
- tab 0, row 0, @0x1f7e
- tl: 10 fb: --H-FL-- lb: 0x2 cc: 2 --表示事务为0x02
- col 0: [ 2] c1 02
- col 1: [ 3] 61 61 61 --对比下边第5步骤
- tab 0, row 1, @0x1f74
- tl: 10 fb: --H-FL-- lb: 0x0 cc: 2
- col 0: [ 2] c1 03
- col 1: [ 3] 62 62 62
- tab 0, row 2, @0x1f6a
- tl: 10 fb: --H-FL-- lb: 0x0 cc: 2
- col 0: [ 2] c1 04
- col 1: [ 3] 63 63 63
- tab 0, row 3, @0x1f60
- tl: 10 fb: --H-FL-- lb: 0x0 cc: 2
- col 0: [ 2] c1 05
- col 1: [ 3] 64 64 64
- tab 0, row 4, @0x1f56
- tl: 10 fb: --H-FL-- lb: 0x0 cc: 2
- col 0: [ 2] c1 06
- col 1: [ 3] 65 65 65
- end_of_block_dump
- End dump data blocks tsn: 0 file#: 1 minblk 60578 maxblk 60578
4.验证Uba第一部分得到的Undo是否和v$transaction.ubablk、v$transaction.ubafil一致
- SYS@prod> select to_number('0080cdb9','xxxxxxxx') from dual;
-
- TO_NUMBER('0080CDB9','XXXXXXXX')
- --------------------------------
- 8441273
-
- SYS@prod> select dbms_utility.data_block_address_file(8441273) from dual;
-
- DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(8441273)
- ---------------------------------------------
- 2
-
- SYS@prod> select dbms_utility.data_block_address_block(8441273) from dual;
-
- DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(8441273)
- ----------------------------------------------
- 52665
- 对比:
- SYS@prod> select xidusn,xidslot,xidsqn,ubafil,ubablk from v$transaction;
-
- XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK
- ---------- ---------- ---------- ---------- ----------
- 6 41 7754 2 52665
- SYS@proc> select dump(1,16),dump('aaa',16) from dual;
-
- DUMP(1,16) DUMP('AAA',16)
- ----------------- ----------------------
- Typ=2 Len=2: c1,2 Typ=96 Len=3: 61,61,61
6.对事务所在undo块做dump操作
- SYS@prod> alter system dump datafile 2 block 52665;
-
- System altered.
-
- [oracle@oracle udump]$ mv prod_ora_4317.trc undo_block.trc
- [oracle@oracle udump]$ ll
- total 28
- -rw-r----- 1 oracle oinstall 3277 Mar 16 19:43 data_block.trc
- -rw-r----- 1 oracle oinstall 5490 Mar 16 19:59 undo_block.trc
- -rw-r----- 1 oracle oinstall 16159 Mar 16 19:38 undo_header.trc
- *-----------------------------
- * Rec #0x3 slt: 0x29 objn: 52640(0x0000cda0) objd: 52640 tblspc: 0(0x00000000)
- * Layer: 11 (Row) opc: 1 rci 0x00
- Undo type: Regular undo Begin trans Last buffer split: No
- Temp Object: No
- Tablespace Undo: No
- rdba: 0x00000000
- *-----------------------------
- uba: 0x0080cdb8.01f7.08 ctl max scn: 0x0000.01692b12 prv tx scn: 0x0000.01692ce2
- txn start scn: scn: 0x0000.0169351b logon user: 0
- prev brb: 8441256 prev bcl: 0
- KDO undo record:
- KTB Redo
- op: 0x04 ver: 0x01
- op: L itl: xid: 0x0005.004.000009f9 uba: 0x00806335.0159.24
- flg: C--- lkc: 0 scn: 0x0000.01693517
- Array Update of 1 rows:
- tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 12
- ncol: 2 nnew: 1 size: 0
- KDO Op code: 21 row dependencies Disabled
- xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x0040eca2 hdba: 0x0040eca1
- itli: 2 ispac: 0 maxfr: 4863
- vect = 0
- col 0: [ 2] c1 02(数据前镜像)
Rec #0x3:表示第0x3条undo记录
slt: 0x29:表示slot=0x29=41 与上面从v$transaction中查出来的一致;
-
- Itl Xid Uba Flag Lck Scn/Fsc
- 0x02 0x0006.029.00001e4a 0x0080cdb9.01f7.03 ---- 1 fsc 0x0000.00000000
- Itl Xid Uba Flag Lck Scn/Fsc
Uba中最后的03就是表示的第几条undo记录,与Rec一致。
这个记录可以到文件undo_header.trc中找到事务表中编号为0x03的记录(暂时看不懂)。
ITL中的Xid--0x0006.029.00001e4a分成三部分:
第一个0x0006代表段号,跟下边的xidusn对应,此处值都为6
第二个的029代表事务槽编号为2*16+9=41,与下边的xidslot对应
第三个代表事务表的该事务被覆盖的次数1e4a=7754,与下边sidsqn对应
- SYS@prod> select xidusn,xidslot,xidsqn,ubafil,ubablk from v$transaction;
-
- XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK
- ---------- ---------- ---------- ---------- ----------
- 6 41 7754 2 52665
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30174570/viewspace-2140023/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30174570/viewspace-2140023/