ST_GEOMETRY  problem on Oracle RDBMS 11g R2 11.2.0.3

16694
22
12-13-2012 09:02 AM
irishadar
New Contributor III
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
0 Kudos
22 Replies
VinceAngelo
Esri Esteemed Contributor
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
New Contributor III
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
0 Kudos
VinceAngelo
Esri Esteemed Contributor
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
0 Kudos
ForrestJones
Esri Contributor


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
0 Kudos
irishadar
New Contributor III
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
0 Kudos
VinceAngelo
Esri Esteemed Contributor
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
0 Kudos
irishadar
New Contributor III
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
0 Kudos
VinceAngelo
Esri Esteemed Contributor
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
0 Kudos
irishadar
New Contributor III
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.
0 Kudos