Redo and undo of Oracle 10g Reading Notes

Redo is the information recorded in Oracle online (or archived) redo log files and used to redo transactions. undo is the information recorded in oracle's undo segment, used to cancel or roll back a transaction.

The undo operations on the database are only logical recovery to the original form, but the data structure and data block itself can be very different after rollback. The reason for this is that there may be multiple concurrent transactions at the same time.

Although the undo information is stored in the undo tablespace or undo segment, it is also protected by redo. In other words, the undo data is treated as table data or index data, and the undo changes generate some redo logs.

Oracle transaction COMMIT is a very fast operation. No matter how large the transaction is, the response time of COMMIT is almost the same. Because 99.9% has been completed before submission, for example, the following operations have been performed:

A. the undo block has been generated in SGA.
B. modified data blocks have been generated in SGA.
C. cache redo corresponding to the first two items has been generated in SGA
D. Depending on the size of the first three items and the time it takes to work, some of the previous data may have been refreshed and output to the disk.
E. All required locks have been obtained.
The rest of the work is
A. generate an SCN for the transaction. It is a simple timing mechanism used by oracle to ensure the transaction order and support failure recovery. It is also used to ensure read consistency and checkpoints in the database.
B. LGWR writes all the remaining cache redo log entries to the disk and records the SCN to the online redo log file. This is really a COMMIT. Then the TRANSACTION entry is "deleted" from V $ TRANSACTION ".
C. V $ LOCK records the locks held by our sessions. These locks will be released, and everyone waiting in the queue will be awakened, they can continue to complete their work.
D. If some blocks modified by the transaction are still in the buffer, the lock-related information stored in the database block header will be cleared.

In contrast, ROLLBACK does the following:
A. Revoke all modifications made. Read back data from the undo segment, perform the previous operation in reverse order, and mark the undo entry as used.
B. All the locks held by the session will be released. If someone waits in queue for the locks we hold, they will be awakened.

Reduce the generation of redo logs:
A. Set NOLOGGING in SQL. This does not mean that no redo log is generated during the execution of all operations on this object, but that some specific operations generate much fewer redo logs than usual.
B. Set NOLOGGING on the index. Set the NOLOGGING attribute on the segment (index or table) to use the NOLOGGING mode implicitly.
C. You can use the NOLOGGING mode to perform the following operations:
1. Create and ALTER indexes. For example, create index t_idx on t (name) nologging, alter index t_idx nologging, and alter index t_idx rebuild
2. Batch INSERT of tables. Table data does not generate redo, but all index changes generate redo.
3. LOB operation (you do not need to generate logs for updates to large objects ).
4. create table as select to CREATE a TABLE.
5. Various alter table operations, such as MOVE and SPLIT.

Block clearing: deletes information related to "locking" on the modified database block. ORA-01555: snapshot too old will be used later.
A Data lock is actually a data attribute stored in the block header. When you access this block next time, you need to delete the transaction information. This action will generate redo and cause the block to become corrupted. That is to say, SELECT may also generate redo and refresh the block to the disk.
If the block is still in the buffer, it will be cleared during the COMMIT process, so that the subsequent SELECT statements do not need to be cleared. Only the UPDATE of the block will actually clear the residual transaction information. Because the redo has been generated during the UPDATE, you are not aware of the clearing work.

Temporary tables do not generate redo For their blocks, but will generate undo, and this undo is logged.
INSERT generates few or even no undo/redo activities. Because few undo logs are generated, oracle only needs to record the rowid to be deleted.
The redo generated by DELETE on the temporary table is the same as the redo generated on the normal table. Because of its large undo, You need to record the front image of the entire row to the undo segment.
The UPDATE of the temporary table generates half of the redo of the normal table. For the temporary table, you do not need to save the "Post image" (redo ).

SQL> show parameter undo
NAME TYPE VALUE
-----------------------------------------------------------------------------
Undo_management string AUTO // automatic undo Management
Undo_retention integer 900 // undo retention time
Undo_tablespace string UNDOTBS1 // undo storage tablespace