Oracle中UNDO与REDO的区别详解

一 为了更清楚的看出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： https://oracleinaction.com/undo-and-redo-in-oracle/ https://oraclenz.wordpress.com/2008/06/22/differences-between-undo-and-redo/

--------------------------------------- dylan presents.