主说明：自动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)/1024/1024 MB, COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

 示例：

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

STATUS            MB   COUNT(*)
--------- ---------- ----------
ACTIVE       2162.25        408
EXPIRED     18233.75      16146
UNEXPIRED   27083.25      21389

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