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.
Peter patched a system from 19.10.0 to 19.11.0. This database has undergone an upgrade from 184.108.40.206 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 220.127.116.11.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
glad you are here Mike to help customers out, on support you don’t have to count a lot.
Imagine what happens when a “regular” unknown end user created such a call. No way that Oracle support will further assist you. Even customers which have paid millions for support are challenged. Is a customer running Rdbms on a Windows platform : then you receive an extra hurdle as a bonus.
I really disagree. I know the frustration with Support – and we in our team try our best to assist whenever time allows. Especially in cases where it is upgrade/patching related as here. As often, it depends who receives your SR. But trust me, if you are working in Support for a longer while, you do this because you want to help people. Otherwise you won’t survive in this hard job longer than for a few weeks.
We’ve been fighting this problem with SDO patch on Locator-only environments for a year now (the problem with SDO_RTREE_READ_ONLY is present since 19.8 DB RU). My understanding until now was, that the only solution supported by the Oracle is installation of the full Spatial component.
> And this has been glued into a MOS Note. Which – thanks to my colleagues in Spatial Development – has been removed already.
The same recommendation (install Spatial, which is the only supported version) is still present in 2740852.1 and 2752845.1
> The workaround is very simple and straight forward
We’ve been also using work-around with Oracle Locator re-installation as described in 2752845.1 (running catmdloc.sql and then utlrep.sql).
Thanks Alexey – do you have an SR you can share with me about the other issue?
Thank you very much for sharing this issue on your blog.
I have 2 questions related to this blog post :
1) Is executing prvtimd.plb also a solution for this issue with RU 19.10 which is also described in this blog post :
“Spatial Index Creation Fails with ORA-13249, ORA-06512 After Applying 18.104.22.168.210119DBRU (Doc ID 2752845.1)”
2) What about the installation of a “Spatial Patch Bundle”, documented in “What Is The Latest Spatial Patch Bundle for 12c and Higher Databases? (Doc ID 2514624.1)”, on an Oracle DB only having “Locator” installed without “SDO” ? Does this mean there might be other issues which might pop up that would require us to execute other SQL scripts that might not have been executed via datapatch ?
for 1) I think, yes.
For 2) I really don’t know as I lack experience. You may need to check with Oracle Support please.
Based on your advice I created a SR for Oracle Support where I asked above questions.
Here’s the answer I got from Oracle Support :
I was checking this with our internal Spatial experts and I got the below response:
The best option is for the customers to install Spatial as there is no real reason not to stay with Locator.
If they stay with Locator, there are limitations with automatic patching and other issues, so we strongly advise them to install Spatial.
For specific issues in (1), yes they have to manually do that steps. Only applicable for 19.10/19.11. The issue is fixed for 19.12.
So they apparently keep insisting on the fact to install Spatial instead of only Locator due to limitations with automatic patching and other issues. This does not really give much confidence for patching Oracle DB’s only having Locator installed.
I can’t control what Support says unfortunately. But I see your point.
First of all, the Locator is fully supported in Oracle 19c.
Hence, all patching issues with the Locator as we saw them already are nothing else than a bug.
That is not only my view on this but also confirmed by the development team owning Spatial and the Locator.
Of course, from the Oracle perspective it would be nice if you’d just installed Spatial.
But we explained internally that customers don’t want this in certain cases for several good reasons.
Hope this helps – thanks,