Troubleshooting ORA-30036 - Unable To Extend Undo Tablespace (Doc ID 460481.1)

Troubleshooting ORA-30036 - Unable To Extend Undo Tablespace (Doc ID 460481.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.2 [Release 9.2 to 11.2]
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.3 [Release 11.2]
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
Information in this document applies to any platform.

PURPOSE

Purpose of this document is to have a checklist for troubleshooting ORA-30036 errors i.e unable to extend segment by %s in undo tablespace '%s' when using Automatic Undo Management (AUM). This note will also discuss some known issues on this subject along with the diagnostic information required while raising a Service Request with Oracle Support.

本文档的目的是要提供一个清单来解决ORA-30036错误，即使用Automatic Undo Management (AUM) 时无法在undo tablespace '％s' 中将段扩展％s。本说明还将讨论有关此主题的一些已知问题，以及在通过Oracle支持提出服务请求时所需的诊断信息

TROUBLESHOOTING STEPS

When a ORA-30036 will be reported.  何时会报告ORA-30036

ORA-30036 error is reported when the current Undo tablespace has no more free space available for the active transactions.

当前的undo表空间没有更多的可用空间可用于活动事务时，报告ORA-30036错误

When transactions hit a database and they need undo space. The undo space allocation happens in the following sequence:

当事务命中数据库时，它们需要undo空间。undo空间分配按以下顺序进行

1. Allocate an extent in an undo segment which has no active transaction. Oracle tries to distribute transactions over all undo segments.

1. 在没有活动事务的undo段中分配一个扩展区。 Oracle尝试在所有undo段上分配事务。

2. If no undo segment found then oracle tries to online an off-line undo segment and use it.

2. 如果未找到任何undo段，则oracle尝试联机离线undo段并使用它

3. If no undo segments to online, then we create a new undo segment and use it.

3. 如果没有undo段联机，那么我们将创建一个新的undo段并使用它

4. If space does not permit creation of undo segment, then we try to reuse an expired extent from the existing undo segments.

4. 如果空间不允许创建undo段，则我们尝试重用现有undo段中已过期的范围

For a running transaction associated with undo segment/ extent, if it needs more undo space then:

对于与 undo段/扩展区 关联的正在运行的事务，如果它需要更多的undo空间，则

1. If the current extent has more free blocks then use the next free block that is all ready allocated to the extent.

1. 如果当前扩展区具有更多可用块，则使用下一个已准备好为扩展区分配的空闲块

2. If the current extent does not have free blocks and if the next extent of the segment has expired then wrap in the the next extent and return the first block.

2. 如果当前扩展区没有空闲块，并且该段的下一个扩展区已到期，则包装下一个扩展区并返回第一个块。

3. If the next extent has not expired then get space from the UNDO tablespace. If a free extent is available then allocate it to the undo segment and return the first block in the new extent.

3. 如果下一个扩展区尚未过期，请从UNDO表空间获取空间。如果有可用的扩展区，则将其分配给undo段，并返回新扩展区中的第一个块。

4. If there is no free extent available then steal from an offline undo segment. Deallocate the extent from the offline undo segment and add it to the current undo segment. Return the first free block of the extent.

4. 如果没有可用的扩展区，则从脱机undo段中窃取。从脱机undo段中取消分配范围，并将其添加到当前undo段中。返回范围的第一个空闲块。

5. Steal from online undo segment. Deallocate the extent from the online undo segment and add it to the current undo segment. Return the first free block of the extent.

5. 从在线undo部分中窃取。从在线undo段中取消分配范围，并将其添加到当前undo段中。返回范围的第一个空闲块

NOTE: Bug 2900863 indicates this steps is broken in some cases. This is fixed in Server patchset version 9.2.0.5 and above, and in Server release 10g.

注意：Bug 2900863 指示在某些情况下此步骤已被破坏。在服务器修补程序版本9.2.0.5和更高版本以及服务器版本10g中，此问题已修复

6. Extend the file in the UNDO tablespace. If the file can be extended then add an extent to the current undo segment then return the block.

6. 在UNDO表空间中扩展文件。如果文件可以扩展，则将范围添加到当前undo段，然后返回该块。

7. Otherwise try to reuse unexpired extents from own undo segment. If all extents are currently busy(they contains uncommitted information) go to the step 8. Otherwise wrap into the next extent.

7. 否则，请尝试重用自己的undo段中未到期的扩展区。如果当前所有盘区都处于繁忙状态（它们包含未提交的信息），请转到步骤8。否则请包装到下一个盘区。

8. Randomly steal unexpired extents from offline undo segments. If this fails then try to online undo segments for reuse.

8. 从离线undo段中随机窃取未到期的扩展区。如果失败，则尝试联机undo段以进行重用

9. If all the above fails then return ORA-30036 unable to extend segment by %s in undo tablespace '%s'

9. 如果以上所有方法均失败，则返回 ORA-30036 unable to extend segment by %s in undo tablespace '%s'

This Error states that we need to add more space to Undo Tablespace, which might not be case always.

此错误表明我们需要向 Undo Tablespace 添加更多空间，但情况并非总是如此。

Failure to steal occurs where the undo is sized too small and the transaction is unable to get undo blocks even by stealing from other undo segments.

如果undo的大小太小并且即使从其他undo段中进行窃取，事务也无法获得undo块，则会发生窃取失败

Troubleshooting ORA-30036 Errors  排除ORA-30036错误

i) Check free space in the undo tablespace.  检查undo表空间中的可用空间

select sum(bytes) from dba_free_space where tablespace_name='<undo tablespace>';
select sum(bytes) from dba_data_files where tablespace_name='<undo tablespace>';

 

ii) Check whetherUndo tablespace datafile is autoextensible.   检查 Undo tablespace数据文件是否可自动扩展

select autoextensible from dba_data_files where tablespace_name='<undo tablespace>;

iii) Check whether unexpired extents are available in the same segment as the current transaction. 检查与当前事务相同的extents中是否有未到期的范围

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

In case no undo space is left, then we try to use unexpired extents (Undo Extent required to honour UNDO_RETENTION). This sometimes results in ORA-1555 errors. Now if you do not have unexpired extents also, then you need to add space to undo tablespace.

如果没有剩余undo空间，则我们尝试使用未过期的扩展区（需要undo扩展以兑现UNDO_RETENTION）。有时这会导致ORA-1555错误。现在，如果您也没有未到期的扩展区，则需要添加空间以undo表空间

iv) Check the status of the Undo extents.  检查Undo extents的状态

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

The actions depends based on the output: 操作取决于输出

Case 1: No Expired extents available and mostly Active extents present.  情况1：没有可用的扩展盘区，并且主要存在活动盘区

If there are no Expired extents that can be re-used then its possible to encounter ORA-30036. If we see mostly Active extents then this is most likely Undo sizing issue. In this case, check if Undo Tablespace is correctly sized.

如果没有可以重复使用的扩展范围，则可能会遇到ORA-30036。如果我们主要看到活动范围，则很可能是“撤消调整大小”问题。在这种情况下，请检查“撤消表空间”的大小是否正确。

The following query calculates the number of bytes needed (based on the current workload): 

以下查询计算所需的字节数（基于当前工作负载）

SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),
(select block_size as DBS from dba_tablespaces where tablespace_name=
(select value from v$parameter where name = 'undo_tablespace'));

*(UR) UNDO_RETENTION in seconds

*(UPS) Number of undo data blocks generated per second

*(DBS) Overhead varies based on extent and file size (db_block_size)

Refer to below note for more explanation on this
How To Size UNDO Tablespace For Automatic Undo Management (Doc ID 262066.1)

With Oracle database 10g, you can use the Undo Advisor feature to get Undo sizing Recommendations

Refer: Check Current Undo Configuration and Advise Recommended Setup (Doc ID 1579035.1)

Case 2: No Expired extents available and mostly Unexpired extents present. 情况2：没有可用的扩展盘区，并且大多存在未过期的盘区

If there are no Expired extents that can be re-used then its possible to encounter ORA-30036. If we see mostly Unexpired extents then it can be either a Undo spacing issue or caused by unreasonably high Undo retention. In other words, this means the Undo space is not enough for the specified Undo_Retention or the Tuned_undoretention value.

如果没有可以重复使用的扩展范围，则可能会遇到ORA-30036。如果我们主要看到未到期的范围，则可能是撤消间距问题，也可能是由于撤消保留率过高造成的。换句话说，这意味着对于指定的Undo_Retention或Tuned_undoretention值，Undo空间不足。

Check the undo retention and the tuned_undo retention 检查undo retention和tuned_undo保留

Show parameter Undo

select max(tuned_undoretention) from v$undostat;

 

If the tuned_undoretention is too high the workarounds will be (any one of the following): 

如果tuned_undoretention太高，解决方法将是（以下任一方法）

a. Turn on autoextensibility of the undo tablespace datafiles and set the MAXSIZE to the actual size of the all the datafiles of the undo tablespace.

a. 打开undo表空间数据文件的自动扩展功能，并将MAXSIZE设置为撤消表空间的所有数据文件的实际大小

This alters the algorithm used for calculating tuned_undoretention and thus the tuned_undoretention values set wont be too high compared to the actual requirements.

这改变了用于计算tuned_undoretention的算法，因此设置的tuned_undoretention值与实际需求相比不会太高

b. Set _smu_debug_mode=33554432

This again alters the algorithm used for calculating tuned_undoretention and thus the tuned_undoretention values set wont be too high compared to the actual requirements.

这再次改变了用于计算tuned_undoretention的算法，因此所设置的tuned_undoretention值与实际需求相比不会太高

c. set the _first_spare_parameter (10.2) or _highthreshold_undoretention (11.x) instance parameter to a value limiting the tuned undo retention value.

c. 将 _first_spare_parameter（10.2）或 _highthreshold_undoretention（11.x）实例参数设置为限制已调整撤消保留值的值

This value acts as an upper limit for the tuned_undoretention

此值用作tuned_undoretention的上限

d. Disable automatic tuning of undo by setting _undo_autotune=false

d. 通过设置_undo_autotune = false禁用自动调整撤消

Refer Automatic Tuning of Undo Retention Common Issues (Doc ID 1579779.1) for details.

Case 3: Lot of Expired extents are available.   情况3：有许多可用的扩展区

If Expired extents are present and still ORA-30036 is encountered , it means that the EXPIRED extents are not being reused. These Expired extents should have been reused and instead we are getting ORA-30036 error.

如果存在Expired扩展盘区，仍然遇到ORA-30036，则表示EXPIRED扩展盘区没有被重用。这些过期的范围应该已被重用，相反，我们收到ORA-30036错误

This could be because of Unpublished Bug 5442919 which is fixed in 10.2.0.4 ( and 11g ).

Bug 5442919 - Expired extents not being reused (ORA-30036) (Doc ID 5442919.8)

If this is happening on other versions, then please report the same to Oracle Support for further investigation

Known issues

1. In case of 10g and above, if you are using Guaranteed Undo Retention, then unexpired extents will not be reused and you will be required to add space to the Undo tablespace.

1. 在 10g及以上的情况下，如果您使用 Guaranteed Undo Retention，那么未到期的扩展区将不会被重用，并且需要向撤消表空间添加空间

2. You might see in 10gR2, that Undo tablespace might be at100%.

2. 在10gR2中，您可能会看到Undo表空间可能位于100％。

This is expected behavior. You can refer to below note which discusses the issue

这是预期的行为。您可以参考下面的文章，其中讨论了该问题

Full UNDO Tablespace In 10gR2 (Doc ID 413732.1)

3. For issues reported during export or import

3. 对于在导出或导入过程中报告的问题

Troubleshooting ORA-01555/ORA-01628/ORA-30036 during export and import (Doc ID 1579437.1)

4. For errors reported after enabling Flashback Data Archive.

4. 对于启用闪回数据存档后报告的错误

Errors After Enabling Fbda ORA-1950 ORA-942 ORA-30036 (Doc ID 1330817.1)

Diagnostic information to be collected while raising the SR with Oracle Support

a. Upload the alert log file

b. Provide the following outputs

select sum(bytes) from dba_free_space where tablespace_name='<undo tablespace>';
select sum(bytes) from dba_data_files where tablespace_name='<undo tablespace>';

select sum(blocks) "UNEXPIRED BLOCKS" from dba_undo_extents where tablespace_name='UNDOTBS1'and status='UNEXPIRED';
select sum(blocks) "EXPIRED BLOCKS" from dba_undo_extents where tablespace_name='UNDOTBS1'and status='EXPIRED';

select sum(blocks) "ACTIVE BLOCKS" from dba_undo_extents where tablespace_name='UNDOTBS1'and status='ACTIVE';

select count(*) from dba_rollback_segs where status='OFFLINE';

Show parameter Undo

select max(tuned_undoretention) from v$undostat;

c. Set _smu_debug_mode=16384 and provide the tracefile generated when ORA-30036 is generated.

The parameter _smu_debug_mode=16384 at system level generate diagnostic information when error ora-30036 is reported.

REFERENCES

NOTE:413732.1 - Full UNDO Tablespace In 10gR2 and above
NOTE:461480.1 - FAQ – Automatic Undo Management (AUM) / System Managed Undo (SMU)