update 一条语句的来龙去脉！
update 一条语句的来龙去脉！
今天是2013-10-24，在今天翻电脑资料的时候看到了如下一个图，随即兴致盎然的在一次研究了一下内部过程。
容易混淆的知识点：
uba=0x00800055.02de.3f
0x00800055代表数据的前镜像
seq：代表是顺序号
3f，是undo记录的开始地址（irb信息）
xid=0x0006.018.000036ce
该事务指向6号回滚段，slot好为0x17,wrap#为36ce
xid=undo.segment.number+transaction.table.slot.number+wrap;
dba：包含这个事务的前镜像的数据块地址：转换为二进制，然后转换为前22为块后10数据文件号
bdba:0x00405c5a 记录了更改数据块的地址。
session 1：
首先切换一下redo，保证新产生的redo条目存在于干净的redo file中：
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 364 52428800 512 1 NO CURRENT 5294094 24-OCT-13 2.8147E+14
2 1 362 52428800 512 1 YES INACTIVE 5293624 24-OCT-13 5294000 24-OCT-13
3 1 363 52428800 512 1 YES ACTIVE 5294000 24-OCT-13 5294094 24-OCT-13
SQL> conn rhys/amy
Connected.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7500 AMY CLERK 7902 17-DEC-80 800 20
7499 amy SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
更新一条数据：
SQL> update emp set ename='Rhys' where empno=7500;
1 row updated.
查看当前用户信息：
SQL> select sid,serial#,username from v$session where username='RHYS';
SID SERIAL# USERNAME
---------- ---------- ------------------------------
1 7 RHYS
SQL>
session 2：
在另一个会话中转储redo file内容：
SQL> set linesize 200
SQL> col name for a60
SQL> alter system dump logfile '/opt/app/oracle/oradata/RHYS/redo01.log';
SQL> col value for a60
SQL> r
1* select * from v$diag_info where name='Default Trace File'
INST_ID NAME VALUE
---------- ------------------------------------------------------------ ------------------------------------------------------------
1 Default Trace File /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_6532.trc
SQL>
session 3：
SQL> select addr,xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,status from v$transaction;
select usn,latch,extents,rssize,xacts,gets,optsize,wraps,status from v$rollstat where xacts>0;
select * from v$rollname;
ADDR XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN STATUS
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------------
000000007BF283F0 20 31 4284 9 37599 91 ACTIVE
SQL> SQL>
USN LATCH EXTENTS RSSIZE XACTS GETS OPTSIZE WRAPS STATUS
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------
20 0 32 20766720 1 27 0 ONLINE
SQL> SQL>
USN NAME
---------- ------------------------------
0 SYSTEM
11 _SYSSMU11_2531470812$
12 _SYSSMU12_3537717698$
13 _SYSSMU13_19894467$
14 _SYSSMU14_4095940644$
15 _SYSSMU15_452080806$
16 _SYSSMU16_3613570610$
17 _SYSSMU17_2475065771$
18 _SYSSMU18_892025711$
19 _SYSSMU19_927439322$
20 _SYSSMU20_487181632$
11 rows selected.
SQL> alter system dump undo header "_SYSSMU20_487181632$";
System altered.
SQL> col value for a50
SQL> select * from v$diag_info where name='Default Trace File';
INST_ID NAME VALUE
---------- ---------------------------------------------------------------- --------------------------------------------------
1 Default Trace File /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_or
a_6535.trc
SQL>
session 4：
SQL> set linesize 200
SQL> col name for a60
SQL> col value for a50
SQL> alter system dump datafile 9 block 37599;
System altered.
SQL> select * from v$diag_info where name='Default Trace File';
INST_ID NAME VALUE
---------- ------------------------------------------------------------ --------------------------------------------------
1 Default Trace File /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_or
a_6561.trc
SQL>
1）、查看redo record信息如下：
REDO RECORD - Thread:1 RBA: 0x00016c.0000000a.0010 LEN: 0x0218 VLD: 0x05
SCN: 0x0000.0050c823 SUBSCN: 1 10/24/2013 18:13:58
(LWN RBA: 0x00016c.0000000a.0010 LEN: 0002 NST: 0001 SCN: 0x0000.0050c823)
这是第一个改变向量：（change #1)
CHANGE #1 TYP:0 CLS:55 AFN:9 DBA:0x02400110 OBJ:4294967295 SCN:0x0000.0050c7ea SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x001f sqn: 0x000010bc flg: 0x000a siz: 160 fbi: 181
uba: 0x024092df.005b.01 pxid: 0x0000.000.00000000
获得信息为afn：相对文件 号为9（undo 表空间数据文件号）；slt为：1f正是undo事务槽号；op：5.2表示（Opcode 2 : Update rollback segment header - KTURDH），
sequence为：10bc也是undo的warp值：0x10bc，uba：前镜像地址为，24092df，顺序号为：005b，irb为：01，这指向了undo中的内容信息如下（在后边有记录）：
###############################################################
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x1f 10 0x80 0x10bc 0x0013 0x0000.0050c823 0x024092df 0x0000.000.00000000 0x00000001 0x00000000 0
UNDO BLK:
xid: 0x0014.01f.000010bc seq: 0x5b cnt: 0x1 irb: 0x1 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f48
################################################################
总结：这是第一步：首先要在undo空间中分配事务槽等信息：
CHANGE #2 TYP:1 CLS:56 AFN:9 DBA:0x024092df OBJ:4294967295 SCN:0x0000.0050c823 SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 160 spc: 0 flg: 0x000a seq: 0x005b rec: 0x01
xid: 0x0014.01f.000010bc
ktubl redo: slt: 31 rci: 0 opc: 11.1 [objn: 89179 objd: 89716 tsn: 8]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x024092de.005b.25
prev ctl max cmt scn: 0x0000.005072ce prev tx cmt scn: 0x0000.0050730f
txn start scn: 0xffff.ffffffff logon user: 92 prev brb: 37786328 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01c0296e hdba: 0x01c0296a
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 8 nnew: 1 size: -1
col 1: [ 3] 41 4d 59
从第二个向量中可以获得的信息有：相对文件号为：9，数据的前镜像地址为dba:数据文件号为9，数据块为：37599，redo的操作码：op：5.1（Opcode 1 : Undo block or undo segment header - KTURDB），可以看到这里的事务号：xid: 0x0014.01f.000010bc获得回滚段为20事务槽位31wrap为10bc，后边记录的： slt: 31 rci: 0 opc: 11.1 [objn: 89179 objd: 89716 tsn: 8]表示：事务槽为31，rci为0说明这是在undo chain中最后一个的记录，对象变化为89179，数据对象变化为89716 tsn：为8，如下查询可知道正是我操作的这张表。另外登录的用户为rhys（通过92可知），然后在记录的就是前字段值为：41 4d 59转储为：AMY这正是原先字段值。如下是获得部分信息的查询操作：
SQL> col object_name for a50
SQL> r
1* select object_name,object_id,data_object_id,object_type from dba_objects where object_id=89179
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
-------------------------------------------------- ---------- -------------- -------------------
EMP 89179 89716 TABLE
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 EXAMPLE YES NO YES
7 STATSPACK YES NO YES
8 RHYS YES NO YES
9 INDEX_TABLESPACE YES NO YES
10 UNDOTBS02 YES NO YES
9 rows selected.
SQL> select username,user_id from dba_users where user_id=92;
USERNAME USER_ID
------------------------------ ----------
RHYS 92
SQL>
SQL> col value for a40
SQL> r
1* select utl_raw.cast_to_varchar2(replace('41 4d 59',' ')) value from dual
VALUE
----------------------------------------
AMY
SQL>
这是第三个改变向量。
CHANGE #3 TYP:2 CLS:1 AFN:7 DBA:0x01c0296e OBJ:89716 SCN:0x0000.00505633 SEQ:1 OP:11.5 ENC:0 RBL:0
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0014.01f.000010bc uba: 0x024092df.005b.01
Block cleanout record, scn: 0x0000.0050c823 ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.00505633
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01c0296e hdba: 0x01c0296a
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
ncol: 8 nnew: 1 size: 1
col 1: [ 4] 52 68 79 73
其他的我就不说了，我们看一下这个值为：52 68 79 73 正是将表emp的empno为7500的ename改为Rhys。对了，注意这里还有一个bdba，代表修改块的前镜像：bdba：0x01c0296e 代表第七个数据文件的10606这个数据块。
这是第4个改变向量：
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:5.20 ENC:0
session number = 1
serial number = 7
transaction name =
version 186647552
audit sessionid 7720329
Client Id =
login username = RHYS
做了一个标记，MEDIA RECOVERY MARKER SCN:0x0000.00000000，但是没有提交，那么事务恢复的话就在此处了。可以看到sid为1，serial为7 还有audit正是session 1的用户信息：
2）查看undo信息如下：
。。。。。。
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x1f 10 0x80 0x10bc 0x0013 0x0000.0050c823 0x024092df 0x0000.000.00000000 0x00000001 0x00000000 0
0x20 9 0x00 0x10bc 0x0002 0x0000.00507584 0x024092da 0x0000.000.00000000 0x00000001 0x00000000 1382608682
0x21 9 0x00 0x10bc 0x001d 0x0000.0050764f 0x024092db 0x0000.000.00000000 0x00000001 0x00000000 1382608826
。。。。。。。。
。。。。。。。。
********************************************************************************
********************************************************************************
Undo Segment: _SYSSMU20_487181632$ (20)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 32 #blocks: 2535
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x024092df ext#: 19 blk#: 95 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 19
Unlocked
Map Header:: next 0x00000000 #extents: 32 obj#: 0 flag: 0x40000000
。。。。。。。。。。
UNDO BLK:
xid: 0x0014.01f.000010bc seq: 0x5b cnt: 0x1 irb: 0x1 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f48
*-----------------------------
* Rec #0x1 slt: 0x1f objn: 89179(0x00015c5b) objd: 89716 tblspc: 8(0x00000008)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x024092de.005b.25 ctl max scn: 0x0000.005072ce prv tx scn: 0x0000.0050730f
txn start scn: scn: 0x0000.0050c823 logon user: 92
prev brb: 37786328 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01c0296e hdba: 0x01c0296a
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 8 nnew: 1 size: -1
col 1: [ 3] 41 4d 59
End dump data blocks tsn: 10 file#: 9 minblk 37599 maxblk 37599
原镜像数据也是41 4d 59为amy
3）查看原镜像数据块如下：
。。。。。。。。。。。。。。。。。
Start dump data blocks tsn: 8 file#:7 minblk 10606 maxblk 10606
Block dump from cache:
Dump of buffer cache at level 4 for tsn=8 rdba=29370734
BH (0x777e2e10) file#: 7 rdba: 0x01c0296e (7/10606) class: 1 ba: 0x77554000
set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,0
dbwrid: 0 obj: 89716 objn: 89179 tsn: 8 afn: 7 hint: f
hash: [0x7c22e880,0x7c22e880] lru: [0x777e3038,0x777e2dc8]
ckptq: [NULL] fileq: [NULL] objq: [0x777e3408,0x796cea90] objaq: [0x777e3418,0x796cea80]
st: XCURRENT md: NULL fpin: 'kduwh01: kdusru' tch: 1
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
Block dump from disk:
buffer tsn: 8 rdba: 0x01c0296e (7/10606)
scn: 0x0000.0050c823 seq: 0x01 flg: 0x04 tail: 0xc8230601
frmt: 0x02 chkval: 0x9ef8 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
。。。。。。。。。。。。。。。。。。。。
Block header dump: 0x01c0296e
Object id on Block? Y
seg/obj: 0x15e74 csc: 0x00.50c823 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1c02968 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0014.017.00001085 0x024092b6.005b.01 C--- 0 scn 0x0000.00505633
0x02 0x0014.01f.000010bc 0x024092df.005b.01 ---- 1 fsc 0x0000.00000000
bdba: 0x01c0296e
note：bdba,xid这和前面的一致
另外注意这个flag：
Flag – Transaction flag
---- = Uncommitted
-B-- = The UBA (Undo Block Address) contains undo for this ITL
--U- = Committed by fast commits & delayed block cleanout has not occurred
---T = Transaction active at block cleanout SCN
-C-U- = Block cleaned by delayed block cleanout, and rollback segment info is overwritten.
。。。。。。。。。。。
block_row_dump:
tab 0, row 0, @0x1d42
tl: 36 fb: --H-FL-- lb: 0x2 cc: 8
col 0: [ 2] c2 4c
col 1: [ 4] 52 68 79 73
col 2: [ 5] 43 4c 45 52 4b
col 3: [ 3] c2 50 03
col 4: [ 7] 77 b4 0c 11 01 01 01
col 5: [ 2] c2 09
col 6: *NULL*
col 7: [ 2] c1 15
tab 0, row 1, @0x1d89
注意：这里的lb代表锁定状态正是指向了itl为0x02。
总结一下：
当我更新一条数据的时候，
1、首次查看该数据是否在buffer cache中，如果没有则从数据文件读入buffer cache
2、更新操作时首先在undo分配事务槽，并记录redo信息
2、在buffer cache中创建数据的前镜像，并记录redo信息
3、修改值，需要记录redo信息
4、提交之后再redo中记录信息，并标记undo中的数据为非激活状态，flag标记进行相应改变。
