一 為了更清楚的看出2者差別，請(qǐng)看下表：

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 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

簡(jiǎn)單看來(lái)，UNDO主要記錄怎樣撤銷(xiāo)事務(wù)和保證讀一致性；REDO則是負(fù)責(zé)數(shù)據(jù)庫(kù)前滾（重做）。保護(hù)數(shù)據(jù)不丟失。

?二 以下我們來(lái)通過(guò)實(shí)例說(shuō)明undo 和 redo的關(guān)系：

1 我們將證明下面事實(shí)：

- oracle 中redo包括undo；

-?checkpoint 會(huì)導(dǎo)致臟數(shù)據(jù)寫(xiě)入datafile；

-?buffers 會(huì)被寫(xiě)入當(dāng)前的undo 表空間

2 操作步驟：

- 創(chuàng)建1個(gè)undo表空間：undotbs2 - 創(chuàng)建1個(gè)表空間：test_undo - 在表空間test_undo創(chuàng)建表：test_undo_tab （txt char(1000)） - 向表test_undo_tab插入2條記錄txt – teststring1, teststring2。運(yùn)行手工checkpoint操作 - 手工日志切換、切換undo 表空間 - 更新teststring1為teststring_uncommitted而且不提交 - 新開(kāi)一個(gè)session 更新?teststring2為teststring_uncommitted而且提交 - 檢查update前后的值都被記錄在當(dāng)前redo log中 - 檢查undo 表空間不包括更新之前的值 - 進(jìn)行手工checkpoint，這樣undo信息將被寫(xiě)入磁盤(pán) - 檢查undo 表空間包括更新前的值

3 詳細(xì)實(shí)現(xiàn)：

?- 查找當(dāng)前undo表空間
SQL> show parameter undo_tablespaceNAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_tablespace string UNDOTBS1
- 創(chuàng)建Undo表空間 undotbs2：
SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/undotbs2.dbf'2 size 100m;Tablespace created.
- 創(chuàng)建表空間 test_undo SQL> create tablespace test_undo datafile '/u01/app/oracle/test_undo.dbf'2 size 128k;Tablespace created.

- 創(chuàng)建測(cè)試表 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;
- 運(yùn)行手工檢查點(diǎn)。將以上改變寫(xiě)入數(shù)據(jù)文件：
SQL> alter system checkpoint;System altered.
- 設(shè)置undotbs2為當(dāng)前undo表空間：
SQL> alter system set undo_tablespace=undotbs2;System altered.SQL> show parameter undo_tablespace;NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_tablespace string UNDOTBS2
- 進(jìn)行日志切換使當(dāng)前日志不包括字符串teststring
SQL> alter system switch logfile;System altered.

- 查找當(dāng)前日志
SQL> col member for a30 SQL> select member, l.status from v$log l, v$logfile f2 where l.group# = f.group#3 and l.status = 'CURRENT';MEMBER STATUS ------------------------------ ---------------- /u01/app/oracle/oradata/orcl/r CURRENT edo02.log

- 更新測(cè)試表中一行而且不提交
SQL> update test_undo_tab set txt = 'teststring_uncommitted'2 where txt = 'teststring1';1 row updated.
- 新開(kāi)一個(gè)session 更新另外一行而且提交
SQL> update test_undo_tab set txt = 'teststring_committed'where txt = 'teststring2';commit;

- 查看這時(shí)候的redo log應(yīng)該包括redo 和 undo （提交的和未提交的數(shù)據(jù)信息）
[oracle@dylan ~]$ strings /u01/app/oracle/oradata/orcl/redo02.log | grep teststring teststring_uncommitted teststring1 teststring_committed teststring2 - 檢查當(dāng)前數(shù)據(jù)文件應(yīng)該是不包括更新后的數(shù)值（僅僅有更新前數(shù)據(jù)）由于還未觸發(fā)檢查點(diǎn)
[oracle@dylan ~]$ strings /u01/app/oracle/test_undo.dbf | grep teststring
teststring2 teststring1

- 此時(shí)觸發(fā)檢查點(diǎn)
SQL> alter system checkpoint;
- 再次檢查數(shù)據(jù)文件發(fā)現(xiàn)數(shù)據(jù)已為最新值（提交的和未提交的值）
[oracle@dylan ~$ strings /u01/app/oracle/test_undo.dbf|grep teststringteststring_committed , teststring_uncommitted


- 最后檢查Undotbs2表空間發(fā)現(xiàn)包括更新前的數(shù)值 [oracle@dylan ~]$ strings /u01/app/oracle/undotbs2.dbf | grep teststringteststring2 teststring1
- 清理創(chuàng)建的對(duì)象 SQL>drop tablespace test_undo including contents and datafiles;alter system set undo_tablespace=undotbs1;drop tablespace undotbs2 including contents and datafiles;

三 進(jìn)一步探討：

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

假設(shè)僅將undo信息存儲(chǔ)于redo logs會(huì)怎么樣？

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 ?假設(shè)我改變的數(shù)據(jù)而沒(méi)提交。此時(shí)改變將記錄到redo log
- checkpoint takes place ?檢查點(diǎn)發(fā)生
- uncommitted change is written to datafile ?后未提交的數(shù)據(jù)寫(xiě)入了數(shù)據(jù)文件
- I decide to rollback the change ?這時(shí)我打算回滾
- If redo log has not been overwritten ?假設(shè)redo log沒(méi)被覆蓋
. search entire redo log for the undo and then rollback ?那么搜素整個(gè)redo log進(jìn)行回滾操作
else (redo log has been overwritten)
. undo information is not available for rollback. ? ?否則將無(wú)法回滾，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?

有人或許會(huì)爭(zhēng)論：那就不同意redo log 覆蓋undo 信息直到包括新的undo，這樣redo log將變得異常大從而影響系統(tǒng)性能！

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 ? ? ?閃回查詢(xún)
- flashback version query ? 閃回版本號(hào)查詢(xún)

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









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

創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎(jiǎng)勵(lì)來(lái)咯，堅(jiān)持創(chuàng)作打卡瓜分現(xiàn)金大獎(jiǎng)

總結(jié)

以上是生活随笔為你收集整理的Oracle 中UNDO与REDO的差别具体解释的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。