Overview of Oracle Rollback and revocation

One, rollback (ROLLBACK) and undo (undo)

Rollback and roll forward are important means of ensuring that data in an Oracle database is in a consistent state.

Before the 9i version

Oracle uses rollback segments in the database to implement uncommitted data or rollback operations when a system failure causes an instance to crash

Each table space needs to create a rollback segment and each table space implements its own management of the rollback segment

In 9i and subsequent versions

Provides a new way to manage rollback data by using the automated Oracle Managed Undo Table Space

Automatic undo Management table Space Unified management of all DML rollback operations simplifies management of rollback work

The rollback segment in i,10g is used only to preserve backward compatibility

The undo segment replaces the rollback segment in the original version, so all of the descriptions in this article use the Undo

The essence of the revocation means that the modification is returned to the state before the modification, that is, to reverse all DML statements

Ii. the contents and related characteristics of the cancellation section

For any DML operation, both the data block and the undo block must be processed, and the redo information will also be generated

In acid, A, C, I require a build undo, D requires a build redo

INSERT:

The Undo segment record is inserted into the rowid of the record, and if it needs to be revoked, the record is deleted according to ROWID

UPDATE:

The undo segment record is updated with the original value of the field and the original value is overwritten when the new value is revoked

DELETE:

Undo segment record entire row of data, reverse operation when undo insert the record into the original table

From the above, the contents of the Undo section are summarized as follows:

Data is a copy before modification

From each transaction that changes data

is kept until the end of the transaction

The role of data in the Undo segment:

For rolling back operations

Read consistency and flash-back queries

For recovery when a transaction fails

Undo segment and Transaction:

When a thing is started, Oracle assigns it just one undo segment, and if the segment is exhausted, Oracle automatically adds another interval for the undo segment (extent)

A undo segment can serve multiple transactions at the same time

Undo segment and Undo table space:

The contents of the undo segment are stored in the Undo table space

Only one Udno table space can be used at any given time

The Undo table space must be created as a persistent, locally managed, automatically extensible table space

The Undo table space being used cannot be undone or deleted

The Undo table space is written in a circular way, similar to that of an online log file, with the difference that undo_retention retention time can be set in Undo

Two ways to manage the Undo segment:

Auto Management (recommended)

MANUAL Manual Management (reserved only)

Iii. several parameters related to the cancellation

--View the version of Oracle in this computer

Sql> SELECT * from V$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-prod

Pl/sql Release 10.2.0.1.0-production

CORE 10.2.0.1.0 Production

TNS for Linux:version 10.2.0.1.0-production

Nlsrtl Version 10.2.0.1.0-production

--View and undo-related parameters

Sql> show PARAMETER undo;

NAME TYPE VALUE

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

Undo_management string MANUAL

Undo_retention Integer 900

Undo_tablespace string UNDOTBS1

Undo_management:

Set whether the undo segment of the database uses automatic management mode, the value can be auto or manual, and the undo segment will not be used when manual, that is, not using Automatic management mode

This parameter is a static parameter and must be restarted to take effect after modification

Undo_retention:

Specifies the time, in seconds, that the undo segment data is retained before it is overwritten in the undo segment. Effective when undo_management bit auto, for dynamic parameters

Undo_tablespace:

Specifies which tablespace to use to implement data revocation, which takes effect when undo_management bit auto, for dynamic parameters

Retention GUARANTEE Clause:

Make sure to undo the reservation and use the following actions to implement the

ALTER tablespace undo_tablespace_name RETENTION GUARANTEE;

--The following query is the result set when Undo_management is manual, it can be seen that the undo section of the undo Tablespace is in the offline state

Sql> SELECT segment_name,tablespace_name,status from Dba_rollback_segs;

Segment_name Tablespace_name STATUS

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/