Oracle 回滚(ROLLBACK)和撤销(UNDO)_综合

--==================================

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

--==================================

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

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

在9i版本以前

Oracle使用数据库中的回滚段来实现未提交数据或因系统故障导致实例崩溃时进行回滚操作

每一个表空间需要创建回滚段，各个表空间对回滚段实现各自的管理

在9i及后续版本

提供了一种新的回滚数据的管理方式，即使用Oracle自动管理的撤销(Undo)表空间

自动撤销管理表空间统一管理所有DML的回滚操作，简化了对于回滚工作的管理

在9i,10g中的回滚段仅仅用作保留向后兼容

撤销段代替了原有版本中的回滚段，因此本文所有描述均使用撤销

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

二、撤销段中的内容及相关特性

对于任何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

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

--查看和UNDO相关的参数

SQL> SHOW PARAMETER undo;

NAME TYPE VALUE

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

undo_management string MANUAL

undo_retention integer 900

undo_tablespace string UNDOTBS1

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_NAME TABLESPACE_NAME STATUS

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

SYSTEM SYSTEM ONLINE

_SYSSMU1$ UNDOTBS1 OFFLINE

_SYSSMU2$ UNDOTBS1 OFFLINE

_SYSSMU3$ UNDOTBS1 OFFLINE

_SYSSMU4$ UNDOTBS1 OFFLINE

_SYSSMU5$ UNDOTBS1 OFFLINE

_SYSSMU6$ UNDOTBS1 OFFLINE

_SYSSMU7$ UNDOTBS1 OFFLINE

_SYSSMU8$ UNDOTBS1 OFFLINE

_SYSSMU9$ UNDOTBS1 OFFLINE

_SYSSMU10$ UNDOTBS1 OFFLINE

--在undo_management 参数为manual时，对scott.emp插入一条新记录，收到了错误提示

--非系统表空间不能够使用回滚段

SQL> INSERT INTO scott.emp(empno,ename,salary)

2 VALUES(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

2 ORDER BY tablespace_name;

SEGMENT_TYPE TABLESPACE_NAME

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

INDEX EXAMPLE

INDEX PARTITION EXAMPLE

LOBINDEX EXAMPLE

LOBSEGMENT EXAMPLE

NESTED TABLE EXAMPLE

TABLE EXAMPLE

TABLE PARTITION EXAMPLE

INDEX SYSAUX

INDEX PARTITION SYSAUX

LOB PARTITION SYSAUX

LOBINDEX SYSAUX

SEGMENT_TYPE TABLESPACE_NAME

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

LOBSEGMENT SYSAUX

NESTED TABLE SYSAUX

TABLE SYSAUX

TABLE PARTITION SYSAUX

CLUSTER SYSTEM

INDEX SYSTEM

LOBINDEX SYSTEM

LOBSEGMENT SYSTEM

NESTED TABLE SYSTEM

ROLLBACK SYSTEM --与之前的版本兼容的回滚段

TABLE SYSTEM

SEGMENT_TYPE TABLESPACE_NAME

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

TABLE TBS1

TYPE2 UNDO UNDOTBS1 --9i之后使用的撤销段

INDEX USERS

LOBINDEX USERS

LOBSEGMENT USERS

NESTED TABLE USERS

TABLE USERS

--下面将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 Area 251658240 bytes

Fixed Size 1218796 bytes

Variable Size 67110676 bytes

Database Buffers 180355072 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

--再次查看dba_rollback_segs视图所有的撤销段全部处于online状态

--注意第一行为system表空间的撤销段，用于系统表空间的撤销

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

由上面的示例可知：

ROLLBACK 段： --与之前的版本兼容的回滚段

TYPE2 UNDO 段： --9i之后使用的撤销段

关于回滚，一个时刻仅能使用一种类段类型，即要么使用与以前版本兼容的回滚段，要么使用撤销段

事实上，在9i之后仅仅支持撤销段，从上面错误的提示即可证实

--查看DML语句产生的事务

SQL> SHOW USER;

USER is "SYS"

SQL> SELECT * FROM scott.emp WHERE ename = 'SCOTT';

EMPNO ENAME JOB MGR HIREDATE SALARY DEPTNO

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

7788 SCOTT ANALYST 7566 19-APR-87 3500 20

SQL> UPDATE scott.emp SET sal = sal * 2 WHERE ename = 'SCOTT';

1 row updated.

SQL> SELECT addr,xidusn,status,start_time,used_ublk

2 FROM v$transaction;

ADDR XIDUSN STATUS START_TIME USED_UBLK

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

2DA2B17C 9 ACTIVE 07/10/10 20:29:08 1

--查看当前哪些用户使用撤销段以及段的大小，启动时间，活动状态等

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_TIME USED_UBLK STATUS USERNAME SEGMENT_NAME

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

9 07/10/10 20:29:08 1 ACTIVE SYS _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_NAME TABLESPACE_NAME

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

/u01/app/oracle/oradata/orcl/tbs1_2.dbf TBS1

/u01/app/oracle/oradata/orcl/tbs1_1.dbf TBS1

/u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE

/u01/app/oracle/oradata/orcl/users01.dbf USERS

/u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX

/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1

/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM

--创建一个新的UNDO表空间undo2

SQL> CREATE UNDO TABLESPACE undo2

2 DATAFILE '/u01/app/oracle/oradata/orcl/undotbs02.dbf' SIZE 3M

3 AUTOEXTEND ON;

Tablespace created.

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

FILE_NAME TABLESPACE_NAME

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

/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1

/u01/app/oracle/oradata/orcl/undotbs02.dbf UNDO2

--查看当前系统使用的UNDO表空间为UNDOTBS1

SQL> SELECT name,value FROM v$parameter WHERE name LIKE 'undo%';

NAME VALUE

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

undo_management AUTO

undo_tablespace UNDOTBS1

undo_retention 900

--创建一张表tb_test用于演示，假定该会话为session1

SQL> CREATE TABLE tb_test

2 (

3 ID INT,

4 Name 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;

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 900

undo_tablespace string undo2

--在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

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

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 900

undo_tablespace string undotbs1

--在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.ctl redo03.log redo2.log system01.dbf undotbs02.dbf

control02.ctl redo04.log redo3.log tbs1_1.dbf users01.dbf

example01.dbf redo07.log redo7.log tbs1_2.dbf

redo01.log redo08.log redo8.log temp01.dbf

redo02.log redo1.log sysaux01.dbf undotbs01.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

2 WHERE tablespace_name = 'UNDOTBS1';

TABLESPACE_NAME BYTES/1024/1024

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

UNDOTBS1 30

--循环插入记录到tb_test后查看undo表空间的使用情况

SQL> BEGIN

2 FOR i IN 1..20000

3 LOOP

4 INSERT INTO tb_test VALUES(i,'Unkown Name');

5 END LOOP;

6 END;

7 /

PL/SQL procedure successfully completed.

--可以看到UNDO 表空间只用了个块

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

ADDR XIDUSN USED_UBLK

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

2D9FC160 6 174

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

2 FROM v$tablespace t

3 JOIN v$datafile d

4 USING (ts#)

5 WHERE t.name LIKE 'UNDO%';

NAME NAME TOTALSIZE FLA STATUS

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

UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf 30 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

2 WHERE tablespace_name LIKE 'UNDO%';

TABLESPACE_NAME CONTENTS RETENTION

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

UNDOTBS1 UNDO GUARANTEE

--修改保留时间为分钟

SQL> ALTER SYSTEM SET undo_retention = 120;

System altered.

--循环删除tb_test中的记录，提示undo表空间空间容量不够

SQL> BEGIN

2 FOR i IN 1..1000

3 LOOP

4 DELETE FROM tb_test WHERE rownum < 1001;

5 COMMIT;

6 END LOOP;

7 END;

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

2 FROM v$undostat;

BEGIN_TIME END_TIME UNDOBLKS MAXQUERYLEN SSOLDERRCNT NOSPACEERRCNT

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

2010-07-12 19:12:18 2010-07-12 19:22:18 6 0 0 0

2010-07-12 19:02:18 2010-07-12 19:12:18 9 0 0 0

2010-07-12 18:52:18 2010-07-12 19:02:18 47 0 0 0

2010-07-12 18:42:18 2010-07-12 18:52:18 2136 0 0 1

2010-07-12 18:32:18 2010-07-12 18:42:18 6 0 0 0

2010-07-12 18:22:18 2010-07-12 18:32:18 413 1541 0 0

2010-07-12 18:12:18 2010-07-12 18:22:18 179 938 0 0

2010-07-12 18:02:18 2010-07-12 18:12:18 6 0 0 0

--计算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

4 FROM 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的在线文档

九、更多

Oracle 参数文件

SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)

Oracle 用户、对象权限、系统权限

Oracle 角色、配置文件

SQL 基础--> 集合运算(UNION 与UNION ALL)