主说明：自动Undo管理的故障排除指南(DocID1579081.1)ZYLONG

Z时代
2024-01-10
分类：综合

database

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的各种状态是什么？

SELECTDISTINCT STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUPBY STATUS;

示例：

SQL>SELECTDISTINCT STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUPBY STATUS;
STATUS    SUM(BYTES)   COUNT(*)
--------- ---------- ----------
UNEXPIRED    655360010
EXPIRED     49283072152

c. Tuned Retention 调整保留

SELECTMAX(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
SELECTFILE_ID, BYTES/1024/1024AS "BYTES (MB)", MAXBYTES/1024/1024AS "MAXBYTES (MB)", AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME="&UNDOTBS";

示例：

SQL> COL AUTOEXTENSIBLE FORMAT A14
SQL>SELECTFILE_ID, BYTES/1024/1024AS "BYTES (MB)", MAXBYTES/1024/1024AS "MAXBYTES (MB)", AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME="&UNDOTBS";
Enter value for undotbs: UNDOTBS1
FILE_ID BYTES (MB) MAXBYTES (MB) AUTOEXTENSIBLE
---------- ---------- ------------- --------------
37032767.9844 YES