ORA-00701: object necessary for warmstarting database cannot be altered

After upgrading database from 11.2.0.3 to 12.2.0.1, most of the statements were failing with below error:

Let me check the indexes for which number of extents has reached max_extents value.

So it’s just I_OBJ5 index. Now let’s increase the value of MAXEXTENTS for this index.

Data Dictionary objects may be not be allowed to be altered by a DDL. Data Dictionary tables stored in table sys.bootstrap$ are not allowed to be altered with a DDL (drop, alter index, etc) as error ORA-701 is produced.

Since this index is part of sys.bootstrap$ and can not be modified, following are the 2 options suggested by Oracle Support:

A) Supported option is to recreate the database using expdp/create/impdp with or without TTS option (TTS would take less time due to no physical import of data/creation of indexes).

B) Unsupported option does consist of an update of the DD (DataDictionary) which requires careful testing. This is an undocumented/unsupported option which does consist of an update of the DD (DataDictionary).

In my case the database size is around 20TB, so using option-A will need a big downtime, so I have decided to go with option-B.

Note: Following steps should not be used without Oracle Support consultation and make sure to take a full backup before running these steps.

a. Get the header FILE and BLOCK of the segment
(Note that you need the RELATIVE FILE# not the absolute FILE#)

b. Patch the SEG$ entry for this segment to set a higher MAXEXTENTS
value
(Note: We are constraining SEG$ updates to TS#=0 for the
relevant relative FILE#)

STEPS:
1. Shutdown the database (cleanly)

2. Run these steps taking great care to reference the correct
FILE# and BLOCK# (these will be in TS#=0)

This should return one and only one row. IF NOT ROLLBACK NOW AND ABORT. If it returns ONE row then commit as below

3. Startup again after the above abort

4. Check for MAX_EXTENTS

This should show MAX_EXTENTS=10000

5. Shutdown cleanly and take another fresh backup.

6. Check again if there are any index which reached max_extents

And now I was no longer recieving the ORA-01632 errors.

Please follow and like us:
0

Leave a Reply

Your email address will not be published. Required fields are marked *