ST_GEOMETRY problem

5311
3
Jump to solution
07-01-2015 10:32 PM
Highlighted
New Contributor II

Hi guys,

I get following error when try to select from gdb_items_vw:

select * from gdb_items_vw;

ERROR:

ORA-28595: Extproc agent : Invalid DLL Path

ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 1339

ORA-06512: at "SDE.ST_GEOM_UTIL", line 760

select st_point(0,0,0) from dual

ERROR at line 1:

ORA-28595: Extproc agent : Invalid DLL Path

ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 12

ORA-06512: at "SDE.ST_POINT", line 176

We have Oracle Database 11.2.0.3 installed at RHEL and ArcSDE 10SP5 at Windows.

After some reading get confused how should I point to correct user library, the current shows this:

SELECT * FROM USER_LIBRARIES;

LIBRARY_NAME       FILE_SPEC  D STATUS

------------------------------ -------------------------------------------------- - -------

ST_SHAPELIB       C:\ArcSDE\ArcSDE\ora11gexe\bin\st_shapelib.dll  Y VALID

Should i download ArcSDE for Linux and extract from it Linux versions of library files and put it at Linux Server where Oracle database is?

OR just update extproc.ora with C:\ArcSDE\ArcSDE\ora11gexe\bin\st_shapelib.dll, because it didn't worked - the same result???

Tags (1)
Reply
0 Kudos
1 Solution

Accepted Solutions
Highlighted
New Contributor II

Hi All,

[PS. Thanks Vince Angelo, I didn't mean and don't at all and all times to offend anybody.]

So my solution was from:
1) ArcGIS Desktop

2) ST_GEOMETRY  problem on Oracle RDBMS 11g R2 11.2.0.3

So, my steps:

1) Downloaded ArcSDE10_UNIX_121100.iso, unpacked and get required files.

2) INSTALLING ORACLE AND ARCSDE ON SEPARATE SERVERS: [6] - Copy the st_shape shared library used by EXTPROC to the Oracle server.

[root@host /]# mkdir /arcsdehome

[root@host /]# chown -Rf oracle.oinstall /arcsdehome/

[root@host /]# cp -pr /tmp/sdeexe100/lib/libst_* /arcsdehome/

[root@host /]# ll /arcsdehome/

total 12920

-rw-r--r--. 1 oracle oinstall 7572806 Jul  2 11:23 libst_raster_ora.so

-rw-r--r--. 1 oracle oinstall 5652930 Jul  2 11:23 libst_shapelib.so

[root@host /]#

[oracle@host ~]$ cat .bash_profile

export SDEHOME=/arcsdehome

export LD_LIBRARY_PATH=$SDEHOME:$ORACLE_HOME/ctx/lib:$ORACLE_HOME/lib:$LD_LIBRARY_PATH

[oracle@host ~]$ echo $SDEHOME

[oracle@host ~]$ . ~/.bash_profile

[oracle@host ~]$ echo $SDEHOME

/arcsdehome

[oracle@host ~]$

3) INSTALLING ORACLE AND ARCSDE ON SEPARATE SERVERS: [7] - Create the Oracle library entries for these ArcSDE libraries with the following SQL command as the ArcSDE administrative user (the sde user).

02-JUL-15:SDE()@dbname>column file_spec format a50

02-JUL-15:SDE()@dbname>SELECT * FROM USER_LIBRARIES;

LIBRARY_NAME       FILE_SPEC  D STATUS

------------------------------ -------------------------------------------------- - -------

ST_SHAPELIB       C:\ArcSDE\ArcSDE\ora11gexe\bin\st_shapelib.dll  Y VALID

1 row selected.

Elapsed: 00:00:00.03

02-JUL-15:SDE()@dbname>

02-JUL-15:SDE()@dbname>CREATE OR REPLACE LIBRARY st_shapelib AS '/arcsdehome/libst_shapelib.so';

  2  /

CREATE OR REPLACE LIBRARY st_shapelib AS '/arcsdehome/libst_shapelib.so';

*

ERROR at line 1:

ORA-01031: insufficient privileges

02-JUL-15:SYS(AS SYSDBA)@dbname>grant create library to sde;

Grant succeeded.

02-JUL-15:SDE()@dbname>CREATE OR REPLACE LIBRARY st_shapelib AS '/arcsdehome/libst_shapelib.so';

  2  /

Library created.

Elapsed: 00:00:00.15

02-JUL-15:SDE()@dbname>column file_spec format a50

02-JUL-15:SDE()@dbname>SELECT * FROM USER_LIBRARIES;

LIBRARY_NAME       FILE_SPEC  D STATUS

------------------------------ -------------------------------------------------- - -------

ST_SHAPELIB       /arcsdehome/libst_shapelib.so  Y VALID

1 row selected.

Elapsed: 00:00:00.03

02-JUL-15:SDE()@dbname>CREATE OR REPLACE LIBRARY libst_raster AS '/arcsdehome/libst_raster_ora.so'

  2  /

Library created.

Elapsed: 00:00:00.07

02-JUL-15:SDE()@dbname>SELECT * FROM USER_LIBRARIES;

LIBRARY_NAME       FILE_SPEC  D STATUS

------------------------------ -------------------------------------------------- - -------

LIBST_RASTER       /arcsdehome/libst_raster_ora.so  Y VALID

ST_SHAPELIB       /arcsdehome/libst_shapelib.so  Y VALID

2 rows selected.

Elapsed: 00:00:00.01

02-JUL-15:SDE()@dbname>

4) INSTALLING ORACLE AND ARCSDE ON SEPARATE SERVERS: [8] - Configure the Oracle EXTPROC so it can find the st_shapelib and libst_raster_ora libraries.

[8.a] - Add the following entry in the tnsnames.ora on the remote database server: Actually I have added per official documentation but then removed per forum note(links are above).

[8.b] - Add the following entry to the SID_LIST in listener.ora on the remote database server:Actually I have added per official documentation but then removed per forum note(links are above)

[8.c] - Modify your extproc.ora as follows

SET EXTPROC_DLLS=ONLY:/arcsdehome/libst_shapelib.so:/arcsdehome/libst_raster_ora.so

5) Compile st_geometry_shapelib_pkg.

02-JUL-15:SYS(AS SYSDBA)@dbname>alter package sde.st_geometry_shapelib_pkg compile reuse settings;

Package altered.

Elapsed: 00:00:00.39

6) 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)

02-JUL-15:SYS(AS SYSDBA)@dbname>GRANT EXECUTE ON SYS.UTL_RECOMP TO SDE;

Grant succeeded.

Elapsed: 00:00:00.03

02-JUL-15:SYS(AS SYSDBA)@dbname>conn sde/************

Connected.

02-JUL-15:SDE()@dbname>EXECUTE sys.utl_recomp.recomp_serial('SDE');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.56

02-JUL-15:SDE()@dbname>select object_name, object_type from user_objects where status = 'INVALID';

no rows selected

Elapsed: 00:00:00.03

02-JUL-15:SDE()@dbname>

Hope this procedure will help you in configuring your mixed environment!!!

Izgi niyetpen,

Gabit Almenbetov

View solution in original post

3 Replies
Highlighted
Honored Contributor

Hi Gabit,

Go to the ArcGIS install folder > database support. Copy the appropriate files from Oracle > Linux64

Paste it in the library of the Oracle installed in Linux. Restart the Oracle services.

Highlighted
Esri Esteemed Contributor

Your situation is a bit more complicated than just following the 10.1/10.2/10.3 installation procedure.

It's been a long time since I last installed 10.0 sp5 DLLs in an Oracle instance, but I don't believe the DLLs shipped with ArcGIS Desktop until 10.1, so you'll probably need to find a Linux ArcSDE Server installation binary (the DLLs must match the architecture of the LISTENER binary -- 64 bit Linux, I assume).

Oracle significantly changed ExtProc configuration between when ArcGIS 10 was released and Oracle 11gR2.  The new (more secure) procedure may not work correctly with an elderly shapelib binary.  There are a number of Knowledge Base documents on Listener configuration.  I usually google them with the Oracle error message and "arcgis".

- V

PS: Please use a gender-neutral greeting in public forums.  There are plenty of non-"guys" who would be available to answer questions if you don't offend them. 

Highlighted
New Contributor II

Hi All,

[PS. Thanks Vince Angelo, I didn't mean and don't at all and all times to offend anybody.]

So my solution was from:
1) ArcGIS Desktop

2) ST_GEOMETRY  problem on Oracle RDBMS 11g R2 11.2.0.3

So, my steps:

1) Downloaded ArcSDE10_UNIX_121100.iso, unpacked and get required files.

2) INSTALLING ORACLE AND ARCSDE ON SEPARATE SERVERS: [6] - Copy the st_shape shared library used by EXTPROC to the Oracle server.

[root@host /]# mkdir /arcsdehome

[root@host /]# chown -Rf oracle.oinstall /arcsdehome/

[root@host /]# cp -pr /tmp/sdeexe100/lib/libst_* /arcsdehome/

[root@host /]# ll /arcsdehome/

total 12920

-rw-r--r--. 1 oracle oinstall 7572806 Jul  2 11:23 libst_raster_ora.so

-rw-r--r--. 1 oracle oinstall 5652930 Jul  2 11:23 libst_shapelib.so

[root@host /]#

[oracle@host ~]$ cat .bash_profile

export SDEHOME=/arcsdehome

export LD_LIBRARY_PATH=$SDEHOME:$ORACLE_HOME/ctx/lib:$ORACLE_HOME/lib:$LD_LIBRARY_PATH

[oracle@host ~]$ echo $SDEHOME

[oracle@host ~]$ . ~/.bash_profile

[oracle@host ~]$ echo $SDEHOME

/arcsdehome

[oracle@host ~]$

3) INSTALLING ORACLE AND ARCSDE ON SEPARATE SERVERS: [7] - Create the Oracle library entries for these ArcSDE libraries with the following SQL command as the ArcSDE administrative user (the sde user).

02-JUL-15:SDE()@dbname>column file_spec format a50

02-JUL-15:SDE()@dbname>SELECT * FROM USER_LIBRARIES;

LIBRARY_NAME       FILE_SPEC  D STATUS

------------------------------ -------------------------------------------------- - -------

ST_SHAPELIB       C:\ArcSDE\ArcSDE\ora11gexe\bin\st_shapelib.dll  Y VALID

1 row selected.

Elapsed: 00:00:00.03

02-JUL-15:SDE()@dbname>

02-JUL-15:SDE()@dbname>CREATE OR REPLACE LIBRARY st_shapelib AS '/arcsdehome/libst_shapelib.so';

  2  /

CREATE OR REPLACE LIBRARY st_shapelib AS '/arcsdehome/libst_shapelib.so';

*

ERROR at line 1:

ORA-01031: insufficient privileges

02-JUL-15:SYS(AS SYSDBA)@dbname>grant create library to sde;

Grant succeeded.

02-JUL-15:SDE()@dbname>CREATE OR REPLACE LIBRARY st_shapelib AS '/arcsdehome/libst_shapelib.so';

  2  /

Library created.

Elapsed: 00:00:00.15

02-JUL-15:SDE()@dbname>column file_spec format a50

02-JUL-15:SDE()@dbname>SELECT * FROM USER_LIBRARIES;

LIBRARY_NAME       FILE_SPEC  D STATUS

------------------------------ -------------------------------------------------- - -------

ST_SHAPELIB       /arcsdehome/libst_shapelib.so  Y VALID

1 row selected.

Elapsed: 00:00:00.03

02-JUL-15:SDE()@dbname>CREATE OR REPLACE LIBRARY libst_raster AS '/arcsdehome/libst_raster_ora.so'

  2  /

Library created.

Elapsed: 00:00:00.07

02-JUL-15:SDE()@dbname>SELECT * FROM USER_LIBRARIES;

LIBRARY_NAME       FILE_SPEC  D STATUS

------------------------------ -------------------------------------------------- - -------

LIBST_RASTER       /arcsdehome/libst_raster_ora.so  Y VALID

ST_SHAPELIB       /arcsdehome/libst_shapelib.so  Y VALID

2 rows selected.

Elapsed: 00:00:00.01

02-JUL-15:SDE()@dbname>

4) INSTALLING ORACLE AND ARCSDE ON SEPARATE SERVERS: [8] - Configure the Oracle EXTPROC so it can find the st_shapelib and libst_raster_ora libraries.

[8.a] - Add the following entry in the tnsnames.ora on the remote database server: Actually I have added per official documentation but then removed per forum note(links are above).

[8.b] - Add the following entry to the SID_LIST in listener.ora on the remote database server:Actually I have added per official documentation but then removed per forum note(links are above)

[8.c] - Modify your extproc.ora as follows

SET EXTPROC_DLLS=ONLY:/arcsdehome/libst_shapelib.so:/arcsdehome/libst_raster_ora.so

5) Compile st_geometry_shapelib_pkg.

02-JUL-15:SYS(AS SYSDBA)@dbname>alter package sde.st_geometry_shapelib_pkg compile reuse settings;

Package altered.

Elapsed: 00:00:00.39

6) 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)

02-JUL-15:SYS(AS SYSDBA)@dbname>GRANT EXECUTE ON SYS.UTL_RECOMP TO SDE;

Grant succeeded.

Elapsed: 00:00:00.03

02-JUL-15:SYS(AS SYSDBA)@dbname>conn sde/************

Connected.

02-JUL-15:SDE()@dbname>EXECUTE sys.utl_recomp.recomp_serial('SDE');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.56

02-JUL-15:SDE()@dbname>select object_name, object_type from user_objects where status = 'INVALID';

no rows selected

Elapsed: 00:00:00.03

02-JUL-15:SDE()@dbname>

Hope this procedure will help you in configuring your mixed environment!!!

Izgi niyetpen,

Gabit Almenbetov

View solution in original post