ORA-28579: network error during callback from external procedure

One of our application used EXTPROC_LISTENER/ external procedure, and the application displayed two errors when the SQL statement ran 120 seconds or more.


ORA-28576: lost RPC connection to external procedure agent
ORA-28579: network error during callback from external procedure


Further investigation, the SQLNET.INBOUND_CONNECT_TIMEOUT was a suspect.  It was set to 120 seconds in the sqlnet.ora


NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.INBOUND_CONNECT_TIMEOUT = 120


SQLNET.INBOUND_CONNECT_TIMEOUT parameter specifies the time (60 seconds is the default) that would be allowed for a client to connect with the database server and provide necessary authentication information.  If the client fails to establish a connection and complete authentication in the time specified, then the database server terminates the connection.  Oracle recommends setting this parameter in combination with the INBOUND_CONNECT_TIMEOUT_<listener_name> parameter in the listener.ora file.

Setup 10046 and 28579 errorstack at database level
 SQL> alter session set tracefile_identifier='10046';
SQL> alter session set timed_statistics = true;
SQL> alter session set statistics_level=all;
SQL> alter session set max_dump_file_size = unlimited;
SQL> alter session set events '10046 trace name context forever,level 12';
SQL> alter system set events ‘28579 trace name errorstack level 3’;


To unset it after trace file is produced:
SQL> alter system set events ‘28579 trace name errorstack off’;


Setup sqlnet and Extproc listener Traces
1. Enable the client trace in client side sqlnet.ora.
(If you are running from database server itself,then you need to set the following in server sqlnet.ora)
TRACE_LEVEL_CLIENT = 16
TRACE_FILE_CLIENT = Client
TRACE_DIRECTORY_CLIENT= <valid directory path that exists>
TRACE_TIMESTAMP_ CLIENT = ON
TRACE_UNIQUE_CLIENT = ON
DIAG_ADR_ENABLED =OFF

2.Enable the server trace in server side sqlnet.ora
TRACE_LEVEL_SERVER = 16
TRACE_FILE_SERVER = Server
TRACE_DIRECTORY_SERVER = <valid directory path that exists>
TRACE_TIMESTAMP_SERVER=ON
DIAG_ADR_ENABLED =OFF

3. Enable the listener tracing for Extproc listener (Listener needs to be restart to enable / disable the trace)

TRACE_FILE_<LISTENER NAME>=Listener
TRACE_LEVEL_<LISTENER NAME>=SUPPORT
TRACE_TIMESTAMP_<LISTENER NAME>=TRUE
TRACE_DIRECTORY_<LISTENER NAME>=<valid directory path>
DIAG_ADR_ENABLED_listener_name>=off

4.Enable the tracing for Extproc Agent (in Server side sqlnet.ora)
TRACE_LEVEL_AGENT= SUPPORT
TRACE_DIRECTORY_AGENT =<valid directory path that exists>
TRACE_FILE_AGENT=agent
TRACE_TIMESTAMP_AGENT =ON

Conclusion:The issue occured in our application when SQLNET.INBOUND_CONNECT_TIMEOUT = 120, the   parameter was "explicitly" set to a hard coded value, it forced the External Procedure callout execution to that limited value. The problem was resolved either changing SQLNET.INBOUND_CONNECT_TIMEOUT  = 0 (client connection to the Database server can stay open  indefinitely without authentication) or comment it from sqlnet.ora