7 Replies Latest reply: Mar 1, 2013 6:47 AM by B Hall RSS

    Oracle Java stack trace files

    B Hall
      Possibly someone's been down this road before. This is a first for me with this particular issue, thanks in advance for looking.

      Issue: Oracle is dumping about 3-4 Java stack trace's per minute due to a bad call (according to support) to FROM_WKTGEOMETRY. But - v$sql does not show anything, and unless I am missing something, there is not enough information in the trace file to discover WHO or WHAT is causing this problem (looking up the PID shown does no good - as the one that caused it is now gone). Yes it is most likely bad input - but from what?

      Fortunately the trace files are all small, so disk space isn't really an issue. And from what I can tell, the system's been doing this for a long time, but no one noticed before because Java-based trace files are not recorded in the alert log.

      Any ideas at this point are very welcome as support has no suggestions.

      Example dump (with a few things blanked with XXX's to protect the innocent):

      Trace file /okcgisrwprdsg/u01/app/oracle/diag/rdbms/gisrwprd/gisrwprd/trace/gisrwprd_ora_23920962.trc
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      With the Partitioning and Real Application Testing options
      ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1
      System name: AIX
      Node name: okcaixprd300a
      Release: 1
      Version: 6
      Machine: 00F618644C00
      Instance name: gisrwprd
      Redo thread mounted by this instance: 1
      Oracle process number: 1281
      Unix process pid: 23920962, image: oracle@okcaixprd300a


      *** 2013-02-13 14:00:26.249
      *** SESSION ID:(103.56321) 2013-02-13 14:00:26.249
      *** CLIENT ID:() 2013-02-13 14:00:26.249
      *** SERVICE NAME:(gisrwprd.XXX) 2013-02-13 14:00:26.249
      *** MODULE NAME:(php-cgi.exe) 2013-02-13 14:00:26.249
      *** ACTION NAME:() 2013-02-13 14:00:26.249

      java.lang.RuntimeException: 41
      at oracle.spatial.util.WKT$WKTInputStream.readDouble(WKT.java)
      at oracle.spatial.util.WKBasis.readDouble(WKBasis.java)
      at oracle.spatial.util.WKBasis.toJGeometry_WKB_POINT(WKBasis.java)
      at oracle.spatial.util.WKBasis.toJGeometry(WKBasis.java)
      at oracle.spatial.util.WKBasis.toJGeometry(WKBasis.java)
      at oracle.spatial.util.WKBasis.toSTRUCT(WKBasis.java)
      at oracle.spatial.util.Adapters.wktToSTRUCT2(Adapters.java)
      at oracle.spatial.util.Adapters.wktToSTRUCT(Adapters.java)
      at oracle.spatial.util.Adapters.wktToSTRUCT(Adapters.java)
      Exception in thread "Root Thread" java.sql.SQLException
      at oracle.spatial.util.Adapters.wktToSTRUCT(Adapters.java)
        • 1. Re: Oracle Java stack trace files
          Simon Greener
          Bryan (?),

          Since the call is FROM_WKTGEOMETRY you have an app or some SQL that is converting externally sourced WKT to SDO_GEOMETRY.

          Since the main stack trace error at fault is:

          at oracle.spatial.util.WKBasis.readDouble(WKBasis.java)

          I can generate your stack trace error (see example below with exception type 41) as follows:
          select SDO_UTIL.FROM_WKTGEOMETRY('POINT(1E5 195080.22)') 
            from dual;
          /* java.lang.RuntimeException: -3
               at oracle.spatial.util.WKT$WKTInputStream.readDouble(WKT.java)
               at oracle.spatial.util.WKBasis.readDouble(WKBasis.java)
               at oracle.spatial.util.WKBasis.toJGeometry_WKB_POINT(WKBasis.java)
               at oracle.spatial.util.WKBasis.toJGeometry(WKBasis.java)
               at oracle.spatial.util.WKBasis.toJGeometry(WKBasis.java)
               at oracle.spatial.util.WKBasis.toSTRUCT(WKBasis.java)
               at oracle.spatial.util.Adapters.wktToSTRUCT2(Adapters.java)
               at oracle.spatial.util.Adapters.wktToSTRUCT(Adapters.java)
               at oracle.spatial.util.Adapters.wktToSTRUCT(Adapters.java)
          Exception in thread "Root Thread" java.sql.SQLException
               at oracle.spatial.util.Adapters.wktToSTRUCT(Adapters.java)
          */
          
          select SDO_UTIL.FROM_WKTGEOMETRY('POINT(105,094.54 195080.22)') 
            from dual;
          /*
          java.lang.RuntimeException: 44
               at oracle.spatial.util.WKT$WKTInputStream.readDouble(WKT.java)
               at oracle.spatial.util.WKBasis.readDouble(WKBasis.java)
               at oracle.spatial.util.WKBasis.toJGeometry_WKB_POINT(WKBasis.java)
               at oracle.spatial.util.WKBasis.toJGeometry(WKBasis.java)
               at oracle.spatial.util.WKBasis.toJGeometry(WKBasis.java)
               at oracle.spatial.util.WKBasis.toSTRUCT(WKBasis.java)
            at oracle.spatial.util.Adapters.wktToSTRUCT2(Adapters.java)
               at oracle.spatial.util.Adapters.wktToSTRUCT(Adapters.java)
               at oracle.spatial.util.Adapters.wktToSTRUCT(Adapters.java)
          Exception in thread "Root Thread" java.sql.SQLException
               at oracle.spatial.util.Adapters.wktToSTRUCT(Adapters.java)
            */
          
          select SDO_UTIL.FROM_WKTGEOMETRY('POINT(105094.54)') 
          from dual;
          /*
          java.lang.RuntimeException: 41
               at oracle.spatial.util.WKT$WKTInputStream.readDouble(WKT.java)
               at oracle.spatial.util.WKBasis.readDouble(WKBasis.java)
               at oracle.spatial.util.WKBasis.toJGeometry_WKB_POINT(WKBasis.java)
               at oracle.spatial.util.WKBasis.toJGeometry(WKBasis.java)
               at oracle.spatial.util.WKBasis.toJGeometry(WKBasis.java)
               at oracle.spatial.util.WKBasis.toSTRUCT(WKBasis.java)
               at oracle.spatial.util.Adapters.wktToSTRUCT2(Adapters.java)
               at oracle.spatial.util.Adapters.wktToSTRUCT(Adapters.java)
               at oracle.spatial.util.Adapters.wktToSTRUCT(Adapters.java)
          Exception in thread "Root Thread" java.sql.SQLException
               at oracle.spatial.util.Adapters.wktToSTRUCT(Adapters.java)
          */
          
          select SDO_UTIL.FROM_WKTGEOMETRY('POINT(105094.54 NULL)') 
          from dual;
          /* 
          java.lang.RuntimeException: -3
               at oracle.spatial.util.WKT$WKTInputStream.readDouble(WKT.java)
               at oracle.spatial.util.WKBasis.readDouble(WKBasis.java)
               at oracle.spatial.util.WKBasis.toJGeometry_WKB_POINT(WKBasis.java)
               at oracle.spatial.util.WKBasis.toJGeometry(WKBasis.java)
               at oracle.spatial.util.WKBasis.toJGeometry(WKBasis.java)
               at oracle.spatial.util.WKBasis.toSTRUCT(WKBasis.java)
               at oracle.spatial.util.Adapters.wktToSTRUCT2(Adapters.java)
               at oracle.spatial.util.Adapters.wktToSTRUCT(Adapters.java)
               at oracle.spatial.util.Adapters.wktToSTRUCT(Adapters.java)
          Exception in thread "Root Thread" java.sql.SQLException
               at oracle.spatial.util.Adapters.wktToSTRUCT(Adapters.java)
          */
          41 appears to be insufficient ordinates, perhaps the parser has hit ')' before it should have.

          I suggest you look in v$sql to see if you can find the offending SQL.
          select * 
            from v$sql 
           where UPPER(sql_text) like '%FROM_WKTGEOMETRY%';
          regards
          Simon
          • 2. Re: Oracle Java stack trace files
            B Hall
            Simon - yes that's me...
            I suggest you look in v$sql to see if you can find the offending SQL.
            Did that - nothing is logged. That is what has me - stumped.

            Looking at general activity, I "think" it may be SDE, as I have found a suspicions set of SQL calls that appear to have it converting from an SDE.ST_GEOMETRY type to SDO_GEOMETRY. My guess is that is what is causing the problem (bad code on their side).

            I'm going to try and log an issue with ESRI to see what shakes out - it's just quite frustrating that I can't narrow the offending issue down more before doing so.

            Bryan
            • 3. Re: Oracle Java stack trace files
              Simon Greener
              Bryan,

              They could, I presume, be converting via anonymous pl/sql but that just doesn't seem very "ESRI".

              If they are converted SDE.ST_GEOMETRY to SDO then for the error you describe to end in the trace files they must be using Oracle queries to do the conversion and not doing it themselves in their own code. But as you say there is nothing in the v$sql tables.

              Have you turned on full ArcSDE logging and looked at its own log files? The error should appear there if they are the culprit.

              regards
              SImon
              • 4. Re: Oracle Java stack trace files
                B Hall
                Have you turned on full ArcSDE logging and looked at its own log files? The error should appear there if they are the culprit.
                I have no control over ArcSDE, but I have suggested that to the GIS admins. Time will tell...

                Bryan
                • 5. Re: Oracle Java stack trace files
                  Rpitts-Oracle
                  What RDBMS exact version are we dealing with here?

                  There is considerable improvements in error reporting coming

                  SQL> select SDO_UTIL.FROM_WKTGEOMETRY('POINT(1E5 195080.22)')
                  2 from dual;

                  SDO_UTIL.FROM_WKTGEOMETRY('POINT(1E5195080.22)')(SDO_GTYPE, SDO_SRID, SDO_POINT(
                  --------------------------------------------------------------------------------
                  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(100000, 195080.22, NULL), NULL, NULL)

                  SQL>
                  SQL> select SDO_UTIL.FROM_WKTGEOMETRY('POINT(105,094.54 195080.22)')
                  2 from dual;
                  select SDO_UTIL.FROM_WKTGEOMETRY('POINT(105,094.54 195080.22)')
                  *
                  ERROR at line 1:
                  ORA-29532: Java call terminated by uncaught Java exception:
                  java.sql.SQLException: Malformed WKT
                  ORA-06512: at "MDSYS.SDO_JAVA_STP", line 59
                  ORA-06512: at "MDSYS.SDO_UTIL", line 4773

                  THIS is a tricky potential NLS issue

                  SQL>
                  SQL> select SDO_UTIL.FROM_WKTGEOMETRY('POINT(105094.54)')
                  2 from dual;
                  select SDO_UTIL.FROM_WKTGEOMETRY('POINT(105094.54)')
                  *
                  ERROR at line 1:
                  ORA-29532: Java call terminated by uncaught Java exception:
                  java.sql.SQLException: Malformed WKT
                  ORA-06512: at "MDSYS.SDO_JAVA_STP", line 59
                  ORA-06512: at "MDSYS.SDO_UTIL", line 4773

                  IF I know the RDBMS version we might be able to do something to help here....

                  regards
                  Rich
                  • 6. Re: Oracle Java stack trace files
                    B Hall
                    Rich,
                    What RDBMS exact version are we dealing with here?
                    11.2.0.3.0 on AIX 6.1, which will be patched to 11.2.0.3.5 on Thursday.
                    There is considerable improvements in error reporting coming
                    Good to hear, that will be most welcome. Not being able to identify what system/user that is calling this makes it very difficult to get to the root cause. We eliminated SDE, but still have ArcServer processes and lots of FME processes (initiated by UC4) to check.

                    Thanks,

                    Bryan
                    • 7. Re: Oracle Java stack trace files
                      B Hall
                      We are now patched to 11.2.0.3.5. Sadly, no change - so I am still trying to track down the culprit.