程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle中UNDO與REDO的區別詳解

Oracle中UNDO與REDO的區別詳解

編輯：Oracle教程

一為了更清楚的看出2者區別，請看下表：

UNDO REDO
Record of How to undo a change How to reproduce a change Used for Rollback, Read-Consistency Rolling forward DB Changes Stored in Undo segments Redo log files Protect Against Inconsistent reads in multiuser systems Data loss
簡單看來，UNDO主要記錄如何撤銷事務和保證讀一致性；REDO則是負責數據庫前滾（重做），保護數據不丟失。

二下面我們來通過實例說明undo 和 redo的關系：

1 我們將證明以下事實：

- oracle 中redo包含undo；

- checkpoint 會導致髒數據寫入datafile；

- buffers 會被寫入當前的undo 表空間

2 操作步驟：

- 創建1個undo表空間：undotbs2 - 創建1個表空間：test_undo - 在表空間test_undo創建表：test_undo_tab （txt char(1000)） - 向表test_undo_tab插入2條記錄txt – teststring1, teststring2，執行手工checkpoint操作 - 手工日志切換、切換undo 表空間 - 更新teststring1為teststring_uncommitted並且不提交 - 新開一個session 更新 teststring2為teststring_uncommitted並且提交 - 檢查update前後的值都被記錄在當前redo log中 - 檢查undo 表空間不包含更新之前的值 - 進行手工checkpoint，這樣undo信息將被寫入磁盤 - 檢查undo 表空間包含更新前的值

3 具體實現：

- 查找當前undo表空間

SQL> show parameter undo_tablespace


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1

- 創建Undo表空間 undotbs2：

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/undotbs2.dbf'
  2  size 100m;

Tablespace created.

- 創建表空間 test_undo

SQL> create tablespace test_undo datafile '/u01/app/oracle/test_undo.dbf'
  2  size 128k;

Tablespace created.

- 創建測試表 test_undo_tab：

SQL> create table test_undo_tab(txt char(1000)) tablespace test_undo;

Table created.

SQL> insert into test_undo_tab values ('teststring1');

1 row created.

SQL> insert into test_undo_tab values ('teststring2');

1 row created.

SQL> commit;

- 執行手工檢查點，將以上改變寫入數據文件：

SQL> alter system checkpoint;

System altered.

- 設置undotbs2為當前undo表空間：

SQL> alter system set undo_tablespace=undotbs2;

System altered.

SQL> show parameter undo_tablespace;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS2

- 進行日志切換使當前日志不包含字符串teststring

SQL> alter system switch logfile;


System altered.

- 查找當前日志

SQL> col member for a30
SQL> select member, l.status from v$log l, v$logfile f
  2  where l.group# = f.group#
  3  and l.status = 'CURRENT';

MEMBER                         STATUS
------------------------------ ----------------
/u01/app/oracle/oradata/orcl/r CURRENT
edo02.log

- 更新測試表中一行並且不提交

SQL> update test_undo_tab set txt = 'teststring_uncommitted'
  2  where txt = 'teststring1';

1 row updated.

- 新開一個session 更新另外一行並且提交

SQL>  update test_undo_tab set txt = 'teststring_committed'
      where txt = 'teststring2';
      commit;

- 查看這時候的redo log應該包含redo 和 undo （提交的和未提交的數據信息）

[oracle@dylan ~]$  strings /u01/app/oracle/oradata/orcl/redo02.log | grep teststring

teststring_uncommitted                                                                                  
teststring1                                                          

teststring_committed                                                 

teststring2

- 檢查當前數據文件應該是不包含更新後的數值（只有更新前數據）因為還未觸發檢查點

[oracle@dylan ~]$ strings /u01/app/oracle/test_undo.dbf | grep teststring

teststring2                                                                  
teststring1

- 此時觸發檢查點

SQL> alter system checkpoint;

- 再次檢查數據文件發現數據已為最新值（提交的和未提交的值）

[oracle@dylan ~$ strings /u01/app/oracle/test_undo.dbf|grep teststring

teststring_committed                                                                                                               ,
teststring_uncommitted

- 最後檢查Undotbs2表空間發現包含更新前的數值

[oracle@dylan ~]$ strings /u01/app/oracle/undotbs2.dbf | grep teststring

teststring2                                                                  
teststring1

- 清理創建的對象

SQL>drop tablespace test_undo including contents and datafiles;
    alter system set undo_tablespace=undotbs1;
    drop tablespace undotbs2 including contents and datafiles;

三進一步探討：

Let’s see what will happen if undo is stored in redo logs only.

如果僅將undo信息存儲於redo logs會怎麼樣？

A redo log can be reused once changes protected by it have been written to datafiles (and archivelogs if database is in archivelog mode).

It implies that if I make a change and do not commit it
- Change is written to a redo log 如果我改變的數據而沒提交，此時改變將記錄到redo log
- checkpoint takes place 檢查點發生
- uncommitted change is written to datafile 後未提交的數據寫入了數據文件
- I decide to rollback the change 這時我打算回滾
- If redo log has not been overwritten 如果redo log沒被覆蓋
. search entire redo log for the undo and then rollback 那麼搜素整個redo log進行回滾操作
else (redo log has been overwritten)
. undo information is not available for rollback. 否則將無法回滾，undo信息已丟失！

One might argue that if somehow a redo log is not allowed to be overwritten until it contains active undo, we might be able to manage with undo stored in redo logs only. This solution is not feasible as
- size of redo logs will grow enormously large very soon as thet contain both undo and redo (a user might decide not to end a transaction for months)
- to rollback a change, enormous amount of data in redo logs (both redo and undo) will have to be searched leading to degraded performance
- there will be contention on redo logs as they are being used for both
. writing redo and undo
. reading to rollback a change

有人也許會爭論：那就不允許redo log 覆蓋undo 信息直到包含新的undo，這樣redo log將變得異常大從而影響系統性能！

Hence, undo information has to be stored separately from redo and is used for rolling back uncommited transactions . The undo stored in undo buffers/undo tablespace is additionally used for
- read consistency 讀一致性
- flashback query 閃回查詢
- flashback version query 閃回版本查詢

Reference： http://oracleinaction.com/undo-and-redo-in-oracle/ http://oraclenz.wordpress.com/2008/06/22/differences-between-undo-and-redo/

--------------------------------------- Dylan Presents.