unable to open RPC connection to external procedure agent

4950
6
03-30-2015 06:48 AM
Highlighted
New Contributor

Hi I know that there is a lot of discussion about this error. But when i tried to apply the sollution presented, i still get the same error.

Here is my problem definition.

I have

-2.6.18-371.6.1.el5 GNU/Linux 64 bit

-Oracle 11.2.0

-Arcgis 10.2

installed.

I create a geodatabase using arccatolog software, and after that i import some tables to this geodatabase. However when i try to use some functions of this geodatabase such as st_astext i get "unable to open RPC connection to external procedure agent" error. The complete error list is given below.

ORA-28575: unable to open RPC connection to external procedure agent

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

ORA-06512: at "SDE.ST_GEOMETRY_OPERATORS", line 118

28575. 00000 -  "unable to open RPC connection to external procedure agent"

I checked for this error and found out that oracle requires some configurations for calling functions from extarnal libraries.In this case the library is libst_shapelib.so. I tried to make some configrurations in accordance to oracle documantation. The content of listener.ora, tnsnames.ora and extrproc.ora is given below.

listener.ora

ID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /u01/app/oracle11/product/11.2.0/dbhome_1)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = PGEO01)

      (ORACLE_HOME = /u01/app/oracle11/product/11.2.0/dbhome_1)

      (SID_NAME = PGEO01)

    )

    (SID_DESC =

      (SID_NAME = PLSExtProcSDE)

      (ORACLE_HOME = /u01/app/oracle11/product/11.2.0/dbhome_1)

      (PROGRAM = extproc)

        (ENVS="EXTPROC_DLLS=/geocode/arcgis/server/lib/libst_shapelib.so")

    )

    (SID_DESC =

      (SID_NAME = xxxxxx)

      (ORACLE_HOME = /u01/app/oracle11/product/11.2.0/dbhome_1)

      (PROGRAM = dg4odbc)

      (ENVS="LD_LIBRARY_PATH=/u01/app/oracle11/product/11.2.0/dbhome_1/bin")

    )

  )

LISTENER =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xx.xxx.xx)(PORT = 1525))

    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCSDE1528))

  )

#----ADDED BY TNSLSNR 19-FEB-2014 16:45:13---

PASSWORDS_LISTENER = xxxxxxxxxxx

#--------------------------------------------

tnsnames.ora

PGEO01=

   (DESCRIPTION=

      (ADDRESS=

         (PROTOCOL=TCP)

         (HOST=xxx.xx.xxx.xxx)

         (PORT=1525)

      )

      (CONNECT_DATA=

         (SERVER = DEDICATED)

         (SERVICE_NAME=PGEO01)

      )

   )

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(Key = EXTPROCSDE1528))

    )

    (CONNECT_DATA =

      (SID =PLSExtProcSDE)

      (PRESENTATION = RO)

    )

  )

PGSQL  =

  (DESCRIPTION=

    (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xx.xxx.xxx)(PORT=1525))

    (CONNECT_DATA = (SID = xxxxx))

    (HS = OK)

  )

extproc.ora

-I tried all options below:

SET EXTPROC_DLLS=

SET EXTPROC_DLLS=ANY

SET EXTPROC_DLLS=ONLY:/dddd/arcgis/server/DatabaseSupport/Oracle/Linux64/lib

After changing this files. I got the same error. As error indicates that there are 3 possibilities for this error.

- Something wrong in tnsnames or listener. ora files

-Incorrect keys or not matching

-Network issues

-Or the library is corrupted.

I still think that i dis some mistake in the tnsnames and listener . ora file configuration. But i do not know where is the mistake can you help me .

one final note:

my lsnrctl status output is:

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xx.xx.xxx)(PORT=1525)))

Services Summary...

Service "+ASM" has 1 instance(s).

  Instance "+ASM", status READY, has 1 handler(s) for this service...

Service "PGEO01" has 1 instance(s).

  Instance "PGEO01", status READY, has 1 handler(s) for this service...

Service "PGEO01XDB" has 1 instance(s).

  Instance "PGEO01", status READY, has 1 handler(s) for this service...

The command completed successfully

Thank You for your Time

Reply
0 Kudos
6 Replies
Highlighted
Esri Frequent Contributor

I would not recommend modifying the listener.ora file for the configuration of the ST_Functions with Oracle 11.2.

Please review the following documentation for setting this up within your Oracle geodatabase.

Configuring the Oracle extproc to access the geodatabase with SQL  - Look at the section for Oracle 11g only!!!

Be sure to update the EXTPROC.ora to ANY or the location of the .so file. Include the .so file name in the path.

Let us know the results.

--- George T.
Highlighted
New Contributor

Hi

I made the changes described in the link that you gave. I basically add line

  -SET EXTPROC_DLLS=ONLY:/mypath/libst_shapelib.so

But the error remained same. When i tried to call st_function like st_asText or any other i got this error. I checked the file location.

   Error:ORA-28575: unable to open RPC connection to external procedure agent

Do you have any idea why i am getting this error. Here is my lsrnctl status result:

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production

Start Date                30-MAR-2015 19:47:42

Uptime                    0 days 15 hr. 32 min. 42 sec

Trace Level               off

Security                  ON: Password or Local OS Authentication

SNMP                      OFF

Listener Parameter File   /mypath/listener.ora

Listener Log File         /mypath/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xx.xxx.xxx)(PORT=1525)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))

Services Summary...

Service "+ASM" has 1 instance(s).

  Instance "+ASM", status READY, has 1 handler(s) for this service...

Service "PGEO01" has 2 instance(s).

  Instance "PGEO01", status UNKNOWN, has 1 handler(s) for this service...

  Instance "PGEO01", status READY, has 1 handler(s) for this service...

Service "PGEO01XDB" has 1 instance(s).

  Instance "PGEO01", status READY, has 1 handler(s) for this service...

Service "PLSExtProc" has 1 instance(s).

  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

Thanks.

Reply
0 Kudos
Highlighted
Esri Frequent Contributor

Did you remove the reference from the listener.ora file (see underlined info below)? The issue seems to be a mix up of the configuration settings.

-Does the oracle account have access to the location of the .so file and permissions to execute against it?

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /u01/app/oracle11/product/11.2.0/dbhome_1)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = PGEO01)

      (ORACLE_HOME = /u01/app/oracle11/product/11.2.0/dbhome_1)

      (SID_NAME = PGEO01)

    )

    (SID_DESC =

      (SID_NAME = PLSExtProcSDE)

      (ORACLE_HOME = /u01/app/oracle11/product/11.2.0/dbhome_1)

      (PROGRAM = extproc)

        (ENVS="EXTPROC_DLLS=/geocode/arcgis/server/lib/libst_shapelib.so")

    )

    (SID_DESC =

      (SID_NAME = xxxxxx)

      (ORACLE_HOME = /u01/app/oracle11/product/11.2.0/dbhome_1)

      (PROGRAM = dg4odbc)

      (ENVS="LD_LIBRARY_PATH=/u01/app/oracle11/product/11.2.0/dbhome_1/bin")

    )

Here is what my listener looks like. I know that is on Windows, but should be very similar.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = C:\app\[install location]\product\11.2.0\dbhome_1)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\[install location]\product\11.2.0\dbhome_1\bin\oraclr11.dll")

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = [machine name].esri.com)(PORT = 1521))

      (ADDRESS = (PROTOCOL = NMP)(SERVER = [machine name])(PIPE = ORAPIPE))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

ADR_BASE_LISTENER = C:\app\[install location]

Hope this helps!!

-George

--- George T.
Reply
0 Kudos
Highlighted
New Contributor

Hi,

I am confused, you have reference to library in your listener file. Is it because you have different oracle version or shall i do the same. ?

Thanks

Reply
0 Kudos
Highlighted
Esri Frequent Contributor

I understand what you are saying, I think that this is specific to an Oracle library and not another product library.

If you are still having issues, you may need to contact Oracle support to have them help configure your EXTPROC to be configured properly.

Do you have another Oracle instance that you can test a clean workflow on?

-George

--- George T.
Reply
0 Kudos
Highlighted
New Contributor

Actually it is the only library that i need to use. And lsnrctl  restart takes a lot of time because of some required procedures. I will remove the file reference from listener.ora. the file path will be in only extproc.ora file as in the esri documantation. Than i will try it i let you know about results. Before listener restart

my listener file

LISTENER=

   (DESCRIPTION=

     (ADDRESS_LIST=

     (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1525))

     (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC732))))

# line added by Agent

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = PGEO01)

      (ORACLE_HOME = /mypath/dbhome_1)

      (SID_NAME = PGEO01)

    )

  )

#----ADDED BY TNSLSNR 19-FEB-2014 16:45:13---

PASSWORDS_LISTENER = 580EB7AB802BE1AA

#--------------------------------------------

LISTENER_+ASM=

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = geopdb2)(PORT = 1525))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1525))

    )

  )

ADR_BASE_LISTENER = /u01/app/grid

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

SECURE_REGISTER_LISTENER = (TCP)

and my tnsnames.ora file as below:

LISTENER_+ASM =

  (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1525))

PGEO01=

   (DESCRIPTION=

      (ADDRESS=

         (PROTOCOL=TCP)

         (HOST=xxx.xxx.xxx.xxx)

         (PORT=1525)

      )

      (CONNECT_DATA=

         (SERVER = DEDICATED)

         (SERVICE_NAME=PGEO01)

      )

   )

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(Key = EXTPROC732))

    )

    (CONNECT_DATA =

      (SID =PLSExtProc)

      (PRESENTATION = RO)

    )

  )

and i add following line to extproc.ora file.

SET EXTPROC_DLLS=ONLY:/mypath/libst_shapelib.so

do you see any thing wrong ?

Thank you for your help.

Reply
0 Kudos