Oracle Managing UNDO

Oracle UNDO表空间的作用：事务回滚，实例恢复，一致性读取。在此总结一下UNDO的表空间管理。

1，Oracle UNDO中的几个重要概念
http://docs.oracle.com/cd/E11882_01/server.112/e25494/undo002.htm#BJFFIACJ
http://docs.oracle.com/cd/E11882_01/server.112/e25789/logical.htm#CNCPT1080
(1),UNDO_RETENTION
实际上当事务一旦提交，UNDO的数据对数据库来说就可以不需要了。但是，为了FLASH BACK能够查询到更多过去的数据，所以设置了这个最小保留时间。
注意：
当UNDO表空间的数据文件大小为固定，即autoextend参数为off时，UNDO_RETENTION不会生效；因为此时默认的_undo_autotune=TRUE，此时数据库会根据UNDO自动管理方式，进行自我调整，确定一个当前合适大小的RETENTION直。
当autoextend参数为on时，UNDO_RETENTION生效，为了满足保留时间，UNDO会自动扩展文件大小。
(2),UNDO_MANAGEMENT
默认为自动管理，官方详解如下
When automatic undo management is enabled, there is always a current undo retention period, which is the minimum amount of time that Oracle Database attempts to retain old undo information before overwriting it. Old (committed) undo information that is older than the current undo retention period is said to be expired and its space is available to be overwritten by new transactions. Old undo information with an age that is less than the current undo retention period is said to beunexpired and is retained for consistent read and Oracle Flashback operations.
Automatic undo retention - Oracle Database automatically provides the best possible undo retention for the current undo tablespace. The database collects usage statistics and tunes the retention period based on these statistics and the undo tablespace size. If the undo tablespace is configured with the AUTOEXTEND option, and if the maximum size is not specified, then undo retention tuning is different. In this case, the database tunes the undo retention period to be slightly longer than the longest-running query, if space allows.



2,UNDO表空间常见问题
(1), ORA-01555 snapshot to old
a,当UNDO数据为autoextend，增加UNDO_RETENTION的值，使得该值能够满足最大的FLASH BACK查询;
b, 将数据文件修为改固定大小，且设置为一个足够大小的固定值，此时RETENTION就会自动调整，满足查询数据的存在。
(2), 扩展UNDO表空间
a, resize
b, add datafile
c, re-create undo tablespace
(3), 重建UNDO表空间
(4), 修复UNDO表空间
当UNDO表空间损坏，需要进行修复或恢复。

3，UNDO表空间查询


SELECT tablespace_name,status,SUM(bytes)/1024/1024 "Bytes(M)"FROM   dba_undo_extentsGROUP BY  tablespace_name, status;

select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,to_char(end_time, 'DD-MON-RR HH24:MI') end_time, tuned_undoretentionfrom v$undostat order by end_time;

select BEGIN_TIME,END_TIME,ACTIVEBLKS*8192/1024/1024 ACTIVEBLKS,UNEXPIREDBLKS*8192/1024/1024 UNEXPIREDBLKS,EXPIREDBLKS*8192/1024/1024 EXPIREDBLKS,TUNED_UNDORETENTION from dba_hist_undostat order by 2 desc;