The Puzzle

After switching to a new undo tablespace, transactions would fail with ORA-01552.

The Background

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.

The Solution

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%';

--- --------
break on report
compute sum of segments on report
select max(US#),count(1) segments,ts# from sys.undo$ group by 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;

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s