Oracle 中UNDO與REDO的差別具體解釋

標籤：creat   cte   san   session   word   actions   into   bsp   prot   

一 為了更清楚的看出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_tablespaceNAME                                 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 a30SQL> 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 CURRENTedo02.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 （提交的和未提交的資料資訊）

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

teststring_uncommitted                                                                                  teststring1                                                          teststring_committed                                                 teststring2

- 檢查當前資料檔案應該是不包括更新後的數值（僅僅有更新前資料）由於還未觸發檢查點

[[email protected] ~]$ strings /u01/app/oracle/test_undo.dbf | grep teststring

teststring2                                                                  teststring1

- 此時觸發檢查點

SQL> alter system checkpoint;

- 再次檢查資料檔案探索資料已為最新值（提交的和未提交的值）

[[email protected] ~$ strings /u01/app/oracle/test_undo.dbf|grep teststringteststring_committed                                                                                                               ,teststring_uncommitted

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

[[email protected] ~]$ strings /u01/app/oracle/undotbs2.dbf | grep teststringteststring2                                                                  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.

Oracle 中UNDO與REDO的差別具體解釋