Oracle Basic Data Change Principle Analysis (redo and undo) -- oracle Core Technology reader I, redo -- oracle

Source: Internet
Author: User

Oracle Basic Data Change Principle Analysis (redo and undo) -- oracle Core Technology reader I, redo -- oracle

In oracle, we have made some update operations. How does the bottom layer of oracle flow? This is what we will explain in this article.

I. Principles

Suppose we have updated a row of data in an OLTP system that has updated many distributed records. The update process is as follows:

1. Create a redo Change vector to describe how to insert an undo record to the undo block (that is, to describe the change of the undo block)

2. Create a redo Change vector to describe the change of data blocks (that is, the change of data)

3. merge the two redo vectors into a log record and write them to the redo log buffer for future redo operations)

4. Insert undo records into the undo block (to facilitate future rollback)

5. Change the data in the data block (the data is actually changed here)

The following example shows the process.


Ii. Practice

First, create a table, and then update records (3, 4, 5) of the first data block in the Table. After each update, a record of the second data block is updated. That is, this update operation will update 6 records, and it will change the third column of each record ------ A varchar2 type field, which is composed of xxxxxx (6 Characters in lower case) changed to YYYYYYYYYY (10 characters in upper case ).

1. Run the cmd command line to log on as a sys user.


2. Preparation (several stored procedures are created to dump blocks, dump redo logs, and so on)

See these scripts: http://download.csdn.net/detail/liwenshui322/7912909


3. Preparation (mainly clearing information deleted from the recycle bin, setting the block read cost, and calculating the valuation basis)

Start setenvset timing offexecute dbms_random.seed (0) drop table t1; beginbeginexecute immediate 'purge recyclebin'; -- clear the recycle bin exceptionwhen others then null; end; terminate ('mbrc', 8 ); -- eight dbms_stats.set_system_stats ('mreadtim', 26) for multi-block reading; -- the average block read time is 26 Ms dbms_stats.set_system_stats ('sreadtim', 12 ); -- average reading time of a single block is 30 ms hour ('cpuspeed', 800); -- the cpu can execute 800,000,000 operations per second predictionwhen others then null; end; beginexecute immediate 'in in dbms_stats.delete_system_stats; end; '; -- delete system Statistical Information predictionwhen others then null; end; beginexecute immediate 'alter session set "_ optimizer_cost_model" = io '; -- calculate the estimated value based on io: predictionwhen others then null; end ;/

4. Create tables and Indexes

create table t1asselect2 * rownum - 1id,rownumn1,cast('xxxxxx' as varchar2(10))v1,rpad('0',100,'0')paddingfromall_objectswhererownum <= 60union allselect2 * rownumid,rownumn1,cast('xxxxxx' as varchar2(10))v1,rpad('0',100,'0')paddingfromall_objectswhererownum <= 60;create index t1_i1 on t1(id);

5. Statistical table information

begindbms_stats.gather_table_stats(ownname => user,tabname =>'T1',method_opt  => 'for all columns size 1');end;/

6. view the block used by the table and the number of data entries in each block.

select dbms_rowid.rowid_block_number(rowid)block_number, count(*)rows_per_blockfrom t1 group by dbms_rowid.rowid_block_number(rowid)order byblock_number;

We can see that two blocks are used in total, and each block has 60 records.

7. Dump data blocks

alter system switch logfile;execute dump_seg('t1')

8. Update

update/*+ index(t1 t1_i1) */t1setv1 = 'YYYYYYYYYY'whereid between 5 and 9;
9. dump the data blocks and undo blocks after the update blocks (after the checkpoint statement is executed, the next statement is executed after 5 or 6 s. When a checkpoint occurs, it only tells oracle to write dirty data to the disk, A little time required)

pause Query the IMU structures now  (@core_imu_01.sql)
Alter system checkpoint; -- a checkpoint occurs to write data to the disk.
execute dump_seg('t1')execute dump_undo_block
10. Dump redo Blocks
rollback;commit;execute dump_log
11. Locate the location of the dump information file
Select sid from v $ mystat where rownum = 1; -- input the query result to the next sqlSELECT d. value | '/' | lower (rtrim (I. instance, chr (0) | '_ ora _' | p. spid | '. trc 'trace_file_name from (select p. spid from v $ session s, v $ process p where s. sid = '000000' and p. addr = s. paddr) p, (select t. instance from v $ thread t, v $ parameter v where v. name = 'thread' and (v. value = 0 or t. thread # = to_number (v. value) I, (select value from v $ parameter where name = 'user _ dump_dest ') d;

12. open the file
Next, let's take a look at several key parts. We can see 5th pieces of data in the first block. We changed the third column of this row from xxxxxx to YYYYYYYYYY.

Before update:

tab 0, row 4, @0x1d3ftl: 117 fb: --H-FL-- lb: 0x0  cc: 4col  0: [ 2]  c1 0acol  1: [ 2]  c1 06col  2: [ 6]  78 78 78 78 78 78col  3: [100] 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
We can see that col2 is 6 and then 6 78 (x's hexadecimal ASCII code is 78 ).

After update:

tab 0, row 4, @0x2a7tl: 121 fb: --H-FL-- lb: 0x2  cc: 4col  0: [ 2]  c1 0acol  1: [ 2]  c1 06col  2: [10]  59 59 59 59 59 59 59 59 59 59col  3: [100] 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
We can see that the length of col2 is changed to 10, which is 10 59 (The hexadecimal ASCII code of Y is 59). At the same time, we can see that the row address is changed from @ 0x1d3f to @ 0x2a7, this shows that the space in this row cannot accommodate the new data, but it is changed to another place. (This can also be found in the row directory) at the same time, we can see that lb (lock byte) is changed from 0x0 to 0x2, indicates that this record is locked by the transaction identified by the second transaction slot in the transaction slot list. The transaction slot can be seen in the block header.

Next, let's take a look at what 5th pieces of data are stored in redo (how to ensure data redo), search for tabn: 0 slot: 4 (0x4) flag in the file: 0x2c lock: 2, we will find this description

CHANGE #6 TYP:0 CLS:1 AFN:1 DBA:0x004161c9 OBJ:77125 SCN:0x0000.002796b6 SEQ:2 OP:11.5 ENC:0 RBL:0KTB Redo op: 0x02  ver: 0x01  compat bit: 4 (post-11) padding: 0op: C  uba: 0x00c0055a.0123.27KDO Op code: URP row dependencies Disabled  xtype: XA flags: 0x00000000  bdba: 0x004161c9  hdba: 0x004161c8itli: 2  ispac: 0  maxfr: 4863tabn: 0 slot: 4(0x4) flag: 0x2c lock: 2 ckix: 50ncol: 4 nnew: 1 size: 4col  2: [10]  59 59 59 59 59 59 59 59 59 59
This describes a change in the world. We can see Row 3 op code: URP (update row). Row 7 shows the updated block address bdba and the segment address hdba.

Row 8 itli: 2 indicates that the transaction performing the update operation is using the second transaction slot, which is consistent with the data block.

Row 9 tabn: 0 slot: 4 indicates that we are updating 5th records of the first table (one block may store data from multiple tables.

In the last two rows, we can see that this record has four columns (nclo: 4), modified a column (nnew: 1), and increased the length by 4 (size: 4 ). change the value of column 3rd to YYYYYYYYYY. (The modified data is saved for easy redo)


Next, let's take a look at how to save 5th pieces of data in undo (how to ensure data rollback), search for tabn: 0 slot: 4 (0x4) flag: 0x2c in the file, we will find the following description:

*-----------------------------* Rec #0x27  slt: 0x04  objn: 77125(0x00012d45)  objd: 77125  tblspc: 0(0x00000000)*       Layer:  11 (Row)   opc: 1   rci 0x26   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: 0op: C  uba: 0x00c0055a.0123.25KDO Op code: URP Disabled row dependencies  xtype: XA flags: 0x00000000  bdba: 0x004161c9  hdba: 0x004161c8itli: 2  ispac: 0  maxfr: 4863tabn: 0 slot: 4(0x4) flag: 0x2c lock: 0 ckix: 50ncol: 4 nnew: 1 size: -4col  2: [ 6]  78 78 78 78 78 78

Focus on the following six rows of data, which is similar to the data in the previous redo, that is, size =-4, col2 is changed to 6 78 (x's hexadecimal ASCII code is 78 ). (Ensure that data can be returned to previous versions)

Finally, we can find the undo block change description in the dump redo. Search the file for tabn: 0 slot: 4 (0x4) flag: 0x2c lock: 0, we will find the following description:

CHANGE #11 TYP:0 CLS:36 AFN:3 DBA:0x00c0055a OBJ:4294967295 SCN:0x0000.002796b6 SEQ:4 OP:5.1 ENC:0 RBL:0ktudb redo: siz: 92 spc: 4078 flg: 0x0022 seq: 0x0123 rec: 0x27            xid:  0x000a.004.00000467  ktubu redo: slt: 4 rci: 38 opc: 11.1 objn: 77125 objd: 77125 tsn: 0Undo type:  Regular undo       Undo type:  Last buffer split:  No Tablespace Undo:  No              0x00000000KDO undo record:KTB Redo op: 0x02  ver: 0x01  compat bit: 4 (post-11) padding: 0op: C  uba: 0x00c0055a.0123.25KDO Op code: URP row dependencies Disabled  xtype: XA flags: 0x00000000  bdba: 0x004161c9  hdba: 0x004161c8itli: 2  ispac: 0  maxfr: 4863tabn: 0 slot: 4(0x4) flag: 0x2c lock: 0 ckix: 50ncol: 4 nnew: 1 size: -4col  2: [ 6]  78 78 78 78 78 78
The fifth line indicates that this is an undo block change description. We can see that the last few rows are very similar to the data in the undo, because the undo block change is recorded here.

Since then, we have basically been able to see how oracle describes the changes in data before we can actually change the data.



Ask oracle's redo and undo questions,

1. In case of power failure, the transaction will not be committed. In fact, the redo apply and undo apply procedures are described as follows: the database first uses redo to restore the data file (including the data file of the common permanent tablespace and the data file of the undo tablespace ), this is what you say: "restoring the system to the failed point". After this, the database instance will be opened. After the instance is opened, the instance rolls back uncommitted transactions based on the information recorded in the undo.
The following is a description in the original factory manual:
In the context of refiles, the undo information is used to undo the effects of uncommitted transactions, once all the datafile changes from the redo logs have been applied to the datafiles. the database is actually opened before the undo is applied.

You shoshould not have to concern yourself with undo segments or manage them directly as part of your backup and recovery process.

2. If the disk is damaged, this recovery is also called media recovery. Before restoring (recover), you need to use backup to restore (restore) Damaged files.

Oracle redo and undo

1. Record Database changes, such as update, insert, and delete.
2. It is not directly written to the undo tablespace. It is written only when the dirty data block reaches a certain level.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.