表空间管理----UNDO表空间

对Oracle数据库UNDO表空间的监控和管理是我们日常最重要的工作之一，UNDO表空间通常都是Oracle自动化管理 （通过undo_management初始化参数确定）；
UNDO表空间是用于存储DML操作的前镜像数据，它是实例恢复，数据回滚，一致性查询功能的重要组件；
我们常常会忽略对它的监控，这会导致UNDO表空间可能出现以下问题：
1).空间使用率100%，导致DML操作无法进行。
2).告警日志中出现大量的ORA-01555告警错误。
3).实例恢复失败，数据库无法正常打开。
需要了解以下参数
undo_management=AUTO 表示实例自动化管理UNDO表空间
undo_retention=900 事务提交后，相应的UNDO数据保留的时间，单位：秒。
undo_tablespace=UNDOTBS1 活动的UNDO表空间。
_undo_autotune=TRUE 自动优化undo的保留时间。
当使用的UNDO表空间非自动增长，tuned_undoretention是基于UNDO表空间大小的使用率计算出来的，
在一些情况下，特别是较大的UNDO表空间时，这将计算出较大的值。
undo段中区的状态：
1）free：没有分配给任何一个段
2）active：区中有事务没有提交
3）inactive：区中的事务提交了
4）expired：事务提交而且达到了undo_retention
注：我们可以通过设定undo_retention来保住inactive的区，若没有free，则自动扩展；
若扩展不了，则优先使用expired；若还不够，则就会使用inactive，但如果此时retention是
guarantee保证的，则无法使用inactive，会报ORA-30036.

现在我们面临三个问题
1、设置多大的undo表空间合适。
2、设置多长的表留时间合适。

3、undo表空间已经扩大很大了，我们该是如何释放。
首先针对第一个问题，设置多大的undo表空间合适。
业务繁忙期查询：只是一个参考值，要比此值大一些。

select ((UR * (UPS * DBS)) + (DBS * 24)) / 1024 / 1024 / 1024 as "bytes"
from (select value as UR from v$parameter where name = 'undo_retention'),
(select (sum(undoblks) / sum(((end_time - begin_time) * 86400))) as ups
from v$undostat),
(select value as DBS from v$parameter where name = 'db_block_size');
其次要设置多长时间合适，

v$undostat视图包含了数据库如何来使用撤销空间的统计信息，以及运行时间最长的查询等信息。可以使用这些信息计算当前数据库所处理的工作负载的撤销空间大小。v$undostat视图中每一行显示十分钟时间间隔的撤销统计信息。表中最多包含576行数据，每一行记录十分钟。因此，最多可以查看四天内的撤销空间使用情况。
BEGIN_TIME：时间段的起始时间。
END_TIME：时间段的截止时间。
UNDOBLKS：在十分钟的间隔内数据库所消耗的撤销数据块数量。
TXNCOUNT：在十分钟时间间隔内所执行的事务数。
MAXQUERYLEN：显示了在十分钟间隔内该实例上执行的，耗时最长的查询所花的时间（单位为秒）。
MAXQUERYID：时间间隔内运行时间最长的SQL语句的标识符。
NOSPACEERRCNT:数据库在撤销表空间中，因为整个空间都被活动事务所占用，因而没有足够空间来存放新撤销数据的次数。
TUNED_UNDORETENTION:数据库在提交撤销所属事务后，将会保留撤销数据的时长，单位为秒。
select to_char(begin_time, 'hh24:mi:ss') BEGIN_TIME,
to_char(end_time, 'hh24:mi:ss') END_TIME,
maxquerylen,
nospaceerrcnt,
tuned_undoretention
from v$undostat;

查看tuned_undoretention的最大值，把undo_retention设置一个下相对较大的值，把自动调整undo retention
从10.2版本开始，oracle默认采用自动调整undo retention的方法
根据你undo tablespace的大小以及系统的繁忙程度(v$undostat中信息）自动调整undo_retention参数
所以在10g的数据库上你会经常发现undo tablespace永远是满的
因为当你undo tablespace有空闲空间时，系统自动调大undo_retention来保留更多的undo blocks
这一方法有利于时间长的查询，但是对于典型的OLTP系统来说不太适用
因为OLTP上不太可能跑如此长时间的查询，而且在很繁忙的 OLTP上还会导致上面所遇到的问题
该参数可以在线修改：
alter system set “_undo_autotune” = false;

如何释放应经扩展的undo表空间。
1. 启动SQLPLUS，并用sys登陆到数据库。
#su - oracle
$>sqlplus / as sysdba
2. 查找数据库的UNDO表空间名,确定当前例程正在使用的UNDO表空间：
Show parameter undo_tablespace
3. 确认UNDO表空间；
SQL> select name from v$tablespace;
4. 检查数据库UNDO表空间占用空间情况以及数据文件存放位置；
SQL>select file_name,bytes/1024/1024 fromdba_data_files where tablespace_name like 'UNDOTBS%';
5. 查看回滚段的使用情况，哪个用户正在使用回滚段的资源，如果有用户最好更换时间（特别是生产环境）。
SQL> select s.username, u.name fromv$transaction t,v$rollstat r, v$rollname u,v$session s
where s.taddr=t.addr and t.xidusn=r.usn andr.usn=u.usn order by s.username;
6. 检查UNDO Segment状态；
select s.username, s.SID, u.name, r.RSSIZE,r.WRITES, r.HWMSIZE
fromv$transaction t, v$rollstat r, v$rollname u, v$session s
wheres.taddr = t.addr
andt.xidusn = r.usn
andr.usn = u.usn
orderby r.rssize desc;
7. 创建新的UNDO表空间，并设置自动扩展参数；
SQL> create undo tablespace undotbs2datafile '/opt/oracle/oradata/ge01/UNDOTBS2.dbf' size 100m reuse autoextend onnext 50m maxsize 5000m;
8. 切换UNDO表空间为新的UNDO表空间 ， 动态更改spfile配置文件；
SQL> alter system set undo_tablespace=undotbs2scope=both;
9.验证当前数据库的 UNDO表空间
SQL> show parameter undo
9. 等待原UNDO表空间所有UNDO SEGMENT OFFLINE；
selectusn,xacts,status,rssize/1024/1024,hwmsize/1024/1024, shrinks from v$rollstatorder by rssize;
selectt.segment_name,t.tablespace_name,t.segment_id,t.status from dba_rollback_segst;
10. 删除原有的UNDO表空间；
SQL>drop tablespace undotbs1including contents and datafiles;
最后需要在重启数据库或者重启计算机后到存储数据文件的路径下删除数据文件（为什么要手动删除呢：以上步骤只是删除了ORACLE中undo表空间的逻辑关系，即删除了数据文件在数据字典中的关联，不会自动删除项关联的数据文件）。

drop tablespace undotbs1including contents and datafiles;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31324783/viewspace-2132142/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31324783/viewspace-2132142/