Oracle Undo Learning

Rollback segmentIt can be said that it is a disk storage area that provides consistent read and transaction integrity to maintain the image before data changes. When a transaction starts, it first writes the data before and after the change to the log buffer, and then writes the data before the change to the rollback segment, last modified in the data buffer (the log buffer content may be written to the disk after certain conditions are met, but the log must be written to the disk when the transaction is committed, the data in the data buffer depends on the occurrence of checkpoints and the activity of the DBWR process)

Rollback is an expensive operation. If the transaction Rollback rate of a system is too high, check whether the system is normal or whether there is a problem with the program design philosophy. Query the database startup Dependencies

Transaction rollback rate, if found too high, must be paid attention.

-- SQL statement for querying the rollback Rate

Select name, VALUE
FROM v $ sysstat
Where name in ('user commits ', 'transaction rollbacks ');

For data of rollback segments, if it is a delete operation, the rollback segments record the data of the entire row. If it is an update, only the data before the changed field is recorded (before the image); if the data is inserted, only the rowid of the inserted record is recorded. Therefore, if commit is used, the rollback segment simply indicates that the transaction has been committed; If rollback is used, the operation is delete, and the data in the rollback segment is rewritten back to the data block, if the operation is update, the data before the change is modified back. If the operation is insert, the record is deleted based on rowid. This process ensures transaction integrity and prevents data loss.

Consistent read(Consisitent reads) the query set of Oralce is determined based on the time point. Oracle uses the system change number SC as the standard for relative time points, and any changes to the database will generate SCN, when the data in the data block is changed, the SCN corresponding to the change is recorded in the block. If the SCN is T at the beginning of the query, then in the data block scanned by the query, if the commit scn of the data block contains T, the query accepts the data, if the commit scn is greater than T or no commit scn is generated, the query attempts to find data in the rollback segment. This ensures the consistency of data reading time points.

When data is obtained through a rollback segment, the data block in the data buffer is essentially copied and the content recorded in the rollback segment is restored to this block, then the query uses this block for reading.

System rollback segments and delayed rollback segments

The SYSTEM rollback segment is created in the SYSTEM tablespace and is mainly used for SYSTEM-level transactions and allocating common transactions on other rollback segments. Before creating a common rollback segment, you must first create a system rollback segment. According to the oracle documentation, system rollback segments may be used when there are many exceptions in normal transactions. Under normal circumstances, the system rollback segment is mainly used in two aspects: first, the system transaction is not as good as the truncate table and drop table for data dictionary operations. If the truncate or drop table operation fails, the system rolls back the DDL operation based on the data dictionary operation information in the system rollback segment. Another aspect is the delay Rollback Segment (Deferred Rollback Segment ). The delayed rollback segment indicates that when a tablespace is OFFLINE and the tablespace is unavailable, if transaction data is in this space and the rollback command is executed, it seems that the transaction has been rolled back on the client, but the rollback has not been completed for the data block, at this time, the database writes the rollback information to the system rollback segment (this is the delay rollback segment). When the tablespace is ONLINE again, the data block writes the rollback information from the system rollback segment to the tablespace.

Configure and manage rollback segments

You need to manually set the rollback segment before Oracle9i. We need to consider the following issues:

1. Number of concurrent transactions in the system
2. Does the system have large queries or large transactions? Is it frequent?
3. What is the disk space that can be provided to the tablespace of the system rollback segment?

 

9iUndo tablespace

From 9i, we recommend that you use undo tablespace to automatically manage rollback segments.

SQL> show parameter undo
Undo_management string AUTO
Undo_retention integer 900
Undo_tablespace string UNDOTBS1

Undo tablespace has changed a lot and we cannot shrink it down. In this case, we need to consider creating a new undo tablespace and changing it to a new TABLESPACE. In this case, you can switch the UNDO tablespace even if there are transactions, but you cannot immediately delete the tablespace. After the switchover, wait until all the transactions in the original tablespace have finished and reached the undo_retention time, you can drop the original UNDO tablespace.

SQL> alter system set undo_tablespace = undotbs02;

System altered.

After switching the UNDO tablespace, you should modify the pfile or spfile so that the new UNDO tablespace will be started next time.

 

Rollback segment famous ORA-01555Problem

ORA-01555 from the application perspective

1. the query execution time is too long. First, optimize the query, and then consider running when the data block is not busy, and finally consider increasing the rollback segment.

2. Frequent submission. Change a single transaction that can be submitted in batches to batch commit.

3. consistent = y. this parameter is mainly used to ensure the consistency of all the tables everywhere at the time point during exp, avoid tables with primary/foreign key relationships that compromise data integrity due to inconsistent time points. We recommend that you perform this operation when the system is idle.

4. data cannot be found in the rollback segment even if the rollback segment is not used cyclically due to rollback segment rollback. You can only increase the rollback segment to the optimal setting.

 

UNDO scripts

Query the size of the rollback segment used by the current session of a data block.

SELECT B. SID, a. xidusn, a. xidusn
FROM v $ transaction a, v $ session B
WHERE a. addr = B. taddr

 

Restoration Method for loss or damage of a data file in the tablespace of the rollback segment

................

Because the corresponding undo block/or undo header slot information is overwritten, resulting in consistent read cannot be completed, the system will report an error message for the ora-01555 ..

There may be the following reasons for this situation.

1. Early manual undo may be caused by the system setting optimal size and rollback segment wrap, which may cause some undo information to be discarded ..

2. Since the undo retention setting time is less than the SQL Execution length, the undo generated by other sessions is overwritten due to the retention, resulting in the failure to complete the consistent read.

3. As the commit over fetch loop. causes the transaction slot to be overwritten, consistent read cannot be completed ..

ORA-01555 Error Analysis-http://www.hellodba.com/Doc/ORA-01555_analysis (1).htm

Summarize the following methods to solve the 1555 error:

1. Expand the rollback segment

Because rollback segments are used cyclically. If the rollback segments are large enough, the committed data information can be saved for a long enough time to complete consistent reading for those large transactions.

2. added the undo_retention time.

No other transactions can overwrite the data within the time specified by undo_retention.

3. Optimize related query statements to reduce consistent reads.

Reduce consistent reads of query statements, thus reducing the risk of failing to read rollback segments. This is very important!

4. Reduce unnecessary transaction commit

The less transactions are committed, the less rollback segment information is generated.

5. Specify rollback segments for large transactions

You can use the following statement to specify the rollback segment of a transaction:

SET TRANSACTION USE ROLLBACK SEGMENT rollback_segment；

Specifying a rollback segment for a large transaction reduces the probability that the rollback information of a large transaction will overwrite the rollback information of other transactions, and reduces the probability that the rollback information of the large transaction will be overwritten. The existence of large transactions is often the cause of the 1555 error.

6. When using a cursor, try to use an explicit cursor as much as possible, and open the cursor only when necessary, and take out all the operations that can be done outside the cursor from the cursor loop.

When the cursor is opened, the query starts until the cursor is closed. Reduce the cursor opening time to reduce the probability of 1555 errors.