Advice required upgrading Oracle with ArcSDE

Question

Problem: We want to upgrade our Oracle 11g + ArcSDE 10.1.0 to Oracle 12c + ArcSDE 10.4.1

Steps tried so far:

* Created a new virtual server
* Exported a full database backup from current Oracle 11g 
* installed Oracle 12c to new server
* from an ArcGIS 10.4.1 client used python to create ST_GEOMETRY as described here: http://desktop.arcgis.com/en/arcmap/10.3/tools/data-management-toolbox/create-spatial-type.htm
* imported full backup from oracle 12c
* from ArcGIS 10.4.1 client connected to new server using TNSNAMES.ora to define connection 
* connected to geodatabase using ArcCatalog unfortunately all spatial tables were identified by a default icon and not the relevant spatial type icon. Right clicking on object would update icon but if loaded into ArcGIS it would put up a “New Query Layer” form and ask the user to identify a Unique Identified Field, then calculate extent. 
* Raster datatype not recognised at all, so installed ST_RASTER type and reloaded raster files from backup, unfortunately they are still not recognised as rasters. Direct copy/pasting the rasters from the original geodatabase to the new one using ArcGIS client works but this would need to be done by every user/schema that owns rasters to maintain ownership.
* Noticed the new SDE.LAYERS table is empty except for the table directly loaded using copy/paste.

Can anyone advise what we are doing wrong and what steps we are missing.

Thanks in advance

* *

Anonymous User · Answer

Hi all

I think that point 9. Noticed the new SDE.LAYERS table is empty except for the table directly loaded using copy/paste. and you will need to identify why that table (or maybe other tables in the SDE schema) has been created but not populated during the Oracle Import/Export process...

How are you migrating the data from one database to the new one? Imp/exp? Backup/restore? Do you have a log of the process? Any error or warning during this process?

Regards

Jesús

CarolineKeay · Answer

The restore of the SDE user was done as part of the backup import. Then the upgrade geodatabase was done from the ArcGIS client on the new DB (Sorry, I think we missed listing that in the steps…). That would have been done between 6 and 7.

So you are saying if we are enterprise geodatabase (which we believe we are) we don’t need ST_Geometry? (or ST_Raster?). We don't have Oracle Spatial, does that make a difference? The original server had the ArcSDE  application installed on it, but I believe that is no longer necessary on ArcGIS 10.4.

We believe the upgrade process has been corrupted somewhere so are going to try again from scratch.

It would be good to get a set of documented steps to go from existing Oracle/GIS to new Oracle/GIS. I wonder if we need to upgrade the geodatabase on the existing DB first? Will that mess with our ArcGIS clients who are using older versions of ArcGIS?

Asrujit_SenGupta · Answer

Seems like the Oracle Full Database Backup/Restore process may not have been done correctly. It is being treated as a Simple database.

(Check if R-clicking on the connection file in ArcCatalog gives you the 'Enable Geodatabase' option)

Also Step-4 is not necessary. Its for simple databases and not Enterprise Geodatabase.

The Create Spatial Type tool adds or upgrades the ST_Geometry SQL type, subtypes, and functions to an Oracle or PostgreSQL database. This allows you to use the ST_Geometry SQL type to store geometries in a database that does not contain a geodatabase. This tool also can be used to upgrade an existing ST_Geometry installation in an Oracle or PostgreSQL database.