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

[danan]

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")?

[danan]

Thanks Travis and Vince. Moot for me presently but will consider both suggestions for the future. Vince, thanks for explaining the benefits of a fresh db implementation.

I am having the same problem when exporting from 11g R2 and importing into another 11G R2 instance.  The issue appears to be on import on 11G.  I'm going to open a premium incident on this..

Tracie, if you don't mind, please share the resolution details from your incident when it's closed out.

[anthonysanchez]

Thanks Vishal. I tried again using the method recommended above and got the same exact errors as before. Also, one of the errors listed in the KB is not the same as the one I'm getting. I get the first two it lists, but I don't get:

ORA-39779: type "SDE"."ST_GEOMFROMTEXT" not found or conversion to latest version is not possible".

Instead, I get the following (note the different function):
ORA-00600: internal error code, arguments: [kpudpxcs_ctxConvertStream_ref_1], [SYS_TYPEID("SHAPE")], [], [], [], [], [], [], [], [], [], []

So there is something different going on. What do you advise?

Hello,
ST_GEOMETRY is a user defined type.  There are many dependencies and public synonyms, many of which will not come across in a schema level dp export.  I'd suggest taking a FULL data pump export and importing into your test system.

[VinceAngelo]

If you are changing RDBMS releases (10g ->11g) you should probably upgrade the ArcSDE install
before attempting data import. Even when homogeneous, you can't rely on impdb to create the
SDE user first. This is why I recommend doing a full ArcSDE install, then a data import, then register
the imported layers with ArcSDE (sdelayer -o register).

- V

[TracieStreltzer]

The datapump import also fails on 11.2.0.2 to 11.2.0.2.  We have an open SR with Oracle on the issue.  The workaround is to use regular oracle export and import, temporarily grant the schema owner DBA role, and then make the following grants using attached script after the import finishes:

[MatjazHabic]

After upgrade from ArcSDE 9.2 (Oracle 10.2 g)  to ArcSDE 10.2.2 (Oracle 12c) there was problem by ST_GEOMETRY spatial index export with datapump. This script resolves the problem.

Thanks Tracie

Matjaz

[H__KorayGUNDUZ]

Patch

http://resources.arcgis.com/content/patches-and-service-packs?fa=viewPatch&PID=66&MetaID=1793

[danan]

Patch

http://resources.arcgis.com/content/patches-and-service-packs?fa=viewPatch&PID=66&MetaID=1793

Thanks Koray. The Oracle 11.2.0.2 item you reference above seems related only to versioned FCs, or multi-versioned views, with an Oracle Spatial (SDO_GEOMETRY) spatial data type. Didn't see any mention of ST_GEOMETRY.

[ForrestJones]

There were a few issues in 11.2 related to datapump that you could be running into. This one looks like a possibility:

Technical Article #41252 - Problem:  Importing an Oracle 11g R2 Data Pump file containing an ArcSDE ...

There is another mentioned here:

Technical Article #44687 - Index:  Issues related to Oracle 11.2.0.3