深入浅出Oracle学习笔记（7）

第七章 回滚和撤销

如果说Redo是用来保证在故障时事务可以被恢复，那么Undo则是用来保证事务可以被回退或者撤销。

在修改操作中，对于回退段的操作存在多处，在事务开始时，首先需要在回滚段表空间获得一个事务槽，分配空间，然后创建前镜像，此后事务的修改才能进行，Oracle必须以此来保证事务是可以回退的。

如果用户提交了事务，Oracle会在日志文件记录提交，并且写出日志，同时会在回滚段中把该事务标记为已提交；如果用户回滚事务，则Oracle需要从回滚段把前镜像数据读取出来，修改数据缓冲区，完成回滚，这个过程本身也要产生Redo，所以回退这个操作是很昂贵的。

在Oracle性能优化中，有一个性能指标称为平均事务回滚率（Rollback per transaction），用来衡量数据库的提交与回滚效率。可以在Statspack中找到这个指标。

该参数计算公式为：

Round(User rollbacks/(user commits+user rollbacks),4)*100%

其中user commits和user rollbacks数据来自系统的统计信息，可以从v$sysstat视图中得到，

SQL> select name,value from v$sysstat

  2  where name in ('user commits','user rollbacks');

 

NAME                                                                  VALUE

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

user commits                                                              5

user rollbacks                                                            0

 

这个指标应该接近于0，如果该指标过高，则说明数据库的回滚过多。回滚过多不仅说明数据库经历了太多的无效操作，而且这些操作会极大影响数据库性能。

 

回滚段存储的内容：

对于INSERT操作，回滚段只需要记录插入记录的rowid，如果回退，只需将该记录根据rowid删除即可；

对于UPDATE操作，回滚段只需要记录被更新字段的旧值即可（前镜像），回退时通过旧值覆盖新值即可完成回退；

对于DELETE操作，Oracle则必须记录整行的数据，在回退时，Oracle通过一个反向操作恢复删除的数据。

通过以上可以总结：对于相同数据量的数据操作，通常INSERT产生最少的Undo，Update产生的Undo居中，而Delete操作产生的Undo最多。这也就是我们经常看到，当一个大的DELETE操作失败或者回滚，总是需要很长的时间，并且会有大量的Redo生成。所以通常在进行大规模数据删除操作时，推荐通过分批删除分次提交，以减少对于回滚段的占用和冲击。

 

并发控制和一致性读的实现

一方面Oracle通过锁定机制实现数据库的并发控制；一方面通过多版本模型来进行并发数据访问。通过多版本架构，Oracle实现了读取和写入的分离，使得写入不阻塞读取，读取不阻塞修改。

多版本模型在Oracle数据库中是通过一致性读来实现的，一致性读也正是回滚段表空间的主要作用之一。

Oracle一方面不允许其他用户读取未提交数据，另一方面保证用户读取的数据要来自同一时间点。

Oracle内部使用SCN作为数据库时钟，查询结果集就是根据SCN来进行判断的，每个数据块头部都会记录一个提交SCN，当数据更改提交后，提交SCN同时被修改，这个SCN在查询时用来进行一致性读判断。

比如：假定查询开始的时间为T1，则在查询获取的数据块中，如果数据块的提交SCN小于T1，则Oracle接受该数据，如果提交SCN大于T1或者数据被锁定修改尚未记录COMMIT SCN，则Oracle需要通过回滚段构造前镜像来返回结果，这就是一致性读的本质含义。

 

回滚段的前世今生

在Oracle 9i之前，回滚段表空间创建之后，Oracle随后创建回滚段供数据库使用，也可以手工创建或者删除回滚段进行维护，在开始事务之前，也可通过如下命令指定用户想要使用的回滚段。

set transaction user rollback segment ;

可以从数据库中查询这些回滚段的状态：

SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS

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

SYSTEM                         SYSTEM                         ONLINE

_SYSSMU1$                      UNDOTBS1                       ONLINE

_SYSSMU2$                      UNDOTBS1                       ONLINE

_SYSSMU3$                      UNDOTBS1                       ONLINE

_SYSSMU4$                      UNDOTBS1                       ONLINE

_SYSSMU5$                      UNDOTBS1                       ONLINE

_SYSSMU6$                      UNDOTBS1                       ONLINE

_SYSSMU7$                      UNDOTBS1                       ONLINE

_SYSSMU8$                      UNDOTBS1                       ONLINE

_SYSSMU9$                      UNDOTBS1                       ONLINE

_SYSSMU10$                     UNDOTBS1                       ONLINE

 

从Oracle 9i开始，Oracle引入了自动管理的Undo表空间，如果选择使用自动的Undo表空间的管理，那么用户不再能够创建或删除回滚段，也不再需要为事务指定回滚段，这一切由Oracle自动进行。

伴随自动的Undo管理功能的引入，Oracle随之引入了几个新的初始化参数：

Undo_management：用来定义数据库使用的回滚段是否使用自动管理模式，该参数有两个可选项，AUTO表示自动管理，MANUAL表示手工管理。

Undo_tablespace：用来定义在自动管理模式下，当前实例使用哪个undo表空间。

Undo_suppress_errors：表示当使用自动管理模式时，如果使用不再支持的操作时（如为事务指定回滚段）是否返回出错信息。设置为True时不返回出错信息，操作无效但是可以继续，设置为False时，则操作不能继续，这实际上是一个向后兼容的参数。该参数在Oracle 10g中已经被舍弃。

Undo_retention：表示在自动管理模式下，当回滚段变得非激活之后，回滚段中的数据在被覆盖前保留的时间，该参数单位是秒。在Oracle 9iR2中，这个参数的缺省值为10800秒，也就是3个小时。

在自动管理的Undo表空间下，回滚段的个数是Oracle根据数据库的繁忙程度自动分配或者回收的。缺省情况下数据库创建时初始化10个回滚段。

Select * from v$rollname;

在系统繁忙时，可以从数据库的alert­_.log文件中看到回滚段的动态创建和释放过程。动态创建和释放，这也是自动管理的Undo表空间的优势之一。

 

回滚机制的深入研究

从DML更新事务开始：

SQL> show user

USER 为"SCOTT"

SQL> update emp set sal=4000 where empno=7788;

已更新 1 行。

 

SQL> select * from emp where empno=7788;

 

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM

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

    DEPTNO

----------

      7788 SCOTT      ANALYST         7566 19-4月 -87       4000

        20

先不提交这个事务，在另外窗口新开session，使用sys用户查询相关信息，进行进一步的分析研究。

 

获得事务信息：

从事务表中可以获得关于事务的信息，该事务位于4号回滚段（XIDUSN），在4号回滚段上，该事务位于第22号事务槽（XIDSLOT）：

SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;

 

    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBAREC

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

         4         22       3820         63          2         16

 

从v$rollstat视图中也可以获得事务信息，XACTS字段代表的是活动事务的数量，同样可以看到该事务位于4号回滚段：

SQL> select usn,writes,rssize,xacts,hwmsize,shrinks,wraps from v$rollstat;

 

       USN     WRITES     RSSIZE      XACTS    HWMSIZE    SHRINKS      WRAPS

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

         0       6900     385024          0     385024          0          0

         1      66768    2220032          0    2220032          0          0

         2      59370     516096          0     516096          0          1

         3     114244     450560          0     450560          0          1

         4      11164    2220032          1    2220032          0          0

         5      59242     450560          0     450560          0          1

         6      68544    5365760          0    5365760          0          0

         7      66556    2285568          0    2285568          0          0

         8      58888    2220032          0    2220032          0          0

         9      59494     450560          0     450560          0          1

        10      64192    9560064          0    9560064          0          0

 

已选择11行。

 

获得回滚段名称并转储段头信息：

查询v$rollname视图获得回滚段名称，并转储回滚段段头信息：

SQL> select * from v$rollname where usn=4;

       USN NAME

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

         4 _SYSSMU4$

 

SQL> alter system dump undo header '_SYSSMU4$';

系统已更改。

 

获得跟踪文件信息：

 

TRN TBL::

  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num

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

   0x00    9    0x00  0x0eec  0x0025  0x0000.006fabac  0x0080003e  0x0000.000.00000000  0x00000001   0x00000000

………………………………………………………………

   0x15    9    0x00  0x0eec  0x0010  0x0000.006faeaf  0x0080003f  0x0000.000.00000000  0x00000001   0x00000000

   0x16   10    0x80  0x0eec  0x0000  0x0000.006faf8d  0x0080003f  0x0000.000.00000000  0x00000001   0x00000000

   0x17    9    0x00  0x0eeb  0x0019  0x0000.006faa95  0x0080003e  0x0000.000.00000000 

……………………………………………………………….

 

从上面可以看到，该事务正好占用的是第22号事务槽（0x16），状态为10代表的是活动事务。

上面DBA（Data Block Address）指向的是包含这个事务的前镜像的数据块地址0x0080003f。DBA代表数据块的存储地址，由10位文件号和22位数据块组成。将0x0080003f转换为二进制就是0000 0000 1000 0000 0000 0000 0011 1111。前10位代表文件号为2，后22位代表Block号为16。经过转换后，该前镜像信息位于file 2 block 16。这和从事务表v$transaction中查询到的数据完全一致。

 

 

整个事务过程解析的基本流程：

首先当一个事务开始时，需要在回滚段事务表上分配一个事务槽；

在数据块头部获得一个ITL事务槽，该事务槽指向回滚段段头的事务槽；

在修改数据之前，需要记录前镜像信息，这个信息以UNDO RECORD的形势存储在回滚段中，回滚段头事务槽指向该记录；

锁定修改行，修改行锁定位（lb-lock block）指向ITL事务槽；

数据修改可以进行。

 

块清除（Block Cleanouts）

在提交时，Oracle需要对数据块做哪些操作：

在事务需要修改数据时，必须分配ITL事务槽，必须锁定行，必须分配回滚段事务槽和回滚空间记录前镜像。当事务提交时，Oracle需要将回滚段上的事务表信息标记为非活动，以便空间可以重用；还有ITL事务信息和锁定信息需要清除，以记录提交。

由于Oracle在数据块上存储了ITL和锁定等事务信息，所以Oracle必须在事务提交之后清除这些事务数据，这就是块清除。块清除主要清除的数据有行级锁、ITL信息（包括提交标志、SCN等）。

如果提交时修改过的数据块仍然在Buffer Cache之中，那么Oracle可以清除ITL信息，这叫做快速块清除。快速块清除有一个限制，当修改的块数量超过Buffer Cache的约10%，则对超出部分不再进行快速块清除。

如果提交事务时，修改过的数据块已经被写回到数据文件上（或大量修改超出10%的部分），再次读出该数据块进行修改显然成本过于昂贵。对于这种情况，Oracle选择延迟块清除，等到下次访问该Block时再来清除ITL锁定信息。Oracle通过延迟块清除来提高数据库的性能，加快提交操作。

 

提交之后的Undo信息：

虽然事务已经提交，不可以回滚了，但是在覆盖之前，这个前镜像信息仍然存在，通过某种手段，仍然可以获得这个信息。

 

Oracle 9i闪回查询的新特性

SQL> select dbms_flashback.get_system_change_number scn from dual;    --查询当前数据库的SCN

通过特定的语法，可以将历史SCN状态数据查询出来：

Select * from emp as of scn 89803404323255 where empno in(7788,7782);

由于这个查询需要从Undo中获取前镜像信息，如果Undo中的信息被覆盖，则以上查询将会失败。

观察回滚段的使用：

SQL> select usn,xacts,rssize,hwmsize from v$rollstat;

 

       USN      XACTS     RSSIZE    HWMSIZE

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

         0          0     385024     385024

         1          0    2220032    2220032

         2          0     516096     516096

         3          0     450560     450560

         4          0    2220032    2220032

         5          0     450560     450560

         6          0    5365760    5365760

         7          0    2285568    2285568

         8          0    2220032    2220032

         9          0     450560     450560

        10          0    9560064    9560064

 

Oracle 10g闪回查询特性的增强

Oracle 9i的闪回查询只能提供某个时间点的数据视图，并不能告诉用户这样的数据经过了几个事务、怎样的修改（UPDATE、INSERT、DELETE等），而这些信息在回滚段中是存在的，在Oracle 10g中，Oracle进一步加强了闪回查询的特性，提供了以下两种闪回查询：

闪回版本查询（Flashback Versions Query）

闪回事务查询（Flashback Transaction Query）

闪回版本查询允许使用一个新的VERSIONS子句查询两个时间点或者SCN之间的数据版本。这些版本可以按照事务区分，闪回版本查询只返回提交数据，未提交数据不被显示。

Oracle 10g的闪回版本查询通过使用VERSIONS子句和对数据表引入一系列的伪列（version_starttime等），可以获得对数据表的所有事务操作，versions_operation代表不同类型的操作（D-DELETE、I_INSERT、U_UPDATE），VERSIONS_XID是一个重要依据，代表了不同版本的事务ID。

Select versions_starttime,versions_endtime,versions_xid,versions_operation,username,user_id

From tbl_name versions between timestamp minvalue and maxvalue;

通过以上查询，根据versions_xid可以清晰地区分不同事务在不同时间对数据所作的更改。

 

 

ORA—01555错误

由于回滚段是循环使用的，当事务提交以后，该事务占用的回滚段事务表会被标记为非活动，回滚段空间可以被覆盖重用。如果一个查询需要使用被覆盖的回滚段构造前镜像实现一致性读，那么此时会出现ORA-01555错误。

ORA-01555错误的另一个原因是因为延迟块清除。当一个查询触发延迟块清除时，Oracle需要去查询回滚段获得该事务的提交SCN，如果事务的前镜像信息已经被覆盖，并且查询SCN也小于回滚段中记录的最小SCN，那么Oracle将无法判断查询SCN和事务提交SCN的大小，此时出现延迟块清除导致的ORA-01555错误。

另外一种导致ORA-01555错误的情况出现在使用sqlldr直接方式加载（direct=true）数据时。当通过sqlldr direct=true方式加载数据时，由于不产生重做和回滚信息，Oracle直接指定Cached Commit SCN给加载数据，在访问这些数据时，有时会产生ORA-01555错误。

ORA-01555的直观解释是“Snapshot too old”，也就是快照太旧，其根本含义就是查询需要的前镜像过于久远，已经无法找到了。当数据出现ORA-01555错误时，Oracle会将错误信息记录在警告日志中。

在Oracle 9i自动管理的Undo 表空间模式下，undo_retention参数设置当事务提交之后，回滚段变得非激活，回滚段中的数据在被覆盖前保留的时间，该参数以秒为单位，9iR1初始值为900秒，在Oracle 9iR2增加为10800秒。

显然该参数设置的越高九越能够减少ORA-01555错误的出现，但是保留时间和存储空间是紧密相关的。如果Undo表空间的存储空间有限，那么Oracle就会选择回收已提交事务占用的空间，而置undo_retention参数的设置于不顾。

在Oracle 9i的AUM模式下，undo_retention实际上是一个非但保（No Guaranteed）限制。也就是说，如果有其他事务需要回滚空间，而空间出现不足时，这些信息仍然会被覆盖。

从Oracle 10g开始，Oracle引入了自动的undo_retention调整，缺省情况下，这个功能被启用，Oracle动态的收集系统的事务信息，自动调整以满足最长运行查询的需要。当然如果空间不足，那么Oracle满足最大允许的长时间查询，而不再需要用户手工调整。

这个新特性的引入伴随着几个新的隐含初始化参数：

_undo_autotune           enable auto tuning of undo_retention

_collect_undo_stats     Collect statistics v$undostat

这两个参数缺省都是打开的。

同时Oracle 10g增加了Guarantee控制，也就是说，用户可以指定Undo表空间必须满足Undo_retention的限制：

Alter tablespace undotbs1 retention guarantee;

Alter tablespace undotbs1 retention noguarantee;

在DBA_TABLESPACES视图增加了RETENTION字段用以描述该选项：

当Undo表空间设置为Guarantee，那么提交事务的回滚空间必须被保留足够的时间，如果Undo表空间的空间不足，那么新事务会因空间不足而失败，而不是选择之前的覆盖。

 

AUM下如何重建Undo表空间

在迁移（同平台）的时候由于Undo表空间过大，不打算要现有的Undo文件，想重建一个，具体怎么做？

这个前提是拥有一个有效的冷备份（拥有一个Clean Shutdown的数据库）。

同平台迁移时可以放弃Undo表空间，这时候启动会报错ORA-01157；

删除Undo文件启动数据库：

Alter database datafile ‘D:\oradata\..........’ offline drop;

Alter database open;

重建Undo表空间，并切换为当前undo表空间。

Create undo tablespace undotbs2 datafile ‘………’ size 10m;

Alter system set undo_tablespace=undotbs2;

然后数据库即可恢复正常使用。

 

诊断案例一：使用Flashback Query恢复误删除数据

用户误删除了部分重要数据，并且已经提交，需要恢复。数据库是Oracle 9iR2，首先尝试使用Flashback Query闪回数据。

首先确认数据库的SCN变化：

Select name,first_change# fscn,next_change# nscn,first_time from v$archived_log;

Select dbms_flaskback.get_system_change_number fscn from dual;

使用应用用户尝试闪回：

Select count(*) from tbl_name;

创建恢复表：

Create table tbl_name_recov as select * from tbl_name where 1=0;

选择合适的SCN向前恢复：

Select count(*) from tbl_name as of scn 124234934;

尝试多个SCN，获取最佳值（如果能得知具体时间，那么可以获得准确的数据闪回）。

最后选择恢复到一个较合适的SCN：

Insert into tbl_name_recov select * from tbl_name as of scn 1393239423;

 

诊断案例二：释放过渡扩展的undo空间

从Oracle 9i开始，当使用AUM管理时，通常会选择设置undo表空间自动扩展，这就可能出现undo表空间过渡扩展而不能回缩的问题。

检查数据库表空间占用空间情况：

Select tablespace_name,sum(bytes)/1024/1024/1024 GB

From dba_data_files group by tablespace_name

Union all

Select tablespace_name,sum(bytes)/1024/1024/1024 GB

From dba_temp_files group by tablespace_name order by GB;

Undo和Temp表空间已经扩展到很大，表明曾经有大事务占用了大量的Undo表空间和Temp表空间，Oracle的AUM从出生以来就经常出现只扩展、不收缩的情况（通常可以设置足够的Undo表空间大小，然后取消其自动扩展属性）。

可以采用以下步骤回收Undo表空间：

确认文件：

Select file_name,bytes/1024/1024 from dba_data_files

Where tablespace_name like ‘UNDOTBS1’;

检查Undo segment状态及大小：

Select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks

From v$rollstat order by rssize;

创建新的Undo表空间：

Create undo tablespace undotbs2;

切换Undo表空间为新的Undo表空间：

Alter system set undo_tablespace=undotbs2 scope=both;

等待原Undo表空间所有Undo Segment Offline：

Select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks

From v$rollstat order by rssize;

确认原回滚段表空间所有回滚段都正常OFFLINE，然后删除原Undo表空间：

Drop tablespace undotbs1 including contents;

 

 

特殊情况的恢复

在很多情况下，特别是在使用隐含参数强制打开数据库之后，可能会出现ORA-00600 4194错误。

4194错误通常说明Undo段出现问题，最好的办法是通过备份进行恢复，如果没有备份，那么可以通过特殊的初始化参数进行强制启动，下面介绍如何使用Oracle的隐含参数进行恢复：

首先确定当前回滚段名称，这可以从alert文件中获得，对应得AUM下的回滚段名称为：

‘_SYSSMU11$’ , ‘_SYSSMU13$’,………

修改init.ora参数文件，使用Oracle隐含参数_corrupted_rollback_segments将回滚段标记为损坏 ，此时启动数据库，Oracle会跳过对于这些回滚段的相关操作，强制启动数据库。

. _corrupted_rollback_segments=‘_SYSSMU11$’, ‘_SYSSMU13$’

此时可以重新创建新的Undo表空间，删除出现问题的表空间，修改参数文件，由参数文件生成新的spfile，重新启动数据库。

Create undo tablespace undotbs1 datafile ‘d:\oradata\........’ size 10M;

Alter system set undo_tablespace=undotbs1;

Drop tablespace undotbs;

通过以上方法恢复数据库，通常会导致数据库内部存在不一致的状况，建议立即进行全库exp，然后重新建库，再通过imp恢复数据库。

 

 

数值在Oracle的内部存储

Oracle在数据库内部通过相应的算法转换来进行数据存储，可以通过DUMP函数来转换数字的存储形式：

SQL> select dump(1) from dual;

DUMP(1)

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

Typ=2 Len=2: 193,2

 

DUMP函数的输出格式类似：

类型 长度]>, 符号/指数位 [数字1, 数字2, 数字3, ……, 数字20]

主要由以下几个组成部分：

类型，Number型，Type=2

长度，指存储的字节数

符号/指数位

在存储上，Oracle对正数和负数分别进行存储转换。

正数：加1存储（为了避免NULL）

负数：被101减，如果总长度小于21个字节，最后加一个102（是为了排序的需要）

指数位换算：

正数：指数=符号/指数位-193（最高为为1是代表正数）

负数：指数=62-第一字节

从<数字1>开始是有效的数据位：

从<数字1>开始是最高有效位，所存储的数值计算方法为：

每个<数字位>*100^(指数-N)，其中N是有效位数的顺序位，第一个有效位的N=0。

SQL> select dump(123456.789) from dual;

DUMP(123456.789)

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

Typ=2 Len=6: 195,13,35,57,79,91

<数字1>            13-1           =12*100^(2-0) 120000

<数字2>            35-1           =34*100^(2-1) 3400

<指数>               195-193     =2

<数字3>            57-1           =56*100^(2-2) 56

<数字4>            79-1           =78*100^(2-3) .78

<数字5>            91-1           =90*100^(2-4) .009

                                                        123456.789

 

SQL> select dump(-123456.789) from dual;

DUMP(-123456.789)

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

Typ=2 Len=7: 60,89,67,45,23,11,102

<指数>               62-60        =2 (最高为是0，代表为负数)

<数字1>            101-89      =12*100^(2-0) 120000

<数字2>            101-67       =34*100^(2-1) 3400

<数字3>            101-45       =56*100^(2-2) 56

<数字4>            101-23       =78*100^(2-3) .78

<数字5>            101-1         1       =90*100^(2-4) .009

                                                        123456.789(-)

 

为什么在最后加上 102 是为了排序的需要， -123456.789 在数据库中实际存储为 60,89,67,45,23,11 ，而 -123456.78901 在数据库中实际存储为 60,89,67,45,23,11,91 。可见，如果不在最后加上 102 ，在排序时会出现 -123456.789的情况。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15203236/viewspace-604459/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15203236/viewspace-604459/