Understanding transaction, redo and undo record

Stephen Hawking: The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge.

I once firmly believed that I’m familiar with the concept of transactions in Oracle such as the ACID properties, rollback, commit, etc. However, it suddenly began to dawn on me that I knew nothing about how Oracle actually do those work at all. They say that the proof is in the pudding. So here I am, trying to understand what’s behind the scene.

The whole test scenario is quite simple. We insert 1000 rows into a table and check out what really happened in Oracle.

drop user caoff cascade;
create user caoff identified by caoff;
grant connect, resource to caoff;

conn caoff/caoff
create table t1(id number, name varchar2(64));

— insert data into table

begin
for i in 1..1000 loop
insert into t1 values(
i, 'INITIAL VALUE OF COLUMN');
end loop;
end;
/
select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
---------------------
10.18.2906       

commit;

alter system flush buffer_cache;

— find the file and block these data went to:

select
dbms_rowid.rowid_relative_fno(rowid) File#,
dbms_rowid.rowid_block_number(rowid) Block#,
count(1)
from t1
group by
dbms_rowid.rowid_relative_fno(rowid),
dbms_rowid.rowid_block_number(rowid)
order by 1,2
/

FILE# BLOCK# COUNT(1)
---------- ---------- ----------
4 435 220
4 436 117
4 437 220
4 438 223
4 439 220

— identify rows in a specific block

select min(id), max(id), count(id)
from t1
where dbms_rowid.rowid_block_number(rowid) = 436;

MIN(ID) MAX(ID) COUNT(ID)
---------- ---------- ----------
884 1000 117

— dump datafile to check current ITL information as sysdba

oradebug setmypid
oradebug tracefile_name
/oraapp/oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_20905.trc

alter system dump datafile 4 block 436;

— information in trace file

Block header dump: 0x010001b4
Object id on Block? Y
seg/obj: 0x3b02 csc: 0x00.b57e5 itc: 2 flg: E typ: 1 - DATA 
brn: 0 bdba: 0x10001b0 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.012.00000b5a 0x00c007e0.017a.0a --U- 117 fsc 0x0000.000b57f7
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

-- we can see the xid in the first slot ( which is consistent with the output of dbms_transaction.local_transaction_id) :

000a : undo segment number, decimal 10
012 : undo slot number, decimal 18
00000b5a : undo slot wrap number, decimal 2906

— Note the UBA format:

0x00c007e0 .017a .0a
Undo block Address Seq No. Record No.
select name from v$rollname where usn=10;

— Now we dump the undo header

alter system dump undo header '_SYSSMU10_2623394399$';

------
TRN TBL::

index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x0b 9 0x00 0x0b57 0x0020 0x0000.000b5831 0x00c007e0 0x0000.000.00000000 0x00000001 0x00000000 1453703700
0x0c 9 0x00 0x0b55 0x0012 0x0000.000b57e3 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1453703622
0x0d 9 0x00 0x0b52 0x0002 0x0000.000b5788 0x00c007d5 0x0000.000.00000000 0x00000001 0x00000000 1453703519
0x0e 9 0x00 0x0b52 0x0014 0x0000.000b57c0 0x00c007d6 0x0000.000.00000000 0x00000001 0x00000000 1453703612
0x0f 9 0x00 0x0b54 0x0001 0x0000.000b57c3 0x00c007d5 0x0000.000.00000000 0x00000001 0x00000000 1453703612
0x10 9 0x00 0x0b59 0x0015 0x0000.000b5730 0x00c007c9 0x0000.000.00000000 0x00000001 0x00000000 1453703311
0x11 9 0x00 0x0b53 0x000a 0x0000.000b5726 0x00c007cc 0x0000.000.00000000 0x00000001 0x00000000 1453703305
->0x12 9 0x00 0x0b5a 0x0013 0x0000.000b57f7 0x00c007e0 0x0000.000.00000000 0x0000000a 0x00000000 1453703641
0x13 9 0x00 0x0b53 0x0006 0x0000.000b5810 0x00c007e0 0x0000.000.00000000 0x00000001 0x00000000 1453703700
0x14 9 0x00 0x0b54 0x000f 0x0000.000b57c2 0x00c007d6 0x0000.000.00000000 0x00000001 0x00000000 1453703612
0x15 9 0x00 0x0b53 0x001e 0x0000.000b5733 0x00c007cc 0x0000.000.00000000 0x00000001 0x00000000 1453703311
0x16 9 0x00 0x0b55 0x001f 0x0000.000b5822 0x00c007e0 0x0000.000.00000000 0x00000001 0x00000000 1453703700
0x17 9 0x00 0x0b57 0x0004 0x0000.000b57d8 0x00c007d6 0x0000.000.00000000 0x00000001 0x00000000 1453703618
0x18 9 0x00 0x0b56 0x0008 0x0000.000b581e 0x00c007e0 0x0000.000.00000000 0x00000001 0x00000000 1453703700

State 9 means the transaction is invactive. 10 means active transaction.

commit SCN: 0x0000.000b57f7

SQL> select scn_to_timestamp(to_number('0b57f7','xxxxxx')) from dual;

SCN_TO_TIMESTAMP(TO_NUMBER('0B57F7','XXXXXX'))
---------------------------------------------------------------------------
25-JAN-16 02.34.00.000000000 PM

UNDO dba: 0x00c007e0

— convert to decimal

select to_number('00c007e0','xxxxxxxx') from dual;

TO_NUMBER('00C007E0','XXXXXXXX')
--------------------------------
12584928

— identify undo datafile and block number

select dbms_utility.data_block_address_file(12584928) FILE#,
dbms_utility.data_block_address_block(12584928) BLOCK#
from dual;

FILE# BLOCK#
---------- ----------
3 2016

— Now we dump undo block

alter system dump datafile 3 block 2016;

--- We found the Undo record in the UNDO block:
*-----------------------------
* Rec #0xa slt: 0x12 objn: 15106(0x00003b02) objd: 15106 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x09
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
compat bit: 4 (post-11) padding: 1
op: C uba: 0x00c007e0.017a.09
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010001b4 hdba: 0x010001b2
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 116(0x74)

Basically, the above information means “delete the row from the directory at slot# 116 in the block with DBA 0x010001b4”

In the data block dump, we found the information about slot# 116(0x74):

0x74:pri[49] offs=0x198b
-->
tab 0, row 49, @0x198b
tl: 31 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] c2 0a 22
col 1: [23]
49 4e 49 54 49 41 4c 20 56 41 4c 55 45 20 4f 46 20 43 4f 4c 55 4d 4e
I N I T I A L V A L U E O F C O L U M N

‘C2 0A 22’ converts to decimal ‘194 10 34’. We can discard the 194 as it is a prefix, and then subtract one from each digit-pair and append them to get the number 0933.

By the way, Oracle internally stores number as pairs and basically the prefix C2 means “there should be 2 subsequent number pairs” and you can apply the above converting rules to those number pairs. This also means that when there is only one number pair following the prefix, i.e “C2 02”, the above rule would not apply and “C2 02” simply means 100 (02-1 plus 00 as number pair).

SQL> select dump(100,16) from dual;

DUMP(100,16)
-----------------
Typ=2 Len=2: c2,2

select UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('49')) from dual;

UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('49'))
---------------------------------------
I

select DBMS_ROWID.ROWID_OBJECT(rowid) "OBJECT",
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) "FILE",
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) "BLOCK",
DBMS_ROWID.ROWID_ROW_NUMBER(rowid) "ROW"
from T1
where id=933;

OBJECT FILE BLOCK ROW
---------- ---------- ---------- ----------
15106 4 436 49

We can see that number 933 was indeed stored in row 49.

Now we dump redo logfile to checkout what happened there.

select member
from v$log l, v$logfile lf
where l.group# = lf.group#
and l.status='CURRENT';

alter system dump logfile '/oradata/mydb/redo02.log';

In the trace file, we search “objn: 15106”:

REDO RECORD - Thread:1 RBA: 0x000029.0000d585.0010 LEN: 0x0180 VLD: 0x01
SCN: 0x0000.000b57e5 SUBSCN: 2 01/25/2016 14:33:49
CHANGE #1 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.000b57e3 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0012 sqn: 0x00000b5a flg: 0x0012 siz: 112 fbi: 0
uba: 0x00c007d6.017a.24 pxid: 0x0000.000.00000000 
CHANGE #2 TYP:0 CLS:36 AFN:3 DBA:0x00c007d6 OBJ:4294967295 SCN:0x0000.000b57e2 SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 112 spc: 4444 flg: 0x0012 seq: 0x017a rec: 0x24
xid: 0x000a.012.00000b5a
ktubl redo: slt: 18 rci: 0 opc: 11.1 [objn: 15106 objd: 15106 tsn: 4]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c007d6.017a.23
prev ctl max cmt scn: 0x0000.000b56a9 prev tx cmt scn: 0x0000.000b56ae
txn start scn: 0xffff.ffffffff logon user: 51 prev brb: 12584908 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: DRP row dependencies Disabled 
xtype: XA flags: 0x00000000 bdba: 0x010001b6 hdba: 0x010001b2
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0)
CHANGE #3 TYP:0 CLS:1 AFN:4 DBA:0x010001b6 OBJ:15106 SCN:0x0000.000b57e5 SEQ:1 OP:11.2 ENC:0 RBL:0
KTB Redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x000a.012.00000b5a uba: 0x00c007d6.017a.24
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010001b6 hdba: 0x010001b2
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 30
fb: --H-FL-- lb: 0x1 cc: 2
null: --
col 0: [ 2] c1 02 col 1: [23]
49 4e 49 54 49 41 4c 20 56 41 4c 55 45 20 4f 46 20 43 4f 4c 55 4d 4e

Now we can see what ITL, transaction table, redo record, undo segment header, undo record, data block header really mean. Enjoy the rest of your work!

Reference:

http://arup.blogspot.jp/2011/01/more-on-interested-transaction-lists.html
https://jonathanlewis.wordpress.com/2009/06/16/clean-it-up/
http://www.sosdb.com/article/datafile_block.htm

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s