Symptom:
When Apps / DB team are doing data migration , there are big batches with insert sql and the migration tool failed with error:
ORA-01628: max # extents (32765) reached for rollback segment XXXXXXXX
Diagnosis:
The error informs that there were high volume of data undo segments and it reached the limit 32k.
Use sql to check status of undo segments:
SQL> SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024, COUNT(*) FROM
DBA_UNDO_EXTENTS GROUP BY STATUS;
STATUS SUM(BYTES)/1024/1024 COUNT(*)
--------------------------- -------------------- ----------
UNEXPIRED 162676.563 55123
EXPIRED 12121.5625 12319
So majority of the space is used by UNEXPIRED extents
So something is wrong here, undo extents should not have majority as UNEXPIRED unless there are huge queries running right now.
From AWR report, we can find the longest query is about 7654s. As long as undo tablespace has enough space for this longest query, the undo extents it uses should expire soon after the query is finished.
From 10g , oracle DB added auto tuned undo which is based on statistic data. Default it is TRUE
Use sql to check this hidden parameter
SELECT
a.ksppinm Param , b.ksppstvl SessionVal ,c.ksppstvl InstanceVal,a.ksppdesc Descr
FROM x$ksppi a , x$ksppcv b , x$ksppsv c WHERE
a.indx = b.indx AND
a.indx = c.indx AND
a.ksppinm LIKE '/_%undo_autotune%' escape '/'
ORDER BY 1;
The extents are not expired as we expect due to another hidden parameter
_highthreshold_undoretention. It is used to control auto tuned undo behaviour.
By default it is set 4294967294 (50 days) . It normally works if no big migration.
However giving big migration project, it turns out to keep large volume of undo extents
unexpired with this big value. So the solution is to make it small 86400s (1 day) and they will expire in 1 day.
Solution:
ALTER SYSTEM SET "_highthreshold_undoretention"=86400 scope=both and sid='*';
