管理undo

一、UNDO的自动管理与手动管理
undo推荐使用自动管理
在早期的版本中，undo使用手动管理，管理起来相当的麻烦烦琐。
要手动的创建undo的回滚段，要手动使这些回滚段上线，要手动的指定事务使用哪个回滚段。
如下例
[oracle@oel ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu May 12 17:06:54 2016
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


查看当前系统中使用的UNDO表空间
SQL> show parameter undo_tablespace
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1


查看当前系统中undo的管理方式是手动还是自动
SQL> show parameter undo_management
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO


修改UNDO的管理方式为手动，并重启数据库
SQL> alter system set undo_management=manual scope=spfile;
System altered.


SQL> startup force
ORACLE instance started.


Total System Global Area  389189632 bytes
Fixed Size                  1336736 bytes
Variable Size             314575456 bytes
Database Buffers           67108864 bytes
Redo Buffers                6168576 bytes
Database mounted.
Database opened.


确认UNDO的管理方式
SQL> show parameter undo_management
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL


查看当前系统中有哪些UNDO回滚段在使用
SQL> select * from v$rollname;
       USN NAME
---------- ------------------------------
         0 SYSTEM


0段回滚段，提供给system表空间中数据字典的变更时使用，不推荐普通用户的普通事务使用system的回滚段，帮我们自己创建一个回滚段出来。
为了实验，我们先创建一个UNDO表空间，叫UNDOTBS2，大小3M。足够小，一会好演示，UNDO不够用的时候，DML操作会报错，因为这么小的UNDO表空间，不足以存储下DML操作所更改的块的镜像。、




创建UNDO表空间
SQL> create  undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/orcl/undotbs2.dbf' size 3m;
Tablespace created.


创建UNDO回滚段
SQL> create rollback segment u1 tablespace undotbs2;
Rollback segment created.


使用UNDO回滚段U1上线
SQL> alter rollback segment u1 online;
Rollback segment altered.


确认UNDO回滚段已经上线
SQL> select * from v$rollname;
       USN NAME
---------- ------------------------------
         0 SYSTEM
        21 U1


指定事务使用的UNDO回滚段为U1
SQL> set transaction use rollback segment u1;
Transaction set.


可见，手动管理UNDO，是件十分繁琐的工作。所以系统默认是自动管理，自动创建回滚段自动分配给事务使用。




二、UNDO空间不足，会导致DML执行失败。


确认UNDO的表空间，以及UNDO管理方式


SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1


修改UNDO的管理方式为自动，并重启数据库使之生效。
SQL> alter system set undo_management=auto scope=spfile;
System altered.


SQL> startup force
ORACLE instance started.


Total System Global Area  389189632 bytes
Fixed Size                  1336736 bytes
Variable Size             314575456 bytes
Database Buffers           67108864 bytes
Redo Buffers                6168576 bytes
Database mounted.
Database opened.
SQL>




创建一张大表，利用dba_objects这个表创建，这个表有7万多条记录 
SQL> create table big_t as select * from dba_objects;
Table created.


进行该表的递归插入，确保这表让我们搞的足够大。提交插入
SQL> insert into big_t select * from big_t;
72507 rows created.


SQL> /
145014 rows created.


SQL> /
290028 rows created.


SQL> /
580056 rows created.


SQL> commit;
Commit complete.




确认UNDO的表空间及管理方式
SQL> show parameter undo


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1、




变更当前系统默认的UNDO表空间，我们讲过，同一时间，只能使用一个UNDO表空间，所以，我们把UNDO表空间，切换成我们之前创建的那个只有3M的UNDOTBS2


SQL> alter system set undo_tablespace=undotbs2;
System altered.alter


我们执行一个需要大量UNDO空间的DML操作，delete big_t，因为BIG_t这个表，有几十万行记录，所占的空间不只3M，对全表进行DELETE，需要把这个表所有的块都进行镜像。而undo表空间只有3M，完全放不下这张大表，所以报错。
SQL> delete big_t;
delete big_t
       *
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'




三、模拟快照过旧的错误
当undo空间不足的时候，会覆盖已经commit事务所占用的undo数据空间，当需要查询这个已经commit事务之前的undo数据时（例如闪回查询），会报ORA-1555快照过旧的错误，例如：1633878


为了实验，也给scott一些权限，方便做查询，不必来回切换sys用户去查询了
SQL> grant select on dba_objects to scott;
Grant succeeded.


SQL> grant select on v_$transaction to scott;
Grant succeeded.


SQL> grant select on v_$session to scott;
Grant succeeded.


SQL> grant select on v_$mystat to scott;
Grant succeeded.


SQL> grant select on v_$database to scott;
Grant succeeded.


创建一个undo表空间，大小30M
SQL> create undo tablespace undotbs3 datafile '/u01/app/oracle/oradata/orcl/undotbs3.dbf' size 30m;
Tablespace created.


设置undotbs3为系统默认的Undo表空间
SQL> alter system set undo_tablespace=undotbs3;
System altered.


我们计算一下30M能存储多少个8k的数据块，计算得出3840个块，也就是说，这个undo表空间，最多能存储3840个事务变更之前的数据块的镜像。
SQL> select 30*1024*1024/8192 from dual;


30*1024*1024/8192
-----------------
             3840




连接到scott用户
SQL> conn scott/tiger
Connected.


清除实验环境
SQL> drop table t1 purge;
drop table t1 purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist




SQL> drop table t2 purge;
drop table t2 purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist




创建T1表，根据dba_objects，这个表足够大，有7万多行
SQL> create table t1 as select * from dba_objects;
Table created.




记录下来，当前的系统的SCN（代表前当前的时间信息，SCN是SYSTEM CHANGE NUMBER的缩写，代表系统变化的状态，跟时间也有关系，在数据库中是使用SCN来精确记录数据变化系统变化的），当前这个表刚创建完毕，表中应该有7万多行。


SQL> select current_Scn from v$database;


CURRENT_SCN
-----------
    2051290


确认表中的行数。
SQL> select count(*) from t1;


  COUNT(*)
----------
     72508




执行全表删除的DML，但是不要提交。
SQL> delete t1;
72508 rows deleted.


此时，该事务没有提交，所以，在v$transaction里能查到该事务的一些状态信息，我们查询该事务（即delete t1;）共使用了多少个UNDO数据块，可以得出，我们使用了2119个UNDO数据块，这个UNDO表空间，一共30M，一共有3840个块，这一个事务就用过2119个，还剩余1721个数据块。


SQL> select used_ublk from v$transaction where addr=(select taddr from v$session where sid=(select sid from v$mystat where rownum=1));


 USED_UBLK==
----------
      2119


查询完，我们COMMIT，把这个事务提交掉，此时，这个事务所使用的undo数据块，ORACLE会尽量帮我们保存900s，但是，如果有新的事务，也需要大量的UNDO数据块，我们剩余的1721个块不够用的时候，ORACLE会复用我们刚才已经提交事务的UNDO空间，也就是说，我们再搞一个大的事务，就有可能造成之前这个事务的UNDO数据被覆盖，在还没有到900s的之前，所以这个尽量，不是强制保留。
SQL> commit;
Commit complete.


此时该表中，0行，被删除空了
SQL> select count(*) from t1;


  COUNT(*)
----------
         0


我们现在使用闪回查询，可以查询到，我们之前刚刚创建该表时的状态，利用刚才我们保存的SCN，可以看到，那个时候，该表还有数据，72508行
SQL> select count(*) from t1 as of scn 2051290;


  COUNT(*)
----------
     72508




我们再开一个窗口，再连接一个会话
[root@oel ~]# su - oracle
[oracle@oel ~]$ sqlplus scott/tiger


SQL*Plus: Release 11.2.0.1.0 Production on Thu May 12 17:34:15 2016
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


再创建一个大表，t2，也是根据dba_objects
SQL> create table t2 as select * from dba_objects;
Table created.


再次执行删除该表，不要提交
SQL> delete t2;
72509 rows deleted.


再次通过v$transaction查询该事务的一些信息，其中包括，该事务使用多少个UNDO 数据块。可以看到，共使用了2119个UNDO数据块，刚才我们在执行删除T1表时，已经使用了2119个，使用后剩余的1721个块，不足以完成现在的这个删除的事务，所以，ORACLE使用了上一个事务的UNDO空间，因为上一个事务已经提交了，在空间足够的情况，会尽量帮上一个事务保留这些数据900S，但是现在空间不够了。


SQL> select used_ublk from v$transaction where addr = (select taddr from v$session where sid = (select sid from v$mystat where rownum = 1));


 USED_UBLK
----------
      2119


我们提交我们的事务
SQL> commit;


Commit complete.




现在我们去闪回查询T1表，刚刚创建的时候，我们保存的那个SCN对应的时间T1的状态。
为了确保实验结果，我们清空一下数据库高速缓存中的数据，防止我们查询的数据，都在内存中被缓存，影响实验结果。
SQL> conn / as sysdba
Connected.


清空数据库高速缓存
SQL> alter system flush buffer_cache;
System altered.


清空shared_pool
SQL> alter system flush shared_pool;
System altered.


使用闪回查询，发生快照过旧的错误。由于undo表空间太小，第一个会话中的undo数据被覆盖，所以报快照太旧错误
SQL> select count(*) from scott.t1 as of scn 2051290;
select count(*) from scott.t1 as of scn 2051290
                           *
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 13 with name "_SYSSMU13_1872547554$"
too small


1.Select file_name,file_id,status,bytes,tablespace_name 
From dba_data_files where tablespace_name like '%undo%'


2.select autoextensible,file_id,file_name from dba_data_files;


3.create tablespace test datafile '/u01/app/oracle/oradata/orcl/test1.dbf' size 30m autoextented on next 1m maxsize 1g;


4.Select file_name,file_id,status,bytes,tablespace_name,autoextensible --- 表空间能不能自动扩展的属性是由数据文件datafile决定是 
From dba_data_files where tablespace_name=’OAEC’


四、总结一下undo_retention：一个事务没有提交，这个事务undo数据一定不会被覆盖


undo_retention：已经提交事务产生的undo数据保留的最小时间


1、对于UNDO表空间的数据文件属性为autoextensible，即UNDO表空间是自动扩展的，则undo_retenion参数设置UNDO信息将至少保留至undo_retention参数设定的时间，
表空间是不是自动扩张是数据文件的的属性
Alter database datafile 6 resize 30m autoextend on next 1 maxsize 40m; 
a.如果空间不够，oracle会自动扩展UNDO表空间，不会覆盖未过期的undo数据，
b.如果undo表空间设置了maxsize，当到达maxsize，oracle会覆盖未过期的undo数据
c.如果undo表空间到达了所有数据文件所能增长的最大值（8k标准块数据文件最大能扩展到32GB），oracle会覆盖未过期的undo数据
  数据文件拥有的块的最大值为400万块， 400W*8k=32g


2、对于固定大小的UNDO表空间，将会通过表空间的剩余空间来最大限度保留UNDO信息。


a.如果固定大小的UNDO表空间没有对保留时间作GUARANTEE（即alter tablespace xxx retention guarantee;），
则undo_retention参数将不会起作用，oracle根据系统活动和表空间的大小自动调整undo_retention
a
b.如果设置UNDO表空间为retention guarantee，已经提交的事务transaction则未过期的数据不会被复写，如果表空间不够则会导致DML操作失败或者transation挂起。
alter tablespace undotbs2 retention guarantee
alter system set undo_tablespace='UNDOTBS2';
drop tablespace undotbs3 including contents and datafiles;