+ Reply to Thread
Results 1 to 16 of 16

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

  1. #1
    dana n
    Join Date
    Apr 2010
    Posts
    253
    Points
    7
    Answers Provided
    0


    0

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

    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")?
    Last edited by dananrg; 03-16-2011 at 05:43 AM.

  2. #2
    Vishal Pahuja
    Join Date
    Oct 2009
    Posts
    7
    Points
    0
    Answers Provided
    0


    0

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

    Try suggestions made in the following KB

    http://resources.arcgis.com/content/...leShow&d=34329

  3. #3
    dana n
    Join Date
    Apr 2010
    Posts
    253
    Points
    7
    Answers Provided
    0


    0

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

    Quote Originally Posted by vishalpahuja View Post
    Try suggestions made in the following KB

    http://resources.arcgis.com/content/...leShow&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?

  4. #4
    dana n
    Join Date
    Apr 2010
    Posts
    253
    Points
    7
    Answers Provided
    0


    0

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

    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?

  5. #5
    dana n
    Join Date
    Apr 2010
    Posts
    253
    Points
    7
    Answers Provided
    0


    0

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

    Any thoughts?

  6. #6
    H. Koray GUNDUZ
    Join Date
    Apr 2010
    Posts
    11
    Points
    0
    Answers Provided
    0


    0

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

    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/...leShow&d=38713


    Regards

    H.Koray GUNDUZ
    Izmir Metropolitan Municipalty
    Geographic Information Systems
    Last edited by koraykey; 03-21-2011 at 12:05 PM.

  7. #7
    Travis Val
    Join Date
    May 2010
    Posts
    31
    Points
    10
    Answers Provided
    2


    0

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

    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/...leShow&d=34342

    Good luck
    Travis
    Last edited by tval; 03-21-2011 at 04:50 PM.

  8. #8
    Vince Angelo

    Join Date
    Feb 2010
    Posts
    2,722
    Points
    761
    Answers Provided
    109


    0

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

    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

  9. #9
    H. Koray GUNDUZ
    Join Date
    Apr 2010
    Posts
    11
    Points
    0
    Answers Provided
    0


    0

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

    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.


    Quote Originally Posted by tval View Post
    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/...leShow&d=34342

    Good luck
    Travis

  10. #10
    Tracie Streltzer
    Join Date
    Apr 2010
    Posts
    2
    Points
    0
    Answers Provided
    0


    0

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

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

  11. #11
    dana n
    Join Date
    Apr 2010
    Posts
    253
    Points
    7
    Answers Provided
    0


    0

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

    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.

    Quote Originally Posted by tstreltzer View Post
    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.

  12. #12
    anthony sanchez
    Join Date
    May 2010
    Posts
    45
    Points
    6
    Answers Provided
    1


    0

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

    Quote Originally Posted by dananrg View Post
    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.

  13. #13
    Vince Angelo

    Join Date
    Feb 2010
    Posts
    2,722
    Points
    761
    Answers Provided
    109


    0

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

    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

  14. #14
    Tracie Streltzer
    Join Date
    Apr 2010
    Posts
    2
    Points
    0
    Answers Provided
    0


    0

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

    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:
    Attached Files

  15. #15
    H. Koray GUNDUZ
    Join Date
    Apr 2010
    Posts
    11
    Points
    0
    Answers Provided
    0


    0

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


  16. #16
    dana n
    Join Date
    Apr 2010
    Posts
    253
    Points
    7
    Answers Provided
    0


    0

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

    Quote Originally Posted by koraykey View Post
    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.
    Dana

+ Reply to Thread

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts