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”部分的查询输出