Oracle redo 与 undo

Oracle数据库中最重要的两部分数据：redo与undo。redo(重做信息)是oracle 在线（或归档）重做日志文件中记录的信息，万一出现失效时可以利用这些数据来“重放”（或重做）事务。

undo(撤销信息)是Oracle在undo段中记录的信息，用于取消或回滚事务。

1 什么是redo

重做日志文件（redo log file）对Oracle数据库来说至关重要。Oracle维护着两类重做日志文件：

在线（online）重做日志文件和归档（archived）重做日志文件。 都用于恢复。

其主要目的是：万一实例失效或者介质失败，它们就能派上用场。

归档重做日志文件实际上就是已填满的”旧“在线重做日志文件的副本。

2 什么是undo

对数据库执行修改时，数据库会生成undo信息。以便回到更改前的状态。

redo用于在失败时重放事务（恢复事务），undo则用于取消一条语句或一组语句的作用。

与redo不同，undo在数据库内部存储在一组特殊的段中，这称为undo段（undo segment）

怎么才能看到undo生成（undo generation)的具体情况呢？

（1）创建一个空表

（2）对它做一个全表扫描，观察读表所执行的I/O数量。

（3）在表中填入许多行（但没有提交）

（4）回滚这个工作、并撤销。

（5）再次进行全表扫描，观察所执行的I/O数量。

scott@orclpdb1:orclcdb> create table t
  2  as
  3  select * 
  4    from all_objects
  5  where 1=0;

Table created.

scott@orclpdb1:orclcdb> set autotrace on
scott@orclpdb1:orclcdb> select * from t;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   481 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |     1 |   481 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         96  recursive calls
        181  db block gets
        148  consistent gets
          1  physical reads
      30504  redo size
       2375  bytes sent via SQL*Net to client
        366  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed

scott@orclpdb1:orclcdb> set autotrace traceonly statistics
scott@orclpdb1:orclcdb> select * from t;

no rows selected


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       2375  bytes sent via SQL*Net to client
        366  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

scott@orclpdb1:orclcdb> 
scott@orclpdb1:orclcdb> insert into t select * from all_objects;

54936 rows created.


Statistics
----------------------------------------------------------
       1377  recursive calls
       9467  db block gets
     105040  consistent gets
        304  physical reads
    9009588  redo size
        494  bytes sent via SQL*Net to client
        937  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         31  sorts (memory)
          0  sorts (disk)
      54936  rows processed

scott@orclpdb1:orclcdb> rollback;

Rollback complete.

scott@orclpdb1:orclcdb> select * from t;

no rows selected


Statistics
----------------------------------------------------------
          2  recursive calls
          1  db block gets
       1134  consistent gets
          0  physical reads
        140  redo size
       2375  bytes sent via SQL*Net to client
        370  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

scott@orclpdb1:orclcdb> set autotrace traceonly statistics
scott@orclpdb1:orclcdb> select * from t;

no rows selected


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1118  consistent gets
          0  physical reads
          0  redo size
       2375  bytes sent via SQL*Net to client
        370  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

scott@orclpdb1:orclcdb> 
scott@orclpdb1:orclcdb> 
scott@orclpdb1:orclcdb> create table t (x int)
  2  ;
create table t (x int)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


scott@orclpdb1:orclcdb> drop table t;

Table dropped.

scott@orclpdb1:orclcdb> create table t(x int);

Table created.

scott@orclpdb1:orclcdb> select extent_id, bytes, blocks
  2  from user_extents
  3  where segment_name='T'
  4  order by extend_id;
order by extend_id
         *
ERROR at line 4:
ORA-00904: "EXTEND_ID": invalid identifier


scott@orclpdb1:orclcdb> select extent_id, bytes, blocks
  2  from user_extents
  3  where segment_name='T'
  4  order by extent_id;

no rows selected


Statistics
----------------------------------------------------------
        252  recursive calls
          0  db block gets
        497  consistent gets
          6  physical reads
          0  redo size
        504  bytes sent via SQL*Net to client
        446  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
         14  sorts (memory)
          0  sorts (disk)
          0  rows processed

scott@orclpdb1:orclcdb> insert into t(x) values (1);

1 row created.


Statistics
----------------------------------------------------------
         39  recursive calls
         62  db block gets
         35  consistent gets
          1  physical reads
       6536  redo size
        494  bytes sent via SQL*Net to client
        925  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

scott@orclpdb1:orclcdb> rollback;

Rollback complete.

scott@orclpdb1:orclcdb> select extent_id, bytes, blocks
  2  from user_extents
  3  where segment_name='T'
  4  order by extent_id;

1 row selected.


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        135  consistent gets
          0  physical reads
          0  redo size
        709  bytes sent via SQL*Net to client
        457  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

scott@orclpdb1:orclcdb> set trace off
SP2-0158: unknown SET option "trace"
scott@orclpdb1:orclcdb> set autotrace off
scott@orclpdb1:orclcdb> select extent_id, bytes, blocks
  2  from user_extents
  3  where segment_name='T'
  4  order by extent_id;

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         0      65536          8

1 row selected.

scott@orclpdb1:orclcdb> 

可以看到，表创建之后没有分配任何存储空间——这个表没有使用任何区段。完成一个INSERT 后，紧接着执行ROLLBACK，可以看到INSERT分配了存储空间，不过ROLLBACK并没有将分配的存储空间”释放“。

总结：

• 段确实由INSERT创建但是未被ROLLBACK撤销。
• 由INSERT新创建的格式化块被第二次扫描，这两方面结合在一起，说明回滚只是一种逻辑上”将数据库恢复原状态“的操作。数据库并不会完全恢复原状，只是逻辑上相同而已。

3. redo 和undo如何协作

尽管undo信息存储在undo表空间或undo段中，但也会受到redo的保护。

意思是，会把undo数据当成是表数据或索引数据已有，对undo的修改会生成一些redo,这些redo将计入日志。将undo数据增加到undo段中，并像其他部分的数据一样，在缓冲区中得到缓存。

提交和回滚处理

4.提交和回滚处理

COMMIT做什么

Rollback做什么

5.分析redo

生成的redo越多，操作花费的时间就越长，整个系统也会越慢。

能关掉重做日志生成程序吗？

不能，因为重做日志对数据库至关重要，它不是开销，不是浪费。重做日志是实实在在必不可少的。是数据库采用的工作方式。 如果你关闭了redo,那么磁盘驱动器的任何暂时失败，掉电或某个软件崩溃都会导致整个数据库不可用。而且不可恢复。