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

danan · ‎03-08-2012

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

danan · ‎03-20-2012

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.

danan · ‎03-29-2012

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.

InakiSanchez · ‎03-30-2012

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

Shwu-jingJeng · ‎04-02-2012

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

AlanStearns · ‎06-27-2012

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.

ForrestJones · ‎06-29-2012

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

AlanStearns · ‎07-02-2012

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

danan · ‎08-21-2012

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.

EmadAl-Mousa · ‎12-01-2013

does anybody knows any workaround for this problem ?

its really frustrating ....

danan · ‎04-07-2014

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.