Oracle 回滚(ROLLBACK)和撤销(UNDO)

一、回滚(ROLLBACK)和撤销(UNDO)

回滚和前滚是保证Oracle数据库中的数据处于一致性状态的重要手段。

在9i版本以前
Oracle使用数据库中的回滚段来实现未提交数据或因系统故障导致实例崩溃时进行回滚操作
每一个表空间需要创建回滚段，各个表空间对回滚段实现各自的管理
在9i及后续版本
提供了一种新的回滚数据的管理方式，即使用Oracle自动管理的撤销(Undo)表空间
自动撤销管理表空间统一管理所有DML的回滚操作，简化了对于回滚工作的管理
在9i,10g中的回滚段仅仅用作保留向后兼容
撤销段代替了原有版本中的回滚段，因此本文所有描述均使用撤销

撤销的实质意味着将所作的修改退回到修改前的状态，即倒退所有DML语句

关于如何创建恢复目录数据库及恢复目录脚本，此处省略，请参考:

RMAN catalog 的创建和使用 http://www.linuxidc.com/Linux/2013-08/88788.htm

基于catalog 创建RMAN存储脚本 http://www.linuxidc.com/Linux/2013-08/88789.htm

基于catalog 的RMAN 备份与恢复 http://www.linuxidc.com/Linux/2013-08/88790.htm

二、撤销段中的内容及相关特性
对于任何DML操作而言，必须同时处理数据块和撤销块，并且还会生成重做信息
在ACID中，A、C、I要求生成撤销，D则要求生成重做
INSERT：
撤销段记录插入记录的rowid,如果需要撤销，则根据rowid将该记录删除即可
UPDATE:
撤销段记录被更新字段的原始值，撤销时将原始值覆盖新值即可
DELETE：
撤销段记录整行的数据，撤销时执行反向操作将该记录插入原表

由上可知，UNDO段中的内容总结如下：
数据为修改之前的副本
从每个改变数据的事务中获得
在事务结束前一直被保留

UNDO段中数据的作用：
用于回滚操作
读一致性和闪回查询
用于事务失败时的恢复

UNDO段与事务：
一个事务的启动，Oracle将为其分配仅仅一个UNDO段,若该段用完,则Oracle会自动为该UNDO段添加另一个区间(extent)
一个UNDO段能够同时为多个事务服务

UNDO段与UNDO表空间：
UNDO段中的内容存储在UNDO表空间
任意给定时刻只能使用一个UDNO表空间
UNDO表空间必须被创建为持久的、本地管理、可自动扩展的表空间
正在使用的UNDO表空间不能撤销或删除
UNDO表空间使用循环写的方式，与联机日志文件写相似，不同的是UNDO中可以设置了undo_retention 保留时间

UNDO段的两种管理方式：
AUTO自动管理(推荐)
MANUAL手动管理(仅保留)

三、与撤销相关的几个参数
–查看本机中Oracle的版本
SQL> SELECT * FROM v$version;

BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE10.2.0.1.0Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

–查看和UNDO相关的参数
SQL> SHOW PARAMETER undo;

NAMETYPEVALUE

---

undo_managementstringMANUAL
undo_retentioninteger900
undo_tablespacestringUNDOTBS1

undo_management：
设置数据库的撤销段是否使用自动管理模式，值可以为auto或manual，当为manual时将不使用撤销段，即不使用自动管理模式
该参数为静态参数，修改后需重启实例才能生效

undo_retention：
指定撤销段数据在undo段中为非活动状态后被覆盖前保留的时间，单位为秒。在undo_management位auto时生效，为动态参数

undo_tablespace：
指定使用哪个表空间来实现数据的撤销，在undo_management位auto时生效，为动态参数

retention guarantee子句：
保证撤销保留，使用下面的操作来实现
ALTER TABLESPACE undo_tablespace_name RETENTION GUARANTEE;

–下面的查询中是当undo_management为manual时的结果集，可以看出撤销表空间的撤销段都处于offline状态
SQL> SELECT segment_name,tablespace_name,status FROM dba_rollback_segs;

SEGMENT_NAMETABLESPACE_NAMESTATUS

---

SYSTEMSYSTEMONLINE
_SYSSMU1$UNDOTBS1OFFLINE _SYSSMU2$UNDOTBS1OFFLINE
_SYSSMU3$UNDOTBS1OFFLINE _SYSSMU4$UNDOTBS1OFFLINE
_SYSSMU5$UNDOTBS1OFFLINE _SYSSMU6$UNDOTBS1OFFLINE
_SYSSMU7$UNDOTBS1OFFLINE _SYSSMU8$UNDOTBS1OFFLINE
_SYSSMU9$UNDOTBS1OFFLINE _SYSSMU10$UNDOTBS1OFFLINE

–在undo_management 参数为manual时，对scott.emp插入一条新记录，收到了错误提示
–非系统表空间不能够使用回滚段
SQL> INSERT INTO scott.emp(empno,ename,salary)
2VALUES(6666,’Jenney’,3000);
INSERT INTO scott.emp(empno,ename,salary)
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace ‘USERS’

–查看段的类型，发现仅仅system表空间存在ROLLBACK 段，所以前一条插入语句收到错误提示
SQL> SELECT DISTINCT segment_type,tablespace_name FROM dba_segments
2ORDER BY tablespace_name;

SEGMENT_TYPETABLESPACE_NAME

---

INDEXEXAMPLE
INDEX PARTITIONEXAMPLE
LOBINDEXEXAMPLE
LOBSEGMENTEXAMPLE
NESTED TABLEEXAMPLE
TABLEEXAMPLE
TABLE PARTITIONEXAMPLE
INDEXSYSAUX
INDEX PARTITIONSYSAUX
LOB PARTITIONSYSAUX
LOBINDEXSYSAUX

SEGMENT_TYPETABLESPACE_NAME

---

LOBSEGMENTSYSAUX
NESTED TABLESYSAUX
TABLESYSAUX
TABLE PARTITIONSYSAUX
CLUSTERSYSTEM
INDEXSYSTEM
LOBINDEXSYSTEM
LOBSEGMENTSYSTEM
NESTED TABLESYSTEM
ROLLBACKSYSTEM–与之前的版本兼容的回滚段
TABLESYSTEM

SEGMENT_TYPETABLESPACE_NAME

---

TABLETBS1
TYPE2 UNDOUNDOTBS1–9i之后使用的撤销段
INDEXUSERS
LOBINDEXUSERS
LOBSEGMENTUSERS
NESTED TABLEUSERS
TABLEUSERS

–下面将undo_management改为支持自动管理，需要重启实例
SQL> ALTER SYSTEM SET undo_management = ‘auto’ SCOPE = SPFILE;

System altered.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.

Total System Global Area251658240 bytes
Fixed Size1218796 bytes
Variable Size67110676 bytes
Database Buffers180355072 bytes
Redo Buffers2973696 bytes
Database mounted.
Database opened.

–再次查看dba_rollback_segs视图所有的撤销段全部处于online状态
–注意第一行为system表空间的撤销段，用于系统表空间的撤销
SQL> SELECT segment_name,tablespace_name,status FROM dba_rollback_segs;

SEGMENT_NAMETABLESPACE_NAMESTATUS

---

SYSTEMSYSTEMONLINE
_SYSSMU1$UNDOTBS1ONLINE _SYSSMU2$UNDOTBS1ONLINE
_SYSSMU3$UNDOTBS1ONLINE _SYSSMU4$UNDOTBS1ONLINE
_SYSSMU5$UNDOTBS1ONLINE _SYSSMU6$UNDOTBS1ONLINE
_SYSSMU7$UNDOTBS1ONLINE _SYSSMU8$UNDOTBS1ONLINE
_SYSSMU9$UNDOTBS1ONLINE _SYSSMU10$UNDOTBS1ONLINE
由上面的示例可知：
ROLLBACK 段：–与之前的版本兼容的回滚段
TYPE2 UNDO 段：–9i之后使用的撤销段
关于回滚，一个时刻仅能使用一种类段类型，即要么使用与以前版本兼容的回滚段，要么使用撤销段
事实上，在9i之后仅仅支持撤销段，从上面错误的提示即可证实

–查看DML语句产生的事务
SQL> SHOW USER;
USER is “SYS”
SQL> SELECT * FROM scott.emp WHERE ename = ‘SCOTT’;

EMPNO ENAMEJOBMGR HIREDATESALARYDEPTNO

---

7788 SCOTTANALYST7566 19-APR-87350020

SQL> UPDATE scott.emp SET sal = sal * 2 WHEREename = ‘SCOTT’;

1 row updated.

SQL> SELECT addr,xidusn,status,start_time,used_ublk
2FROM v$transaction;

ADDRXIDUSN STATUSSTART_TIMEUSED_UBLK

---

2DA2B17C9 ACTIVE07/10/10 20:29:081

–查看当前哪些用户使用撤销段以及段的大小，启动时间，活动状态等
SQL> SELECT t.xidusn,t.start_time,t.used_ublk,t.status,
s.username,r.segment_name
FROM v$transaction t JOIN v$session s
ON t.ses_addr = s.saddr
JOIN dba_rollback_segs r
ON r.segment_id = t.xidusn ;

XIDUSN START_TIMEUSED_UBLK STATUSUSERNAMESEGMENT_NAME

---

9 07/10/10 20:29:081 ACTIVESYS_SYSSMU9$
四、UNDO表空间的创建与管理
创建UNDO表空间
创建语法：
CREATE UNDO TABLESPACE tablespace_name DATAFILE ‘…’ SIZE n

更多表空间的创建：
请参照：Oracle 表空间与数据文件

切换UNDO表空间
实例中允许多个UNDO表空间存在
可以从一个UNDO表空间切换到另外一个UNDO表空间
任一时刻只能有一个UNDO表空间被指定
使用ALTER SYSTEM SET undo_tablespace = undo_tablespace_name实现切换

删除UNDO表空间
DROP TABLESPACE undo_tablespace_name
任意实例的UNDO表空间在非活动状态可以删除
对于活动状态的UNDO表空间，应当先将切换到其它表空间，在所有事务完成后再删除该表空间

演示创建、切换及删除UNDO表空间
–查看当前系统中的表空间
SQL> SELECT file_name,tablespace_name FROM dba_data_files;

FILE_NAMETABLESPACE_NAME

---

/u01/app/oracle/oradata/orcl/tbs1_2.dbfTBS1
/u01/app/oracle/oradata/orcl/tbs1_1.dbfTBS1
/u01/app/oracle/oradata/orcl/example01.dbfEXAMPLE
/u01/app/oracle/oradata/orcl/users01.dbfUSERS
/u01/app/oracle/oradata/orcl/sysaux01.dbfSYSAUX
/u01/app/oracle/oradata/orcl/undotbs01.dbfUNDOTBS1
/u01/app/oracle/oradata/orcl/system01.dbfSYSTEM

–创建一个新的UNDO表空间undo2
SQL> CREATE UNDO TABLESPACE undo2
2DATAFILE ‘/u01/app/oracle/oradata/orcl/undotbs02.dbf’ SIZE 3M
3AUTOEXTEND ON;

Tablespace created.

SQL> SELECT file_name,tablespace_name FROM dba_data_files WHERE tablespace_name LIKE ‘UNDO%’;

FILE_NAMETABLESPACE_NAME

---

/u01/app/oracle/oradata/orcl/undotbs01.dbfUNDOTBS1
/u01/app/oracle/oradata/orcl/undotbs02.dbfUNDO2

–查看当前系统使用的UNDO表空间为UNDOTBS1
SQL> SELECT name,value FROM v$parameter WHERE name LIKE ‘undo%’;

NAMEVALUE

---

undo_managementAUTO
undo_tablespaceUNDOTBS1
undo_retention900

–创建一张表tb_test用于演示，假定该会话为session1
SQL> CREATE TABLE tb_test
2(
3ID INT,
4Name VARCHAR2(20)
5);

Table created.

–插入一条记录到tb_test，此时未提交将产生UNDO 信息
SQL> INSERT INTO tb_test SELECT 1,’Robinson’ FROM dual;

1 row created.

–此时打开另外一个回话，假定为session2，在session2中切换表空间
SQL> ALTER SYSTEM SET undo_tablespace = ‘undo2’;

System altered.–undotbs1中有未提交的事务，竟然可以成功切换？如此这般闪回时估计会有问题

SQL> SHOW PARAMETER undo;

NAMETYPEVALUE

---

undo_managementstringAUTO
undo_retentioninteger900
undo_tablespacestringundo2

–在session1中执行commit
SQL> COMMIT;

Commit complete.–成功执行了commit,且下面的查询看到了提交后的结果

SQL> SELECT * FROM tb_test;

ID NAME

---

1 Robinson

–登出系统后再次查看，结果依然存在，比较纳闷
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[uniread] Saved history (716 lines)
[oracle@robinson ~]$ sqlplus / as sysdba;

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jul 10 21:29:36 2010

Copyright (c) 1982, 2005, Oracle.All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> SELECT * FROM tb_test;

ID NAME

---

1 Robinson

–删除UNDO表空间
–在session1中插入一条新记录
SQL> INSERT INTO tb_test SELECT 2,’Jack’ FROM DUAL;

1 row created.

–在session2中将撤销表空间切换为undotbs1
SQL> ALTER SYSTEM SET undo_tablespace = ‘undotbs1’;

System altered.

–紧接着在该回话中删除undo2，提示正在使用
SQL> DROP TABLESPACE undo2;
DROP TABLESPACE undo2
*
ERROR at line 1:
ORA-30013: undo tablespace ‘UNDO2’ is currently in use

–在session1中提交事务
SQL> COMMIT;

Commit complete.
–在session2中再次删除表空间undo2，收到相同的错误提示
SQL> /
DROP TABLESPACE undo2
*
ERROR at line 1:
ORA-30013: undo tablespace ‘UNDO2’ is currently in use

SQL> SHOW PARAMETER undo–查看的确是已切换到undotbs1

NAMETYPEVALUE

---

undo_managementstringAUTO
undo_retentioninteger900
undo_tablespacestringundotbs1

–在session2中再次执行表空间切换到undotbs1
SQL> ALTER SYSTEM SET undo_tablespace = ‘undotbs1’;

System altered.

–此时undo2成功删除，可以看出需要在将活动事务提交或回滚后，再切换之后才能成功删除撤销表空间
SQL> DROP TABLESPACE undo2;

Tablespace dropped.

SQL> ho ls $ORACLE_BASE/oradata/orcl;
control01.ctlredo03.logredo2.logsystem01.dbfundotbs02.dbf
control02.ctlredo04.logredo3.logtbs1_1.dbfusers01.dbf
example01.dbfredo07.logredo7.logtbs1_2.dbf
redo01.logredo08.logredo8.logtemp01.dbf
redo02.logredo1.logsysaux01.dbfundotbs01.dbf

–删除UNDO表空间的物理文件
SQL> ho rm $ORACLE_BASE/oradata/orcl/undotbs02.dbf;

–在session1中可以看到两条记录也被成功插入
SQL> SELECT * FROM tb_test;

ID NAME

---

1 Robinson
2 Jack

–查看当前撤销表空间的大小
SQL> SELECT tablespace_name,bytes/1024/1024 FROM dba_data_files
2WHERE tablespace_name = ‘UNDOTBS1’;

TABLESPACE_NAMEBYTES/1024/1024

---

UNDOTBS130

–循环插入记录到tb_test后查看undo表空间的使用情况
SQL> BEGIN
2FOR i IN 1..20000
3LOOP
4INSERT INTO tb_test VALUES(i,’Unkown Name’);
5END LOOP;
6END;
7/

PL/SQL procedure successfully completed.

–可以看到UNDO 表空间只用了个块
SQL> SELECT addr,xidusn,used_ublk FROM v$transaction;

ADDRXIDUSNUSED_UBLK

---

2D9FC1606174

SQL> SELECT 174 * 8 || ‘KB’ FROM dual;

174*8|

1392KB
五、计算UNDO表空间的大小
计算公式：
MAX(undoblks)/600 * MAX(maxquerylen)位于v$undostat * db_block_size位于v$parameter

–创建演示环境
SQL> INSERT INTO tb_test SELECT employee_id,first_name FROM hr.employees;

107 rows created
SQL> INSERT INTO tb_test SELECT * from tb_test;

109 rows created.

–多次执行上述命令，下面是的tb_test表中的记录数
SQL> /

892928 rows created.
SQL> COMMIT;

Commit complete.

–查看当前undo表空间的大小
SQL> SELECT t.name,d.name,d.bytes/1024/1024 as TotalSize ,t.flashback_on,d.status
2FROM v$tablespace t 3JOIN v$datafile d
4USING (ts#)
5WHERE t.name LIKE ‘UNDO%’;

NAMENAMETOTALSIZE FLA STATUS

---

UNDOTBS1/u01/app/Oracle/oradata/orcl/undotbs01.dbf30 YES ONLINE

–将undo表空间修改为RETENTION GUARANTEE及关闭自动扩展
SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

Tablespace altered.

SQL> ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/orcl/undotbs01.dbf’ AUTOEXTEND OFF;

Database altered.

SQL> SELECT tablespace_name,contents,retention FROM dba_tablespaces
2WHERE tablespace_name LIKE ‘UNDO%’;

TABLESPACE_NAMECONTENTSRETENTION

---

UNDOTBS1UNDOGUARANTEE

–修改保留时间为分钟
SQL> ALTER SYSTEM SET undo_retention = 120;

System altered.

–循环删除tb_test中的记录，提示undo表空间空间容量不够
SQL> BEGIN
2FOR i IN 1..1000
3LOOP
4DELETE FROM tb_test WHERE rownum < 1001;
5COMMIT;
6END LOOP;
7END;
8/
BEGIN
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDOTBS1’
ORA-06512: at line 4

–修改回话的时间参数
SQL> ALTER SESSION SET nls_date_format=’yyyy-mm-dd HH24:MI:SS’;

Session altered.

–查看v$undostat视图，获得相关信息 SQL> SELECT begin_time,end_time,undoblks,maxquerylen, ssolderrcnt,nospaceerrcnt 2FROM v$undostat;

BEGIN_TIMEEND_TIMEUNDOBLKS MAXQUERYLEN SSOLDERRCNT NOSPACEERRCNT

---

2010-07-12 19:12:18 2010-07-12 19:22:186000
2010-07-12 19:02:18 2010-07-12 19:12:189000
2010-07-12 18:52:18 2010-07-12 19:02:1847000
2010-07-12 18:42:18 2010-07-12 18:52:182136001
2010-07-12 18:32:18 2010-07-12 18:42:186000
2010-07-12 18:22:18 2010-07-12 18:32:18413154100
2010-07-12 18:12:18 2010-07-12 18:22:1817993800
2010-07-12 18:02:18 2010-07-12 18:12:186000

–计算undo表空间所需的大小
SQL> SELECT (
2(SELECT MAX(undoblks)/600 * MAX(maxquerylen) FROM v$undostat) * 3(SELECT value FROM v$parameter WHERE name = ‘db_block_size’))/1024/1024 as Need_Size
4FROM dual;

NEED_SIZE

42.8590625

–取消撤销保留选项
SQL> ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;

Tablespace altered

六、UNDO配额
对于超长的事务或不当的SQL脚本将耗用大量的UNDO表空间，使用UNDO表空间配额可以提高资源的利用率
对于不同组的用户可以分配不同的最大UNDO表空间配额
当某个组超出了最大的资源限制，则该组不允许新的事务产生，直到当前组的UNDO表空间释放或终止

七、撤销常见的两个错误
1.ORA-1555 snapshot too old 快照过旧错误的解决
配置合适的保留时间(undo_retention)
调整undo表空间的大小
考虑保证撤销保留的使用(retention guarantee)

2.ORA-30036 unable to extend segment in undo tablespace 无法扩展撤销表空间内的撤销段
调整undo表空间的大小
确保大量的事务能够周期性的提交

八、UNDO涉及的几个相关视图：
V$TRANSACTION V$SESSION
DBA_ROLLBACK_SEGS–显示所有的segments
V$ROLLSTAT V$UNDOSTAT
V$ROLLNAME–显示当前在线的segments

关于UNDO涉及视图的更多信息，请参考oracle的在线文档