nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Find more posts tagged with

Accepted answers

All comments

VishalPahuja

Try suggestions made in the following KB

http://resources.arcgis.com/content/kbase?fa=articleShow&d=34329

danan

Try suggestions made in the following KB

http://resources.arcgis.com/content/kbase?fa=articleShow&d=34329

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?

danan

Any more thoughts? Should I open an SR with Oracle or might I be missing some steps? If I didn't mention it already, I can take the 10g dump file (containing both the SDE and data owner schemas) and import it into a 10g instance without any problems. There was no need to do SDE first and then the data owner. Not so with importing the 10g dump file into an 11g instance.

Also, would the old exp/imp utilities (pre-data pump) be worth giving a try?

danan

Any thoughts?

H__KorayGUNDUZ

Oracle version: 11.2.0.2.0 bug Oracle bug number 11666567.
Oracle SDO_DIMNAME Trigger Bug
�?� For the Adds table: "_ArcSDE Delta Table_"
�?� For the MV View: "_ArcSDE IMV Table_"

Bug fix ArcSDE 10 Service Pack 2
Bug fix ArcSDE 9.3.1 Unknown ???

Install
ArcSDE 9.3.1 Sp2 for Oracle 11.2.0.1.0
or
ArcSDE 9.3.1 Sp 2 for Oracle 11.2.0.2.0 disable MDSYS.CHK_SDO_DIMNAME Trigger and restore dump file Oracle 11.2.0.1.0 copy and paste data ArcCatalog Oracle 11.2.0.2.0

Trigger Disable Script

CREATE OR REPLACE TRIGGER MDSYS.chk_sdo_dimname BEFORE INSERT OR UPDATE ON MDSYS.SDO_GEOM_METADATA_TABLE FOR EACH ROW
DISABLE
DECLARE
cnt   NUMBER;
res   NUMBER;
BEGIN
FOR cnt IN 1 .. :NEW.sdo_diminfo.COUNT LOOP

    SELECT REGEXP_INSTR(:NEW.sdo_diminfo(cnt).sdo_dimname, '[^a-zA-Z0-9_]')
      INTO res FROM DUAL;

    IF (res > 0) THEN
      mderr.raise_md_error('MD', 'SDO_GEOM_METADATA_TABLE',-13249, 'Only alphanumeric characters and "_" are allowed in SDO_DIMNAME');
    END IF;

END LOOP;
END;
/

==========================================

Bug: Oracle 11.2.0.2 constraint on SDO_DIMNAME
http://resources.arcgis.com/content/kbase?fa=articleShow&d=38713

Regards

H.Koray GUNDUZ
Izmir Metropolitan Municipalty
Geographic Information Systems

TravisVal

koraykey,

Disabling that trigger will certainly avoid getting that error when versioning SDO_Geometry feature classes or creating multi-versioned views on feature classes that were versioned in previous releases. I'm not sure how Oracle would feel about having one of their triggers disabled though....

In this case I will be very surprised if this helps as danarng said that they were using SDE.ST_GEOMETRY. So metadata isn't stored in the SDO_GEOM_METADATA views.

danarng,

It kind of sounds like there is a problem with the type when the SYS_TYPE function is called, so maybe having two dump files would actually solve the problem. If I were you I would open an incident with ESRI technical support.

Something else that you could try, if you haven't already, is dropping the table indexes and disassociating any stats before you export the data. As for giving exp or imp a try, I think for ST_Geometry you have to use the expdp and impdp.

Finally, here is a KB article with a number links to other known issues with import/export of ST_Geometry:
http://resources.arcgis.com/content/kbase?fa=articleShow&d=34342

Good luck
Travis

VinceAngelo

I can't help but wonder if you can bridge the gap by installing ArcSDE in the 11g instance,
capture the DDL to recreate the tables, then import them, as three discrete steps. I've done
this dozens of times with SDEBINARY -> SDELOB conversion using 'exp' and 'imp', between
9i and 10g, and from 10g Windows to 10g Linux, and from 10gR2 to 11gR2.

It just seems too much to ask a single utility to get the entire process perfect, which is what
you're expecting of 'impdp' in this situation (and 'expdp' just can't be smart enough to organize
the data in the perfect order that 'impdp' would need to make the attempt).

Upgrading in place has its allures, but I find it beneficial to do a fresh database implementation
with each release, so that optimization of the vector tablespaces and raster blocks can occur
(this usually involves editing the DDL SQL text to change storage clauses and reloading the
rasters with different tile sizes to reduce chaining [especially when going from a 16k to 8k
DB_BLOCK_SIZE]). One way to achieve this is to optimize in a 10g->10g transfer, then do
an 11g in-situ upgrade.

- V

H__KorayGUNDUZ

Travis
problem in oracle, Bug Number 11666567

Esri Error "table Regitered As Versioned"

MDSYS.SDO_GEOM_METADATA_TABLE in SDO_DIMINFO column

True : ArcSDE_Delta_Table
False : ArcSDE Delta Table (blank)

e.g

False
((_ArcSDE Delta Table_; 220000; 220214,7483647; 5E-7); (_ArcSDE Delta Table_; 3900000; 3900214,7483647; 5E-7); ; )

Esri ArcSDE 9.3.1 Service Pack 2, users can publish the patch for

True
((_ArcSDE_Delta_Table_; 220000; 220214,7483647; 5E-7); (_ArcSDE Delta Table_; 3900000; 3900214,7483647; 5E-7); ; )

Bug Number 11666567

Metalink Knowlledge

Problem : ORA-39127, ORA-13249 from EXP and EXPDP when exporting Spatial indexes in 11.2.0.2
Applies to:
Oracle Spatial - Version: 11.2.0.2 to 11.2.0.2 - Release: 11.2 to 11.2
Information in this document applies to any platform.
Symptoms
Since upgrading the database to 11.2.0.2, EXP and EXPDP generate errors while exporting some Spatial indexes:

EXP:

EXP-00008: ORACLE error 13249 encountered
ORA-13249: Only alphanumeric characters and "_" are allowed in SDO_DIMNAME
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 270
ORA-06512: at line 1
EXP-00078: Error exporting metadata for index SDO_INDEX_A. Index creation will be skipped

EXPDP:

...
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
ORA-39127: unexpected error from call to local_str := SYS.DBMS_EXPORT_EXTENSION.GET_DOMAIN_INDEX_METADATA('SDO_INDEX_A','USER_A','SDO_INDEX_METHOD_10I','MDSYS',11.02.00.01.00,newblock,0)
ORA-13249: Only alphanumeric characters and "_" are allowed in SDO_DIMNAME
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 270
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 6498
ORA-39127: unexpected error from call to local_str := SYS.DBMS_EXPORT_EXTENSION.GET_DOMAIN_INDEX_METADATA('SDO_INDEX_B','USER_A','SDO_INDEX_METHOD_10I','MDSYS',11.02.00.01.00,newblock,0)
ORA-13249: Only alphanumeric characters and "_" are allowed in SDO_DIMNAME
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 270
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 6498
...

Changes
Beginning 11.2.0.2, the DIMINFO of Spatial indexes in MDSYS.SDO_GEOM_METADATA_TABLE cannot have a <space> in SDO_DIMNAME.
Cause
In earlier releases, a <space> in SDO_DIMNAME is acceptable. Therefore, in an upgraded database, these "invalid" data already exist in MDSYS.SDO_GEOM_METADATA_TABLE, and cause ORA-13249 error during export when the metadata is accessed.
Solution
Update the index's metadata and remove the <space> from its SDO_DIMNAME.

Example:

SQL> SELECT table_name, column_name AS colname, diminfo
     FROM USER_SDO_GEOM_METADATA;

TABLE_NAME   COLNAM DIMINFO(SDO_DIMNAME, SDO_LB, SDO_UB, SDO_TOLERANCE)
------------ ------ -----------------------------------------------------
COLA_MARKETS SHAPE SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X Axis', 0, 20, .005),
                     SDO_DIM_ELEMENT('Y', 0, 20, .005))

SQL> -- Changing "X Axis" TO "XAxis"
SQL> UPDATE user_sdo_geom_metadata
     SET diminfo = MDSYS.SDO_DIM_ARRAY(
                  MDSYS.SDO_DIM_ELEMENT('XAxis', 0, 20, 0.005),
                  MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, 0.005))
     WHERE table_name = 'COLA_MARKETS'
     AND column_name = 'SHAPE';

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> -- Query USER_SDO_GEOM_METADATA to view the change
SQL> SELECT table_name, column_name AS colname, diminfo
     FROM USER_SDO_GEOM_METADATA;

TABLE_NAME   COLNAM DIMINFO(SDO_DIMNAME, SDO_LB, SDO_UB, SDO_TOLERANCE)
------------ ------ -----------------------------------------------------
COLA_MARKETS SHAPE SDO_DIM_ARRAY(SDO_DIM_ELEMENT('XAxis', 0, 20, .005),
                    SDO_DIM_ELEMENT('Y', 0, 20, .005))

===============
Oracle Bug Knowledge

Line Oracle Database Products Family Oracle Database
Area Spatial - Location Services Product 619 - Oracle Spatial

Hdr: 11666567 11.2.0.2 SDOGEN 11.2.0.2 PRODID-619 PORTID-226 ORA-13249
Abstract: NEW RESTRICTION ON SDO_DIMNAME IN 11.2.0.2 IS NOT DOCUMENTED

*** 01/20/11 06:56 am ***

*** 01/20/11 07:02 am ***
Beginning in 11.2.0.2, a <space> in SDO_DIMNAME is no longer accepted,
however, this new restriction is not found in 11.2.0.2's documentation.

SQL> INSERT INTO mdsys.sdo_geom_metadata_table
2 VALUES ('SPATIAL_TEST',
3 'COLA_MARKETS2','SHAPE',
4 MDSYS.SDO_DIM_ARRAY( -- 20X20 grid
5 MDSYS.SDO_DIM_ELEMENT('X TEST', 0, 20, 0.005),   <--- X<space>TEST
6 MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, 0.005)
7 ),
8 NULL -- SRID this is our own Coordinate System.
9 );
INSERT INTO mdsys.sdo_geom_metadata_table
                  *
ERROR at line 1:
ORA-13249: Only alphanumeric characters and "_" are allowed in SDO_DIMNAME
ORA-6512: at "MDSYS.MD", line 1723
ORA-6512: at "MDSYS.MDERR", line 17
ORA-6512: at "MDSYS.CHK_SDO_DIMNAME", line 11
ORA-4088: error during execution of trigger 'MDSYS.CHK_SDO_DIMNAME'

In previous releases up to 11.1.0.7, a <space> in SDO_DIMNAME is acceptable.
*** 01/20/11 08:08 am *** (CHG: Asg->NEW OWNER OWNER)
*** 01/20/11 10:48 am *** (CHG: Fixed->12)
*** 01/20/11 10:48 am *** (CHG: Sta->89)
*** 01/20/11 10:48 am ***
I have revised the material about valid characters in the "Geometry Metadata
Views" section (Section 2.8 in the current manual) in my document source file
to include this:

------------------
The following considerations apply to schema, table, and column names, and to
any SDO_DIMNAME values, that are stored in any Oracle Spatial metadata views:

- They must contain only letters, numbers, and underscores. For example, such
a name cannot contain a space ( ), an apostrophe ('), a quotation mark ("),
or a comma (,).

[etc.]
---------------------

This revised text will appear in the next published version of the Oracle
Spatial Developer's Guide.

koraykey,

Disabling that trigger will certainly avoid getting that error when versioning SDO_Geometry feature classes or creating multi-versioned views on feature classes that were versioned in previous releases. I'm not sure how Oracle would feel about having one of their triggers disabled though....

In this case I will be very surprised if this helps as danarng said that they were using SDE.ST_GEOMETRY. So metadata isn't stored in the SDO_GEOM_METADATA views.

danarng,

It kind of sounds like there is a problem with the type when the SYS_TYPE function is called, so maybe having two dump files would actually solve the problem. If I were you I would open an incident with ESRI technical support.

Something else that you could try, if you haven't already, is dropping the table indexes and disassociating any stats before you export the data. As for giving exp or imp a try, I think for ST_Geometry you have to use the expdp and impdp.

Finally, here is a KB article with a number links to other known issues with import/export of ST_Geometry:
http://resources.arcgis.com/content/kbase?fa=articleShow&d=34342

Good luck
Travis

TracieStreltzer

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..

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:

sde_public_oracle.zip

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.

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

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 10.x geodatabase encounters Oracle errors ORA-31693, ORA-02354, ORA-00600

There is another mentioned here:

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

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.