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

[JeffMachamer]

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

[JeffMachamer]

Quick note.  This table was not in fact empty, so I couldn't just unregister and reregister.  However adding the missing fields through sql allowed me to get the copy working.  (Export to xml still just crashes ArcCatalog, but Copy worked fine, which is enough for me for now.)

Doesn't explain how anything happened, but allows me to continue working forward.