Troubleshooting ORA-1628 - max # extents (32765) reached for rollback segment <SEGMENT_NAME> (Doc ID 1580182.1)

Troubleshooting ORA-1628 - max # extents (32765) reached for rollback segment <SEGMENT_NAME> (Doc ID 1580182.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 Cloud Exadata Service - Version N/A and later
Oracle Database Backup 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-01628 errors i.e max # extents (32765) reached for rollback segment <SEGMENT_NAME> when using Automatic Undo Management (AUM). 

本文档的目的是提供一个清单，以解决ORA-01628错误，即使用 Automatic Undo Management (AUM) 时 max # extents (32765) reached for rollback segment <SEGMENT_NAME>

This document also lists Known issues and bugs which may cause ORA-01628. 本文档还列出了可能导致ORA-01628的已知问题和错误

TROUBLESHOOTING STEPS

First: Check UNDO tablespace utilization and tuned undo retention :  第一：检查UNDO表空间利用率并调整undo retention

Aspect of the problem can be due to long running queries which can raise tuned_undoretention to very high values and exhausts the undo tablespace resulting in ORA-1628.

问题的一部分可能是由于长时间运行的查询可能将 tuned_undoretention 提升到非常高的值，并且耗尽了undo表空间，从而导致ORA-1628

So before diagnosing 1628 errors, it is important first to check UNDO tablespace utilization and tuned undo retention as follow :

因此，在诊断1628错误之前，重要的是首先要检查UNDO表空间利用率并调整undo retention，如下所示

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

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

SQL> select max(maxquerylen),max(tuned_undoretention) from DBA_HIST_UNDOSTAT;

SQL> select sum(bytes) from dba_free_space where tablespace_name='&UNDOTBS';

Before proceed, Invistigate/Resolve any excessive allocation of ACTIVE/UNEXPIRED extents and high calculation of tuned_undoretention.

在继续之前，请 Invistigate/Resolve 任何对 ACTIVE/UNEXPIRED extents 的过度分配以及对 tuned_undoretention 的大量计算

Second: 1628 troubleshooting :  第二：1628故障排除

Basically, It is obvious to see high undo usage when there are huge transactions. 基本上，当有大量事务时，很明显会看到较高的 undo 使用率

Here is a query you can use to find out how much undo a transaction is using:  这是一个查询，您可以使用它查询事务正在使用多少undo操作

select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
from v$session a, v$transaction b
where a.saddr=b.ses_addr;

As you know, with automatic undo, you have no control over the extent sizes, nor do you have the ability to shrink them. This all happens automatically. The system automatically decides on the extent size, however it will generally start allocating larger extents if an undo segment is extending a lot. The large number of extents in the rollback segment is likely due to fragmentation in the undo tablespace: Oracle can probably only allocate extents of 64k due to fragmentation, so it is very probable to hit the max extents issue.

如您所知，使用自动undo，您无法控制扩展区大小，也无法缩小它们。这一切都是自动发生的。系统会自动决定扩展区的大小，但是如果undo段扩展了很多，通常它将开始分配更大的扩展区。回滚段中的大量扩展区可能是由于undo表空间中的碎片所致：由于碎片，Oracle可能只能分配64k的扩展区，因此很有可能遇到最大扩展区问题

The maximum number of extents for undo segments is limited to 32K and a long/large runing transaction can exhaust this limit by adding new extents if the next extent of the current one is not an expired one and finally will receive ORA-1628.

undo 段的最大扩展区数限制为32K，并且如果当前扩展区中的下一个扩展区不是过期的扩展区，则长/大型运行事务可以通过添加新扩展区来耗尽此限制，并且最终将收到ORA-1628

So, after getting the ORA-1628 errors in the transaction that extended the undo segment until its limit, future transactions will not be allowed to bind to the undo segment until it is not shrinked (you may see that the number of extents is decreased).

因此，在将undo段扩展到其限制之前的事务中出现ORA-1628错误之后，在不缩小undo段之前，将来的事务将不被绑定到undo段（您可能会看到扩展区的数量减少了）

So, The two major causes of ORA-1628 issue are a very large transaction or undo tablespace fragmentation.

因此，ORA-1628问题的两个主要原因是非常大的事务或undo表空间碎片

In case of large transaction, That is solved by splitting the large transaction to smaller ones (e.g. frequent commits).  

对于大事务，可以通过将大事务拆分为较小的事务（例如频繁提交）来解决。

In case of undo tablespace fragmentation, That is solved by recreating the undo tablespace (this is also the recommended solution of Bug 10330444 and Bug 10229998 which were filed for the same issue and closed as not a bug).

如果是undo表空间碎片，可以通过重新创建undo表空间来解决（这也是建议的Bug 10330444和Bug 10229998的解决方案，它们是针对同一问题而提交的，并且已作为非bug关闭）。

To sum up:  总结一下

The ORA-1628 error is occurring in a transaction that is generating a lot of undo data, during an add extent operation in an undo segment and is indicating we have hit the MAXEXTENTS (32765) and then we cannot extend the undo segment.

ORA-1628 错误发生在一个事务中，该事务生成大量undo数据，在undo段的加范围操作期间，这表明我们已经达到了MAXEXTENTS（32765），然后我们无法扩展undo段

Suggested solutions  建议的解决方案

1) Set parameter "_rollback_segment_count" to online more available UNDO segments. Value should be set by placing the highest value obtained of the following queries:

1) 将参数 "_rollback_segment_count" 设置为在线更多可用的UNDO段。应通过放置以下查询获得的最大值来设置值

select status,count(*) from dba_rollback_segs group by status; --You add OFFLINE+ONLINE to get the number

select max(maxconcurrency) from wrh$_undostat;

select max(maxconcurrency) from v$undostat;

2) In case you have large value for TUNED_UNDORETENTION :

2) 如果 TUNED_UNDORETENTION 的值很高

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

SQL> select max(maxquerylen),max(tuned_undoretention) from DBA_HIST_UNDOSTAT;

A fix to Bug:7291739 is to set a new hidden parameter, _HIGHTHRESHOLD_UNDORETENTION to set a high threshold for undo retention completely distinct from maxquerylen:

Bug:7291739 的一个解决  方法  是设置一个新的隐藏参数 _HIGHTHRESHOLD_UNDORETENTION 来设置一个与 maxquerylen 完全不同的 undo retention 的高阈值：

ALTER SYSTEM SET "_highthreshold_undoretention"=max(maxquerylen)+1;

3) Before/after running large transactions, Shrink undo segments when reaching certain threshold (Ex: 15000 extents) do not wait to reach its maximum (32765) to be able to bring it below certain threshold so that this undo segment can qualify for binding again.

3) 在运行大型事务之前/之后，收缩undo段达到一定阈值（例如15000扩展区）时，不必等待达到最大值（32765）才能使其低于某个阈值，以便该undo段可以进行再次绑定

a) select a.inst_id, a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and a.inst_id=b.inst_id and b.inst_id=c.inst_id
and a.ksppinm in ('_smu_debug_mode')
order by 2;

select segment_name,
round(nvl(sum(act),0)/(1024*1024*1024),3 ) "ACT GB BYTES",
round(nvl(sum(unexp),0)/(1024*1024*1024),3) "UNEXP GB BYTES",
round(nvl(sum(exp),0)/(1024*1024*1024),3) "EXP GB BYTES",
NO_OF_EXTENTS
from ( select segment_name, nvl(sum(bytes),0) act,00 unexp, 00 exp, count(*) NO_OF_EXTENTS
from DBA_UNDO_EXTENTS
where status='ACTIVE' and tablespace_name = 'UNDOTBS1'
group by segment_name
union
select segment_name,00 act, nvl(sum(bytes),0) unexp, 00 exp , count(*) NO_OF_EXTENTS
from DBA_UNDO_EXTENTS
where status='UNEXPIRED' and tablespace_name = 'UNDOTBS1'
group by segment_name
union
select segment_name, 00 act, 00 unexp, nvl(sum(bytes),0) exp, count(*) NO_OF_EXTENTS
from DBA_UNDO_EXTENTS
where status='EXPIRED' and tablespace_name = 'UNDOTBS1'
group by segment_name
) group by segment_name, NO_OF_EXTENTS order by 5 desc;

select sum(blocks),count(*) extents,segment_name from DBA_EXTENTS
where tablespace_name = 'UNDOTBS1' group by segment_name order by 2 desc;

b) alter system set "_smu_debug_mode" = 4 scope=memory;

c) alter rollback segment "_SYSSMU<n>$" shrink;

d) alter system set "_smu_debug_mode" = <old_value_showed_at_step_a> scope=memory;

Then you can check the result of this measure by running the query in step a again before and after the above three steps.

然后，您可以通过在上述三个步骤之前和之后再次在步骤a中运行查询来检查此度量的结果

4) Drop and recreate undo tablespace (due to it's fragmentation) 

4) 删除并重新创建undo表空间（由于其碎片） 

The steps for recreating an undo tablespace are in Note 268870.1 Ext/Pub How to Shrink the datafile of Undo Tablespace.

Note 268870.1 Ext/Pub 如何缩小Undo Tablespac 的数据文件中提供了重新创建Undo Tablespac的步骤

5) Minimize the generated undo as much as possible :

5) 尽可能减少生成的undo

Example:

- split large transactions into smaller one  将大型事务拆分为较小的事务

- commit more often  更频繁地提交

- use direct path load rather than conventional path load to significantly reduce the amount of undo and thus also avoid a too high fragmentation of undo tablespace.

- 使用直接路径负载而不是常规路径负载来显着减少undo的数量，从而避免undo表空间的碎片过多。

Known issues/bugs

Bug 17306264 - ORA-1628: MAX # EXTENTS (32765) REACHED FOR ROLLBACK SEGMENT - OFTEN ENCOUNTERE

Bug 17306264 or the Patch 17306264 for 11g readme contains pre-requisite step to set the below event
event="64000 trace name context forever, level 25"
We recommend to set the event 64000 to level 25, as mentioned in the readme of the patch.

 

Bug 7291739 - Contention with auto-tuned undo retention or high TUNED_UNDORETENTION (Doc ID 7291739.8)

Bug 6499872 - ORA-01628: max # extents (32765) for rollback seg (Doc ID 6499872.8)

How To Check the Usage of Active Undo Segments in AUM (Doc ID 1337335.1)

Data Pump (or other Oracle process) Reports ORA-01628: Max # Extents (32765) For Rollback Segment _SYSSMUx$ (Doc ID 1434643.1)

ORA-1628 Max # Extents Reached Using AUM On Locally Managed Tablespace (Doc ID 761176.1)

Ora-01628: Max # Extents (32765) Reached For Rollback Segment. (Doc ID 837853.1)

ORA-1628: max # extents 32765 reached for rollback segment _SYSSMUxxx$ (Doc ID 432652.1)

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

 

Setting  _rollback_segment_count to a high value will create those many undo segments , inspite of whether its required or not. If there is a space constraint, and you are creating huge number of undo segments while startup, the existing undo segments may not be able to grow as expected. This can result in more extents with lesser size and eventually result in ORA-1628. If you have enough space available in the undo segment, setting _rollback_segment_count may not have this side effect.

 将_rollback_segment_count设置为较高的值会创建许多undo段，无论是否需要。如果存在空间限制，并且在启动时要创建大量的undo段，则现有的undo段可能无法按预期增长。这可能会导致以较小的大小生成更多的扩展区，并最终生成ORA-1628。如果undo段中有足够的可用空间，则设置_rollback_segment_count可能不会产生此副作用。

REFERENCES

NOTE:1337335.1 - How To Check the Usage of Active Undo Segments in AUM
NOTE:1434643.1 - Data Pump (or other Oracle process) Reports ORA-01628: Max # Extents (32765) For Rollback Segment _SYSSMUx$
NOTE:7291739.8 - Bug 7291739 - Contention with auto-tuned undo retention or high TUNED_UNDORETENTION
NOTE:6499872.8 - Bug 6499872 - ORA-01628: max # extents (32765) for rollback seg
NOTE:761176.1 - ORA-1628 Max # Extents Reached Using AUM On Locally Managed Tablespace

BUG:10330444 - AUM ORA-01628 ERROR AFTER UPGRADED TO 11.2.0.2
BUG:10229998 - ORA-01628: MAX # EXTENTS REACHED FOR ROLLBACK SEGMENT _SYSSMU34_14488782
NOTE:432652.1 - ORA-1628: max # extents 32765 reached for rollback segment _SYSSMUxxx$
NOTE:1579437.1 - Troubleshooting ORA-01555/ORA-01628/ORA-30036 During Export and Import
BUG:7291739 - CONTENTION UNDER AUTO-TUNED UNDO RETENTION

BUG:17306264 - ORA-1628: MAX # EXTENTS REACHED FOR ROLLBACK SEGMENT - OFTEN ENCOUNTERE
NOTE:837853.1 - Ora-01628: Max # Extents (32765) Reached For Rollback Segment.