15 Replies Latest reply on Aug 18, 2011 8:42 AM by dananrg

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

    dananrg
      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")?
        • Re: Oracle data pump import of ArcSDE 9.3.1 ST_GEOMETRY tables fails (10g to 11g)
          dananrg
          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?
          • Re: Oracle data pump import of ArcSDE 9.3.1 ST_GEOMETRY tables fails (10g to 11g)
            dananrg
            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?
            • Re: Oracle data pump import of ArcSDE 9.3.1 ST_GEOMETRY tables fails (10g to 11g)
              koraykey
              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
              • Re: Oracle data pump import of ArcSDE 9.3.1 ST_GEOMETRY tables fails (10g to 11g)
                tval-esristaff
                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
                • Re: Oracle data pump import of ArcSDE 9.3.1 ST_GEOMETRY tables fails (10g to 11g)
                  vangelo-esristaff
                  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
                  • Re: Oracle data pump import of ArcSDE 9.3.1 ST_GEOMETRY tables fails (10g to 11g)
                    koraykey
                    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
                    • Re: Oracle data pump import of ArcSDE 9.3.1 ST_GEOMETRY tables fails (10g to 11g)
                      tstreltzer
                      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..
                      • Re: Oracle data pump import of ArcSDE 9.3.1 ST_GEOMETRY tables fails (10g to 11g)
                        dananrg
                        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.
                        • Re: Oracle data pump import of ArcSDE 9.3.1 ST_GEOMETRY tables fails (10g to 11g)
                          geomofo
                          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.
                          • Re: Oracle data pump import of ArcSDE 9.3.1 ST_GEOMETRY tables fails (10g to 11g)
                            vangelo-esristaff
                            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
                            • Re: Oracle data pump import of ArcSDE 9.3.1 ST_GEOMETRY tables fails (10g to 11g)
                              tstreltzer
                              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:
                              • Re: Oracle data pump import of ArcSDE 9.3.1 ST_GEOMETRY tables fails (10g to 11g)
                                dananrg
                                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.