主说明：自动Undo管理的故障排除指南(Doc ID 1579081.1)

master note: troubleshooting guide for automatic undo management (doc id 1579081.1)

applies to:

oracle database - enterprise edition - version 9.2.0.1 to 12.1.0.1 [release 9.2 to 12.1]
information in this document applies to any platform.

purpose

this is a master note for troubleshooting the various issues reported on undo management. this document provides a brief explanation for the various issues and the list of diagnostic information to be collected before raising a service request with oracle support.  

这是一个主要说明，用于对undo management中报告的各种问题进行故障排除。本文档简要说明了各种问题，并在通过oracle support提出服务请求之前要收集的诊断信息列表。

troubleshooting steps

there are various undo related issues reported. refer : assistant: get assistance to understand and solve oracle undo management issues (doc id 1575667.2)

报告了各种与undo相关的问题。请参阅：assistant: get assistance to understand and solve oracle undo management issues (doc id 1575667.2)

1- please provide the following diagnostic information if the issue persists:  如果问题仍然存在，请提供以下诊断信息：

a. undo parameters    undo参数

 

select nam.ksppinm name, val.ksppstvl value
  from x$ksppi nam, x$ksppsv val
 where nam.indx = val.indx
   and (nam.ksppinm like '%undo%' or
       nam.ksppinm in ('_first_spare_parameter', '_smu_debug_mode'))
 order by 1;

 

 

 示例：

 

col name for a35
col value for a50
select nam.ksppinm name, val.ksppstvl value
  from x$ksppi nam, x$ksppsv val
 where nam.indx = val.indx
   and (nam.ksppinm like '%undo%' or
       nam.ksppinm in ('_first_spare_parameter', '_smu_debug_mode'))
 order by 1;

name                                value
----------------------------------- --------------------------------------------------
_collect_undo_stats                 true
_disable_undo_tablespace_alerts     false
_enable_default_undo_threshold      true
_first_spare_parameter
_flush_undo_after_tx_recovery       true
_gc_undo_affinity                   true
_gc_undo_block_disk_reads           true
_highthreshold_undoretention        4294967294
_in_memory_undo                     true
_lm_spare_undo                      0
_optimizer_undo_changes             false
_optimizer_undo_cost_change         11.2.0.4
_smon_undo_seg_rescan_limit         10
_smu_debug_mode                     0
_undo_autotune                      true
_undo_block_compression             true
_undo_debug_mode                    0
_undo_debug_usage                   0
_verify_undo_quota                  false
undo_management                     auto
undo_retention                      900
undo_tablespace                     undotbs1

22 rows selected.

b. what are the various statuses for undo extents?   undo extents的各种状态是什么？

select distinct status, sum(bytes), count(*) from dba_undo_extents group by status;

 示例：

sql> select distinct status, sum(bytes), count(*) from dba_undo_extents group by status;

status    sum(bytes)   count(*)
--------- ---------- ----------
unexpired    6553600         10
expired     49283072        152

 c. tuned retention  调整保留

select max(tuned_undoretention), max(maxquerylen), max(nospaceerrcnt), max(expstealcnt) from v$undostat;
select begin_time, end_time, tuned_undoretention, maxquerylen, maxqueryid, nospaceerrcnt, expstealcnt, undoblks, txncount from v$undostat;

 示例：

sql> select begin_time, end_time, tuned_undoretention, maxquerylen, maxqueryid, nospaceerrcnt, expstealcnt, undoblks, txncount from v$undostat;

begin_time        end_time          tuned_undoretention maxquerylen maxqueryid    nospaceerrcnt expstealcnt   undoblks   txncount
----------------- ----------------- ------------------- ----------- ------------- ------------- ----------- ---------- ----------
20191129 15:22:20 20191129 15:27:42                1420         699 0rc4km05kgzb9             0           0          0          4
20191129 15:12:20 20191129 15:22:20                1118         397 0rc4km05kgzb9             0           0          1         83
20191129 15:02:20 20191129 15:12:20                1717         997 0rc4km05kgzb9             0           0          0         13
20191129 14:52:20 20191129 15:02:20                1114         394 0rc4km05kgzb9             0           0         75         79
20191129 14:42:20 20191129 14:52:20                1716         995 0rc4km05kgzb9             0           0          0          2
20191129 14:32:20 20191129 14:42:20                1174         393 0rc4km05kgzb9             0           0          1         14
20191129 14:22:20 20191129 14:32:20                1775         993 0rc4km05kgzb9             0           0          0          6
20191129 14:12:20 20191129 14:22:20                1170         391 0rc4km05kgzb9             0           0          0         83
20191129 14:02:20 20191129 14:12:20                1772         991 0rc4km05kgzb9             0           0          0         11
20191129 13:52:20 20191129 14:02:20                1167         386 0rc4km05kgzb9             0           0         71         78
20191129 13:42:20 20191129 13:52:20                1768         988 0rc4km05kgzb9             0           0          0          6
20191129 13:32:20 20191129 13:42:20                1164         382 0rc4km05kgzb9             0           0          0         22
20191129 13:22:20 20191129 13:32:20                1765         983 0rc4km05kgzb9             0           0          0         11
20191129 13:12:20 20191129 13:22:20                2554        1773 3k9h91mkys9gw             0           0          0          9
20191129 13:02:20 20191129 13:12:20                1951        1170 3k9h91mkys9gw             0           0          3        103
20191129 12:52:20 20191129 13:02:20                1347         566 3k9h91mkys9gw             0           0        109         83
20191129 12:42:20 20191129 12:52:20                1532         751 3k9h91mkys9gw             0           0          2         60
20191129 12:32:20 20191129 12:42:20                1168         386 89km4qj1thh13             0           0          0         13
20191129 12:22:20 20191129 12:32:20                1754         974 0rc4km05kgzb9             0           0          3         31
20191129 12:12:20 20191129 12:22:20                1151         370 0rc4km05kgzb9             0           0          1         80
20191129 12:02:20 20191129 12:12:20                1752         971 0rc4km05kgzb9             0           0          0         12
20191129 11:52:20 20191129 12:02:20                1208         366 0rc4km05kgzb9             0           0         81         77
20191129 11:42:20 20191129 11:52:20                1811         969 0rc4km05kgzb9             0           0          2          7
20191129 11:32:20 20191129 11:42:20                1206         364 0rc4km05kgzb9             0           0          1         16
20191129 11:22:20 20191129 11:32:20                1807         966 0rc4km05kgzb9             0           0          0         10
20191129 11:12:20 20191129 11:22:20                1203         361 0rc4km05kgzb9             0           0          5        157
20191129 11:02:20 20191129 11:12:20                1803         962 0rc4km05kgzb9             0           0          0         12
20191129 10:52:20 20191129 11:02:20                1200         358 0rc4km05kgzb9             0           0        102         95
20191129 10:42:20 20191129 10:52:20                2464        1623 9dzjush42kmfs             0           0          1          7
20191129 10:32:20 20191129 10:42:20                1860        1019 9dzjush42kmfs             0           0          1         49
20191129 10:22:20 20191129 10:32:20                1797         955 0rc4km05kgzb9             0           0         14        216
20191129 10:12:20 20191129 10:22:20                1192         351 0rc4km05kgzb9             0           0        109        228
20191129 10:02:20 20191129 10:12:20                1796         955 0rc4km05kgzb9             0           0         38        481
20191129 09:52:20 20191129 10:02:20                1193         351 0rc4km05kgzb9             0           0         71        942
20191129 09:42:20 20191129 09:52:20                1795         953 0rc4km05kgzb9             0           3        129        654
20191129 09:32:20 20191129 09:42:20                1190         348 0rc4km05kgzb9             0          10       5446        540

36 rows selected.

d. the size details and auto-extend setting for the undo tablespace  undo表空间的大小详细信息和自动扩展设置

col autoextensible format a14
select file_id, bytes/1024/1024 as "bytes (mb)", maxbytes/1024/1024 as "maxbytes (mb)", autoextensible from dba_data_files where tablespace_name='&undotbs';

 示例：

sql> col autoextensible format a14
sql> select file_id, bytes/1024/1024 as "bytes (mb)", maxbytes/1024/1024 as "maxbytes (mb)", autoextensible from dba_data_files where tablespace_name='&undotbs';
enter value for undotbs: undotbs1

   file_id bytes (mb) maxbytes (mb) autoextensible
---------- ---------- ------------- --------------
         3         70    32767.9844 yes

e. upload the alert log file from the startup.

2-  provide the query outputs from the "diagnostic information" section of doc id 1579081.1"

提供来自doc id 1579081.1的"diagnostic information"部分的查询输出