impdp导入一张大表时，显示extents (32765) reached for rollback segme...

linxs

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

导入数据时，数据将近有1.3T,其中大字段数据占用一大部分
impdp system cluster=no schemas=SFHISTORY DIRECTORY=dump_dir0109 parallel=2 dumpfile=SFHISTORY_%U.dmp logfile=SFHISTORY_imp.log job_name=SFHISTORY01;

导入时显示错误信息：
ORA-01628: max # extents (32765) reached for rollback segment _SYSSMU8_3328692315$

查询
select count(1) from dba_ROLLBACK_SEGS  t where t.SEGMENT_NAME='_SYSSMU8_3328692315$';
32765

如果不加空间，有啥办法解决这类问题。谢谢！

Liu Maclean(刘相兵

未给出数据库版本和alert.log

linxs

Liu Maclean(刘相兵 发表于 2015-1-22 11:01
未给出数据库版本和alert.log

数据库版本和alter日志已上传，谢谢！

Liu Maclean(刘相兵

odm fiding:

statement in resumable session 'NSFDATA.SFHISTORY01.1' was suspended due to
    ORA-01628: max # extents (32765) reached for rollback segment _SYSSMU8_3328692315$

[oracle@vrh8 ~]$ oerr ora 1628
01628, 00000, "max # extents (%s) reached for rollback segment %s"
// *Cause:  An attempt was made to extend a rollback segment that was
//          already at the MAXEXTENTS value.
// *Action: If the value of the MAXEXTENTS storage parameter is less than
//          the maximum allowed by the system, raise this value.

Liu Maclean(刘相兵

odm finding:

_highthreshold_undoretention = undo_retention +1 as suggested in
bug 14383619

First: Check UNDO tablespace utilization and tuned 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.

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

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, Invistiagte/Resolve any excessive allocation of ACTIVE/UNEXPIRED extents and high calculation of tuned_undoretention.

Second: 1628 troubleshooting :

Basically, It is obvious to see high undo usage when there are huge transactions.

Here is a query you can use to find out how much undo a transaction is using:

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.

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.

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).

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

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).

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.

Suggested solutions

1) Online more available UNDO segments by setting the following parameter to distribute transactions load among more undo segments :

alter system set "_rollback_segment_count"=1000 scope=spfile;

Bounce the database.

2) In case you have large value for 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:

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.

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.

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

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

5) Minimize the generated undo as much as possible :

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.

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)

Liu Maclean(刘相兵

1、尝试 执行上述建议 包括 ALTER SYSTEM SET "_highthreshold_undoretention"=max(maxquerylen)+1;
2、尝试 将impdp分成多个步骤来完成

linxs

后来解决了
1.重建undo
2.分步导入