Oracle undo image data exploration

As we all know, undo is designed to ensure consistent read. That is to say, if you are not submitting the changed data, other sessions cannot view the changed content, if the other party reads the data, the data is still the previous data, and the undo is the pre-image that saves the data. After reading this article, you will know how undo saves the pre-image and what its internal mechanism is. Now we start to explore how undo saves the pre-image.

Recommended reading:

[Oracle] What should I do if undo is damaged without backup?

How to handle UNDO tablespace failures

Undo tablespace faults and ORA-01548 Processing

Recovery of undo tablespace loss under RAC

UNDO tablespace Backup Recovery

1) update a piece of data first, but do not perform the following commit:
SQL> col name for a50
SQL> select obj #, name from t where rownum <5;

OBJ # NAME
------------------------------------------------------------
46 I _USER1
28 Rhys1
15 RHYS20
29 RHYS3

SQL> update t set name = 'rhys1' where obj # = 46;

1 row updated.

SQL> update t set name = 'rhys2' where obj # = 28;

1 row updated.

SQL> update t set name = 'rhys3' where obj # = 15;

1 row updated.

SQL> update t set name = 'rhys4 'where obj # = 29;

1 row updated.

SQL> select xidusn, xidslot, ubafil, ubablk from v $ transaction;

XIDUSN XIDSLOT UBAFIL UBABLK
----------------------------------------
12 7 8 1493

SQL> select * from v $ rollname where usn = 12;

USN NAME
----------------------------------------
12 _ SYSSMU12_584745277 $

SQL> select usn, status, curext from v $ rollstat;

USN STATUS CUREXT
-----------------------------------
0 ONLINE 5
11 ONLINE 4
12 ONLINE 3
13 ONLINE 2
14 ONLINE 3
15 ONLINE 2
16 ONLINE 2
17 ONLINE 14
18 ONLINE 4
19 ONLINE 3
20 ONLINE 2

11 rows selected.

SQL> select usn, status, curext, xacts from v $ rollstat where xacts> 0;

USN STATUS CUREXT XACTS
---------------------------------------------
12 ONLINE 3 1

SQL> alter system dump undo header '_ SYSSMU12_584745277 $ ';

System altered.

SQL> alter system dump datafile 8 block 1493;

System altered.

SQL> col name for a80
SQL> col inst_id for 9999
SQL> col value for a80
SQL> select * from v $ diag_info;

INST_ID NAME VALUE
------- Certificate -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 Diag Enabled TRUE
1 ADR Base/opt/app/oracle
1 ADR Home/opt/app/oracle/diag/rdbms/rhys/RHYS
1 Diag Trace/opt/app/oracle/diag/rdbms/rhys/RHYS/trace
1 Diag Alert/opt/app/oracle/diag/rdbms/rhys/RHYS/alert
1 Diag Incident/opt/app/oracle/diag/rdbms/rhys/RHYS/incident
1 Diag Cdump/opt/app/oracle/diag/rdbms/rhys/RHYS/cdump
1 Health Monitor/opt/app/oracle/diag/rdbms/rhys/RHYS/hm
1 Default Trace File/opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_5284.trc
1 Active Problem Count 1
1 Active Incident Count 1

11 rows selected.
2) from the above steps, we can obtain the following information:
The undo segment used by this transaction is 12, and its zone is 3
The undo segment is named "_ SYSSMU12_584745277 $"
The transaction has 7th slots in the undo, the data file number is 8, and the data block used is 1493;
3) We start to explore the undo internal mechanism, first dumping the undo header; (the extracted information is as follows)
**************************************** ****************************************
Undo Segment: _ SYSSMU12_584745277 $ (12)
**************************************** ****************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header: spare1: 0 spare2: 0 # extents: 4 # blocks: 271
Last map 0x00000000 # maps: 0 offset: 4080
Highwater: 0x020005d5 ext #: 3 blk #: 85 ext size: 128
# Blocks in seg. hdr's freelists: 0
# Blocks below: 0
Mapblk 0x00000000 offset: 3
Unlocked
Map Header: next 0x00000000 # extents: 4 obj #: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x02000091 length: 7
0x02000168 length: 8
0x02000280 length: 128
0x02000580 length: 128

Retention Table
-----------------------------------------------------------
Extent Number: 0 Commit Time: 1376748046
Extent Number: 1 Commit Time: 1376748092
Extent Number: 2 Commit Time: 1376804143
Extent Number: 3 Commit Time: 1376804143

Trn ctl: seq: 0x033a chd: 0x0020 ctl: 0x0001 inc: 0x00000000 nfb: 0x0000
Mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
Uba: 0x020005d5. 033a. 24 scn: 0x0000. 009b9276
Version: 0x01
Free block pool ::
Uba: 0x00000000. 033a. 23 ext: 0x3 spc: 0xc78
Uba: 0x00000000. 033a. 27 ext: 0x3 spc: 0xb20
Uba: 0x00000000. 033a. 08 ext: 0x3 spc: 0x1c66
Uba: 0x00000000. 033a. 45 ext: 0x3 spc: 0x200
Uba: 0x00000000. 0000.00 ext: 0x0 spc: 0x0
Trn tbl ::
 
Index state cflags wrap # uel scn dba parent-xid nub stmt_num cmt
Bytes ------------------------------------------------------------------------------------------------
0x00 9 0x00 0x0059 0x0016 0x0000. 009b9292 0x020005d3 0x0000. 000.00000000 0x00000001 0x00000000 1376806203
0x01 9 0x00 0x005a 0 xffff 0x0000. 009b94e5 0x020005d5 0x0000. 000.00000000 0x00000001 0x00000000 1376806887
0x02 9 0x00 0x005a 0x000a 0x0000. 009b9461 0x020005d5 0x0000. 000.00000000 0x00000001 0x00000000 1376806707
0x03 9 0x00 0x0059 0x0017 0x0000. 009b930e 0x020005d3 0x0000. 000.00000000 0x00000001 0x00000000 1376806203
0x04 9 0x00 0x0059 0x0005 0x0000. 009b92cf 0x020005d3 0x0000. 000.00000000 0x00000001 0x00000000 1376806203
0x05 9 0x00 0x0059 0x0008 0x0000. 009b92d8 0x020005d3 0x0000. 000.00000000 0x00000001 0x00000000 1376806203
0x06 9 0x00 0x0059 0x0009 0x0000. 009b92aa 0x020005d3 0x0000. 000.00000000 0x00000001 0x00000000 1376806203
0x07 10 0x80 0x005a 0x0003 0x0000.00000000 0x020005d5 0x0000. 000.00000000 0x00000001 0x00000000 0
0x08 9 0x00 0x0059 0x001b 0x0000. 009b92e1 0x020005d3 0x0000. 000.00000000 0x00000001 0x00000000 1376806203
0x09 9 0x00 0x0059 0x000c 0x0000. 009b92b6 0x020005d3 0x0000. 000.00000000 0x00000001 0x00000000 1376806203
0x0a 9 0x00 0x0059 0x001f 0x0000. 009b9479 0x020005d5 0x0000. 000.00000000 0x00000001 0x00000000 1376806707
0x0b 9 0x00 0x0059 0x001a 0x0000. 009b92fc 0x020005d3 0x0000. 000.00000000 0x00000001 0x00000000 1376806203
0x0c 9 0x00 0x0059 0x0004 0x0000. 009b92c3 0x020005d3 0x0000. 000.00000000 0x00000001 0x00000000 1376806203

It can be seen that 0x07 is the seventh slot in the transaction table, which converts dba into a binary system and converts it into a 10-hexadecimal system. The data file number is 8 and the data block is 1493, this is the pre-image of the data.

1 2 3 Next Page