Oracle Database Undo space explained(-)

下午花点时间对一篇UNDO的文章学习并翻译做了以下笔记

The Undo tablespace is used for several features: ROLLBACK, READ CONSISTENCY and FLASHBACK technology.

Undo Lifetime
 
Undo information has different states during it’s lifecycle, depending on running transactions and retention settings.
 
There are three states or types of extents in the Undo tablespace: ACTIVE, EXPIRED and UNEXPIRED. Oracle is still using Rollback segments, but with Automatic Undo          Management these are completely controlled by Oracle.

ACTIVE
You will get ‘ORA-30036 unable to extend segment in Undo tablespace‘ errors when no more space is left to store ACTIVE Undo. This will automatically rollback the transaction causing it. The NOSPACEERRCNT column in V$UNDOSTAT is a good indication how many times this has occurred.

当不再有空间可以存储活动的UNDO，将有ORA-30036 unable to extend segment in Undo tablespace的错误，这将有自动回滚事物产生，在视图v$undostat中nospaceerrcnt列显示了发生过多少次。
select nospaceerrcnt from v$undostat where nospaceerrcnt>0;

EXPIRED
 
Expired extents are not used by transactions, the data in these extends is committed and the UNDO_RETENTION time has passed, so it is not needed for Read Consistency.
过期的EXTENT不被事物使用，这些区内的数据已被提交并且UNDO_RETENTION时间已超，所以不被一致性读所需要。

UNEXPIRED

Unexpired extents are non-active extents that still honour UNDO_RETENTION. The transactions belonging to these undo extents are committed, but the retention time has not passed: You still want/need these for Read Consistency!
非过期的EXTENT是非活动的EXTENT，但仍看UNDO_RETENTION参数。属于这些EXTENT的事物已被提交，但保留时间未过期：你仍想/需要这些用来读一致性。
When the Undo mechanism requires more extents for ACTIVE extents, it is allowed to steal UNEXPIRED extents when there are no EXPIRED extents left for reuse and it can not allocate more free extents (autoextend maxsize reached or fixed tablespace size). One can check the steal-count in UNXPSTEALCNT in V$UNDOSTAT.
You will get ‘ORA-01555 snapshot too old‘ errors if no Read Consistency information for a query is available. The SSOLDERRCNT in V$UNDOSTAT will show a count of these errors.

    当UNDO机制请求更多的EXTENT给活动的区，当不再有过期EXTENT来重用时，并且不能分配更多空闲EXTENT（自动扩展最大值或适合的表空间大小）时，它被允许偷用UNEXPIRED EXTENT.
    可以在V$UNDOSTAT中UNXPSTEALCNT检查偷用次数。
    如果没有可用的一致性读信息给查询，你将得到‘ORA-01555 snapshot too old’的错误。

总结：
活动的UNDO被活动的事物使用：回滚  读一致性
过期的UNDO是旧的且被重用
非过期的UNDO用作一致性读，保留时间未超
非过期的UNDO可以被活动的UNDO偷取，如果正在发生将在UNDO RETENTION过期前得到ORA-01555
非过期的UNDO可以安全通过设置RETENTION GUARANTEE 选项当创建UNDO表空间。

用下面的语句可以得到UNDO的内容并统计EXTENT类型：状态   使用量    所占百分比

select status,
  round(sum_bytes / (1024*1024), 0) as USEDMB,
  round((sum_bytes / undo_size) * 100, 0) as PERC
from
(
  select status, sum(bytes) sum_bytes
  from dba_undo_extents
  group by status
),
(
  select sum(a.bytes) undo_size
  from dba_tablespaces c
    join v$tablespace b on b.name = c.tablespace_name
    join v$datafile a on a.ts# = b.ts#
  where c.contents = 'UNDO'
    and c.status = 'ONLINE'
);

Out of Free/EXPIRED extents
 STATUS            MB       PERC
--------- ---------- ----------
ACTIVE           230         90
EXPIRED            0          0
UNEXPIRED         26         10

当系统高负载并且EXPIRED接近0%，ACTIVE和UNEXPIRED的总和接近100%，UNDO 表空间不能再扩展，ORACLE将偷取UNEXPIRED extent给ACTIVE的EXTENT。如果这样的话就可能ORA-01555 errors
因为不能满足撤消保留。

Out of Undo space
 STATUS            MB       PERC
--------- ---------- ----------
ACTIVE           255        100
EXPIRED            0          0
UNEXPIRED          1          0

当系统高负载且ACTIVE extent接近100%，EXPIRED与UNEXPIRED之和接近0%，UNDO表空间不能被扩展，ORACLE不能分配FREE EXTENT或窃取UNEXPIRED extent给活动的EXTENT，这样就造成了ORA-30036 的错误

Retention to large or UNDO to small?
 STATUS            MB       PERC
--------- ---------- ----------
ACTIVE             2          1
EXPIRED            0          0
UNEXPIRED        254         99

In this case, all undo extents are used for the retention period. It might be the retention is to large, or the UNDO tablespace is to small. A DBA must investigate this and take a decision!
在这种情况下,所有撤销区段用于保存期。它可能是RETENTION大,或撤消表空间小。DBA必须调查和作出决定!

 

--未完  

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

转载于:http://blog.itpub.net/23891491/viewspace-755067/