Going My Way?

"There are no wrong roads to anywhere."
-- Norton Juster, The Phantom Tollbooth 

Since version 12 Oracle has changed the way EXTPROC is configured, and it may be confusing to some. The documentation provides two methods to setup EXTPROC functionality, one including the listener (as older versions required) but that method isn’t like the original as it involves both the listener.ora and tnsnames.ora files. Compound that with the “new way” to configure the service and it may leave a DBA wondering what, exactly, needs to be done. Let’s clear that up.

In versions 12 and later a new configuration file, extproc.ora, located under $ORACLE_HOME/hs/admin, provides the location for the default configuration settings. At installation this file contains the following text:

# 
# extproc.ora is used by extproc in the default Oracle configuration.
#
# This is a sample extproc init file that contains a name-value(s) pair which
# is same as the value of ENVS parameter in listener.ora file.
#
# Syntax: SET name=value (environment variable name and value)
# 
# When specifying the EXTPROC_DLLS environment variable to restrict the DLLs 
# that extproc is allowed to load, you may set EXTPROC_DLLS to one of the 
# following values:
# 
# * ONLY (maximum security) 
#
#   When EXTPROC_DLLS=ONLY:DLL[:DLL], only the specified DLL(s) can be loaded.
# 
#   Syntax: SET EXTPROC_DLLS=ONLY:DLL[:DLL]
#
# * NULL (the default value)
#
#   When EXTPROC_DLLS is not set, only the DLL(s) in $ORACLE_HOME/bin and 
#   ORACLE_HOME/lib can be loaded.
#
# * Colon-seperated list of the DLLs
#
#   When EXTPROC_DLLS=DLL[:DLL], the specified DLL(s) and the DLLs in 
#   $ORACLE_HOME/bin and ORACLE_HOME/lib can be loaded.
# 
#   Syntax: SET EXTPROC_DLLS=DLL:DLL
#
# * ANY 
#   When EXTPROC_DLLS=ANY, DLL checking is disabled.
# 
#   Syntax: SET EXTPROC_DLLS=ANY
#
#
# To turn extproc tracing on, set TRACE_LEVEL=ON (default is OFF).
#
#   Syntax: TRACE_LEVEL=ON
#

The text is really self-explanatory, providing instructions and parameter setting examples to make it fairly simple to get extproc functioning. The most basic of configurations involves a single setting:

SET EXTPROC_DLLS=ANY

which allows Oracle to use any libraries created in the database. Specific locations can be set using the following syntax:

SET EXTPROC_DLLS=DLL:DLL

where DLL represents any DLL or shared library, including the full path, located on the database server. By default this also includes any unnamed libraries under $ORACLE_HOME/bin and $ORACLE_HOME/lib. An example would be:

SET EXTPROC_DLLS=/my/lib/shared1.so:/my/lib/shared2.so:/your/lib/shared3.so

Entries are separated with colons; no restrictions are listed outside of the overall line length but if there are a large number to configure it may be best to use the ANY configuration to avoid any potential issues. LD_LIBRARY_PATH can be set, if desired, but it isn’t necessary as the configuration file applies to the current $ORACLE_HOME. [In installations where the listener has a separate home then setting LD_LIBRARY_PATH may be required.] A configuration file for a working installation is shown below:

# 
# extproc.ora is used by extproc in the default Oracle configuration.
#
# This is a sample extproc init file that contains a name-value(s) pair which
# is same as the value of ENVS parameter in listener.ora file.
#
# Syntax: SET name=value (environment variable name and value)
# 
# When specifying the EXTPROC_DLLS environment variable to restrict the DLLs 
# that extproc is allowed to load, you may set EXTPROC_DLLS to one of the 
# following values:
# 
# * ONLY (maximum security) 
#
#   When EXTPROC_DLLS=ONLY:DLL[:DLL], only the specified DLL(s) can be loaded.
# 
#   Syntax: SET EXTPROC_DLLS=ONLY:DLL[:DLL]
#
# * NULL (the default value)
#
#   When EXTPROC_DLLS is not set, only the DLL(s) in $ORACLE_HOME/bin and 
#   ORACLE_HOME/lib can be loaded.
#
# * Colon-seperated list of the DLLs
#
#   When EXTPROC_DLLS=DLL[:DLL], the specified DLL(s) and the DLLs in 
#   $ORACLE_HOME/bin and ORACLE_HOME/lib can be loaded.
# 
#   Syntax: SET EXTPROC_DLLS=DLL:DLL
#
# * ANY 
#   When EXTPROC_DLLS=ANY, DLL checking is disabled.
# 
#   Syntax: SET EXTPROC_DLLS=ANY
#
#
# To turn extproc tracing on, set TRACE_LEVEL=ON (default is OFF).
#
#   Syntax: TRACE_LEVEL=ON
#
SET EXTPROC_DLLS=ANY

At this point nothing further needs to be done; EXTPROC is now configured.

For those who aren’t comfortable with the new configuration file the listener.ora can still be configured for EXTPROC, but now this configuration includes the tnsnames.ora file. To set the listener to spawn the extproc process the following entries need to be made to the listener.ora file:

EXTPLSNR=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=ipc)(KEY=extp))))

SID_LIST_EXTPLSNR=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=ep_agt1)
      (ORACLE_HOME=/u01/app/oracbase/product/12.2.0/dbhome_1)
      (ENVS="EXTPROC_DLLS=ANY LD_LIBRARY_PATH=/home/oracle/lib")
      (ORACLE_SID=extp)
      (PROGRAM= extproc)))

The tnsnames.ora also needs changes, which deviates from the pre-12 configuration that only involved adding EXTPROC-specific settings; those are provided below:

extproc_agent=
  (DESCRIPTION=
    (ADDRESS=PROTOCOL=ipc)(KEY=extp))
    (CONNECT_DATA=
      (PRESENTATION=RO)
      (SID=ep_agt1)))

Yet another change requires that the new EXTPROC listener, EXTPLSNR, be started:</

$  lsnrctl start extplsnr

The usual listener output should be seen:

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extp)))
STATUS of the LISTENER
------------------------
Alias                     EXTPLSNR
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                09-SEP-2020 15:41:44
Uptime                    1 days 0 hr. 39 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /my/oracle/home/network/admin/listener.ora
Listener Log File         /my/oracle/diag/tnslsnr/myserver/extplsnr/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extp)))
Services Summary...
Service "ep_agt1" has 1 instance(s).
  Instance "ep_agt1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

At this point the listener will spawn the EXTPROC agent and external procedures will run as expected. [If processes using these external procedures access endpoints on remote servers it will be necessary to implement the proper firewall rules to permit connections between the servers.] Both CAN be configured, however the default configuration using the extproc.ora file will take precedence, making the listener configuration unnecessary; if the listener is preferred simply restore the extproc.ora file to its original state and the listener configuration will take over.

EXTPROC isn’t just the configuration discussed here, it’s also external shared libraries and procedures/functions written to use those libraries. The external libraries are operating system dependent, so shared libraries from AIX cannot be used on a Linux system, for example. Such libraries will need to be recreated from the source code; note that different operating systems can employ different compilers and not all compilers provide the same inherent functions. As an example HP-UX provides an ltoa() function native to the compiler, and Linux does not. When dealing with such inconsistencies it may be necessary to modify the original code to produce a working library. That discussion is beyond the scope of this post; it is mentioned to help troubleshoot EXTPROC installations migrated from one O/S to another. Ensure that all of the source code necessary for the shared libraries is available and is migrated to its new home or external procedure calls may fail.

EXTPROC in version 12 and later isn’t rocket science, but it could take a little time and patience to get up and running. The effort expended will most likely be worth the time it takes to get it properly configured, and it will open a new realm of possibilities in data processing by allowing other avenues of data entry and data manipulation, all from within an Oracle database. As an example an external library call can access a shell script to facilitate file transfers between two servers and provide feedback on the success or failure of that process. That’s, well, cool.

And that makes this another road to … anywhere.