What do we need to know about Oracleundo?

What do we need to know about Oracleundo? Introduction: undo is an important component of Oracle Database. Just getting started, I suggest you understand the principle and mechanism of undo, especially the difference and connection with the redo component. Learn about undo

What do we need to know about Oracleundo? Introduction: undo is an important component of Oracle Database. Just getting started, I suggest you understand the principle and mechanism of undo, especially the difference and connection with the redo component. Learn about undo

What do we need to know about Oracle undo?

Introduction: undo is an important component of Oracle databases. It is recommended that you understand the principle and mechanism of undo, especially the difference and connection with redo components. Understanding undo is equivalent to half understanding of oracle restoration. Next we will start to learn what Oracle undo needs to know!

For more exciting articles, please contact your blog. You are welcome to discuss them.

"Sharing Technology ~ Achieving dreams"

UNDO

Hi everyone, I am Oracle's invincible xiao'an [Chinese name (small undo [English name). First of all, let me introduce myself. I was born from a big family of Oracle, and now I have a big leader, Larry. herison was my creator. I was born with Oracle and made a great contribution, allowing Oracle to break the wild! I was born to solve three major problems: Transaction rollback, instance recovery, and query consistency. This feature makes Oracle a leader in relational databases.

For example, read is not blocked by writing. When the data block we read is modified by others, we can find the pre-image saved in the undo segment to maintain consistency. Other databases and SQL server mysql do not have undo segments, which means they may be blocked during query.

Difference between Undo and Redo

Many people, especially those who are just getting started, will always confuse these two concepts.

Redo: it is based on security considerations. It records all changes in the database. when the data is mistakenly modified, redo can be used to generate and Redo things. It is a roll-forward, or a new creation from scratch. The birth of a new life.

Undo: it is based on rollback. when the data is mistakenly modified, it can be rolled back from the new state to the old state to implement transaction rollback, which is equivalent to canceling the operation. It is a rollback. Just like a tape record, you can roll the road back to the origin.

Undo objective: 1. Transaction rollback: rollback

2. instance recovery: power loss, crashes, forced database shutdown, and rollback of all uncommitted items

3. query consistency: Read is not blocked by write operations.

Undo segment: the least recently used LRU algorithm is used for loop coverage. Its blocks are active, inactive, and new, if the block status is inactive, the subsequent data can overwrite it. If the space is insufficient, Oracle automatically allocates new space.

Distribution: Oracle is used in a sequential manner and cross-zone coverage is not allowed.

Recycling: Oracle is also recycled in order, and cross-zone recycling is not allowed.

The Rollback and Commit identifiers are the same and both represent the end of a transaction.

Rollback: Roll back all the items from the last commit to the present

Database 2

SYS @ LEO1> select * from v $ version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

PL/SQL Release 11.2.0.1.0-Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0-Production

NLSRTL Version 11.2.0.1.0-Production

The three example shows whether the rollback generates REDO logs.

Principle explanation: First of all, it is clear to everyone that the redo mechanism is caused by changes in the underlying data block, whether it is a data file or an undo file, changes to data blocks are recorded in the redo log.

OK. Let's take A look at the undo rollback principle. Assume that A data block is named A and data 1 is stored in, now we want to change 1-> modify-> 2-> modify-> 3-> modify-> 2-> modify-> 1 step.

1. First, copy an image to the undo block for rollback. This is the previous image. The undo block changes generate redo records.

2. Change 1 on the data block to 2. A changes to generate redo records. Now A stores 2 records.

LEO1 @ LEO1> drop table leo2 purge; clean up the environment

Table dropped.

LEO1 @ LEO1> create table leo2 as select * from leo1 where rownum <10; create a leo2 table with only nine rows of records

LEO1 @ LEO1> select. name, B. value from v $ statname a, v $ mystat B where. STATISTIC # = B. STATISTIC # and. NAME = 'redo size ';

NAME VALUE

Certificate -----------------------------------------------------------------------------------------------------------------------------------

Redo size 2362280

This is the initial value.

LEO1 @ LEO1> update leo2 set object_id = 2 where object_id = 1; 1-> modify-> 2

1 row updated.

LEO1 @ LEO1> select. name, B. value from v $ statname a, v $ mystat B where. STATISTIC # = B. STATISTIC # and. NAME = 'redo size ';

NAME VALUE

Certificate ---------------------------------------------------------------------------------------------------------------------------------------------

Redo size 2362992

LEO1 @ LEO1> select 2362992-2362280 from dual; this is the amount of redo generated

2362992-2362280

-------------------------

712

3. at this time, we want to change 2 to 3, or copy 2 to the undo block for rollback. This is the second pre-image, and the undo block changes generate redo records.

4. Modify 2 in A to 3, and A changes generate redo records. Currently, 3 is stored in.

LEO1 @ LEO1> update leo2 set object_id = 3 where object_id = 2; 2-> modify-> 3

1 row updated.

LEO1 @ LEO1> select. name, B. value from v $ statname a, v $ mystat B where. STATISTIC # = B. STATISTIC # and. NAME = 'redo size ';