QZone Editor
Statistics
----------------------------------------------------------
165 recursive calls
0 db block gets
25 consistent gets
0 physical reads
0 redo size
621 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
5 rows processed
SQL> alter system dump datafile 4 block 56;
在ORACLE的学习和工作中，我们和UNDO接触的非常频繁，所以对她我们要多花时间去研究，今天先个简单的实验，并记录之！
1：实验环境：ORACLE 10R2版本，实验用户：SCOTT,实验表：SALGRADE
2：用到的技术：TRC，NUMBER型的进制换算
3：实验过程：
3.1 update SALGRADE表，并查看执行计划
SQL> select * from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL>set autotrace on
SQL>select * from salgrade;
Execution Plan
----------------------------------------------------------
Plan hash value: 2489195056
----------------------------------------------------------
Plan hash value: 2489195056
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 50 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| SALGRADE | 5 | 50 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 50 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| SALGRADE | 5 | 50 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
165 recursive calls
0 db block gets
25 consistent gets
0 physical reads
0 redo size
621 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
5 rows processed
SQL> update salgrade set losal=1000 where grade=1;
1 row updated
注意这里我们暂时不提交改事务，接下来获取该事务信息
3.2，获取事务信息
SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;
XIDUSN XIDSLOT XIDSQN
UBABLK
UBAFIL UBAREC
---------- ---------- ----------- --------- ---------- ----------
4 41 227 1885 2 16
---------- ---------- ----------- --------- ---------- ----------
4 41 227 1885 2 16
我们可以发现该事务在4号回滚段上面（XIDUSN),事务槽为41(XIDSLOT),数据文件为2（UBAFIL），数据块为1885（UBABLK)，另外可以从V$ROLLSTAT视图中得到验证
SQL> select usn,writes,rssize,xacts,hwmsize,shrinks,wraps from v$rollstat;
USN WRITES RSSIZE XACTS HWMSIZE SHRINKS WRAPS
---------- ---------- ---------- ---------- ---------- ---------- ----------
0 7620 385024 0 385024 0 0
1 864622 188416 0 516096 1 15
2 1680594 319488 0 909312 2 32
3 1430048 2088960 0 2088960 0 16
4 1397364 2088960 1 2088960 0 16
5 1775740 2088960 0 2088960 0 16
6 1238998 2088960 0 2088960 0 16
7 1124482 253952 0 909312 1 20
8 6811862 1236992 0 6283264 2 33
9 1569972 253952 0 2088960 4 29
10 1098782 188416 0 2088960 3 20
---------- ---------- ---------- ---------- ---------- ---------- ----------
0 7620 385024 0 385024 0 0
1 864622 188416 0 516096 1 15
2 1680594 319488 0 909312 2 32
3 1430048 2088960 0 2088960 0 16
4 1397364 2088960 1 2088960 0 16
5 1775740 2088960 0 2088960 0 16
6 1238998 2088960 0 2088960 0 16
7 1124482 253952 0 909312 1 20
8 6811862 1236992 0 6283264 2 33
9 1569972 253952 0 2088960 4 29
10 1098782 188416 0 2088960 3 20
11 rows selected.
3.2 转储回滚段信息
SQL> select * from v$rollname where usn=4;
USN NAME
---------- ------------------------------
4 _SYSSMU4$
---------- ------------------------------
4 _SYSSMU4$
SQL> alter system dump undo header '_SYSSMU4$';
System altered
获取TRC文件
select a.value||b.symbol||c.instance_name||'_ora_'||d.spid||'.trc' trace_file
from
(select value from v$parameter where name='user_dump_dest') a,
(select substr(value,-6,1) symbol from v$parameter where name='user_dump_dest') b,
(select instance_name from v$instance) c,
(select spid from v$session s,v$process p,v$mystat m where s.paddr=p.addr
and s.sid=m.sid and m.statistic#=0) d
/
from
(select value from v$parameter where name='user_dump_dest') a,
(select substr(value,-6,1) symbol from v$parameter where name='user_dump_dest') b,
(select instance_name from v$instance) c,
(select spid from v$session s,v$process p,v$mystat m where s.paddr=p.addr
and s.sid=m.sid and m.statistic#=0) d
/
------------------------------
/home/oracle/admin/oratest/udump/oratest_ora_7108.trc
localhost-> more /home/oracle/admin/oratest/udump/oratest_ora_7108.trc
/home/oracle/admin/oratest/udump/oratest_ora_7108.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /home/oracle/product/10.2.0/db_1
System name: Linux
Node name: localhost.localdomain
Release: 2.6.9-42.0.0.0.1.ELhugemem
Version: #1 SMP Sun Oct 15 14:06:18 PDT 2006
Machine: i686
Instance name: oratest
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 7108, p_w_picpath: oracle@localhost.localdomain (TNS V1-V3)
********************************************************************************
Undo Segment: _SYSSMU4$ (4)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 17 #blocks: 255
localhost->
localhost-> more /home/oracle/admin/oratest/udump/oratest_ora_7108.trc
/home/oracle/admin/oratest/udump/oratest_ora_7108.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /home/oracle/product/10.2.0/db_1
System name: Linux
Node name: localhost.localdomain
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub
stmt_num cmt
-----------------------------------------------------------------------------------------
-------
0x29 10 0x80 0x00e3 0x000e 0x0000.000839fb 0x0080075d 0x0000.000.00000000 0x0
0000001 0x00000000 0
0x2a 9 0x00 0x00e2 0x0026 0x0000.00083999 0x00800746 0x0000.000.00000000 0x0
0000001 0x00000000 1301748946
0x2b 9 0x00 0x00e2 0x002a 0x0000.00083987 0x00800745 0x0000.000.00000000 0x0
0000001 0x00000000 1301748946
0x2c 9 0x00 0x00e2 0x001f 0x0000.00083a2b 0x0080074b 0x0000.000.00000000 0x0
0000003 0x00000000 1301749223
0x2d 9 0x00 0x00e2 0x002c 0x0000.00083a2a 0x00800749 0x0000.000.00000000 0x0
0000003 0x00000000 1301749223
0x2e 9 0x00 0x00e2 0x0000 0x0000.00083a3a 0x0080075e 0x0000.000.00000000 0x0
0000007 0x00000000 1301749234
0x2f 9 0x00 0x00e2 0x0002 0x0000.00083a2f 0x00800751 0x0000.000.00000000 0x0
0000003 0x00000000 1301749225
我们可以看到事物状态为10，说明是活动的事务，事务槽位0x29
/home/oracle/admin/oratest/udump/oratest_ora_7108.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /home/oracle/product/10.2.0/db_1
System name: Linux
Node name: localhost.localdomain
Release: 2.6.9-42.0.0.0.1.ELhugemem
Version: #1 SMP Sun Oct 15 14:06:18 PDT 2006
Machine: i686
Instance name: oratest
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 7108, p_w_picpath: oracle@localhost.localdomain (TNS V1-V3)
********************************************************************************
Undo Segment: _SYSSMU4$ (4)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 17 #blocks: 255
localhost->
localhost-> more /home/oracle/admin/oratest/udump/oratest_ora_7108.trc
/home/oracle/admin/oratest/udump/oratest_ora_7108.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /home/oracle/product/10.2.0/db_1
System name: Linux
Node name: localhost.localdomain
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub
stmt_num cmt
-----------------------------------------------------------------------------------------
-------
0x29 10 0x80 0x00e3 0x000e 0x0000.000839fb 0x0080075d 0x0000.000.00000000 0x0
0000001 0x00000000 0
0x2a 9 0x00 0x00e2 0x0026 0x0000.00083999 0x00800746 0x0000.000.00000000 0x0
0000001 0x00000000 1301748946
0x2b 9 0x00 0x00e2 0x002a 0x0000.00083987 0x00800745 0x0000.000.00000000 0x0
0000001 0x00000000 1301748946
0x2c 9 0x00 0x00e2 0x001f 0x0000.00083a2b 0x0080074b 0x0000.000.00000000 0x0
0000003 0x00000000 1301749223
0x2d 9 0x00 0x00e2 0x002c 0x0000.00083a2a 0x00800749 0x0000.000.00000000 0x0
0000003 0x00000000 1301749223
0x2e 9 0x00 0x00e2 0x0000 0x0000.00083a3a 0x0080075e 0x0000.000.00000000 0x0
0000007 0x00000000 1301749234
0x2f 9 0x00 0x00e2 0x0002 0x0000.00083a2f 0x00800751 0x0000.000.00000000 0x0
0000003 0x00000000 1301749225
我们可以看到事物状态为10，说明是活动的事务，事务槽位0x29
3.3 转储镜像前信息
注意我们先来研究下DBA（data block address)，这个指向事物前地址的镜像（0x0080075d），我们进行换算下这个DBA的地址， 0x0080075d 转换为二进制
100000000000011101011101，我们先计算出数据块的位置，从后面往前数22为（
00000000000111010），然后转换为十进制58，剩下的为数据文件，占10位，不够位数在前面补0,（注意是前面），0000000010,转为十进制为2，我们转储事务块的信息（注意是块上面有事务的那个块，前面我们查出来的数据文件为2，块为1885，不是数据文件为2，块为58这个块）,为了分析更清楚，我们再更新2条信息：
SQL> update salgrade set losal=1100 where grade=2;
1 row updated.
SQL> update salgrade set losal=1200 where grade=3;
1 row updated
SQL> alter system dump datafile 2 block 1885;
System altered
SQL> @gettrac.sql
TRACE_FILE
---------------------------------
---------------------------------
/home/oracle/admin/oratest/udump/oratest_ora_7307.trc
3.4 分析TRC文件
localhost-> more /home/oracle/admin/oratest/udump/oratest_ora_7307.trc
/home/oracle/admin/oratest/udump/oratest_ora_7307.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /home/oracle/product/10.2.0/db_1
System name: Linux
Node name: localhost.localdomain
Release: 2.6.9-42.0.0.0.1.ELhugemem
Version: #1 SMP Sun Oct 15 14:06:18 PDT 2006
Machine: i686
Instance name: oratest
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 7307, p_w_picpath: oracle@localhost.localdomain (TNS V1-V3)
/home/oracle/admin/oratest/udump/oratest_ora_7307.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /home/oracle/product/10.2.0/db_1
System name: Linux
Node name: localhost.localdomain
Release: 2.6.9-42.0.0.0.1.ELhugemem
Version: #1 SMP Sun Oct 15 14:06:18 PDT 2006
Machine: i686
Instance name: oratest
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 7307, p_w_picpath: oracle@localhost.localdomain (TNS V1-V3)
*** 2011-04-02 21:36:10.009
*** ACTION NAME:() 2011-04-02 21:36:09.979
*** MODULE NAME:(SQL*Plus) 2011-04-02 21:36:09.978
*** SERVICE NAME:(SYS$USERS) 2011-04-02 21:36:09.978
*** SESSION ID:(140.89) 2011-04-02 21:36:09.977
Start dump data blocks tsn: 1 file#: 2 minblk 1885 maxblk 1885
buffer tsn: 1 rdba: 0x0080075d (2/1885)
scn: 0x0000.00084049 seq: 0x01 flg: 0x00 tail: 0x40490201
frmt: 0x02 chkval: 0x0000 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CC0F600 to 0x0CC11600
CC0F600 0000A202 0080075D 00084049 00010000 [....]...I@......]
*** ACTION NAME:() 2011-04-02 21:36:09.979
*** MODULE NAME:(SQL*Plus) 2011-04-02 21:36:09.978
*** SERVICE NAME:(SYS$USERS) 2011-04-02 21:36:09.978
*** SESSION ID:(140.89) 2011-04-02 21:36:09.977
Start dump data blocks tsn: 1 file#: 2 minblk 1885 maxblk 1885
buffer tsn: 1 rdba: 0x0080075d (2/1885)
scn: 0x0000.00084049 seq: 0x01 flg: 0x00 tail: 0x40490201
frmt: 0x02 chkval: 0x0000 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CC0F600 to 0x0CC11600
CC0F600 0000A202 0080075D 00084049 00010000 [....]...I@......]
.........................
UNDO BLK:
xid: 0x0004.029.000000e3 seq: 0xa9 cnt: 0x12 irb: 0x12 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f70 0x02 0x1f1c 0x03 0x1e70 0x04 0x1df4 0x05 0x1d88
0x06 0x1d1c 0x07 0x1c70 0x08 0x1c04 0x09 0x1b98 0x0a 0x1aec
0x0b 0x1a44 0x0c 0x199c 0x0d 0x18f0 0x0e 0x1844 0x0f 0x17d8
0x10 0x173c 0x11 0x16e0 0x12 0x1684
.................
* Rec #0x9 slt: 0x23 objn: 517(0x00000205) objd: 517 tblspc: 0(0x00000000)
* Layer: 11 (Row) opc: 1 rci 0x08
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x0080075d.00a9.08
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x0040100d hdba: 0x00401001
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 45(0x2d) flag: 0x2c lock: 0 ckix: 254
ncol: 9 nnew: 7 size: 0
Vector content:
col 2: [ 1] 80
col 3: [ 2] c1 08
col 4: [ 1] 80
col 5: [ 1] 80
col 6: [ 1] 80
col 7: [ 1] 80
col 8: [ 7] 78 6f 04 02 15 15 25
*-----------------------------
* Rec #0xa slt: 0x1e objn: 517(0x00000205) objd: 517 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: 0x0080075d.00a9.07 ctl max scn: 0x0000.000837ed prv tx scn: 0x0000.00083814
txn start scn: scn: 0x0000.00083d3e logon user: 0
prev brb: 8390469 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0008.018.00000116 uba: 0x008000ee.00eb.31
flg: C--- lkc: 0 scn: 0x0000.00083d37
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x0040100d hdba: 0x00401001
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 62(0x3e) flag: 0x2c lock: 0 ckix: 254
ncol: 9 nnew: 7 size: 0
Vector content:
col 2: [ 1] 80
col 3: [ 2] c1 08
col 4: [ 1] 80
col 5: [ 1] 80
col 6: [ 1] 80
col 7: [ 1] 80
col 8: [ 7] 78 6f 04 02 15 15 25
............................
*-----------------------------
* Rec #0x10 slt: 0x29 objn: 51151(0x0000c7cf) objd: 51151 tblspc: 4(0x00000004)
* 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: 0x0080075d.00a9.0e ctl max scn: 0x0000.00083918 prv tx scn: 0x0000.0008393e
txn start scn: scn: 0x0000.00083b17 logon user: 54
prev brb: 8390469 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 10
ncol: 3 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000038 hdba: 0x01000033
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 2] c2 08
*-----------------------------
* Rec #0x11 slt: 0x29 objn: 51151(0x0000c7cf) objd: 51151 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x10
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x0080075d.00a9.10
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01000038 hdba: 0x01000033
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 10
ncol: 3 nnew: 1 size: 1
col 1: [ 3] c2 0d 02
*-----------------------------
* Rec #0x12 slt: 0x29 objn: 51151(0x0000c7cf) objd: 51151 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x11
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x0080075d.00a9.11
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01000038 hdba: 0x01000033
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 10
ncol: 3 nnew: 1 size: 1
col 1: [ 3] c2 0f 02
End dump data blocks tsn: 1 file#: 2 minblk 1885 maxblk 1885
xid: 0x0004.029.000000e3 seq: 0xa9 cnt: 0x12 irb: 0x12 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f70 0x02 0x1f1c 0x03 0x1e70 0x04 0x1df4 0x05 0x1d88
0x06 0x1d1c 0x07 0x1c70 0x08 0x1c04 0x09 0x1b98 0x0a 0x1aec
0x0b 0x1a44 0x0c 0x199c 0x0d 0x18f0 0x0e 0x1844 0x0f 0x17d8
0x10 0x173c 0x11 0x16e0 0x12 0x1684
.................
* Rec #0x9 slt: 0x23 objn: 517(0x00000205) objd: 517 tblspc: 0(0x00000000)
* Layer: 11 (Row) opc: 1 rci 0x08
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x0080075d.00a9.08
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x0040100d hdba: 0x00401001
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 45(0x2d) flag: 0x2c lock: 0 ckix: 254
ncol: 9 nnew: 7 size: 0
Vector content:
col 2: [ 1] 80
col 3: [ 2] c1 08
col 4: [ 1] 80
col 5: [ 1] 80
col 6: [ 1] 80
col 7: [ 1] 80
col 8: [ 7] 78 6f 04 02 15 15 25
*-----------------------------
* Rec #0xa slt: 0x1e objn: 517(0x00000205) objd: 517 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: 0x0080075d.00a9.07 ctl max scn: 0x0000.000837ed prv tx scn: 0x0000.00083814
txn start scn: scn: 0x0000.00083d3e logon user: 0
prev brb: 8390469 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0008.018.00000116 uba: 0x008000ee.00eb.31
flg: C--- lkc: 0 scn: 0x0000.00083d37
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x0040100d hdba: 0x00401001
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 62(0x3e) flag: 0x2c lock: 0 ckix: 254
ncol: 9 nnew: 7 size: 0
Vector content:
col 2: [ 1] 80
col 3: [ 2] c1 08
col 4: [ 1] 80
col 5: [ 1] 80
col 6: [ 1] 80
col 7: [ 1] 80
col 8: [ 7] 78 6f 04 02 15 15 25
............................
*-----------------------------
* Rec #0x10 slt: 0x29 objn: 51151(0x0000c7cf) objd: 51151 tblspc: 4(0x00000004)
* 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: 0x0080075d.00a9.0e ctl max scn: 0x0000.00083918 prv tx scn: 0x0000.0008393e
txn start scn: scn: 0x0000.00083b17 logon user: 54
prev brb: 8390469 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 10
ncol: 3 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000038 hdba: 0x01000033
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 2] c2 08
*-----------------------------
* Rec #0x11 slt: 0x29 objn: 51151(0x0000c7cf) objd: 51151 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x10
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x0080075d.00a9.10
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01000038 hdba: 0x01000033
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 10
ncol: 3 nnew: 1 size: 1
col 1: [ 3] c2 0d 02
*-----------------------------
* Rec #0x12 slt: 0x29 objn: 51151(0x0000c7cf) objd: 51151 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x11
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x0080075d.00a9.11
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01000038 hdba: 0x01000033
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 10
ncol: 3 nnew: 1 size: 1
col 1: [ 3] c2 0f 02
End dump data blocks tsn: 1 file#: 2 minblk 1885 maxblk 1885
我们注意到这个块的信息的:
UNDO BLK:
xid: 0x0004.029.000000e3 seq: 0xa9 cnt: 0x12 irb: 0x12 icl: 0x0 flg: 0x0000
irb指的是回滚段中记录的最近未提交变更开始之处，如果开始回滚，这是起始的搜索点。
xid: 0x0004.029.000000e3 seq: 0xa9 cnt: 0x12 irb: 0x12 icl: 0x0 flg: 0x0000
irb指的是回滚段中记录的最近未提交变更开始之处，如果开始回滚，这是起始的搜索点。
接下来是回滚信息的偏移量，最后一个地址正好0x12 的信息:
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f70 0x02 0x1f1c 0x03 0x1e70 0x04 0x1df4 0x05 0x1d88
0x06 0x1d1c 0x07 0x1c70 0x08 0x1c04 0x09 0x1b98 0x0a 0x1aec
0x0b 0x1a44 0x0c 0x199c 0x0d 0x18f0 0x0e 0x1844 0x0f 0x17d8
0x10 0x173c 0x11 0x16e0 0x12 0x1684
---------------------------------------------------------------------------
0x01 0x1f70 0x02 0x1f1c 0x03 0x1e70 0x04 0x1df4 0x05 0x1d88
0x06 0x1d1c 0x07 0x1c70 0x08 0x1c04 0x09 0x1b98 0x0a 0x1aec
0x0b 0x1a44 0x0c 0x199c 0x0d 0x18f0 0x0e 0x1844 0x0f 0x17d8
0x10 0x173c 0x11 0x16e0 0x12 0x1684
我们查找0x12的信息：
* Rec #0x12 slt: 0x29 objn: 51151(0x0000c7cf) objd: 51151 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x11
* Layer: 11 (Row) opc: 1 rci 0x11
..................
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 10
ncol: 3 nnew: 1 size: 1
col 1: [ 3] c2 0f 02
我们来看看c2 0f 02这个值代表是什么意思:
ncol: 3 nnew: 1 size: 1
col 1: [ 3] c2 0f 02
我们来看看c2 0f 02这个值代表是什么意思:
SQL> select dump(1401,16) from dual;
DUMP(1401,16)
-------------------
Typ=2 Len=3: c2,f,2
-------------------
Typ=2 Len=3: c2,f,2
SQL> update salgrade set losal=1200 where grade=3; 原来的losal值为1401
我们发现原来这个是更新前的的值，我们再看看其他的2个值是不是更新前的值呢？
SQL> select dump(700,16) from dual;
DUMP(700,16)
-----------------
Typ=2 Len=2: c2,8
-----------------
Typ=2 Len=2: c2,8
SQL> update salgrade set losal=1000 where grade=1; 原来的值为losal为700
1 row updated
SQL> select dump(1201,16) from dual;
DUMP(1201,16)
-------------------
Typ=2 Len=3: c2,d,2
-------------------
Typ=2 Len=3: c2,d,2
SQL> update salgrade set losal=1100 where grade=2; 原来的losal为1201
1 row updated
我们发现TRC文件时吻合的，
Rec #0x10 slt: 0x29 另外undo chain 的指针为 rci:0x00,说明是最后一条记录,我们也可以从v$bh视图来查看事务的状态：
select b.segment_name,a.file#,a.dbarfil,a.dbablk,a.class,a.state
from x$bh a,dba_extents b where b.relative_fno=a.dbarfil
and b.block_id <= a.dbablk and b.block_id + b.blocks > a.dbablk
and b.owner='SCOTT'
and b.segment_name='SALGRADE';
from x$bh a,dba_extents b where b.relative_fno=a.dbarfil
and b.block_id <= a.dbablk and b.block_id + b.blocks > a.dbablk
and b.owner='SCOTT'
and b.segment_name='SALGRADE';
SEGMENT_NAME FILE# DBARFIL DBABLK CLASS STATE
------------------------------ ---------- ---------- ---------- ---------- ----------
SALGRADE 4 4 52 1 1
SALGRADE 4 4 54 1 1
SALGRADE 4 4 51 4 1
SALGRADE 4 4 56 1 1
SALGRADE 4 4 56 1 3
SALGRADE 4 4 56 1 3
SALGRADE 4 4 56 1 3
SALGRADE 4 4 53 1 1
SALGRADE 4 4 55 1 1
------------------------------ ---------- ---------- ---------- ---------- ----------
SALGRADE 4 4 52 1 1
SALGRADE 4 4 54 1 1
SALGRADE 4 4 51 4 1
SALGRADE 4 4 56 1 1
SALGRADE 4 4 56 1 3
SALGRADE 4 4 56 1 3
SALGRADE 4 4 56 1 3
SALGRADE 4 4 53 1 1
SALGRADE 4 4 55 1 1
9 rows selected.
我们可以发现块56上面有4个事务，转储块56的信息
SQL> alter system dump datafile 4 block 56;
System altered.
SQL> @gettrac.sql
TRACE_FILE
--------------------------------------------------------------------------------
/home/oracle/admin/oratest/udump/oratest_ora_7762.trc
--------------------------------------------------------------------------------
/home/oracle/admin/oratest/udump/oratest_ora_7762.trc
查看TRC文件
localhost-> more /home/oracle/admin/oratest/udump/oratest_ora_7762.trc
/home/oracle/admin/oratest/udump/oratest_ora_7762.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /home/oracle/product/10.2.0/db_1
System name: Linux
Node name: localhost.localdomain
Release: 2.6.9-42.0.0.0.1.ELhugemem
Version: #1 SMP Sun Oct 15 14:06:18 PDT 2006
Machine: i686
Instance name: oratest
Redo thread mounted by this instance: 1
Oracle process number: 26
Unix process pid: 7762, p_w_picpath: oracle@localhost.localdomain (TNS V1-V3)
/home/oracle/admin/oratest/udump/oratest_ora_7762.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /home/oracle/product/10.2.0/db_1
System name: Linux
Node name: localhost.localdomain
Release: 2.6.9-42.0.0.0.1.ELhugemem
Version: #1 SMP Sun Oct 15 14:06:18 PDT 2006
Machine: i686
Instance name: oratest
Redo thread mounted by this instance: 1
Oracle process number: 26
Unix process pid: 7762, p_w_picpath: oracle@localhost.localdomain (TNS V1-V3)
*** 2011-04-02 23:11:43.104
*** SERVICE NAME:(SYS$USERS) 2011-04-02 23:11:43.080
*** SESSION ID:(145.211) 2011-04-02 23:11:43.079
Start dump data blocks tsn: 4 file#: 4 minblk 56 maxblk 56
buffer tsn: 4 rdba: 0x01000038 (4/56)
scn: 0x0000.00084049 seq: 0x01 flg: 0x04 tail: 0x40490601
frmt: 0x02 chkval: 0x8774 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CC0F600 to 0x0CC11600
CC0F600 0000A206 01000038 00084049 04010000 [....8...I@......]
CC0F610 00008774 00020001 0000C7CF 00083DC9 [t............=..]
CC0F620 1FE80000 00321F02 01000031 001C0009 [......2.1.......]
CC0F630 00000112 00804D5A 002B00BC 00008000 [....ZM....+.....]
CC0F640 0006BFFA 00290004 000000E3 0080075D [......).....]...]
CC0F650 001200A9 00020003 00000000 00000000 [................]
CC0F660 00000000 00050100 001CFFFF 1F3B1F3F [............?.;.]
CC0F670 00001F3D 1F8C0005 1F3F1F4B 1F571F65 [=.......K.?.e.W.]
CC0F680 00000000 00000000 00000000 00000000 [................]
Repeat 497 times
CC115A0 2C000000 C1020302 0DC20204 2C15C202 [...,...........,]
CC115B0 C1020302 0CC20203 2C0FC202 C1020300 [...........,....]
CC115C0 1FC20306 64C20302 03002C64 0305C102 [.......dd,......]
CC115D0 020215C2 002C1FC2 04C10203 020FC203 [......,.........]
CC115E0 2C15C202 C1020300 0DC20303 0FC20202 [...,............]
CC115F0 0203022C C20202C1 0DC2020B 40490601 [,.............I@]
Block header dump: 0x01000038
Object id on Block? Y
seg/obj: 0xc7cf csc: 0x00.83dc9 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000031 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0009.01c.00000112 0x00804d5a.00bc.2b C--- 0 scn 0x0000.0006bffa
0x02 0x0004.029.000000e3 0x0080075d.00a9.12 ---- 3 fsc 0x0002.00000000
data_block_dump,data header at 0xcc0f664
===============
tsiz: 0x1f98
hsiz: 0x1c
pbl: 0x0cc0f664
bdba: 0x01000038
76543210
flag=--------
ntab=1
nrow=5
frre=-1
fsbo=0x1c
fseo=0x1f3f
avsp=0x1f3b
tosp=0x1f3d
0xe:pti[0] nrow=5 offs=0
0x12:pri[0] offs=0x1f8c
0x14:pri[1] offs=0x1f4b
0x16:pri[2] offs=0x1f3f
0x18:pri[3] offs=0x1f65
0x1a:pri[4] offs=0x1f57
block_row_dump:
tab 0, row 0, @0x1f8c
tl: 12 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 02
col 1: [ 2] c2 0b
col 2: [ 2] c2 0d
tab 0, row 1, @0x1f4b
tl: 12 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 03
col 1: [ 2] c2 0c
col 2: [ 2] c2 0f
tab 0, row 2, @0x1f3f
tl: 12 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 04
col 1: [ 2] c2 0d
col 2: [ 2] c2 15
tab 0, row 3, @0x1f65
tl: 13 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 05
col 1: [ 3] c2 15 02
col 2: [ 2] c2 1f
tab 0, row 4, @0x1f57
tl: 14 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 06
col 1: [ 3] c2 1f 02
col 2: [ 3] c2 64 64
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 56 maxblk 56
*** SERVICE NAME:(SYS$USERS) 2011-04-02 23:11:43.080
*** SESSION ID:(145.211) 2011-04-02 23:11:43.079
Start dump data blocks tsn: 4 file#: 4 minblk 56 maxblk 56
buffer tsn: 4 rdba: 0x01000038 (4/56)
scn: 0x0000.00084049 seq: 0x01 flg: 0x04 tail: 0x40490601
frmt: 0x02 chkval: 0x8774 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CC0F600 to 0x0CC11600
CC0F600 0000A206 01000038 00084049 04010000 [....8...I@......]
CC0F610 00008774 00020001 0000C7CF 00083DC9 [t............=..]
CC0F620 1FE80000 00321F02 01000031 001C0009 [......2.1.......]
CC0F630 00000112 00804D5A 002B00BC 00008000 [....ZM....+.....]
CC0F640 0006BFFA 00290004 000000E3 0080075D [......).....]...]
CC0F650 001200A9 00020003 00000000 00000000 [................]
CC0F660 00000000 00050100 001CFFFF 1F3B1F3F [............?.;.]
CC0F670 00001F3D 1F8C0005 1F3F1F4B 1F571F65 [=.......K.?.e.W.]
CC0F680 00000000 00000000 00000000 00000000 [................]
Repeat 497 times
CC115A0 2C000000 C1020302 0DC20204 2C15C202 [...,...........,]
CC115B0 C1020302 0CC20203 2C0FC202 C1020300 [...........,....]
CC115C0 1FC20306 64C20302 03002C64 0305C102 [.......dd,......]
CC115D0 020215C2 002C1FC2 04C10203 020FC203 [......,.........]
CC115E0 2C15C202 C1020300 0DC20303 0FC20202 [...,............]
CC115F0 0203022C C20202C1 0DC2020B 40490601 [,.............I@]
Block header dump: 0x01000038
Object id on Block? Y
seg/obj: 0xc7cf csc: 0x00.83dc9 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000031 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0009.01c.00000112 0x00804d5a.00bc.2b C--- 0 scn 0x0000.0006bffa
0x02 0x0004.029.000000e3 0x0080075d.00a9.12 ---- 3 fsc 0x0002.00000000
data_block_dump,data header at 0xcc0f664
===============
tsiz: 0x1f98
hsiz: 0x1c
pbl: 0x0cc0f664
bdba: 0x01000038
76543210
flag=--------
ntab=1
nrow=5
frre=-1
fsbo=0x1c
fseo=0x1f3f
avsp=0x1f3b
tosp=0x1f3d
0xe:pti[0] nrow=5 offs=0
0x12:pri[0] offs=0x1f8c
0x14:pri[1] offs=0x1f4b
0x16:pri[2] offs=0x1f3f
0x18:pri[3] offs=0x1f65
0x1a:pri[4] offs=0x1f57
block_row_dump:
tab 0, row 0, @0x1f8c
tl: 12 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 02
col 1: [ 2] c2 0b
col 2: [ 2] c2 0d
tab 0, row 1, @0x1f4b
tl: 12 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 03
col 1: [ 2] c2 0c
col 2: [ 2] c2 0f
tab 0, row 2, @0x1f3f
tl: 12 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 04
col 1: [ 2] c2 0d
col 2: [ 2] c2 15
tab 0, row 3, @0x1f65
tl: 13 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 05
col 1: [ 3] c2 15 02
col 2: [ 2] c2 1f
tab 0, row 4, @0x1f57
tl: 14 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 06
col 1: [ 3] c2 1f 02
col 2: [ 3] c2 64 64
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 56 maxblk 56
这里我们来分析这个块，我们先来看看ITL的信息（Interested Transaction List),要想修改修改数据，首先先要获得一个事务槽，事务槽主要包括，xid（Transaction ID）、Uba（Undo Block Address）和Lck（Lock Status），属于我们可以归结为：
XID=Undo.Segment.Number+Transaction.Table.Slot.Number+Wrap ,
所以，看到ITL2（0x02)存在活动的事务，我们来分析下这个事务槽，xid=
0x0004.029.000000e3，
该事务指向4号回滚段，slot为029转化为十进制刚好为41，wrap为000000e3刚好是上面的dump事务，
index state cflags wrap# uel scn dba parent-xid nub
stmt_num cmt
-----------------------------------------------------------------------------------------
-------
0x29 10 0x80 0x00e3 0x000e 0x0000.000839fb 0x0080075d 0x0000.000.00000000 0x0
0000001 0x00000000 0
同时我们还可以看到，该块上存在指向会滚段的事务信息，这个事务地址就是UBA（UNDO BLOCK ADDRESS)0x0080075d.00a9.12，我们来分析该UBA代表的意思
stmt_num cmt
-----------------------------------------------------------------------------------------
-------
0x29 10 0x80 0x00e3 0x000e 0x0000.000839fb 0x0080075d 0x0000.000.00000000 0x0
0000001 0x00000000 0
同时我们还可以看到，该块上存在指向会滚段的事务信息，这个事务地址就是UBA（UNDO BLOCK ADDRESS)0x0080075d.00a9.12，我们来分析该UBA代表的意思
0x0080075d 刚好代表的是前镜像的地址，seq:00a9的顺序号，12是UNDO记录开始的地址（irb)的信息，UBA的内容和UNDO信息吻合：
UNDO BLK:
xid: 0x0004.029.000000e3 seq: 0xa9 cnt: 0x12 irb: 0x12 icl: 0x0 flg: 0x0000
我们继续分析改块的信息，锁定位置lb指向0x02号事务槽，
xid: 0x0004.029.000000e3 seq: 0xa9 cnt: 0x12 irb: 0x12 icl: 0x0 flg: 0x0000
我们继续分析改块的信息，锁定位置lb指向0x02号事务槽，
tl: 12 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 03
col 1: [ 2] c2 0c
col 2: [ 2] c2 0f
tab 0, row 2, @0x1f3f
tl: 12 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 04
col 1: [ 2] c2 0d
col 2: [ 2] c2 15
tab 0, row 3, @0x1f65
tl: 13 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 05
col 1: [ 3] c2 15 02
col 2: [ 2] c2 1f
tab 0, row 4, @0x1f57
tl: 14 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 06
col 1: [ 3] c2 1f 02
col 2: [ 3] c2 64 64
col 0: [ 2] c1 03
col 1: [ 2] c2 0c
col 2: [ 2] c2 0f
tab 0, row 2, @0x1f3f
tl: 12 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 04
col 1: [ 2] c2 0d
col 2: [ 2] c2 15
tab 0, row 3, @0x1f65
tl: 13 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 05
col 1: [ 3] c2 15 02
col 2: [ 2] c2 1f
tab 0, row 4, @0x1f57
tl: 14 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 06
col 1: [ 3] c2 1f 02
col 2: [ 3] c2 64 64
到此一个完整的事务过程给分解了，总结下事务的过程：
⑴ 当一个事务开始时，需要在回滚段事务表上分配一个事务槽。
⑵ 在数据块头部获取一个ITL事务槽，该事务槽指向回滚段头的事务槽。
⑶ 在修改数据之前，需要记录前镜像信息，这个信息以UNDO RECORD的形式存储在回滚段中，回滚段头事务槽指向该记录。
⑷ 锁定修改行，修改行锁定位指向ITL事务槽。
⑸ 数据修改可以进行。
⑵ 在数据块头部获取一个ITL事务槽，该事务槽指向回滚段头的事务槽。
⑶ 在修改数据之前，需要记录前镜像信息，这个信息以UNDO RECORD的形式存储在回滚段中，回滚段头事务槽指向该记录。
⑷ 锁定修改行，修改行锁定位指向ITL事务槽。
⑸ 数据修改可以进行。
转载于:https://blog.51cto.com/invan/534593
933
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
