Exploration of Oracleundo image data

Undo is to ensure consistent read. That is to say, if you have not submitted the changed data, you cannot view the changed content in other sessions.

Undo is to ensure consistent read. That is to say, if you have not submitted the changed data, you cannot view the changed content in other sessions.

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 where obj # = 46;

1 row updated.

SQL> update t set where obj # = 28;

1 row updated.

SQL> update t set where obj # = 15;

1 row updated.

SQL> update t set 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