Upgrade from 9.3.1 to 10.1 on Oracle : Coordinates out of bounds error

1974
10
08-22-2013 05:00 AM
JeffMachamer
New Contributor III
We recently upgraded a client running 9.3.1 on Oracle 11g to a 10.1 SP1. This involved a new Oracle server for which their DBA loaded with a backup from the old database.  It appears to have all been transferred successfully as we did not see any errors after this process was done.  (Note, we have limited access to the oracle database, only logins to an sde user and the schema owner so we are unable to do much trouble shooting on the database side without getting their DBA involved.)

Using ArcCatalog we performed the Upgrade Geodatabase function on the connection successfully.  (After validation errors for permissions and getting the DBA to grant the appropriate permissions.)

After this was complete we have issues with certain feature classes in ArcMap.  When we load certain layers we receive the drawing error:  Specified coordinate exceeds the valid coordinate range.  Research on this suggests that it is due to the feature class being created incorrectly and the only way to fix it is to reload the feature class.  Since it was correct before the upgrade my assumption was that the upgrade did something incorrect to the spatial references...

Before doing that we tried rebuilding the spatial indexes but couldn't either due to a generate DBMS error (no error number) on some feature classes and error stating our SRIDs did not match on others.   Underlying DBMS error [ORA-29875: failed in the execution of the ODCIINDEXINSERT routine
ORA-20085: Insert Spatial Reference SRID 3857 does not match GEOSPATIAL.A95.SHAPE registered Spatial Reference SRID 3
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 1986
] [GEOSPATIAL.CRO_NOTE_LINE]

I looked at the spatial reference in ArcCatalog for the feature class and it said SRID of 3.

That led me to looking at the sde schema tables directly where I found some really odd stuff.   I found a mismatch of SRID 3 and 3857 in various tables.  It's hard to explain since it also created the ST_* tables and some of the tables seem to reference it while others don't.  But bottom line is that in one case I found the Layers table appeared to be using a different SRID than the geometry_columns table.  For this one feature class I updated the Layers table to the SRID I believed it should have been and it fixed the problem for this feature class.  (Don't yell at me for modifying the sde tables directly...  even though I have yelled at coworkers for doing the same!)

Unfortunately the same fix didn't fix the other feature classes.  The fix I had to use for them was to backup the data from the feature classes, delete the feature class, recreated it, then reload it.  After that it all appears to work correctly.

The new feature classes show an SRID of 3857 in ArcCatalog and the old ones still show SRID of 3, so I'm a bit worried about the fact that they are in the same feature dataset but show up differently.  I feel this is going to come back and bite me down the road.

So I guess my questions from all of this are:  Is there a known issue with the 10.1 Upgrade?  Is there something we did wrong concerning the ST_ configuration?  Is there something we can do to identify and fix the SRID mismatches?  Seems to me that I could easily write a SQL script to identify where there appear to be problems, but I don't have a full understanding of why some times it uses the SRID from the spatial reference table and sometimes it uses the AUTH_SRID instead...

Any insite into what may be going on here and how to resolve it would be greatly appreciated.

Jeff Machamer
3-GIS
0 Kudos
10 Replies
VinceAngelo
Esri Esteemed Contributor
If you look deeper, you'll find that the '3' and '3857' SRIDs are different, but
this is the sort of issue that's easier to work out with Tech Support, rather than
going through dozens of "what does this command return" iterations in these
Forums.

- V
0 Kudos
JeffMachamer
New Contributor III
vangelo, I agree that it would be easier through Tech Support. I've asked our support team to raise an issue but haven't heard back yet.

I was hoping someone here would be able to basically say that they too had seen a similar problem and the answer is to run some cleanup routine or something like that.
0 Kudos
VinceAngelo
Esri Esteemed Contributor
I've never heard of this problem, but I've only been using SDE 18-19 years (since v1.2.1).
Tech Support is way more likely to be familiar with something like this.

- V
0 Kudos
MandarPurohit
Esri Contributor
Hi  jmachamer,

Has 'migrate storage' tool been used after (or before) the geodatabase upgrade?

A message similar to "Insert Spatial Reference SRID 3857 does not match GEOSPATIAL.A95.SHAPE registered Spatial Reference SRID 3" was reported with NIM089682.

Regards,
Mandar
0 Kudos
JeffMachamer
New Contributor III
No, the migrate storage tool has not been used.
0 Kudos
JeffMachamer
New Contributor III
mvpurohit,

After looking at the error though it does seem like the same symptom.  I'll get our Support guy to try and contact Esri about this bug and see what the fix was for it in this case and see if it is applicable to ours.

Thanks for the info.
0 Kudos
JeffMachamer
New Contributor III
Okay, now I'm really confused.  One way we know that works for fixing this is to export the data out and re-import it in.  I was going to export an entire version out just to make sure that would work when I had an error during export.

[ORA-00904: "A"."DUCT_MATERIAL":invalid identifier][GEOSPATIAL.SPAN]

Well, the base table looks fine but when I look at the A table I see that there are a ton of fields missing from it...  I don't even know how to go about explaining how that is even possible.    (Well, except for someone modifiying the schema through oracle rather than ArcCatalog, but I hope everyone knows not to do that...)

Jeff
0 Kudos
MarcoBoeringa
MVP Regular Contributor
Okay, now I'm really confused.  One way we know that works for fixing this is to export the data out and re-import it in.  I was going to export an entire version out just to make sure that would work when I had an error during export.

[ORA-00904: "A"."DUCT_MATERIAL":invalid identifier][GEOSPATIAL.SPAN]

Well, the base table looks fine but when I look at the A table I see that there are a ton of fields missing from it...  I don't even know how to go about explaining how that is even possible.    (Well, except for someone modifiying the schema through oracle rather than ArcCatalog, but I hope everyone knows not to do that...)

Jeff


If it is an issue, it may be unrelated. But are you absolutely sure you are looking at the right A table? You need to look in the:

SDE_table_registry

table and find out what the "registration_id" is for your layer / Feature Class, in order to find the correct "Ax" table. A and D tables use the registration_ID as part of the table name, not the layer_id from the SDE_layers table.

Anyway, the error by itself indicates an issue and missing fields looking at the error message, so you probably do have missing fields in the A table.
0 Kudos
JeffMachamer
New Contributor III
Yes, I was using the registration_id to determine the A table to use.  I even checked a few other tables this way to verify I was calculating the A table number correctly since it had been a while since I had to do that.  (These other tables were correct.) 

Luckily this feature class that is having this problem is empty, so I should be able to unregister/re-register to fix the issue.

I still really want to find out how this happened.
0 Kudos