topic Re: Best way to move versioned ST_GEOMETRY FCs between Oracle 11gR2 instances? in Data Management Questions

Best way to move versioned ST_GEOMETRY FCs between Oracle 11gR2 instances?

danan — Sat, 11 Dec 2021 16:20:56 GMT

What is the current recommended way to move versioned feature classses having ST_GEOMETRY columns from one 11gR2 Oracle instance to another? Is this supported at all with datapump? The primary use case: developers want occassional data refreshes from production spatial data on another instance at the same Oracle/SDE version. Another use case might be for recovery.

We have tried using datapump and had errors like the following for any FC with an ST_GEOMETRY column:

ORA-31693: Table data object "<schema here>"."<feature class here>" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-00600: internal error code, arguments: [kpudpxcs_ctxConvertStream_ref_1], [SYS_TYPEID("SHAPE")], [], [], [], [], [], [], [], [], [], []

We've used expdp and impdp in various ways and always get the same type of error above.
We've tried:

* Exported SDE and all data owner schemas in one expdp statement; then used impdp to import all schemas simultaneously with that dump file
* Exported SDE and data owner schemas with separate expdp statements; then imported the SDE first and data owner schemas afterwards

In a related thread from a year ago (where I'd asked about using datapump to go from a 10g to 11g instance), some suggested that datapump wasn't up to this sort of task; that there were too many dependencies like PUBLIC synonyms and various grants to PUBLIC, etc. Does that hold true for the 11.2.0.2 incarnation of datapump working with ArcSDE 9.3.1 SP2?

We are using:
Oracle version: 11.2.0.2 on RHEL 5.4
SDE version: 9.3.1 SP2

What options do we have considering ST_GEOMETRY is a "user defined" data type? It makes sense to me that the SDE schema should be imported first to get ST_GEOMETRY created; presuming the type lives in the SDE schema as it seems to. Here is an object count from the Oracle source instance:

select owner, object_type, count(*) from dba_objects where owner = 'SDE' and object_type like '%TYPE%' group by owner, object_type order by object_type;

OWNER                          OBJECT_TYPE           COUNT(*)
------------------------------ ------------------- ----------
SDE                            INDEXTYPE                    1
SDE                            TYPE                        39
SDE                            TYPE BODY                   24

After importing, the Oracle target instance has only 31 TYPEs under the SDE schema. It is missing the following 8. However, all %TYPE% objects that *did* import have a status of VALID. So again, here are objects that are valid on the source instance but missing on the target instance. Not sure datapump even complained about these. I noticed they all have subobject names:

OWNER                          OBJECT_TYPE         OBJECT_NAME                    SUBOBJECT_NAME         STATUS
------------------------------ ------------------- ------------------------------ -----------------------------
SDE                            TYPE                ST_DOMAIN_METHODS              $VSN_4                 VALID
SDE                            TYPE                ST_DOMAIN_METHODS              $VSN_3                 VALID
SDE                            TYPE                ST_DOMAIN_METHODS              $VSN_2                 VALID
SDE                            TYPE                ST_DOMAIN_METHODS              $VSN_5                 VALID
SDE                            TYPE                ST_DOMAIN_METHODS              $VSN_1                 VALID
SDE                            TYPE                ST_DOMAIN_STATS                $VSN_3                 VALID
SDE                            TYPE                ST_DOMAIN_STATS                $VSN_2                 VALID
SDE                            TYPE                ST_DOMAIN_STATS                $VSN_1                 VALID

UPDATE:
Would doing a datapump import of the SDE schema first (which has been error-free for us), creating public synonyms as recommended by the KB article below, *then* importing the data owner schemas be a good workaround? Or are we looking at having to use the full option in datapump to make things work?

Exporting and importing an Oracle SDE schema does not include public synonyms
http://support.esri.com/en/knowledgebase/techarticles/detail/34328

Re: Best way to move versioned ST_GEOMETRY FCs between Oracle 11gR2 instances?

JaneLamont — Fri, 09 Mar 2012 21:46:35 GMT

Hi Dana,
I have encountered the exact problem and symptoms as you have described, with my Oracle 11.2.0.3 and SDE 9.3. How to move the data between instances?
We have also encountered in 3 databases that Data Pump broke. Rebuilding the DBMS_DATAPUMP PL/SQL package has not been successful. Oracle Support has designated it a bug, 13829814, with their development.
Through testing or rebuilding instances and bringing in the SDE data that the SDE data breaks Data Pump. Then yesterday I encountered the same errors as yours.
The previous thread of using export/import with the grants to PUBLIC is being tested as I write. Please keep this thread updated with any new information as I will also.
Thanks,
Jane

Re: Best way to move versioned ST_GEOMETRY FCs between Oracle 11gR2 instances?

danan — Sat, 10 Mar 2012 15:15:32 GMT

Hi Dana,
I have encountered the exact problem and symptoms as you have described, with my Oracle 11.2.0.3 and SDE 9.3. How to move the data between instances?
We have also encountered in 3 databases that Data Pump broke. Rebuilding the DBMS_DATAPUMP PL/SQL package has not been successful. Oracle Support has designated it a bug, 13829814, with their development.
Through testing or rebuilding instances and bringing in the SDE data that the SDE data breaks Data Pump. Then yesterday I encountered the same errors as yours.
The previous thread of using export/import with the grants to PUBLIC is being tested as I write. Please keep this thread updated with any new information as I will also.
Thanks,
Jane

Thanks Jane. Sorry for your troubles and hope you succeed. I'll add to the thread if we have any success. Hoping others out there can comment since data migration is a fundamental function. But not something I've seen definitively addressed where ST_GEOMETRY is concerned.

I suppose one solution (compromise) might be to jettison ST_GEOMETRY completely and convert to SDE_LOB. Another might be to use RMAN to clone an instance. I wonder if datapump failing is specific to Esri's ST_GEOMETRY UDT (User Defined Type). Or if the datapump problem exists for migrating UDTs from a variety of vendors. Based on some other issues we've had with ST_GEOMETRY, I wonder if it's best to switch to SDE_LOB unless ST_GEOMETRY spatial *functions* are an absolute business requirement.

Re: Best way to move versioned ST_GEOMETRY FCs between Oracle 11gR2 instances?

VinceAngelo — Sat, 10 Mar 2012 19:44:30 GMT

SDE.ST_GEOMETRY has consistently better performance than SDE_LOB. You should
evaluate the cost of moving to LOBs before deciding to do so.

- V

Re: Best way to move versioned ST_GEOMETRY FCs between Oracle 11gR2 instances?

danan — Mon, 12 Mar 2012 11:42:42 GMT

SDE.ST_GEOMETRY has consistently better performance than SDE_LOB. You should
evaluate the cost of moving to LOBs before deciding to do so.

- V

Thanks Vince. I have also heard the opposite. In principle ST_GEOMETRY should be consistently faster. But I don't know if that's the result for every customer on every sub-platform. Seems reasonable to test in one's own environment to see what is actually the case. That said, what to do about migrating feature classes with the ST_GEOMETRY User Defined Type? Is there a current Best Practice? Is datapump no longer recommended (if it ever was), etc? It's also been recommended to me to use RMAN to clone a DB instance as a means of moving ST_GEOMETRY-based data from instance to instance.

Dana

Re: Best way to move versioned ST_GEOMETRY FCs between Oracle 11gR2 instances?

JaneLamont — Mon, 12 Mar 2012 15:07:29 GMT

I can replicate your missing SDE types.
There is 1 missing subobject:
OBJECT_NAME SUBOBJECT_NAME STATUS
------------------------------ ------------------------------ ----------
ST_DOMAIN_METHODS $VSN_1 VALID

Re: Best way to move versioned ST_GEOMETRY FCs between Oracle 11gR2 instances?

danan — Tue, 13 Mar 2012 08:00:20 GMT

I can replicate your missing SDE types.
There is 1 missing subobject:
OBJECT_NAME SUBOBJECT_NAME STATUS
------------------------------ ------------------------------ ----------
ST_DOMAIN_METHODS $VSN_1 VALID

No progress here. Jane, are you using ST_GEOMETRY because it's the default spatial data type for SDE on Oracle, because you have a business requirement for spatial SQL functions, or another reason? For us, SQL functions were a "nice to have." Don't know that anyone is currently using them. And what's very much not nice to have: limited options for moving data around. I believe we also chose it because of a belief that ST_GEOMETRY being the default spatial data type means it would be the focus of future development.

Re: Best way to move versioned ST_GEOMETRY FCs between Oracle 11gR2 instances?

JaneLamont — Tue, 13 Mar 2012 17:10:40 GMT

We have come to the conclusion that our problems of Data PUmp not working are related to Oracle Note, Ora-21700 Object Does Not Exist Or Is Marked For Delete When Dropping a User With ST_GEOMETRY Dependencies [ID 1385929.1]. Dictionary gets corrupted when tables using a specific object type (SDE.ST_GEOMETRY) are tried to be dropped after the type was tried to get dropped. This corruption in the dictionary may have affected the DBMS_DATAPUMP package. I have tried re-installing the package with no success.

ESRI has a note on the subject of dropping the user SDE and/or other users who use the ST_GEOMETRY datatype, http://support.esri.com/en/knowledgebase/techarticles/detail/34483. Drop the users using the ST_GEOMETRY, before dropping the SDE user, not the other way around.

Yes, we use the SDE for business. We are in the midst of moving the instance to a new server and have been pulling our hair out trying to move SDE. On our source database, Data Pump is broken with no solution from Oracle Support to fix it. Now the new destination database has a broken Data Pump as well with some schemas up and running in production. We are working towards re-building the instance, re-build SDE from scratch and bring in the other schemas again. And be very clear which user schemas use the ST_GEOMETRY datatype and never drop them!

Re: Best way to move versioned ST_GEOMETRY FCs between Oracle 11gR2 instances?

danan — Fri, 16 Mar 2012 10:05:34 GMT

We have come to the conclusion that our problems of Data PUmp not working are related to Oracle Note, Ora-21700 Object Does Not Exist Or Is Marked For Delete When Dropping a User With ST_GEOMETRY Dependencies [ID 1385929.1]. Dictionary gets corrupted when tables using a specific object type (SDE.ST_GEOMETRY) are tried to be dropped after the type was tried to get dropped. This corruption in the dictionary may have affected the DBMS_DATAPUMP package. I have tried re-installing the package with no success.

ESRI has a note on the subject of dropping the user SDE and/or other users who use the ST_GEOMETRY datatype, http://support.esri.com/en/knowledgebase/techarticles/detail/34483. Drop the users using the ST_GEOMETRY, before dropping the SDE user, not the other way around.

Yes, we use the SDE for business. We are in the midst of moving the instance to a new server and have been pulling our hair out trying to move SDE. On our source database, Data Pump is broken with no solution from Oracle Support to fix it. Now the new destination database has a broken Data Pump as well with some schemas up and running in production. We are working towards re-building the instance, re-build SDE from scratch and bring in the other schemas again. And be very clear which user schemas use the ST_GEOMETRY datatype and never drop them!

Hi Jane,

Thanks for posting your findings. Sorry you're having so much trouble. We may ask our customer to open a ticket with Esri. Although the support forums are terrific, they aren't intended to be a replacement for Esri Support. Many problems can be solved here but this issue is particularly stubborn / tricky. I cannot open a ticket myself so I can only suggest it as an option.

Incidentally, at a class this week on Oracle Enterprise Manager (Grid Control) I mentioned the datapump issue to an instructor. His take was that many 3rd party UDTs have trouble with datapump. My (over-?)interpretation: Esri may be the victim here, not the culprit. Still, it would be nice to have official guidance. I'll post an update if we open a ticket or have success in some other way.

What about using a file geodatabase as an intermediate medium for versioned feature classes? There's no ST_GEOMETRY for Oracle spatial data type in a file GDB (naturally). But I wonder if the customer could run a Python geoprocessing task to recreate things as ST_GEOMETRY from a FGDB source to the desired Oracle target. Anyone done this? It's not entirely clear to me if the customer needs versioned edits and state trees preserved during the migration process. Will ask for clarification. Likely overthinking this?

Happy Friday. 🙂

Best,

Dana

Re: Best way to move versioned ST_GEOMETRY FCs between Oracle 11gR2 instances?

JaneLamont — Tue, 20 Mar 2012 14:46:05 GMT

Our service request with Oracle is not providing workarounds, tips or solutions for working with SDE to avoid the data dictionary corruption or avoiding the breakage of Data Pump. We have just rebuilt SDE manually in another database so have a good export to try importing into another working instance, hopefully not breaking Data Pump and keeping the d.d. intact and no missing ST_GEOMETRY data types. I have just completed a support call to ESRI for their help. I will keep you updated.

Jane

Re: Best way to move versioned ST_GEOMETRY FCs between Oracle 11gR2 instances?

danan — Tue, 20 Mar 2012 15:30:22 GMT

Our service request with Oracle is not providing workarounds, tips or solutions for working with SDE to avoid the data dictionary corruption or avoiding the breakage of Data Pump. We have just rebuilt SDE manually in another database so have a good export to try importing into another working instance, hopefully not breaking Data Pump and keeping the d.d. intact and no missing ST_GEOMETRY data types. I have just completed a support call to ESRI for their help. I will keep you updated.
Jane

Thanks for the update Jane. If and when our downstream customer opens a support ticket with Esri I will also report any findings.

Re: Best way to move versioned ST_GEOMETRY FCs between Oracle 11gR2 instances?

danan — Thu, 29 Mar 2012 10:40:33 GMT

Still working this issue. No support ticket has been opened yet as far as I know.

At an Oracle 11.2 upgrade and migration seminar yesterday, I asked Roy Swonger (owner of the dev group for various Oracle utilities) what datapump could and could not handle regarding User Defined Types (UDTs)--because he'd mentioned limitations using Oracle Streams (and was promoting Oracle Golden Gate--which sounds quite cool by the way; similar to cross-DB vendor platform GDB replication evidently). He claimed datapump could handle all UDTs but not long/long raw data types and anything having Nested Tables. That answer didn't correspond with what some other Oracle professionals have told me.

So, is Esri's ST_GEOMETRY Oracle UDT implemented in some way with Nested Tables? Maybe it's time to post to Oracle-L @ freelists.org to find a Best Practice when migrating versioned Feature Classes containing ST_GEOMETRY columns. Seems difficult to obtain posting privileges there. So if anyone here with posting privileges to Oracle-L would like to crowdsource an answer there, please feel free. The official Oracle forums haven't been the most nurturing environment I've participated in...

The upgrade and migration seminar was free and I learned quite a lot from it. Highly recommended to attend if one is coming to a town near you. Check this site for dates:

https://blogs.oracle.com/upgrade/

The slides used for the seminar are here:
http://apex.oracle.com/pls/apex/f?p=202202:2:::::P2_SUCHWORT:migrate112

... or go to the Oracle Upgrade Blog, scroll to the right, and look for a heading named Slides Download Center. Very informative.

Re: Best way to move versioned ST_GEOMETRY FCs between Oracle 11gR2 instances?

InakiSanchez — Sat, 11 Dec 2021 16:20:59 GMT

Hi all,

I am new to ESRI. We have just configured a geodatabase in our 11.2.0.3 Oracle database and have the same problem as you. If we perform a expdp of SDE schema we get an error. We are using arcSDE 10.

We only have one schema using the ST_GEOMETRY datatype, the one containing the SDE objects.

ORA-39127: error inesperado de la llamada a local_str := SYS.DBMS_EXPORT_EXTENSION.GET_DOMAIN_INDEX_METADATA('<index_name>','<schema_name>','ST_DOMAIN_METHODS','<schema_name>',11.02.00.00.00,newblock,0)
ORA-06502: PL/SQL: error : character to number conversion error numérico o de valor
ORA-06512: en "SYS.DBMS_EXPORT_EXTENSION", línea 270
ORA-06512: en línea 1
ORA-06512: en "SYS.DBMS_METADATA", línea 8360

We can get avoid this error droping the index prior to the expdp and recreate it afterwards, but in any case we get a lot of errors when doing an impdp of SDE schema.

KUP-11007: conversion error loading table "<schema>"."<feature_class>"
ORA-00600: internal error code, arguments: [kpudpxcs_ctxConvertStream_ref_1], [SYS_TYPEID("SHAPE")], [], [], [], [], [], [], [], [], [], []
ORA-31693: Fallo del objeto de datos de tabla "<schema>"."<feature_class>" al cargarse/descargarse y se está saltando debido al error:
ORA-29913: error al ejecutar la llamada de ODCIEXTTABLEFETCH
ORA-26062: No se puede continuar desde los errores anteriores.

We have an open SR to Oracle without solution so far.

Thanks,

Inaki

Re: Best way to move versioned ST_GEOMETRY FCs between Oracle 11gR2 instances?

Shwu-jingJeng — Sat, 11 Dec 2021 16:21:01 GMT

Hi Inaki,
Do you apply any service pack for ArcSDE 10? There is a fix for this in ArcSDE SP3, try to apply sp3 to ArcSDE and upgrade your database to see if the problem resolves.

Shwu-Jing

Hi all,

I am new to ESRI. We have just configured a geodatabase in our 11.2.0.3 Oracle database and have the same problem as you. If we perform a expdp of SDE schema we get an error. We are using arcSDE 10.

We only have one schema using the ST_GEOMETRY datatype, the one containing the SDE objects.
ORA-39127: error inesperado de la llamada a local_str := SYS.DBMS_EXPORT_EXTENSION.GET_DOMAIN_INDEX_METADATA('<index_name>','<schema_name>','ST_DOMAIN_METHODS','<schema_name>',11.02.00.00.00,newblock,0)
ORA-06502: PL/SQL: error : character to number conversion error numérico o de valor
ORA-06512: en "SYS.DBMS_EXPORT_EXTENSION", línea 270
ORA-06512: en línea 1
ORA-06512: en "SYS.DBMS_METADATA", línea 8360
We can get avoid this error droping the index prior to the expdp and recreate it afterwards, but in any case we get a lot of errors when doing an impdp of SDE schema.
KUP-11007: conversion error loading table "<schema>"."<feature_class>"
ORA-00600: internal error code, arguments: [kpudpxcs_ctxConvertStream_ref_1], [SYS_TYPEID("SHAPE")], [], [], [], [], [], [], [], [], [], []
ORA-31693: Fallo del objeto de datos de tabla "<schema>"."<feature_class>" al cargarse/descargarse y se está saltando debido al error:
ORA-29913: error al ejecutar la llamada de ODCIEXTTABLEFETCH
ORA-26062: No se puede continuar desde los errores anteriores.
We have an open SR to Oracle without solution so far.

Thanks,

Inaki

Re: Best way to move versioned ST_GEOMETRY FCs between Oracle 11gR2 instances?

AlanStearns — Wed, 27 Jun 2012 20:11:54 GMT

1. Follow sde installation procedures to create the SDE schema in database B
2. Drop all the triggers and sequences in the SDE schema in database B
3. Drop all the user schemas (if any exist) in database B
4. Create the user schemas and assign privilege grants in database B
5. Expdp the SDE and user schemas in database A
6. Impdp the SDE and user schemas in database B using the TABLE_EXISTS_ACTION=REPLACE

Steps 2 through 6 can be performed repetitively via a script to refresh database B with all changes in database A. Impdp will generate a lot of SDE schema errors because it can't create/replace objects that already exist, but those objects are not data related so no worries. Just rememeber to keep your SDE schemas in database A and B the same version by performing the SDE install and patching in both places.

Re: Best way to move versioned ST_GEOMETRY FCs between Oracle 11gR2 instances?

ForrestJones — Fri, 29 Jun 2012 14:12:21 GMT

Hi Dana,

It sounds like you might be running into an oracle problem with datapump. Check out the following oracle document and bug:

Ora-22809 Nonexistent Attribute Creating Table With Sde.St_geometry Type [ID 1411854.1]

Bug filed with Datapump:
Bug 13693393 - ORA-600: [KPUDPXCS_CTXCONVERTSTREAM_REF_1], [SYS_TYPEID("SHAPE")] DURING IMPORT

Re: Best way to move versioned ST_GEOMETRY FCs between Oracle 11gR2 instances?

AlanStearns — Mon, 02 Jul 2012 12:25:50 GMT

Thanks for the Oracle bug number on the ORA-600 error during SDE schema datapump import F J.

I'm running 11.2.0.2 and had to install interim patch 13451606 to fix datapump bugs 11895713 and 12794090 as none of my spatial views would make it over due to a missing select statement error.

Using the workaround I posted last week, I also consistently see the following errors on the SDE.ST_GEOMETRY_INDEX table, however the errors are not valid as the table does get updated. It's the only table where an ORA-31693 is reported ... not sure what makes it different from all other tables.

ORA-31693: Table data object "SDE"."ST_GEOMETRY_INDEX" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data

Re: Best way to move versioned ST_GEOMETRY FCs between Oracle 11gR2 instances?

danan — Tue, 21 Aug 2012 13:51:39 GMT

Hi Dana,

It sounds like you might be running into an oracle problem with datapump. Check out the following oracle document and bug:

Ora-22809 Nonexistent Attribute Creating Table With Sde.St_geometry Type [ID 1411854.1]

Bug filed with Datapump:
Bug 13693393 - ORA-600: [KPUDPXCS_CTXCONVERTSTREAM_REF_1], [SYS_TYPEID("SHAPE")] DURING IMPORT

Thanks Fojo. In My Oracle Support (MOS), a search for Bug 13693393 beneath Patches & Updates > Number/Name or Bug Number (Simple) with Bug Number selected from the drop down and 13693393 in the search box revealed nothing.

Does this mean the bug hasn't been fixed yet or was so recently submitted that it's not appearing the the search? Presuming User Error on my part and will keep looking.

[UPDATE: MOS ID 1411854.1 reports the bug in Data Pump has not yet been fixed; and to use suggested workarounds]

This all looks a bit hairy, and Esri Tech Support seems to conclusively consider this an Oracle Corp issue; not an Esri one. Wherever the truth lay, is there any easier way to handle this? Like using RMAN cloning to refresh a Recovery instance with data from a source instance? Would be interested in reading about this as a potential approach. One we eventually have Snap Manager for Oracle (SMO) configured with our NetApp storage, would SMO cloning be something worth looking into? Oracle Corp and NetApp jointly seem to tout SMO cloning as a key functionality that's a great boon to developers needing refresh data and an isolated instance to work against. That capability sounds a bit to me like ArcSDE Versioning in its approach.

Re: Best way to move versioned ST_GEOMETRY FCs between Oracle 11gR2 instances?

EmadAl-Mousa — Mon, 02 Dec 2013 07:54:27 GMT

does anybody knows any workaround for this problem ?

its really frustrating ....

Re: Best way to move versioned ST_GEOMETRY FCs between Oracle 11gR2 instances?

danan — Mon, 07 Apr 2014 17:55:25 GMT

Bump.

Need to potentially migrate an ArcSDE Oracle 11.2.0.2 instance (on a physical server running RHEL x86-64) to a VM host running RHEL x86-64, upgrading to Oracle 11.2.0.4 by the end of the process. What method should we use and which sequencing for the upgrade process? This is for an instance using ASM diskgroups if that helps any. So it's Grid Infrastructure (GI) "Oracle Restart" but not a RAC installation.

I say "potentially migrate" because it's presently being decided (by the customer) whether to stay on physical servers (out of place same server upgrade--easy) or migrate to VMWare VMs, where most of our other Oracle instances live--and certainly any newly created Oracle instances run on VMs. Easier to manage a homogenous environment in my opinion.

No more PSUs being released for Oracle 11.2.0.2. And I understand the last PSU for Oracle 11.2.0.3 for Linux x86-64 is scheduled to be July 2015.

If you've had to do a similar migration, what upgrade path did you take? And what method did you use? Or if you're in planning mode for this, what method are you considering and why?

Thanks in advance.