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.

답변 없음

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인

관련 콘텐츠