After switching to a new undo tablespace, transactions would fail with ORA-01552.
During a data warehouse migration using GoldenGate, the target database returned an ORA-1628 error when applying transactions from the source.
The assigned DBA set a couple of initialisation parameters as prescribed by
Troubleshooting ORA-1628 – max # extents (32765) reached for rollback segment <SEGMENT_NAME> (Doc ID 1580182.1)
alter system set "_smu_debug_mode" = 4 scope=both sid='*'; alter system set "_rollback_segment_count"=999999 scope=both sid='*';
The second parameter caused this warning:
ORA-30018: Create Rollback Segment failed, USN 32760 is out of range
During the migration, the undo tablespace grew to a ridiculous size. In an attempt to reclaim terabytes of space, the DBA tried to replace the undo tablespace with a smaller one. However, after creating a new tablespace and switching to it, transactions failed with:
ORA-01552: cannot use system rollback segment for non-system tablespace...
So, the original and huge undo tablespace was retained for the ‘go-live’ while Oracle Support tried to work out what caused the ORA-01552.
After going live on the new database, ETL performance was disappointing due to the overhead of DDL for management of the undo segments and the domination of the buffer cache by undo blocks.
At first Oracle Support claimed that creating the maximum number of undo segments was beneficial. However, when pushed, they could not explain exactly how having 32,760 undo segments helped in a database with a low number concurrent transactions. How would it prevent hitting the maximum number of extents (ORA-1628)? What was the cost?
After removing the undo parameters prescribed by 1580182.1, there was no improvement to performance and the ORA-01552 still prevented a switch to a smaller undo tablespace.
I started investigating and found that when a new undo tablespace was created, no new rows were inserted into UNDO$ is because the limit had been reached:
select ts#,name from ts$ where name like 'UNDO%'; TS# NAME --- -------- 2 UNDOTBS1 4 UNDOTBS2 145 UNDOTBS3
break on report compute sum of segments on report select max(US#),count(1) segments,ts# from sys.undo$ group by ts#; MAX(US#) SEGMENTS TS# ---------- ---------- --- 32758 20753 2 32759 12006 4 0 1 0 ---------- sum 32760
The UNDOTBS3 tablespace had no undo segments in it because the database couldn’t create any more!
Once the UNDOTBS1 tablespace was dropped, new undo segments could be created in other undo tablespaces, and the ORA-01552 was avoided.
I suggested that Oracle Support change the careless recommendations in Doc 1580182.1. At the time of writing, Doc 1580182.1 now says:
alter system set "_rollback_segment_count"=1000 scope=spfile;