Oracle Database Migration for ESRI ArcGIS Geo-Database

Oracle database platform is a popular database to host ESRI ArcGIS Geo-Database, so I am going here to list the best approach to migrate your database for ESRI ArcGIS SDE:

From the source database, use expdp to fully export the Oracle database using SDE account (you need to temporary grant “DBA” role for such action):

expdp sde/XXXX dumpfile=GISDB_FULL%U.DMP directory=EXP_DIR FULL=Y PARALLEL=4 compression=all logfile=FULL_EXP_LOG.log

 

Now, in the target database start importing ONLY SDE schema first (SDE schema holds GIS data metadata and management scripts so its very important that this schema gets imported successfully):

impdp sde/XXXX dumpfile=GISDB_FULL%U.DMP directory=EXP_DIR SCHEMAS=SDE PARALLEL=4 logfile=SDE_IMP.log

After the import, check the log file and make sure that all objects were imported successfully…..you can compare the number of SDE schema objects by executing the following query in both source & target database:

SQL> select count(*) from dba_objects where OWNER=’SDE’;

In my case some objects were not created successfully !

One failed object is indextype ST_SPATIAL_INDEX , to view the definition of the index type from the source database:

SQL> set long 100000

SQL> set head off

SQL> set echo off

SQL> set pagesize 0

SQL> set verify off

SQL> set feedback off

SQL> SELECT dbms_metadata.get_ddl(‘INDEXTYPE’,’ST_SPATIAL_INDEX’,’SDE’) FROM dual;

 

I will execute the definition of the indextype:

 

CREATE OR REPLACE INDEXTYPE “SDE”.”ST_SPATIAL_INDEX” FOR

“SDE”.”ST_BUFFER_INTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, NUMBER

),

“SDE”.”ST_CROSSES” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, NUMBER, NUMBER, NUMBER, NUMBER)

,

“SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, NUMBER, NUMBER, NUMBER, NUMBER,

VARCHAR2),

“SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, VARCHAR2),

 

“SDE”.”ST_EQUALS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_INTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_ORDERINGEQUALS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_OVERLAPS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_RELATE” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, VARCHAR2),

“SDE”.”ST_TOUCHES” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_WITHIN” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”)

USING “SDE”.”ST_DOMAIN_METHODS”

WITH LOCAL RANGE PARTITION;

 

The following error will be thrown:

ORA-29829: implementation type does not exist

After digging….the following two “types” are missing:

ST_DOMAIN_METHODS

ST_DOMAIN_STATS

 

Take their SQL definition from the source database and create them in the destination, and try re-creating the indextype ST_SPATIAL_INDEX..it should be created successfully.

 

Now after ensuring SDE schema is fully mimicking the source database, start importing other schemas (user geodatabase schemas) and test it using ArcGIS Desktop.

Underlying DBMS error [Layer not found] [Error executing PL/SQL Block db_stgeom_create_indexORA-29833: indextype does not exist]

while upgrading ArcSDE Geodatabase from version 9.3.1 SP2 to version 10 SP5, the below error was faced:

Underlying DBMS error [Layer not found] [Error executing PL/SQL Block db_stgeom_create_indexORA-29833: indextype does not exist]

This error indicates that you are missing an indextype in your ArcSDE Geodatabase in Oracle, the indextype name is: “ST_SPATIAL_INDEX”

when attempting to execute the following Sql Query to create the index type:

*******************************************************************************************************

CREATE OR REPLACE INDEXTYPE “SDE”.”ST_SPATIAL_INDEX” FOR “SDE”.”ST_BUFFER_INTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, NUMBER), “SDE”.”ST_CROSSES” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”), “SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),  “SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, VARCHAR2),”SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, NUMBER, NUMBER, NUMBER, NUMBER, VARCHAR2),  “SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, NUMBER, NUMBER, NUMBER, NUMBER), “SDE”.”ST_EQUALS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”), “SDE”.”ST_INTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”), “SDE”.”ST_ORDERINGEQUALS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),  “SDE”.”ST_OVERLAPS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”), “SDE”.”ST_RELATE” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, VARCHAR2), “SDE”.”ST_TOUCHES” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),”SDE”.”ST_WITHIN” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”)USING “SDE”.”ST_DOMAIN_METHODS” WITH LOCAL RANGE PARTITION;

**********************************************************************************************************

the following error was received:

ORA-29830: operator does not exist
ORA-29830: operator does not exist

This error indicates that for some reason there are missing operators in your ArcSDE geodatabase, you need to create them.

but, first you need to query the complete list of operators from another ArcSDE geodatabase that is intact and healthy, using the query: select * from user_operators;

Image

compare the list of operators where the ArcSDE geodatabase that has the upgrade issue, and create the missing ones.

In order to extract the sql definition of the operator use the following query as an example:

select dbms_metadata.get_ddl(‘OPERATOR’,’ST_WITHIN’) from dual;

after creating all missing operators, try executing the previous sql query mentioned: “CREATE OR REPLACE INDEXTYPE “SDE”.”ST_SPATIAL_INDEX

then use the following sql, to check indextype was created:

select * from user_indextypes;

ST_SPATIAL_INDEX indextype
ST_SPATIAL_INDEX indextype

Re-Run the Upgrade, and it will be successful.

Query Reference:

select * from user_operators;

select * from user_indextypes;

select dbms_metadata.get_ddl(‘INDEXTYPE’,’ST_SPATIAL_INDEX’) from dual;

select dbms_metadata.get_ddl(‘OPERATOR’,’ST_WITHIN’) from dual;

If this is not the case you are facing….but facing the same error then try the following:

 

SQL> GRANT EXECUTE ON sde.st_spatial_index TO SCHEMA_NAME;

OR

SQL> GRANT EXECUTE ON sde.st_spatial_index TO PUBLIC;

AND

SQL> GRANT CREATE INDEXTYPE TO SCHEMA_NAME;