Skip to content

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.

1 Answer
0

I tested your scenario with RDS Oracle 19c SE2 with the database option group with Spatial enabled. I ran the following query after a a snapshot restore with the same option group with Spatial enabled. Snapshot restore worked without issues. I had to pick the correct option group with Spatial enabled this was not the default. select count(*), owner, status from dba_objects where owner = 'MDSYS' group by owner, status;

count(*) owner status


4429 MDSYS VALID

select count(*), owner, status from dba_objects where status <> 'VALID' group by owner, status;

No rows returned

I tested the snapshot restore without picking the option group with Spatial enabled. I still had no invalid objects and MDSYS schema was missing. See the following queries:

select count(*), owner, status from dba_objects where owner = 'MDSYS' group by owner, status;

No rows returned

select count(*), owner, status from dba_objects where status <> 'VALID' group by owner, status;

No rows returned

Both of these tests is what I would expect from snapshot restore. I was not able to reproduce your scenario where snapshot restore did not work.

AWS
answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.