Summary of Oracle 11g undo_retention and retention guarantee

Summary of Oracle 11g undo_retention and retention guarantee

Undo records the pre-image of the modified data block, but it is not the copy of the original data block, but a change vector. The real consistent read requires the use of CR blocks, the Cr block is the consistent read block, which is used to maintain the read consistency of Oracle. When querying some data, it is found that the version of the data block is newer than the one we want to query. For example, session1 has executed the dml operation and has not submitted it, session2 queries the data of dml operations related to session1, and the data queried is the original data information.
The Query Process searches for the front image of the data block in the undo segment, and then merges the front image with the current block to form a CR block, by querying the cr block, Data Consistency can be satisfied.

CR block exists in the buffer cache of sga. Apply for a data block (current block) in the db cache, and then generate a cr block with the pre-image of the corresponding rollback segment.

Undo parameters:

SQL> show parameter undo

NAME TYPE VALUE

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

Undo_management string AUTO

Undo_retention integer 900

Undo_tablespace string UNDOTBS1

Oracle automatically manages undo tablespace by default from 11 GB. If you do not specify the undo_tablespace parameter, that is, there is no undo tablespace, oracle will put the pre-Modified Image of the block into the system tablespace, in addition, an alarm will be triggered in the alert Log: database running without an undo tablespace.

The following describes undo_retention.

We know that undo segments has four extents states: free, active, inacitve, and expired.
SQL> select SEGMENT_NAME, TABLESPACE_NAME, STATUS from dba_undo_extents;

SEGMENT_NAME TABLESPACE_NAME STATUS
---------------------------------------------------------------------
_ SYSSMU10_3534552179 $ UNDOTBS1 EXPIRED
_ SYSSMU10_3534552179 $ UNDOTBS1 EXPIRED
_ SYSSMU10_3534552179 $ UNDOTBS1 UNEXPIRED
_ SYSSMU10_3534552179 $ UNDOTBS1 EXPIRED
_ SYSSMU10_3534552179 $ UNDOTBS1 EXPIRED
_ SYSSMU10_3534552179 $ UNDOTBS1 EXPIRED
_ Syssmu9_00003992930 $ UNDOTBS1 EXPIRED
_ Syssmu9_00003992930 $ UNDOTBS1 EXPIRED
_ Syssmu9_00003992930 $ UNDOTBS1 EXPIRED

1) free: No segments are allocated.
2) active: transactions in the zone are not committed.
3) inactive: the transaction in the partition has been committed, but it has not reached the undo_retention time.
4) expired: the transaction is committed and reaches undo_retention.
Note: We can set undo_retention to keep the inactive zone. If there is no free zone, it will be automatically extended; if it cannot be extended, expired will be used first; if it is not enough, inactive will be used, however, if retention is guaranteed by guarantee at this time (that is, alter tablespace undotbs1 retention guarantee), inactive cannot be used and a ORA-30036 is reported.
Conclusion: If you tend to ensure data consistency, that is, to focus on queries, you need to use alter tablespace undotbs1 retention guarantee to ensure consistency, that is, whether you have enough space, you cannot use inactive zones, which may lead to database hang because there is no available undo space, however, this ensures the consistency of all queries whose query statement execution time is within the undo_retention value. If your business tends to be transaction, you can not set retention guarantee, so that when there is no available undo space, you can overwrite the inactive state of the zone, so that there may be an error in the ora-01555, it cannot be read in the same way, because your undo_retention value is determined by the longest execution time of the query statement for your current business, that is, the longest execution time of undo_retention> SQL, therefore, your use of GUARANTEE is dependent on your business needs.