Configuring ST_RASTER on ORACLE 10g / ArcSDE 10

mohd_syafidabdullah · ‎09-29-2011

Hi GURU..
Perhaps you can help me..

I'm trying to configure keyword on ST_RASTER to store raster on certain tablespace..
But I'm facing error when run:

sdesetup -o prerequisite_check -d ORACLE10G -u sde

in arcsde..

The log stated :
[HTML]Checking ST_GEOMETRY configuration setup ...
Current instance is not correctly setup for ST_GEOMETRY configuration.
Unable to determine current version of ST_SHAPELIB
The latest ST_GEOMETRY and dependent libraries need
to be copied to the correct software location.
Please consult ArcSDE for Oracle Installation Guide
for further details.[/HTML]

Im using Oracle Release 10.2.0.3.0 with ArcSDE 10 on RHEL 5..

I already configure my listener.ora and tnsnames.ora follow the
[HTML]http://edndoc.esri.com/arcobjects/9.2/NET_Server_Doc/manager/geodatabase/administering_a-557706548/c...

Please help me...Is there missing step?

BenLin · ‎09-29-2011

Hi syafid,

Please scan through the following web help document to see if you can manage the installation of ST_Raster in your Oracle database.

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/Installing_the_ST_Raster_type_in_a_loc...

Thanks,
Ben L.

mohd_syafidabdullah · ‎09-29-2011

Hi Blin26..
I already run the command

./sdesetup -o install_st_raster -d ORACLE10G -s gdb-prd -u sde -p sde

The Output

ESRI ArcSDE Server Setup Utility Fri Sep 30 09:13:36 2011
----------------------------------------------------------------
Install or update ST_RASTER schema objects: Are you sure? (Y/N): y
Creating ST_RASTER schema...
Successfully created ST_RASTER schema.


Successfully installed ArcSDE components.

But when I issued an oracle command

select sde.st_raster_util.describe()
  2  from dual;

The result

ERROR:
ORA-28575: unable to open RPC connection to external procedure agent
ORA-06512: at "SDE.ST_RASTER_UTIL", line 191
ORA-06512: at "SDE.ST_RASTER_UTIL", line 378
ORA-06512: at "SDE.ST_RASTER_UTIL", line 352

Here also my tnsnames.ora and listener.ora

tnsnames.ora

# tnsnames.ora Network Configuration File: /oracle-prd/oracle/product/10.2.0/gis/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)
)
)
gisdb =
#LISTENER_GISDB = 
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = gdb-prd)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = gisdb)
    )
  )

putr = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = putr)(PORT = 1521))
    (CONNECT_DATA = 
       (SERVER = DEDICATED)
       (SERVICE_NAME = putr)
    )
  )

gisstg = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = gdb-stg)(PORT = 1521))
    (CONNECT_DATA = 
      (SERVER = DEDICATED)
      (SERVICE_NAME = gisstg)
    )
  )

eccdev = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = sapecc)(PORT = 1527))
    (CONNECT_DATA = 
      (SERVER = DEDICATED)
      (SID = DEV)
      (SERVICE_NAME = DEV.WORLD)
    )
  )

listener.ora

# Name of listener and addresses to listen on
gisdb =
 (DESCRIPTION_LIST = 
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = gdb-prd)(PORT = 1521))   
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
     )   
   )
)
# List of services served by this listener
SID_LIST_LISTENER=
 (SID_LIST=
  (SID_DESC=
   (SID_NAME = gisdb)
   (ORACLE_HOME = /oracle-prd/oracle/product/10.2.0/gis)
  )
  (SID_DESC=
   (SID_NAME=PLSExtProc)
   (ORACLE_HOME = /oracle-prd/oracle/product/10.2.0/gis) 
   (PROGRAM = extproc)
    (ENVS="EXTPROC_DLLS=/oracle-prd/oracle/product/10.2.0/gis/lib/libst_raster_ora.so:/oracle-prd/oracle/product/10.2.0/gis/lib/libst_shapelib.so")
  )
 )

I refer to your help link..

any work around?

BenLin · ‎09-30-2011

Hi syafid,

First, make sure that you have copied libst_shapelib.so and libst_raster_ora.so files to the ORACLE_HOME/lib folder.

Please try changing your listener.ora file as following:

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      (ADDRESS = (PROTOCOL = TCP)(HOST = gdb-prd)(PORT = 1521))
    )
)

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle-prd/oracle/product/10.2.0/gis)
      (PROGRAM = extproc)
        (ENVS="EXTPROC_DLLS=/oracle-prd/oracle/product/10.2.0/gis/lib/libst_shapelib.so:/oracle-prd/oracle/product/10.2.0/gis/lib/libst_raster_ora.so")
    )
)

Then you shall restart the listener, like this:
$ lsnrctl stop
$ lsnrctl start

Wait for one or two minutes, then run this:
$ lsnrctl status

Please send back the result for above lsnrctl status command.

Please also run following to see if you have the libraries setup correctly.

$ sqlplus sde/password@gisdb
SQL> select * from user_libraries;

Thanks,
Ben

mohd_syafidabdullah · ‎09-30-2011

Hi Ben,

I already make changes to listener.ora and start it back..also copy the related file to $ORACLE_HOME/lib directory and change permissions...Here are the output

./lsnrctl status

LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 01-OCT-2011 10:08:51

Copyright (c) 1991, 2006, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=gdb-prd)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.3.0 - Production
Start Date                01-OCT-2011 10:05:44
Uptime                    0 days 0 hr. 3 min. 6 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle-prd/oracle/product/10.2.0/gis/network/admin/listener.ora
Listener Log File         /oracle-prd/oracle/product/10.2.0/gis/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gdb-prd.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@gdb-prd bin]$

Here also the output for :

SQL> conn sde/sde
Connected.
SQL> select * from user_libraries;

LIBRARY_NAME
------------------------------
FILE_SPEC
--------------------------------------------------------------------------------
D STATUS
- -------
ST_SHAPELIB
/oracle-prd/oracle/product/10.2.0/gis/lib/libst_shapelib.so
Y VALID

LIBST_RASTER
/oracle-prd/oracle/product/10.2.0/gis/lib/libst_raster_ora.so
Y VALID

LIBRARY_NAME
------------------------------
FILE_SPEC
--------------------------------------------------------------------------------
D STATUS
- -------

Is there any miss configuration?

Thanks for yr help..

BenLin · ‎10-04-2011

Hi syafid,

Please list your environment settings for these:
- Full machine name (hostname with domain)
- $ORACLE_HOME path
- $TNS_ADMIN path
- $LD_LIBRARY_PATH
- current listener.ora and tnsnames.ora files

Some other questions:
- Are the Oracle database and ArcSDE installed on the same Linux box?
- Are the Oracle/ArcSDE/Linux all 64-bit? Or all 32-bit?
- Are you able to run following SQL query?
SQL> select sde.st_point(0,0,0) from dual;

Thanks,
Ben

mohd_syafidabdullah · ‎10-04-2011

Hi syafid,

Please list your environment settings for these:
- Full machine name (hostname with domain)
- $ORACLE_HOME path
- $TNS_ADMIN path
- $LD_LIBRARY_PATH
- current listener.ora and tnsnames.ora files

Some other questions:
- Are the Oracle database and ArcSDE installed on the same Linux box?
- Are the Oracle/ArcSDE/Linux all 64-bit? Or all 32-bit?
- Are you able to run following SQL query?
SQL> select sde.st_point(0,0,0) from dual;

Thanks,
Ben

Hi Ben , Many thanks for the reply..
- Full machine name - gdb-prd.localdomain
- $ORACLE_HOME path - /oracle-prd/oracle/product/10.2.0/gis
- $TNS_ADMIN path - not apply
- $LD_LIBRARY_PATH - $ORACLE_HOME/lib:/lib:/usr/lib:/oracle-prd/arcsde10/sdeexe100/lib
- current listener.ora

# Name of listener and addresses to listen on
#gisdb =
LISTENER = 
 (DESCRIPTION_LIST = 
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.0.71)(PORT = 1521))   
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
   )   
 )

# List of services served by this listener
SID_LIST_LISTENER=
 (SID_LIST=
  (SID_DESC=
   (SID_NAME = gisdb)
   (ORACLE_HOME = /oracle-prd/oracle/product/10.2.0/gis)
  (SID_DESC=
   (SID_NAME = PLSExtProc)
   (ORACLE_HOME = /oracle-prd/oracle/product/10.2.0/gis) 
   (PROGRAM = extproc)
   (ENVS="EXTPROC_DLLS=/oracle-prd/oracle/product/10.2.0/gis/bin/extproc:/oracle-prd/oracle/product/10.2.0/gis/lib/libst_raster_ora.so:/oracle-prd/oracle/product/10.2.0/gis/lib/libst_shapelib.so")
  )
 )
)

-Current tnsnames.ora

# tnsnames.ora Network Configuration File: /oracle-prd/oracle/product/10.2.0/gis/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)
)
)
#gisdb =
LISTENER = 
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.0.71)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = gisdb)
    )
  )

putr = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = putr)(PORT = 1521))
    (CONNECT_DATA = 
       (SERVER = DEDICATED)
       (SERVICE_NAME = putr)
    )
  )

gisstg = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = gdb-stg)(PORT = 1521))
    (CONNECT_DATA = 
      (SERVER = DEDICATED)
      (SERVICE_NAME = gisstg)
    )
  )

eccdev = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = sapecc)(PORT = 1527))
    (CONNECT_DATA = 
      (SERVER = DEDICATED)
      (SID = DEV)
      (SERVICE_NAME = DEV.WORLD)
    )
  )

- Are the Oracle database and ArcSDE installed on the same Linux box? -YES
- Are the Oracle/ArcSDE/Linux all 64-bit? Or all 32-bit? -ALL 32 bit
- Are you able to run following SQL query?
SQL> select sde.st_point(0,0,0) from dual; - Error Below

SQL> select sde.st_point(0,0,0) from dual;
select sde.st_point(0,0,0) from dual
       *
ERROR at line 1:
ORA-28575: unable to open RPC connection to external procedure agent
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 12
ORA-06512: at "SDE.ST_POINT", line 176


SQL>

Thanks..Hope to hear good news as soon as possible..

MichelleVan_Broekhoven · ‎11-15-2011

I'm posting this because I wasted a good part of day on fixing a st_geometry library problem, and hope it may be useful to someone having similar problems.

After a server rebuild, we had to restore an Oracle instance from a backup and reinstall sde. I used a tnsname file and listner file that I knew was correct(from backup), but still had the ORA-28575 error. I tried a tnsping on EXTPROC_CONNECTION_DATA and it worked, so I guessed my configuration was not the issue.

For some reason, oracle could not load the dll

My first issue was i needed to recreate the library, because i had installed sde in a different drive after the rebuild. In the SDE schema I used the command

CREATE OR REPLACE LIBRARY sde.ST_SHAPELIB AS '<FULL_PATH_TO_DLL>\st_shapelib.dll';
/

You also need to recompile the package ST_GEOMETRY_SHAPELIB_PKG

After that, I needed to manually restart the extraproc agent from the oracle box, because after a little debugging, I could see the agent itself seemed to be the problem

Do something like this at the command line-

C:>agtctl
agtctl>set agent_sid PLSExtProc
agtctl>startup extproc

you may want to also try shutdown then startup again to make sure the thing is working ok.

After that things worked.