Oracle getting started Tutorial: Redo and Undo differences and contact details

Redo and Undo

• Redo and its Functions
• Undo and its Functions
• Relationship between Redo and Undo
• Submit and roll back
• LogMiner-based Log Analysis

1

1.1 Redo and its Functions

1.1.1Redo is used to restore committed transactions, so as to ensure that the committed transactions can be restored no matter when the media fails or the instance fails, so that the database is in a consistent state.

1.1.2the implementation of the redo function depends on the following three components:

• -Log Buffer
• -LGWR
• -Online redo log files and archive log files

1.2 Redo data volume measurement

1.2.1 use SQL * Plus's built-in automee function for Statistics (only for simple DML operations, and cannot count the log information of complex operations such as stored procedure calls ):

1. SetAutotraceOn Statistics
2. UpdateBig_table
3. SetObject_name =Lower(Object_name)
4. WhereRownum <10;

1.2.2. Search by using the dynamic performance view v $ mystat. For example:

1. SelectA.Name, B. value
2. FromV $ statname a, v $ mystat B
3. WhereA. statistic # = B. statistic #
4. And Lower(.Name)Like 'Redo size';

2 Undo and its Functions

2.2.1Undo:-rollback operation;-read consistency supported;-recovery of failed transactions. 2.2.2Undo is implemented through the Undo segments in the undo tablespace. Each transaction is assigned only one undo segment, which can serve multiple transactions. 2.2.3Undo only logically restores data to the state before modification, rather than physically recovering data. The statement uses the following dynamic performance view to query the number of undo blocks used by the current TRANSACTION: V $ MYSTAT: Get session id; V $ SESSION: Get TRANSACTION address; V $ TRANSACTION: find the number of undo blocks used by the transaction. When a transaction is committed or rolled back, the space occupied by the undo block is released.

1. SelectUsed_ublk
2. FromV $Transaction
3. WhereAddr = (
4. SelectTaddr
5. FromV $ session
6. WhereSid = (
7. SelectSid
8. FromV $ mystat
9. WhereRownum = 1
10. )
11. );

9.3 comparison between Redo and Undo

	Undo	Redo
Record	How to restore and modify	How to create and modify
Used	Rollback and read consistency	Rollback and database Modification
Stored in	Undo segment	Redo log file
Protection	Read consistency in multi-user systems	Lost Data

 

3.4 The undo management goals are to avoid two errors: vORA-01650: unable to extend rollback segment vORA-01555: snapshot too old management implementation to correctly configure three parameters and a restored tablespace property: UNDO_MANAGEMENT UNDO_TABLESPACE UNDO_RETENTION retention guarantee 9.5 The time required to submit and roll back the commit operation is "flat" and has no direct relationship with the transaction size. The time required for the rollback operation is directly related to the volume of data modified by the transaction. Therefore, when designing a transaction, do not consider the transaction size and quantity, but should be based on whether the executed operation constitutes a logical unit of work. Operations that can be completed before and after submission operations that can be completed before submission-generate UNDO blocks in SGA-generate modified data blocks in SGA-generate redo logs corresponding to the first two items in SGA -If necessary, some of the preceding data may have been refreshed and output to the disk-operations to be completed when all required locks are submitted-SCN-LGWR is generated for the transaction to redo all the remaining cache logs and SCN is written to the log file. -Release lock-operations that can be performed before and after block cleaning and rollback of data blocks in the buffer zone v operations that can be completed before rollback-generate UNDO blocks in SGA-generate modified blocks in SGA -generate the redo log corresponding to the first two items in SGA-if needed, some of the preceding data may have been refreshed and output to the disk-the operation to be completed when getting all required locks v rollback-undo all the modifications made-Release the lock