欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-07-04 22:34:25
...
--==================================
--Oracle 回滚(ROLLBACK)和撤销(UNDO)
--==================================
一、回滚(ROLLBACK)和撤销(UNDO)
    回滚和前滚是保证Oracle数据库中的数据处于一致性状态的重要手段。
    在9i版本以前
        Oracle使用数据库中的回滚段来实现未提交数据或因系统故障导致实例崩溃时进行回滚操作
        每一个表空间需要创建回滚段,各个表空间对回滚段实现各自的管理
    在9i及后续版本
        提供了一种新的回滚数据的管理方式,即使用Oracle自动管理的撤销(Undo)表空间
        自动撤销管理表空间统一管理所有DML的回滚操作,简化了对于回滚工作的管理
        在i,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之后使用的撤销段

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

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

 

    --查看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)

        [[email protected] ~]$ 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

 

        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)