Not AUTO segment space management error during upgrade to 11g

I must admit my only thought about the statements used to check SYSAUX attributes in the catupstr (CATalog UPgrade STaRt called when upgrading a database) script was that they could be more elegant. Something like using an anonymous block to check and raise the error if necessary. Never realized it could fail, as Oracle states clearly and concisely how the SYSAUX tablespace must be created: local with segment space management auto.

These are the checking statements related to SYSAUX performed by the script:

SELECT TO_NUMBER('No SYSAUX tablespace') FROM dual
WHERE 'SYSAUX' NOT IN (SELECT name from ts$);

SELECT TO_NUMBER('Not ONLINE for READ/WRITE') from ts$
WHERE name='SYSAUX' AND online$ !=1;

SELECT TO_NUMBER ('Not PERMANENT') from ts$
WHERE name='SYSAUX' AND
(contents$ !=0 or (contents$ = 0 AND bitand(flags, 16)= 16));

SELECT TO_NUMBER ('Not LOCAL extent management') from ts$
WHERE name='SYSAUX' AND bitmapped = 0;

SELECT TO_NUMBER ('Not AUTO segment space management') from ts$
WHERE name='SYSAUX' AND bitand(flags,32) != 32;

While trying to upgrade a database to 11g it failed as the SYSAUX tablespace was created with manual segment space management. Well, there is not much to do other than recreate the database. This is an old database, created with 9i and upgraded manually to 10g. But there is a mystery: in order to upgrade to 10g the SYSAUX creation is mandatory, and the 10g upgrade script performs the same checking as 11g. So, how come the database was upgraded to 10g? Answer: creativity!

The DBA running the upgrade to 10g just commented the AUTO segment space management checking. My best guess for this situation is:

- the SYSAUX was created in 9i with manual space management
- the database was started for upgrade with the compatible parameter set to 10
- when the DBA started the catupgrd script it just failed, as the SYSAUX did not meet the requirements
- At this point the DBA was stuck: it was not possible to start the database with 9i to recreate the SYSAUX, neither to proceed with the upgrade to 10g.

Instead of restoring the database backup and scheduling a new outage for the upgrade, the DBA decided for the creative path – this is the kind of not appreciated innovation :o)

About The COMPATIBLE Initialization Parameter