Master Note: High Undo Space Usage (Doc ID 1578639.1)

APPLIES TO:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.

PURPOSE

This troubleshooting guide is to identify high undo usage and correct them. This also lists various known issues on high undo usage and their workarounds.

本故障排除指南旨在识别高undo用法并进行更正。这也列出了有关高undo使用率的各种已知问题及其解决方法。

This also explains the method for Sizing Undo Tablespace appropriately.

这也说明了适当调整undo表空间大小的方法。

TROUBLESHOOTING STEPS

Sizing Undo Tablespace  调整undo表空间的大小

Sizing the undo tablespace plays vital role in most of the undo issues.  You can either use Auto-extensible tablespace or use undo advisor to determine the size of the fixed size undo tablespace

调整undo表空间的大小在大多数undo问题中都起着至关重要的作用。您可以使用自动扩展表空间，也可以使用undo顾问程序来确定固定大小的undo表空间的大小。

When the undo tablespace is auto-extensible, Oracle automatically increases the size of the tablespace when more space is needed. By combining automatic extension of the undo tablespace with automatically tuned undo retention, you can ensure that long-running queries succeed by guaranteeing the undo for such queries.

当undo表空间是自动扩展的时，当需要更多空间时，Oracle会自动增加表空间的大小。通过将undo表空间的自动扩展与自动调整的undo保留相结合，可以通过保证此类查询的undo来确保长时间运行的查询成功

If using Fixed size Undo tablespace, use Undo Advisor to size the tablespace. The Undo Advisor helps you analyze various scenarios to determine an appropriate undo tablespace size for different values of maximum undo retention.

如果使用固定大小的undo表空间，请使用undo顾问来调整表空间的大小。undo顾问可帮助您分析各种方案，以为最大undo保留的不同值确定合适的undo表空间大小

Note:
Always allow 10 to 20% extra space in your undo tablespace to provide for some fluctuation in your workload    

始终在undo表空间中留出10％到20％的额外空间，以减轻工作量的变化在    

With AUM in place, UNDO size can be controlled with the undo_retention parameter and the size of the UNDO tablespace, and the setting for these are determined by the level of DML activity in the database

使用AUM的情况下，可以使用undo_retention参数和UNDO表空间的大小来控制UNDO的大小，这些设置取决于数据库中DML活动的级别

Refer: How To Size UNDO Tablespace For Automatic Undo Management (Doc ID 262066.1)

Determining Space Requirement:  确定空间要求

V$UNDOSTAT displays a histogram of statistical data to show how well the system is working. The available  include undo space consumption, transaction concurrency, and length of queries executed in the instance. You can use this view to estimate the amount of undo space required for the current workload. Oracle uses this view to tune undo usage in the system. This view is available in both automatic undo management mode and manual undo management mode.

V$UNDOSTAT 显示统计数据的直方图，以显示系统的运行状况。可用的包括undo空间消耗，事务并发以及在实例中执行的查询的长度。您可以使用此视图来估计当前工作负载所需的undo空间量。Oracle使用此视图来调整系统中的undo使用情况。该视图在自动undo管理模式和手动undo管理模式下均可用

Shrink Undo Datafiles  缩小undo数据文件

Refer the document  How to Shrink the datafile of Undo Tablespace (Doc ID 268870.1)

Troubleshooting High Space Usage  解决空间使用高的情况

Check the freespace avialable in the undo tablespace:  检查undo表空间中可用的可用空间

SELECT SUM(BYTES) FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='&UNDOTBS';

The high space usage can be mainly because:  高空间使用率可能主要是因为

a. A huge transaction that requires more space  巨大的事务需要更多空间

b. Lots of Unexpired extents (the extents that are required for undo retention)  许多未到期的扩展区（undo保留所需的扩展区）

c. Expired extents not being re-used (mostly due to some bugs)  过期的扩展区无法重复使用（主要是由于某些错误）

d. Other reported bugs.  其他报告的错误

To proceed with the analysis, we need to know the status of the undo extents :  要进行分析，我们需要知道undo范围的状态

select sum(bytes /(1024*1024)) from dba_undo_extents where status='EXPIRED';
select sum(bytes /(1024*1024)) from dba_undo_extents where status='ACTIVE';
select sum(bytes /(1024*1024)) from dba_undo_extents where status='UNEXPIRED';

DBA_UNDO_EXTENTS describes the extents comprising the segments in all undo tablespaces in the database.  This view shows the status and size of each extent in the undo tablespace.

DBA_UNDO_EXTENTS 描述了组成数据库中所有undo表空间中的段的范围。此视图显示undo表空间中每个扩展区的状态和大小

To tune SQL queries or to check on runaway queries, use the value of the SQLID column provided in the long query or in the V$UNDOSTAT or WRH$_UNDOSTAT views to retrieve SQL text and other details on the SQL from V$SQL view.

要调整SQL查询或检查失控查询，请使用长查询或 V$UNDOSTAT or WRH$_UNDOSTAT 视图中提供的SQLID列的值，以从 V$SQL 视图中检索SQL文本和有关SQL的其他详细信息

Case 1. Active transaction consuming space (Undo extents are marked Active)  情况1.活动事务消耗空间（undo范围标记为活动）

If there is a huge transaction then high space usage is expected. You can verify the same using the above output, where the Undo extents will be mostly 'Active'.

如果事务量很大，那么将需要大量空间使用。您可以使用上面的输出来验证相同的结果，其中undo范围将大部分为“活动”。

Case 2. High Space Usage by Committed transactions (Undo extents are marked Unexpired)  情况2：已提交事务的高空间使用率（undo范围标记为未过期）

In this case, the Undo blocks for the committed transactions are kept to honour the undo retention time. This is controlled either by a high Undo_Retention parameter set or by the high value for tuned_undoretention.

在这种情况下，将保留已提交事务的“undo”块以兑现undo保留时间。这由较高的 Undo_Retention 参数集或 tuned_undoretention 的较高值控制

select max(maxquerylen),max(tuned_undoretention) from v$undostat;

 In case of non autoextensible Undo tablespace, the tuned_undoretention is calculated in such a way that we try to provide the maximum retention utilizing the space available. Hence, the Undo blocks are retained for a longer time and this inturn makes the Undo tablespace to appear as if its full. The tuned undoretention will be adjusted when there is a space requirement.

在非自动扩展的Undo表空间的情况下，tuned_undoretention 的计算方式是我们试图利用可用空间来提供最大的保留。因此，undo块保留了更长的时间，这反过来使undo表空间看起来像是已满。有空间需求时，将调整调整后的未保留时间

Refer: Full UNDO Tablespace In 10gR2 and above (Doc ID 413732.1)

When undo tablespace is using NON-AUTOEXTEND datafiles,V$UNDOSTAT.TUNED_UNDORETENTION may be calculated too high preventing undo block from being expired and reused.

当undo表空间正在使用NON-AUTOEXTEND数据文件时，V$UNDOSTAT.TUNED_UNDORETENTION 可能计算得太高，从而导致undo块过期和重用

Refer: Automatic Tuning of Undo_retention Causes Space Problems (Doc ID 420525.1)    

Case 3: Lots of Expired blocks  情况3：大量过期块

The Expired blocks will be reused and hence this should be counted as 'availabe' space in the Undo segment.

过期的块将被重用，因此在undo段中应将其计为“可用”空间

Known Issues

a) If there is a heavy workload before shutdown then there may have been a lot of undo produced in a short space of time giving a low tuned retention. If DB is then shutdown and restarted the in-memory information starts afresh, but there can be a lot of blocks in undo segments from before the shutdown which are not expired , and will not now expire for a long time as they fall inside the new higher tuned_undoretention period.

a) 如果关机前工作量很大，则可能会在很短的时间内产生大量undo，从而导致调整后的保留率较低。如果然后关闭数据库并重新启动，则内存中信息将重新开始，但是从关闭之前起，undo段中可能有很多块未到期，并且由于它们属于新内存，因此现在很长一段时间都不会到期较高的tuned_undo保留期。

BUG:9681444 - TUNED_UNDORETENTION CAN BE TOO HIGH AFTER DB BOUNCE IF HIGH WORKLOAD BEFORE
Fixed in 12.1

Workaround : Setting _highthreshold_undoretention

This helps them limit the tuned undo retention to any particular value. @alter system set  "_highthreshold_undoretention" =  10800;  (set to 10800 seconds if undo_retention value is less than 3 hours. Otherwise go with max  of (undo_retention, 10800 seconds))

这有助于他们将调整后的undo保留限制为任何特定值。@alter system set  "_highthreshold_undoretention" =  10800; （如果undo_retention值小于3小时，则设置为10800秒。否则，最大值为（undo_retention，10800秒））

In 12g the above parameter will be set by default  在12g中，默认情况下将设置上述参数

Refer:Undo Remains Unexpired When Using Non-autoextensible Datafiles For The Undo Tablespace (Doc ID 1112431.1)    

b) High undo usage seen when import by impdp is done to the table where the index is created. It seems to consume UNDO with the split of the index.When import is done without creating the index, the consumption of UNDO is a little.

b) 通过impdp导入到创建索引的表时，会看到较高的undo使用率。似乎使用了索引拆分消耗了UNDO。在未创建索引的情况下完成导入时，UNDO的消耗很小

impdp maintenance index during import as default, and does not use direct_path if table and index is already created. 

默认情况下，导入期间的impdp维护索引为默认值，如果已经创建表和索引，则不使用direct_path

But, if there is no index who enforce constraint, and specify access_method=direct_path at impdp command line, we can use direct path method to import. 

但是，如果没有索引执行约束，并在impdp命令行上指定 access_method = direct_path，则可以使用直接路径方法导入

Workaround: load data by direct path by disabling primary key constraint (using ALTER TABLE ... MODIFY CONSTRAINT ... DISABLE NOVALIDATE) and using access_method=direct_path.

解决方法：通过禁用主键约束（使用ALTER TABLE ... MODIFY CONSTRAINT ... DISABLE NOVALIDATE）并使用access_method = direct_path来通过直接路径加载数据。

- after loading data, enable primary key constraint (using ALTER TABLE ... MODIFY CONSTRAINT ... ENABLE VALIDATE)

- 加载数据后，启用主键约束（使用ALTER TABLE ... MODIFY CONSTRAINT ... ENABLE VALIDATE）

Refer: Error ORA-30036 DataPump Import (IMPDP) Exhausts Undo Tablespace (Doc ID 727894.1)    

c) Active Undo segments without actual transactions  没有实际的活动undo段交易

Issue 1: There exist active undo segments without actual transaction presence. 95-98% of undo space is consuming by ACTIVE undo segments, and consumed space continues to increase despite the absence of transactions)

问题1：存在没有实际交易存在的活动undo段。活动undo段占用了95-98％的undo空间，尽管没有事务，但消耗的空间仍在增加）

It is caused by the fact that the active undo had touched flashback enabled tables but there was no active flashback data archiver (FBDA) process to handle the archiving of those transactions.

这是由于活动undo已触及启用了闪回功能的表，但没有活动的闪回数据存档器（ FBDA）流程来处理这些交易的归档

The instance parameter "_disable_flashback_archiver" is set, so the FBDA processs was not starting. This must be reset and the instance must be restarted.

实例参数“ _disable_flashback_archiver”已设置，因此FBDA进程未启动。必须将其重置，并且必须重新启动实例

Refer : Undo Continuously Growing And Not Releasing Active Segments (Doc ID 1476614.1)

Issue 2:

In 11.2.0.3

A rapidly growing number of active undo extents may be seen in a non-RAC (or former RAC) environment if Flashback Archive is being used.

如果正在使用闪回存档，则在非RAC（或以前的RAC）环境中可以看到数量迅速增加的活动undo范围

Its due to Bug 16196536  Many active undo extents with flashback Archive 由错误16196536引起的许多带有闪回的活动undo范围存档
If the amount of active undo segments is growing very rapidly AND select * from SYS_FBA_BARRIERSCN shows more than one instance (INST_ID) in a non-RAC environment, then you have likely hit this issue.

如果活动undo段的数量增长非常迅速，并且从SYS_FBA_BARRIERSCN中选择*，则在非RAC环境中显示多个实例（INST_ID），那么您很可能遇到了这个问题。

Try the following workaround: 请尝试以下解决方法

a)  backup the current record in sys_fba_barrierscn table
    SQL> create table csdba.sys_fba_barrierscn as select * from sys_fba_barrierscn;
    
b) Deleting information in the sys.sys_fba_barrierscn table where inst_id = 1
    SQL>   alter system set "_fbda_debug_mode"=8;
    SQL>   delete from sys.sys_fba_barrierscn where inst_id = 1;
    SQL>   commit;
    SQL>   alter system set "_fbda_debug_mode"=0;
 
c) flush the shared pool to make sure the memory is clean
    SQL>   alter system flush shared_pool;

Refer :Bug 14164829: ACTIVE UNDO GROWING NO ACTIVE TRANSACTION CAN BE FOUND

This issue is fixed in 11.2.0.4 and above. 此问题已在11.2.0.4及更高版本中修复

Diagnostic Information Required While Raising a Service Request  提出服务请求时需要的诊断信息

Provide the following information while raising a Service Request with Oracle Support 

在通过Oracle Support持提出服务请求时提供以下信息

1. Provide the outputs of the following queries: 提供以下查询的输出

Status of the undo blocks: undo块的状态

 col segment_name format a30 head "Segment Name"
 col "ACT BYTES" format 999,999,999,999 head "Active Bytes"
 col "UNEXP BYTES" format 999,999,999,999 head "Unexpired Bytes"
 col "EXP BYTES" format 999,999,999,999 head "Expired Bytes"
 
 select segment_name, nvl(sum(act),0) "ACT BYTES",
    nvl(sum(unexp),0) "UNEXP BYTES",
    nvl(sum(exp),0) "EXP BYTES"
    from (select segment_name, nvl(sum(bytes),0) act,00 unexp, 00 exp
    from dba_undo_extents where status='ACTIVE' group by segment_name
    union
    select segment_name, 00 act, nvl(sum(bytes),0) unexp, 00 exp
    from dba_undo_extents where status='UNEXPIRED' group by segment_name
    union
   select segment_name, 00 act, 00 unexp, nvl(sum(bytes),0) exp
   from dba_undo_extents where status='EXPIRED' group by segment_name)
   group by segment_name
   order by 1
   /

 Free space available within the Undo tablespace:  undo表空间中的可用空间

SELECT SUM(BYTES) FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='&UNDOTBS';

SELECT autoextensible FROM dba_data_files WHERE tablespace_name='&UNDOTBS';

Undo Retention and the tuned_undoretention  undo保留和tuned_undoretention

Show parameter Undo

Select max(maxquerylen),max(tuned_undoretention) from v$undostat;

REFERENCES

NOTE:16196536.8 - Bug 16196536 - Many active undo extents with flashback Archive