dananrg

Oracle data pump import of ArcSDE 9.3.1 ST_GEOMETRY tables fails (10g to 11g)

Discussion created by dananrg on Mar 16, 2011
Latest reply on Jul 30, 2015 by fjones-esristaff
I made a data pump dump (expdp) of the SDE and data owner schemas (our data owner = GISU) from a working test instance. I can use the dump file to successfully import to an instance of the same platform (Oracle 10g on same hardware and OS).

However, importing into an 11g R2 lab instance only partially succeeds. The SDE objects get created and populated. But rows fail to load into feature class tables having an SDE.ST_GEOMETRY shape type. An example of the errors I get are:

ORA-31693: Table data object "GISU"."HYDRO24K_ARCS" 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")], [], [], [], [], [], [], [], [], [], []

In the 11g instance, a select * from dba_objects where status = 'INVALID' returns zero rows. Finally, the A and D tables in GISU get created and populated. But of course A and D tables have no SHAPE column.

Details about expdp platform:
* ArcSDE version: 9.3.1
* OS and version: x86_64 Red Hat Enterprise Linux Server release 5.4 (Tikanga) 2.6.18-164.11.1.el5
* Oracle version: 10.2.0.4.0
* Oracle compatible parameter: 10.2.0.3.0
* Syntax used for export:
expdp <dba user>@<10g source instance> DUMPFILE=Sde10g.dmp LOGFILE=Sde10g.log
DIRECTORY=<data pump dir> SCHEMAS=SDE,GISU

Details about impdp platform:
* ArcSDE version: 9.3.1 (SDE and GISU schemas are empty on the target instance but will obviously be 9.3.1 after the import)
* OS and version: x86_64 Red Hat Enterprise Linux Server release 5.4 (Tikanga) 2.6.18-164.11.1.el5
* Oracle version: 11.2.0.2.0
* Oracle compatible parameter: 10.2.0.3.0 (haven't upgraded this yet because it's irreversible and we're still getting acquainted with 11g / testing)
* Syntax used for import:
impdp <dba user>@<11g source instance> directory=<data pump dir> schemas=SDE,GISU
dumpfile=Sde10g.dmp
logfile=Sde11g.log

Main questions:

Q1) Is importing SDE 9.3.1 from 10g to 11g using data pump supported?
Q2) If not Q1, why?
Q3) If not Q1, what is the recommended method for migrations? Or is upgrading in place first the preferred solution?

Other thoughts / questions

* I've read that using the parallel option with the 11g data pump import can cause failures. Thing is, I haven't explicitly asked for parallelism. Are 11g impdp jobs run using parallelism by default?

* What's going on with SYS_TYPEID("SHAPE")?

Outcomes