topic Re: unable to open RPC connection to external procedure agent in Data Management Questions

unable to open RPC connection to external procedure agent

Nurullah_Beşli — Mon, 30 Mar 2015 13:48:56 GMT

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

Re: unable to open RPC connection to external procedure agent

George_Thompson — Mon, 30 Mar 2015 14:12:45 GMT

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.

Re: unable to open RPC connection to external procedure agent

Nurullah_Beşli — Tue, 31 Mar 2015 08:22:01 GMT

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.

Re: unable to open RPC connection to external procedure agent

George_Thompson — Tue, 31 Mar 2015 11:27:43 GMT

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

Re: unable to open RPC connection to external procedure agent

Nurullah_Beşli — Tue, 31 Mar 2015 12:04:56 GMT

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

Re: unable to open RPC connection to external procedure agent

George_Thompson — Tue, 31 Mar 2015 12:09:12 GMT

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

Re: unable to open RPC connection to external procedure agent

Nurullah_Beşli — Tue, 31 Mar 2015 12:17:53 GMT

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.