GIS/spatial throws errors on update/delete with base "ORA-00904: SDO_RTREE_READ_ONLY: Invalid identifier"

After migrating a spatial / gis database from standard edition 12.2 to 19c we run into a strange error. The product (in our case it was "Luxdata", but it could be any GIS/Spatial application) stopped working with the new database and throwed a number of errors. The database itself was set up like any other Standard Edition database before (as 19.6) and upgraded to a newer version (in our case Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.10.0.0.0.). The database was also migrated to pdb, so it was plugged into as a new container. Operating system is Linux, but the error can happen at every OS.

The application was only reconfigured to use the new database version, but updates to different tables all got an error in "commit method" - leading to a problem with the "ORCDINDEX".

Figuring out the problem we have seen, that the "ORA-00904: SDO_RTREE_READ_ONLY: Invalid identifier" was raised while updating or deleting rows.

The error stack we found is:

ORA-29400: data cartridge error
ORA-00904: "SDO_RTREE_READ_ONLY": invalid identifier
ORA-00904: ORA-00904: "SDO_RTREE_READ_ONLY": invalid identifier: invalid identifier
ORA-06512: at "MDSYS.SDO_RTREE_ADMIN", line 173
ORA-06512: at line 1

Searching a while, we were sure that this happens with any newer Oracle database Standard Edition version, and, as we concluded, it also can be found at Oracle database Enterprise Edition and Oracle Database Cloud Services.

What happened? As you may know, Oracle has changed its Spatial licence to be free of cost (it was an Enterprise Edition option before) end of 2019. Now it seems with Oracle database version 19.8 (and later), Oracle has changed the installation mode. At the "spatial oracle blog" (where the licence change was released) it was mentionend that one must maybe install an update to use the free spatial licence at a later point in time.

After searching at Oracle Support with different criterias we stumbled upon Spatial Query Fails with ORA-00904: "SDO_RTREE_READ_ONLY": invalid identifier (Doc ID 2740852.1) where Oracle tells us "With the inclusion of Spatial in the database license, Oracle Locator only installations are no longer supported." The solution is "install spatial".

What you have to do now is to install spatial (mdinst.sql) completely new into the CDB, the PDB$SEED and (all of) your application pdb(s). This is not very complicated and is done in half an hour to an hour. Unfortunately one has to change the session to be recognized as "Oracle script" session - this is, why I don't post the solution directly herein. Please follow the note I have linked above. You may run into this problem not only while upgrading from 12c, but also if you do have installed e.g. a 19.3 database and upgrade it later on to 19.8 or higher.

Beside of the installation, don't forget to set the pfile parameter SPATIAL_VECTOR_ACCELERATION to true as it will speed up your spatial installation.