After patching, Spatial Index creation fails with ORA-13249

Thanks again to Peter Lehmann from T-Systems for highlighting this issue to me. After patching, Spatial Index creation fails with ORA-13249. And Peter’s customer was quite worried. But see what may have caused this, and how we fixed it.

Photo by Bill Oxford on Unsplash

What happened?

Peter patched a system from 19.10.0 to 19.11.0. This database has undergone an upgrade from 12.2.0.1 to 19.7.0 before, then got patched to 19.9.0 before. The database has the Oracle Locator only but no Spatial installed. The patching including datapatch worked fine. All seemed to be good. Until the customer tried to create a new table with a spatial index on it.

CREATE INDEX cola_spatial_idx_cs ON cola_markets_cs(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;

CREATE INDEX cola_spatial_idx_cs
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13249: Error executing stmt: INSERT INTO MDSYS.SDO_INDEX_METADATA_TABLE
(SDO_INDEX_OWNER, SDO_INDEX_TYPE, SDO_LEVEL, SDO_NUMTILES, SDO_MAXLEVEL,
SDO_COMMIT_INTERVAL, SDO_INDEX_TABLE, SDO_INDEX_NAME, SDO_INDEX_PRIMARY,
SDO_TSNAME, SDO_COLUMN_NAME, SDO_RTREE_HEIGHT, SDO_RTREE_NUM_NODES,
SDO_RTREE_DIMENSIONALITY, SDO_RTREE_FANOUT, SDO_RTREE_ROOT, SDO_RTREE_SEQ_NAME,
SDO_INDEX_DIMS, SDO_LAYER_GTYPE, SDO_RTREE_PCTFREE, SDO_PARTITIONED ...

Now they checked the same code in the previous release (19.10.0), and there all went fine.

Peter did a search on MOS and found potential “solutions” such as MOS Note: 2752845.1 – Spatial Index Creation Fails with ORA-13249, ORA-06512 After Applying 19.10.0.0.210119DBRU. But when I checked the underlying bug entry it looked to me that this is not exactly the same issue.

In addition, Peter was quite unhappy with the recommended solution:

Install Oracle Spatial which will run the new/upgraded scripts and will thus recreate the MDSYS.SDO_INDEX_METADATA_TABLE with the new structure.

This doesn’t look very appealing when you operate a lot of Multitenant environments for end-customers.

Even worse, the PDBs where this has happened, didn’t open unrestricted anymore.

What did Oracle Support say?

Of course, Peter did open an SR. And this lead to what I call a “30 second SR” where the analyst tells you “This is not supported. Goodbye.”. It wasn’t that short of course, but the proposed solution was basically the same as in the note: Install Spatial, and all is fine. Spatial is license-free since December 2019. Of course, the fact that installing an option into hundreds of PDBs is resource-intense work but in addition requires the agreement of the end customer and may cause extra maintenance work. So it is not always desired to have more options in a database.

And as you know as blog readers, more options make your upgrades take longer.

But it got better. The Support Engineer told Peter that he won’t do any further work unless Peter installs Spatial. And this has been glued into a MOS Note. Which – thanks to my colleagues in Spatial Development – has been removed already.

What did Development say?

And thanks to my colleagues in the Spatial Development team, we quickly could diagnose the root cause for this issue. When a Spatial fix gets rolled into an RU, then datapatch checks whether SDO is present in REGISTRY$. If it isn’t, datapatch does not run the script. So far so good.

But when you have the Locator only, and no SDO (Spatial Data Option) installed, it has no entry in REGISTRY$. So datapatch does not know that it still needs to run this particular script. Of course, this is not good and an issue. But currently the responsible parties are discussing how to fix this in the future.

So in our case, a binary patch got applied to the new home. But the SQL portion didn’t get run, and hence the database failed to create this index.

What is the workaround?

The workaround is very simple and straight forward – run:

alter session set current_schema=MDSYS;
@@?/md/admin/prvtimd.plb

in the PDBs or the database where this has happened. Then you need to recompile with utlrp.sql – and all will work fine again.

I suspect (but haven’t tested this by myself yet) that you need to run this also in CDB$ROOT and PDB$SEED for future new PDBs to avoid any issues. So catcon.pl is the vehicle to make sure you execute the script in the system containers as well.

Further Links and Information

• Spatial is license-free since December 2019
• How long does my upgrade take … revisited
• Workaround for sdoloadj.sql errors after patching

–Mike