Main note: Troubleshooting Guide for automatic Undo management (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.  

This is a main instruction for troubleshooting various problems reported in Undo Management. This document provides a brief description of the various issues and a list of diagnostic information to collect before making a service request through 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)

Various problems related to Undo were reported. See: 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

 

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;

 

 

Example:

 

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 states for undo extensions?

SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

Example:

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;

Example:

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

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';

Example:

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"

Provide query output from the "diagnostic information" section of Doc ID 1579081.1

Keywords: Oracle SQL Database

Added by Oren on Fri, 29 Nov 2019 16:06:59 +0200