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 dataORA-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 afterwardsIn 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.4SDE version: 9.3.1 SP2What 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 synonymshttp://support.esri.com/en/knowledgebase/techarticles/detail/34328
