Understanding Redo (3) explain an update process

Theoretical analysis:

1) server process searches buffer cache for the block to be updated by Update (also known as buffer in memory). If no block is found, it reads the buffer cache from disk.

2) server process constructs a group of change vectors to record changes to the data blocks (put in PGA at this time). This group of change vectors form a redo record.
Four data blocks may be changed:
The segment header of the rollback segment: indicates the transaction information on the corresponding transaction table, including Xid and UBA, and allocates rollback blocks.
Block header blocks: allocate transaction slots and Mark transaction information, including Xid and UBA.
Rollback block: record the pre-image
Data Block: Modify

3) use the redo record size to determine the size of the redo log buffer.

4) judge the current SCN and save it to the redo record (different redo record may share the same SCN value and use the value seq in the Change Vector header to differentiate it)

5) obtain the redo copy latch, and then obtain the redo allocation latch.

6) Check whether another process has a higher SCN value than the currently held SCN. If yes, generate a new SCN and replace the SCN in step 1. In short, save the latest SCN.

7) determine whether there is sufficient redo log buffer space. The details are as follows:

 

If the redo log buffer does not have enough space, release the redo copy latch and redo allocation latch. If other processes or conditions start with lgwr, wait for lgwr to complete, if lgwr is not started at this time, lgwr is triggered (redo writing latch is required, And the lgwr process will always occupy this latch when the redo record is written to the online current redo log file from the log buffer, prevent other processes from triggering lgwr at the same time. If other processes attempt to obtain the latch, they must wait and obtain the redo
Allocation latch prevents a New Change vector from continuing to write data to the log buffer, causing lgwr to be unable to determine the number of redo writes). When there is not enough space, after the redo copy/allocation latch is released, if the redo writing latch cannot be obtained, the lgwr is started by other processes, after obtaining the data, check whether the log buffer has space (because the lgwr may have released a large number of redo log buffer space). If the space is available, release the redo writing latch and obtain the redo
Copy/allocation latch. If there is no space, lgwr is triggered and redo writing latch is released (this latch is given to lgwr ). if there is no space and lgwr writes the redo record in the redo log buffer to the online current redo logfile, when the redo logfile space is insufficient, the process checks whether other processes have started the switch logfile. If not, the switch logfile is triggered. If other processes have started, the current process waits.
After logfile, continue to allow lgwr to write the redo log buffer. (Note that the generation of redo is prohibited when the log is switched)

If the redo log buffer has enough space, allocate space, release the redo allocation latch, and then copy the Change vector from PGA to the redo log buffer, mount the block corresponding to the redo record modified in the buffer cache to ckptq (checkpoint Queue) (wait for dbwr to write) and then release the redo copy latch.

 

8) check whether the redo log buffer and redo record have reached threadshold after writing. If so, lgwr is triggered.

9) Update the block in the buffer cache.

 

Experiment details (dump undo information to understand ):

1)

SQL> conn HR/HR is connected. SQL> drop table T1; drop table T1 * 1st row error: ORA-00942: Table or view does not exist SQL> Create Table T1 (ID number, name varchar2 (20 )); the table has been created. SQL> insert into T1 (1, 'A'); insert into T1 (1, 'A') * row 1st error: ORA-00928: the Select keyword SQL> insert into T1 values (1, 'A') is missing; 1 row has been created. SQL> insert into T1 values (2, 'B'); 1 row has been created. SQL> commit; submitted completely.

2)

SQL> select dbms_rowid.rowid_relative_fno (rowid) fno, 2 dbms_rowid.rowid_block_number (rowid) BNO 3 from HR. t1; fno BNO ---------- 4 437 4 437sql> select data_object_id, owner from region where object_name = 't1'; data_object_id owner ------------ limit 53467 hrsql> alter system flush buffer_cache; the system has changed. SQL & gt; Select File #, block #, dirty from V $ BH where objd = 53467; file # block # Di ---------- -- 4 438 N 4 433 N 4 436 N 4 439 N 4 434 N 4 437 N 4 N 6 rows have been selected.

3)

SQL> select * from T1; ID name ---------- ------------------------------------------ 1 A 2 bsql> Update T1 set name = 'C' where id = 1; Update 1 line.

4)

SQL & gt; Select File #, block #, dirty from V $ BH where objd = 53467; file # block # Di ---------- -- 4 440 N 4 435 N 4 438 N 4 438 N 4 433 N 4 436 N 4 436 N 4 439 N 4 439 N 4 434 N 4 437 N 4 N 4 437 N 4 437 y selected 13 rows. SQL> select xidusn, xidslot, xidsqn, ubafil, ubablk from V $ transaction; xidusn xidslot xidsqn ubafil ubablk ---------- 8 6 579 2 2636

Server process finds file No. 4 and 437th blocks in buffer cache, and finds an available undo block: 2,636th blocks, stores the old values, and generates a Change vector, then, the new value is saved into 437th blocks to generate a Change vector.

5)

SQL> Conn/As sysdba is connected. SQL> select * from HR. T1; ID name ---------- ---------------------------------------- 1 A 2 bsql> Update HR. T1 set name = 'D' where id = 2; 1 line has been updated.

6)

SQL & gt; Select File #, block #, dirty from V $ BH where objd = 53467; file # block # Di ---------- -- 4 440 N 4 435 N 4 438 N 4 438 N 4 433 N 4 436 N 4 436 N 4 439 N 4 439 N 4 434 N 4 437 N 4 N 4 437 N 4 437 N 4 437 N 4 437 Y 4 437 N 4 437 n selected 17 rows. SQL> select xidusn, xidslot, xidsqn, ubafil, ubablk from V $ transaction; xidusn xidslot xidsqn ubafil ubablk ---------- ------------ ---------- 9 3 607 2 2184 -- T2 8 6 579 2 2636 -- T1

At this time, when transaction T1 is committed, lgwr will write the redo entries of transaction T2 together into the log file

7)

SQL> select name,usn from v$rollname where usn in (8,9);NAME                                                                USN------------------------------------------------------------ ----------_SYSSMU8$                                                             8_SYSSMU9$                                                             9

8)

Dump udno segment 8sql> select spid from V $ process where ADDR in (select paddr from V $ session where Sid in (select Sid from V $ mystat where 2 rownum = 1 )); SPID------------------------5316SQL> alter system dump undo header '_ syssmu8 $'; the system has changed. Dump undo segment 9sql> select spid from V $ process where ADDR in (select paddr from V $ session where Sid in (select Sid from V $ mystat where rownum = 1 )); SPID------------------------5544SQL> alter system dump undo header '_ syssmu9 $'; the system has changed.

9)

_ Syssmu8 $ dump trn tbl :: index state cflags wrap # uel scn dba parent-Xid nub stmt_num CMT 0x06 10 0x80 0x0243 0x0002 0x0000.00215819 0x00800a4c 0x0000. 000.00000000 0x00000001 0x00000000 0 xidslot is 0x06, that is, the transaction table contains 6th rows. State = 10 indicates that there is an active transaction (9 indicates that it has been committed) _ syssmu9 $ dump is the same as dump8, and this is not a table.

According to the log file information, to be continued ...........