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.

ora-00600, ora-44303 and ora-44775 after cloning a remote pdb

These days we had an incident with a 19.6 Oracle database on MS Windows. We have cloned a PDB like we have done it a lot times before using a database link. I already have written some blog posts about that (and you can download the scripts there). 

The "create pluggable database XXXY from XXXX@clone_source refresh mode manual;" succeded, but when we tried to open the pdb, it failed with

ERROR at line 1:
ORA-44303: service name exists
ORA-44775: Pluggable database service cannot be created

The crazy thing is, this is the first time we tried to clone the database to this fresh and empty CDB. 

The alert.log had an additional entry:

ORA-00600: internal error code, arguments: [kpdbIdToTenantKey1], [4099], [], [], [], [], [], [], [], [], [], []

While querying sys.cdb_service$ we have analyzed that the service name was in lowercase letters, while all other service names can be found there in uppercase. 

With this information we have found a bug at MOS - there are different operating system where you can find a patch for - unfortunately not for MS Windows:

Patch 31143870: CORE DUMP DURING DBMS_SERVICE.START_SERVICE USING CASE SENSITIVE NAME FOR PDB

It seems that this is fixed not so long ago as it can be downloaded as one-time fix for 19.10.0.0.0 DBRU (which was released 2 weeks ago).

If you run into this, please try first to install the patch - we weren't able to test it, but it seems it could fix this problem also. 

If this does not fix this problem or if you are on a platform (like MS Windows) where the patch isn't available yet, you may try the workaround that helped us:

delete the wrong (lowercase) service name from sys.cdb_service$ (and if it exists also from sys.service$). Don't forget to commit. 

Afterwards unplug, drop and plugin the PDB again - for us this was working well. The PDB was cloned successfully and we were able to open it without any error. 

Comment: Feb., 22nd, 2021. The 19.10 Bundle is now available also for MS Windows.