Oracle undo我们需要掌握什么

来源：互联网发布：spring 接收json对象编辑：程序博客网时间：2024/05/05 13:06

转载时请务必以超链接形式标明文章原始出处、作者信息和本声明。否则将追究法律责任。http://leonarding.blog.51cto.com/6045525/1167641

一大话UNDO

Hi大家好，我是Oracle 的无敌小安[中文名（small undo[英文名），首先我先来介绍一下自己吧，我诞生于一个大家庭Oracle，现在的一把手拉里.埃里森就是我的缔造者，我的诞生可谓Oracle立下了汗马功劳，使Oracle开拓缰野攻城略地！我的诞生是为了解决三个大问题，事物回滚，实例恢复，查询一致性。这样的功能使Oracle成为了关系型数据库中的佼佼者。

例如：读不会被写所阻塞，当我们读取的数据块正好被他人修改时，我们就可以找到undo段里保存的前映像来维护一致性。而其他数据库，sql server mysql等都没有undo段，也就是说它们查询时可能被阻塞。

Undo和Redo区别

好多人尤其是刚刚入门的朋友，总会把这两个概念搞混淆。

Redo：是基于安全考虑的，会记录数据库的所有变化，当数据被误修改时，使用redo可重新生成，事物重做。是前滚，就是从无到有的新创建。一个新生命的诞生。

Undo：是基于回滚的，当数据被误修改时，可以从修改的新状态回退到老状态，实现事物回滚，相当于撤销操作。是回滚，就像倒录音带一样，把走过的路反向在走一遍回到原点。

Undo目的：1.事物回滚：rollback

2.实例的恢复：掉电，死机，强制关库，把没有提交的事物全部回滚

3.查询一致性：读不会被写所阻塞

Undo段：采用LRU最近最少使用算法来循环覆盖使用，它的块有active和inactive和new状态，如果块的状态为inactive那么后续的数据就可以覆盖它了。如果空间不够Oracle自动分配新空间。

分配：Oracle是按顺序循环使用的，不允许跨区覆盖。

回收：Oracle也是按顺序回收的，不允许跨区回收。

Rollback和Commit标识位都一样，都代表事物的结束

Rollback：表示会回滚从上一次提交到现在的所有事物

一大话UNDO

Undo和Redo区别

好多人尤其是刚刚入门的朋友，总会把这两个概念搞混淆。

Undo目的：1.事物回滚：rollback

2.实例的恢复：掉电，死机，强制关库，把没有提交的事物全部回滚

3.查询一致性：读不会被写所阻塞

分配：Oracle是按顺序循环使用的，不允许跨区覆盖。

回收：Oracle也是按顺序回收的，不允许跨区回收。

Rollback和Commit标识位都一样，都代表事物的结束

Rollback：表示会回滚从上一次提交到现在的所有事物

二数据库版本

SYS@LEO1> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

三示例演示回滚是否产生REDO日志。

原理解释：首先向大家明确一点，redo的产生机理是因为底层数据块的变化而产生的，这个数据块不管是数据文件的还是undo文件的，数据块的变化都会记录在redo日志里面。

OK我们再来看看undo回滚的原理，假设一个数据块命名为A，这个A里面存放了数据1，现在我们要把这个1->修改->2->修改->3->修改->2->修改->1步骤

1.先把1复制一份映像存放到undo块上用于回滚，这就是前映像，undo块改变产生redo记录

2.再把数据块上的1修改为2，A改变产生redo记录，现在A中存放的是2

LEO1@LEO1> drop table leo2 purge; 清理环境

Table dropped.

LEO1@LEO1> create table leo2 as select * from leo1 where rownum<10;创建leo2表只需要9行记录即可

LEO1@LEO1> select a.name,b.value from v$statname a,v$mystat b where a.STATISTIC# =b.STATISTIC# and a.NAME='redo size';

NAME VALUE

-----------------------------------------------------------------------------------------------------------------------------------

redo size 2362280

这是初始化的值

LEO1@LEO1> update leo2 set object_id=2 where object_id=1; 1->修改->2

1 row updated.

LEO1@LEO1> select a.name,b.value from v$statname a,v$mystat b where a.STATISTIC# =b.STATISTIC# and a.NAME='redo size';

NAME VALUE

---------------------------------------------------------------------------------------------------------------------------------------------

redo size 2362992

LEO1@LEO1> select 2362992-2362280 from dual; 这是产生的redo量

2362992-2362280

-------------------------

712

3.这时我们又想把2修改为3，还是先把2复制一份映像存放到undo块上用于回滚，这就是第二个前映像，undo块改变产生redo记录

4.再把A中的2修改为3，A改变产生redo记录，现在A中存放的是3

LEO1@LEO1> update leo2 set object_id=3 where object_id=2; 2->修改->3

1 row updated.

LEO1@LEO1> select a.name,b.value from v$statname a,v$mystat b where a.STATISTIC# =b.STATISTIC# and a.NAME='redo size';

NAME VALUE

-------------------------------------------------------------------------------------------------------------------------------------------

redo size 2363348

LEO1@LEO1> select 2363348-2362992 from dual; 这是产生的redo量

2363348-2362992

---------------------------

356

5.我们又想把3回退为1，那么就需要先把3回退为2，由于我们在undo段上记录了每次改变的前映像，我们直接把前映像回滚回来即可（事物的回滚），使用第二个前映像把A中的3修改为2，A改变会产生redo记录，现在A中存放的是2，因此这种回滚操作也会产生redo日志的，在使用第一个前映像把A中的2修改为1，A改变会产生redo记录，现在A中存放的是1，此时rollback操作完毕事务结束。

我们用语句来实现一下上面的操作，rollback特点会回滚从上次提交到现在的所有事务，3->1

LEO1@LEO1> rollback; 3->修改->2->修改->1

Rollback complete.

LEO1@LEO1> select a.name,b.value from v$statname a,v$mystat b where a.STATISTIC# =b.STATISTIC# and a.NAME='redo size';

NAME VALUE

---------------------------------------------------------------------------------------------------------------------------------------

redo size 2364608

LEO1@LEO1> select 2364608-2363348 from dual; 产生的redo量很大的

2364608-2363348

------------------------

1260

小结：我们要知道rollback和commit的标识位都一样都代表事务结束，同时也都会产生redo日志。

四示例演示为数据库创建一个新的UNDO表空间。

Oracle 8i需要手工创建undo表空间

Oracle 9i自动管理UNDO表空间

Oracle 10g 11g在创建数据库时，必须一起创建undo表空间

未来自动化是大趋势，Oracle 12c会更加的自动化更加的智能

SYS@LEO1> show parameter undo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO Oracle自动管理表空间

undo_retention integer 900 默认保留期15分钟

undo_tablespace string UNDOTBS1 Oracle默认undo表空间

SYS@LEO1> select tablespace_name,autoextensible from dba_data_files;

TABLESPACE_NAME AUT

-------------------------------------------------------------

USERS YES

UNDOTBS1 YES 这也显示当前使用的undo表空间

SYSAUX YES

SYSTEM YES

LEO1 NO

Oracle特点：可以创建好多个undo表空间，但只能使用其中一个，下面我们来创建一个新undo

SYS@LEO1> select tablespace_name,file_name,bytes/1024/1024,autoextensible from dba_data_files;

TABLESPACE_NAME FILE_NAME BYTES/1024/1024 AUT

----------------------------------------------------------------------------------------------------------------------------------------------

USERS /u01/app/oracle/oradata/LEO1/users01.dbf 5 YES

UNDOTBS1 /u01/app/oracle/oradata/LEO1/undotbs01.dbf 165 YES

SYSAUX /u01/app/oracle/oradata/LEO1/sysaux01.dbf 610 YES

SYSTEM /u01/app/oracle/oradata/LEO1/system01.dbf 700 YES

LEO1 /u01/app/oracle/oradata/LEO1/leo1_01.dbf 400 NO

这是所有表空间文件路径和大小，当前undo表空间165M，我们把新的设置成100M

SYS@LEO1> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/LEO1/undotbs02.dbf' size 100M;

Tablespace created.

UNDOTBS2 /u01/app/oracle/oradata/LEO1/undotbs02.dbf 100 NO

为什么设置成100M呢，这个大小因人而异，你可以根据自己系统的情况个性化设计满足需求即可。

SYS@LEO1> alter system set undo_tablespace=undotbs2;如果要使用新的undo表空间，必须切换到该表空间

System altered.

SYS@LEO1> show parameter undo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO Oracle自动管理表空间

undo_retention integer 900 默认保留期15分钟

undo_tablespace string UNDOTBS2 成为新的默认undo表空间

小结：1.undo是一个很重要的表空间，一个数据库当且仅当只有一个undo表空间，如果我们想使用新的，必须切换到新表空间。

2.我们不能删除当前正在使用的undo表空间，如果删除了数据库就会crash，如果发现空间太小或者undo段出现坏块，你必须先创建一个新的，再从老的切换过去。

五示例分别说明什么是consistent read和current read?

一致性读这个话题由来已久，通俗的说一致性读，就是你什么时候发出select，不管查询了多长时间，返回的值都应该是你查询时刻时间点的。这个话题是由业务特性所引起的，是基于业务需求而来的，Oracle中由SCAN号顺序来实现

例如我们查询一张leo1表

LEO1@LEO1> drop table leo1 purge; 清理环境

Table dropped

LEO1@LEO1> create table leo1 as select * from dba_objects; 创建leo1表

Table created.

LEO1@LEO1> set time on 我们启动时间点显示

09:44:38 LEO1@LEO1> set autotrace traceonly

09:44:50 LEO1@LEO1> select * from leo1 where object_type='TABLE'; 我们看一下执行计划

2818 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2716644435

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3003 | 607K| 287 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| LEO1 | 3003 | 607K| 287 (1)| 00:00:04 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_TYPE"='TABLE')

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

1218 consistent gets

0 physical reads

0 redo size

149962 bytes sent via SQL*Net to client

2580 bytes received via SQL*Net from client

189 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

2818 rows processed

09:45:04 LEO1@LEO1>

我们从09:44:50发出select语句，到查询到结果是09:45:04，中间经过了14秒，如果这14秒内有另外的用户去修改我们查询的数据块，那么得到的结果是修改后的呢，还是修改前的呢，从业务的角度看，我们查询的是09:44:50的select，那么不管查了多长时间，我们就想要这个时间点的结果，即查什么时候的就得到什么时候的结果就叫一致性读。

一致性读解决方案：就利用到了回滚段，当我们查询时发现这个数据块正在被修改，那么我们就要跳到回滚段上去读取它的前映像，结果不会因为块修改而改变。这就是著名的“读不会被写所阻塞定理”

Select统计信息里反应出的一致性读

0 db block gets 当前读0个块

1218 consistent gets 一致性读1218个块

当前读，什么是当前读，当前读就是我们操作的数据块一定是最当前的状态，这就会产生锁，防止多个会话同时操作一个数据块，谁先获得块谁就拥有修改权，其他会话就要等待。

例如我们修改一张leo1表

10:13:24 LEO1@LEO1> update leo1 set object_type='leonarding' where object_type='TABLE';

2818 rows updated.

Execution Plan

----------------------------------------------------------

Plan hash value: 3524047104

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | UPDATE STATEMENT | | 12 | 132 | 287 (1)| 00:00:04 |

| 1 | UPDATE | LEO1 | | | | |

|* 2 | TABLE ACCESS FULL| LEO1 | 12 | 132 | 287 (1)| 00:00:04 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("OBJECT_TYPE"='TABLE')

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

285 recursive calls

12121 db block gets

3126 consistent gets

13 physical reads

2273320 redo size

837 bytes sent via SQL*Net to client

825 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

2818 rows processed

10:13:27 LEO1@LEO1>

我们从10:13:24发出update语句，到修改完是10:13:27，中间经过了3秒，这期间都是锁定状态，别的会话必须等待，如果修改完后我一直不提交，这些数据块就都是锁定的，此时如果有其他会话来查询这些数据块的内容，那么就要从回滚段里读取前映像数据了，不允许直接查看数据块当前的状态，因为新修改的数据还没有提交，事物还没有结束。

update统计信息里反应出的当前读

12121 db block gets 当前读12121个块，说明锁定了这么多个块

3126 consistent gets 一致性读相对就少很多

小结：这就是undo segment在consistent read和current read方式下的运作机制。

六示例演示一个导致ora-01555错误的场景

Ora-01555经典中的经典，直到现在Oracle还是没有完美解决，既然oracle使用到了回滚段来保证读一致性，有利必有弊，那么就会有快照太旧的情况发生，我们所查询的前映像被无情的覆盖了。

原因：就是当一个select查询等待时间太长，这个过程中查询的数据被修改完毕commit了，此时它的前映像就为inactive状态，如果这时候有其他会话也想用undo段，就会把inactive状态的回滚区覆盖掉，这时正好select查询完毕想读取它的前映像，发现已经被覆盖了，此时oracle会报ora-01555错误。理解原理很重要

我用一种叫做闪回查询的功能来实现ora-01555

flashback query:原理也是使用undo segment中的前映像，来查询出我们修改之前的数据映像，一般用于误操作恢复。可以基于时间闪回还可以基于SCN号闪回，一秒钟包括好几个SCN号，不管基于什么oracle内部都是按照SCN号顺序操作的。只要我们查找的原映像被覆盖了，就会报ora-01555错误。

实验

LEO1@LEO1> drop table leo3 purge; 清理环境

Table dropped.

LEO1@LEO1> create table leo3 (name varchar2(20),sale number); 创建leo3

Table created.

这个表里记录下面4位小朋友去米国游玩的旅行费

LEO1@LEO1> insert into leo3 values('leonarding',10000);

1 row created.

LEO1@LEO1> insert into leo3 values('sunev_yu',20000);

1 row created.

LEO1@LEO1> insert into leo3 values('alan',30000);

1 row created.

LEO1@LEO1> insert into leo3 values('tigerfish',40000);

1 row created.

LEO1@LEO1> commit;

Commit complete.

看样子tiger最有钱了，跟着tiger走吃喝全都有！

LEO1@LEO1> select * from leo3;

NAME SALE

----------------------------------------------------------

leonarding 10000

sunev_yu 20000

alan 30000

tigerfish 40000

先查一下还没有ship之前的SCN号备用

LEO1@LEO1> select dbms_flashback.get_system_change_number scn from dual;

SCN

-----------------

2595945

由于tiger的慷慨大方包吃包住，我们玩耍的很尽兴！在结束旅行之前大家去银行查一查每个人的cash book

LEO1@LEO1> update leo3 set sale=100 where name='tigerfish';

1 row updated.

LEO1@LEO1> update leo3 set sale=200 where name='alan';

1 row updated.

LEO1@LEO1> update leo3 set sale=300 where name='sunev_yu';

1 row updated.

LEO1@LEO1> update leo3 set sale=400 where name='leonarding';

1 row updated.

LEO1@LEO1> commit;

Commit complete.

哈发现一个规律现金越少蹭功越强，在此向tigerfish alan两位大师表达敬意！欧巴~阿加西~

LEO1@LEO1> select * from leo3;

NAME SALE

----------------------------------------------------------

leonarding 400

sunev_yu 300

alan 200

tigerfish 100

LEO1@LEO1> select dbms_flashback.get_system_change_number scn from dual;

SCN

----------------

2605429

看SCN：2595945~增长~2605429，记录着数据库所有变化，是持续增长的

这时他们都想看看来时每个人的现金额是多少，花了多少，回去好交差

LEO1@LEO1> select name,sale from leo3 as of scn 2595945;

NAME SALE

----------------------------------------------------------

leonarding 10000

sunev_yu 20000

alan 30000

tigerfish 40000

这些数据，就是从undo segment中读取出来的，我们需要指定当时的SCN号，我们要根据SCN号进行闪回查询。

LEO1@LEO1> alter database datafile '/u01/app/oracle/oradata/LEO1/undotbs02.dbf' autoextend off;

Database altered.

禁用undo表空间自动扩展功能

LEO1@LEO1> select tablespace_name,file_name,autoextensible from dba_data_files where tablespace_name='UNDOTBS2';

TABLESPACE_NAME FILE_NAME AUT

----------------------------------------------------------------------------------------------------------------------------------------------

UNDOTBS2 /u01/app/oracle/oradata/LEO1/undotbs02.dbf NO

我们现在开始覆盖undo segment中的inactive区中前映像，覆盖之后我们再查询时就会出现ora-01555

LEO1@LEO1> begin

for i in 1..100000 loop

update leo3 set sale=10000 where sale>=100;

rollback;

end loop;

end;

2 3 4 5 6 7

PL/SQL procedure successfully completed.

经典中的经典出现了ORA-01555

LEO1@LEO1> select name,sale from leo3 as of scn 2595945;

select name,sale from leo3 as of scn 2595945

ERROR at line 1:

ORA-01555: snapshot too old: rollback segment number 12 with name "_SYSSMU12_3331027169$" too small

ORA-01555：快照太旧：名为_SYSSMU12_3331027169$的12号回滚段太小，哈~我们成功模拟出undo segment被覆盖场景。

小结：上述实验说明了undo分配是按顺序循环使用，不可跨区覆盖，undo回收也是按顺序循环回收，不可跨区回收。