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

[danan]

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

[ForrestJones]

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]

If you search in the general search for "13693393" the bug info and a document will come up (Doc ID 1411854.1). The document mentions to then go to the Patch search as you did and look for Patch ID or Number : 14026888

Hope this helps.

[ShawnThorne]

The behavior you are encountering sounds very similar to a known Oracle limitation involving Datapump and ST_GEOMETRY.

Here is a Knowledge Base Article describing this issue in more detail :

http://support.esri.com/index.cfm?fa=knowledgebase.techArticles.articleShow&d=41252

The solution is to either move to 11.2.0.3.0 and apply the Oracle Patch or move to 11.2.0.4.0.  Contact Oracle Support Services for more information regarding this issue.

Hope this helps.

-Shawn

[danan]

Thanks Fojo and Sean. Will take a look.

The behavior you are encountering sounds very similar to a known Oracle limitation involving Datapump and ST_GEOMETRY.

Here is a Knowledge Base Article describing this issue in more detail :

http://support.esri.com/index.cfm?fa=knowledgebase.techArticles.articleShow&d=41252

The solution is to either move to 11.2.0.3.0 and apply the Oracle Patch or move to 11.2.0.4.0.  Contact Oracle Support Services for more information regarding this issue.

Hope this helps.

-Shawn

[Maartende_Jong]

I've succesfully migrated ST_GEOMETRY databases with datapump. This is how:

1. Use 11.2.0.4 or apply the patch as mentioned before

2. Grant permissions

GRANT EXECUTE ON sys.dbms_pipe TO public;

GRANT EXECUTE ON sys.dbms_lock TO public;

GRANT EXECUTE ON sys.dbms_lob TO public;

GRANT EXECUTE ON sys.dbms_utility TO public;

GRANT EXECUTE ON sys.dbms_sql TO public;

GRANT EXECUTE ON sys.utl_raw TO public;

GRANT EXECUTE ON sys.DBMS_CRYPTO TO public;

-- DBMS_CRYPTO appears to be needed to create the ST_GEOMETRY data type and as the user SDE doesn't exist yet we grant it to public to avoid permission errors

3. Import SDE schema without indexes/constraints etc (this is the content of the .par file):

userid = "/@TARGETDATABASE as sysdba"

directory = DMPDIR

dumpfile = ORADUMP.DMP

logfile = oraimpdp_stap1.log

EXCLUDE = CONSTRAINT,REF_CONSTRAINT,INDEX,TABLE_STATISTICS,TRIGGER,REFRESH_GROUP

SCHEMAS       = SDE

4. Verify import by fixing invalid objects

Exec dbms_utility.compile_schema( 'SDE', compile_all => FALSE );

SELECT *

FROM DBA_OBJECTS

WHERE STATUS <> 'VALID'

ORDER BY OWNER, OBJECT_NAME;

5. Import USER schemas without indexes/constraints etc (this is the content of the .par file):

userid = "/@TARGETDATABASE as sysdba"

directory = dmpdir

dumpfile = ORADUMP.DMP

logfile = oraimpdp_stap2.log

EXCLUDE = CONSTRAINT,REF_CONSTRAINT,INDEX,TABLE_STATISTICS,TRIGGER,REFRESH_GROUP

SCHEMAS       = GEO,REF,TOPO

6. Verify import by fixing invalid objects

Exec dbms_utility.compile_schema( 'GEO', compile_all => FALSE );

SELECT *

FROM DBA_OBJECTS

WHERE STATUS <> 'VALID'

ORDER BY OWNER, OBJECT_NAME;

7. Import SDE & USER schema indexes/constraints etc (this is the content of the .par file):

userid = "/@TARGETDATABASE as sysdba"

directory = dmpdir

dumpfile = ORADUMP.DMP

logfile = oraimpdp_stap3.log

SKIP_UNUSABLE_INDEXES = Y

INCLUDE = CONSTRAINT,REF_CONSTRAINT,INDEX,TABLE_STATISTICS,TRIGGER,REFRESH_GROUP

SCHEMAS       = SDE,GEO,REF,TOPO

8. Update statistics

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SDE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'GEO', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'REF', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'TOPO', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

You might need to remove the REFRESH_GROUP option if these objects don't exists in the dmp file. (You can just try and if you get an error just remove it)