Forum Stats

  • 3,768,625 Users
  • 2,252,822 Discussions
  • 7,874,654 Comments

Discussions

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

Penny Cookson
Penny Cookson Member Posts: 12
edited May 7, 2020 8:21PM in R Technologies

Hi

Any help appreciated - trying to get Oracle Enterprise R working

I have installed R 3.3.0 with OML4R 1.5 on a Windows 10 server.  I have not done the client install as I am just working on the server.

Oracle 19 database with the OML4R install done into a PDB called ORA19PDB.

I used the downloaded R-3.3.0-win.exe for R, and ran C:\orabase\ORA19c\WINDOWS\R\server\rqcfg/.ql for the server instal

Connection works fine:

> ore.connect(user="OML4R", conn_string="ORA19PDB", password="OML4R", all=TRUE)

> ore.is.connected()

[1] TRUE

Call to OML4SQL works fine:

> example("ore.odmAI")

or.dAI>   IRIS <- ore.push(iris)

or.dAI>   ore.odmAI(Species ~ ., IRIS)

Call:

ore.odmAI(formula = Species ~ ., data = IRIS)

Importance:

              importance rank

Petal.Width   0.75224037    1

Petal.Length  0.50083423    2

Sepal.Length  0.04311746    3

Sepal.Width  -0.09256225    4

Call to any library fails:

> example("ore.glm")

or.glm> ## Don't show:

or.glm> if (!interactive())

or.glm+     ore.connect(user     = Sys.getenv("ORE_USERNAME", "rquser"),

or.glm+                 sid      = Sys.getenv("ORACLE_SID"),

or.glm+                 host     = Sys.getenv("HOST"),

or.glm+                 password = Sys.getenv("ORE_PASSWORD", "rquser"),

or.glm+                 port  = if (.Platform$OS.type == "windows")

or.glm+                            Sys.getenv("ORACLE_PORT")

or.glm+                         else

or.glm+                            Sys.getenv("TCPPORT"),

or.glm+                 all = TRUE)

or.glm> ## End(Don't show)

or.glm>   # Load libraries for examples

or.glm>   library(OREstats)

or.glm>   library(rpart)   # kyphosis and solder data sets

or.glm>   # Logistic regression

or.glm>   KYPHOSIS <- ore.push(kyphosis)

or.glm>   kyphFit1 <- ore.glm(Kyphosis ~ ., data = KYPHOSIS, family = binomial())

Error in .oci.GetQuery(conn, statement, data = data, prefetch = prefetch,  :

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

ORA-06512: at line 11

ORA-06512: at "RQSYS.RQEVALIMPL", line 17

ORA-06512: at "RQSYS.RQEVALIMPL", line 14

ORA-06512: at line 6

Listener has no entries for extproc:

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

c:\orabase\ORA19c\WINDOWS\network\admin\listener.ora

# listener.ora Network Configuration File: C:\orabase\ORA19c\WINDOWS\NETWORK\ADMIN\listener.ora

# Generated by Oracle configuration tools.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = WIN10VM1)(PORT = 1521))

    )

  )

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

Tnsnames has no entries for extproc:

c:\orabase\ORA19c\WINDOWS\network\admin and

c:\orabase\ORA19c\WINDOWS\hs\admin

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

# tnsnames.ora Network Configuration File: C:\orabase\ORA19c\WINDOWS\NETWORK\ADMIN\tnsnames.ora

# Generated by Oracle configuration tools.

ORA19 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = WIN10VM1)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ORA19)

    )

  )

ORA19PDB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = WIN10VM1)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ORA19PDB)

    )

  )

PDB2 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = WIN10VM1)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = PDB2)

    )

  )

LISTENER_ORA19 =

  (ADDRESS = (PROTOCOL = TCP)(HOST = WIN10VM1)(PORT = 1521))

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

Extproc.ora has the following entries:

SET EXTPROC_DLLS=ANY

SET TRACE_LEVEL=ON

I have removed any other older oracle folders from the path to avoid picking up and old tnsnames

Environment Variables:

ORACLE_HOME   C:\orabase\ORA19c\WINDOWS

ORACLE_ID  ORA19

Path   C:\orabase\ORA19c\WINDOWS\bin;C:\Program Files\R\R-3.3.0\bin\x64;C:\Program Files (x86)\Common Files\Oracle\Java\javapath;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;%SYSTEMROOT%\System32\OpenSSH\;

2 Trace files produced when getting the ORA-28575 error :

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

Oracle Corporation --- 2020-04-18 09:25:34.896000000

Heterogeneous Agent Release

19.0.0.0.0

Oracle Corporation --- 2020-04-18 09:25:34.895000000

    Version 19.0.0.0.0

HOA 04/18 09:25:34.898000000: (horcrase_AllocStackElt) Entered!

HOA 04/18 09:25:34.898000000: (horcrase_AllocStackElt) Exiting...

HOA 04/18 09:25:34.899000000: (horcrpuoe_PushOciEnv) Entered!

HOA 04/18 09:25:34.899000000: (horcrpuoe_PushOciEnv) Exiting...

HOA 04/18 09:25:34.900000000: (horcries_InitExtprocSession) Exiting...

HOA 04/18 09:25:34.900000000: (hotkisc_InitSessionContext) Exiting...

HOA 04/18 09:25:34.901000000: (hotkec_EstablishConnection) Entered!

2020/04/18-09:25:34.902000000: Entered shorcsje_spawn_jssu_extproc

  HS: Parent extproc received the IN param, flags_horrx = 0x1000

  HS: Parent extproc is falling back to the old behavior with connect string = (ADDRESS=(PROTOCOL=BEQ)(PROGRAM=C:\orabase\ORA19c\WINDOWS\bin\extproc)(ARGV0=C:\orabase\ORA19c\WINDOWS\bin\extproc)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))'))

  HS: Parent extproc called nsbequeath! status = 0

2020/04/18-09:25:34.964000000: Exiting shorcsje_spawn_jssu_extproc, rc=0

  HS: Parent extproc is calling exit(0)!!!

HOA 04/18 09:25:34.965000000: (hotkec_EstablishConnection) Parent extproc called shorcsje_spawn_jssu_extproc, status = 0

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

second trace file

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

Oracle Corporation --- 2020-04-18 09:25:34.955000000

Heterogeneous Agent Release

19.0.0.0.0

Oracle Corporation --- 2020-04-18 09:25:34.955000000

    Version 19.0.0.0.0

HOA 04/18 09:25:34.956000000: (horcrase_AllocStackElt) Entered!

HOA 04/18 09:25:34.957000000: (horcrase_AllocStackElt) Exiting...

HOA 04/18 09:25:34.957000000: (horcrpuoe_PushOciEnv) Entered!

HOA 04/18 09:25:34.958000000: (horcrpuoe_PushOciEnv) Exiting...

HOA 04/18 09:25:34.958000000: (horcries_InitExtprocSession) Exiting...

HOA 04/18 09:25:34.959000000: (hotkisc_InitSessionContext) Exiting...

HOA 04/18 09:25:34.959000000: (hotkec_EstablishConnection) Entered!

2020/04/18-09:25:34.961000000: Entered shorcppt_print_pid_tid

  HS: extproc is writing [12988/12256] to stdout!

  HS: Null stdout handle! extproc failed to write.

2020/04/18-09:25:34.962000000: Exiting shorcppt_print_pid_tid

HOA 04/18 09:25:34.964000000: (hotkec_EstablishConnection) Child extproc called nsinherit, status = 0

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

Best Answer

  • Sherry Lamonica-Oracle
    Sherry Lamonica-Oracle Posts: 438 Employee
    edited Apr 22, 2020 4:36PM Accepted Answer

    Hi Penny,

    The trace files don't provide the root cause for the problem in this case.  This error is often a Database restriction or OML4R configuration problem. It is most often happens when:

    • The OML4R user has not been granted RQADMIN role.
    • Networking layer restrictions or issues exist.
    • Restrictions on external procedure calls are in force.
      OML4R library configuration problem

    To narrow the problem, log into sqlplus as your ORE_USER and execute these commands, which execute along the same code paths but remove the R layer to simplify troubleshooting

    1. Test OML4R's extproc:

    SQL> select rqGamma(0.1) from dual;   

                             

    2. Test OMl4R's embedded R:

    SQL>  select rqBesselI(1,1,1) bes1, rqBesselI(1,1,0) bes2 from dual; 

    Post the output here and I'll take a look.

    Sherry

Answers

  • Sherry Lamonica-Oracle
    Sherry Lamonica-Oracle Posts: 438 Employee
    edited Apr 22, 2020 4:36PM Accepted Answer

    Hi Penny,

    The trace files don't provide the root cause for the problem in this case.  This error is often a Database restriction or OML4R configuration problem. It is most often happens when:

    • The OML4R user has not been granted RQADMIN role.
    • Networking layer restrictions or issues exist.
    • Restrictions on external procedure calls are in force.
      OML4R library configuration problem

    To narrow the problem, log into sqlplus as your ORE_USER and execute these commands, which execute along the same code paths but remove the R layer to simplify troubleshooting

    1. Test OML4R's extproc:

    SQL> select rqGamma(0.1) from dual;   

                             

    2. Test OMl4R's embedded R:

    SQL>  select rqBesselI(1,1,1) bes1, rqBesselI(1,1,0) bes2 from dual; 

    Post the output here and I'll take a look.

    Sherry

  • Penny Cookson
    Penny Cookson Member Posts: 12
    edited Apr 23, 2020 12:59AM

    Hi Sherry

    Both of those given me the same error.

    So I obviously have a config error .

    I might try a clean install on a new Windows10 VM and see how far I get.  The box does have other Oracle installs on it (although I have removed them from the paths)

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

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

    *Cause:    Initialization of a network connection to the extproc agent did

               not succeed. This problem can be caused by network problems,

               incorrect listener configuration, or incorrect transfer code.

    *Action:   Check listener configuration in LISTENER.ORA and TNSNAMES.ORA, or

               check Oracle Names Server.

    Thank you for looking

    Penny

  • Penny Cookson
    Penny Cookson Member Posts: 12
    edited Apr 29, 2020 10:57PM

    Hi Sherry

    I have had a question from one of my clients who has just installed on Linux 7 and is getting a similar issue.

    I don't have their files etc but I asked them to try the statements you gave me and they have the following result:

    1. Test OML4Rs extproc

    select rqGamma(0.1) from dual

    successfully returns

    9.51350769866873

    select rqBesseli(1,1,1) bes1, rqBesseli(1,1,0) from dual

    ORA-28578: protocol error during callback from an external procedure

    28578. 00000 -  "protocol error during callback from an external procedure"

    *Cause:    An internal protocol error occurred while trying to execute a

               callback to the Oracle server from the user's 3GL routine.

    *Action:   Contact Oracle customer support.

    Could you give me an idea what  this tells you, and as a result where we should be looking for the issue.

    Thanks

    Penny

  • Sherry Lamonica-Oracle
    Sherry Lamonica-Oracle Posts: 438 Employee
    edited May 6, 2020 4:38PM

    Hi Penny,


    Is it possible for us to set up a live troubleshooting session for the issues mentioned in this post?  We can follow-up with the solution here.

    Let me know what is convenient for you.

    Thanks,

    Sherry

  • Penny Cookson
    Penny Cookson Member Posts: 12
    edited May 6, 2020 9:19PM

    Hi Sherry

    I have given up on my installation on Windows and am using a Linux VM (that's my learning /play environment) and that is working fine so no issues there.

    My client is evaluating this and has a small POC going.  We have managed to resolve most of the issues (libpng issue followed by Wallet issue)

    We have one outstanding issue which is a Cairo library error.  I found another response from you re setting GRAPHICS = FALSE and that works for us so I am assuming maybe a Cairo library version or an X11 issue.  They are not prepared to make any more changes on the box since they have a lot of other 18c DBs on the same VM and this is just a POC, and their only 19c database.

    So at this stage I am happy that we can evaluate with the GRAPHICS = FALSE workaround.  If they go with Enterprise R as a solution, and I think they will, then they will  deploy a separate VM just for  their AA/ML and we will be able to configure how we like.  If they still have issues when we get to that stage then I will definitely be calling on you again.  Thank you for you always prompt responses in the meantime.  In the meantime I will mark this as answered.

    Regards

    Penny

  • Sherry Lamonica-Oracle
    Sherry Lamonica-Oracle Posts: 438 Employee
    edited May 7, 2020 8:21PM

    Hi Penny,

    Thanks for the update. The Cairo issues are likely due to missing OS dependencies.  We can run some diagnostic tests when you are ready.  I'll be alerted when this thread is updated in the future, so let me know if additional help his needed.

    Best,

    Sherry