ST_GEOMETRY problem on Oracle RDBMS 11g R2 11.2.0.3

irishadar · ‎12-13-2012

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

VinceAngelo · ‎12-13-2012

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

irishadar · ‎12-13-2012

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

VinceAngelo · ‎12-14-2012

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

ForrestJones · ‎12-14-2012

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

irishadar · ‎12-14-2012

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

VinceAngelo · ‎12-14-2012

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

irishadar · ‎12-14-2012

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

VinceAngelo · ‎12-15-2012

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

irishadar · ‎12-15-2012

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.