【理论】关于UNDO表空间的管理介绍

 

关于UNDO表空间的管理介绍

 

• Oracle undo表空间用于存放undo数据。当用户运行DML操作时，undo数据被放在undo段，可以回退事务。运行一个事务时，新数据存放在目标数据段中，如果事务存在问题或者故障，也可以用undo数据来实现闪回或者恢复。Oracle的Undo表空间有两种管理方式：一是使用undo表空间，二是使用回滚段。

• 我们通过undo_management参数来控制使用哪种方式，如果设为auto，就使用UNDO表空间，这时必须要指定一个UNDO 表空间。如果设为Manual，系统启动后使用rollback segment（回滚段）方式存储undo信息。如果系统没有指定undo_management，那么系统默认以Manual方式启动，即使设置了auto方式的参数，这些参数将被忽略。也就是说，如果创建了UNDO tablespace。但是不设置undo_management参数为AUTO，数据库依旧无法使用UNDO tablespace。

SQL> SHO PARAMETER UNDO_MANAGEMENT;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO

• 当实例启动的时候，系统自动选择第一个有效的undo表空间或者是rollback segment，如果无有效的可用undo表空间或者是回滚段，系统使用system rollback segment。这种情况一般是不被推荐的，当系统运行在没有undo的情况下，系统会在alert.log中记录一条警告信息。UNDO_MANAGEMENT用于指定undo数据的管理方式。如果使用自动管理，必须设置为AUTO；如果手动管理，必须设置为MANUAL。使用自动管理的时候，oracle会使用UNDO表空间管理UNDO数据；而使用手动管理的时候，oracle会使用回滚段管理UNDO数据。如果我们使用自动管理模式，必须建立UNDO表空间，并且配置UNDO_TABLESPACE参数，否则oracle会使用SYSTEM回滚段存放UNDO记录，并在警告日志中记录相关日志信息。

• undo tablespace表空间的大小由3个参数决定：

    1、block的大小，一般为8192bytes
    2、每分钟产生的undo数据
    3、undo_retention的大小，一般为900秒。

     （意思就是只要undo 表空间允许，undo数据就可以保存900秒）

SQL> SHO PARAMETER UNDO_RETENTION

AME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     900

 

• ORA-30012错误是由于undo表空间导致的。如果我们undo表空间错误，则需要我们重新创建，一般我们可以在参数文件中修改这两个参数

undo_management=’manual’,undo_tablespace=’system’

• 再启动服务就可以重新启动数据库，这时要创建undo表空间，用create undo tablespace undotbs datafile ’e:\oradata\whx\undotbs.dbf ’ size 100m;语句创建撤消表空间，然后把相应的oracle undo参数设置为undo_management=’auto’,undo_tablespace=’undotbs’即可。一个数据库只有一个undo表空间，但RAC有两个undo表空间，因为一个UNDO tablespace不能由多个例程同时使用。

• CREATE UNDO表空间

• 1、使用CREATE DATABASE命令建立UNDO表空间。如果使用的是UNDO管理模式，但是没 有指定UNDO TABLESPACE（撤消表空间），那么建立数据库的时候oracle会自动生成名称为SYS_UNDOTBS（UNDOTBS1）的UNDO表空间。

• 2、使用CREATE UNDO TABLESPACE 命令建立UNDO表空间。建立数据库以后，可以使用该命令建立UNDO表空间。

• 3、修改UNDO表空间。当事务用尽UNDO表空间后，增加数据文件：ALTER TABLESPACE … ADD DATAFILE;

     |当UNDO表空间所在磁盘填满时，移动数据文件到其他磁盘：
      -|ALTER TABLESPACE …RENAME DATAFILE;
     |当在OPEN状态下移动UNDO表空间时，可以使表空间脱机/联机：
      -|ALTER TABLESPACE … OFFLINE/ONLINE;
     |当数据库处于归档模式时，备份UNDO表空间：
      -|ALTER TABLESPACE … BEGIN BACKUP/END BACKUP

 

• 切换UNDO表空间

• 动并打开oracle数据库后，同一时刻只能使用一个UNDO表空间。可以切换UNDO表空间：
  

     ALTER SYSTEM SET undo_tablespace = undotbs02;

 

• 删除UNDO表空间

• 当前使用的UNDO表空间不能被删除，如果要删除当前实例使用的UNDO，需要先切换至新的撤消表空间，然后删除: DROP TABLESPACE undotbs01;

• 监控UNDO表空间的使用：

 1、确定当前正在使用的undo表空间：

SQL> show parameter undo_tablespace 

SQL> SHO PARAMETER UNDO_TABLESPACE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1

• Oracle的Undo有两种方式：一是使用undo 表空间，二是使用回滚段。我们通过指定参数undo_management来控制使用哪种方式，如果设为auto，就使用UNDO tablespace表空间，这时必须要指定一个UNDO表空间。如果设为manuall，系统启动后使用rollback segment方式存储undo信息。如果系统没有指定undo_management，那么系统默认以manual方式启动，即使设置了auto方式的参数，这些参数将被忽略。

• 当实例启动的时候，系统自动选择第一个有效的undo表空间或者是rollback segment，如果无可用的undo表空间或者是回滚段，系统使用system rollback segment。这种情况是不被推荐的，当系统运行在没有undo的情况下，系统会在alert.log中记录一条警告信息。

• 查询UNDO表空间信息

• 下面先来看一下undo表空间的使用情况：

SELECT   a.tablespace_name,
         ROUND (a.total_size) “total_size(MB)”,
         ROUND (a.total_size) – ROUND (b.free_size, 3) “used_size(MB)”,
         ROUND (b.free_size, 3) “free_size(MB)”,
         ROUND (b.free_size / total_size * 100, 2) || ‘%’ free_rate
  FROM   (  SELECT   tablespace_name, SUM (bytes) / 1024 / 1024 total_size
              FROM   dba_data_files
          GROUP BY   tablespace_name) a,
         (  SELECT   tablespace_name, SUM (bytes) / 1024 / 1024 free_size
              FROM   dba_free_space
          GROUP BY   tablespace_name) b
 WHERE   a.tablespace_name = b.tablespace_name(+);

TABLESPACE_NAME   TOTAL_SIZE(MB)  USED_SIZE(MB)  FREE_SIZE(MB)   FREE_RATE
---------------- ----------------- ------------- --------------- ---------
SYSAUX           830               785.75        44.25           5.33%
UNDOTBS1         280               54.312        225.688         80.6%
SPACE_TRADE      200               2             198             99%
SPACE_MASTER     50                1             49              98%
USERS            5                 1.312         3.688           73.75%
SYSTEM           810               809.562       0.43            8.05%
TRADE            5000              163.187       4836.813        96.74%
UNDOTBS2         100               2.25          97.75           97.75%
8 rows selected

SQL> 
SELECT tablespace_name, status, SUM(bytes) / 1024 / 1024 "Bytes(M)"
  FROM dba_undo_extents
 GROUP BY tablespace_name, status;

SQL>
TABLESPACE_NAME    STATUS      Bytes(M)
-------------------   ---------    --------------
UNDOTBS1                UNEXPIRED       10
UNDOTBS2                UNEXPIRED       0.625
UNDOTBS1                EXPIRED           43.3125
UNDOTBS2                EXPIRED           0.625

• 我们看一下查询的结果，UNEXPIRED和EXPIRED是已使用的undo 表空间，其中expired说明是已经过期的数据，也就是15分钟（默认情况）以外的数据，可以被系统覆盖重复使用，可以认为是空闲的。

• 采用UNDO表空间时，会有一个参数UNDO_RETENTION，该参数用来指定undo记录保存的最长时间，以秒为单位，是一个动态参数，完全可以在实例运行时随时修改，通常默认是900 秒，也就是15分钟。

• undo_retention只是指定undo数据的过期时间，并不意味着undo 中的数据一定会在undo表空间中保存15 分钟。例如一个新事务开始的时候，如果undo 表空间已经被写满，则新事务的数据会自动覆盖已提交事务的数据，而不管这些数据是否已过期。因此，当创建一个自动管理的undo 表空间时，还要注意其空间大小，要尽可能保证undo 表空间有足够的存储容量。

• undo_retention 中指定的时间一过，已经提交事务中的数据就立刻无法访问，但它只是失效，只要不被别的事务覆盖，它会仍然存在，并可随时被flashback 特性引用。如果的undo表空间足够大，而数据库又不是那么繁忙，那么其实undo_retention参数的值并不会影响到数据库的正常运行。

• 只有在一种情况下，undo表空间能够确保undo中的数据在undo_retention 指定时间过期前一定有效，就是为undo 表空间指定Retention Grantee，指定之后，oracle 对于undo 表空间中未过期的undo数据不会覆盖，例如：

SQL> Alter tablespace undotbs1 retention grantee

• 禁止undo 表空间retention g rantee，例如：

SQL> Alter tablespace undotbs1 retention nograntee 

• UNDO表空间是重用的，只有当事务尚未结束或开了retention g rantee参数在undo_retention时间内不能被重用。在undo_retention规定的时间内，数据都是有效的，过期后都会设为无效，状态被改为Expired，这些回滚段将会被看作Free Space。但是只要数据没有被覆盖就可以使用。如果空间已满，新事务的数据会自动覆盖掉已经提交的事务数据，即使在undo_retention的时间内。除非指定Retention Grantee模式，才能保证在undo_retention时间内不被覆盖。

• UNDO表空间扩容和切换
• 1、建立新的表空间UNDOTBS2

SQL> CREATE UNDO TABLESPACE UNDOTBS2 
     DATAFILE‘F:\backup\undo03.dbf’size 100M reuse;
表空间已创建。

•  2、切换到新建的UNOD表空间上来,操作如下

SQL> alter system set undo_tablespace=UNDOTBS2 scope=both;
系统已更改。

•  3、将原来的UNDO表空间,置为脱机:

SQL> alter tablespace UNDO offline;
表空间已更改。

•  4、删除原来的UNDO表空间:

SQL> drop tablespace UNDO including contents and datafiles cascade constraints ;

• 如果只是drop tablespace UNDO ，则只会在删除控制文件里的记录，并不会物理删除文件。Drop undo表空间的时候必须是在未使用的情况下才能进行。如果undo表空间正在使用（例如事务失败，但是还没有恢复成功），那么drop表空间命令将失败。在drop表空间的时候可以使用including contents参数选项。

• UNDO表空间损坏的恢复方法

• 一般Undo表空间损坏的情况，数据库都已不能正常打开了。启动时都会报类似如下错误：

ORA-01157: cannot identify/lock datafile 12 – see DBWR trace file
ORA-01110: data file 12: ‘/d01/ora11g/prodata/undo01.dbf’

• 要想解决这个问题，必须重建UNDO 表空间，但是如果数据库不是处于open状态，就不能重新创建undo 表空间。所以可以先用系统默认的undo表空间：system rollback segment 来启动数据库，再创建UNDO 表空间。

• 1.创建pfile 文件

SQL> create pfile=’F:\initorcl.ora’ from spfile;
文件已创建。

• 2.修改pfile文件

#*.undo_tablespace=’UNDOTBS1′
#*.undo_management=’AUTO’
undo_management=’MANUAL’
rollback_segments=’SYSTEM’

• 3.启动数据库至Mount 状态

SQL> STARTUP MOUNT pfile=’F:\initorcl.ora’;

• 4.offline drop undo 表空间

SQL> ALTER DATABASE DATAFILE‘D:\app\Administrator\oradata\orcl\UNDOTBS01.DBF’ OFFLINE DROP;

• 5.open 数据库

SQL> ALTER DATABASE OPEN;

• 6.删除旧的undo 表空间

SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS; 

  注：该命令不会删除物理文件。要想一起删除物理文件需要 AND DATAFILES CASCADE CONSTRAINTS ;

  如drop tablespace UNDOTBS1 including contents AND DATAFILES CASCADE CONSTRAINTS ;

 

• 7.创建新的UNDO 表空间

SQL>create undo tablespace undotbs1
    datafile ‘D:\app\Administrator\oradata\orcl\UNDOTBS01.DBF’ size 100M ;

create undo tablespace undotbs1 
datafile ‘D:\app\Administrator\oradata\orcl\UNDOTBS01.DBF’ size 100M
*
第 2 行出现错误:
ORA-01119: 创建数据库文件 ‘D:\app\Administrator\oradata\orcl\UNDOTBS01.DBF’时出错
ORA-27038: 所创建的文件已存在
OSD-04010: 指定了 <create> 选项, 但文件已经存在

• 因为我们之前删除时并没有删除物理文件，所以在建同名文件时就会报错。 我们可以加上REUSE 参数。只要文件不再使用，就可以重写已经存在的文件。

SQL>create undo tablespace undotbs1
       datafile ‘D:\app\Administrator\oradata\orcl\UNDOTBS01.DBF’ size 100M reuse;
表空间已创建。

• 8.shutdown 数据库并还原pfile

SQL> select name,issys_modifiable 
          from v$parameter
        where name=’undo_management’ or name=’rollback_segments’;

SQL> shutdown immediate

•  9.修改pfile 参数

*.undo_tablespace=’UNDOTBS1′
*.undo_management=’AUTO’
#undo_management=’MAN L’
#rollback_segments=’SYSTEM’

• 10.使用pfile 启动数据库，并创建spfile

SQL> startup pfile=’F:\initorcl.ora’ ;
SQL> create spfile from pfile=’F:\initorcl.ora’;

•  11.再次shutdown，用spfile 启动.

SQL> shutdown immediate
SQL> startup

• 一般数据文件损坏的情况也可以采用类似的方法，先启动到mount，再将损坏的数据文件offline drop。 在open数据库，drop掉损坏的数据文件。当然这种做法有数据丢失。下面的sql语句查询undo表空间的使用：—-优化出现的sql语句。

SELECT e.sql_text,
       r.name 回滚段名,
       s.serial#,
       s.sid,
       s.username 用户名,
       t.used_ublk * 8192 / 1024 / 1024 || 'M' 使用大小,
       substr(s.program, 1, 78) 操作程序
  FROM sys.v_$session     s,
       sys.v_$transaction t,
       sys.v_$rollname    r,
       sys.v_$sqlarea     e
 WHERE t.addr = s.taddr
   and t.xidusn = r.usn
   and e.address =
       DECODE(s.sql_hash_value, 0, s.prev_sql_addr, s.sql_address)
 order by t.USED_UBLK desc;

SQL_TEXT    回滚段名       SERIAL#      SID 用户名       使用大小       操作程序
----------   --------      ---------     ----------      ---------     ---------