MDSYS schema problems after snapshot restoration of an Oracle RDS instance

0

Restored database snapshot to perform some testing. For reasons unknown, after restoring the snapshot the SPATIAL option had to be removed/reapplied to enable Spatial features as all the Spatial schemas were missing. Any table containing a field of type SDO_GEOMETRY is invalid across all schemas, as well as a number in the MDSYS and PUBLIC. Querying a table yields an error e.g.

ORA-04063: table "EXAMPLE_SCHEMA.EXAMPLE_TABLE" has errors 04063. 00000 - "%s has errors" *Cause: Attempt to execute a stored procedure or use a view that has errors. For stored procedures, the problem could be syntax errors or references to other, non-existent procedures. For views, the problem could be a reference in the view's defining query to a non-existent table. Can also be a table which has references to non-existent or inaccessible types. *Action: Fix the errors and/or create referenced objects as necessary. Error at Line: 1 Column: 34

MDSYS.SDO_GEOMETRY type is in an invalid state, but compiling yields this error:

Error(1,1): ORA-02303: can't drop or replace a type with type or table dependents. SQLDev advises setting "Drop Type Force" preference

I don't understand why the SPATIAL schemas aren't applied immediately upon restoration. You literally have to remove the option from the option group and re-add it to have them reinstalled. But, more importantly, why are all those objects invalid? The result is an unusable database.

This doesn't exactly inspire confidence in RDS snapshots.

preguntada hace 6 meses329 visualizaciones
No hay respuestas

No has iniciado sesión. Iniciar sesión para publicar una respuesta.

Una buena respuesta responde claramente a la pregunta, proporciona comentarios constructivos y fomenta el crecimiento profesional en la persona que hace la pregunta.

Pautas para responder preguntas