Basic Oracle tutorial-data restoration Management

UNDO tablespace Management

1. For DML statements, as long as the data block is modified, the Oracle database retains the data block before the modification and stores it in the undo segment, the undo segment is stored in the undo tablespace.

2. undo Management

Automatic undo Management (starting with Oracle9i) AUM

Manual undo MUM Management

We recommend that you use AUM after 9i, so we will not discuss MUM any more.

Execution Process of a dml statement update t set coll = 'A' where coll = 'B'

1. parse in the shared pool to generate an execution plan

2. According to the execution plan, the records of coll = 'B' are obtained and stored in the No. 54 data block of the No. 10 data file.

3. The server process first looks for an available undo data block in the buffer cache (if a transaction has been committed, the undo data block used by this transaction can be used ), if no undo data block is found, find an available undo data block in the undo tablespace and transfer it to the buffer cache. Assume that the obtained undo data block number is 24 and is located in the undo data file no. 11.

4. Put the change value (that is, B) into the undo data block (buffer cache) on the 24th)

5. As the undo data block changes (as long as the data block changes, a redo record is generated), a redo record is generated. Assume that the redo record number is 120.

650) this. width = 650; "border = 0>

6. Find the No. 54 data block in the buffer cache. If not, transfer it from the No. 10 data file.

7. Place the changed value, that is, A, into the 54 th data block.

8. A redo record is generated because the data block has changed. Assume that the redo record number is 121.

650) this. width = 650; "border = 0>

9. Control is returned to the user. If SQLPLUS is used, the cursor returns

10. The user issues the commit command to trigger LGWR and write the redo records 120 and 121 to the online redo log file, set the transaction status tag recorded in the headers of data blocks 54 and 24 to submitted, and the control is returned to the user. If SQLPLUS is used, the cursor is returned.

11. At this time, data blocks 54 and 24 are not necessarily written into the data file by DBWr. They are written only when the number of dirty data blocks reaches a certain level.

After a transaction is committed, the undo data block used by the transaction can be overwritten. In the preceding example, after the user commits the transaction in step 1, the undo data block on the 24 th can be overwritten.

Role of Undo

1. Read-Only execution

2. roll back the transaction

3. instance recovery

Read consistency

Scenario Description

Read consistency is relative to dirty reads. Table T contains 10000 records. It takes 15 minutes to obtain all the records. The current time is nine o'clock. The user issues a select * from T command, the statement is completed. When the user executes this statement until, another user issues a DELETE command to delete the last record and submits it.

By 09:15, the number of records returned by the user.

If there are 9999 rows, It is dirty reading. If there are 10000 rows, it is read consistency.

Oracle does not have dirty reads, provides read consistency, and does not block DML operations

How Does Oracle achieve read consistency?

1. When a user issues a select statement at, the server process will record the SCN number (SCN number is a function return value that uses time (timestamp) as the parameter, call a function (with timestamp as the parameter by default) to return the SCN number at any time. You can use the function to convert between SCN and timestamp. Assume that the SCN number is scloud: 00, scloud: 00 must be greater than or equal to the SCN number recorded in the ITL slot of all data block headers (if there are multiple ITL slots, the largest SCN)

2. When the server process scans the T table, it compares the SCN number in the ITL slot of the scanned data block header with scloud: 00, which is larger. If the SCN of the data block header is smaller than scloud: 00, it indicates that the data block has not been changed since and can be directly read. If the SCN Number of the data block header is greater than scloud: 00, it indicates that the data block was changed after. It is no longer the data at, so we need to use the undo Block

3. At 09:10, the user changed the last record of table T and submitted it (whether or not the table T was submitted or not, the user will read the undo data block ), assume that the number N data block is changed, then the SCN recorded in the ITL slot of the number N data block header is changed to scloud: 10, when the server process scans the data block, it finds that scloud: 10 in the ITL slot is greater than scloud: 00, indicating that the data block was updated after, so the server process goes to the header of Block N and finds the ITL slot where scloud: 10 is located. Because the ITL slot records the address of the corresponding undo block, the server process finds the undo data block, use undo data blocks to provide read consistency.

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12

1 2 3 4 5 6 Next Page