undo 诊断脚本

脚本不足恐惧症

先说一下原理：

Automatic Undo Management 中的 Undo Block 分配算法如下：

1. 如果当前范围有更多空闲块，则分配下一个空闲块。
2.否则，如果下一个extent过期，则在下一个extent中换行并返回第一个块。
3. 如果下一个extent没有过期，则从UNDO表空间中获取空间。如果空闲范围可用，则将其分配给撤消段并返回新范围中的第一个块。
4. 如果没有可用的空闲区，则从离线撤消段中窃取过期区。从离线 undo 段中取消分配过期的 extent 并将其添加到 undo 段。返回范围的第一个空闲块。
5.如果离线undo段中没有过期extent，则从在线undo段中窃取，并将新的extent添加到当前undo段中。返回范围的第一个空闲块。
6. 扩展UNDO 表空间中的文件。如果文件可以扩展，则向当前撤消段添加一个范围，然后返回该块。
7. 以 10% 的减量调整保留并窃取未过期但现在相对于较低保留值已过期的范围。
8. 从任何脱机撤消段中窃取未过期的范围。
9. 尝试重用自己的undo段中未过期的extents。如果所有范围当前都忙（它们包含未提交的信息），则转到步骤 10。否则，进入下一个范围。
10. 尝试从任何在线撤消段中窃取未过期的范围。
11. 如果以上都失败，则返回 ORA-30036 无法在 undo tablespace '%s' 中扩展段 %s

当使用 NO AUTOEXTEND 创建 UNDO 表空间时，遵循分配算法，以下是对这种正确行为的解释：

对于固定大小的 UNDO 表空间（NO AUTOEXTEND），从 10.2 开始，我们提供给定固定撤消空间的最大保留时间，该空间设置为基于 UNDO 表空间大小的值。
这意味着即使 undo_retention 设置为秒数（默认为 900），固定的 UNDO 表空间也支持更大的 undo_retention 时间间隔（例如：36 小时），基于表空间大小，这使得撤消范围是未过期。但这并不表示当事务将在数据库中运行时没有可用的撤消范围，因为 UNEXPIRED 撤消段将被重用。

参考：DOC ID 413732.1

--检查 AUM 中活动撤消段的使用情况 DOC ID 1337335.1

 select max(maxquerylen),max(tuned_undoretention) from v$undostat;

 select max(maxquerylen),max(tuned_undoretention) from DBA_HIST_UNDOSTAT;

select * from (select MAXQUERYSQLID, MAXQUERYLEN from DBA_HIST_UNDOSTAT order by MAXQUERYLEN desc)where rownum<31;

select * from (select MAXQUERYID, MAXQUERYLEN from v$UNDOSTAT order by MAXQUERYLEN desc)where rownum<31;

SELECT DISTINCT STATUS,TABLESPACE_NAME, SUM(BYTES/1024/1024)MB, COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS, TABLESPACE_NAME;

alter session set nls_date_format='YYYY-mm-dd HH24:MI:SS';
select * from (select BEGIN_TIME , UNDOBLKS from DBA_HIST_UNDOSTAT order by UNDOBLKS desc )where rownum<31;
select * from (select BEGIN_TIME , UNDOBLKS from v$UNDOSTAT order by UNDOBLKS desc)where rownum<31;

select avg(maxquerylen) from DBA_HIST_UNDOSTAT;
select avg(maxquerylen) from v$UNDOSTAT;

SELECT segment_name, bytes "Extent_Size", count(extent_id) "Extent_Count", round(bytes * count(extent_id)/1024/1024) "Extent_MBytes"
FROM dba_undo_extents WHERE status = 'ACTIVE' group by segment_name, bytes order by 1, 3 desc;

col USERNAME for a12
col PROGRAM for a25
col SEGMENT_NAME for a20
SELECT s.sid, s.serial#, s.username, u.segment_name, count(u.extent_id) "Extent Count", t.used_ublk, t.used_urec, s.program
FROM v$session s, v$transaction t , dba_undo_extents u
WHERE s.taddr = t.addr and u.segment_name like '_SYSSMU'||t.xidusn||'_%$' and u.status = 'ACTIVE'
GROUP BY s.sid, s.serial#, s.username, u.segment_name, t.used_ublk, t.used_urec, s.program
ORDER BY t.used_ublk desc, t.used_urec desc, s.sid, s.serial#, s.username, s.program;

select b.name "UNDO Segment Name", b.inst# "Instance ID", b.status$ STATUS,
 a.ktuxesiz "UNDO Blocks", a.ktuxeusn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq
from x$ktuxe a, undo$ b
where a.ktuxesta = 'ACTIVE'
--and a.ktuxecfl like '%DEAD%'
and a.ktuxeusn = b.us#;


--To find the max query length and the tuned_undo retention, run this SQL.

set pagesize 25
set linesize 150
column UNXPSTEALCNT heading "# Unexpired|Stolen"
column EXPSTEALCNT heading "# Expired|Reused"
column SSOLDERRCNT heading "ORA-1555|Error"
column NOSPACEERRCNT heading "Out-Of-space|Error"
column MAXQUERYLEN heading "Max Query|Length"
select inst_id, to_char(begin_time,'YYYY-mm-dd HH24:MI') begin_time,
UNXPSTEALCNT, EXPSTEALCNT , SSOLDERRCNT, NOSPACEERRCNT, MAXQUERYLEN, TUNED_UNDORETENTION
from gv$undostat
order by inst_id, begin_time;

/*
Then look for the timestamp at which the ORA-1555 occurred.
The max query length will be less than undo_retention, and less than tuned_undoretention.

Note: When tuned_undoretention is used, the undo_retention becomes a minimum setting.
*/
-- “_undo_autotune”=false

--跟踪 undo 异常 761128.1
        event = "10442 trace name context forever, level 10"
        event = "1555 trace name errorstack level 3"
        _smu_debug_mode = 2049
        max_dump_file_size = unlimited

        select count(*) from wrh$_undostat;

alter session set nls_date_format = 'YYYY-mm-dd HH24:MI:SS';
col status for 9999
select begin_time, end_time, status, maxquerylen, tuned_undoretention from wrh$_undostat where status > 1;

select (sum(undoblks) * (select value from v$parameter where name='db_block_size')/(1024*1024*1024)) Undo_GB from wrh$_undostat
 where begin_time > (to_date('', 'YYYY-MM-DD HH24:MI:SS') - 3/24) and
  begin_time <= to_date('', 'YYYY-MM-DD HH24:MI:SS') and
  instance_number = <Your Instance Number>;


SET LINESIZE 150
COLUMN username FORMAT A8
col segment_name for a22
col status for a8
SELECT s.username,
      s.sid,
      s.serial#,
      t.used_ublk,
      t.used_urec,
      rs.segment_name,
      r.rssize/1024 size_KB,
      r.status,
      r.EXTENDS, --here is important
      s.program
FROM v$transaction t,
      v$session s,
      v$rollstat r,
      dba_rollback_segs rs
WHERE s.saddr = t.ses_addr
AND t.xidusn = r.usn
AND rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;

/*alter system set "_smu_debug_mode" = 4;
SQL> alter rollback segment "_SYSSMU4$" shrink; (based on the above example)
alter system set "_smu_debug_mode" = 0;
*/

UNDO不足判断

--是否有undo告警
SELECT creation_time,metric_value,message_type,reason,
     Suggested_action FROM dba_outstanding_alerts
     WHERE object_name='';

SELECT creation_time,metric_value,message_type,reason,Suggested_action,resolution
     FROM dba_alert_history
     WHERE object_name='';

--表空间告警阈值
SELECT object_type,object_name,warning_value,critical_value
FROM dba_thresholds
WHERE object_type='TABLESPACE';

-- SELECT tablespace_name FROM dba_tablespaces where contents='UNDO';

-- undo 表空间使用率
SELECT
         round(((SELECT (NVL(SUM(bytes),0))
           FROM dba_undo_extents
           WHERE tablespace_name='UNDOTBS1'
           AND status IN ('ACTIVE','&UNDOTBS_NAME')) * 100)/
         (SELECT SUM(bytes)
          FROM dba_data_files
          WHERE tablespace_name='&UNDOTBS_NAME'),1)
         "PCT_INUSE"
     FROM dual;