topic Re: ST_GEOMETRY problem on Oracle RDBMS 11g R2 11.2.0.3 in Data Management Questions

ST_GEOMETRY problem on Oracle RDBMS 11g R2 11.2.0.3

irishadar — Thu, 13 Dec 2012 17:02:35 GMT

We've installed ARCSDE 10.1 on Oracle RDBMS 11g R2 11.2.0.3 in Solaris 10 box.

Unfortunately we can't get the ST_GEOMETRY to work.

We keep getting the following error even though every thing configured by the book.

Error starting at line 2 in command:

select st_point(0,0,0) from dual

Error report:

SQL Error: ORA-28595: Extproc agent : Invalid DLL Path

ORA-06512: "SDE.ST_GEOMETRY_SHAPELIB_PKG",line12

ORA-06512: "SDE.ST_POINT",line176

28595. 00000 - "Extproc agent : Invalid DLL Path"

I've enclose my DBA notes as well.

has any one got st_geometry working on oracle 11.2.0.3 on unix ?

help would be much appriciated

iris hadar
IAA

Re: ST_GEOMETRY problem on Oracle RDBMS 11g R2 11.2.0.3

VinceAngelo — Thu, 13 Dec 2012 17:29:48 GMT

The nominal ArcGIS install uses the Windows path for ST_GEOMETRY creation. You need
to update the user library to point to the Unix path to the ArcSDE server install.

"SELECT * FROM USER_LIBRARIES" as the SDE user should indicate whether your path
has been assigned correctly.

- V

Re: ST_GEOMETRY problem on Oracle RDBMS 11g R2 11.2.0.3

irishadar — Fri, 14 Dec 2012 02:29:50 GMT

Hi,
Thanks for your prompt response.
But we've already notice that and recompile the shape library to match the UNIX path.

LIBRARY_NAME FILE_SPEC
------------------------------ -------------------------------------
ST_SHAPELIB /var/opt/esrilibs/libst_shapelib.so

It is still doent work 😞

My DBA talked with Oracle and says that oracle version higher then 11.2.0.1 has a bug concerning external proc (LD_LIBRARY_PATH Set In extproc.ora File Is Ignored, ORA-06522 Could Be Raised (Doc ID 1194224.1))

Unfittingly I couldnâ??t find any documentation or work around on this .

if any one has suggestion or was able to install it on 11.2..0.3 I would me more than happy to know how ?

iris

Re: ST_GEOMETRY problem on Oracle RDBMS 11g R2 11.2.0.3

VinceAngelo — Fri, 14 Dec 2012 10:34:31 GMT

First off, I must strongly discourage copying libraries out of $SDEHOME/lib. Instead,
reference them in the installed location. This will eliminate confusion at service pack
application, since applying the SP to the install will update the listener automatically.

Since it's so easy to change the LD_LIBRARY_PATH in the boot script, it's hard to see
why inability to set it once the app has been started is a show-stopper.

Please provide the listener.ora and tnsnames.ora files from your
$ORACLE_HOME/network/admin directory.

- V

Re: ST_GEOMETRY problem on Oracle RDBMS 11g R2 11.2.0.3

ForrestJones — Fri, 14 Dec 2012 15:09:29 GMT

Please provide the listener.ora and tnsnames.ora files from your
$ORACLE_HOME/network/admin directory.

- V

Or please provide/check the extproc.ora file if using that instead...
$ORACLE_HOME/hs/admin/extproc.ora

Re: ST_GEOMETRY problem on Oracle RDBMS 11g R2 11.2.0.3

irishadar — Fri, 14 Dec 2012 18:39:56 GMT

Hi all,
Thanks again for your help 🙂

As I understand now we must use ONLY one of the two configurations.
I see that we are using both configurations at once.
However I'm sending you all tree files, for you to check please, tell how should we proceed from here.

Do you think we should leave it and go back to 11.2.0.1.0?

iris

Re: ST_GEOMETRY problem on Oracle RDBMS 11g R2 11.2.0.3

VinceAngelo — Sat, 11 Dec 2021 17:10:52 GMT

I had no difficulty configuring listener.ora/tnsnames.ora in 11.2.0.2 with ST_GEOMETRY:

% sqlplus sde/redacted@o11gR2a

SQL*Plus: Release 11.2.0.2.0 Production on Fri Dec 14 16:42:37 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select st_point(0,0,0) from dual;

ST_POINT(0,0,0)(ENTITY, NUMPTS, MINX, MINY, MAXX, MAXY, MINZ, MAXZ, MINM, MAXM,
--------------------------------------------------------------------------------
ST_POINT(1, 1, 0, 0, 0, 0, NULL, NULL, NULL, NULL, 0, 0, 0, '0C00000001000000808
0DD9DA4178080DD9DA417')

Please do not use zipfiles to transfer short ASCII files that could otherwise
be pasted into a text window. Not everyone can, and many won't download
them due to security or platform issues.

- V

Re: ST_GEOMETRY problem on Oracle RDBMS 11g R2 11.2.0.3

irishadar — Sat, 11 Dec 2021 17:10:55 GMT

ok , so this are the files :

TNSNAMES.ORA

# TNSNAMES.ORA Network Configuration File: /app_root/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

o11g =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = archeo)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = o11g)
    )
)

LISTENER.ORA

# LISTENER.ORA Network Configuration File: /app_root/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = archeo)(PORT = 1521))
      )
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM = extproc)
 (ENVS = "EXTPROC_DLLS=/var/opt/esrilibs/libst_shapelib.so,LD_LIBRARY_PATH=/var/opt/esrilibs")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = o11g.iaa.local)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = o11g)
    )
  )
  )

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
INBOUND_CONNECT_TIMEOUT_LISTENER=500

extproc.ora

SET EXTPROC_DLLS=ONLY:/var/opt/esrilibs/libst_shapelib.so
 SET LD_LIBRARY_PATH=/var/opt/esrilibs:/u01/app/oracle/product/11.2.0/dbhome_1/lib

this is what i get from

SELECT * FROM USER_LIBRARIES

LIBRARY_NAME                   FILE_SPEC                           DYNAMIC          STATUS  
------------------------------ ---------------------------------------------------------------------
ST_SHAPELIB    /var/opt/esrilibs/libst_shapelib.so  Y   VALID

this is what i get now from

select st_point(0,0,0) from dual

SQL*Plus: Release 11.2.0.3.0 Production on  Fri Dec 15 05:55:23 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select st_point(0,0,0) from dual;
select st_point(0,0,0) from dual
       *
ERROR at line 1:
ORA-06520: PL/SQL: Error loading external library
ORA-06522: ld.so.1: extproc: fatal: libclntsh.so.10.1: open failed: No such
file or directory
ORA-06512: "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 12
ORA-06512: "SDE.ST_POINT", line 176

any suggestions ?

iris

Re: ST_GEOMETRY problem on Oracle RDBMS 11g R2 11.2.0.3

VinceAngelo — Sat, 15 Dec 2012 19:08:51 GMT

It looks like you've installed ArcSDE for 10gR2. You should also run 'ldd' against
the ST_GEOMETRY libst_shapelib.so DLL -- You may need to list all the dependent
libraries (sde.so, sg.so, pe.so) to conform with the ONLY tag (yet another reason
to use the $SDEHOME/lib directory in place).

- V

Re: ST_GEOMETRY problem on Oracle RDBMS 11g R2 11.2.0.3

irishadar — Sun, 16 Dec 2012 03:47:07 GMT

Hi, thank again for all of your help,
Why do you think we have installed ArcSDE for 10gR2?

We don�??t have the "ArcSDE application server" currently installed at all.
I'll describe our work flow, maybe it can clarify some things:

1. We've followed the instruction laid out in "setting up a geodatabase in oracle" and used the "Create Enterprise Geodatabase" geoprocessing tool in ArcGIS for Desktop
To create the sde schema and then used direct connect to use it.

2. We've followed the instruction laid out in "Installations on a Linux or UNIX server" we used this file to do so "ArcSDE_for_Oracle11g_on_Solaris_101_130017.tar.gz".

3. We've tested the st_geometry and it returned the " SQL Error: ORA-28595: Extproc agent : Invalid DLL Path " , so our local distributor said that we do not need the " ArcSDE application server" on Unix any more , so we've removed it.

As you can see nothing changed , we can use sde from within "AcGIS for Desktop"
But can't use the st_geometry functionality.

Re: ST_GEOMETRY problem on Oracle RDBMS 11g R2 11.2.0.3

irishadar — Sat, 11 Dec 2021 17:10:58 GMT

I've run the lld command, this is the results:

SDE /var/opt/esrilibs 12 > ldd libst_shapelib.so
        libclntsh.so.10.1 =>     /u01/app/oracle/product/11.2.0/dbhome_1/lib/lib
clntsh.so.10.1
        libCrun.so.1 =>  /usr/lib/64/libCrun.so.1
        libnnz11.so =>   /u01/app/oracle/product/11.2.0/dbhome_1/lib/libnnz11.so
        libkstat.so.1 =>         /lib/64/libkstat.so.1
        libnsl.so.1 =>   /lib/64/libnsl.so.1
        libsocket.so.1 =>        /lib/64/libsocket.so.1
        libresolv.so.2 =>        /lib/64/libresolv.so.2
        libgen.so.1 =>   /lib/64/libgen.so.1
        libdl.so.1 =>    /lib/64/libdl.so.1
        libsched.so.1 =>         /usr/lib/64/libsched.so.1
        librt.so.1 =>    /lib/64/librt.so.1
        libc.so.1 =>     /lib/64/libc.so.1
        libaio.so.1 =>   /lib/64/libaio.so.1
        libpool.so.1 =>  /usr/lib/64/libpool.so.1
        libm.so.2 =>     /lib/64/libm.so.2
        libmp.so.2 =>    /lib/64/libmp.so.2
        libmd.so.1 =>    /lib/64/libmd.so.1
        libscf.so.1 =>   /lib/64/libscf.so.1
        libxml2.so.2 =>  /usr/lib/64/libxml2.so.2
        libnvpair.so.1 =>        /lib/64/libnvpair.so.1
        libexacct.so.1 =>        /usr/lib/64/libexacct.so.1
        libdoor.so.1 =>  /lib/64/libdoor.so.1
        libuutil.so.1 =>         /lib/64/libuutil.so.1
        libpthread.so.1 =>       /lib/64/libpthread.so.1
        libz.so.1 =>     /usr/lib/64/libz.so.1
        /platform/SUNW,Sun-Fire-V240/lib/sparcv9/libc_psr.so.1
        /platform/SUNW,Sun-Fire-V240/lib/sparcv9/libmd_psr.so.1

I've also run the command "ldd -v libst_shapelib.so |grep 'not found'"
it came back with nothing .

I think it is simply not working on 11.2.0.3.0 for some reason.

iris

Re: ST_GEOMETRY problem on Oracle RDBMS 11g R2 11.2.0.3

irishadar — Sun, 16 Dec 2012 05:20:36 GMT

problem solved 🙂
we realy don't know what solve it but here are the three major changes:
1.You must take the libst_shapelib.so from the unix installation and not from the "AcGIS for Desktop" as documented by ESRI.
2. you must have both extproc.ora and old configuration "LISTENER.ORA+TNSNAMES.ORA".
3. the libst_shapelib.so must be in oracle root installation (oracle recommendation)

thank you for your time
iris

Re: ST_GEOMETRY problem on Oracle RDBMS 11g R2 11.2.0.3

VinceAngelo — Sun, 16 Dec 2012 14:39:22 GMT

I don't have the time to go through a 11.2.0.3 install right now, but I have doubts
about your solution as a recipe for others:
+ My ArcSDE for Oracle 11g libst_shapelib.so is dependent on libclntsh.so.11.1,
and the 10g install is dependent on libclntsh.so.10.1
+ Can you provide a URL to the instruction to use the Windows DLL on Unix?
+ The Oracle docs say that only changes to extproc.ora are necessary
+ Your distributor was wrong to say that the ArcSDE application server was
unnecessary. While you certainly don't need to run it, the application server
install is the location that service packs must be applied, so you should keep it
on the database server, preferably as the location from which the DLL is obtained.
Removing the install hides the fact that Esri components are installed on the
system, and makes it more difficult on a future admin/DBA to understand what
has occurred previously.

- V

Re: ST_GEOMETRY problem on Oracle RDBMS 11g R2 11.2.0.3

irishadar — Sun, 16 Dec 2012 15:10:04 GMT

I completely agree with you since as I mentioned in my reply , we realy don't know what solve it , I simply pointing the changes that made the difference.

I didn�??t mentioned it before but this was a completely new installation , no ARCSDE was ever install on it , so maybe that's way we don�??t have the dependencies you have (I've enclose ours in my last reply) .

In any case this is the URL: http://resources.arcgis.com/en/help/main/10.1/index.html#/A_quick_tour_of_setting_up_a_geodatabase_in_Oracle/002n00000001000000/
Under "Setting up a geodatabase in Oracle" you can find these line:
"The ST_Geometry library can be found in the DatabaseSupport directory of your ArcGIS client."

Regarding the extproc.ora , I know this is what's documented but , it didn�??t work till we added the "old style" configuration as well .we plan on testing it again with only the extproc.ora config, once we finish making some benchmarks.

I accepts your suggestion regarding the" ArcSDE application server " and will pass on to my DBA .

Thanks again for all your effort to help us, it is really appreciated.

Iris hadar

Re: ST_GEOMETRY problem on Oracle RDBMS 11g R2 11.2.0.3

EmadAl-Mousa — Sun, 23 Feb 2014 09:48:33 GMT

i have the same "EXACT" problem.

Our environment:

Database release: ORACLE11G (11.2.0.3)
ArcSDE Geodatabase 10.1 SP1
Server: IBM AIX 6.1

error received:

ORA-28575: unable to open RPC connection to external procedure
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG",line 1325
ORA-06512: at "SDE.ST_GEOM_UTIL",line 823

i have configured the external procedure per ESRI & Oracle documentation, and still received the same problem.
Also, i have configured the (tnsnames and listener) as a last resort, and still having the same issue.

i have tried this in multiple oracle instances with release "11.2.0.3"

i think this is a serious issue and ESRI should look into it and provide technical article if possible.

if anybody has any suggestions, it will be appreciated.

Re: ST_GEOMETRY problem on Oracle RDBMS 11g R2 11.2.0.3

irishadar — Sun, 23 Feb 2014 17:58:11 GMT

Hi,
We eventually went down to oracle 11.2.0.1 but we also made the following changes , so maybe this will help you:

1.Take the shape.so lib from the arcsde installation for unix and not from arc catalog as describe in the documentation ( we have tested it twice to be sure , the file from windows arc catalog is not working ).

2.Make sure that the path is ok , using these syntax,
   �?�SELECT * FROM USER_LIBRARIES

3.We've followed these steps provided by our local distributer , step by step :

     a.Modify your extproc.ora as follows: SET EXTPROC_DLLS=ONLY:�?�.�?�(path)�?�.�?� libst_shapelib.so

     b.Re-create the libraries: $sqlplus sde/sde SQL> create or replace library ST_SHAPELIB as '�?�.�?�(path)�?�.�?� libst_shapelib.so '; Library created.

    c.Make sure that the dlls are valid. SQL>select * from user_libraries; Make sure it is pointing to the correct path and is valid.

    d.Compile st_geometry_shapelib_pkg SQL> alter package sde.st_geometry_shapelib_pkg compile reuse settings; Package altered.

    e.Creating the library causes dependent object to become invalid. Execute the following command as the SDE user (you may need to grant the execute privilege on sys.utl_recomp to user SDE):
          i.As sys user, grant execute on SYS.UTL_RECOMP TO SDE. SQL> GRANT EXECUTE ON SYS.UTL_RECOMP TO SDE;
          ii.As SDE user, recompile. SQL> EXECUTE sys.utl_recomp.recomp_serial('SDE');

    f. Verify all objects are valid as SDE user. SQL> select object_name, object_type from user_objects where status = 'INVALID'

     g.Remove for now the "tnsnames+listener" config and see if it works

hope this will work for you

iris hadar

Re: ST_GEOMETRY problem on Oracle RDBMS 11g R2 11.2.0.3

EmadAl-Mousa — Mon, 24 Feb 2014 02:21:00 GMT

Thanks alot iris,

if you don't mind i have the following questions:

1. did you report this as a "bug" to Oracle ? is ESRI support investigated and reported this as a bug after checking your case ?

2. So, "extproc" configuration only worked when you reverted to 11.2.0.1 ?

Re: ST_GEOMETRY problem on Oracle RDBMS 11g R2 11.2.0.3

irishadar — Mon, 24 Feb 2014 05:59:37 GMT

Emad Al-Mousa hi,

For you second question, I don�??t know if it works only on 11.2.0.1 we never tested it again on 11.2.0.3 so if you follow the steps it might work for you.

I have to emphasis that our initial install process was not as described in my last replay, so we really don�??t know what made it work the oracle "reverted to 11.2.0.1" or simply the install process.

For the first question, we did open an incident in oracle, never got something worth working with, incident number: SR 3-6541500181 : extproc.ora DLL path not found

There was also incident opened at esri which resulted with the steps described in my last replay, Esri Incident #1105536.

And just to clarify we've got "extproc" and "tnsnames+listener" working side by side with no problem

Hope that helps you ,
Iris Hadar

Re: ST_GEOMETRY problem on Oracle RDBMS 11g R2 11.2.0.3

EmadAl-Mousa — Mon, 24 Feb 2014 07:19:58 GMT

Thank You.

Re: ST_GEOMETRY problem on Oracle RDBMS 11g R2 11.2.0.3

EmadAl-Mousa — Tue, 25 Feb 2014 15:07:55 GMT

after extensive troubleshooting, i found out that this will occur in one of two situations:

1. the libst_shapelib.so external procedure library is corrupted.

OR

2. the libst_shapelib.so external procedure library is a 32 bit.......to check that use the Unix Command ( file libst_shapelib.so) the output should give you 64bit

in my situation, the library was corrupted (for some reason).....when i copied the the library form my local machine (C:\Program Files (x86)\ArcGIS\Desktop10.1\DatabaseSupport) to the database server and used the external procedure method 'ONLY' it worked fine and i was able to execute the query: select * from gdb_items_vw;