UNDO表空间的管理

 

UNDO表空间的管理

1.概念

回滚段概述
　　回滚段用于存放数据修改之前的值（包括数据修改之前的位置和值）。回滚段的头部包含正在使用的该回滚段事务的信息。
    一个事务只能使用一个回滚段来存放它的回滚信息，而一个回滚段可以存放多个事务的回滚信息。

回滚段的作用
　　1。事务回滚：当事务修改表中数据的时候，该数据修改前的值（即前影像）会存放在回滚段中，当用户回滚事务（ROLLBACK）时，
       ORACLE将会利用回滚段中的数据前影像来将修改的数据恢复到原来的值。

　　2。事务恢复：当事务正在处理的时候，例程失败，回滚段的信息保存在重做日志文件中，ORACLE将在下次打开数据库时利用回滚来恢复未提交的数据。

　　3。读一致性：当一个会话正在修改数据时，其他的会话将看不到该会话未提交的修改。而且，当一个语句正在执行时，该语句将看不到从该语句开始执行
       后的未提交的修改（语句级读一致性）。当ORACLE执行SELECT语句时，ORACLE依照当前的系统改变号（SYSTEM CHANGE NUMBER-SCN）来保证任何
       前于当前SCN的未提交的改变不被该语句处理。可以想象：当一个长时间的查询正在执行时，若其他会话改变了该查询要查询的某个数据块，ORACLE将利
       用回滚段的数据前影像来构造一个读一致性视图。

事务级的读一致性
　　ORACLE一般提供SQL语句级（SQL STATEMENT LEVEL）的读一致性，可以用以下语句来实现事务级的读一致性。
　　SET TRANSACTION READ ONLY；
　　或：
　　SET TANNSACTION SERIALIZABLE；
　　以上两个语句都将在事务开始后提供读一致性。需要注意的是，使用第二个语句对数据库的并发性和性能将带来影响。

回滚段的种类
　　1。系统回滚段：当数据库创建后，将自动创建一个系统回滚段，该回滚段只用于存放系统表空间中对象的前影像。

　　2。非系统回滚段：拥有多个表空间的数据库至少应该有一个非系统回滚段，用于存放非系统表空间中对象的数据前影像。非系统回滚段又分为私有回
       滚段和公有回滚段，私有回滚段应在参数文件的ROLLBACK SEGMENTS参数中列出，以便例程启动时自动使其在线（ONLINE）。公有回滚段一般在
       OPS（ORACLE并行服务器）中出现，将在例程启动时自动在线。

　　3。DEFERED回滚段：该回滚段在表空间离线（OFFLINE）时由系统自动创建，当表空间再次在线（ONLINE）时由系统自动删除，用于存放表空间离线时产生的回滚信息。

回滚段的使用
　　分配回滚段：当事务开始时，ORACLE将为该事务分配回滚段，并将拥有最少事务的回滚段分配给该事务。事务可以用以下语句申请指定的回滚段：
　　SET TRANSTRACTION USE ROLLBACK SEGMENT rollback_segment

　　事务将以顺序，循环的方式使用回滚段的区（EXTENTS），当当前区用满后移到下一个区。几个事务可以写在回滚段的同一个区，
    但每个回滚段的块只能包含一个事务的信息。

　　例如（两个事务使用同一个回滚段，该回滚段有四个区）：
　　1、事务在进行中，它们正在使用回滚段的第三个区；
　　2、当两个事务产生更多的回滚信息，它们将继续使用第三个区；
　　3、当第三个区满后，事务将写到第四个区，当事务开始写到一个新的区时，称为翻转（WRAP）；
　　4、当第四个区用满时，如果第一个区是空闲或非活动（使用该区的所有事务完成而没有活动的事务）的，事务将接着使用第一个区。

回滚段的扩张（EXTEND）
　　当当前回滚段区的所有块用完而事务还需要更多的回滚空间时，回滚段的指针将移到下一个区。当最后一个区用完，指针将移到第一个区的前面。
    回滚段指针移到下一个区的前提是下一个区没有活动的事务，同时指针不能跨区。当下一个区正在使用时，事务将为回滚段分配一个新的区，这种
    分配称为回滚段的扩展。回滚段将一直扩展到该回滚段区的个数到达回滚段的参数MAXEXTENTS的值时为止。

回滚段的回收和OPTIMAL参数
　　OPTIMAL参数指明回滚段空闲时收缩到的位置，指明回滚段的OPTIMAL参数可以减少回滚段空间的浪费。

V$ROLLSTAT中的常用列
?        USN：回滚段标识
?        RSSIZE：回滚段默认大小
?        XACTS：活动事务数

在一段时间内增量用到的列
?        WRITES：回滚段写入数(单位:bytes)
?        SHRINKS：回滚段收缩次数
?        EXTENDS：回滚段扩展次数
?        WRAPS：回滚段翻转(wrap)次数
?        GETS：获取回滚段头次数
?        WAITS：回滚段头等待次数

V$ROLLSTAT中的连接列
Column                                        View                                         Joined Column(s)
--------------                                -----------------------                        ------------------------
USN                                        V$ROLLNAME                        USN

注意：
　　通过花费时间除以翻转次数，你可以得到一次回滚段翻转(wrap)的平均用时。此方法常用于在长查询中指定合适的回滚段大小
    以避免'Snapshot Too Old'错误。同时，通过查看extends和shrinks列可以看出optimal是否需要增加。

2.实践操作

和undo相关的视图:

v$rollstat
v$rollname
dba_segments
DBA_ROLLBACK_SEGS
v$transaction

1.查看回滚段的信息
select * from dba_rollback_segs

SEGMENT_NAME                   OWNER  TABLESPACE_NAME                SEGMENT_ID             FILE_ID                BLOCK_ID               INITIAL_EXTENT         NEXT_EXTENT            MIN_EXTENTS            MAX_EXTENTS            PCT_INCREASE           STATUS           INSTANCE_NUM                             RELATIVE_FNO          
------------------------------ ------ ------------------------------ ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------- ---------------------------------------- ----------------------
SYSTEM                         SYS    SYSTEM                         0                      1                      9                      114688                                        1                      32765                                         ONLINE                                                    1                     
_SYSSMU1$                      PUBLIC UNDOTBS3                       1                      9                      2825                   131072                                        2                      32765                                         ONLINE                                                    9                     
_SYSSMU2$                      PUBLIC UNDOTBS3                       2                      9                      2841                   131072                                        2                      32765                                         ONLINE                                                    9                     

 MIN_EXTENTS :一个段中最小的区的数量(一般段为1,回滚段为2)
 MAX_EXTENTS :一个段中最大的区的数量(最大为32765)

 有的人会疑问,那这个段最大值=32765*每个区中段的数量*段的大小,

2.当前库中创建了多少个回退段,回退段所在的表空间名,回退段的大小

SQL> select segment_name,tablespace_name,bytes,blocks from dba_segments where
  2  segment_type='ROLLBACK';

SEGMENT_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                     BYTES     BLOCKS
------------------------------ ---------- ----------
SYSTEM
SYSTEM                             393216         48

3.使用回滚段的信息的查询

select r.name rollbackname,
       s.sid,
       s.serial#,
       s.username ,
       t.cr_get,
       t.phy_io,
       t.used_ublk,
       t.noundo,
       substr(s.program,1,78) ops_program
from v$rollname r,v$transaction t, v$session s
where t.addr=s.taddr
and t.xidusn=r.usn
order by t.cr_get,phy_io

4.查看回滚段的竞争情况,如果命中率大于2%,那就存在回退段竞争,必须增加回退段的个数,

select rn.name,rs.gets,rs.waits,(rs.waits/rs.gets)*100 ratio
from v$rollname rn,v$rollstat rs
where rs.usn=rn.usn

gets: 回退段被访问的次数
waits:进程等待回退段的次数
ratio:回退段的命中率

5.undu表空间的大小计算方法

select (rd*(ups*overhead)+overhead)/1024/1024/1024 bytes
 from (select value rd from v$parameter where ),
 (select (sum(undoblks)/sum(((end_time-begin_time)*10800))) ups from v$undostat),
 (select value overhead from v$parameter where );

Rd：undo_retention设置的时间；
Ups：undo blocks per second；
Overhead：rollback segment header；

估计undo tablespace大小的公式：
Undo space = (undo_retention * (undo blocks per second * db_block_size)) + db_block_size;

6.查看系统使用的undo空间的情况

方法1:
SQL> show parameter undo

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

方法二:
SQL> select name ,value from v$parameter where name like 'un%';

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
undo_management
AUTO

undo_tablespace
UNDOTBS1

undo_retention
900

在我们的数据库往往有undo tablespace占用空间过大，占用了磁盘60%的空间。如果发现此问题的话，应从应用着手了，
什么事务能使用若大的undo tablespace.发现不能回收，可以考虑我如下的方法:

方法一: 用resize收缩表空间
方法二: 用新健的undo表空间替换现有的

方法一 有时候,无法收缩,最有效的方法是方法二,可以根本的解决空间回缩的方法

方法一:

1.

SQL> select tablespace_name ,sum(bytes)/1024/1024/1024 GB from  dba_data_files group by tablespace_name
  2  union all
  3  select tablespace_name ,sum(bytes)/1024/1024/1024 GB from dba_temp_files group by tablespace_name;

TABLESPACE_NAME                        GB
------------------------------ ----------
SYSAUX                         .263671875
UNDOTBS1                       .034179688
USERS                          .067138672
SYSTEM                         .478515625
EXAMPLE                         .09765625
UNDOTBS2                       .306640625
TEMP                            .01953125

已选择7行。

SQL>

2.

SQL> select  file#,name from v$datafile;

     FILE#
----------
NAME
-----------------------------------------------------------

.....

         6
F:\ORACLE\PRODUCT\10.2.0\ORADATA\MOVO\UNDOTBS02.DBF

已选择6行。

3.

SQL> select max(block_id) from dba_extents where file_id=6;

MAX(BLOCK_ID)
-------------
         5625

4.

SQL> show parameter db_block_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
db_block_size                        integer     8192

5,查询已经用的undo空间

SQL> select 5625*8/1024 from dual;

5625*8/1024
-----------
 43.9453125

6.

SQL> alter database datafile 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MOVO\UNDOTBS02.DBF' resize 13m;
alter database datafile 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MOVO\UNDOTBS02.DBF' resize 13m
*
第 1 行出现错误:
ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据

因为我给undo初始大小定义为10M,而现在是43m,说明是扩展的,所以都是用过的

SQL> select file_name ,bytes/1024/1024 from dba_data_files where tablespace_name='UNDOTBS2';

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024
---------------
F:\ORACLE\PRODUCT\10.2.0\ORADATA\MOVO\UNDOTBS02.DBF
            314

SQL> alter database datafile 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MOVO\UNDOTBS02.DBF' resize 32

数据库已更改。

SQL> select file_name ,bytes/1024/1024 from dba_data_files where tablespace_name='UNDOTBS2';

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024
---------------
F:\ORACLE\PRODUCT\10.2.0\ORADATA\MOVO\UNDOTBS02.DBF
            320

SQL> alter database datafile 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MOVO\UNDOTBS02.DBF' resize 31

数据库已更改。

SQL> select file_name ,bytes/1024/1024 from dba_data_files where tablespace_name='UNDOTBS2';

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024
---------------
F:\ORACLE\PRODUCT\10.2.0\ORADATA\MOVO\UNDOTBS02.DBF
            314

方法 二:

1.查看数据库文件的路径

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
F:\ORACLE\PRODUCT\10.2.0\ORADATA\MOVO\SYSTEM01.DBF
F:\ORACLE\PRODUCT\10.2.0\ORADATA\MOVO\SYSAUX01.DBF
F:\ORACLE\PRODUCT\10.2.0\ORADATA\MOVO\USERS01.DBF
F:\ORACLE\PRODUCT\10.2.0\ORADATA\MOVO\EXAMPLE01.DBF
F:\ORACLE\PRODUCT\10.2.0\ORADATA\MOVO\UNDOTBS02.DBF

2. 查看当前数据库的默认undo空间

SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2
SQL> create undo tablespace undotbs1
  2  datafile 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MOVO\UNDOTBS01.DBF' size 10m;

表空间已创建。

3.修改参数文件

SQL> alter system  set  undo_tablespace=undotbs1 scope=both;

系统已更改。

4.查看回滚段的 使用情况,要等没有是使用回滚段的时候.才能删除表空间

SQL> select usn,xacts,status,rssize/1024/1024/1024 ,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;

       USN      XACTS STATUS          RSSIZE/1024/1024/1024
---------- ---------- --------------- ---------------------
HWMSIZE/1024/1024/1024    SHRINKS
---------------------- ----------
         5          0 ONLINE                     .000114441
            .000114441          0

         1          0 ONLINE                     .000114441
            .000114441          0

         2          0 ONLINE                     .000114441
            .000114441          0

       USN      XACTS STATUS          RSSIZE/1024/1024/1024
---------- ---------- --------------- ---------------------
HWMSIZE/1024/1024/1024    SHRINKS
---------------------- ----------
         3          0 ONLINE                     .000114441
            .000114441          0

         4          0 ONLINE                     .000114441
            .000114441          0

        10          0 ONLINE                     .000114441
            .000114441          0

       USN      XACTS STATUS          RSSIZE/1024/1024/1024
---------- ---------- --------------- ---------------------
HWMSIZE/1024/1024/1024    SHRINKS
---------------------- ----------
         6          0 ONLINE                     .000114441
            .000114441          0

         7          0 ONLINE                     .000114441
            .000114441          0

         8          0 ONLINE                     .000114441
            .000114441          0

       USN      XACTS STATUS          RSSIZE/1024/1024/1024
---------- ---------- --------------- ---------------------
HWMSIZE/1024/1024/1024    SHRINKS
---------------------- ----------
         9          0 ONLINE                     .000114441
            .000114441          0

         0          0 ONLINE                     .000358582
            .000358582          0

已选择11行。

5.删除undo空间

SQL> drop tablespace ubdotbs2 including contents and datafiles;  //如果数据库在使用状态删除时,一般是无法物理删除数据文件的,因为还有事务在使用,有两个方法,一个另外建一个数据文件
                                                                   ,等没有事务用的时候,在物理删除;第二个方法是重新启动下数据库,然后物理删除数据文件,(如果可以重启数据库,.执行
           这个语句会自动删除数据文件)

表空间已删除。

6.查看表空间的大小

SQL> select file_name,bytes/1024/1024 from  dba_data_files
  2  where tablespace_name like 'UNDOTBS1';

FILE_NAME
------------------------------------------------------------------------------
BYTES/1024/1024
---------------
F:\ORACLE\PRODUCT\10.2.0\ORADATA\MOVO\UNDOTBS01.DBF
             10

或者

SQL> select tablespace_name ,sum(bytes)/1024/1024/1024 GB from  dba_data_files group by tablespace_
  2  union all
  3  select tablespace_name ,sum(bytes)/1024/1024/1024 GB from dba_temp_files group by tablespace_n

TABLESPACE_NAME                        GB
------------------------------ ----------
SYSAUX                         .263671875
UNDOTBS1                       .009765625
USERS                          .067138672
SYSTEM                         .478515625
EXAMPLE                         .09765625
TEMP                            .01953125

已选择6行。

SQL>

---待续 回滚段管理

1.监控回退段

SQL> select begin_time,end_time,maxquerylen len,undoblks,txncount,maxconcurrency maxcon ,nospaceerrcnt err from v$undostat where
  2  begin_time > sysdate-(2/24);

BEGIN_TIME     END_TIME              LEN   UNDOBLKS   TXNCOUNT     MAXCON
-------------- -------------- ---------- ---------- ---------- ----------
       ERR
----------
14-4月 -08     14-4月 -08              0         14        268          1
         0

14-4月 -08     14-4月 -08              0         83        179          3
         0

14-4月 -08     14-4月 -08           2185         16        136          1
         0

如果产生空间错误的问题,则参数nospaceerrcnt会增加,这是就要扩大undo表空间

2.重建undo表空间,更改系统的表空间

SQL> create undo tablespace undotbs2
  2  datafile 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MOVO\UNDOTBS03.DBF' size 10m
  3  extent management local autoallocate;

表空间已创建。

SQL> alter system  set undo_tablespace=undotbs2;

系统已更改。

SQL> alter tablespace undotbs1 offline;

表空间已更改。

SQL> alter database datafile 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MOVO\UNDOTBS02.DBF','F:\ORACLE\PRODUC
T\10.2.0\ORADATA\MOVO\UNDOTBS01.DBF' OFFLINE DROP;

数据库已更改。

SQL> commit;

提交完成。

SQL>

------待续  空间的故障恢复

undo空间的案例恢复的方法

--- end ----

引文：UNDO表空间的管理 - 冰刀(skate) - CSDNBlog